我有一个存储过程,该存储过程每分钟都会从作业中调用一次,但是它也可以在作业外部随时调用。
如果我在作业调用存储过程的同时调用了存储过程,就会出现问题。
我需要的是让第二个呼叫等到第一个呼叫完成,然后再开始第二个呼叫。
我按照此链接使用sp_getapplock。
注意:我尝试了sp_getapplock,但它取消了第二个调用。
更新:这是我正在使用的示例
ALTER PROCEDURE TestingLocking
AS
BEGIN
SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DECLARE @returnCode INT, @DriverID INT, @OrderID int, @CarTypeID int
EXEC @returnCode = sp_getapplock
@Resource = 'TestingLocking',
@LockMode = 'Exclusive',
@LockOwner = 'Session',
@LockTimeout = 5,
@DbPrincipal = 'public'
DECLARE db_cursor CURSOR FOR
SELECT top 5 OrderID, CarTypeID FROM Orders
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @OrderID, @CarTypeID
WHILE @@FETCH_STATUS = 0
BEGIN
--Update Orders Set DriverID = @DriverID WHERE OrderID = @OrderID
WAITFOR DELAY '00:00:02';
--INSERT INTO Logs(OrderID,DriverID) Values(@OrderID,@DriverID)
FETCH NEXT FROM db_cursor INTO @OrderID, @CarTypeID
END
CLOSE db_cursor
DEALLOCATE db_cursor
EXEC @returnCode = sp_releaseapplock
@Resource = 'TestingLocking',
@LockOwner = 'Session',
@DbPrincipal = 'public'
END
GO
当我从两个SSMS实例执行存储时,第二个实例引发错误
无法释放应用程序锁(数据库主体:“ public”,资源:“ TestingLocking”),因为当前未持有该锁。
您指定的锁定超时为5毫秒,因此,当然,当已经持有该锁定的第二个尝试获取该锁定的进程几乎会立即失败。
如果要无限期等待,请指定-1
为锁定超时。或者,指定一个合理的超时值(请记住,该参数@LockTimeout
以毫秒为单位)。
其次,您无需sp_getapplock
在statement之后验证对的调用的返回值EXEC @returnCode = sp_getapplock ...
。如果获取锁失败,则不应释放锁。您应该从存储过程或作业中返回。检查文档中可能的错误代码:
Value Result
---------------------------------------------------------------------------------------------------
0 The lock was successfully granted synchronously.
1 The lock was granted successfully after waiting for other incompatible locks to be released.
-1 The lock request timed out.
-2 The lock request was canceled.
-3 The lock request was chosen as a deadlock victim.
-999 Indicates a parameter validation or other call error.
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句