Avoiding concurrency problems with MAX+1 integer in SQL Server 2008... making own IDENTITY value

sql locking identity max increment
stackonfire picture stackonfire · Jan 21, 2012 · Viewed 16.4k times · Source

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 of an e-commerce site where each customer gets their own incrementing order number, starting with 1. The values must be unique (per customer).

For example,

Customer1  (Order #s 1,2,3,4,5...)
Customer2  (Order #s 1,2,3,4,5...)

Essentially, I will need to manually do the work of SQL's identity function since the number of customers is unlimited and I need order # counters for each of them.

I am quite comfortable doing:

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

My problem is locking and concurrency concerns and assuring a unique value. It seems we need to lock with TABLOCKX. But this is a high volume database and I can't just lock the whole Orders table every time I need to do a SELECT MAX+1 process and insert a new order record.

But, if I don't lock the whole table, then I might not get a unique value for that customer. Because some of our order entry is done after-the-fact in batches by a multi-threaded Windows process, it is possible that 2 operations will be simultaneously wanting to insert a new order for the same customer.

So what locking methodology or technique will avoid deadlocks and still let me maintain unique incrementing order numbers PER customer?

Answer

J.T. Taylor picture J.T. Taylor · Aug 24, 2016

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

Related questions

Effect of NOLOCK hint in SELECT statements

I guess the real question is: If I don't care about dirty reads, will adding the with (NOLOCK) hint to a SELECT statement affect the performance of: the current SELECT statement other transactions against the given table Example: Select * from …

Read More
How to find out what is locking my tables?

I have a SQL table that all of a sudden cannot return data unless I include with (nolock) on the end, which indicates some kind of lock left on my table. I've experimented a bit with sys.dm_tran_locks …

Read More
SQL Server - How to lock a table until a stored procedure finishes

I want to do this: create procedure A as lock table a -- do some stuff unrelated to a to prepare to update a -- update a unlock table a return table b Is something like that possible? Ultimately I …

Read More