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

Asked 21/1, 2012 at 19:35 Answered 24/8, 2016 at 18:17

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?

Mccrae answered 21/1, 2012 at 19:35 Comment(1)

possible duplicate of sql server: Is this nesting in a transcation sufficient for getting a unique number from the database? – Veliavelick 21/1, 2012 at 19:51

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

Raleigh answered 24/8, 2016 at 18:17 Comment(0)

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

K answered 21/1, 2012 at 19:42 Comment(6)

or use "update CustomerNextOrderNumber set NextOrderNumber=NextOrderNumber+1 output inserted.NextOrderNumber where Customerid=?" – Mebane 21/1, 2012 at 19:48

@Joachim Isaksson : After thinking about it I like your approach more :) – K 21/1, 2012 at 20:8

Actually my suggestion was a single statement, not two :) – Mebane 21/1, 2012 at 20:16

Alexm, we receive a lot of deadlocks when stress testing with UPDLOCK. It seems like a good approach, though. What was very strange to us is that when we switched to TABLOCKX, the stress testing produced NO deadlocks (which made no sense) AND gave no duplicate IDs. Do you know why that would be? My reading suggests it does a hard lock on the whole table and should produce deadlocks. I agree with your approach that we should try to limit locking to just the applicable customer number, but the results were illogical during testing. – Mccrae 21/1, 2012 at 20:17

@Joachim Isaksson: Indeed I did not take your answer literally :) Will take a closer look at new SQL 2008 "OUTPUT" keyword . – K 21/1, 2012 at 20:32

@stackonfire: deadlocks usually happen when the sequence in which locks are acquired is not consistent. To address it one can place the critical code in a separate stored procedure. Another source of deadlocks is lock escalation - I sometimes use application locks to avoid this. – K 21/1, 2012 at 20:38

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.

Monopteros answered 21/1, 2012 at 19:46 Comment(2)

Andomar, thanks for the reply. During stress testing, we received far too many deadlocks when testing SERIALIZABLE. That is why we tried TABLOCKX. We did not test with HOLDLOCK. We will indeed have an index on CustomerID but know that we will have >10 orders per second when inputting batches of orders in batch processing that uses multiple threads and some will be with the same CustomerID. – Mccrae 21/1, 2012 at 20:4

Well tablockx without holdlock opens the door for deadlocks: the table lock will be released between the select and the update. So try tests with holdlock. You can also test with the updlock query hint, although tablockx should already cover that territory. – Monopteros 21/1, 2012 at 20:8

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.

Gaygaya answered 21/1, 2012 at 19:53 Comment(2)

Thank you. Can you explain why you need your first query (select ID from Customer with (rowlock) where Customer.ID=@ID) at all? The locking concern is on the Orders table and even that SELECT statement limits to just the one customer ID. – Mccrae 21/1, 2012 at 20:9

@stackonfire, I add some more explanation, let me know if it is still not clear. – Gaygaya 21/1, 2012 at 20:15

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.

Lemmon answered 21/1, 2012 at 19:37 Comment(1)

Antony, I can't create separate IDENTITY fields for each customer because the number of customers is large and always increasing. – Mccrae 21/1, 2012 at 20:9

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.

Fiedling answered 21/1, 2012 at 19:54 Comment(2)

Tony, thanks for the reply. We won't delete earlier order numbers. I agree that this situation is not ideal, versus just using identity or other sql IDs, but the business logic requires this methodology. The actual details are more complicated than what I have posted, of course. The reasons are actually good ones. And the customers have to refer to their unique order IDs by number, so I can just count them. Anyway, the question is still very interesting in theory regardless of whether I can debate the business logic. – Mccrae 21/1, 2012 at 20:13

No it doesn't require it, separation of concerns time. You are making your database layer hideously complex, fragile and expensive simply so the customer can refer to an order by it's key. Surrogates is the way to go and if scaling is an issue then use a Guid, its what they are for. The only thing interesting about teh question is how often people fall into this trap. – Fiedling 21/1, 2012 at 20:34

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

Roop answered 21/1, 2012 at 20:8 Comment(0)

Hot tags

Godot Unity Godot Help Programming Godot 4.X GUI GDScript 3D 2D Physics CSharp Godot 3.X VR XR Projects C++

Recommended topics

Hot tags