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

Question

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 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