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.
Best Answer
Short Answer
The update lock is sufficient, but you can achieve what you want more simply with:
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
andsp_releaseapplock
), but in all honesty using the built-in serializable isolation level is probably simplest.For more information, see my series of articles: