sql-server - 如何在存储过程中设置锁？

Question

我在 SQL Server 数据库上有一个长时间运行的存储过程。我不希望它每十分钟运行一次以上。

存储过程运行后，我想根据时间将最新结果存储在 LatestResult 表中，并让对该过程的所有调用在接下来的十分钟内返回该结果。

这相对简单，但我们发现，由于该过程检查 LatestResult 表并对其进行更新，当两个用户调用该过程时，大型用户群会出现许多死锁同时。

在客户端/线程的情况下，我会通过使用锁来解决这个问题，让第一个用户锁定函数，第二个用户遇到锁，等待结果，第一个用户完成他们的过程调用，更新LatestResult 表，并解锁第二个用户，然后第二个用户从 LatestResult 表中获取结果。

有什么方法可以在 SQL Server 中实现这种锁定？

编辑:

这基本上是代码在没有错误检查调用的情况下的样子:

DECLARE @LastChecked AS DATETIME
DECLARE @LastResult AS NUMERIC(18,2)
SELECT TOP 1 @LastChecked = LastRunTime, @LastResult = LastResult FROM LastResult

DECLARE @ReturnValue AS NUMERIC(18,2)

IF DATEDIFF(n, @LastChecked, GetDate()) >= 10 OR NOT @LastResult = 0
BEGIN 
    SELECT @ReturnValue = ABS(ISNULL(SUM(ISNULL(Amount,0)),0)) FROM Transactions WHERE ISNULL(DeletedFlag,0) = 0 GROUP BY GroupID ORDER BY ABS(ISNULL(SUM(ISNULL(Amount,0)),0))
        UPDATE LastResult SET LastRunTime = GETDATE(), LastResult = @ReturnValue
        SELECT @ReturnValue
    END
ELSE
BEGIN
    SELECT @LastResult
END

我不太确定分组发生了什么，但我发现了一个执行时间大约为 4 秒的测试系统。

我认为已经安排了一些工作来归档其中的一些记录并将它们归结为运行总计，这可能会有所帮助，因为在那个四秒钟的表中有几百万行...

Answer 1

这是使用应用程序锁(参见 sp_getapplock 和 sp_releaseapplock)的有效机会，因为它是对您定义的概念而不是对任何给定表中的任何特定行的锁。这个想法是你创建一个事务，然后创建这个带有标识符的任意锁，其他进程将等待进入那段代码，直到锁被释放。这就像应用程序层的 lock() 一样。 @Resource 参数是任意“概念”的标签。在更复杂的情况下，您甚至可以连接 CustomerID 或其中的其他内容以实现更精细的锁定控制。

DECLARE @LastChecked DATETIME,
        @LastResult NUMERIC(18,2);
DECLARE @ReturnValue NUMERIC(18,2);

BEGIN TRANSACTION;
EXEC sp_getapplock @Resource = 'check_timing', @LockMode = 'Exclusive';

SELECT TOP 1 -- not sure if this helps the optimizer on a 1 row table, but seems ok
       @LastChecked = LastRunTime,
       @LastResult = LastResult
FROM LastResult;

IF (DATEDIFF(MINUTE, @LastChecked, GETDATE()) >= 10 OR @LastResult <> 0)
BEGIN 
   SELECT @ReturnValue = ABS(ISNULL(SUM(ISNULL(Amount, 0)), 0))
   FROM   Transactions
   WHERE  DeletedFlag = 0
   OR     DeletedFlag IS NULL;

   UPDATE LastResult
   SET    LastRunTime = GETDATE(),
          LastResult = @ReturnValue;
END;
ELSE
BEGIN
   SET @ReturnValue = @LastResult; -- This is always 0 here
END;

SELECT @ReturnValue AS [ReturnValue];

EXEC sp_releaseapplock @Resource = 'check_timing';
COMMIT TRANSACTION;

您需要自己管理错误/ROLLBACK(如链接的 MSDN 文档中所述)，因此放入通常的 TRY/CATCH。但是，这确实允许您管理情况。

如果对这个过程的争用有任何顾虑，应该不会有太多的顾虑，因为在锁定资源后立即执行的查找是从单行表中进行的 SELECT，然后是一个 IF 语句(理想情况下)只返回如果 10 分钟计时器还没有过去，则为最后已知值。因此，大多数调用应该处理得相当快。

请注意 sp_getapplock/sp_releaseapplock 应谨慎使用；应用程序锁绝对可以非常方便(例如在这种情况下)，但只有在绝对必要时才应使用它们。