优化锁定

项目
02/06/2025

适用于：Azure SQL 数据库 Microsoft Fabric SQL 数据库

本文介绍了优化的锁定，数据库引擎功能提供了改进的事务锁定机制，以减少并发事务的锁定内存消耗和阻塞。

什么是优化锁定？

优化锁定有助于减少锁内存，因为即使是大型事务，持有的锁也很少。此外，优化锁定还可以避免锁升级。这允许对表进行更多并发访问。

优化锁定由两个主要部分组成：事务 ID (TID) 锁定和限定后锁定 (LAQ)。

事务 ID (TID) 是事务的唯一标识符。每一行都标有修改它的最后一个 TID。使用 TID 上的单个锁，而不是使用多个键或行标识符锁。有关详细信息，请参阅事务 ID (TID) 锁定。
限定后锁定 (LAQ) 是一种优化，它在不获取锁的情况下，使用行的最新提交版本对查询谓词进行评估，从而提高并发性。有关详细信息，请查看限定后锁定 (LAQ)。

例如：

如果不进行优化锁定，更新表中的 1,000 行可能需要保持 1,000 个独占 (X) 行锁，直到事务结束。
通过优化锁定功能，更新表中的 1,000 行可能需要 1,000 个 X 行锁，但每个锁在每行更新后会立即释放，并且只有一个 TID 锁会一直保持到事务结束。由于锁的释放速度很快，因此锁内存使用量会减少，且发生锁升级的可能性要小得多，从而提高工作负载并发性。

注意

启用优化锁定可以减少或消除数据修改语言 (DML) 语句（例如 INSERT、UPDATE、DELETE、MERGE）获取的行锁和页锁。它不会影响其他类型的数据库和对象锁，例如架构锁。

可用性

优化锁定仅在 Azure SQL 数据库和 Microsoft Fabric SQL 数据库中提供，并适用于所有服务层级和计算大小。

优化锁定当前在 Azure SQL 托管实例或 SQL Server 中不可用。

是否已启用优化锁定？

每个用户数据库都启用了优化锁定。连接到数据库，然后使用以下查询检查是否启用了优化锁定：

SELECT IsOptimizedLockingOn = DATABASEPROPERTYEX(DB_NAME(), 'IsOptimizedLockingOn');

Result	说明
`0`	禁用优化锁定。
`1`	启用了优化锁定。
`NULL`	优化锁定功能不可用。

优化锁定建立在其他数据库功能的基础上：

优化锁定要求在数据库上启用加速数据库恢复 (ADR)。
为充分利用优化锁定，应为数据库启用读提交快照隔离 (RCSI)。仅当启用了 RCSI 时，优化锁定的 LAQ 组件才会生效。

Azure SQL 数据库默认启用 ADR 和 RCSI。若要验证是否为当前数据库启用了这些选项，请连接到数据库并运行以下 T-SQL 查询：

SELECT name,
       is_read_committed_snapshot_on,
       is_accelerated_database_recovery_on
FROM sys.databases
WHERE name = DB_NAME();

锁定概述

这是未启用优化锁定时行为的简短摘要。有关详细信息，请查看事务锁定和行版本控制指南。

在数据库引擎中，锁定是一种机制，可防止多个事务同时更新相同的数据，以确保事务的 ACID 属性。

当事务需要修改数据时，它请求数据的锁。如果数据上没有其他冲突锁，则授予该锁，并且事务可以继续进行修改。如果数据上保留其他冲突锁，则事务必须等待该锁释放，然后才能继续。

当多个事务试图同时访问相同数据时，数据库引擎必须解决与并发读取和写入相关的潜在复杂冲突。锁定是数据库引擎可为 ANSI SQL 事务隔离级别提供语义的机制之一。虽然数据库中的锁定必不可少，但减少并发性、死锁、复杂性和锁开销可能会影响性能和可伸缩性。

事务 ID (TID) 锁定

