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:
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.
אין תגובות:
הוסף רשומת תגובה