sp_getapplock
This article explains about the sp_getapplock and sp_releaselock stored procedures and their usage with example.
本文通过示例介绍了sp_getapplock和sp_releaselock存储过程及其用法。
什么是sp_getapplock (What is sp_getapplock)
The stored procedure sp_getapplock puts the lock on the application resource. Following is the syntax of the sp_getapplock:
存储过程sp_getapplock将锁放在应用程序资源上。 以下是sp_getapplock的语法：
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:
sp_getapplock接受以下五个参数：
- 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
@ResourceName ='Resource_Name'参数是您要放置锁定的资源的名称。 资源名称的数据类型是nvarchar（255） 。 如果resource_name的长度超过255个字符，则它将被截断。 对于应用程序，资源名称必须唯一。 resource_name是一个比较的二进制文件； 因此，区分大小写
- 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:
@LockMode ='Lock_Mode'参数是您要放置在资源上的锁的模式。 lock_mode的数据类型为nvarchar（32），并且没有任何默认值。 锁定模式可以是以下任意一种：
- 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
@lockOwner ='lock_owner'参数是锁的所有者。 lock_owner的数据类型为nvarchar（32）。 lock_owner参数的值可以是会话或事务。 默认情况下，lock_owner的值为事务。 当lock_owner是事务时，我们必须在事务内执行sp_getapplock
- 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
@LockTimeout ='Lock_timeout'参数是一个锁定超时值，以毫秒为单位。 lock_timeout的默认值是@@ LOCKTIME函数返回的值。 立即授予锁定请求时，它返回0，否则返回-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
@ DBPrinciple ='database_principle'参数是有权访问数据库中对象的用户，角色或应用程序角色。 database_principle的默认值为public。 要成功运行此功能，用于执行此功能的登录名必须是database_principal ， dbo或db_owner固定数据库角色的成员。
返回值 (Return value)
The return value of the sp_getapplock functions can be >= 0 (success) or < 0 (failed):
sp_getapplock函数的返回值可以是> = 0（成功）或<0（失败） ：
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 |
返回值 | 描述 |
0 | 请求的锁已成功同步授予 |
1个 | 等待其他锁释放后，已成功授予请求的锁 |
-1 | 请求的锁定超时 |
-2 | 呼叫者取消了请求的锁定 |
-3 | 请求的锁被选为死锁受害者 |
999 | 这表明参数无效或其他调用错误 |
笔记 (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 可以使用sp_releaseapplock释放由sp_getapplock获取的锁。 如果创建的应用程序具有多个sp_getapplock，则必须以相同的次数使用sp_releaseapplock来释放由sp_getapplock创建的锁。 例如，在应用程序中，如果我们已使用sp_getapplock四次，则必须使用sp_releaseapplock四次
- 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 只有dbo，db_owner的成员或@DBPrinciple参数中指定的原理的成员才能获取应用程序上的锁
- @DatabaseID (The database id, which contains the lock), @DatabaseID （包含锁的数据库ID）， @DbPrincipal (the database principle), and @ DbPrincipal （数据库原理）和@Resource (the application) parameters @Resource （应用程序）参数中指定的值来标识会话中创建的锁资源。
什么是sp_releaseapplock (What is sp_releaseapplock)
The stored procedure sp_releaseapplock puts the lock on the application resource. Following is the syntax of the sp_ releaseapplock:
存储过程sp_releaseapplock将锁置于应用程序资源上。 以下是sp_ releaseapplock的语法：
EXEC Sp_releaseapplock
@Resource = 'resource_name',
@LockOwner = 'lock_owner',
@DbPrincipal = 'database_principal'
The sp_ releaseapplock accepts the following three arguments.
sp_ releaseapplock接受以下三个参数。
- 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
@ResourceName ='Resource_Name'参数是您要释放锁定的资源的名称。 资源名称的数据类型是nvarchar（255） 。 如果resource_name大于255个字符，则它将被截断。 对于应用程序，资源名称必须唯一。 resource_name是一个比较的二进制文件； 因此，区分大小写
- 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
@lockOwner ='lock_owner'参数是锁的所有者。 lock_owner的数据类型为nvarchar（32）。 lock_owner参数的值可以是会话或事务。 默认情况下，lock_owner的值为事务。 当lock_owner是事务时，我们必须在事务内执行sp_releaseapplock
- 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
@ DBPrinciple ='database_principle'参数是有权访问数据库中对象的用户，角色或应用程序角色。 database_principle的默认值为public。 要成功运行此功能，用于执行此功能的登录名必须是database_principal ， dbo或db_owner固定数据库角色的成员。
返回值 (Return value)
The return value of the sp_getapplock functions can be >=0 (success) or < 0 (failed):
sp_getapplock函数的返回值可以> = 0（成功）或<0（失败） ：
Return value | Description |
0 | The lock was released successfully |
999 | This indicates the invalid parameter or other call error |
返回值 | 描述 |
0 | 锁已成功释放 |
| 999 | 这表明参数无效或其他调用错误 |
例 (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.
例如，我要防止用户同时执行相同的存储过程。 为了演示该场景，我创建了一个名为procInsertEmployees的存储过程，该过程将数据插入tblEmployee表中。 我想确保在存储过程将数据插入tblEmpoyee表中之前，没有人可以访问该存储过程。 此外，我添加了一个waitfor delay '00：00：15'语句来模拟15秒钟的阻塞。
Execute the following T-SQL script to create the tblEmployee table:
执行以下T-SQL脚本创建tblEmployee表：
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:
执行以下T-SQL脚本以创建procInsertEmployees表：
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:
当您将会话作为锁所有者（@ LockOwner = Session ）执行存储过程时，请确保使用TRY…CATCH代码，以便在存储过程执行结束之前释放锁。 按照顺序执行以下步骤：
Step 1: First, launch SQL Server Management Studio and open two query editor windows.
步骤1：首先，启动SQL Server Management Studio并打开两个查询编辑器窗口。
Step 2: Copy and paste the code below in the first query window:
步骤2：将以下代码复制并粘贴到第一个查询窗口中：
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:
步骤3：将以下代码复制并粘贴到第二个查询窗口中：
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.
步骤4：在第一个查询编辑器窗口中执行查询，并立即在第二个查询编辑器窗口中执行查询。
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:
如您所见，第一个会话成功获取了对的锁定，并在等待15秒后释放了该锁定。 以下是第二个查询窗口的屏幕截图：
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.
其次，让我们测试锁所有者为事务的情况。 使用事务作为锁所有者时，我们不必使用sp_releaseapplock释放锁。 事务完成后，对资源的锁定将自动释放。
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.
当您使用事务作为锁所有者（@ LockOwner = Transaction ）执行存储过程时，请确保使用TRY…CATCH代码，以便在存储过程执行结束之前释放锁。 按照顺序执行以下步骤。
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:
为了演示，我们必须对过程代码进行一些更改。 如上所述，一旦事务完成，对资源的锁定将自动释放。 因此，我们应该删除“ sp_releaseapplock”的代码。 要使用锁定所有者的事务执行该过程，我们必须将事务包装在Begin Tran和Commit Tran之间 。 因此，将insert语句放在Begin Tran和Commit Tran中 。 以下是更新的存储过程的代码：
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.
步骤1：首先，启动SQL Server Management Studio并打开两个查询编辑器窗口。
Step 2: Copy and paste the following code on the first query window:
步骤2：将以下代码复制并粘贴到第一个查询窗口中：
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:
步骤3：将以下代码复制并粘贴到第二个查询窗口中：
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.
步骤4：在第一个查询编辑器窗口中执行查询，然后立即在第二个查询编辑器窗口中执行查询。
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:
如您所见，第一个会话成功获取了对的锁定，并在等待15秒后释放了该锁定。 以下是第二个查询窗口的屏幕截图：
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.
在本文中，我解释了有关过程sp_getapplock和sp_releaseapplock的信息， 它们在示例中的用法。 我还解释了如何防止用户同时执行相同的存储过程。
翻译自: https://www.sqlshack.com/an-overview-of-sp_getapplock-and-sp_releaseapplock-stored-procedures/
sp_getapplock
4970
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
