Solved

Need to prevent race condition during select and update

Posted on 2001-08-07
6
616 Views
Last Modified: 2012-06-27
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)
     from LastSalesNumber


/*  increment the last Sales number assigned  */

update LastSalesNumber
     set LastNumber = LastNumber + 1

-- end snippet
0
Comment
Question by:geowilli
6 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 6362123
Since there is no implict row locking in SQL Server 6.5 you may want to try reversing the statements:

In other words, update first
update LastSalesNumber
    set LastNumber = LastNumber + 1

And then Select:
select @SalesNumber = "SLS-" + convert(varchar(13), LastNumber)
    from LastSalesNumber

Here the problem may be the reverse: "gaps" in your sales numbers.
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 50 total points
ID: 6362684
The problem is atomicity, which can be solved using transactions:

begin transaction
/* Assign Sales number to be last sales number assigned + 1  */

select @SalesNumber = "SLS-" + convert(varchar(13), LastNumber + 1)
    from LastSalesNumber


/*  increment the last Sales number assigned  */

update LastSalesNumber
    set LastNumber = LastNumber + 1

commit transaction


This will make that if a first user is inside this transaction, any other user will have to wait until that one has finished. Using your code, they can immediately go on...

Cheers
0
 
LVL 18

Expert Comment

by:nigelrivett
ID: 6362843
Unfortunately with this code there is nothing to stop two spids getting the same sales number as

select @SalesNumber = "SLS-" + convert(varchar(13), LastNumber + 1)
   from LastSalesNumber

will take a shared lock which is compatible with another shared lock and the lock will be released immediately.
Code that will work is

update LastSalesNumber
set LastNumber = LastNumber + 1 ,
@SalesNumber = "SLS-" + convert(varchar(13), LastNumber + 1)

or
begin tran
update LastSalesNumber
   set LastNumber = LastNumber + 1

select @SalesNumber = "SLS-" + convert(varchar(13), LastNumber)
from LastSalesNumber
commit tran

commit transaction
0
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
LVL 18

Expert Comment

by:nigelrivett
ID: 6362847
update LastSalesNumber
   set LastNumber = LastNumber + 1

select @SalesNumber = "SLS-" + convert(varchar(13), LastNumber)
   from LastSalesNumber

This doesn't work either without the transaction as two spids could do the update before both getting the same number.
0
 
LVL 1

Author Comment

by:geowilli
ID: 6364721
Thanks for the answer and brief explanation
0
 
LVL 18

Expert Comment

by:nigelrivett
ID: 6364830
um.
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL server performance baseline 19 45
How to handle time out inside the stored procedure 10 24
SQL convert varchar to INT 17 29
Copy data to New Year 9 29
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question