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 queries to read that first original row from query, until I finish the read and insert. After that..go ahead.

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

OleDbTransaction is in play because I use SQL Server 6.5 (oh yes you read it right, don't ask why :)

I was digging through isolation levels description but can't quite understand them and find solution for my problem, so my question is what isolation level to use for OleDbTransaction?

Hope I was clear :)

Thanks.

Answer 1

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