Application Locks

Application locks open up the whole world of SQL Server locks for custom uses within applications. Instead of using data as a locked resource, application locks use any named user resource declared in the sp_GetAppLock stored procedure.

Application locks must be obtained within a transaction. As with the locks the engine puts on the database resources, you can specify the lock mode (Shared, Update, Exclusive, IntentExclusive, or IntentShared). The return code indicates whether the procedure was successful in obtaining the lock, as follows:

  • 0: Lock was obtained normally.
  • 1: Lock was obtained after another procedure released it.
  • -1: Lock request failed (timeout).
  • -2: Lock request failed (canceled).
  • -3: Lock request failed (deadlock).
  • -999: Lock request failed (other error).

The sp_ReleaseAppLock stored procedure releases the lock. The following code shows how you can use the application lock in a batch or procedure:

EXEC @ShareOK = sp_GetAppLock 
         @Resource = ‘TimChapman', 
         @LockMode = ‘Exclusive'
IF @ShareOK < 0 
 --Error handling code
EXEC sp_ReleaseAppLock @Resource = ‘TimChapman'

When the application locks are viewed using SQL Server Management Studio or sp_Lock, the lock appears as an “APP”-type lock. The following is an abbreviated listing of sp_lock executed at the same time as the previous code:



spid dbid ObjId IndId Type Resource Mode Status ----- ----- ------ ...

Get Microsoft SQL Server 2012 Bible now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.