Avoiding Concurrency Problems With Max+1 Integer In Sql Server 2008... Making Own Identity Value

December 06, 2023 Post a Comment

I need to increment an integer in a SQL Server 2008 column. Sounds like I should use an IDENTITY column, but I need to increment separate counters for each of my customers. Think

Solution 1:

In SQL Server 2005 and later, this is best done atomically, without using any transactions or locking:

update ORDERS 
set OrderNumber=OrderNumber+1 
output inserted.OrderNumber where CustomerID=@ID

Solution 2:

I would introduce a table to keep last number per customer to query and update it in the same transaction with order generation.

TABLE CustomerNextOrderNumber
{
    CustomerID id PRIMARY KEY,
    NextOrderNumber int
}

Update lock on select will help to avoid race condition when two orders are placed concurrently by the same customer.

BEGIN TRANSACTION

DECLARE@NextOrderNumberINTSELECT@NextOrderNumber= NextOrderNumber
FROM  CustomerNextOrderNumber (UPDLOCK)
WHERE CustomerID =@CustomerIDUPDATE CustomerNextOrderNumber
SET   NextOrderNumber = NextOrderNumber +1WHERE CustomerID =@CustomerID


... use number here


COMMIT

Similar, but more straightforward approach (inspired by Joachim Isaksson) update lock here is imposed by the first update.

BEGIN TRANSACTION

DECLARE@NextOrderNumberINTUPDATE CustomerNextOrderNumber
SET   NextOrderNumber = NextOrderNumber +1WHERE CustomerID =@CustomerIDSELECT@NextOrderNumber= NextOrderNumber
FROM CustomerNextOrderNUmber
where CustomerID =@CustomerID

...

COMMIT

Solution 3:

The default transaction level, read committed, does not protect you against phantom reads. A phantom read is when another process inserts a row in between your select and insert:

BEGIN TRANSACTION
SELECT@NewOrderNumber=MAX(OrderNumber)+1From Orders where CustomerID=@IDINSERTINTO ORDERS VALUES (@NewOrderNumber, other order columns here)
COMMIT TRANSACTION

Even one level higher, repeatable read, doesn't protect you. Only the highest isolation level, serializable, protects against phantom reads.

So one solution is the highest isolation level:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION
...

Another solution is to use the tablockx, holdlock and updlock table hints to make sure only your transaction can modify the table. The first locks the table, the second keeps the lock until the end of the transaction, and the third grabs an update lock for the select, so it doesn't have to upgraded later.

SELECT@NewOrderNumber=MAX(OrderNumber)+1From Orders with (tablockx, holdlock, updlock)
where CustomerID=@ID

These queries will be quick if you have an index on CustomerID, so I wouldn't worry too much about concurrency, certainly not if you have less than 10 orders per minute.

Solution 4:

You could do this:

BEGIN TRANSACTION
  SELECT ID
  FROM Customer WITH(ROWLOCK)
  WHERE Customer.ID =@IDSELECT@NewOrderNumber=MAX(OrderNumber)+1From Orders where CustomerID=@IDINSERTINTO ORDERS VALUES (@NewOrderNumber, other order columns here)
COMMIT TRANSACTION

We are now only locking one Customer from the customers table and not all customers, whenever 2 people try to add an order for the same customer at the same time, whoever gets the lock on the customer first wins and the other person will have to wait.

If people are inserting orders for different customers, they won't get in each others way!

Here is how this would work:

User1 start to insert an order for Customer with ID 1000.
User2 tries to insert an order for Customer with ID 1000.
User2 have to wait until User1 finish inserting the order.
User1 insert the order and the transaction is committed.
User2 can now insert the order and is guaranteed to get the true max orderId for customer 1000.

Solution 5:

would it be possible to create a table with an IDENTITY field in for each customer, then you could insert a new record in to the customer's table and pull the value from that.

Lab Sql