如何解决 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 子句中的列的索引是不切实际的。
  
  嵌套循环联接也可以使用预取，，这将导致相同的锁定行为。
  
  有关详细信息，请单击下面的文章编号，以查看 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				

  此查询获取，并一小时，这可以防止在表上的锁升级这段时间内保持在mytable的 IX 锁。这批不会修改任何数据或阻止其他查询 （除非其他查询强制使用 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 服务。打开跟踪标志可防止任何将来的锁升级，但它不会反转任何已经发生的活动事务的锁升级。