代码之家 › 专栏 › 技术社区 › Mark Meuer

为什么即使没有更新记录,SQL Update Top也明显减少了锁定?

sql-server-2005 sql-server sql

4

Mark Meuer · 技术社区 · 16 年前

我们有一个这样的更新语句,它被应用于一个有300多万条记录的表:

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

改进代码:

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)”子句将查询放入循环中,即使什么都不做也花了同样长的时间,它也为其他查询腾出了表!

如果我刚才说的很清楚,
为什么第二段代码允许其他查询在没有记录更新的情况下访问表?

4 回复 | 直到 16 年前

1

3

Randy Levy 16 年前

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

在第一种情况下,您正在更新看起来可能是300万行表中的大量记录。有两件重要的事情需要考虑:

Lock Escalation (Database Engine)
ROWLOCK等锁定提示不会防止锁升级。
“数据库引擎不将行或键范围锁升级到

避免锁升级的建议是:

将大型操作分解为小型操作(你已经做到了!)。
作为最后的手段,您可以设置跟踪 ( 不推荐!

看 How to resolve blocking problems that are caused by lock escalation in SQL Server

2

0

Alex Bagnolini 16 年前

很清楚。
这些条件非常严重 ISNULL(email, '') = '' AND Foo = 'Y' .

这是一个盲目的尝试,但你应该考虑为两者添加一个索引 Email 和那个 Foo 字段(不是每个字段一个索引,而是两个字段都有一个索引)。

你对这张桌子有什么疑问?这个表中有哪些索引?

3

0

Andomar 16 年前

SQL Server似乎正在根据 TOP 200 ,即使您指定 ROWLOCK Management -> Activity Montior ,下 Locks by Object ?

4

0

kmacmahon 16 年前

您还应该考虑重构为if的更新。根据我的经验,对于大型表,它们的性能更好:

更新用户 FROM(如果您不关心未提交的读取,请选择USER.ID FROM USER//可选的NOLOCK提示。其中COALESCE(电子邮件,“”)=“”,Foo=“Y”)D 其中D.ID=用户。ID