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

Question

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

一旦存储过程运行，我想将最新结果存储在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秒。

我想有一些工作计划将其中一些记录存档，并将它们归纳为运行总数，这可能会有所帮助，因为在那个四秒表中有几百万行......

- Frosty840

---

你能发一些 T-SQL 代码吗？ - Dave Mason

除了这个问题之外，这段代码还存在一个明显的问题。第一条语句中使用了“select top 1”，但是没有指定排序方式，这意味着你无法保证会得到哪一行数据。 - Sean Lange

设置 ReturnValue 时不需要使用 order by，因为它是一个聚合值，重新计算该值会增加工作量但没有任何收益。 - Sean Lange

提取分组和排序可以将执行时间缩短约75％。这样做可能会大大减少人们在使用此功能时遇到的问题。真奇怪，它们一开始就存在... - Frosty840

@Frosty840：你确定 OR NOT @LastResult = 0 这个条件是正确的吗？这似乎会导致它在更新后立即进行更新，对吗？ - Solomon Rutzky

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;

需要自己管理错误/回滚（如链接的MSDN文档中所述），因此请使用通常的TRY / CATCH。但是，这确实允许您管理情况。

如果对此过程的争用有任何疑虑，那么应该不会太多，因为在锁定资源后执行的查找是从单行表中选择，然后是一个IF语句（理想情况下）仅在10分钟计时器未经过时返回最后已知值。因此，大多数调用应该处理得相当快。

请注意：应谨慎使用sp_getapplock / sp_releaseapplock; 应用程序锁定肯定非常方便（例如在此类情况下），但只有在绝对必要时才应使用。

- Solomon Rutzky

---

FYI... 这里的默认行为是锁定事务范围，因此当此事务提交或回滚时，它将自动释放，因此不需要调用 sp_releaseapplock。 - CajunCoding