Does UPDLOCK guarantee concurrency

Question

I have a table called tblOrderNumber which has 1 row and 1 column. This table stores what the next order number will be for my ecommerce website. It is ABSOLUTELY VITAL that the same order number is not used more than once. Currently the team are using this stored procedure and it seems to work fine:

My question is does UPDLOCK guarantee this? I would have thought a Read Lock is required on the SELECT too (in the unlikely case that 2 orders are placed within a millisecond of each other and the first one has not done the UPDATE before the second one had done a SELECT, as I understand there is no read lock in this procedure)?

DECLARE @NextOrderNumber INT

BEGIN TRANSACTION

SELECT @NextOrderNumber = NextOrderNumber
FROM  tblOrderNumber (UPDLOCK)

UPDATE tblOrderNumber
SET   NextOrderNumber = NextOrderNumber + 1

COMMIT

SELECT @NextOrderNumber

--Customer Implementation (Would UPDLOCK OR SERIALIZABLE be better here as I don't think we need to lock the full table?)

UPDATE dbo.tblOrderNumber WITH (SERIALIZABLE) 
SET @NextOrderNumber = NextOrderNumber, 
    NextOrderNumber = NextOrderNumber + 1; 
WHERE CustomerId=@CustomerId

I am using SQL Server 2014 but I will be changing to SQL Azure soon.

Answer 1

Short Answer

The update lock is sufficient, but you can achieve what you want more simply with:

UPDATE dbo.tblOrderNumber WITH (SERIALIZABLE)
SET @NextOrderNumber = NextOrderNumber,
    NextOrderNumber = NextOrderNumber + 1
WHERE CustomerID = @CustomerID;

The WITH (SERIALIZABLE) hint is not strictly required if there is a unique index on CustomerID.

Longer Answer

The update lock hint is sufficient in the code provided. Only one transaction can obtain an update (U) lock on a resource at a time, and update locks are held to the end of the transaction. The update lock is converted to an exclusive (X) lock just before the change is made. The exclusive lock is also held to the end of the transaction.

Taking an update lock when reading therefore provides the concurrency guarantees you are looking for. To be clear: Once the update lock is acquired (by the select), no other transaction can acquire an update lock on the same resource until the first transaction has committed or aborted.

You could also run the transaction (or single update statement) at the SERIALIZABLE isolation level without hints. After all, the guarantee you are looking for is that the transaction should execute according to a serializable schedule. If you are uncomfortable relying on knowledge of locking internals, specifying the desired isolation level, and letting SQL Server handle the details, is probably simpler.

Obviously you would need code to handle errors or possible deadlocks as well. I assume you omitted this from your example. A belt-and-braces approach would also SET XACT_ABORT ON for the procedure to ensure almost all possible errors will abort the transaction instead of silently continuing.

It would also be possible to write a robust locking implementation manually using application locks (with sp_getapplock and sp_releaseapplock), but in all honesty using the built-in serializable isolation level is probably simplest.

For more information, see my series of articles:

• SQL Server Isolation Levels