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
SELECT @NextOrderNumber = NextOrderNumber
FROM tblOrderNumber (UPDLOCK)
SET NextOrderNumber = NextOrderNumber + 1
--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.