5

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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
100r
  • 1,099
  • 1
  • 12
  • 24

2 Answers2

5

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

gbn
  • 422,506
  • 82
  • 585
  • 676
  • 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
  • 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
-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)

Adrian Smith
  • 17,236
  • 11
  • 71
  • 93
  • useless answer, because basic isolation level in MS SQL is looser then one in Oracle – Andrey Oct 15 '10 at 15:59
  • -1 Why did you answer for Oracle when it's SQL Server, and then mention you don't know about it either? – 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