Suppose you have a SQL Stored Procedure and need to prvenet users or processes to run it concurrently. If an instance of SP is called and there exists another instance of this SP currently running, we want the SP to wait. The wait may time out after a certain amout of time
To this end, the SP code needs to be put inside a transaction. Transactions are capable of monitoring their concurrency. I am calling the transaction, MyLock here. Note the name of stored procedure, MyStoredProc is also passed in sp_getapplock SP. Below code, waits 15 seconds before it retries for another SP call attempt.
ALTER PROCEDURE MyStoredProc AS BEGIN declare @msg nvarchar(100), @result int = -1, @retry_counter int = 0, @retry_MAX int = 15*4*10 /* 10 mins */ begin tran MyLock while @result = -1 and @retry_counter < @retry_MAX begin exec @result = sp_getapplock @Resource = 'MyStoredProc', @LockMode = 'Exclusive', @LockOwner = 'Transaction', @LockTimeout = 0 if @result = -1 waitfor delay '00:00:15' set @retry_counter = @retry_counter + 15 end
Now we want the SP to time out and break the SP if @retry_MAX is met.
if @result < -1 or @retry_counter >= @retry_MAX begin rollback tran MyLock set @msg = N'Error Code= ' + cast(@result as nvarchar) + N' Could not acquire application lock' raiserror(@msg, 0, 1) with nowait return -2 end
Then comes the actual code that MyStoredProc is supposed to do
select 'Do the Job...' select 'Do the Job...'
Finally the lock is release and this is the case the SP has normally finished.
exec sp_releaseapplock @Resource = 'MyStoredProc' commit tran MyLock