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:

Arguments

The sp_getapplock accepts the following five arguments:

1. 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

2. 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
   
   
3. 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

4. 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

5. 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):

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:

The sp_ releaseapplock accepts the following three arguments.

1. 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

2. 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

3. 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):

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:

Execute the following T-SQL script to create the procInsertEmployees table:

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:

Step 3: Copy and paste the following code in second query window:

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:

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:

Step 3: Copy and paste the following code in second query window:

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.

• Author
• Recent Posts

Nisarg Upadhyay

Nisarg Upadhyay is a SQL Server Database Administrator and Microsoft certified professional who has more than 8 years of experience with SQL Server administration and 2 years with Oracle 10g database administration.

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

Latest posts by Nisarg Upadhyay (see all)

• 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
• Manage SQL Databases in CentOS: Manage filegroups of user databases - October 5, 2022