CODEWITHSUNDEEP
sql-server-2008
Graham
Graham

Reputation: 8151

How to deal with two transactions updating the same row in SQL Server

I have the following situation that I wish to deal with:

A table of values:

id int
val varchar(20)
used bit
flag int

I want to find the first row WHERE used = 0 AND flag IS NULL and stick something in 'flag'. Once that is done any other user will not be able to use that row (because flag is not null)

That's simple enough to do of course:

UPDATE top (1) mytable
SET flag = someUniqueValue
WHERE used = 0
  AND flag IS NULL

What I want to know is what happens if two users are running the same UPDATE at the same time. Obviously one will get there first.

I don't know how to go about testing this scenario, so can't find out myself.

Does the second user over-ride the first? (straight away or after the lock is released?)

Does the second user get locked out and get an error? (If so how do I go about detecting the error?)

Any help would be appreciated.

Upvotes: 2

Views: 2310

Answers (2)

Graham
Graham

Reputation: 8151

OK, after a bit of research I have found my own answer.

It seems I have to lock the table, do my update, and then release the table lock. The following sql does all of that in one go:

UPDATE top (1) mytable WITH (TABLOCKX)
SET flag = someUniqueValue
WHERE used = 0
  AND flag IS NULL

To test it, I ran two loops (of 10000 cycles each - a bit over the top but did the trick). The first loop stuck one value in, the second another value. The end result showed that there were exactly 10000 of each value in the table when both loops finished running.

Upvotes: 1

Tirumudi
Tirumudi

Reputation: 443

The second user will over-ride the first. It wont get locked unless enclosed in transaction. Check this link....

Upvotes: 1

Related Questions