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

Answer 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 @NextOrderNumber INT

SELECT @NextOrderNumber = NextOrderNumber
FROM  CustomerNextOrderNumber (UPDLOCK)
WHERE CustomerID = @CustomerID

UPDATE CustomerNextOrderNumber
SET   NextOrderNumber = NextOrderNumber + 1
WHERE 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 @NextOrderNumber INT

UPDATE CustomerNextOrderNumber
SET   NextOrderNumber = NextOrderNumber + 1
WHERE CustomerID = @CustomerID

SELECT @NextOrderNumber = NextOrderNumber
FROM CustomerNextOrderNUmber
where CustomerID = @CustomerID

...

COMMIT

Answer 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)+1 From Orders where CustomerID=@ID
INSERT INTO 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)+1 
From 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.

Answer 4

You could do this:

BEGIN TRANSACTION
  SELECT ID
  FROM Customer WITH(ROWLOCK)
  WHERE Customer.ID = @ID

  SELECT @NewOrderNumber = MAX(OrderNumber)+1 From Orders where CustomerID=@ID
  INSERT INTO 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.

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

Answer 6

You are trying to relate two completely different requirements.

Even if you got this working. What happens if Customer A has an ealier order deleted, are you going to renumber the all their existing records to keep them consecutive and starting from 1. Now that would be a locking a problem....

Give the record an identity (or possibly a guid) When you want a count, query for it, if you want row number (never seen the point of that myself), use rowno.

You do not need a an auto increementing order per customer, you don't want one, and without a massive amount of locking can't have one.

Lateral thinking time.

If you present

Order Description Date Due
1     Staples     26/1/2012
2     Stapler     1/3/2012
3     Paper Clips 19/1/2012

it doesn't mean (and in fact shouldn't mean) that the order keys are 1, 2 and 3, they can be anything as long as they fulfill a uniqueness requirement.

Answer 7

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