通过

解决SQL Server中锁升级导致的阻塞问题

  • 项目

摘要

锁升级是将许多细粒度锁 (（如行锁或页锁) 转换为表锁）的过程。 Microsoft SQL Server动态确定何时执行锁升级。 做出此决定时，SQL Server会考虑在特定扫描中持有的锁数、整个事务持有的锁数，以及整个系统中用于锁的内存。 通常，SQL Server的默认行为会导致锁升级仅在提高性能或必须将过多的系统锁内存减少到更合理的级别时发生。 但是，某些应用程序或查询设计可能会在不需要执行此操作时触发锁升级，并且升级的表锁可能会阻止其他用户。 本文讨论如何确定锁升级是否导致阻塞，以及如何处理不需要的锁升级。

原始产品版本：SQL Server
原始 KB 编号： 323630

确定锁升级是否导致阻塞

锁升级不会导致大多数阻塞性问题。 若要确定锁定升级是在遇到阻塞问题时还是接近时发生，请启动包含该 lock_escalation 事件的扩展事件会话。 如果未看到任何 lock_escalation 事件，则服务器上不会发生锁升级，并且本文中的信息不适用于你的情况。

如果发生锁升级，请验证升级的表锁是否正在阻止其他用户。

有关如何识别头阻止程序和头阻止程序持有的锁资源以及阻止其他服务器进程 ID (SPID) 的详细信息，请参阅 INF：了解和解决SQL Server阻塞问题。

如果阻止其他用户的锁不是 TAB (表级) 锁，其锁定模式为 S (共享) 或 X (独占) ，则锁升级不是问题。 具体而言，如果 TAB 锁是意向锁 (，例如 IS、IU 或 IX) 的锁定模式，则这不是由锁升级引起的。 如果阻止问题不是由锁升级引起的，请参阅 INF：了解和解决SQL Server阻塞问题故障排除步骤。

防止锁升级

防止锁定升级的最简单、最安全的方法是使事务保持较短，并减少昂贵查询的锁占用空间，以便不会超过锁升级阈值。 可通过多种方法实现此目标，包括以下策略：

  • 将大型批处理操作分解为多个较小的操作。 例如，运行以下查询以从审核表中删除 100,000 多条旧记录，然后确定该查询导致锁定升级，阻止其他用户：

    DELETE FROM LogMessages WHERE LogDate < '20020102';

    通过一次删除这些记录数百条，可以显著减少每个事务累积的锁数。 这将防止锁升级。 例如，运行以下查询：

    DECLARE @done bit = 0;
WHILE (@done = 0)
BEGIN
    DELETE TOP(1000) FROM LogMessages WHERE LogDate < '20020102';
    IF @@rowcount < 1000 SET @done = 1;
END;

  • 通过使查询尽可能高效，减少查询的锁定占用量。 大型扫描或许多书签查找可能会增加锁定升级的机会。 此外，这会增加死锁的几率，并会对并发和性能产生不利影响。 确定导致锁升级的查询后，请查找创建新索引或向现有索引添加列的机会，以删除索引或表扫描，并最大程度地提高索引查找的效率。 查看执行计划，并可能创建新的非聚集索引以提高查询性能。 有关详细信息，请参阅SQL Server索引体系结构和设计指南。

    此优化的一个目标是使索引查找返回的行尽可能少，以最大程度地降低书签查找的成本， (最大化查询) 索引的选择性。 如果SQL Server估计书签查找逻辑运算符将返回许多行，则它可能会使用 PREFETCH 子句执行书签查找。 如果SQL Server确实用于PREFETCH书签查找，则必须将部分查询的事务隔离级别提高为部分查询的“可重复读取”。 这意味着 SELECT ，类似于“已提交读取”隔离级别的语句可能会获取成千上万的键锁， (聚集索引和一个非聚集索引) 。 这可能会导致此类查询超过锁升级阈值。 如果发现升级的锁是共享表锁，这一点尤其重要，尽管这些锁在默认的“已提交”隔离级别并不常见。 如果书签查找 WITH PREFETCH 子句导致升级，请考虑将列添加到出现在 Index Seek 中的非聚集索引，或向查询计划中书签查找逻辑运算符下方的“索引扫描”逻辑运算符添加列。 可以创建覆盖索引 (包含查询) 中使用的表中的所有列的索引，或者如果包含“选择列”列表中的所有内容不切实际，则至少创建一个索引，该索引涵盖用于联接条件或 WHERE 子句的列。

    嵌套循环联接也可能使用 PREFETCH，这会导致相同的锁定行为。

  • 如果其他 SPID 当前持有不兼容的表锁，则无法进行锁升级。 锁升级始终升级到表锁，从不升级到页锁。 此外，如果锁升级尝试由于另一个 SPID 持有不兼容的 TAB 锁而失败，则尝试升级的查询在等待 TAB 锁时不会阻止。 相反，它会继续在其原始、更精细的级别 (行、键或页面) 获取锁，并定期进行其他升级尝试。 因此，防止对特定表进行锁升级的一种方法是在与升级锁类型不兼容的其他连接上获取并保留锁。 表级别的 IX (意向独占) 锁不会锁定任何行或页，但它仍与升级的 S (共享) 或 X (独占) TAB 锁不兼容。 例如，假设必须运行一个批处理作业，该作业修改了 mytable 表中的许多行，并且由于锁定升级而导致阻塞。 如果此作业始终在一小时内完成，则可以创建包含以下代码的 Transact-SQL 作业，并将新作业计划为在批处理作业开始时间前几分钟启动：

    BEGIN TRAN;