当使用基于行版本控制的隔离级别，或已启用 ADR 时，数据库中的每行在内部都包含一个事务 ID (TID)。此 TID 保留在磁盘上。每个修改行的事务都会使用其 TID 标记该行。

使用 TID 锁定时，不是对行的键锁定，而是对行的 TID 锁定。修改事务会在其 TID 上保留 X 锁。在等到第一个事务完成之前，其他事务在 TID 上获取 S 锁。使用 TID 锁定时，修改时会继续使用页锁和行锁，但修改每行后，每个页锁和行锁都会释放。事务结束前唯一保留的锁是 TID 资源上的单个 X 锁，它取代了多个页锁和行（键）锁。

请考虑以下示例，该示例显示了写入事务处于活动状态时当前会话的锁：

/* Is optimized locking is enabled? */
SELECT IsOptimizedLockingOn = DATABASEPROPERTYEX(DB_NAME(), 'IsOptimizedLockingOn');

CREATE TABLE t0
(
a int PRIMARY KEY,
b int NULL
);

INSERT INTO t0 VALUES (1,10),(2,20),(3,30);
GO

BEGIN TRANSACTION;

UPDATE t0
SET b = b + 10;

SELECT *
FROM sys.dm_tran_locks
WHERE request_session_id = @@SPID
      AND
      resource_type IN ('PAGE','RID','KEY','XACT');

COMMIT TRANSACTION;
GO

DROP TABLE IF EXISTS t0;

如果启用了优化锁定，则请求仅保留 X（事务）资源上的单个 XACT 锁。

如果未启用优化锁定，则同一个请求将持有四个锁 - 包含这些行的页面上的一个 IX（意图独占）锁，以及每行各有三个 X 键锁：

在检查或排除锁定问题（例如，观察优化锁定的实际运作）时，sys.dm_tran_locks 动态管理视图 (DMV) 很有用。

资格审核后锁定 (LAQ)

优化锁定在 TID 基础结构上构建，可更改 DML 语句（如 INSERT、UPDATE 和 DELETE ）获取锁的方式。

如果没有优化锁定，则需先获取更新 (U) 行锁，以便在扫描中逐行检查查询谓词。如果满足谓词，则会在更新行之前获取排他 (X) 行锁，并保留到事务结束。

使用优化锁定并且启用 READ COMMITTED 快照隔离级别 (RCSI) 时，系统可以在行的最新提交版本上乐观地检查谓词，而不执行任何锁定。如果谓词条件不满足，查询将移动到扫描中的下一行。如果满足谓词，则会获取 X 行锁来更新行。

换句话说，在对要修改的行进行资格审核后执行锁定。在事务结束前，行更新完成后，就会释放 X 行锁。

由于谓词评估在不获取任何锁的情况下进行，因此修改不同行的并发查询不会互相阻止。

例如：

CREATE TABLE t1
(
a int NOT NULL,
b int NULL
);

INSERT INTO t1
VALUES (1,10),(2,20),(3,30);
GO

会话 1	会话 2
`BEGIN TRANSACTION;` `UPDATE t1` `SET b = b + 10` `WHERE a = 1;`
	`BEGIN TRANSACTION;` `UPDATE t1` `SET b = b + 10` `WHERE a = 2;`
`COMMIT TRANSACTION;`
	`COMMIT TRANSACTION;`

如果没有优化锁定，则因为会话 1 持有会话 2 需要更新的行上的 U 锁，所以会阻止会话 2。但是，使用优化锁定时，会话 2 不会被阻止，因为没有获取 U 锁。此外，在第 1 行的最新提交版本中，列 a 的值等于 1，这不满足会话 2 的谓词条件。

系统在假设某行在检查谓词后未被修改时乐观地执行 LAQ。如果满足谓词，并且行在检查谓词后未被修改，则由当前事务修改该行。

