I have a SP for an e-commerce site that creates an order doing the following fashion:
-Fill out some variables
-Create Order number
-While loop select on order table and recreate if ordernumber already exists
-Insert into order table
*End Critical Section
-Insert into order lines table, address table, etc.
The problem is the Create Order number and While loop used to be at the top and occasionally I would get duplicate order numbers if two submitted at the same time. I rearranged it to the above and it happens less now.
My question is if there is a way to put an XLOCK on the orders table only during the critical section lines. Also my understanding is that once the insert into orders is done that the server will hold a lock until the commit in case of rollback.? I don't want to XLOCK the whole SP if I don't have to. It is quick though.
How difficult would it be to use a guid instead of an order number for your primary key? Once you got the row added, you could monkey with the order number in a much less time-critical fashion (if it still mattered).