1087
Chapter 47: Managing Transactions, Locking, and Blocking
47
Application Locks
Application locks open up the whole world of SQL Server locks for custom uses within appli-
cations. 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
...
EXECsp_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
57 8 0 0 APP Cabl1f94c136 X GRANT
c47.indd 1087c47.indd 1087 7/31/2012 10:23:33 AM7/31/2012 10:23:33 AM
http://www.it-ebooks.info