Learn more about SQL Server tools

 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Prevent multiple users from running the same SQL Server stored procedure at the same time


By:   |   Read Comments (19)   |   Related Tips: More > Locking and Blocking

Problem

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

For years I've implemented home grown solutions using a "lock" table but this always had inherent problems with either faulty code or with failures that didn't clean up the lock table. Invariably there were situations where a process died and the "lock" hung around preventing other users from running the protected code.  It was always necessary to have a "Clean up the bad locks" feature as part of the application.

A better solution is available: 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:

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:08'            
commit

The sp_getapplock call takes out a lock to the resource "MyLock" and holds it as long as the transaction is alive. In this case it will wait 8 seconds and then execute the COMMIT, which will release the lock. To see how it works open a Management Studio session and executed the code above, then quickly open another Management Studio window and execute this same code again.  That's what I've done in this picture:

sp_getapplock running in two windows with one waiting on the other Img

The session at the top was started first and immediately returned the resultset with the time as the last column. Then as quickly as I could I started the second session and you can see that it's executing the query. Once it's completed you'll see something like the next picture where both sessions have now completed:

sp_getapplock both sessions complete

Notice that the return code for the first query is zero, which means that the lock was granted right away. The return code for the second session is one, which means that the lock was granted after waiting for another session that held the lock. There are also negative return codes such as minus one which indicates that the lock could not be granted by the timeout and other negative codes for other error situations. You should also notice the time. Session 2 returned it's result set eight seconds after session 1. That's because it had to wait those eight seconds to acquire the lock.

Once the lock is acquired releasing it depends on the @LockOwner parameter. If @LockOwner is "Session" the lock is held until it is explicitly released with a call to sp_releaseapplock or if the session ends. A session ends when the connection is closed that can be somewhat risky if the caller has a tendency to hold onto connections. If @LockOwner is "Transaction" the lock is released either with a call to sp_releaseapplock or when the transaction is committed or rolled back. Coding tends to be easier when @LockOwner is Transaction so I try and use it whenever possible but, of course, @LockOnwer='Transaction' can only be used inside a user transaction.

Using sp_releasapplock to release a resource

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

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

Now take a look at how to use sp_getapplock in a stored procedure.  The sample procedure, critical_section_worker, includes transaction control, messaging, and error handling typical of a real world procedure that uses sp_getapplock.  Here's a script that creates it as a permanent procedure in tempdb.

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.

This next picture shows how I ran the query in two windows.  The upper window, with session 56, was started first, at 19:15.14.413.  The lower window, with session 53, was started 5 seconds later.  The upper window grabbed the lock first and held it for 30 seconds.  Since the timeout on the call to sp_getapplock is only 15 seconds, the lower session never got the lock and sp_getapplock returned -1.  

sp_getapplock running in two windows with one waiting on the other Img

In this last picture I ran critical_section_worker with a runtime of 10 seconds.  This time the lower session got the lock before the timeout and was able to complete it's work but only after waiting for the upper session to complete.

sp_getapplock running in two windows with one waiting on the other Img

That's exactly what I wanted: only one instance of the stored procedure can enter the critical section, protected by the sp_getapplock call, at a time.  It's always better to write code that allows multiple instance to run at the same time, but when that's impossible, or just would take too much effort, I use sp_getapplock to ensure that critical sections are single threaded.

Next Steps
  • Locate any places where you've implemented similar functionality using rows in a lock table and replace them with the more robust sp_getapplock.
  • Check your code for sections that must be single threaded.  These are usually in long running batch routines.  If these are not protected from having multiple instances, add the necessary calls to sp_getapplock to add robustness to your code.


Last Update:






About the author
MSSQLTips author Andy Novick Andy Novick is a SQL Server Developer in the Boston area with 25 years of database and application development experience.

View all my tips
Related Resources





More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


Get free SQL tips:

*Enter Code refresh code     



Friday, December 02, 2016 - 4:55:49 AM - Syl Back To Top

Same question as bellow : why is 'if @@Trancount > 1' used rather than 'if @@Trancount > 0'?


Monday, October 17, 2016 - 9:17:52 AM - Jeff Back To Top

 

I know this is an old thread, but I recently came across it while searching for a solution to prevent concurrent executions of a stored procedure.  I found this very valuable, but do have one question:  In the catch section, why is 'if @@Trancount > 1' used rather than 'if @@Trancount > 0'?


Monday, February 01, 2016 - 2:44:09 PM - joely Back To Top

 thanks for this post.

 

 


Wednesday, May 27, 2015 - 2:54:53 PM - Claudio Back To Top

Hi Alain,

I think you forgot to add @LockOwner (Session or Transaction).

Have you checked that?


Tuesday, May 12, 2015 - 2:31:27 PM - Alain Back To Top

