Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Implementing a T-SQL semaphore

By Robert Cary,

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.

Total article views: 10882 | Views in the last 30 days: 26
 
Related Articles
FORUM

sp_getapplock

Locking SQL Resources using getapplock

FORUM

sp_getapplock useful?

Hi there, I'm managing a database where I noticed lock type APP. Never heard of it, could not find ...

FORUM

Access that can allow to execute SP (create object), however not allow to create/drop/alter table from query window

Access that can allow to execute SP (create object), however not allow to create/drop/alter table fr...

FORUM

ADO and sp_getapplock

The following attempt to place a transaction lock on resource XXX always fails with a return value...

FORUM

Where To Create/Modify Database Maintenance Plans

Confused as to where to create DB Maint Plans

Tags
miscellaneous    
programming    
semaphore    
stored procedures    
t-sql    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones