Correct way to generate order numbers in SQL Server

Question

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?

EDIT

Just to make few things clearer:

• Performance in this particular corner of the app is absolutely not an issue: orders will be placed relatively infrequently and will involve an expensive call to vendors' web service, so 1-second delay is pretty tolerable
• We really need to have each vendors' order numbers to be independent and sequential

Answer 1

You could use an OUTPUT clause. This should do it all 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 examples above assume that the VendorID column has a unique constraint, or at the very least that there'll only be one row per vendor ID. If that's not the case then you'll potentially be updating and/or returning multiple rows, which doesn't seem like a good idea!

Answer 2

Your solution will create a potential performance bottleneck on OrderNumberInfo table.

Is there any specific reason why the orders can't simply be an identity column, possibly prefixed with a vendor ID on application side (e.g. MSFT-232323)?

The only drawback of this approach is that per-vendor orders will not be an "Add-1-to-get-next-order-#" pattern, but I'm not aware of any technical or business consideration of why that would present a problem, though it might make in-sequence order processing slightly more complicated.

They'd still be incremented and unique per-vendor which is the only real requirement for an order ID.

It will, of course have the added side benefit of very easy vendor-independent logic assuming you ever have any) - such as application-wide QC/reporting.

Answer 3

I normally use something like this:

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

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

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

If your table design is such that the vendor row needs to be created on demand if it doesn't exist, then use this logic instead:

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 doesn't require a transaction, because each atomic statement will succeed regardless of how many other connections are executing the code simultaneously.

Disclaimer: I have used this without problems on SQL Server 7-2005. I cannot yet comment on its behaviour in 2008.