1. At '13:00:00' WebServerA send this query to DbServer
begin transaction;
  WaitFor time '13:05:00'
  declare @id int;
  exec @id = sp_getapplock @Resource = 'r1', @LockMode = 'Exclusive'; -- I've tried all modes
  if (@id in (0,1)) begin
    Waitfor delay '00:00:10'; -- hold lock for 10 sec
    exec sp_releaseapplock @Resource = 'r1';

    commit transaction;
  end else rollback transaction;

2. At '13:01:00', WebServerB send the same query to dbserver. 

3. BUG: both sp_getapplock gave 0 at once! No query had to wait 10 sec

Please help me.  Thanks


Friday, July 25, 2014 - 1:29:09 AM - Andy Novick Back To Top

Regarding:

So does @Resource hold the name of the stored procedure. ex: Resource = 'MyLock' , is Mylock a stored procedure.

.......

You could use the name of the stored procedure as @Resource.  That would work. The procedures would start but would block until they could get the lock.  



Friday, July 25, 2014 - 1:26:48 AM - Andy Novick Back To Top

Regarding the version of "Query Analyzer".  That's SSMS from SQL 2008 R2 from roughly 2010.  It's not that old.


Friday, July 25, 2014 - 1:24:39 AM - Andy Novick Back To Top

Regarding 

I have SP name like usp_EPMCALL. how can i use this lock machanisum sp_getapplock at user stored procedure? one of my OLTP database frequently afftected blocking issues.

is it like as below?......

 

sp_getapplock is a way to use a lock to block all but one procedure from owning a resource.  So as long as you only have blocking and not deadlocking there's no need to add additional locks.  SQL Server is already serializing the procedures.  There's nothing to be gained by introducing more locks.

 


Thursday, July 24, 2014 - 1:11:42 AM - ananda Back To Top

I have SP name like usp_EPMCALL. how can i use this lock machanisum sp_getapplock at user stored procedure? one of my OLTP database frequently afftected blocking issues.

is it like as below?

 

DECLARE

 

@RC INT

Begin tran

Exec @RC =sp_getapplock@Resource='usp_EPMCALL', @LockMode='Exclusive'

, @LockOwner='Transaction', @LockTimeout = 15000

SELECT @@SPID [session_id], @RC [return code],GETDATE()

waitfor delay'00:00:08'

commit

 

Thanks

 


Wednesday, July 09, 2014 - 9:35:13 AM - Mark Tierney Back To Top

Thanks Andy.

I have used this previously to lock a range of related rows, via common resource name, but have never thought about single threading a procedure with it. Always great to learn about new ways a feature can be used.


Tuesday, April 29, 2014 - 10:00:08 AM - chirag Back To Top

Hi  all

Basic doubts..

AS i understand this is for preventing same stored procedure from being executed by different users at the same time.

So does @Resource hold the name of the stored procedure. ex: Resource = 'MyLock' , is Mylock a stored procedure.

 

 

 

 


Monday, April 28, 2014 - 8:41:39 AM - Divine Flame Back To Top

Nice Article Andy. Thanks for sharing.


Friday, April 25, 2014 - 2:30:56 AM - Rsotand Abear Back To Top

Nice article, I get to learn something I may need in the future.


Thursday, April 24, 2014 - 1:19:40 PM - Larry Smith Back To Top

I've used this in the past to good effect in many places. That this method utilized SQL Server's own lock mechanism it a life saver.  This means your locks are automatically released if, say, your session is terminated abnormally by loss of connection or any other means, just like any table lock would be if you were to die mid-transaction.  I think of this as a MUTEX within sql.  In fact, we've even used this just as a mutex mechanism where we may have a pool of processes on separate systems, where tasks are mutually exclusive.

Good article!


Thursday, April 24, 2014 - 7:59:16 AM - Steve Johnson Back To Top

I have a couple of processes that spradically cause deadlocks - I'll be investigating the use of these system stored procs. as a means of preventing those. Thanks a lot for this, much appreciated.


Thursday, April 24, 2014 - 4:36:02 AM - Willem Back To Top

Great article. Is that Query Analyzer you are using? Looks retro,

I suddenly felt 10 years younger when I saw the screenshots :-) 


Saturday, April 12, 2014 - 1:57:58 AM - David S Back To Top

Excellent article.  Look forward to reading more!  I even learned the trick of using RAISERROR for messages.


Friday, April 11, 2014 - 5:40:44 PM - Justin Back To Top

I just ran into a situation today where I needed this. Great article.


Thursday, April 10, 2014 - 11:36:40 AM - Wanderlei Santos Back To Top

I'm certainly adding this to my toolbelt. Thanks for the code.


Learn more about SQL Server tools