为什么sp_getapplock不能立即返回控制?

Question

当我的应用程序中的两个用户运行以下过程时:

CREATE PROCEDURE [dbo].[CopyForm](@varId as uniqueidentifier)
AS
BEGIN
DECLARE @returnLock INT
BEGIN try
EXEC @returnLock = Sp_getapplock
@Resource = 'CopyFormLock',
@LockMode = 'Exclusive',
@LockOwner = 'Session'

IF @returnLock <> 0
BEGIN
RAISERROR (
'the operation is occupied by another user, wait a few minutes',
16,1
)
RETURN
END

--do stuff
EXEC @returnLock = Sp_releaseapplock
@Resource = 'CopyFormLock',
@LockOwner = 'Session'
END try
BEGIN catch
IF @returnLock = 0
BEGIN
EXEC @returnLock = Sp_releaseapplock
@Resource = 'CopyFormLock',
@LockOwner = 'Session'
END
END catch
END
GO

第一个锁定过程的用户先执行该过程，然后再将其解锁。第二个用户等待，只有在第一个用户解除锁后，第二个用户才会得到错误"该操作被另一个用户占用，等待几分钟"。我不明白为什么第二个用户没有立即输出错误，而我必须等待第一个用户。请解释我在哪里犯了错误，以及如何使错误立即显示?

Answer 1

你这里有很多问题:

• sp_getapplock总是小于0的结果失败了。如果它等于1，则意味着它必须等待。
• 如果你想要进程在锁被持有的情况下立即纾困，那么通过@LockTimeout = 0。
• 使用THROW代替RAISERROR。

CREATE OR ALTER PROCEDURE [dbo].[CopyForm](@varId as uniqueidentifier)
AS
SET NOCOUNT ON;
DECLARE @returnLock INT
BEGIN TRY
EXEC @returnLock = sp_getapplock
@Resource = 'CopyFormLock',
@LockMode = 'Exclusive',
@LockOwner = 'Session',
@LockTimeout = 0;

IF @returnLock < 0
BEGIN
THROW
50001,
'the operation is occupied by another user, wait a few minutes',
1;
END;

--do stuff
EXEC @returnLock = sp_releaseapplock
@Resource = 'CopyFormLock',
@LockOwner = 'Session';
END TRY
BEGIN CATCH
IF @returnLock = 0
BEGIN
EXEC @returnLock = sp_releaseapplock
@Resource = 'CopyFormLock',
@LockOwner = 'Session';
END;
THROW;
END CATCH
go