The right way to create order numbers in SQL Server

Question

This question, of course, refers to a much wider scope, but here it is.

I have a basic e-commerce application where users can naturally place orders. The written orders must have a unique number, which I am trying to create right now.

Each order depends on the supplier. Basically, I have a table OrderNumberInfo (VendorID, OrderNumber) . Now, when the customer submits the order, I need to increase the OrderNumber for a particular supplier and return this value. Naturally, I do not want other processes to interfere with me, so I need to somehow block this line:

 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 read about select ... with (updlock rowlock) , pessimistic locking, etc., but I just can’t fit all this into a connected picture:

• How do these prompts reproduce using SQL Server 2008 snapshot?
• Do they perform locks at the row, page, or even table level?
• How does this allow multiple users trying to generate numbers for a single provider?
• What insulation levels are suitable here?
• And anyway - what is the way to do such things?

EDIT

Just to make a few understandable:

• Performance in this particular corner of the application is absolutely not a problem: orders will be placed relatively rarely and will be associated with an expensive call to the supplier’s web service, so the 1-second delay is quite portable.
• We really need each supplier’s order numbers to be independent and consistent.

Answer 1

Your solution will create a potential performance bottleneck in the OrderNumberInfo table.

Is there any specific reason why orders cannot be just an identification column, possibly with a prefix of the vendor identifier on the application side (for example, MSFT-232323)?

The only drawback of this approach is that the orders for each supplier will not be the "Add-1-to-get-next-order- #" template, but I don’t know either technical or business considerations about why what is the problem, although this can make processing order in a sequence a bit more complicated.

They will still grow and be unique for each supplier, which is the only real requirement for the order identifier.

Of course, it will have the added benefit of a very simple vendor-independent logic if you ever have one), such as QC / reporting across the entire application.

Answer 2

You can use OUTPUT . This should do all this atomically without requiring a transaction.

 -- either return the order number directly as a single column resultset UPDATE OrderNumberInfo SET OrderNumber = OrderNumber + 1 OUTPUT DELETED.OrderNumber WHERE VendorID = @vendorID -- or use an intermediate table variable to get the order number into @n DECLARE @n INT DECLARE @temp TABLE ( OrderNumber INT ) UPDATE OrderNumberInfo SET OrderNumber = OrderNumber + 1 OUTPUT DELETED.OrderNumber INTO @temp ( OrderNumber ) WHERE VendorID = @vendorID SET @n = (SELECT TOP 1 OrderNumber FROM @temp) 

The above examples assume that the VendorID column has a unique constraint, or at least there will be only one row for the vendor identifier. If this is not the case, then you will potentially update and / or return multiple rows, which does not seem like a good idea!

Answer 3

I usually use something like this:

 update OrderNumberInfo with (rowlock) set @OrderNumber = OrderNumber, OrderNumber = OrderNumber + 1 where VendorID = @VendorID 

It should not be wrapped in a transaction. In fact, if you complete the transaction, then SQL Server will start holding locks on the table and slow down everything. When I need to do such things in a web service, I always execute it on a separate database connection outside of any transaction that might be open at that time, just to make sure.

I believe (but have not proven) that SQL Server uses a latch, not a transaction, to make it atomic, which should be more efficient.

If your table design is such that the provider row should be created on demand, if it does not exist, then use this logic:

 declare @error int, @rowcount int -- Attempt to read and update the number. update OrderNumberInfo with (rowlock) set @OrderNumber = OrderNumber, OrderNumber = OrderNumber + 1 where VendorID = @VendorID select @error = @@error, @rowcount = @@rowcount if @error <> 0 begin return @error end -- If the update succeeded then exit now. if @rowcount > 0 begin return 0 end -- Insert the row if it doesn't exist yet. insert into OrderNumberInfo (VendorID, OrderNumber) select VendorID, 1 where not exists (select null from OrderNumberInfo where VendorID = @VendorID) select @error = @@error if @error <> 0 begin return @error end -- Attempt to read and update the number. update OrderNumberInfo with (rowlock) set @OrderNumber = OrderNumber, OrderNumber = OrderNumber + 1 where VendorID = @VendorID select @error = @@error if @error <> 0 begin return @error end 

This code still does not require a transaction, because each atomic operator will work regardless of how many other connections the code executes at the same time.

Disclaimer: I used this without problems on SQL Server 7-2005. I still can not comment on his behavior in 2008.

Answer 4

The way to do this is to ensure consistency:

 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE BEGIN TRANSACTION 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 

This will use the most severe form of isolation and will not provide a funny business.

Answer 5

here he is:

declare @C int = 0; update Table set Code = @C, @C = @C + 1