How do I lock a record in a MS SQL Table using a stored procedure
Posted on 2006-06-17
I have a VB6 application that uses MS SQL Server to store data. In the app, I produce work orders each with a unique WO#. Currently the way I keep track of the next available number is through the use of a table that just has one record and one field called Next_Available_No.
Since there will be potentially dozens of users updating this table at the same time, I want to find a way (using a stored procedure) to read the next available number and increment the table without another user grabbing the number before it has been incremented.
So the question is this - how do I read the next available number and update that table's number by 1 without another user doing the same thing between the time the stored procedure reads the number and updates it?
Thanks for the help.