This post has already been read 69 times!

There is a stored procedure that must be run by only one user at a time. How do to I prevent multiple users from running the stored procedure at the same time?

Solution

SQL Server provides an application manageable lock mechanism through the sp_getapplock / sp_releaseapplock pair of system stored procedures. They provide a way for application code to use SQL's underlying locking mechanism, without having to lock database rows. The lock can be tied to a transaction or session ensuring lock release when the transaction COMMITs or ROLLSBACK or when the session exits and the connection is closed.

Using sp_getapplock to lock a resource

To obtain a lock, call sp_getapplock as follows:

using sp_getapplock to lock a resource
DECLARE @RC INT
Begin tran
Exec @RC = sp_getapplock @Resource='MyLock', @LockMode='Exclusive', @LockOwner='Transaction', @LockTimeout = 15000
SELECT @@SPID [session_id], @RC [return code], GETDATE()
waitfor delay '00:00:10'            
commit

Using sp_releasapplock to release a resource

The call to sp_releaseapplock requires the @Resource and @LockOwner parameters and looks like this:

using sp_releaseapplock to release a resource
DECLARE @RC INT
Exec @RC = sp_releaseapplock @Resource='MyLock', @LockOwner='Transaction'
select @RC

However, if @LockOwner='Transaction' then sp_releaseapplock must be executed inside the transaction. In addition, if the lock isn't held by the transaction SQL Server doesn't just return a return code, an explicit error is thrown and the code must account for that possibility.

For that reason when using @LockOwner='Transaction' I avoid calling sp_releaseapplock but instead rely on the transaction COMMIT to release the lock.

Stored Procedure Example Using sp_getapplock

stored procedure example using sp_getapplock
USE [tempdb]
GO
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC dbo.critical_section_worker  @wait_duration varchar(30) = '00:01:00' -- default one minute
/* Performs a task in a critical section of code that can only be run
   by one session at a time. The task is simulated by a WAIT  */
AS
declare @rc int = 0 -- return code
      , @msg varchar(2000)
set @msg = convert(varchar,getdate(), 114) + ' critical_section_worker starting'
raiserror (@msg, 0, 1) with nowait 
Begin Try
 Begin tran
 set @msg= convert(varchar,getdate(), 114) + ' requesting lock'
 raiserror (@msg, 0, 1) with nowait
 Exec @rc = sp_getapplock @Resource='CriticalSectionWorker' -- the resource to be locked
         , @LockMode='Exclusive'  -- Type of lock
         , @LockOwner='Transaction' -- Transaction or Session
         , @LockTimeout = 15000 -- timeout in milliseconds, 15 seconds
                            
 set @msg= convert(varchar,getdate(), 114) + ' sp_getapplock returned ' + convert(varchar(30), @rc) + ' -- '
      + case when @rc < 0 then 'Could not obtain the lock'  else 'Lock obtained'  end
 raiserror (@msg, 0, 1) with nowait
  
 if @rc >= 0 begin
  set @msg= convert(varchar,getdate(), 114) + ' got lock starting critical work '
  raiserror (@msg, 0, 1) with nowait
  
  waitfor delay @wait_duration -- Critical Work simulated by waiting
  
  commit tran -- will release the lock
  set @msg= convert(varchar,getdate(), 114) + ' work complete released lock' 
  raiserror (@msg, 0, 1) with nowait
  end 
 else begin
        
  rollback tran
  set @rc = 50000
 end
end try
begin catch
 
 set @msg = 'ERROR: ' + ERROR_MESSAGE() + ' at ' 
            + coalesce(ERROR_PROCEDURE(), '')
            + coalesce (' line:' + convert(varchar(30), ERROR_LINE()), '')
            
 RAISERROR (@msg, 0, 1) with nowait -- ensure the message gets out                                 
 if @@Trancount > 1 rollback tran
 raiserror (@msg, 16, 1)
 end catch
 return @rc
 GO

The lock is held for the duration of the transaction and will be released either by the COMMIT at the end of the TRY block or by the ROLLBACK in the CATCH block.  You might notice the use of RAIERROR... WITH NOWAIT instead of PRINT.  RAISERROR with a error code of zero isn't really an error and adding the WITH NOWAIT forces the message and any preceding messages to be displayed immediately.  I described this feature in detail in the this tip Using the NOWAIT option with the SQL Server RAISERROR statement.

Leave a Reply

Post Navigation