We help IT Professionals succeed at work.

SET TRANSACTION ISOLATION LEVEL

jackbenson
jackbenson used Ask the Experts™
on
Hi,

i have created a database for my companies stock system in SQL Server 2005.

when there are stock movements (i.e. we despatch or receive stock) we need to adjust the stock record for the product being despatched / recieved.

i need to make sure that this stored procedure is not run more than once at the same time.

would using the following be sensible?


SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
      
BEGIN TRANSACTION;

COMMIT TRANSACTION;


Further to this.. do you HAVE to have all the code in a stored procedure within the BEGIN TRANSACTION.... COMMIT TRANSACTION? or just the section of SQL that related to updating the stock record.

thanks

jack
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Billing Engineer
Most Valuable Expert 2014
Top Expert 2009
Commented:
>i need to make sure that this stored procedure is not run more than once at the same time.

with transactions, you cannot do that.
a transaction will only ensure that the second call will eventually have to wait until the first call has finished, but to make it clear:
it's not the BEGIN TRANSACTION that will "stop/wait" the call, but inside the transaction, the call to table data that will "lock" others.

if you want to make sure that the procedure waits, you have to create a "semaphore":
http://social.msdn.microsoft.com/Forums/en-US/sqlnetfx/thread/1525a94b-d124-48b5-86a9-bdc970af3bae
jackbensonDirector

Author

Commented:
would another way to do this be to create a record in a table of the stock movements and then use the SQL server agent to call a routine to make the updates every few seconds?

Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

Commented:
that depends on the process you are trying to implement, of course.
it is a possible solution, as I use it also in some processes