由于未执行 U 锁定，因此并发事务可能会在计算谓词后修改该行。如果某一活动事务持有行上的 X TID 锁，则数据库引擎将等待该事务完成。如果在先前计算谓词后该行发生更改，则数据库引擎会在修改行之前重新评估（进行重新限定）谓词。如果仍满足谓词，则修改该行。

查询引擎运算符的子集支持谓词重新资格审核。如果需要谓词重新评估，但查询计划使用不支持谓词重新限定的运算符，则数据库引擎在内部中止语句处理，并在没有 LAQ 的情况下重启它。发生此类中止时，将触发 lock_after_qual_stmt_abort 扩展事件。

某些语句（例如 UPDATE 具有变量赋值的语句和具有 OUTPUT 子句的语句）不能中止和重启，而无需更改其语义。对于此类语句，不使用 LAQ。

在以下示例中，将重新计算谓词，因为另一个事务已更改该行：

CREATE TABLE t3
(
a int NOT NULL,
b int NULL
);

INSERT INTO t3 VALUES (1,10),(2,20),(3,30);
GO

会话 1	会话 2
`BEGIN TRANSACTION;` `UPDATE t3` `SET b = b + 10` `WHERE a = 1;`
	`BEGIN TRANSACTION;` `UPDATE t3` `SET b = b + 10` `WHERE a = 1;`
`COMMIT TRANSACTION;`
	`COMMIT TRANSACTION;`

LAQ 启发

如资格审核后锁定 (LAQ) 中所述，使用 LAQ 时，某些语句可能在内部被重启并处理，而不使用 LAQ。如果这种情况经常发生，重复处理的开销可能会变得很大。为了尽量减少这一开销，优化锁定机制使用启发式机制来跟踪重复处理。如果开销超过阈值，此机制将禁用针对数据库的 LAQ 功能。

出于启发式机制的目的，语句完成的工作以已处理的页数（逻辑读取）进行度量。如果数据库引擎正在修改在语句处理开始后被另一个事务修改过的行，则语句执行的操作可能被视为浪费，因为该语句可能会被中止并重新启动。系统跟踪数据库中可能浪费的总工作量和所有语句完成的总工作量。

如果可能浪费的工作百分比超过某个阈值，则会为数据库禁用 LAQ。如果重新启动的语句数超过阈值，则 LAQ 也会被禁用。

如果浪费的工作量和重启语句的数量低于其各自的阈值，则会为数据库重新启用 LAQ。

使用优化锁定和 RCSI 更改查询行为

启用优化锁定时，READ COMMITTED 快照隔离 (RCSI) 下依赖严格事务执行顺序的并发工作负载在查询行为方面可能会出现差异。

请考虑以下示例，其中事务 T2 基于事务 T1 期间更新的列 t4 来更新表 b。

CREATE TABLE t4
(
a int NOT NULL,
b int NULL
);

INSERT INTO t4
VALUES (1,1);
GO

会话 1	会话 2
`BEGIN TRANSACTION T1;` `UPDATE t4` `SET b = 2` `WHERE a = 1;`
	`BEGIN TRANSACTION T2;` `UPDATE t4` `SET b = 3` `WHERE b = 2;`
`COMMIT TRANSACTION;`
	`COMMIT TRANSACTION;`

让我们评估在资格判定后上一个场景在使用和不使用锁定 (LAQ) 情况下的结果。

不使用 LAQ

如果不使用 LAQ，则会阻止事务 T2 中的 UPDATE 语句，并等待事务 T1 完成。 T1 完成后，因为已满足其谓词，所以 T2 会更新该行，将列 b 设置为 3。

两个事务提交后，表 t4 包含以下行：

 a | b
 1 | 3

使用 LAQ

使用 LAQ 时，事务 T2 使用行的最新提交版本，其中列 b 等于 1，以便评估其谓词 (b = 2)。该行不符合条件；因此，它将被跳过，并且语句在不会被事务 T1 阻止的情况下完成。在此示例中，LAQ 消除了阻塞，但会导致不同的结果。

