I have these two very simple tables:
Orders
OrderId autoincrement
CustomerOrderId Int
...
Customers
CustomerId AutoIncrement
...
OrderNumber Int
The requirement is to have for each customer an index that will store their own unique order counter (no need to judge the design, I'm only trying to help fixing a bug with minimum impact on their production code). The current locking in use is TABLOCKX for Customers, and once in a blue moon the OrderNumber does not get incremented. Here is the function to retrieve the next index:
Set Transaction Isolation Level Serializable
BeginTran
SELECT @newOrder = OrderNumber FROM Customers WITH (TABLOCKX) WHERE CustomerId = @CID
SET @newOrder = @newOrder +1
UPDATE Customers Set OrderNumber = @newOrder
WHERE CustomerId = @CID
CommitTran
--return @newOrder
I've tried to change it to one statement only
UPDATE Customers SET @newOrder = OrderNumber + 1, OrderNumber = OrderNumber+1 WHERE CustomerId = @CID
but need to wait for the alignment of those planets to verify if that is correct or not. In the meantime I appreciate any feedback with a better solution to this function. Thank you.