如何解决 SQL Server 中的锁升级所致的阻塞问题


概要


锁升级是将众多细粒度锁 (例如行或页锁) 转换到表锁的过程。Microsoft SQL Server 动态地决定何时执行锁升级。在作出决定之前,SQL Server 将特定扫描,通过整个事务处理,并用于锁系统中作为一个整体的内存占用的锁的数目持有的锁的数目考虑。通常情况下,SQL Server 默认行为将导致发生仅在这些时间点,这会提高性能或当必须将过多的系统锁定内存减少到更合理水平的锁升级。但是,一些应用程序或查询设计可能触发一次时不太理想,并已呈报的表锁可以阻止其他用户的锁升级。本文讨论如何确定锁升级是否引起阻塞和如何处理令人不快的锁升级。

详细信息


如何确定是否阻止引起锁升级

锁升级不会导致大多数阻塞问题。若要确定是否出现障碍时的时间围绕发生锁升级,启动 SQL 事件探查器跟踪,其中包括锁: 升级事件。如果看不到任何锁: 升级事件,没有发生锁升级您的服务器上,这篇文章中的信息不适用于您的情况。

如果锁升级正在进行,请验证已呈报的表锁会阻塞其他用户。

有关如何识别头窗口阻止程序和如何识别头窗口阻止程序正在阻止其他服务器进程 Id (Spid) 持有的锁资源的详细信息,请单击下面的文章编号,以查看 Microsoft 知识库中相应的文章:

224453了解和解决 SQL Server 7.0 或 2000年阻塞问题

如果选项卡 (表级) 锁锁定模式或 X (独占) 的 S (共享),不是阻止其他用户的锁,锁升级不是问题。特别是,如果该选项卡锁意图锁 (如 IS、 IU,或 IX 锁模式),这不是锁升级的结果。如果您阻塞的问题不正在由锁升级,请参阅文章 Q224453 的故障排除步骤。

如何防止锁升级

防止锁升级的最简单和最安全方法是保持短的交易记录并以减少锁底昂贵查询以便不超过锁升级阈值。有多种方式可获取这一目标,列出了许多种:
  • 分解成几个较小的操作大的批处理操作。例如,假设您运行下面的查询从审核表,删除几个几十万旧记录,然后找到它导致阻止其他用户的锁升级:
    DELETE FROM LogMessages WHERE LogDate < '2/1/2002'
    通过一次删除这些记录几百,可以大大减少积累每个交易记录,并防止发生锁升级的锁的数目。例如:
    SET ROWCOUNT 500
    delete_more:
    DELETE FROM LogMessages WHERE LogDate < '2/1/2002'
    IF @@ROWCOUNT > 0 GOTO delete_more
    SET ROWCOUNT 0
  • 使查询尽可能高效,从而减少查询的锁占用的空间。大扫描或大量书签查找可能会增加的可能性的锁升级;此外,它增加死锁的可能性,并且通常会影响并发性和性能。查找查询后,会导致锁升级,力图寻找机会来创建新的索引,或者若要将列添加到现有索引删除索引和表扫描和索引的效率最大化。请考虑查询粘贴到查询分析器的查询窗口以在其上执行自动索引分析。为此,请在查询菜单上,单击索引优化向导在 SQL Server 2000 中,或单击 SQL Server 7.0 中执行索引分析

    这种优化的目标之一是使索引搜索返回尽可能少的行,以书签查找 (最大化的选择性的特定查询的索引) 的成本降到最低。如果 SQL Server 估计书签查找逻辑运算符可能会返回多个行,则可能使用预回迁执行书签查找。如果 SQL Server 使用预取的书签查找,则必须增加对该查询的一部分可重复的读取的查询部分的事务隔离级别。这意味着什么可能看起来类似于 SELECT 语句在读取已提交的隔离级别可能获得数以千计的键锁 (在聚集的索引和一个非聚集的索引) 上,这可能会导致此类查询超过锁升级阈值。这一点尤其重要,如果您发现升级的锁不共享的表锁,它,但是,通常看不到在默认读取已提交的隔离级别。如果使用预取的书签查找子句导致升级,请考虑将其他列添加到索引查找或索引扫描逻辑运算符下的书签查找逻辑运算符出现在查询计划中的非聚集索引。它可能可以创建覆盖索引 (包括在查询中使用的表中所有列的索引),或至少涵盖了如果在选择栏中包含一切用于联接条件或 WHERE 子句中的列的索引列表 is 是不切实际。

    嵌套循环联接还可以使用预取,和这将导致相同的锁定行为。

    有关详细信息,请单击下面的文章编号,以查看 Microsoft 知识库中相应的文章:

    使用"书签查找... 260652嵌套循环联接与预取"可能会持有锁更长的时间

  • 如果不同的 SPID 当前持有一个不兼容的表锁,则不会发生锁升级。锁升级总是升级到表级锁,而从不页锁。此外,如果锁定升级尝试失败,因为另一个 SPID 持有的不兼容的选项卡锁,尝试升级该查询不会阻止在等待选项卡锁。相反,它将继续获取锁定在其原始的、 更精细的级别 (行、 键或页),定期进行更多升级尝试。因此,防止在某个特定的表上的锁升级的一种方法是获得并保持与提升的锁类型不兼容的不同连接锁定。IX (意向排它) 锁在表级别不会锁定任何行或页,但它仍不兼容升级 s (共享的) 或 X (独占) 选项卡上的锁。例如,假设您必须运行的批处理作业可修改大量mytable表中的行和已导致阻塞的发生锁升级。如果此作业总是在不到一小时内完成,可能创建的事务处理 SQL 作业包含以下代码中,并安排新的作业启动批处理作业的开始时间前几分钟:
    BEGIN TRAN
    SELECT * FROM mytable (UPDLOCK, HOLDLOCK) WHERE 1=0
    WAITFOR DELAY '1:00:00'
    COMMIT TRAN
    此查询获取和持有 IX 锁mytable上一个小时,它使这段时间内表上的锁升级。这批不修改任何数据或阻止其他查询 (除非其他查询强制使用 TABLOCK 提示表锁或如果管理员已经禁用页或行锁,使用sp_indexoption存储过程)。
