sql - 为什么即使没有更新记录，SQL Update Top 也会明显减少锁定？

Question

我对为什么某些 SQL(在 SQL Server 2005 上运行)的行为方式存在疑问。具体来说，我进行了更改以减少更新期间的锁争用，它似乎在我认为不会的情况下起作用。

原始代码:

我们有一个这样的更新语句，它被应用于一个包含超过 3,000,000 条记录的表:

UPDATE  USER WITH (ROWLOCK)
SET Foo = 'N', Bar = getDate()
WHERE ISNULL(email, '') = ''
AND   Foo = 'Y'

正如您可能猜到的那样，这似乎将 USER 表锁定了一段时间。即使使用 ROWLOCK 提示，对 USER 运行查询和更新的其他作业也会阻塞，直到完成。这对于这个特定的应用程序是 Not Acceptable ，所以我想我会应用我读到的一个技巧，让 update 语句一次只更新 100 条记录。这将使其他查询偶尔有机会获得表。

改进的代码:

DECLARE @LOOPAGAIN AS BIT;
SET @LOOPAGAIN = 1;

WHILE @LOOPAGAIN = 1
  BEGIN
    UPDATE TOP (100) USER WITH (ROWLOCK)
    SET Foo = 'N', Bar = getDate()
    WHERE ISNULL(email, '') = ''
    AND   Foo = 'Y'

    IF @@ROWCOUNT > 0
      SET @LOOPAGAIN = 1
    ELSE
      SET @LOOPAGAIN = 0
  END

这成功了。我们的更新完成了它的工作，并且其他查询能够进入表格。一切都是幸福和光明。

谜:

我了解当表中有许多记录必须更新时，这如何提高性能。通过在每 100 次更新后快速运行循环，它使其他查询有机会获得表。神秘的是，即使没有记录受更新影响，这个循环也有同样的效果!

我们第二次运行原始查询时，它只会运行一小部分时间(比如 30 秒左右)，但在这段时间内它会锁定表，即使没有记录被更改。但是将查询放在带有“TOP (100)”子句的循环中，即使它什么都不做也一样长，它为其他查询释放了表!

我对此感到非常惊讶。谁能告诉我:

如果我刚才说的很清楚，

为什么即使没有更新记录，第二个代码块也允许其他查询访问表？

Answer 1

这听起来像是锁升级的经典案例。

在第一个场景中，您正在更新看起来可能是 3,000,000 行表中的大量记录。有两个重要的事情需要考虑:

当在单个表或索引上获得 5,000 个锁时，SQL Server 2005 将升级您的锁。对此有警告和异常(exception)，请参阅 Lock Escalation (Database Engine)了解更多信息。

诸如 ROWLOCK 之类的锁定提示不会
防止锁升级。

“数据库引擎不
将行或键范围锁升级到
页面锁定，但升级它们
直接到表锁。”

因此，根据上述内容和您的描述，我猜想您尝试锁定行的查询正在升级到表级锁定并阻止对 User 表的所有访问。您注意到此阻塞是因为表很大，因此更新需要很长时间。

避免锁升级的建议是:

将大型操作分解为较小的操作(您已经做到了!)。

调整您的查询以提高效率
尽可能。

作为最后的手段，您可以设置跟踪
标志 1211 禁用锁升级
( 不推荐!)。

见 How to resolve blocking problems that are caused by lock escalation in SQL Server更多细节。

如果您想验证锁升级是否正在发生，您可以使用 SQL Server Profiler 并查看 Lock:Escalation 事件。