CODEWITHSUNDEEP
c#.netsql-servertransactionsisolation-level
100r
100r

Reputation: 1104

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

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.

Upvotes: 5

Views: 1023

Answers (2)

gbn
gbn

Reputation: 432180

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

Upvotes: 5

Adrian Smith
Adrian Smith

Reputation: 17553

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)

Upvotes: -1

Related Questions