Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Let's say you have the following code (please ignore that it's awful):

BEGIN TRAN;
DECLARE @id int
SELECT @id = id + 1 FROM TableA;
UPDATE TableA SET id = @id; --TableA must have only one row, apparently!
COMMIT TRAN;
-- @id is returned to the client or used somewhere else

To my eye, this is NOT managing concurrency properly. Just because you have a transaction doesn't mean someone else won't read the same value that you did before you get to your update statement.

Now, leaving the code as is (I realize this is better handled as a single statement or even better using an autoincrement/identity column) what are sure ways to make it handle concurrency properly and prevent race conditions that allow two clients to get the same id value?

I'm pretty sure that adding a WITH (UPDLOCK, HOLDLOCK) to the SELECT will do the trick. The SERIALIZABLE transaction isolation level would seems to work as well since it denies anyone else to read what you did until the tran is over (UPDATE: this is false. See Martin's answer). Is that true? Will they both work equally well? Is one preferred over the other?

Imagine doing something more legitimate than an ID update--some calculation based on a read that you need to update. There could be many tables involved, some of which you'll write to and others you won't. What is the best practice here?

Having written this question, I think the lock hints are better because then you are only locking the tables you need, but I'd appreciate anyone's input.

P.S. And no, I don't know the best answer and really do want to get a better understanding! :)

share|improve this question
    
Just for clarification: do you want to prevent 2 clients from reading the same value or from issuing update that may be based on obsolete data? If latter, you can use rowversion column to check if the row to be updated hasn't been changed since it was read. –  a1ex07 Feb 12 '12 at 3:13
    
We don't want a second client getting the old id value before it is updated to the new value by the first client. It should block. –  ErikE Feb 12 '12 at 5:23
1  
Someone has to add the single-statement solution, so it might as well be me :) ... DECLARE @id integer; UPDATE dbo.TableA WITH (TABLOCKX) SET @id = id += 1; ... or ... UPDATE dbo.TableA WITH (TABLOCKX) SET id += 1 OUTPUT INSERTED.id; –  Paul White Feb 13 '12 at 17:05
    
Yes of course @SQLKiwi!! Those are they. –  ErikE Feb 13 '12 at 18:33
add comment

3 Answers

up vote 9 down vote accepted

Just addressing the SERIALIZABLE isolation level aspect. Yes this will work but with deadlock risk.

Two transactions will both be able to read the row concurrently. They will not block each other as they will either take an object S lock or index RangeS-S locks dependant on table structure and these locks are compatible. But they will block each other when attempting to acquire the locks needed for the update (object IX lock or index RangeS-U respectively) which will lead to deadlock.

The use of an explicit UPDLOCK hint instead will serialize the reads thus avoiding the deadlock risk.

share|improve this answer
    
Thank you Martin. You prompted me to read more carefully, and I saw that SERIALIZABLE does not prevent reads, just modification of read rows, so exactly so: deadlock is not prevented. It looks like it does the same as HOLDLOCK though. –  ErikE Feb 12 '12 at 20:09
    
+1 but: for heap tables you can still get a conversion deadlock even with update locks: sqlblog.com/blogs/alexander_kuznetsov/archive/2009/03/11/… –  AlexKuznetsov Feb 13 '12 at 2:05
    
Bizarre, @alex. I imagine it has to do with a race condition of the engine trying to find what to lock before actually UPDLOCKing it... –  ErikE Feb 13 '12 at 5:31
1  
UPDLOCK only works to serialize reads if the same access method is chosen by the two queries. If the queries take U locks on different indexes (in the broadest sense, to include RIDs) they will not block at the SELECT, but a conversion deadlock will occur later. –  Paul White Feb 13 '12 at 16:11
    
@SQLKiwi - Agreed but the scenario in the OP is about concurrent invocations of the same query on a single row table. Can you envisage any possibility of UPDLOCK not serializing the reads in this case? Presumably the SELECT will just get a trivial plan and not change unless an index on id is either added or removed. –  Martin Smith Feb 13 '12 at 16:41
show 4 more comments

