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?