Correct way to generate order numbers in SQL Server
- by Anton Gogolev
This question certainly applies to a much broader scope, but here it is.
I have a basic ecommerce app, where users can, naturally enough, place orders. Said orders need to have a unique number, which I'm trying to generate right now.
Each order is Vendor-specific. Basically, I have an OrderNumberInfo (VendorID, OrderNumber) table. Now whenever a customer places an order I need to increment OrderNumber for a particuar Vendor and return that value. Naturally, I don't want other processes to interfere with me, so I need to exclusively lock this row somehow:
begin tranaction
declare @n int
select @n = OrderNumber
from OrderNumberInfo
where VendorID = @vendorID
update OrderNumberInfo
set OrderNumber = @n + 1
where OrderNumber = @n and VendorID = @vendorID
commit transaction
Now, I've read about select ... with (updlock rowlock), pessimistic locking, etc., but just cannot fit all this in a coherent picture:
How do these hints play with SQL Server 2008s' snapshot isolation?
Do they perform row-level, page-level or even table-level locks?
How does this tolerate multiple users trying to generate numbers for a single Vendor?
What isolation levels are appropriate here?
And generally - what is the way to do such things?
