Which isolation level to use to prevent data from being read?

You have to hold the lock the duration of a transaction. And exclusively too.

You have to hold the lock the duration of a transaction. And exclusively too. Now, I'm not sure of the correct options for SQL Server 6.5.Have not worked with it since, er, 199x BEGIN TRAN --edit, changed to XLOCK, ROWLOCK, HOLDLOCK SELECT * from TABLE WITH (XLOCK, ROWLOCK, HOLDLOCK) where ID = 1 ... INSERT COMMIT Edit: My change aims to lock the single row exclusively (with fine granularity) to the end of the transaction.

However, IIRC ROWLOCK was added with SQL Server 7 and 6.5 was only page locks. But it has been some time. I had hair and teeth back then :-).

I must not use table lock. Bacause there are some realy heavy queris going on all the time, and TABLOCKX is table lock right? – 100r Oct 15 '10 at 16:17 Please see update – gbn Oct 15 '10 at 16:23 tnx.

I couldn't make any of locks work. Query won't compile. Maybe 6.5 sintax is different.

I'll try it on monday at work, thank god I don't have 6.5 at home :) – 100r Oct 15 '10 at 19:39.

Described situation is called Phantom Read. So you need Serializable Isolation (SERIALIZABLE).

– Andrey Oct 15 '10 at 16:26 First I put this isolation level, and then few others, but in every case I got error saying that oher process that executes same select query is caught in deadlock, and became victim of deadlock and canceled. Seems like this thing with isolation levels isn't my solution or i'm doing something wrong.. – 100r Oct 15 '10 at 19:45.

You need to put a lock on the row: create the lock before you read the row and release the lock after you've updated the row. In Oracle and similar databases, reads do not lock, so you need to do the following (in a transaction): SELECT * FROM table WHERE id=? FOR UPDATE ... UPDATE table .... In MS SQL, I'm not really sure, the easiest way would be to try the following: Open two windows connected to the database, start a transaction in both, do the SELECT, and see if you can do the select from the second.

If the statement doesn't return, that means the row is locked and you're good. I assume, in your question, you mean to update the row after you've selected it, not insert it (inserting creates a new row, update changes an existing row).

– gbn Oct 15 '10 at 16:00 Firstly somebody on the internet might come across this page and be using Oracle so I thought I might as well include it; secondly it might be similar in SQL Server so it might be a pointer in the right direction. – Adrian Smith Oct 15 '10 at 16:14 No Oracle tag + the default locking strategy is quite different and your answer could actually be dangerous. – gbn Oct 15 '10 at 16:24.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.