优化锁定

项目
10/29/2023

适用于：Azure SQL 数据库

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

什么是优化锁定？

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

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

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

例如：

如果没有优化锁定，更新表中的 100 万行可能需要保留 100 万个独占 (X) 行锁，直到事务结束。
通过优化锁定，更新表中的 100 万行可能需要 100 万个 X 行锁，但每行更新后，每个锁都会立即释放，只有一个 TID 锁会一直保留，直到事务结束。

本文详细介绍了优化锁定的这两个核心概念。

可用性

目前，优化锁定仅在 Azure SQL 数据库中可用。有关详细信息，请参阅优化锁定当前在哪里可用？

是否已启用优化锁定？

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

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

如果未连接到 DATABASEPROPERTYEX 中指定的数据库，则结果为 NULL。你应该会收到 0（优化锁定已禁用）或 1（已启用）。

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

优化锁定要求在数据库上启用加速数据库恢复 (ADR)。
为充分利用优化锁定，应为数据库启用读提交快照隔离 (RCSI)。

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();

锁定概述

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

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

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

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

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

当使用行版本控制时，数据库引擎中的每行内部都包含一个事务 ID (TID)。此 TID 保留在磁盘上。每个修改行的事务都会使用其 TID 标记该行。

使用 TID 锁定时，不是对行的键锁定，而是对行的 TID 锁定。修改事务将在其 TID 上保留 X 锁。其他事务将获取 TID 上的 S 锁，以检查第一个事务是否仍处于活动状态。使用 TID 锁定时，更新时将继续使用页锁和行锁，但每行更新后，每个页锁和行锁都会释放。事务结束前唯一保留的锁是 TID 资源上的 X 锁，它取代了页和行（键）锁，如下一个演示所示。（其他标准数据库和对象锁不受优化锁定的影响。）

优化锁定有助于减少锁内存，因为大型事务保留的锁很少。此外，优化锁定还可以避免锁升级。这允许其他并发事务访问表。

请考虑以下在用户当前会话上查找锁的 T-SQL 示例场景：

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

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

BEGIN TRAN
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 TRAN
GO
DROP TABLE IF EXISTS t0;

同样的查询，如果没有优化锁定，就会产生四个锁：

sys.dm_tran_locks 动态管理视图 (DMV) 可用于检查或排除锁定问题，包括观察优化锁定的实际操作。

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

在 TID 基础结构的基础上，优化锁定改变了查询谓词安全锁的方式。

如果没有优化锁定，则需先获取更新 (U) 行锁，在扫描中逐行检查查询谓词。如果满足谓词，则会在更新行之前获取 X 行锁。

