SQL Server : concurrency issue in stored procedure

Question

I am implementing sequence in SQL Server 2008 and I found this article form MSDN Blog

In the stored procedure usp_GetNewSeqVal, the author is getting the next sequence like this:

update AllSequences
set @NewSeqVal = CurrVal = CurrVal+Incr
where SeqName = @SeqName

This seems problematic to me, it seems like the lack of row lock will potentially create a concurrency issue.

But in the article he is stating:

Concurrency – the execution of extended procedure is not within the transaction scope of the DML statement, and it holds very short duration locks on the row that represents the sequence number.

Is there a concurrency issue here?

If so how to resolve it?

Answer 1

No, there is NO concurrency issue ! That's why it's such an elegant solution!

The UPDATE statement will take an Update (U) lock while reading the existing value - and the U lock is already exclusive in that a second update cannot proceed at this point. If a second connection also tries to update the same counter, it will have to wait until the first UPDATE statement is done in its entirety.

The lock is then upgraded to an exclusive (X) lock to write back the new value while at the same time storing it into the @NewSeqVal variable.

This use of a single, atomic UPDATE prevents any concurrency issues!