考虑会话级应用程序锁,而不是锁定表(
https://msdn.microsoft.com/en-us/library/ms189823.aspx
). 当SQL会话因任何原因终止或显式释放时,锁将被释放。
--acquire lock
DECLARE
@ReturnCode int
, @ClientID nvarchar(255) = '12345';
EXEC @ReturnCode = sp_getapplock
@Resource = @ClientID
, @LockMode = N'Exclusive'
, @LockOwner = 'Session'
, @LockTimeout = 0;
IF @ReturnCode < 0
BEGIN
RAISERROR('Lock for quote not be granted for ClientID %s. Return code=%d', 16, 1, @ClientID, @ReturnCode);
END;
--release lock
DECLARE
@ReturnCode int
, @ClientID nvarchar(255) = '12345';
EXEC @ReturnCode = sp_releaseapplock
@Resource = @ClientID
, @LockOwner = 'Session';