יום רביעי, 16 באפריל 2014

Explicit resource locking in sqlserver

I had a problem where I need explicit locking of resources in the SQL SERVER.
When sp1 run I need to prevent sp2 to run in parallel.
The stored procedures handle deferent DataBase resources so the transaction locking can’t help in this case . 

I have found the following article that helped me solve the problem:

http://www.mssqltips.com/sqlservertip/3202/prevent-multiple-users-from-running-the-same-sql-server-stored-procedure-at-the-same-time/?utm_source=dailynewsletter&utm_medium=email&utm_content=headline&utm_campaign=20140414

The the solution is based on the  sp_getapplock command.

Exec @rc = sp_getapplock @Resource='myLock' -- the resource to be locked can be any mutex name

         , @LockMode='Exclusive'  -- Type of lock

         , @LockOwner='Transaction' -- Transaction or Session

         , @LockTimeout = 15000 -- timeout in milliseconds, 15 seconds

Committing or aborting the transaction release the lock.
allow voiding the call to sp_releaseapplock

Don’t forget to check if timeout error occurred after the sp_getapplock has returned.

I although learn from the article to use the :
set @msg= convert(varchar,getdate(), 114) + ' requesting lock'
raiserror (@msg, 0, 1) with nowait

in order to output debug strings while executing a test code.

אין תגובות:

הוסף רשומת תגובה