sp_getapplock

Places a lock on an application resource. 

Syntax

sp_getapplock [ @Resource = ] 'resource_name',
    [ @LockMode = ] 'lock_mode'
    [ , [ @LockOwner = ] 'lock_owner' ]
    [ , [ @LockTimeout = ] 'value' ]

Arguments

[@Resource =] 'resource_name'

Is a lock resource name specified by the client application. The application must ensure the resource is unique. The specified name is hashed internally into a value that can be stored in the SQL Server lock manager. resource name is nvarchar(255), with no default.

[@LockMode =] 'lock_mode'

Is a lock mode. lock_mode is nvarchar(32), with no default, and can be one of these values: Shared, Update, Exclusive, IntentExclusive, IntentShared.

[@LockOwner =] 'lock_owner'

Is the lock owner. lock_owner is nvarchar(32) and can be Transaction (the default), or Session. When the lock_owner value is the default, or when Transaction is specified explicitly, sp_getapplock must be executed from within a transaction.

[@LockTimeout =] 'value'

Is a lock time-out value, in milliseconds. The default value is the same as the value returned by @@LOCK_TIMEOUT. To indicate that lock requests that cannot be granted immediately should return an error rather than wait for the lock, specify 0.

Return Code Values

>= 0 (success) or < 0 (failure)

Remarks

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, the locks are released.

Locks can be explicitly released with sp_releaseapplock. If 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 specifies different lock modes, 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 existing mode and the newly requested mode. This stronger lock mode is held until the lock is ultimately released, even if lock release calls have occurred. For example, in the following sequence of calls, the resource is held in Exclusive rather than Shared mode.

USE Northwind
GO
BEGIN TRAN
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 TRAN

A deadlock with an application lock does not roll back the transaction that requested the application lock. Any rollback that potentially may be required as a result of the return value must be done manually. Consequently, it is recommended that error checking be included in the code such that if certain values are returned (for example, -3), a ROLLBACK TRANSACTION, or alternative action, is initiated.

Here is an example:

USE Northwind
GO
BEGIN TRAN
DECLARE @result int
EXEC @result = sp_getapplock @Resource = 'Form1', 
            @LockMode = 'Exclusive'
IF @result = '-3'
BEGIN
      ROLLBACK TRAN
END
ELSE
BEGIN
   EXEC @result = sp_releaseapplock @Resource = 'Form1'
   COMMIT TRAN
END

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 sp_lock to examine lock information or the SQL Profiler to monitor locks.

Permissions

Execute permissions default to the public role.

Examples

This example places a shared lock, associated with the current transaction, on the resource 'Form1' in the Northwind database.

USE Northwind
GO
BEGIN TRAN
DECLARE @result int
EXEC @result = sp_getapplock @Resource = 'Form1', 
                   @LockMode = 'Shared'
COMMIT TRAN

See Also

sp_releaseapplock