此外,您可以通过启用跟踪标志 1211来禁用锁升级。但是,此跟踪标志禁用全局的 SQL Server 实例中所有的锁升级。锁升级通过最大化效率的否则减速的获取和释放锁的数千开销的查询在 SQL Server 中用于非常有用目的。锁升级还有助于最小化所需的内存,以便跟踪锁定。可以将 SQL Server 锁结构中动态分配的内存是有限的的因此如果您禁用锁升级和锁定内存增长足够大、 尝试分配额外的锁,任何查询可能会失败并出现下面的错误:

错误: 1204,严重性: 19 日状态: 1
这一次,SQL Server 无法获得锁资源。有较少的活动用户时重新运行该语句或请求系统管理员检查 SQL Server 锁和内存配置。
注意:"1204"错误时,它停止当前语句的处理,并会导致回滚当前事务。回滚本身可能阻止用户或导致长的数据库恢复时间,如果您重新启动 SQL Server 服务。

使用锁定提示如 ROWLOCK 只改变初始锁定计划。锁提示不能防止锁升级。


防止锁升级本文前面讨论的其他方法是更好的方法比启用跟踪标记。此外,其他方法通常会导致查询性能优于禁用整个实例的锁升级。Microsoft 建议您启用此跟踪标志只是为了缓解严重阻塞引起的锁升级时其他选项,如那些讨论先前在本文中,正在调查。要启用跟踪标记,以便它开启当 SQL Server 启动时,将其添加为服务器启动参数。

添加服务器启动参数,服务器在 SQL 企业管理器中用鼠标右键单击,单击属性,然后在常规选项卡上单击启动参数,然后添加下面的参数 (严格按照所示):
-T1211
您必须重启 SQL Server 服务为新的启动参数才会生效。如果在查询分析器中运行下面的查询跟踪标志设置立即生效:
DBCC TRACEON (1211, -1)
但是,如果您没有添加-T1211启动参数traceon命令的效果时会丢失关闭并重新打开 SQL Server 服务。打开跟踪标志将阻止任何将来的锁升级,但它不会反转已经发生在当前事务中任何锁升级。