This article explains about the sp_getapplock and sp_releaselock stored procedures and their usage with example.
What is sp_getapplock
The stored procedure sp_getapplock puts the lock on the application resource. Following is the syntax of the sp_getapplock:
|
1 2 3 4 5 6 |
EXEC Sp_getapplock @Resource = 'resource_name', @LockMode = 'lock_mode', @LockOwner = 'lock_owner', @LockTimeout = 'value', @DbPrincipal = 'database_principal'; |
Arguments
The sp_getapplock accepts the following five arguments:
- Resource Name
The @ResourceName =’Resource_Name’ argument is the name of the resource on which you want to put the lock. The datatype of Resource Name is a nvarchar (255). If resource_name is longer than 255 characters, then it will be truncated. For application, the resource name must be unique. The resource_name is a binary compared; hence it is case sensitive
- Lock Mode
The @LockMode = ‘Lock_Mode’ argument is the mode of the lock that you want to put on the resource. The data type of lock_mode is the nvarchar(32), and it does not have any default value. The lock mode can be any of the following:
- Shared mode
- Update mode
- Exclusive mode
- Intent update mode
- Intent shared mode
- Lock Owner
The @lockOwner =’lock_owner’ argument is an owner of the lock. The data type of lock_owner is nvarchar (32). The value of the lock_owner argument can be a session or transaction. By default, the value of lock_owner is the transaction. When the lock_owner is a transaction, we must execute the sp_getapplock within the transaction
- Lock Timeout
The @LockTimeout = ‘Lock_timeout’ argument is a lock timeout value in a millisecond. The default value of the lock_timeout is the value returned by the @@LOCKTIME function. It returns 0 when the lock request is granted immediately, or it returns -1
- Database principle
The @DBPrinciple=’database_principle’ argument is the user, role, or application role that has permissions to access an object in a database. The default value of the database_principle is public. To run this function successfully, the login which is used to executes this function must be a member of database_principal, dbo, or the db_owner fixed database role
Return value
The return value of the sp_getapplock functions can be >= 0 (success) or < 0 (failed):
Return value | Description |
0 | The requested lock was successfully granted synchronously |
1 | The requested lock was granted successfully after waiting for other locks to be released |
-1 | The requested lock timed out |
-2 | The requested lock was canceled by the caller |
-3 | The requested lock was chosen as a deadlock victim |
999 | This indicates the invalid parameter or other call error |
Notes
- The locks which are associated with the current transaction will be released when the transaction commits or rollback. If the lock is associated with the current session, then the lock releases when the session is logged out or killed. If the server shuts down, all the locks will be released
- The locks acquired by the sp_getapplock can be release using sp_releaseapplock. If you have created an application that has multiple sp_getapplock, the sp_releaseapplock must be used the same number of the time to release the locks created by sp_getapplock. For example, in the application, if we have used the sp_getapplock four times, then the sp_releaseapplock must be used four times
- Only the members of the dbo, db_owner, or the member of the principle specified in the @DBPrinciple argument can acquire the lock on the application
- The lock resources created in a session can be identified by combining values specified in @DatabaseID (The database id, which contains the lock), @DbPrincipal (the database principle), and @Resource (the application) parameters
What is sp_releaseapplock
The stored procedure sp_releaseapplock puts the lock on the application resource. Following is the syntax of the sp_ releaseapplock:
|
1 2 3 4 |
EXEC Sp_releaseapplock @Resource = 'resource_name', @LockOwner = 'lock_owner', @DbPrincipal = 'database_principal' |
The sp_ releaseapplock accepts the following three arguments.
- Resource Name
The @ResourceName =’Resource_Name’ argument is the name of the resource on which you want to release the lock. The datatype of Resource Name is a nvarchar (255). If resource_name is longer then 255 characters, then it will be truncated. For application, the resource name must be unique. The resource_name is a binary compared; hence it is case sensitive
- Lock Owner
The @lockOwner =’lock_owner’ argument is an owner of the lock. The data type of lock_owner is nvarchar (32). The value of the lock_owner argument can be a session or transaction. By default, the value of lock_owner is the transaction. When the lock_owner is a transaction, we must execute the sp_releaseapplock within the transaction
- Database principle
The @DBPrinciple=’database_principle’ argument is the user, role, or application role that has permissions to access an object in a database. The default value of the database_principle is public. To run this function successfully, the login which is used to executes this function must be a member of database_principal, dbo, or the db_owner fixed database role
Return value
The return value of the sp_getapplock functions can be >=0 (success) or < 0 (failed):
Return value | Description |
0 | The lock was released successfully |
999 | This indicates the invalid parameter or other call error |
Example
For example, I want to prevent users from executing the same stored procedure at the same time. To demonstrate the scenario, I have created a stored procedure named procInsertEmployees, which inserts data into the tblEmployee table. I want to make sure that no one can access the stored procedure until the stored procedure inserts the data in the tblEmpoyee table. Moreover, I have added a waitfor delay ’00:00:15’ statement to simulate the blocking for the 15 seconds.
Execute the following T-SQL script to create the tblEmployee table:
|
1 2 3 4 5 6 7 8 9 |
CREATE TABLE tblemployees ( id INT IDENTITY (1, 1), employeename VARCHAR(200), jobtitle VARCHAR(150), phonenumber VARCHAR(50), phonenumbertype VARCHAR(10), emailaddress VARCHAR(250) ) |
Execute the following T-SQL script to create the procInsertEmployees table:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 |
CREATE PROCEDURE [dbo].[Procinsertemployees] @EmployeeName VARCHAR(200), @Jobtitle VARCHAR(150), @PhoneNumber VARCHAR(50), @PhoneNumberType VARCHAR(10), @EmailAddress VARCHAR(250) AS BEGIN DECLARE @returnCode INT BEGIN try EXEC @returnCode = Sp_getapplock @Resource = 'procInsertEmployees', @LockMode = 'Exclusive', @LockOwner = 'Session', @LockTimeout = 50 IF @returnCode NOT IN ( 0, 1 ) BEGIN RAISERROR ( 'Unable to acquire exclusive Lock on procInsertEmployees', 16,1 ) RETURN END WAITFOR delay '00:00:15'; --insert Employee Details INSERT INTO tblemployees (employeename, jobtitle, phonenumber, phonenumbertype, emailaddress) VALUES (@EmployeeName, @Jobtitle, @PhoneNumber, @PhoneNumberType, @EmailAddress) EXEC @returnCode = Sp_releaseapplock @Resource = 'procInsertEmployees', @LockOwner = 'Session' END try BEGIN catch IF @returnCode IN ( 0, 1 ) BEGIN EXEC @returnCode = Sp_releaseapplock @Resource = 'procInsertEmployees', @LockOwner = 'Session', @DbPrincipal = 'public' END DECLARE @ErrMsg VARCHAR(4000) SELECT @ErrMsg = Error_message() RAISERROR(@ErrMsg,15,50) END catch END |
Lock owner = Session
First, let’s test the scenario when the lock owner is the session. The benefit of using the session is that we can also use it when we do not want to use a transaction within our stored procedure.
When you are executing the stored procedure with the session as a lock owner (@LockOwner=Session), make sure you use the TRY…CATCH code so that the lock is gets released before the execution of the stored procedure ends. Execute following the steps in the sequence:
Step 1: First, launch SQL Server Management Studio and open two query editor windows.
Step 2: Copy and paste the code below in the first query window:
|
1 2 3 4 5 6 7 8 |
select getdate() exec [procInsertEmployees] @EmployeeName='Dixit Upadhyay', @Jobtitle='Veterinary Doctor', @PhoneNumber='+91-9825328650', @PhoneNumberType='Cell phone', @EmailAddress='drdixitvet@outlook.com' select getdate() |
Step 3: Copy and paste the following code in second query window:
|
1 2 3 4 5 6 7 8 |
select getdate() exec [procInsertEmployees] @EmployeeName='Nisarg Upadhyay', @Jobtitle='Senior DBA', @PhoneNumber= '+91-9537024898', @PhoneNumberType='Cell phone', @EmailAddress='nisargupadhyay87@outlook.com' select getdate() |
Step 4: Execute query in the first query editor window and immediately execute a query in the second query editor window.
Output
Below is the screenshot of the first query editor window:
As you can see, the first session successfully acquired lock on the and released it after waiting for 15 seconds. Following is the screenshot of the second query window:
As you can see, the second query was unable to obtain the lock on the application resource. It printed the message and exit.
Lock owner = Transaction
Secondly, let’s test the scenario when the lock owner is the transaction. While using a transaction as a lock owner, we do not have to release the lock by using sp_releaseapplock. Once the transaction completes, the lock on the resource will be released automatically.
When you are executing the stored procedure with the transaction as a lock owner (@LockOwner=Transaction), make sure you use the TRY…CATCH code so that the lock is gets released before the execution of the stored procedure ends. Execute following the steps in the sequence.
To demonstrate, we must make some changes in the code of the procedure. As I mentioned above, once the transaction completes, the locks on resources will be released automatically. So, we should remove the code of “sp_releaseapplock.” To execute the procedure with the transaction as a lock owner, we must wrap the transaction between Begin Tran and Commit Tran. Hence put the insert statement in the Begin Tran and Commit Tran. Following is the code of updated stored procedure:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 |
Create PROCEDURE [dbo].[Procinsertemployees] @EmployeeName VARCHAR(200), @Jobtitle VARCHAR(150), @PhoneNumber VARCHAR(50), @PhoneNumberType VARCHAR(10), @EmailAddress VARCHAR(250) AS BEGIN DECLARE @returnCode INT BEGIN try EXEC @returnCode = Sp_getapplock @Resource = 'procInsertEmployees', @LockMode = 'Exclusive', @LockOwner = 'Session', @LockTimeout = 50 IF @returnCode NOT IN ( 0, 1 ) BEGIN RAISERROR ( 'Unable to acquire exclusive Lock on procInsertEmployees', 16,1 ) RETURN END WAITFOR delay '00:00:15'; --insert Employee Details Begin Tran INSERT INTO tblemployees (employeename, jobtitle, phonenumber, phonenumbertype, emailaddress) VALUES (@EmployeeName, @Jobtitle, @PhoneNumber, @PhoneNumberType, @EmailAddress) Commit Tran /*Comment the sp_releaseapplock section*/ -- EXEC @returnCode = Sp_releaseapplock -- @Resource = 'procInsertEmployees', -- @LockOwner = 'Session' /*End*/ END try BEGIN catch DECLARE @ErrMsg VARCHAR(4000) SELECT @ErrMsg = Error_message() RAISERROR(@ErrMsg,15,50) END catch END |
Step 1: First, Launch SQL Server Management Studio and open two query editor windows.
Step 2: Copy and paste the following code on the first query window:
|
1 2 3 4 5 6 7 8 |
select getdate() exec [procInsertEmployees] @EmployeeName='Dixit Upadhyay', @Jobtitle='Veterinary Doctor', @PhoneNumber='+91-9825328650', @PhoneNumberType='Cell phone', @EmailAddress='drdixitvet@outlook.com' select getdate() |
Step 3: Copy and paste the following code in second query window:
|
1 2 3 4 5 6 7 8 |
select getdate() exec [procInsertEmployees] @EmployeeName='Nisarg Upadhyay', @Jobtitle='Senior DBA', @PhoneNumber= '+91-9537024898', @PhoneNumberType='Cell phone', @EmailAddress='nisargupadhyay87@outlook.com' select getdate() |
Step 4: Execute query in the first query editor window and then immediately execute a query in the second query editor window.
Output
Below is the screenshot of the first query editor window:
As you can see, the first session successfully acquired lock on the and released it after waiting for 15 seconds. Following is the screenshot of the second query window:
As you can see, the second query was unable to obtain the lock on the application resource. It printed the message and exit.
Summary
In this article, I have explained about the procedure sp_getapplock and sp_releaseapplock, their usage with the example. I have also explained how we can prevent users from executing the same stored procedure at the same time.
He has expertise in database design, performance tuning, backup and recovery, HA and DR setup, database migrations and upgrades. He has completed the B.Tech from Ganpat University. He can be reached on nisargupadhyay87@outlook.com
- Different ways to identify and change compatibility levels in SQL Server - July 22, 2024
- Copy SQL Databases between Windows 10 and CentOS using SQL Server data tools - October 19, 2022
- Changing the location of FILESTREAM data files in SQL Database - October 14, 2022