Avoiding concurrency problems with MAX+1 integer i

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

create table TestIds
(customerId int,
nextId int)

insert into TestIds
values(1,1)
insert into TestIds
values(2,1)
insert into TestIds
values(3,1)

go

create proc getNextId(@CustomerId int)
as

declare @NextId int

while (@@ROWCOUNT = 0)
begin
    select @NextId = nextId
    from TestIds
    where customerId = @CustomerId

    update TestIds
    set nextId = nextId + 1
    where customerId = @CustomerId
    and nextId = @NextId

end

select @NextId  
go