Was this page helpful?
Your feedback about this content is important. Let us know what you think.
Additional feedback?
1500 characters remaining
Export (0) Print
Expand All


Returns information about whether or not a lock can be granted on a particular application resource for a specified lock owner without acquiring the lock. APPLOCK_TEST is an application lock function, and it operates on the current database. The scope of application locks is the database.

Topic link icon Transact-SQL Syntax Conventions

APPLOCK_TEST ( 'database_principal' , 'resource_name' , 'lock_mode' , 'lock_owner' )

' database_principal '

Is the user, role, or application role that can be granted permissions to objects in the database. The caller of the function must be a member of database_principal, dbo, or the db_owner fixed database role in order to call the function successfully.

' resource_name '

Is a lock resource name specified by the client application. The application must ensure that 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. resource_name is binary compared and is case-sensitive, regardless of the collation settings of the current database.

' lock_mode '

Is the lock mode to be obtained for a particular resource. lock_mode is nvarchar(32) and has no default value. The value can be any of the following: Shared, Update, IntentShared, IntentExclusive, Exclusive.

' lock_owner '

Is the owner of the lock, which is the lock_owner value when the lock was requested. lock_owner is nvarchar(32). The value can be Transaction (the default) or Session. If default or Transaction is explicitly specified, APPLOCK_TEST must be executed from within a transaction.

Returns 0 when the lock cannot be granted to the specified owner and returns 1 if the lock can be granted.




In the following example, two users (User A and User B) with separate sessions run the following sequence of Transact-SQL statements.

User A runs:

USE AdventureWorks2012;
DECLARE @result int;
EXEC @result=sp_getapplock
SELECT APPLOCK_MODE('public', 'Form1', 'Transaction');

User B then runs:

Use AdventureWorks2012;
SELECT APPLOCK_MODE('public', 'Form1', 'Transaction');
--Result set: NoLock

SELECT APPLOCK_TEST('public', 'Form1', 'Shared', 'Transaction');
--Result set: 1 (Lock is grantable.)

SELECT APPLOCK_TEST('public', 'Form1', 'Exclusive', 'Transaction');
--Result set: 0 (Lock is not grantable.)

User A then runs:

EXEC sp_releaseapplock @Resource='Form1', @DbPrincipal='public';

User B then runs:

SELECT APPLOCK_TEST('public', 'Form1', 'Exclusive', 'Transaction');
--Result set: '1' (The lock is grantable.)

User A and User B then both run:

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

© 2015 Microsoft