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

Question

I have situation like this.

Query is like this.

Select * from TABLE where ID = 1

(what a query :)

after that I change stuff in that row and INSERT it with new id.

I want to prevent other qeueries to read that first original row from query, untill I finish the read and insert. After that..go ahead.

Basicly I want select and insert to be in transaction, whit isolation level that will prevent reading only from that row until inserting is finished.

OlebdTransaction is in play because I use SQL server 6.5(oh yes you read it right, don't ask why :) I was digging through issolation levels description but can't quite understand them and find solution for my problem, so my question is what issolation level to use fo OleDbTransaction?

Hope I was clear :)

thanks.

Answer 1

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)

Answer 2

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

Answer 3

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 :-)