Chỉnh sửa

APPLOCK_MODE (Transact-SQL)

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance SQL database in Microsoft Fabric

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

Transact-SQL syntax conventions

Syntax

APPLOCK_MODE( 'database_principal' , 'resource_name' , 'lock_owner' )

Arguments

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

'resource_name'
A lock resource name specified by the client application. The application must ensure a unique resource name. The specified name is hashed internally into a value that the SQL Server lock manager can internally store. resource_nameis 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'
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.

Return types

nvarchar(32)

Return value

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

NoLock
Update
*SharedIntentExclusive

IntentShared
IntentExclusive
*UpdateIntentExclusive

Shared
Exclusive

*This lock mode is a combination of other lock modes and sp_getapplock cannot explicitly acquire it.

Function properties

Nondeterministic

Nonindexable

Nonparallelizable

Examples

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

User A runs:

Note

In Azure SQL Database and SQL database in Microsoft Fabric, the USE statement isn't available. Instead, connect directly to intended database and run the rest of the code sample.

USE AdventureWorks2022;  
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 AdventureWorks2022;  
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

Related content

  • Cập nhật lần cuối vào