使用优化锁定，并且启用读提交快照隔离级别 (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 TRAN` `UPDATE t1` `SET b=b+10` `WHERE a=1;`
	`BEGIN TRAN` `UPDATE t1` `SET b=b+10` `WHERE a=2;`
`COMMIT TRAN`
	`COMMIT TRAN`

在前面的示例中，阻塞行为会因优化锁定而改变。如果没有优化锁定，会话 2 将被阻塞。

但是，有了优化锁定，会话 2 将不会被阻塞，因为行 1 的最新提交版本包含 a=1，不满足会话 2 的谓词。

如果满足谓词，则等待行上的任何活动事务完成。如果必须等待 S TID 锁，该行可能已更改，而最新提交的版本也可能已更改。在这种情况下，数据库引擎不会因为更新冲突而中止事务，而是在同一行上重试谓词评估。如果谓词在重试时限定，则将更新该行。

当自动重试谓词更改时，请考虑以下示例：

CREATE TABLE t2
(a int not null
,b int null);

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

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

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

启用优化锁定时，读提交快照隔离级别 (RCSI) 下的并发系统以及依赖严格事务执行顺序的工作负载可能会出现不同的查询行为。

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

CREATE TABLE t1 (a int not null, b int null);

INSERT INTO t1 VALUES (1,1);
GO

会话 1	会话 2
BEGIN TRAN T1 UPDATE t1 SET b=2 WHERE a=1;
	BEGIN TRAN T2 UPDATE t1 SET b=3 WHERE b=2;
COMMIT TRAN
	COMMIT TRAN

我们来评估使用和不使用限定后锁定 (LAQ)（优化锁定的一个组成部分）的情况下，上述场景的结果。

不使用 LAQ

如果不使用 LAQ，事务 T2 将被阻塞，并等待事务 T1 完成。

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

 a | b
 1 | 3

使用 LAQ

使用 LAQ 时，事务 T2 将使用行 b 的最新提交版本（版本存储中 b =1）来评估其谓词 (b =2)。此行未限定；因此它将被跳过，T2 移动至下一行，而不会被事务 T1 阻塞。在此示例中，LAQ 消除了阻塞，但会导致不同的结果。

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

 a | b
 1 | 2

重要

即使没有 LAQ，应用程序也不会假设 SQL Server（在版本控制隔离级别下）在不使用锁定提示的情况下，保障严格排序。对于 RCSI 下并发系统的客户，我们的一般建议是使用更严格的隔离级别，这些工作负载依赖于严格的事务执行顺序（如前面的练习所示）。

优化锁定的诊断附加功能

要通过优化锁定支持阻塞和死锁的监视和故障排除，请查找以下附加功能：

优化锁定的等待类型
- sys.dm_os_wait_stats (Transact-SQL) 中的 XACT 等待类型和资源描述：
  - 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)

为了最大程度地提高优化锁定的优势，建议在数据库上启用读提交快照隔离 (RCSI)，并使用读提交隔离作为默认隔离级别。如果未启用，请使用以下示例启用 RCSI：

ALTER DATABASE databasename SET READ_COMMITTED_SNAPSHOT ON;

在 Azure SQL 数据库中，RCSI 默认启用，读提交是默认隔离级别。启用 RCSI 并且使用读提交隔离级别时，读取器不会阻止写入器，写入器也不会阻止读取器。读取器从查询开始时获取的快照中读取该行的版本。使用 LAQ，写入器将根据行的最新提交版本来限定每个谓词的行，而无需获取 U 锁。使用 LAQ，只有当行限定并且该行上存在活动的写入事务时，查询才会等待。根据最新提交的版本进行限定，仅锁定限定行可减少阻塞并提高并发性。

除了减少阻塞之外，所需的锁内存也会减少。这是因为读取器不获取任何锁，而写入器仅获取短期锁，而不是在事务结束时过期的锁。如果使用更严格的隔离级别（如可重复读取或可序列化），数据库引擎将强制为读取器和写入器保留行锁和页锁，直到事务结束，从而导致阻塞和锁内存增加。

避免锁定提示

尽管遵循表和查询提示，但它们会减少优化锁定的优势。查询中的锁提示（如 UPDLOCK、READCOMMITTEDLOCK、XLOCK、HOLDLOCK 等）会减少优化锁定的优势。在查询中使用此类锁提示会强制数据库引擎获取行锁/页锁，并将它们保留到事务结束，以实现锁提示的意图。有些应用程序的逻辑需要锁提示，例如，使用 UPDLOCK 读取带有 select 的行，然后更新它时。建议仅在需要时使用锁提示。

使用优化锁定，对现有查询没有任何限制，也不需要重写查询。不使用提示的查询主要使用优化锁定。

查询中某个表的表提示不会对同一查询中的其他表禁用优化锁定。此外，优化锁定仅影响 UPDATE 语句更新的表的锁定行为。例如：

CREATE TABLE t3
(a int not null
, b int not null);

CREATE TABLE t4
(a int not null
, b int not null);
GO
INSERT INTO t3 VALUES (1,10),(2,20),(3,30);
INSERT INTO t4 VALUES (1,10),(2,20),(3,30);
GO

UPDATE t3 SET t3.b = t4.b
FROM t3
INNER JOIN t4 WITH (UPDLOCK) ON t3.a = t4.a;

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

UPDATE t3 SET t3.b = t4.b
FROM t3 WITH (REPEATABLEREAD)
INNER JOIN t4 ON t3.a = t4.a;

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

常见问题 (FAQ)

优化锁定当前在哪里可用？

目前，在 Azure SQL 数据库中可以使用优化锁定。

优化锁定在以下服务层级中可用：

所有 DTU 服务层级
所有 vCore 服务层级，包括预配和无服务器

优化锁定当前在哪里不可用：

Azure SQL 托管实例
SQL Server 2022 (16.x)

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

在 Azure SQL 数据库中，可以。

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

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

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

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

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

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

是否可以禁用优化锁定？

当前，客户可以创建支持请求来禁用优化锁定。

使用以下步骤在 Azure 门户中为 Azure SQL 数据库创建新的支持请求。

首先，验证是否已为数据库启用优化锁定。
在 Azure 门户菜单中，选择“帮助 + 支持”。
在“帮助 + 支持”中，选择“创建支持请求”。
对于“问题类型”，选择“技术”。
对于订阅、服务和资源，选择所需的“SQL 数据库”。
在摘要中，键入“禁用优化锁定”。
对于问题类型，选择“性能和查询执行”。
对于问题子类型，选择“阻止和死锁”。
在其他详细信息中，提供尽可能多的信息，说明为什么要禁用优化锁定。我们有兴趣与您一起回顾禁用优化锁定的原因和用例。

优化锁定

什么是优化锁定？

可用性

是否已启用优化锁定？

锁定概述

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

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

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

优化锁定的诊断附加功能

优化锁定的最佳做法

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

避免锁定提示

常见问题 (FAQ)

优化锁定当前在哪里可用？

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

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

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

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

是否可以禁用优化锁定？

反馈

反馈

其他资源

优化锁定

什么是优化锁定？

可用性

是否已启用优化锁定？

锁定概述

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

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

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

优化锁定的诊断附加功能

优化锁定的最佳做法

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

避免锁定提示

常见问题 (FAQ)

优化锁定当前在哪里可用？

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

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

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

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

是否可以禁用优化锁定？

相关内容

反馈

反馈

其他资源