I think the best approach for you would be to actually expose your module to high concurrency and see for yourself. Sometimes UPDLOCK alone is enough, and there is no need for HOLDLOCK. Sometimes sp_getapplock works out very well. I would not make any blanket statement here - sometimes adding one more index, trigger, or indexed view changes the outcome. We need to stress test code and see for ourselves on case by case basis.

I have written several examples of stress testing here

Edit: for better knowledge of the internals, you can read Kalen Delaney's books. However, books may get out of sync just like any other documentation. Besides, there are too many combinations to consider: six isolation levels, many types of locks, clustered/nonclustered indexes and who knows what else. That is a lot of combinations. On top of that, SQL Server is closed source, so we cannot download source code, debug it and such - that would be the ultimate source of knowledge. Anything else may be incomplete or outdated after the next release or service pack.

So, you should not decide what works for your system without your own stress testing. Whatever you have read, it can help you in understanding what is going on, but you have to prove that the advice you have read works for you. I don't think anybody can do it for you.

share|improve this answer
    
I'm not really comfortable with the "try it and see" method. Of course testing is important but it seems to me that knowledge is better than "trying stuff". –  ErikE Feb 12 '12 at 2:16
3  
+1 I'd tend to consider semaphores (sp_getapplock) rather than lock hints if I needed this concurrency pattern to be safe –  gbn Feb 13 '12 at 7:41
    
+1 for this answer. @gbn: I tried this, including putting transactions in the sproc (which is usually handled by our C#-layer in a fairly complex distributed transaction setup) but now I get problems with "Operation not valid for the state of the transaction." –  user5094 Mar 7 '12 at 11:09
add comment

In this particular case the addition of a UPDLOCK lock to the SELECT would indeed prevent anomalies. The addition of HOLDLOCK isn't necessary as an update lock is held for the duration of the transaction, but I confess to including it myself as a (possibly bad) habit in the past.

Imagine doing something more legitimate than an ID update, some calculation based on a read that you need to update. There could be many tables involved, some of which you'll write to and others you won't. What is the best practice here?

There is no best practice. Your choice of concurrency control must be based on the application's requirements. Some applications/transactions need to execute as if they had exclusive ownership of the database, avoiding anomalies and inaccuracy at all costs. Other applications/transactions can tolerate some degree of interference from each other.

  • Retrieving a banded stock level (<5, 10+, 50+, 100+) for a product in a web store = dirty read (inaccurate doesn't matter).
  • Checking and reducing stock level on that web store checkout = repeatable read (we MUST have the stock before we sell, we MUST not end up with a negative stock level).
  • Moving cash between my current and savings account at the bank = serializable (don't miscalculate or misplace my cash!).

Edit: @AlexKuznetsov's comment prompted my re-reading the question and removing the very obvious error in my answer. Note to self on late night posting.

share|improve this answer
    
I suppose that makes sense. UPDLOCK as a hint wouldn't be very useful if it didn't work that way... You wouldn't use it in a an UPDATE statement so of course. –  ErikE Feb 13 '12 at 5:19
    
HOLDLOCK is useful in a select-then-update-or-insert scenario. If the row doesn't exist, HOLDLOCK locks it (more precisely a range that covers the missing value) so the row continues to fail to exist for the length of the transaction, thus guarding against e.g. unique key violations on a later INSERT. –  Paul White Feb 13 '12 at 15:53
    
@SQLKiwi That probably warrants expanding on in a separate answer. +1 from me for starters when it appears. –  Mark Storey-Smith Feb 13 '12 at 16:15
    
Hi @MarkStorey-Smith, it is well explained here: weblogs.sqlteam.com/dang/archive/2007/10/28/… and here: weblogs.sqlteam.com/dang/archive/2009/01/31/… –  Paul White Feb 13 '12 at 16:38
    
Wow. Concurrency is HARD. I would really like to do a comprehensive study of the whole subject. –  ErikE Feb 13 '12 at 18:37
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.