两个事务提交后，表 t4 包含以下行：

 a | b
 1 | 2

重要

即使没有 LAQ，当使用基于行版本控制的隔离级别时，应用程序也不应假定数据库引擎会在没有使用锁定提示的情况下保证严格排序。对于在 RCSI 下运行并发工作负载并依赖严格事务执行顺序（如前面的示例中所示）的客户，我们的一般建议是使用更严格的隔离级别，例如 REPEATABLE READ 和 SERIALIZABLE。

优化锁定的诊断附加功能

以下改进有助于您在启用优化锁定时监控和排查阻塞和死锁问题：

优化锁定的等待类型
- TID 上 XACT 锁的 S 等待类型，以及 sys.dm_os_wait_stats (Transact-SQL) 中的资源描述：
  - LCK_M_S_XACT_READ – 当任务正在等待 XACTwait_resource 类型上的共享锁并打算读取时发生。
  - LCK_M_S_XACT_MODIFY – 当任务正在等待 XACTwait_resource 类型上的共享锁并打算修改时发生。
  - LCK_M_S_XACT - 当任务正在等待 XACTwait_resource 类型的共享锁时，如果无法推断出意图，就会发生这种情况。此方案并不常见。
锁定资源可见性
- XACT 锁定资源。有关更多信息，请参阅“resource_description 中的 ”。
等待资源可见性
- XACT 等待资源。有关更多信息，请参阅“wait_resource 中的 ”。
死锁图
- 在死锁报告 <resource-list> 中的每个资源下，每个 <xactlock> 元素都会报告死锁中每个成员的锁的基础资源和特定信息。有关更多信息和示例，请参阅“优化锁定和死锁”。
扩展事件
- 当一个语句由于与其他事务发生冲突而在内部中止并重新启动时，会触发 lock_after_qual_stmt_abort 事件。有关详细信息，请查看限定后锁定 (LAQ)。

优化锁定的最佳做法

启用读提交快照隔离级别 (RCSI)

为了最大程度地利用优化锁定的优势，建议在数据库上启用 READ COMMITTED 快照隔离 (RCSI)，并使用 READ COMMITTED 隔离作为默认隔离级别。如果尚未启用 RCSI，请通过连接到 master 数据库并执行以下语句来启用：

ALTER DATABASE [your-database-name] SET READ_COMMITTED_SNAPSHOT ON;

在 Azure SQL 数据库中，默认启用 RCSI，并且 READ COMMITTED 是默认隔离级别。在启用 RCSI 的情况下，使用 READ COMMITTED 隔离级别时，读取器从语句开头创建的快照中读取行的版本。使用 LAQ，写入器会根据行的最新提交版本来限定每个谓词的行，而无需获取 U 锁。使用 LAQ 时，仅当行符合条件且该行上有活跃的写入事务时，查询才会等待。根据最新提交的版本进行限定，仅锁定限定行可减少阻塞并提高并发性。

除了减少阻塞之外，所需的锁内存也会减少。这是因为读取器不获取任何锁，而写入器仅获取短期锁，而不是在事务结束前保留的锁。使用更严格的隔离级别（如 REPEATABLE READ 或 SERIALIZABLE）时，数据库引擎即使在启用优化锁定的情况下也会对读取操作和写入操作持有行锁和页锁，直到事务结束，这会导致阻塞和锁定内存使用量增加。

避免锁定提示

启用优化锁定时，尽管遵循了表和查询提示（例如，UPDLOCK、READCOMMITTEDLOCK、XLOCK、HOLDLOCK 等），但会导致无法充分利用优化锁定。锁提示强制数据库引擎执行行锁或页锁，并持有它们，直到事务结束，以符合锁提示的意图。有些应用程序的逻辑需要锁提示，例如，使用 UPDLOCK 提示读取行，然后进行更新时。建议仅在需要时使用锁提示。

