APPLOCK_TEST (Transact-SQL)
Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance
This function returns information as to whether or not a lock can be granted on a particular application resource, for a specified lock owner, without acquisition of the lock. As an application lock function, APPLOCK_TEST operates on the current database. The database is the scope of the application locks.
Transact-SQL syntax conventions
Syntax
APPLOCK_TEST ( 'database_principal' , 'resource_name' , 'lock_mode' , 'lock_owner' )
Note
To view Transact-SQL syntax for SQL Server 2014 (12.x) and earlier versions, see Previous versions documentation.
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_mode '
The lock mode to obtain for a specific resource. lock_mode is nvarchar(32), with no default value. lock_mode can have any of these values: Shared, Update, IntentShared, IntentExclusive, Exclusive.
' 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. If default or Transaction is explicitly specified, APPLOCK_TEST must be executed from within a transaction.
Return types
smallint
Return value
0 if the lock cannot be granted to the specified owner, or 1 if the lock can be granted.
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:
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 both run:
COMMIT TRAN;
GO
See also
APPLOCK_MODE (Transact-SQL)
sp_getapplock (Transact-SQL)
sp_releaseapplock (Transact-SQL)
प्रतिक्रिया
https://aka.ms/ContentUserFeedback.
जल्द आ रहा है: 2024 के दौरान हम सामग्री के लिए फीडबैक तंत्र के रूप में GitHub मुद्दों को चरणबद्ध तरीके से समाप्त कर देंगे और इसे एक नई फीडबैक प्रणाली से बदल देंगे. अधिक जानकारी के लिए, देखें:के लिए प्रतिक्रिया सबमिट करें और देखें