SELECT * FROM mytable WITH (UPDLOCK, HOLDLOCK) WHERE 1 = 0;
WAITFOR DELAY '1:00:00';
COMMIT TRAN;

    此查询获取并保留 mytable 上的 IX 锁一小时。 这可以防止在此期间对表进行锁升级。 此批处理不会修改任何数据或阻止其他查询 (除非其他查询使用 TABLOCK 提示强制表锁定，或者管理员已使用 ALTER INDEX) 禁用页面或行锁。

  • 消除由于缺乏 SARGability 而导致的锁升级，这是一个关系数据库术语，用于描述查询是否可以对谓词和联接列使用索引。 有关 SARGability 的详细信息，请参阅 内部设计指南查询注意事项。 例如，看似不请求多个行或单个行的相当简单的查询最终仍可能扫描整个表/索引。 如果 WHERE 子句左侧有函数或计算，则可能会出现这种情况。 缺乏 SARGability 的此类示例包括隐式或显式数据类型转换、ISNULL () 系统函数、将列作为参数传递的用户定义函数，或对列进行计算，例如 WHERE CONVERT(INT, column1) = @a 或 WHERE Column1*Column2 = 5。 在这种情况下，查询无法 SEEK 现有索引，即使它包含相应的列，因为必须首先检索所有列值并将其传递给函数。 这会导致扫描整个表或索引，并导致获取大量锁。 在这种情况下，SQL Server可以达到锁计数升级阈值。 解决方案是避免对 WHERE 子句中的列使用函数，从而确保 SARGable 条件。

禁用锁升级

尽管可以在 SQL Server 中禁用锁升级，但我们不建议这样做。 请改用 防止锁定升级 部分中所述的防护策略。

  • 表级别： 可以在表级别禁用锁升级。 请参阅 ALTER TABLE ... SET (LOCK_ESCALATION = DISABLE)。 若要确定要面向哪个表，请检查 T-SQL 查询。 如果不可能，请使用 扩展事件，启用 lock_escalation 事件，并检查 object_id 列。 或者，使用 Lock：Escalation 事件 ，并使用 SQL Profiler 检查 ObjectID2 列。
  • 实例级别： 可以通过为实例启用跟踪标志 1211 或 1224 或两者来禁用锁升级。 但是，这些跟踪标志在 SQL Server 实例中全局禁用所有锁升级。 锁升级在SQL Server中非常有用，它可最大程度地提高查询的效率，否则查询会因获取和释放数千个锁的开销而降低。 锁升级还有助于最大程度地减少跟踪锁所需的内存。 SQL Server可为锁结构动态分配的内存是有限的。 因此，如果禁用锁升级，并且锁内存增长到足够大，则为任何查询分配其他锁的任何尝试都可能会失败，并生成以下错误条目：

错误： 1204，严重性： 19，状态： 1
SQL Server目前无法获取 LOCK 资源。 当活动用户较少时，请重新运行语句，或要求系统管理员检查SQL Server锁和内存配置。

注意

发生 1204 错误时，它会停止处理当前语句，并导致活动事务回滚。 如果重启SQL Server服务，回滚本身可能会阻止用户或导致数据库恢复时间过长。

可以使用 SQL Server 配置管理器 添加这些跟踪标志 (-T1211 或 -T1224 ) 。 必须重启SQL Server服务，新的启动参数才能生效。 如果运行 DBCC TRACEON (1211, -1) 或 DBCC TRACEON (1224, -1) 查询，跟踪标志将立即生效。
但是，如果不添加 -T1211 或 -T1224 作为启动参数，则重启SQL Server服务时命令的效果DBCC TRACEON将丢失。 启用跟踪标志可防止将来的任何锁升级，但不会撤消活动事务中已发生的任何锁升级。

如果使用锁提示（如 ROWLOCK），则只会更改初始锁定计划。 锁提示不会阻止锁升级。

锁定升级阈值

在以下情况之一下，可能会发生锁升级：

  • 达到内存阈值 - 达到锁内存的 40% 的内存阈值。 当锁内存超过缓冲池的 24% 时，可以触发锁升级。 锁内存限制为可见缓冲池的 60%。 锁升级阈值设置为锁内存的 40%。 这是 60% 缓冲池的 40%，即 24%。 如果锁内存超过 60% 的限制 (则如果禁用锁升级) ，则分配其他锁的所有尝试都会失败，并且 1204 会生成错误。

  • 达到锁阈值 - 检查内存阈值后，将评估在当前表或索引上获取的锁数。 如果数字超过 5,000，则会触发锁升级。

若要了解达到的阈值，请使用扩展事件，启用 lock_escalation 事件，并检查 escalated_lock_count 列和 escalation_cause 列。 或者，使用 Lock：Escalation 事件，并检查 EventSubClass 值，其中“0 - LOCK_THRESHOLD”表示语句超出了锁阈值，“1 - MEMORY_THRESHOLD”表示语句已超出内存阈值。 此外，检查 IntegerData 和 IntegerData2 列。

建议

与在表或实例级别禁用升级相比， 防止锁定升级 部分中讨论的方法更好。 此外，与禁用锁升级相比，预防性方法通常为查询带来更好的性能。 Microsoft 建议仅启用此跟踪标志，以缓解锁升级导致的严重阻塞，同时正在调查其他选项（如本文中讨论的选项）。

另请参阅