使用优化锁定时，现有查询没有限制，不需要重写查询。未使用提示的查询从优化锁定中获益最大。

查询中对一个表使用的表提示不会禁用同一查询中其他表的优化锁定。此外，优化锁定仅影响 DML 语句（例如，INSERT、UPDATE、DELETE 或 MERGE）更新的表的锁定行为。例如：

CREATE TABLE t5
(
a int NOT NULL,
b int NOT NULL
);

CREATE TABLE t6
(
a int NOT NULL,
b int NOT NULL
);
GO

INSERT INTO t5 VALUES (1,10),(2,20),(3,30);
INSERT INTO t6 VALUES (1,10),(2,20),(3,30);
GO

UPDATE t5 SET t5.b = t6.b
FROM t5
INNER JOIN t6 WITH (UPDLOCK)
ON t5.a = t6.a;

在前面的查询示例中，只有表 t6 会受到锁定提示的影响，而 t5 仍可以利用优化锁定。

UPDATE t5
SET t5.b = t6.b
FROM t5 WITH (REPEATABLEREAD)
INNER JOIN t6
ON t5.a = t6.a;

在前面的查询示例中，只有表 t5 使用 REPEATABLE READ 隔离级别，并且将锁保留到事务结束。其他更新 t5 仍可以利用优化锁定。这同样适用于 HOLDLOCK 提示。

常见问题 (FAQ)

新数据库和现有数据库中是否默认启用优化锁定？

在 Azure SQL 数据库中，可以。

如何检测是否已启用优化锁定？

请参阅是否已启用优化锁定？。

数据库上未启用加速数据库恢复 (ADR) 时会发生什么情况？

如果禁用 ADR，优化锁定也会自动禁用。

如果我想在优化锁定的情况下强制阻止查询，该怎么办？

对于使用 RCSI 的客户，要在启用优化锁定的情况下强制阻止两个查询，请使用 READCOMMITTEDLOCK 查询提示。

在只读次要副本上是否使用了优化锁定？

否，因为 DML 语句无法在只读副本上运行，因此不会获取相应的行锁和页锁。

修改 tempdb 和临时表中的数据时是否使用了优化锁定？

目前没有。

通过

优化锁定

什么是优化锁定？

可用性

是否已启用优化锁定？

锁定概述

事务 ID (TID) 锁定

资格审核后锁定 (LAQ)

LAQ 启发

使用优化锁定和 RCSI 更改查询行为

优化锁定的诊断附加功能

优化锁定的最佳做法

启用读提交快照隔离级别 (RCSI)

避免锁定提示

常见问题 (FAQ)

新数据库和现有数据库中是否默认启用优化锁定？

如何检测是否已启用优化锁定？

数据库上未启用加速数据库恢复 (ADR) 时会发生什么情况？

如果我想在优化锁定的情况下强制阻止查询，该怎么办？

在只读次要副本上是否使用了优化锁定？

修改 tempdb 和临时表中的数据时是否使用了优化锁定？

反馈

其他资源

通过

优化锁定

什么是优化锁定？

可用性

是否已启用优化锁定？

锁定概述

事务 ID (TID) 锁定

资格审核后锁定 (LAQ)

LAQ 启发

使用优化锁定和 RCSI 更改查询行为

优化锁定的诊断附加功能

优化锁定的最佳做法

启用读提交快照隔离级别 (RCSI)

避免锁定提示

常见问题 (FAQ)

新数据库和现有数据库中是否默认启用优化锁定？

如何检测是否已启用优化锁定？

数据库上未启用加速数据库恢复 (ADR) 时会发生什么情况？

如果我想在优化锁定的情况下强制阻止查询，该怎么办？

在只读次要副本上是否使用了优化锁定？

修改 tempdb 和临时表中的数据时是否使用了优化锁定？

相关内容

反馈

其他资源