优化锁定

项目
11/19/2024

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

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

什么是优化锁定？

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

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

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

例如：

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

注意

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

可用性

优化锁定仅在Azure SQL 数据库和 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;

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

如果未启用优化锁定，则同一个请求会保留四个锁 - 每行有三个 X 键锁，包含行的页面上有一个 IX（意向排他）锁：

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

优化锁定和限定后锁定 (LAQ)

优化锁定基于 TID 基础结构而构建，改变了 DML 语句（例如 INSERT、UPDATE、DELETE 和 MERGE）获取锁的方式。

如果没有优化锁定，则需先获取更新 (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。但是，因为没有获取 U 锁，并且因为在行 1 的最新提交版本中，列 a 等于 1，不满足会话 2 的谓词，所以如果有优化锁定，就不会阻止会话 2。

由于没有取得 LAQ U 锁，因此在某个并发事务评估了谓词之后，该事务可能会修改该行。如果满足谓词且该行上没有其他活动事务（无 X TID 锁），则修改该行。因为其他事务可能已修改该行，所以如果存在活动事务，则数据库引擎会等待该事务完成，并在修改时再次重新评估谓词。如果仍满足谓词，则修改该行。

请考虑以下示例，因为另一个事务已更改该行，所以系统会自动重试谓词评估：

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;`

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

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

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

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 上 S 锁的 XACT 等待类型，以及 sys.dm_os_wait_stats (Transact-SQL) 中的资源描述：
  - LCK_M_S_XACT_READ – 当任务正在等待 XACT wait_resource 类型上的共享锁并打算读取时发生。
  - LCK_M_S_XACT_MODIFY – 当任务正在等待 XACT wait_resource 类型上的共享锁并打算修改时发生。
  - LCK_M_S_XACT – 当任务正在等待 XACT wait_resource 类型上的共享锁且意图不明时发生。这并不常见。
锁定资源可见性
- XACT 锁定资源。有关更多信息，请参阅“sys.dm_tran_locks (Transact-SQL) 中的 resource_description”。
等待资源可见性
- XACT 等待资源。有关更多信息，请参阅“sys.dm_exec_requests (Transact-SQL) 中的 wait_resource”。
死锁图
- 在死锁报告 <resource-list> 中的每个资源下，每个 <xactlock> 元素都会报告死锁中每个成员的锁的基础资源和特定信息。有关更多信息和示例，请参阅“优化锁定和死锁”。

优化锁定的最佳做法

启用读提交快照隔离级别 (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)

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

优化锁定的诊断附加功能

优化锁定的最佳做法

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

避免锁定提示

常见问题 (FAQ)

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

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

如果我的数据库未启用加速数据库恢复 (ADR)，会发生什么？

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

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

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

反馈

其他资源

通过

优化锁定

什么是优化锁定？

可用性

是否已启用优化锁定？

锁定概述

优化锁定和事务 ID (TID) 锁定

优化锁定和限定后锁定 (LAQ)

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

优化锁定的诊断附加功能

优化锁定的最佳做法

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

避免锁定提示

常见问题 (FAQ)

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

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

如果我的数据库未启用加速数据库恢复 (ADR)，会发生什么？

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

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

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

相关内容

反馈

其他资源