Need to prevent race condition during select and update
Posted on 2001-08-07
I am using SQL Server 6.5 and I have a stored procedure that creates a sales number by incrementing the last one used.
I store that information in a table called LastSalesNumber. This function worked fine for over a
year but I am now having problems with an increase in traffic and users. I have had at least two occasions where it appears that two users made sales at the very same time and two different sales records were created with the same number. What changes should I make to the following code in the form of locking or restructuring to ensure that this doesn't happen anymore?
-- begin snippet
/* Assign Sales number to be last sales number assigned + 1 */
select @SalesNumber = "SLS-" + convert(varchar(13), LastNumber + 1)
/* increment the last Sales number assigned */
set LastNumber = LastNumber + 1
-- end snippet