Microsoft SQL Server 2012 Bible

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:

BEGIN TRANSACTION
DECLARE @ShareOK INT 
EXEC @ShareOK = sp_GetAppLock 
         @Resource = ‘TimChapman', 
         @LockMode = ‘Exclusive'
IF @ShareOK < 0 
 --Error handling code
 
 --code
...
EXEC sp_ReleaseAppLock @Resource = ‘TimChapman'
COMMIT TRANSACTION
Go

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:

EXECUTE sp_Lock

Result:

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