Export (0) Print
Expand All

APPLOCK_MODE (Transact-SQL)

Applies To: SQL Server 2014, SQL Server 2016 Preview

Topic Status: Some information in this topic is preview and subject to change in future releases. Preview information describes new features or changes to existing features in Microsoft SQL Server 2016 Community Technology Preview 2 (CTP2).

Returns the lock mode held by the lock owner on a particular application resource. APPLOCK_MODE is an application lock function, and it operates on the current database. The scope of application locks is the database.

Applies to: SQL Server (SQL Server 2008 through current version), Azure SQL Database.

Topic link icon Transact-SQL Syntax Conventions

APPLOCK_MODE( 'database_principal' , 'resource_name' , '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 name 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_owner'

Is the owner of the lock, which is the lock_owner value when the lock was requested. lock_owner is nvarchar(32), and the value can be either Transaction (the default) or Session.

nvarchar(32)

Returns the lock mode held by the lock owner on a particular application resource. Lock mode can be any one of these values:

NoLock

Update

*SharedIntentExclusive

IntentShared

IntentExclusive

*UpdateIntentExclusive

Shared

Exclusive

 

*This lock mode is a combination of other lock modes and cannot be explicitly acquired by using sp_getapplock.

Nondeterministic

Nonindexable

Nonparallelizable

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

User A runs:

USE AdventureWorks2012;
GO
BEGIN TRAN;
DECLARE @result int;
EXEC @result=sp_getapplock
    @DbPrincipal='public',
    @Resource='Form1',
    @LockMode='Shared',
    @LockOwner='Transaction';
SELECT APPLOCK_MODE('public', 'Form1', 'Transaction');
GO

User B then runs:

Use AdventureWorks2012;
GO
BEGIN TRAN;
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.)
GO

User A then runs:

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

User B then runs:

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

User A and User B then run:

COMMIT TRAN;
GO
Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2015 Microsoft