Implementing a T-SQL semaphore (Serializing proc calls without uneccesssary blocking)

Introduction

There are several situations where you might want to marshal calls to a particular PROC. For me, the most common situation is where we have several instances of an application, running in parallel, that need to work with distinct data sets supplied by a stored procedure. These solutions are also useful in any situation where you occasionally encounter race-conditions but do not want to block other processes by acquiring restrictive locks on the objects in use or using a SERIALIZABLE transaction isolation level. Below are various approaches to this problem and reasons why using sp_getapplock is usually the most preferable.

One approach that Ive seen used in the past is to create a surrogate locking table that is only referenced by the PROC you wish to marshal. By acquiring an exclusive table lock on the surrogate table within in the proc, you are ensuring that only one instance of that proc can run at a time. Subsequent calls are effectively queued and thereby serializing execution of the PROC.

   /************** Create Table **************/  CREATE TABLE dbo.SurrogateLockTable
  (
      ID  INT PRIMARY KEY CLUSTERED
  )
  GO
 
  INSERT INTO dbo.SurrogateLockTable(ID)
  VALUES(1)
  GO
     
  /************** Proc Code **************/  CREATE PROC dbo.LockingTest
  AS
   
  BEGIN TRAN
      SELECT ID
      FROM SurrogateLockTable WITH(HOLDLOCK,TABLOCKX)
      /*your code goes here*/  COMMIT

This technique can be extended to lock an individual key, allowing either greater granularity when marshaling calls or to allowing multiple PROCs to utilize the same lock table and thus avoiding a lock table for every PROC you wish you marshal.

   /************** Create Table **************/  CREATE TABLE dbo.SurrogateLockKeyTable
  (
      KeyVal VARCHAR(200) PRIMARY KEY CLUSTERED -- VARCHAR for flexibilty, but could be anything 
                                                -- depending on what your keys will be
  )
  GO
 
  /************** Proc Code **************/  CREATE PROC dbo.KeyLockingTest
  AS
 
  /* CREATE Record in table if it doesn't already exist */  IF NOT EXISTS(SELECT 1
  FROM SurogateLockKeyTable WITH(NOLOCK) --NOLOCK hint allows you to read the table 
                                         -- without being blocked
  WHERE KeyVal = @KeyVal)
  BEGIN
      INSERT INTO SurogateLockKeyTable(KeyVal)
      VALUES(@KeyVal) -- This can cause a race condition, if two identical calls
                      -- are made simultaniuosly.
 
  END
 
  BEGIN TRAN
      SELECT @rand = Id
      FROM [SurogateLockKeyTable] WITH (HOLDLOCK,ROWLOCK,XLOCK)
      WHERE KeyVal = @KeyVal
 
      /*Code goes here*/  COMMIT

This allows for higher concurrency by only blocking calls that would affect the same key. Naturally, key could be a proc name, a table key, a table etc.

An alternative and more favorable approach would be to utilize sp_getapplock and sp_releaseapplock. SP_getapplock is a wrapper for the extened procedure XP_USERLOCK. It allows you to use SQL SERVERs locking mechanism to manage concurrency outside the scope of tables and rows. It can be used you to marshal PROC calls in the same way the above solutions with some additional features.

• By using sp_getapplock, you do not need to create and manage a surrogate table. Sp_getapplock adds locks directly to the server memory. Also, if you look at the second example, there is a flaw where a race-condition could still occur. Sp_getapplock removes this danger.
• Second, you can specify a lock timeout without needing to change session settings. In cases where you only want one call for a particular key to run, a quick timeout would ensure the proc doesnt hold up execution of the application for very long.
• Third, sp_getapplock returns a status which can be useful in determining if the code should run at all. Again, in cases where you only want one call for a particular key, a return code of 1 would tell you that the lock was granted successfully after waiting for other incompatible locks to be released, thus you can exit without running any more code (like an existence check, for example).

The synax is as follows:

       sp_getapplock [ @Resource = ] 'resource_name',
          [ @LockMode = ] 'lock_mode'
          [ , [ @LockOwner = ] 'lock_owner' ]
          [ , [ @LockTimeout = ] 'value' ]

An example using sp_getapplock that is equivalent to the second example:

   /************** Proc Code **************/  CREATE PROC dbo.GetAppLockTest
  AS
 
  BEGIN TRAN
      EXEC sp_getapplock @Resource = @key, @Lockmode = 'Exclusive'
     
      /*Code goes here*/     
      EXEC sp_releaseapplock @Resource = @key
  COMMIT

Conclusions

All the approaches described will allow you to marshal calls to a proc in situations where you don't want to simply acqurie exclusive locks on the tables you are using. Overall, I believe using sp_getapplock is cleaner, more elegant, and more flexible for most situations.

I know it goes without saying, but when using this or any of the other of the locking examples, always wrap them in a transaction with XACT_ABORT on, or checks in code to ensure a ROLLBACK where required.