解决 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 获取数千个键锁（在聚集索引和一个非聚集索引上）。 这可能会导致此类查询超过锁升级阈值。 如果发现升级的锁是共享表锁，这一点尤其重要，尽管这些锁在默认的“已提交”隔离级别并不常见。 如果 Bookmark Lookup WITH PREFETCH 子句导致升级，请考虑将列添加到索引查找中显示的非聚集索引，或查询计划中书签查找逻辑运算符下方的 Index Scan 逻辑运算符。 可以创建覆盖索引（索引包含查询中使用的表中的所有列），或者至少包含用于联接条件的列的索引，或者如果“选择列”列表中的所有内容是不切实际的，则为 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% 或 24% 的 40%。 如果锁内存超过 60% 的限制（如果禁用锁升级的可能性更大），则所有尝试分配其他锁都会失败，并 1204 生成错误。

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

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

建议

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

另请参阅

• 了解和解决 SQL Server 阻塞问题
• 对 SQL Server 上运行缓慢的查询进行故障排除