Prevent Concurrent runs for a SQL Stored Procedure

Problem

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

Solution

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

Leave a comment