Places a lock on an application resource.
sp_getapplock [ @Resource = ] 'resource_name' , [ @LockMode = ] 'lock_mode' [ , [ @LockOwner = ] 'lock_owner' ] [ , [ @LockTimeout = ] 'value' ] [ , [ @DbPrincipal = ] 'database_principal' ] [ ; ]
>= 0 (success), or < 0 (failure)
The lock was successfully granted synchronously.
The lock was granted successfully after waiting for other incompatible locks to be released.
The lock request timed out.
The lock request was canceled.
The lock request was chosen as a deadlock victim.
Indicates a parameter validation or other call error.
Locks placed on a resource are associated with either the current transaction or the current session. Locks associated with the current transaction are released when the transaction commits or rolls back. Locks associated with the session are released when the session is logged out. When the server shuts down for any reason, all locks are released.
The lock resource created by sp_getapplock is created in the current database for the session. Each lock resource is identified by the combined values of:
The database ID of the database containing the lock resource.
The database principle specified in the @DbPrincipal parameter.
The lock name specified in the @Resource parameter.
Only a member of the database principal specified in the @DbPrincipal parameter can acquire application locks that specify that principal. Members of the dbo and db_owner roles are implicitly considered members of all roles.
Locks can be explicitly released with sp_releaseapplock. When an application calls sp_getapplock multiple times for the same lock resource, sp_releaseapplock must be called the same number of times to release the lock.
If sp_getapplock is called multiple times for the same lock resource, but the lock mode that is specified in any of the requests is not the same as the existing mode, the effect on the resource is a union of the two lock modes. In most cases, this means the lock mode is promoted to the stronger of the lock modes, the existing mode, or the newly requested mode. This stronger lock mode is held until the lock is ultimately released even if lock release calls have occurred before that time. For example, in the following sequence of calls, the resource is held in Exclusive mode instead of in Shared mode.
USE AdventureWorks2008R2; GO BEGIN TRANSACTION; DECLARE @result int; EXEC @result = sp_getapplock @Resource = 'Form1', @LockMode = 'Shared'; EXEC @result = sp_getapplock @Resource = 'Form1', @LockMode = 'Exclusive'; EXEC @result = sp_releaseapplock @Resource = 'Form1'; COMMIT TRANSACTION; GO
A deadlock with an application lock does not roll back the transaction that requested the application lock. Any rollback that might be required as a result of the return value must be done manually. Consequently, we recommend that error checking be included in the code so that if certain values are returned (for example, -3), a ROLLBACK TRANSACTION or alternative action is initiated.
Here is an example:
USE AdventureWorks2008R2; GO BEGIN TRANSACTION; DECLARE @result int; EXEC @result = sp_getapplock @Resource = 'Form1', @LockMode = 'Exclusive'; IF @result = -3 BEGIN ROLLBACK TRANSACTION; END ELSE BEGIN EXEC @result = sp_releaseapplock @Resource = 'Form1'; COMMIT TRANSACTION; END; GO
SQL Server uses the current database ID to qualify the resource. Therefore, if sp_getapplock is executed, even with identical parameter values on different databases, the result is separate locks on separate resources.
Use the sys.dm_tran_locks dynamic management view or the sp_lock system stored procedure to examine lock information, or use SQL Server Profiler to monitor locks.
The following example places a shared lock, which is associated with the current transaction, on the resource Form1 in the AdventureWorks2008R2 database.
USE AdventureWorks2008R2; GO BEGIN TRAN; DECLARE @result int; EXEC @result = sp_getapplock @Resource = 'Form1', @LockMode = 'Shared'; COMMIT TRAN; GO
The following example specifies dbo as the database principal.
BEGIN TRAN; EXEC sp_getapplock @DbPrincipal = 'dbo', @Resource = 'AdventureWorks2008R2', @LockMode = 'Shared'; COMMIT TRAN; GO