适用于：
SQL ServerAzure SQL 数据库Azure SQL 托管实例Azure Synapse AnalyticsAnalytics Platform System (PDW)Microsoft Fabric SQL 数据库
在任何数据库中，事务管理不当通常会在具有许多用户的系统中导致争用和性能问题。 随着访问数据的用户数量增加，让应用程序高效使用事务变得重要。 本指南介绍数据库引擎用于确保每个事务完整性的锁定和行版本控制机制，并提供有关应用程序如何高效控制事务的信息。
注意
优化锁定是数据库引擎在 2023 年引入的功能，可大幅减少锁内存和并发写入所需的锁数量。 本文更新为介绍带有和不带有优化锁定情况下的数据库引擎的行为。
- 若要了解详细信息，并了解可在何处使用优化锁定，请参阅优化锁定。
- 要确定是否已在数据库上启用优化锁定，请参阅是否已启用优化锁定？
引入优化锁定后，本文的某些部分发生了重大更改，包括：
事务基本知识
事务是作为单个逻辑工作单元执行的一系列操作。 一个逻辑工作单元必须具备四个属性（称为原子性、一致性、隔离性和持久性 (ACID) 属性）才能符合事务的条件。
原子性
事务必须是一个原子工作单元；其所有数据修改要么全部执行，要么都不执行。
一致性
完成后，事务必须使所有数据处于一致状态。 在关系数据库中，必须对事务的修改应用所有规则以维护所有数据完整性。 事务结束时，所有的内部数据结构（如 B 树索引或双向链接列表）都必须是正确的。
注意
文档中通常使用术语“B 树”来引用索引。 在行存储索引中，数据库引擎实现 B+ 树。 这不适用于列存储索引或内存优化表上的索引。 有关详细信息，请参阅 SQL Server 以及 Azure SQL 索引体系结构和设计指南。
隔离性
并发事务所做的修改必须与任何其他并发事务所做的修改隔离。 事务识别数据时数据所处的状态，要么是另一并发事务修改它之前的状态，要么是第二个事务修改它之后的状态，事务不会识别中间状态的数据。 这被称为可序列化，因为它导致能够重新加载起始数据并重新执行一系列事务，最终使数据处于与执行原始事务后相同的状态。
持久性
完全持久的事务完成后，其效果会永久保留在系统中。 即使系统发生故障，修改也会保留。 SQL Server 2014 (12.x) 及更高版本启用延迟的持久事务。 延迟持久事务在事务日志记录持久保存到磁盘之前提交。 有关延迟事务持续性的详细信息，请参阅文章控制事务持续性。
应用程序负责在强制数据逻辑一致性的点开始和结束事务。 应用程序必须定义数据修改的序列，使数据相对于组织的业务规则处于一致状态。 应用程序在单个事务中执行这些修改，以便数据库引擎可以强制实施事务的完整性。
企业数据库系统（如数据库引擎的实例）有责任提供确保每个事务完整性的机制。 数据库引擎提供：
保留事务隔离的锁定工具。
确保事务持久性的日志记录工具。 对于完全持久的事务，日志记录会在事务提交前固化到磁盘。 因此，即使服务器硬件、操作系统或数据库引擎实例本身失败，实例也会在重启时使用事务日志自动将任何未完成的事务回滚到系统故障点。 延迟持久事务在事务日志记录固化到磁盘之前提交。 如果在日志记录固化到磁盘之前发生系统故障，此类事务可能会丢失。 有关延迟事务持续性的详细信息，请参阅文章控制事务持续性。
强制实施事务原子性和一致性的事务管理功能。 事务开始后，它必须成功完成（提交），或者数据库引擎会撤销事务自开始以来所做的所有数据修改。 此操作称为回滚事务，因为它将数据返回到这些更改之前的状态。
控制事务
应用程序主要通过指定事务开始和结束的时间来控制事务。 可以使用 Transact-SQL 语句或数据库应用程序编程接口 (API) 函数来指定这些时间。 系统还必须能够正确处理在事务完成前终止它的错误。 有关详细信息，请参阅事务、在 ODBC 中执行事务以及 SQL Server Native Client 中的事务。
默认情况下，事务会在连接级别管理。 在一个连接上启动一个事务后，该事务结束之前，在该连接上执行的所有 Transact-SQL 语句都是该事务的一部分。 但是，在多重活动结果集 (MARS) 会话中，Transact-SQL 显式或隐式事务将变成批范围的事务，这种事务按批处理级别进行管理。 当批处理完成时，如果批范围的事务未提交或回滚，数据库引擎会自动回滚它。 有关详细信息，请参阅使用多重活动结果集 (MARS)。
启动事务
使用 API 函数和 Transact-SQL 语句，可以将事务启动为显式、自动提交或隐式事务。
显式事务
显式事务是指通过 API 函数或发出 Transact-SQL BEGIN TRANSACTION、COMMIT TRANSACTION、COMMIT WORK、ROLLBACK TRANSACTION 或 ROLLBACK WORK Transact-SQL 语句显式定义事务开始和结束的事务。 事务结束时，连接返回到显式事务开始前的事务模式，这可能是隐式或自动提交模式。
你可以使用显式事务中除以下语句之外的所有 Transact-SQL 语句：
CREATE DATABASEALTER DATABASEDROP DATABASECREATE FULLTEXT CATALOGALTER FULLTEXT CATALOGDROP FULLTEXT CATALOGDROP FULLTEXT INDEXALTER FULLTEXT INDEXCREATE FULLTEXT INDEXBACKUPRESTORERECONFIGURE- 全文系统存储过程
-
sp_dboption，在显式或隐式事务内设置数据库选项或修改master数据库的任何系统过程。
注意
UPDATE STATISTICS 可以在显式事务内使用。 但是，UPDATE STATISTICS 独立于外围事务提交，无法回滚。
自动提交事务
自动提交模式是数据库引擎的默认事务管理模式。 每个 Transact-SQL 语句完成时提交或回滚。 如果语句成功完成，则提交；如果遇到任何错误，则回滚。 只要此默认模式未被显式或隐式事务所覆盖，到数据库引擎实例的连接就以自动提交模式运行。 自动提交模式也是 SqlClient、ADO、OLE DB 和 ODBC 的默认模式。
隐式事务
当连接以隐式事务模式运行时，数据库引擎实例在当前事务提交或回滚后自动启动新事务。 无需做任何事情来划定事务的开始；只需提交或回滚每个事务。 隐式事务模式生成连续的事务链。 通过 API 函数或 Transact-SQL SET IMPLICIT_TRANSACTIONS ON 语句启用隐式事务模式。 此模式也称为 Autocommit OFF，请参阅 setAutoCommit Method (SQLServerConnection)。
为连接启用隐性事务模式后，数据库引擎实例会在首次执行下列任意语句时自动启动一个事务：
ALTER TABLECREATEDELETEDENYDROPFETCHGRANTINSERTOPENREVOKESELECTTRUNCATEUPDATE
批范围事务
只能应用于多个活动结果集 (MARS)，在 MARS 会话中启动的 Transact-SQL 显式或隐式事务变为批处理级事务。 批处理完成时未提交或回滚的批范围事务由数据库引擎自动回滚。
分布式事务
分布式事务跨越两个或多个称为资源管理器的服务器。 事务的管理必须由称为事务管理器的服务器组件在资源管理器之间协调。 数据库引擎的每个实例都可以在由事务管理器（如 Microsoft 分布式事务协调器 (MS DTC) 或其他支持分布式事务处理的 Open Group XA 规范的事务管理器）协调的分布式事务中充当资源管理器。 有关详细信息，请参阅 MS DTC 文档。
数据库引擎单个实例内跨越两个或多个数据库的事务是分布式事务。 该实例对分布式事务进行内部管理；对于用户而言，其操作就像本地事务一样。
在应用程序中，分布式事务的管理与本地事务大致相同。 当事务结束时，应用程序会请求提交或回滚事务。 不同的是，分布式提交必须由事务管理器管理，以尽量避免出现因网络故障而导致事务由某些资源管理器成功提交，但由另一些资源管理器回滚的情况。 通过分两个阶段（准备阶段和提交阶段）管理提交进程可避免这种情况，这称为两阶段提交。
准备阶段
当事务管理器收到提交请求时，它会向该事务涉及的所有资源管理器发送准备命令。 然后，每个资源管理器执行使事务持久化所需的所有操作，并且事务的所有事务日志缓冲区都刷新到磁盘。 每个资源管理器在完成准备阶段后，都会向事务管理器返回该阶段成功或失败的消息。 SQL Server 2014 (12.x) 引入了延迟事务持续性。 延迟持久事务在每个资源管理器上的事务日志缓冲区刷新到磁盘之前提交。 有关延迟事务持续性的详细信息，请参阅文章控制事务持续性。
提交阶段
如果事务管理器从所有资源管理器收到准备成功的消息，它将向每个资源管理器发送一个提交命令。 然后，资源管理器就可以完成提交。 如果所有资源管理器都报告提交成功，那么事务管理器就会向应用程序发送一个成功通知。 如果任一资源管理器报告准备失败，那么事务管理器将向每个资源管理器发送一个回滚命令，并向应用程序表明提交失败。
数据库引擎应用程序可以通过 Transact-SQL 或数据库 API 管理分布式事务。 有关详细信息，请参阅 BEGIN DISTRIBUTED TRANSACTION (Transact-SQL)。
结束事务
你可以使用 COMMIT 或 ROLLBACK 语句，或者通过相应 API 函数来结束事务。
Commit
如果事务成功，则将其提交。
COMMIT语句保证事务的所有修改都成为数据库的永久部分。 提交还会释放事务使用的资源，如锁。回滚
如果事务中发生错误，或者用户决定取消事务，则回滚事务。
ROLLBACK语句通过将数据返回到事务开始时的状态来取消事务中所做的所有修改。 回滚还会释放事务持有的资源。
注意
在多个活动结果集 (MARS) 会话上，通过 API 函数启动的显式事务在有挂起的执行请求时无法提交。 当有正在执行的请求时，任何尝试提交此类交易的操作都会导致错误。
事务处理期间的错误
如果错误阻止事务成功完成，数据库引擎会自动回滚事务并释放事务持有的所有资源。 如果到数据库引擎实例的客户端网络连接断开，当网络通知实例连接断开时，该连接的任何未完成事务都会回滚。 如果客户端应用程序失败，或者客户端计算机停机或重启，这也会断开连接，并且当网络通知实例连接断开时，数据库引擎实例会回滚任何未完成的事务。 如果客户端与数据库引擎断开连接，任何未完成的事务都会回滚。
如果批处理中发生运行时语句错误（如违反约束），数据库引擎中的默认行为是仅回滚生成错误的语句。 可以使用 SET XACT_ABORT ON 语句更改此行为。 在执行 SET XACT_ABORT ON 后，任何运行时语句错误都会导致当前事务自动回滚。 编译错误（如语法错误）不受 SET XACT_ABORT 影响。 有关详细信息，请参阅 SET XACT_ABORT (Transact-SQL)。
在发生错误时，应用程序代码中应包含适当的操作（COMMIT 或 ROLLBACK）。 处理错误（包括那些事务中的错误）的一种有效工具是 Transact-SQL TRY...CATCH 构造。 有关包括事务的示例的详细信息，请参阅 TRY...CATCH (Transact-SQL)。 从 SQL Server 2012 (11.x) 开始，可使用 THROW 语句引发异常并将执行转移到 CATCH 构造的 TRY...CATCH 块。 有关详细信息，请参阅 THROW (Transact-SQL)。
自动提交模式下的编译和运行时错误
在自动提交模式下，有时看起来数据库引擎实例回滚了整个批处理，而不仅仅是一个 SQL 语句。 如果遇到的错误是编译错误而不是运行时错误，就会发生这种情况。 编译错误会阻止数据库引擎生成执行计划，因此批处理中的任何内容都无法执行。 尽管看起来生成错误之前的所有语句都被回滚，但实际上是错误阻止了批处理中的任何内容被执行。 在以下示例中，由于编译错误，第三个批处理中的任何 INSERT 语句都不会执行。 看起来前两个 INSERT 语句被回滚，而实际上它们从未执行。
CREATE TABLE TestBatch (ColA INT PRIMARY KEY, ColB CHAR(3));
GO
INSERT INTO TestBatch VALUES (1, 'aaa');
INSERT INTO TestBatch VALUES (2, 'bbb');
INSERT INTO TestBatch VALUSE (3, 'ccc'); -- Syntax error.
GO
SELECT * FROM TestBatch; -- Returns no rows.
GO
在以下示例中，第三个 INSERT 语句生成运行时主键重复错误。 前两个 INSERT 语句成功并提交，因此在运行时错误后仍然存在。
CREATE TABLE TestBatch (ColA INT PRIMARY KEY, ColB CHAR(3));
GO
INSERT INTO TestBatch VALUES (1, 'aaa');
INSERT INTO TestBatch VALUES (2, 'bbb');
INSERT INTO TestBatch VALUES (1, 'ccc'); -- Duplicate key error.
GO
SELECT * FROM TestBatch; -- Returns rows 1 and 2.
GO
数据库引擎使用延迟名称解析，其中对象名称在执行时解析，而不是在编译时解析。 在下面的示例中，执行并提交了前两个 INSERT 语句，在第三个 TestBatch 语句由于引用一个不存在的表而产生运行时错误之后，这两行仍然保留在 INSERT 表中。
CREATE TABLE TestBatch (ColA INT PRIMARY KEY, ColB CHAR(3));
GO
INSERT INTO TestBatch VALUES (1, 'aaa');
INSERT INTO TestBatch VALUES (2, 'bbb');
INSERT INTO TestBch VALUES (3, 'ccc'); -- Table name error.
GO
SELECT * FROM TestBatch; -- Returns rows 1 and 2.
GO
锁定和行版本控制基本知识
数据库引擎使用以下机制来确保事务的完整性，并在多个用户同时访问数据时维护数据库的一致性：
锁定
每个事务请求对其依赖的资源（如行、页或表）的不同类型的锁。 锁可以阻止其他事务以某种可能会导致事务请求锁出错的方式修改资源。 每个事务在不再依赖锁定资源时释放其锁。
行版本控制
当使用基于行版本控制的隔离级别时，数据库引擎维护每个被修改行的版本。 应用程序可以指定事务使用行版本来查看事务或语句开始时存在的数据，而不是用锁保护所有读取。 通过使用行版本控制，读取操作阻止其他事务的可能性会大大降低。
锁定和行版本控制可防止用户读取未提交的数据，并防止多个用户尝试同时更改相同的数据。 如果不进行锁定或行版本控制，对数据执行的查询可能会返回数据库中尚未提交的数据，从而产生意外的结果。
应用程序可以选择事务隔离级别，这些隔离级别定义事务免受其他事务所做修改影响的保护级别。 可以为单个 Transact-SQL 语句指定表级提示，以进一步调整行为以适应应用程序的要求。
管理并发数据访问
同时访问资源的用户称为并发访问资源。 并发数据访问需要机制来防止多个用户尝试修改其他用户正在积极使用的资源时产生不利影响。
并发影响
修改数据的用户会影响同时读取或修改相同数据的其他用户。 这些用户被称为并发访问数据。 如果数据库没有并发控制，用户可能会看到以下副作用：
更新丢失
当两个或多个事务选择同一行，然后基于最初选择的值更新该行时，会发生更新丢失。 每个事务都不知道其他事务。 最后一次更新覆盖其他事务所做的更新，从而导致数据丢失。
例如，两位编辑制作同一文档的电子副本。 每位编辑独立更改副本，然后保存更改的副本，从而覆盖原始文档。 最后保存更改副本的编辑会覆盖另一位编辑所做的更改。 如果一位编辑在另一位编辑完成并提交事务之前无法访问文件，就可以避免此问题。
未提交依赖项（脏读）
当第二个事务读取正在被另一个事务更新的行时，会发生未提交依赖项。 第二个事务正在读取尚未提交且可能被更新该行的事务所更改的数据。
例如，一位编辑人员正在对电子文档进行更改。 在更改期间，第二位编辑人员获取该文档的副本，其中包含到目前为止所做的所有更改，并将该文档分发给目标受众。 然后第一位编辑人员认为到目前为止所做的更改是错误的，删除编辑并保存文档。 分发的文档包含不再存在的编辑内容，应视为从未存在过。 如果在第一位编辑完成修改的最终保存并提交事务之前，没有人可以读取更改的文档，就可以避免此问题。
不一致分析（不可重复读）
当第二个事务多次访问同一行并每次读取不同数据时，会发生不一致分析。 不一致分析与未提交依赖项类似，因为另一个事务正在更改第二个事务正在读取的数据。 但是，在不一致分析中，第二个事务读取的数据已由进行更改的事务提交。 此外，不一致分析涉及对同一行的多次读取（两次或更多次），并且每次信息都被另一个事务所更改；因此，术语为“不可重复读”。
例如，一位编辑人员两次读取同一文档，但在每次读取之间，作者重写了文档。 当编辑人员第二次读取文档时，它已被更改。 原始读取不可重复。 如果作者在编辑人员最后一次读完文档之前不能更改文档，就可以避免此问题。
幻读
幻读是指执行两个相同的查询且第二个查询返回的行集不同的情况。 以下示例演示了这种情况是如何发生的。 假设两个事务同时执行。 由于第二个事务中的
INSERT语句更改了两个事务所用的数据，所以第一个事务中的两个SELECT语句可能返回不同的结果。--Transaction 1 BEGIN TRAN; SELECT ID FROM dbo.employee WHERE ID > 5 AND ID < 10; --The INSERT statement from the second transaction occurs here. SELECT ID FROM dbo.employee WHERE ID > 5 and ID < 10; COMMIT;--Transaction 2 BEGIN TRAN; INSERT INTO dbo.employee (Id, Name) VALUES(6 ,'New'); COMMIT;由行更新导致的缺失和重复读取
错过更新的行或多次看到更新的行
在
READ UNCOMMITTED级别运行的事务（或使用NOLOCK表提示的语句）不发出共享锁来防止其他事务修改当前事务读取的数据。 在READ COMMITTED级别运行的事务确实会发出共享锁，但行锁或页锁会在读取行后释放。 无论哪种情况，在你扫描索引时，如果另一个用户在你读取期间更改行的索引键列，则在键更改将行移至你的扫描位置之前的位置时，该行可能会再次出现。 类似地，如果键更改将行移动到索引中已读取的位置，则可能根本不会读取该行。 为避免这种情况，请使用SERIALIZABLE或HOLDLOCK提示，或行版本控制。 有关详细信息，请参阅表提示 (Transact-SQL)。错过一个或多个非更新目标的行
当使用
READ UNCOMMITTED时，如果查询使用分配顺序扫描（使用 IAM 页）读取行，则如果另一个事务导致页拆分，可能会错过行。 使用READ COMMITTED隔离级别时不会发生这种情况。
并发类型
当多个事务尝试同时修改数据库中的数据时，必须实现一个控制系统，以便一个事务所做的修改不会对另一个事务的修改产生不利影响。 这称为并发控制。
并发控制理论对建立并发控制的方法有两种分类：
悲观并发控制
锁系统可防止事务以影响其他事务的方式修改数据。 如果某个事务执行的操作导致应用了某个锁，只有这个锁的所有者释放该锁，其他事务才能执行与该锁冲突的操作。 这称为悲观控制，因为它通常用于数据争用高的系统，其中用锁保护数据的成本低于发生并发冲突时回滚事务的成本。
乐观并发控制
在乐观并发控制中，事务读取数据时不锁定数据。 但是，当事务更新数据时，系统会检查是否有另一个事务在数据被读取后更改了数据。 如果另一个事务更新了数据，则引发错误。 通常，收到错误的事务回滚并重新开始。 这称为乐观，因为它通常用于数据争用低的系统，其中偶尔回滚事务的成本低于读取时锁定数据的成本。
数据库引擎支持这两种并发控制方法。 用户通过为连接选择事务隔离级别或游标上的并发选项来指定并发控制的类型。 这些特性可以使用 Transact-SQL 语句或通过数据库应用程序编程接口（API，如 ADO、ADO.NET、OLE DB 和 ODBC）的属性和特性来定义。
数据库引擎中的隔离级别
事务指定隔离级别，该级别定义一个事务必须与其他事务所做的资源或数据修改隔离的程度。 隔离级别根据允许哪些并发副作用（如脏读或幻读）来描述。
事务隔离级别控制：
- 读取数据时是否获取锁以及所请求的锁类型。
- 占用读取锁的时间。
- 引用其他事务修改的行的读取操作是否：
- 阻塞直到行上的排他锁被释放。
- 检索语句或事务开始时存在的行的已提交版本。
- 读取未提交的数据修改。
重要说明
选择事务隔离级别不影响为保护数据修改而获取的锁。 无论为该事务设置的隔离级别如何，事务始终持有排他锁来执行数据修改，并持有该锁直到事务完成。 对于读取操作，事务隔离级别主要定义保护级别，以防受到其他事务所做更改的影响。
较低的隔离级别增加了许多事务同时访问数据的能力，但也增加了事务可能遇到的并发副作用（如脏读或更新丢失）的数量。 相反，较高的隔离级别减少了事务可能遇到的并发副作用的类型，但需要更多的系统资源，并增加了一个事务阻塞其他事务的可能性。 应平衡应用程序的数据完整性要求与每个隔离级别的开销，在此基础上选择相应的隔离级别。 最高隔离级别 (SERIALIZABLE) 可保证事务在每次重复读取操作时都能准确检索到相同的数据，但需要通过执行一定级别的锁定来完成此操作，而锁定可能会影响多用户系统中的其他事务。 最低的隔离级别 READ UNCOMMITTED 可能检索已被其他事务修改但未提交的数据。 所有并发副作用都可能在 READ UNCOMMITTED 中发生，但没有读取锁定或版本控制，因此开销最小化。
数据库引擎隔离级别
ISO 标准定义了以下隔离级别，所有这些级别都受数据库引擎支持：
| 隔离级别 | 定义 |
|---|---|
READ UNCOMMITTED |
事务仅被隔离到足以确保不读取物理不一致数据的最低级别。 在此级别中，允许脏读，因此一个事务可能会看到其他事务所做的未提交更改。 |
READ COMMITTED |
允许事务读取已被另一个事务读取（未修改）的数据，而无需等待第一个事务完成。 数据库引擎将（在选定数据上获取的）写锁保持到事务结束，但读锁在执行读取操作后立即释放。 这是数据库引擎的默认级别。 |
REPEATABLE READ |
数据库引擎将在选定数据上获取的读锁和写锁保持到事务结束。 但是，由于不管理范围锁，可能会发生幻读。 |
SERIALIZABLE |
事务彼此完全隔离的最高级别。 数据库引擎将在选定数据上获取的读锁和写锁保持到事务结束。 当 SELECT 操作使用范围 WHERE 子句以避免幻读时，会获取范围锁。 注意：当请求 SERIALIZABLE 隔离级别时，复制表上的 DDL 操作和事务可能会失败。 这是因为复制查询使用的提示可能与 SERIALIZABLE 隔离级别不兼容。 |
数据库引擎还支持两种使用行版本控制的附加事务隔离级别。 一种是 READ COMMITTED 隔离级别的实现，另一种是 SNAPSHOT 事务隔离级别。
| 行版本控制隔离级别 | 定义 |
|---|---|
Read Committed Snapshot (RCSI) |
当 READ_COMMITTED_SNAPSHOT 数据库选项设置为 ON（这是 Azure SQL 数据库中的默认设置）时，READ COMMITTED 隔离级别使用行版本控制来提供语句级读取一致性。 读取操作仅需要架构稳定性 (Sch-S) 表级锁，不需要页锁或行锁。 也就是说，数据库引擎使用行版本控制为每个语句提供事务一致的数据快照，就像它在语句开始时存在的那样。 不使用锁来防止其他事务更新数据。 用户定义函数可以返回在包含 UDF 的语句开始后提交的数据。当 READ_COMMITTED_SNAPSHOT 数据库选项设置为 OFF（这是 SQL Server 和 Azure SQL 托管实例中的默认设置）时，READ COMMITTED 隔离使用共享锁来防止其他事务在当前事务运行读取操作时修改行。 共享锁还会阻止语句在其他事务完成之前读取由这些事务修改的行。 这两种实现都符合 ISO 对 READ COMMITTED 隔离的定义。 |
SNAPSHOT |
快照隔离级别使用行版本控制来提供事务级读取一致性。 读取操作不获取页锁或行锁；仅获取架构稳定性 (Sch-S) 表锁。 读取由另一个事务修改的行时，读取操作检索事务开始时存在的行版本。 仅当 SNAPSHOT 数据库选项设置为 ALLOW_SNAPSHOT_ISOLATION 时，才能使用 ON 隔离。 默认情况下，SQL Server 和 Azure SQL 托管实例中的用户数据库将此选项设置为 OFF，Azure SQL 数据库中的数据库将此选项设置为 ON。注意：数据库引擎不支持元数据的版本控制。 因此，在快照隔离下运行的显式事务中可以执行哪些 DDL 操作存在限制。 在 BEGIN TRANSACTION 语句之后，使用快照隔离时不允许使用以下 DDL 语句：ALTER TABLE、CREATE INDEX、CREATE XML INDEX、ALTER INDEX、DROP INDEX、DBCC REINDEX、ALTER PARTITION FUNCTION、ALTER PARTITION SCHEME 或任何公共语言运行时 (CLR) DDL 语句。 在隐式事务中使用快照隔离时允许这些语句。 根据定义，隐式事务是单个语句，即使使用 DDL 语句，也可以强制实施快照隔离的语义。 违反此原则可能导致错误 3961：Snapshot isolation transaction failed in database '%.*ls' because the object accessed by the statement has been modified by a DDL statement in another concurrent transaction since the start of this transaction. It is not allowed because the metadata is not versioned. A concurrent update to metadata could lead to inconsistency if mixed with snapshot isolation. |
下表显示了不同隔离级别启用的并发副作用。
| 隔离级别 | 脏读 | 不可重复读 | 虚拟 |
|---|---|---|---|
READ UNCOMMITTED |
是 | 是 | 是 |
READ COMMITTED |
否 | 是 | 是 |
REPEATABLE READ |
否 | 否 | 是 |
SNAPSHOT |
否 | 否 | 否 |
SERIALIZABLE |
否 | 否 | 否 |
有关每个事务隔离级别控制的特定类型的锁定或行版本控制的详细信息，请参阅 SET TRANSACTION ISOLATION LEVEL (Transact-SQL)。
可以使用 Transact-SQL 或通过数据库 API 来设置事务隔离级别。
Transact-SQL
Transact-SQL 脚本使用 SET TRANSACTION ISOLATION LEVEL 语句。
ADO
ADO 应用程序将 IsolationLevel 对象的 Connection 属性设置为 adXactReadUncommitted、adXactReadCommitted、adXactRepeatableRead 或 adXactReadSerializable。
ADO.NET
使用 System.Data.SqlClient 托管命名空间的 ADO.NET 应用程序可以调用 SqlConnection.BeginTransaction 方法并将 IsolationLevel 选项设置为 Unspecified、Chaos、ReadUncommitted、ReadCommitted、RepeatableRead、Serializable 或 Snapshot。
OLE DB
启动事务时，使用 OLE DB 的应用程序会调用 ITransactionLocal::StartTransaction，isoLevel 设置为 ISOLATIONLEVEL_READUNCOMMITTED、ISOLATIONLEVEL_READCOMMITTED、ISOLATIONLEVEL_REPEATABLEREAD、ISOLATIONLEVEL_SNAPSHOT 或 ISOLATIONLEVEL_SERIALIZABLE。
在自动提交模式下指定事务隔离级别时，OLE DB 应用程序可以将 DBPROPSET_SESSION 属性 DBPROP_SESS_AUTOCOMMITISOLEVELS 设置为 DBPROPVAL_TI_CHAOS、DBPROPVAL_TI_READUNCOMMITTED、DBPROPVAL_TI_BROWSE、DBPROPVAL_TI_CURSORSTABILITY、DBPROPVAL_TI_READCOMMITTED、DBPROPVAL_TI_REPEATABLEREAD、DBPROPVAL_TI_SERIALIZABLE、DBPROPVAL_TI_ISOLATED 或 DBPROPVAL_TI_SNAPSHOT。
ODBC
ODBC 应用程序调用 SQLSetConnectAttr，Attribute 设置为 SQL_ATTR_TXN_ISOLATION，ValuePtr 设置为 SQL_TXN_READ_UNCOMMITTED、SQL_TXN_READ_COMMITTED、SQL_TXN_REPEATABLE_READ 或 SQL_TXN_SERIALIZABLE。
对于快照事务，应用程序调用 SQLSetConnectAttr，属性设置为 SQL_COPT_SS_TXN_ISOLATION，ValuePtr 设置为 SQL_TXN_SS_SNAPSHOT。 你可以使用或 SQL_COPT_SS_TXN_ISOLATION 或 SQL_ATTR_TXN_ISOLATION 检索快照事务。
数据库引擎中的锁定
锁定是数据库引擎用于同步多个用户同时访问同一段数据的机制。
在事务获取对一段数据当前状态的依赖（如通过读取或修改数据）之前，它必须保护自己免受另一个事务修改相同数据的影响。 事务通过请求对该段数据的锁来实现此目的。 锁具有不同的模式，例如共享锁 (S) 或排他锁 (X)。 锁模式定义了事务对数据的依赖级别。 任何事务都无法获得与已授予其他事务的锁模式冲突的锁。 如果事务请求的锁模式与已授予同一数据的锁冲突，数据库引擎将暂停请求事务，直到第一个锁被释放。
当事务修改某个数据块时，它将持有保护所做修改的特定锁直到事务结束。 事务持有为保护读取操作而获取的锁的时间长短取决于事务隔离级别设置以及是否启用了优化锁定。
在未启用优化锁定时，写入所需的行锁和页锁将一直持有到事务结束。
启用优化锁定时，只有事务 ID (TID) 锁会保持到事务结束。 在默认的
READ COMMITTED隔离级别下，事务不会将写入所需的行锁和页锁保持到事务结束。 这减少了所需的锁内存并减少了对锁升级的需求。 此外，启用优化锁定后，限定后锁 (LAQ) 优化会评估行的最新提交版本上的查询谓词，而不获取锁，从而提高并发性。
事务完成时（提交或回滚），该事务持有的所有锁都会被释放。
应用程序通常不会直接请求锁。 锁由数据库引擎中称为锁管理器的部分进行内部管理。 当数据库引擎实例处理 Transact-SQL 语句时，数据库引擎查询处理器会确定要访问哪些资源。 查询处理器根据访问类型和事务隔离级别设置，确定保护每个资源所需的锁类型。 然后，查询处理器向锁管理器请求适当的锁。 如果没有其他事务持有冲突的锁，锁管理器会授予这些锁。
锁粒度和层次结构
数据库引擎具有多粒度锁定，允许事务锁定不同类型的资源。 为了最小化锁定成本，数据库引擎会自动以适合任务的级别锁定资源。 以较小的粒度（如行）锁定可提高并发性，但开销较高，因为如果锁定许多行，则必须持有更多锁。 以较大的粒度（如表）锁定在并发性方面成本较高，因为锁定整个表会限制其他事务对表中任何部分的访问。 但是，由于维护的锁较少，因此开销较低。
数据库引擎通常必须在多个粒度级别获取锁，以完全保护资源。 这种多个粒度级别的锁组称为锁层次结构。 例如，为了完全保护索引的读取，数据库引擎实例可能需要获取行上的共享锁以及页和表上的意向共享锁。
下表显示了数据库引擎可以锁定的资源。
| 资源 | 说明 |
|---|---|
RID |
用于锁定堆中单个行的行标识符。 |
KEY |
用于锁定 B 树索引中单个行的行锁。 |
PAGE |
数据库中的 8 千字节 (KB) 页，如数据页或索引页。 |
EXTENT |
一组连续的八页，如数据页或索引页。 |
HoBT1 |
堆或 B 树。 用于保护没有聚集索引的表中的 B 树（索引）或堆数据页的锁。 |
TABLE1 |
整个表，包括所有数据和索引。 |
FILE |
数据库文件。 |
APPLICATION |
应用程序指定的资源。 |
METADATA |
元数据锁。 |
ALLOCATION_UNIT |
分配单元。 |
DATABASE |
整个数据库。 |
XACT2 |
优化锁定中使用的事务 ID (TID) 锁定。 有关详细信息，请参阅事务 ID (TID) 锁定。 |
1HoBT 和 TABLE 锁可能会受到 ALTER TABLE 的 LOCK_ESCALATION 选项的影响。
2 对于 XACT 锁资源，还有其他锁定资源可用，请参阅优化锁定的诊断新增功能。
锁模式
数据库引擎使用不同的锁模式锁定资源，这些锁模式确定并发事务如何访问资源。
下表显示了数据库引擎使用的资源锁模式。
| 锁模式 | 说明 |
|---|---|
共享 (S) |
用于不更改或更新数据的读取操作，例如 SELECT 语句。 |
更新 (U) |
用于可更新的资源。 防止多个会话读取、锁定并可能在以后更新资源时发生的常见死锁形式。 |
排他 (X) |
用于数据修改操作，如 INSERT、UPDATE 或 DELETE。 确保同一时间不能对同一资源进行多次更新。 |
| 意向 | 用于建立锁层次结构。 意向锁的类型包括：意向共享 (IS)、意向排他 (IX) 和共享意向排他 (SIX)。 |
| 架构 | 在执行依赖于表架构的操作时使用。 架构锁的类型包括：架构修改 (Sch-M) 和架构稳定 (Sch-S)。 |
批量更新 (BU) |
当使用 TABLOCK 提示将数据批量复制到表中时使用。 |
| 键范围 | 在使用 SERIALIZABLE 事务隔离级别时，保护查询读取的行范围。 确保如果重新运行查询，其他事务不能插入符合 SERIALIZABLE 事务查询条件的行。 |
共享锁
在悲观并发控制下，共享 (S) 锁允许并发事务读取资源。 当资源上存在共享 (S) 锁时，其他事务不能修改数据。 资源上的共享 (S) 锁在读取操作完成后立即释放，除非事务隔离级别设置为 REPEATABLE READ 或更高，或者使用锁定提示在事务持续期间保留共享 (S) 锁。
更新锁
数据库引擎在准备执行更新时会放置更新 (U) 锁。 U 锁与 S 锁兼容，但在给定资源上一次只能有一个事务持有 U 锁。 这很关键 — 许多并发事务可以持有 S 锁，但在一个资源上只能有一个事务持有 U 锁。 更新 (U) 锁最终会升级为排他 (X) 锁以更新行。
当语句中指定 UPDLOCK 表提示时，除了 UPDATE 之外，其他语句也可以获取更新 U 锁。
一些应用程序使用“选择一行，然后更新该行”的模式，其中读取和写入在事务内显式分离。 在这种情况下，如果隔离级别是
REPEATABLE READ或SERIALIZABLE，并发更新可能会导致死锁，如下所示：一个事务读取数据，获取资源上的共享 (
S) 锁，然后修改数据，这需要将锁转换为排他 (X) 锁。 如果两个事务在资源上获取共享 (S) 锁，然后尝试同时更新数据，则一个事务会尝试将锁转换为排他 (X) 锁。 共享到排他锁的转换必须等待，因为一个事务的排他 (X) 锁与另一个事务的共享 (S) 锁不兼容；会发生锁等待。 第二个事务尝试为其更新获取排他 (X) 锁。 由于两个事务都在转换为排他 (X) 锁，并且它们各自都在等待另一个事务释放其共享 (S) 锁，因此会发生死锁。在默认的
READ COMMITTED隔离级别下，S锁的持续时间很短，一旦使用就会释放。 虽然上述死锁仍然可能发生，但使用短持续时间的锁时，发生的可能性要小得多。为了避免这种类型的死锁，应用程序可以遵循“使用
UPDLOCK提示选择一行，然后更新该行”的模式。如果在使用
UPDLOCK隔离时在写入中使用SNAPSHOT提示，则事务必须能够访问行的最新版本。 如果最新版本不再可见，可能会收到Msg 3960, Level 16, State 2 Snapshot isolation transaction aborted due to update conflict。 有关示例，请参阅使用快照隔离。
排他锁
排他 (X) 锁阻止并发事务访问资源。 使用排他 (X) 锁时，其他事务不能修改受锁保护的数据；只有使用 NOLOCK 提示或 READ UNCOMMITTED 隔离级别，才能进行读取操作。
数据修改语句（如 INSERT、UPDATE 和 DELETE）结合了读取和修改操作。 该语句首先执行读取操作以获取数据，然后执行所需的修改操作。 因此，数据修改语句通常同时请求共享锁和排他锁。 例如，UPDATE 语句可能会基于与另一个表的联接来修改一个表中的行。 在这种情况下，UPDATE 语句除了请求更新行上的排他锁外，还请求联接表中读取行的共享锁。
意向锁
数据库引擎使用意向锁来保护在锁层次结构中较低级别的资源上放置共享 (S) 锁或排他 (X) 锁。 意向锁之所以命名为“意向锁”，是因为在较低级别锁前可获取它们，因此会通知意向将锁放置在较低级别上。
意向锁有两个用途：
- 防止其他事务以会使较低级别锁失效的方式修改较高级别资源。
- 提高数据库引擎在较高级别粒度检测锁冲突的效率。
例如，在请求表中页或行上的共享 (S) 锁之前，会在表级别请求共享意向锁。 在表级别设置意向锁可防止另一个事务随后在包含该页的表上获取排他 (X) 锁。 意向锁提高了性能，因为数据库引擎仅在表级别检查意向锁，以确定事务是否可以安全地获取该表上的锁。 这消除了检查表上的每个行锁或页锁以确定事务是否可以锁定整个表的要求。
意向锁包括意向共享 (IS)、意向排他 (IX) 和共享意向排他 (SIX)。
| 锁模式 | 说明 |
|---|---|
意向共享 (IS) |
保护层次结构中某些（但不是全部）较低级别资源上请求或获取的共享锁。 |
意向排他 (IX) |
保护层次结构中某些（但不是全部）较低级别资源上请求或获取的排他锁。 IX 是 IS 的超集，它还保护请求较低级别资源上的共享锁。 |
共享意向排他 (SIX) |
保护层次结构中所有较低级别资源上请求或获取的共享锁，以及某些（但不是全部）较低级别资源上的意向排他锁。 允许在顶级资源上并发持有 IS 锁。 例如，在表上获取 SIX 锁还会获取正在修改的页上的意向排他锁和修改行上的排他锁。 每个资源一次只能有一个 SIX 锁，防止其他事务对资源进行更新，尽管其他事务可以通过在表级别获取 IS 锁来读取层次结构中较低的资源。 |
意向更新 (IU) |
保护层次结构中所有较低级别资源上请求或获取的更新锁。 IU 锁仅在页资源上使用。 如果发生更新操作，IU 锁会转换为 IX 锁。 |
共享意向更新 (SIU) |
由于分别获取并同时持有这些锁，因此是 S 和 IU 锁的组合。 例如，事务执行带有 PAGLOCK 提示的查询，然后执行更新操作。 带有 PAGLOCK 提示的查询获取 S 锁，更新操作获取 IU 锁。 |
更新意向排他 (UIX) |
由于分别获取并同时持有这些锁，因此是 U 和 IX 锁的组合。 |
架构锁
数据库引擎在表数据定义语言 (DDL) 操作（如添加列或删除表）期间使用架构修改 (Sch-M) 锁。 在持有 Sch-M 锁期间，它会阻止对表的并发访问。 这意味着 Sch-M 锁会阻止所有外部操作，直到锁被释放。
一些数据操作语言 (DML) 操作（如表截断）使用 Sch-M 锁来防止并发操作访问受影响的表。
数据库引擎在编译和执行查询时使用架构稳定 (Sch-S) 锁。 Sch-S 锁不阻止任何事务锁，包括排他 (X) 锁。 因此，其他事务（包括那些在表上具有 X 锁的事务）在编译查询时继续运行。 但是，并发 DDL 操作和获取 Sch-M 锁的并发 DML 操作会被 Sch-S 锁阻止。
批量更新锁
批量更新 (BU) 锁允许多个线程将数据并发批量加载到同一个表中，同时防止其他非批量加载数据的进程访问该表。 当以下两个条件都为真时，数据库引擎使用批量更新 (BU) 锁。
- 使用 Transact-SQL 的
BULK INSERT语句、OPENROWSET(BULK)函数，或使用批量插入 API 命令之一，如 .NETSqlBulkCopy、OLEDB 快速加载 API 或 ODBC 批量复制 API，将数据批量复制到表中。 - 指定了
TABLOCK提示或使用 sp_tableoption 设置了table lock on bulk load表选项。
提示
与持有限制较少的批量更新 (BU) 锁的 BULK INSERT 语句不同，带有 TABLOCK 提示的 INSERT INTO...SELECT 会在表上持有意向排他 (IX) 锁。 也就是说你不能使用并行插入操作插入行。
键范围锁
键范围锁在使用 SERIALIZABLE 事务隔离级别时，保护 Transact-SQL 语句正在读取的记录集中隐式包含的行范围。 键范围锁定可防止幻读。 通过保护行之间的键范围，它还可以防止向事务访问的记录集插入或删除幻像行。
锁兼容性
锁兼容性控制多个事务是否可以同时在同一资源上获取锁。 如果资源已被其他事务锁定，则只有当请求锁的模式与现有锁的模式兼容时，才能授予新的锁请求。 如果请求锁的模式与现有锁的模式不兼容，则请求新锁的事务将等待释放现有锁或等待锁超时间隔过期。 例如，没有锁模式与排他锁兼容。 当持有排他 (X) 锁时，其他事务无法获取该资源上的任何类型的锁（共享锁、更新锁或排他锁），直到排他 (X) 锁被释放。 相反，如果已对资源应用共享 (S) 锁，即使第一个事务尚未完成，其他事务也可以在该资源上获取共享锁或更新 (U) 锁。 但是，其他事务在共享锁释放之前无法获取排他锁。
| 现有授予的模式 | IS |
S |
U |
IX |
SIX |
X |
|---|---|---|---|---|---|---|
| 请求的模式 | ||||||
意向共享 (IS) |
是 | 是 | 是 | 是 | 是 | 否 |
共享 (S) |
是 | 是 | 是 | 否 | 否 | 否 |
更新 (U) |
是 | 是 | 否 | 否 | 否 | 否 |
意向排他 (IX) |
是 | 否 | 否 | 是 | 否 | 否 |
共享意向排他 (SIX) |
是 | 否 | 否 | 否 | 否 | 否 |
排他 (X) |
否 | 否 | 否 | 否 | 否 | 否 |
注意
意向排他 (IX) 锁与 IX 锁模式兼容，因为 IX 意味着仅打算更新部分行而非所有行。 只要其他事务尝试读取或更新的行不是其他事务所更新的同一行，这些事务也被允许。 此外，如果两个事务尝试更新同一行，则两个事务都会被授予表级和页级的 IX 锁。 但是，一个事务会被授予行级的 X 锁。 另一个事务必须等待，直到行级锁被释放。
| 键 | 说明 |
|---|---|
| N | 无冲突 |
| 我 | 非法 |
| C | 冲突 |
| 荷兰 | 无锁 |
| SCH-S | 架构稳定锁 |
| SCH-M | 架构修改锁 |
| S | 共享 |
| U | 更新 |
| X | 排他 |
| 伊斯兰国 | 意向共享 |
| IU | 意向更新 |
| 九 | 意向排他 |
| SIU | 共享意向更新 |
| SIX | 共享意向排他 |
| UIX | 更新意向排他 |
| BU | 批量更新 |
| RS-S | 共享范围-共享 |
| RS-U | 共享范围-更新 |
| RI-N | 插入范围-null |
| RI-S | 插入范围-共享 |
| RI-U | 插入范围-更新 |
| RI-X | 插入范围-排他 |
| RX-S | 排他范围-共享 |
| RX-U | 排他范围-更新 |
| RX-X | 排他范围-排他 |
键范围锁定
键范围锁在使用 SERIALIZABLE 事务隔离级别时，保护 Transact-SQL 语句正在读取的记录集中隐式包含的行范围。 SERIALIZABLE 隔离级别要求事务期间执行的任何查询在事务期间每次执行时都必须获取相同的行集。 键范围锁通过防止其他事务插入键值属于 SERIALIZABLE 事务读取的键范围的新行来满足此要求。
键范围锁定可防止幻读。 通过保护行之间的键范围，它还可以防止向事务访问的记录集插入幻像行。
键范围锁放置在索引上，指定开始和结束键值。 此锁会阻止任何插入、更新或删除键值属于该范围的行的尝试，因为这些操作首先必须获取索引上的锁。 例如，SERIALIZABLE 事务可以发出 SELECT 语句，读取所有键值符合条件 BETWEEN 'AAA' AND 'CZZ' 的行。 在键值范围从 'AAA' 到 'CZZ' 上的键范围锁可防止其他事务在该范围内的任何位置插入键值，如 'ADG'、'BBD' 或 'CAL'。
键范围锁模式
键范围锁包括以范围-行格式指定的范围和行组件：
- 范围表示保护两个连续索引条目之间范围的锁模式。
- 行表示保护索引条目的锁模式。
- 模式表示使用的组合锁模式。 键范围锁模式由两部分组成。 第一部分表示用于锁定索引范围的锁类型 (RangeT)，第二部分表示用于锁定特定键的锁类型 (K)。 两部分用连字符 (-) 连接，如 RangeT-K。
| 范围 | 行 | 模式 | 说明 |
|---|---|---|---|
RangeS |
S |
RangeS-S |
共享范围，共享资源锁；用于 SERIALIZABLE 范围扫描。 |
RangeS |
U |
RangeS-U |
共享范围，更新资源锁；用于 SERIALIZABLE 更新扫描。 |
RangeI |
Null |
RangeI-N |
插入范围，空资源锁；用于在向索引插入新键之前测试范围。 |
RangeX |
X |
RangeX-X |
排他范围，排他资源锁；用于更新范围中的键时。 |
注意
内部的 Null 锁模式与所有其他锁模式兼容。
键范围锁模式有一个兼容性矩阵，显示哪些锁与在重叠键和范围上获取的其他锁兼容。
| 现有授予的模式 | S |
U |
X |
RangeS-S |
RangeS-U |
RangeI-N |
RangeX-X |
|---|---|---|---|---|---|---|---|
| 请求的模式 | |||||||
共享 (S) |
是 | 是 | 否 | 是 | 是 | 是 | 否 |
更新 (U) |
是 | 否 | 否 | 是 | 否 | 是 | 否 |
排他 (X) |
否 | 否 | 否 | 否 | 否 | 是 | 否 |
RangeS-S |
是 | 是 | 否 | 是 | 是 | 否 | 否 |
RangeS-U |
是 | 否 | 否 | 是 | 否 | 否 | 否 |
RangeI-N |
是 | 是 | 是 | 否 | 否 | 是 | 否 |
RangeX-X |
否 | 否 | 否 | 否 | 否 | 否 | 否 |
转换锁
当键范围锁与另一个锁重叠时，会创建转换锁。
| 锁 1 | 锁 2 | 转换锁 |
|---|---|---|
S |
RangeI-N |
RangeI-S |
U |
RangeI-N |
RangeI-U |
X |
RangeI-N |
RangeI-X |
RangeI-N |
RangeS-S |
RangeX-S |
RangeI-N |
RangeS-U |
RangeX-U |
在不同的复杂情况下，有时在运行并发进程时，转换锁可能会在短时间内被观察到。
可序列化范围扫描、单例获取、删除和插入
键范围锁定可确保以下操作是可序列化的：
- 范围扫描查询
- 不存在行的单例获取
- 删除操作
- 插入操作
在发生键范围锁定之前，必须满足以下条件：
- 事务隔离级别必须设置为
SERIALIZABLE。 - 查询处理器必须使用索引来实现范围筛选谓词。 例如，
WHERE语句中的SELECT子句可以使用此谓词建立范围条件：ColumnX BETWEEN N'AAA' AND N'CZZ'。 只有当ColumnX被索引键覆盖时，才能获取键范围锁。
示例
以下表和索引用作后续键范围锁定示例的基础。
范围扫描查询
为了确保范围扫描查询是可序列化的，同一查询在同一事务中每次执行时都应返回相同的结果。 其他事务不得在范围扫描查询内插入新行；否则，这些行会成为幻像插入。 例如，以下查询使用前一图示中的表和索引：
SELECT name
FROM mytable
WHERE name BETWEEN 'A' AND 'C';
键范围锁放置在与名称介于 Adam 与 Dale 之间的行范围对应的索引条目上，防止添加或删除符合先前查询条件的新行。 尽管此范围内的第一个名称是 Adam，但此索引条目上的 RangeS-S 模式键范围锁可确保不会在 Abigail 之前添加以字母 A 开头的新名称，如 Adam。 类似地，RangeS-S 索引条目上的 Dale 键范围锁可确保不会在 Carlos 之后添加以字母 C 开头的新名称，如 Clive。
注意
持有的 RangeS-S 锁数量为 n+1，其中 n 是满足查询的行数。
不存在数据的单例获取
如果事务中的查询试图选择不存在的行，则以后在相同的事务中发出这一查询时，必须返回相同的结果。 不允许其他事务插入该不存在的行。 例如，给定此查询：
SELECT name
FROM mytable
WHERE name = 'Bill';
键范围锁放置在与名称范围从 Ben 到 Bing 对应的索引条目上，因为名称 Bill 会插入到这两个相邻索引条目之间。 RangeS-S 模式键范围锁放置在索引条目 Bing 上。 这可防止任何其他事务在索引条目 Bill 与 Ben 之间插入值，如 Bing。
未启用优化锁定时的删除操作
在事务内删除行时，执行删除操作的事务期间不必锁定该行所属的范围。 将已删除的键值锁定到事务结束足以维护可序列化性。 例如，给定此 DELETE 语句：
DELETE mytable
WHERE name = 'Bob';
排他 (X) 锁放置在与名称 Bob 对应的索引条目上。 其他事务可以在值为 Bob 的行之前或之后插入或删除值。 但是，任何尝试读取、插入或删除匹配值 Bob 行的事务都会被阻塞，直到删除事务提交或回滚。 （READ_COMMITTED_SNAPSHOT 数据库选项和 SNAPSHOT 隔离级别也允许从先前提交状态的行版本读取。）
范围删除可以使用三种基本锁模式执行：行锁、页锁或表锁。 行锁、页锁或表锁策略由查询优化器决定，也可以由用户通过查询优化器提示（如 ROWLOCK、PAGLOCK 或 TABLOCK）指定。 当使用 PAGLOCK 或 TABLOCK 时，如果索引页中的所有行都被删除，数据库引擎会立即释放该索引页。 相比之下，当使用 ROWLOCK 时，所有已删除的行仅标记为已删除；之后会使用后台任务将它们从索引页中移除。
启用优化锁定时的删除操作
在事务内删除行时，行锁和页锁会被增量获取和释放，不会在事务持续期间持有。 例如，给定此 DELETE 语句：
DELETE mytable
WHERE name = 'Bob';
在事务持续时间内，所有修改的行上都放置 TID 锁。 会获取与值 Bob 对应的索引行的 TID 锁。 使用优化锁定，更新时将继续获取页锁和行锁，但每行更新后，每个页锁和行锁都会释放。 TID 锁可保护行在事务完成之前不被更新。 任何尝试读取、插入或删除值为 Bob 行的事务都会被阻塞，直到删除事务提交或回滚。 （READ_COMMITTED_SNAPSHOT 数据库选项和 SNAPSHOT 隔离级别也允许从先前提交状态的行版本读取。）
否则，删除操作的锁定机制与未优化锁定相同。
没有优化锁定的插入操作
在事务内插入行时，执行插入操作的事务期间不必锁定该行所属的范围。 将已插入的键值锁定到事务结束足以维护可序列化性。 例如，给定此 INSERT 语句：
INSERT mytable VALUES ('Dan');
RangeI-N 模式键范围锁放置在与名称 David 对应的索引行上以测试范围。 如果锁被授予，则插入值为 Dan 的行，并在已插入的行上放置排他 (X) 锁。 RangeI-N 模式键范围锁仅用于测试范围，不会在执行插入操作的事务持续期间持有。 其他事务可以在值为 Dan 的已插入行之前或之后插入或删除值。 但是，任何尝试读取、插入或删除值为 Dan 行的事务都会被阻塞，直到插入事务提交或回滚。
具有优化锁定的插入操作
在事务内插入行时，执行插入操作的事务期间不必锁定该行所属的范围。 很少获取行锁和页锁，仅当正在进行联机索引重建或存在并发的 SERIALIZABLE 事务时才会获取。 如果获取行锁和页锁，则会在事务持续时间内快速释放这些锁，并且不会持有。 将排他 TID 锁放在插入的键值上，直至事务结束足以维护可序列化。 例如，给定此 INSERT 语句：
INSERT mytable VALUES ('Dan');
启用优化锁定时，仅当实例中至少有一个事务使用 SERIALIZABLE 隔离级别时，才会获取 RangeI-N 锁。 RangeI-N 模式键范围锁放置在与名称 David 对应的索引行上以测试范围。 如果锁被授予，则插入值为 Dan 的行，并在已插入的行上放置排他 (X) 锁。 RangeI-N 模式键范围锁仅用于测试范围，不会在执行插入操作的事务持续期间持有。 其他事务可以在值为 Dan 的已插入行之前或之后插入或删除值。 但是，任何尝试读取、插入或删除值为 Dan 行的事务都会被阻塞，直到插入事务提交或回滚。
锁升级
锁升级是将许多细粒度锁转换为较少粗粒度锁的过程，从而减少系统开销，同时增加并发争用的可能性。
锁升级的行为有所不同，具体取决于是否启用优化锁定。
无优化锁定的锁升级
当数据库引擎获取低级锁时，它还会在包含低级对象的对象上放置意向锁：
- 当锁定行或索引键范围时，数据库引擎将在包含这些行或键的页上放置意向锁。
- 当锁定页时，数据库引擎将在包含这些页的更高级别对象上放置意向锁。 除了对象上的意向锁外，还会对以下对象请求意向页锁：
- 非聚集索引的叶级别页
- 聚集索引的数据页
- 堆数据页
数据库引擎可以为同一语句执行行锁定和页锁定，以最大程度地减少锁的数量，并降低需要进行锁升级的可能性。 例如，数据库引擎可能会在非聚集索引上放置页锁（如果索引节点中选择了足够的连续键以满足查询），并在聚集索引或堆上放置行锁。
要升级锁，数据库引擎会尝试将表上的意向锁更改为相应的完整锁，例如，将意向排他 (IX) 锁更改为排他 (X) 锁，或将意向共享 (IS) 锁更改为共享 (S) 锁。 如果锁升级尝试成功并获取了完整的表锁，则事务在堆或索引上持有的所有 HoBT、页 (PAGE) 或行级（RID、KEY）锁都将释放。 如果无法获取全锁，当时不会发生锁升级，而数据库引擎将继续获取行、键或页锁。
数据库引擎不会将行锁或键范围锁升级到页锁，而是将它们直接升级到表锁。 同样，页锁始终升级为表锁。 分区表的锁定可以升级到关联分区的 HoBT 级别，而不是表锁。 HoBT 级别锁不一定锁定分区的对齐 HoBT。
注意
HoBT 级别锁通常会增加并发性，但当锁定不同分区的事务都将排他锁扩展到其他分区时，可能会造成死锁。 在极少数情况下，TABLE 锁定粒度可能表现更好。
如果由于并发事务持有冲突锁而导致锁升级尝试失败，数据库引擎将为事务获取的每额外 1,250 个锁重试锁升级。
每个升级事件主要在单个 Transact-SQL 语句级别操作。 当事件启动时，只要活动语句满足升级阈值的要求，Transact-SQL 就会尝试升级当前事务在活动语句所引用的任何表中持有的所有锁。 如果在语句访问表之前启动升级事件，则不会尝试升级该表上的锁。 如果锁升级成功，当前语句引用并包含在升级事件中的表，其事务在先前语句中获取并在事件开始时仍持有的任何锁都将升级。
例如，假设会话执行以下操作：
- 开始一个事务。
- 更新
TableA。 这会在TableA中生成排他行锁，这些锁将一直持有到事务完成。 - 更新
TableB。 这会在TableB中生成排他行锁，这些锁将一直持有到事务完成。 - 执行将
TableA与TableC联接的SELECT。 查询执行计划要求在从TableA检索行之前先从TableC检索行。 SELECT语句在从TableA检索行时且在访问TableC之前触发锁升级。
如果锁升级成功，仅会话在 TableA 上持有的锁会被升级。 这包括来自 SELECT 语句的共享锁和来自先前 UPDATE 语句的排他锁。 虽然仅计算会话在 TableA 中为 SELECT 语句获取的锁以确定是否应执行锁升级，但一旦升级成功，会话在 TableA 中的所有锁都会升级为表上的排他锁，并且在 TableA 上的所有其他较低粒度的锁（包括意向锁）都会释放。
不会尝试升级 TableB 上的锁，因为 SELECT 语句中没有对 TableB 的活动引用。 同样，也不会尝试升级 TableC 上的锁，因为在升级发生时尚未访问，所以不会升级。
具有优化锁的锁升级
优化锁定有助于减少锁内存，因为在事务持续时间内持有的锁很少。 当数据库引擎获取行锁和页锁时，锁升级可能类似地发生，但频率要低得多。 优化锁定通常可以成功避免锁升级、减少所需的锁数量和锁内存量。
启用优化锁定且处于默认 READ COMMITTED 隔离级别时，数据库引擎会在修改行后立即释放行锁和页锁。 除了单个事务 ID (TID) 锁除以外，在事务持续时间内不会持有行锁和页锁。 这减少了锁升级的可能性。
锁升级阈值
如果没有使用 ALTER TABLE SET LOCK_ESCALATION 选项来禁用表的锁升级并且满足以下任一条件，则将触发锁升级：
- 单个 Transact-SQL 语句在单个未分区表或索引上获取至少 5,000 个锁。
- 单个 Transact-SQL 语句在已分区表的单个分区上获取至少 5,000 个锁，并且
ALTER TABLE SET LOCK_ESCALATION选项设为 AUTO。 - 数据库引擎实例中的锁数量超出了内存或配置阈值。
如果由于锁冲突导致无法升级锁，则每当获取 1,250 个新锁时，数据库引擎便会触发锁升级。
Transact-SQL 语句的升级阈值
当数据库引擎在每 1,250 个新获取的锁上检查可能的升级时，并且当且仅当 Transact-SQL 语句已在表的单个引用上获得至少 5,000 个锁时，才会进行锁升级。 当 Transact-SQL 语句在表的单个引用上获得至少 5,000 个锁时，将触发锁升级。 例如，如果语句在一个索引中获取 3,000 个锁，而在同一个表的另一个索引中又获取 3,000 个锁，则不会触发锁升级。 同样，如果语句对表具有自联接，且对表的每个引用仅获取表中的 3,000 个锁，则不会触发锁升级。
锁升级仅发生在触发升级时已访问的表上。 假设单个 SELECT 语句是按以下顺序访问三个表的连接：TableA、TableB 和 TableC。 该语句在 TableA 的聚集索引中获取 3,000 个行锁，在 TableB 的聚集索引中获取至少 5,000 个行锁，但是尚未访问 TableC。 当数据库引擎检测到该语句已在 TableB 中获取至少 5,000 个行锁时，会尝试升级当前事务在 TableB 中持有的所有锁。 它还尝试升级当前事务在 TableA 上持有的所有锁，但由于 TableA 上的锁数量少于 5,000，升级将不会成功。 没有尝试对 TableC 进行锁升级，因为在升级发生时尚未访问。
数据库引擎实例的升级阈值
每当锁的数量大于锁升级的内存阈值时，数据库引擎都会触发锁升级。 内存阈值取决于锁配置选项的设置：
如果
locks选项设置为默认值 0，则当锁对象使用的内存达到数据库引擎使用的内存（不包括 AWE 内存）的 24%时，将达到锁升级阈值。 用于表示锁的数据结构长度大约为 100 个字节。 此阈值是动态的，因为数据库引擎会动态获取和释放内存以适应不同的工作负荷。如果
locks选项的值非 0，则锁升级阈值为锁选项值的 40%（如果存在内存压力，则更低）。
数据库引擎可以为升级选择任何会话中的活动语句，而且，只要实例中使用的锁内存保持在阈值之上，每获取 1,250 个新锁，它就会为升级选择语句。
混合锁类型的锁升级
在发生锁升级时，为堆或索引选择的锁足够强大，以满足限制性最严格的较低级别锁的要求。
例如，假设会话：
- 开始一个事务。
- 更新包含聚集索引的表。
- 发出引用同一表的
SELECT语句。
UPDATE 语句会获取这些锁：
- 更新数据行上的排他 (
X) 锁。 - 包含这些行的聚集索引页上的意向排他 (
IX) 锁。 - 聚集索引上的
IX锁和表上的另一个锁。
SELECT 语句会获取这些锁：
- 所读取的所有数据行上的共享 (
S) 锁，除非该行已受UPDATE语句中的X锁保护。 - 读取的所有数据行上的共享 (
IS) 锁，除非该行已被语句的IX锁保护。 - 聚集索引或表上没有锁，因为它们已被
IX锁保护。
如果 SELECT 语句获取足够的锁来触发锁升级且升级成功，表上的 IX 锁将转换为 X 锁，并且所有行、页和索引锁都将释放。 更新和读取都由表上的 X 锁保护。
减少锁定和锁升级
在大多数情况下，数据库引擎在使用默认的锁定和锁升级设置进行操作时提供最佳性能。
利用优化锁定。
- 优化锁定提供了一种改进的事务锁定机制，可减少锁内存消耗和并发事务的阻塞。 启用优化锁定时，发生锁升级的可能性要小得多。
- 避免对优化锁定使用表提示。 表提示可能会降低优化锁定的有效性。
- 为数据库启用 READ_COMMITTED_SNAPSHOT 选项，以从优化锁定中获得最大收益。 这是 Azure SQL 数据库中的默认设置。
- 优化锁定要求在数据库上启用加速数据库恢复 (ADR)。
如果数据库引擎实例生成大量锁并且频繁进行锁升级，请考虑使用以下策略减少锁定数量：
对于读取操作，使用不会生成共享锁的隔离级别：
- 当
READ_COMMITTED_SNAPSHOT数据库选项为ON时的READ COMMITTED隔离级别。 -
SNAPSHOT隔离级别。 -
READ UNCOMMITTED隔离级别。 这只能用于可以进行脏读操作的系统。
- 当
使用
PAGLOCK或TABLOCK表提示，使数据库引擎使用页、堆或索引锁而不是低级别锁。 但是，使用此选项会增加用户阻止其他用户尝试访问相同数据的问题，因此不应在具有多个并发用户的系统中使用此选项。如果优化锁定不可用，对于分区表，使用 ALTER TABLE 的
LOCK_ESCALATION选项将锁升级到分区而不是表，或禁用表的锁升级。将大型批处理操作分解为几个较小的操作。 例如，假设你运行以下查询从审计表中删除几十万行旧数据，然后发现它导致锁升级，阻塞了其他用户：
DELETE FROM LogMessages WHERE LogDate < '2024-09-26'通过一次删除几百行，你可以显著减少每个事务累积的锁数量，并防止锁升级。 例如：
DECLARE @DeletedRows int; WHILE @DeletedRows IS NULL OR @DeletedRows > 0 BEGIN DELETE TOP (500) FROM LogMessages WHERE LogDate < '2024-09-26' SELECT @DeletedRows = @@ROWCOUNT; END;通过使查询尽可能高效来减少查询锁占用空间。 大型扫描或大量键查找可能会增加锁升级的机会；此外，这会增加死锁的机会，并通常对并发性和性能产生不利影响。 找到导致锁升级的查询后，寻找创建新索引或向现有索引添加列的机会，以消除完整索引或表扫描，并最大化索引查找的效率。 考虑使用数据库引擎优化顾问对查询执行自动索引分析。 有关详细信息，请参阅教程：数据库引擎优化顾问。 此优化的目标之一是使索引查找返回尽可能少的行，以最小化键查找的成本（最大化索引对特定查询的选择性）。 如果数据库引擎估计键查找逻辑运算符可能返回许多行，它可能会使用预取优化来执行查找。 如果数据库引擎确实对查找使用预取，则必须将查询的一部分的事务隔离级别提高到
REPEATABLE READ。 这意味着，在SELECT隔离级别下看似类似于READ COMMITTED的语句可能会获取数千个键锁（在聚集索引和一个非聚集索引上），这可能导致此类查询超过锁升级阈值。 如果升级的锁是共享表锁，这一点尤其重要，不过在默认READ COMMITTED隔离级别下这种情况并不常见。如果使用预取优化的键查找导致锁升级，考虑向查询计划中键查找逻辑运算符下方的索引查找或索引扫描逻辑运算符中出现的非聚集索引添加额外列。 可能可以创建覆盖索引（包含表中查询中使用的所有列的索引），或者如果在
SELECT列列表中包含所有内容不切实际，至少创建一个覆盖用于连接条件或WHERE子句中列的索引。 嵌套循环连接也可能使用预取优化，这会导致相同的锁定行为。如果不同的 SPID 当前持有不兼容的表锁，则不会发生锁升级。 锁升级始终升级到表锁，从不升级到页锁。 此外，如果锁升级尝试因另一个 SPID 持有不兼容的表锁而失败，尝试升级的查询在等待表锁时不会阻塞。 相反，它继续以其原始的更细粒度（行、键或页）获取锁，定期进行额外的升级尝试。 因此，防止特定表上锁升级的一种方法是在不同的连接上获取并持有与升级锁类型不兼容的锁。 表级别的意向排他 (
IX) 锁不会锁定任何行或页，但它仍然与升级的共享 (S) 或排他 (X) 表锁不兼容。 例如，假设你必须运行一个批处理作业，修改mytable表中的大量行，并且由于锁升级而导致阻塞。 如果此作业始终在不到一个小时内完成，你可以创建一个包含以下代码的 Transact-SQL 作业，并将此新作业计划为在批处理作业开始时间之前的几分钟启动：BEGIN TRAN; SELECT * FROM mytable WITH (UPDLOCK, HOLDLOCK) WHERE 1 = 0; WAITFOR DELAY '1:00:00'; COMMIT TRAN;此查询获取并持有
mytable上的IX锁一小时，这会在此期间防止表上的锁升级。 此批处理不修改任何数据或阻塞其他查询（除非其他查询使用TABLOCK提示强制表锁，或者管理员已禁用mytable上索引的页锁或行锁）。你还可以使用跟踪标志 1211 和 1224 禁用所有或某些锁升级。 但是，这些跟踪标志会为整个数据库引擎实例全局禁用所有锁升级。 锁升级在数据库引擎中发挥了重要作用，通过最大限度地提高效率来弥补因获取和释放数千个锁的开销而导致的查询速度下降。 锁升级还有助于最小化跟踪锁所需的内存。 数据库引擎可以为锁结构动态分配的内存是有限的，因此，如果禁用锁升级并且锁内存增长到足够大，则尝试为任何查询分配额外的锁可能会失败并出现以下错误：
Error: 1204, Severity: 19, State: 1 The SQL Server cannot obtain a LOCK resource at this time. Rerun your statement when there are fewer active users or ask the system administrator to check the SQL Server lock and memory configuration.注意
在出现 MSSQLSERVER_1204 错误时，它将停止处理当前语句并导致活动事务回滚。 如果重新启动数据库服务，回滚本身可能会阻塞用户或导致较长的数据库恢复时间。
注意
使用如
ROWLOCK这样的锁提示仅更改初始锁获取。 锁提示不阻止锁升级。
从 SQL Server 2008（10.0.x）开始，随着 LOCK_ESCALATION 表选项的引入，锁升级的行为发生了变化。 有关详细信息，请参阅 ALTER TABLE 的 LOCK_ESCALATION 选项。
监视锁升级
通过使用 lock_escalation 扩展事件监视锁升级，例如在以下示例中：
-- Session creates a histogram of the number of lock escalations per database
CREATE EVENT SESSION [Track_lock_escalation] ON SERVER
ADD EVENT sqlserver.lock_escalation
(
SET collect_database_name=1,collect_statement=1
ACTION(sqlserver.database_id,sqlserver.database_name,sqlserver.query_hash_signed,sqlserver.query_plan_hash_signed,sqlserver.sql_text,sqlserver.username)
)
ADD TARGET package0.histogram
(
SET source=N'sqlserver.database_id'
)
GO
动态锁定
使用低级锁（如行锁）可以降低两个事务同时在相同数据块上请求锁的可能性，从而提高并发性。 使用低级锁还会增加锁的数量和管理它们所需的资源。 使用高级表锁或页锁可降低开销，但以降低并发性为代价。
数据库引擎使用动态锁定策略来确定最有效的锁。 数据库引擎在执行查询时，会根据架构和查询的特征自动确定最合适的锁。 例如，为了减少锁定开销，优化器在执行索引扫描时可能会在索引中选择页锁。
锁分区
对于大型计算机系统，频繁引用对象上的锁可能成为性能瓶颈，因为获取和释放锁会导致内部锁定资源的争用。 锁分区通过将单个锁资源拆分为多个锁资源来增强锁定性能。 此功能仅适用于具有 16 个或更多逻辑 CPU 的系统，并且会自动启用，无法禁用。 只有对象锁可以分区。 具有子类型的对象锁不会被分区。 有关详细信息，请参阅 sys.dm_tran_locks (Transact-SQL)。
了解锁分区
锁定任务访问多个共享资源，其中两个通过锁分区进行了优化：
Spinlock
这控制对锁资源（如行或表）的访问。
如果没有锁分区，一个自旋锁管理单个锁资源的所有锁请求。 在经历大量活动的系统上，当锁请求等待自旋锁可用时，可能会发生争用。 在这种情况下，获取锁可能成为瓶颈，并对性能产生负面影响。
为了减少单个锁资源上的争用，锁分区将单个锁资源拆分为多个锁资源，以将负载分布到多个自旋锁。
内存
这用于存储锁资源结构。
一旦获取自旋锁，锁结构就会存储在内存中，然后进行访问并可能修改。 将锁访问分布到多个资源中有助于消除在 CPU 之间传输内存块的需要，这有助于提高性能。
实现和监视锁分区
对于具有 16 个或更多 CPU 的系统，默认启用锁分区。 启用锁分区后，将在 SQL Server 错误日志中记录一条信息性消息。
在分区资源上获取锁时：
仅在单个分区上获取
NL、Sch-S、IS、IU和IX锁模式。共享 (
S) 、排他 (X) 和NL、Sch-S、IS、IU和IX以外的其他锁模式必须从分区 ID 0 开始并按分区 ID 顺序在所有分区上获取。 已分区资源的这些锁会比相同模式中未分区资源的锁占用更多的内存，因为每个分区都是一个有效的单独锁。 内存增加量由分区数决定。 SQL Server 锁性能计数器会显示已分区锁和未分区锁使用的内存的相关信息。
事务启动时会分配给一个分区。 对于该事务，所有可以分区的锁请求都使用分配给该事务的分区。 通过这种方法，不同事务对同一对象的锁资源访问分布在不同的分区上。
sys.dm_tran_locks 动态管理视图中的 resource_lock_partition 列提供锁分区资源的锁分区 ID。 有关详细信息，请参阅 sys.dm_tran_locks (Transact-SQL)。
使用锁分区
以下代码示例说明了锁分区。 在示例中，在两个不同的会话中执行两个事务，以显示具有 16 个 CPU 的计算机系统上的锁分区行为。
这些 Transact-SQL 语句创建了后续示例中使用的测试对象。
-- Create a test table.
CREATE TABLE TestTable
(
col1 int
);
GO
-- Create a clustered index on the table.
CREATE CLUSTERED INDEX ci_TestTable ON TestTable (col1);
GO
-- Populate the table.
INSERT INTO TestTable
VALUES (1);
GO
示例 A
会话 1：
在事务下执行 SELECT 语句。 由于 HOLDLOCK 锁提示，此语句获取并保留表上的意向共享 (IS) 锁（在此说明中，行锁和页锁被忽略）。 IS 锁仅在分配给事务的分区上获取。 在此示例中，假设 IS 锁在分区 ID 7 上获取。
-- Start a transaction.
BEGIN TRANSACTION;
-- This SELECT statement will acquire an IS lock on the table.
SELECT col1
FROM TestTable
WITH (HOLDLOCK);
会话 2：
启动事务，在此事务下运行的 SELECT 语句获取并保留表上的共享 (S) 锁。 S 锁在所有分区上获取，从而导致多个表锁，每个分区一个。 例如，在 16 CPU 系统上，将跨锁分区 ID 0-15 发出 16 个 S 锁。 由于 S 锁与会话 1 中事务在分区 ID 7 上持有的 IS 锁兼容，因此事务之间没有阻塞。
BEGIN TRANSACTION;
SELECT col1
FROM TestTable
WITH (TABLOCK, HOLDLOCK);
会话 1：
在会话 1 下仍处于活动状态的事务下执行以下 SELECT 语句。 由于排他 (X) 表锁提示，事务尝试获取表上的 X 锁。 但是，会话 2 中事务持有的 S 锁在分区 ID 0 处阻塞 X 锁。
SELECT col1
FROM TestTable
WITH (TABLOCKX);
示例 B
会话 1：
在事务下执行 SELECT 语句。 由于 HOLDLOCK 锁提示，此语句获取并保留表上的意向共享 (IS) 锁（在此说明中，行锁和页锁被忽略）。 IS 锁仅在分配给事务的分区上获取。 在此示例中，假设 IS 锁在分区 ID 6 上获取。
-- Start a transaction.
BEGIN TRANSACTION;
-- This SELECT statement will acquire an IS lock on the table.
SELECT col1
FROM TestTable
WITH (HOLDLOCK);
会话 2：
在事务下执行 SELECT 语句。 由于 TABLOCKX 锁提示，事务尝试获取表上的排他 (X) 锁。 请记住，必须从分区 ID 0 开始在所有分区上获取 X 锁。 X 锁在所有分区 ID 0-5 上获取，但被在分区 ID 6 上获取的 IS 锁阻塞。
在 X 锁尚未到达的分区 ID 7-15 上，其他事务可以继续获取锁。
BEGIN TRANSACTION;
SELECT col1
FROM TestTable
WITH (TABLOCKX, HOLDLOCK);
数据库引擎中基于行版本控制的隔离级别
从 SQL Server 2005 (9.x) 开始，数据库引擎提供了现有事务隔离级别 READ COMMITTED 的实现，该实现使用行版本控制提供语句级快照。 数据库引擎还提供了事务隔离级别 SNAPSHOT，该级别也使用行版本控制提供事务级快照。
行版本控制是 SQL Server 中的一般框架，它在修改或删除行时调用写入时复制机制。 这要求在事务运行时，行的旧版本必须可供需要早期事务一致状态的事务使用。 行版本控制用于实现以下功能：
- 在触发器中生成
inserted和deleted表。 由触发器修改的任何行都会进行版本控制。 这包括由启动触发器的语句修改的行，以及由触发器进行的任何数据修改。 - 支持多个活动结果集 (MARS)。 如果 MARS 会话在存在活动结果集时发出数据修改语句（如
INSERT、UPDATE或DELETE），则受修改语句影响的行将进行版本控制。 - 支持指定
ONLINE选项的索引操作。 - 支持基于行版本控制的事务隔离级别：
READ COMMITTED隔离级别的新实现，使用行版本控制提供语句级读取一致性。- 新的隔离级别
SNAPSHOT，提供事务级读取一致性。
行版本存储在版本存储区中。 如果在数据库上启用了 加速数据库恢复（ADR），则会在该数据库中创建版本存储。 否则，版本存储区在 tempdb 数据库中创建。
数据库必须有足够的空间用于版本存储区。 如果版本存储位于 tempdb 中，而 tempdb 数据库已满，更新操作会停止生成版本，但会继续成功执行，而读取操作可能会失败，因为所需的特定行版本不存在。 这会影响触发器、MARS 和联机索引等操作。
使用 ADR 且版本存储已满时，读取操作将继续成功，但生成版本的写入操作（如 UPDATE 和 DELETE）将失败。 如果数据库有足够的空间，INSERT 操作将继续成功。
为 READ COMMITTED 和 SNAPSHOT 事务使用行版本控制是一个两步过程：
将
READ_COMMITTED_SNAPSHOT和ALLOW_SNAPSHOT_ISOLATION数据库选项之一或两者设置为ON。在应用程序中设置适当的事务隔离级别：
- 当
READ_COMMITTED_SNAPSHOT数据库选项为ON时，设置READ COMMITTED隔离级别的事务使用行版本控制。 - 当
ALLOW_SNAPSHOT_ISOLATION数据库选项为ON时，事务可以设置SNAPSHOT隔离级别。
- 当
当 READ_COMMITTED_SNAPSHOT 或 ALLOW_SNAPSHOT_ISOLATION 数据库选项设置为 ON 时，数据库引擎向使用行版本控制操作数据的每个事务分配一个事务序列号 (XSN)。 事务在执行 BEGIN TRANSACTION 语句时开始。 但是，事务序列号从 BEGIN TRANSACTION 语句后的第一个读或写操作开始。 事务序列号每次分配时递增 1。
当 READ_COMMITTED_SNAPSHOT 或 ALLOW_SNAPSHOT_ISOLATION 数据库选项设置为 ON 时，将为数据库中执行的所有数据修改维护逻辑副本（版本）。 每当特定事务修改行时，数据库引擎实例会将该行先前提交映像的版本存储在版本存储区中。 每个版本都标记有进行更改的事务的事务序列号。 已修改行的版本使用链表链接。 最新的行值始终存储在当前数据库中，并链接到版本存储区中的版本行。
注意
对于大型对象（LOB）的修改，只有更改的片段会复制到版本存储区。
行版本会保留足够长的时间，以满足在基于行版本控制的隔离级别下运行的事务的要求。 数据库引擎跟踪最早的有用事务序列号，并定期删除所有标记有低于最早有用序列号的事务序列号的行版本。
当两个数据库选项都设置为 OFF 时，只有由触发器或 MARS 会话修改的行，或由联机索引操作读取的行才会进行版本控制。 这些行版本在不再需要时释放。 后台进程会删除过时的行版本。
注意
对于短运行事务，已修改行的版本可能会缓存在缓冲池中而不写入版本存储区。 如果对版本行的需求是短期的，该行会从缓冲池中删除，不会产生 I/O 开销。
读取数据时的行为
当在基于行版本控制的隔离下运行的事务读取数据时，读取操作不会在正在读取的数据上获取共享 (S) 锁，因此不会阻塞正在修改数据的事务。 此外，由于获取的锁数量减少，锁定资源的开销也最小化。 使用行版本控制的 READ COMMITTED 隔离和 SNAPSHOT 隔离旨在为版本化数据提供语句级或事务级读取一致性。
所有查询（包括在基于行版本控制的隔离级别下运行的事务）在编译和执行期间都会获取架构稳定性 (Sch-S) 锁。 因此，当并发事务在表上持有架构修改 (Sch-M) 锁时，查询会被阻塞。 例如，数据定义语言（DDL）操作在修改表的架构信息之前会获取 Sch-M 锁。 事务（包括在基于行版本控制的隔离级别下运行的事务）在尝试获取 Sch-S 锁时会被阻塞。 相反，持有 Sch-S 锁的查询会阻塞尝试获取 Sch-M 锁的并发事务。
当使用 SNAPSHOT 隔离级别的事务启动时，数据库引擎实例会记录所有当前活动的事务。 当 SNAPSHOT 事务读取具有版本链的行时，数据库引擎会跟随该链并检索满足以下条件的行：
事务序列号最接近但低于读取该行的快照事务的序列号。
不在快照事务启动时的活动事务列表中。
SNAPSHOT 事务执行的读取操作检索在 SNAPSHOT 事务启动时已提交的每一行的最后版本。 这提供了事务启动时数据的事务一致快照。
使用行版本控制的 READ COMMITTED 事务的操作方式大致相同。 不同之处在于，READ COMMITTED 事务在选择行版本时不使用自己的事务序列号。 每次启动语句时，READ COMMITTED 事务都会读取为该数据库引擎实例发出的最新事务序列号。 这是用于为该语句选择行版本的事务序列号。 这允许 READ COMMITTED 事务在每个语句启动时查看数据的快照。
注意
尽管使用行版本控制的 READ COMMITTED 事务在语句级别提供数据的事务一致视图，但由此类事务生成或访问的行版本会一直维护到事务完成。
修改数据时的行为
在启用优化锁定和未启用优化锁定的情况下，数据写入的行为有所不同。
在没有优化锁定的情况下修改数据
在使用行版本控制的 READ COMMITTED 事务中，更新行的选择是通过阻塞扫描完成的，在读取数据值时会在数据行上获取更新 (U) 锁。 这与不使用行版本控制的 READ COMMITTED 事务相同。 如果数据行不符合更新标准，将释放更新锁并且将锁定下一行并对其进行扫描。
在 SNAPSHOT 隔离下运行的事务采取乐观方法进行数据修改，仅在执行修改之前获取数据锁以强制实施约束。 否则，在数据要被修改之前不会获取锁。 当数据行满足更新条件时，SNAPSHOT 事务会验证该数据行未被在 SNAPSHOT 事务开始后提交的并发事务修改。 如果数据行在 SNAPSHOT 事务外部被修改，则会发生更新冲突，SNAPSHOT 事务终止。 更新冲突由数据库引擎处理，无法禁用更新冲突检测。
注意
在 READ COMMITTED 隔离下运行的更新操作在 SNAPSHOT 事务访问以下任何内容时，内部在 SNAPSHOT 隔离下执行：
具有外键约束的表。
在另一个表的外键约束中引用的表。
引用多个表的索引视图。
但是，即使是在这些条件下，更新操作仍会继续验证数据是否未经其他事务修改。 如果数据已被另一个事务修改，SNAPSHOT 事务会遇到更新冲突并终止。 更新冲突必须由应用程序处理和重试。
在优化锁定的情况下修改数据
启用优化锁定并启用 READ_COMMITTED_SNAPSHOT (RCSI) 数据库选项并使用默认 READ COMMITTED 隔离级别时，读取器不获取任何锁，写入器获取短持续时间的低级锁，而不是在事务结束时过期的锁。
建议启用 RCSI，以通过优化锁定获取最高效率。 当使用更严格的隔离级别（如 REPEATABLE READ 或 SERIALIZABLE）时，数据库引擎会为读取器和写入器持有行锁和页锁直到事务结束，从而导致阻塞增加和锁内存增加。
启用 RCSI 并使用默认 READ COMMITTED 隔离级别时，写入器根据行的最新提交版本按谓词筛选行，而不获取 U 锁。 查询仅在行符合条件且该行或页上有另一个活动写入事务时等待。 根据最新提交的版本进行限定，仅锁定限定行可减少阻塞并提高并发性。
如果在 RCSI 和默认 READ COMMITTED 隔离级别下检测到更新冲突，它们会自动处理和重试，对客户工作负荷没有任何影响。
启用优化锁定并使用 SNAPSHOT 隔离级别时，更新冲突的行为与未启用优化锁定时相同。 更新冲突必须由应用程序处理和重试。
注意
有关优化锁定的锁定后筛选 (LAQ) 功能的行为更改的更多信息，请参阅使用优化锁定和 RCSI 的查询行为更改。
行为摘要
下表总结了使用行版本控制的 SNAPSHOT 隔离和 READ COMMITTED 隔离之间的差异。
| 属性 | 使用行版本控制的 READ COMMITTED 隔离级别 |
SNAPSHOT 隔离级别 |
|---|---|---|
必须设置为 ON 以启用所需支持的数据库选项。 |
READ_COMMITTED_SNAPSHOT |
ALLOW_SNAPSHOT_ISOLATION |
| 会话如何请求特定类型的行版本控制。 | 使用默认 READ COMMITTED 隔离级别，或运行 SET TRANSACTION ISOLATION LEVEL 语句指定 READ COMMITTED 隔离级别。 这可以在事务开始后完成。 |
需要在事务开始前执行 SET TRANSACTION ISOLATION LEVEL 语句指定 SNAPSHOT 隔离级别。 |
| 语句读取的数据版本。 | 每个语句开始前提交的所有数据。 | 每个事务开始前提交的所有数据。 |
| 如何处理更新。 | 未启用优化锁定时：从行版本还原到实际数据以选择要更新的行，并在所选数据行上使用更新锁。 在要修改的实际数据行上获取排他锁。 无更新冲突检测。 启用优化锁定时：基于最后提交的版本选择行，不获取任何锁。 如果行符合更新条件，则获取排他行锁或页锁。 如果检测到更新冲突，则自动处理和重试更新冲突。 |
使用行版本选择要更新的行。 尝试在要修改的实际数据行上获取排他锁，如果数据已被另一个事务修改，则会发生更新冲突，快照事务终止。 |
| 更新冲突检测 | 未启用优化锁定时：无。 启用优化锁定时：如果检测到更新冲突，它们会自动处理和重试。 |
集成支持。 无法禁用。 |
行版本控制资源使用
行版本控制框架支持以下数据库引擎功能：
- 触发器
- 多个活动结果集 (MARS)
- 联机索引
行版本控制框架还支持以下基于行版本控制的事务隔离级别：
- 当
READ_COMMITTED_SNAPSHOT数据库选项设置为ON时，READ_COMMITTED事务使用行版本控制提供语句级读取一致性。 - 当
ALLOW_SNAPSHOT_ISOLATION数据库选项设置为ON时，SNAPSHOT事务使用行版本控制提供事务级读取一致性。
基于行版本控制的隔离级别通过消除读取操作上共享锁的使用来减少事务获取的锁数量。 这通过减少用于管理锁的资源来提高系统性能。 性能还通过减少事务被其他事务所获取的锁阻塞的次数来提高。
基于行版本控制的隔离级别增加了数据修改所需的资源。 启用这些选项会导致数据库的所有数据修改都进行版本控制。 即使没有使用基于行版本控制的隔离的活动事务，修改前的数据副本也会存储在版本存储区中。 修改后的数据包含指向版本存储区中版本化数据的指针。 对于大型对象，只有更改的对象部分存储在版本存储区中。
tempdb 中使用的空间
对于每个数据库引擎实例，版本存储区必须有足够的空间来保存行版本。 数据库管理员必须确保 tempdb 和其他数据库（如果已启用 ADR）有足够的空间来支持版本存储。 有两种类型的版本存储区：
- 联机索引生成版本存储区用于联机索引生成。
- 公共版本存储区用于所有其他数据修改操作。
行版本必须存储到活动事务需要访问它们为止。 后台线程会定期删除不再需要的行版本，并释放版本存储区中的空间。 长运行事务会阻止版本存储区中的空间释放，如果它满足以下任何条件：
- 它使用基于行版本控制的隔离。
- 它使用触发器、MARS 或联机索引生成操作。
- 它会生成行版本。
注意
当在事务内部调用触发器时，由触发器创建的行版本会一直维护到事务结束，即使触发器完成后不再需要这些行版本。 这也适用于使用行版本控制的 READ COMMITTED 事务。 对于这种类型的事务，只需要事务中的每个语句的数据库事务一致视图。 这意味着事务中为语句创建的行版本在语句完成后不再需要。 但是，事务中每个语句创建的行版本会一直维护到事务完成。
如果版本存储区在 tempdb 中，并且 tempdb 空间不足，数据库引擎会强制版本存储区收缩。 在收缩过程中，尚未生成行版本的最长运行事务被标记为受害者。 错误日志中会为每个受害者事务生成消息 3967。 如果事务被标记为受害者，它将无法再读取版本存储区中的行版本。 当它尝试读取行版本时，会生成消息 3966 并回滚事务。 如果收缩进程成功，则 tempdb 中就有可用空间。 否则，tempdb 运行空间不足，并出现下列情况：
写入操作继续执行但不生成版本。 错误日志中会生成一条信息消息 (3959)，但写数据的事务不受影响。
尝试访问由于
tempdb已满而未生成的行版本的事务将完全回滚并终止，出现错误 3958。
数据行中使用的空间
每个数据库行可能在行末尾最多使用 14 字节用于行版本控制信息。 行版本控制信息包含提交该版本的事务的事务序列号和指向版本行的指针。 在以下任何条件下，首次修改行或插入新行时会添加这 14 字节：
-
READ_COMMITTED_SNAPSHOT或ALLOW_SNAPSHOT_ISOLATION选项设置为ON。 - 表具有触发器。
- 正在使用多个活动结果集 (MARS)。
- 表上当前正在运行联机索引生成操作。
- 已启用加速数据库恢复（ADR）。
在以下所有条件下首次修改行时，这 14 字节会从数据库行中删除：
-
READ_COMMITTED_SNAPSHOT和ALLOW_SNAPSHOT_ISOLATION选项设置为OFF。 - 表上不再存在触发器。
- 当前没有使用 MARS。
- 当前未运行联机索引生成操作。
- 已禁用加速数据库恢复（ADR）。
如果使用任何行版本控制功能，可能需要为数据库分配额外的磁盘空间，以容纳每个数据库行的 14 字节。 如果当前页上没有足够的可用空间，则添加行版本控制信息会导致拆分索引页或分配新的数据页。 例如，如果平均行长度为 100 字节，额外的 14 字节会导致现有表增长高达 14%。
降低填充因子可能有助于防止或减少索引页的碎片。 要查看表或视图的数据和索引的当前页密度信息，可以使用 sys.dm_db_index_physical_stats。
持久版本存储区 (PVS) 使用的空间
启用 ADR 后，行版本可以通过以下方式之一存储在持久性版本存储（PVS）中，具体取决于修改前行的大小：
- 如果大小较小，则整个旧行版本将作为修改行的一部分存储。
- 如果大小是中间的，旧行版本与修改后的行之间的差异将作为修改行的一部分存储。 这种差异是采用一种允许数据库引擎根据需要重新构造整个旧行版本的方式构造的。
- 如果大小较大，则整个旧行版本存储在单独的内部表中。
前两种方法称为 行 版本存储。 最后一个方法称为 行外 版本存储。 不再需要行内版本时，会删除它们以释放页面上的空间。 同样，版本清理器会删除包含不再需要的行外版本的内部表中的页面。
将行版本存储为行的一部分可优化需要读取行版本的事务的数据检索。 如果版本是连续存储的，则不需要单独读取行外 PVS 页。
sys.dm_db_index_physical_stats DMV 提供索引分区中存储在行内和行外的版本数量和类型。 行内存储的版本数据的总大小在 total_inrow_version_payload_size_in_bytes 列中报告。
行外版本存储的大小在 sys.dm_tran_persistent_version_store_stats 动态管理视图的 persistent_version_store_size_kb 列中报告。
大型对象中使用的空间
数据库引擎支持几种数据类型，可以存储长度高达 2 GB 的大型字符串，例如：nvarchar(max)、varchar(max)、varbinary(max)、ntext、text 和 image。 使用这些数据类型存储的大型数据存储在一系列链接到数据行的数据片段中。 行版本控制信息存储在用于存储这些大型字符串的每个片段中。 数据片段存储在表中专门用于大型对象的一组页中。
当新的大型值添加到数据库时，每个片段最多分配 8040 字节的数据。 早期版本的数据库引擎每个片段最多存储 8080 字节的 ntext、text 或 image 数据。
数据库从早期版本的 SQL Server 升级到 SQL Server 时，现有的 ntext、text 和 image 大型对象 (LOB) 数据并未更新来为行版本控制信息释放一些空间。 但是，首次修改 LOB 数据时，会动态升级以支持存储版本控制信息。 即使未生成行版本也是如此。 LOB 数据升级后，每个片段存储的最大字节数从 8080 字节减少到 8040 字节。 升级过程相当于删除 LOB 值并重新插入相同的值。 即使只修改 1 个字节也会升级 LOB 数据。 这是每个 ntext、text 或 image 列的一次性操作，但每个操作可能会根据 LOB 数据的大小生成大量的页分配和 I/O 活动。 如果修改被完全记录，还可能生成大量的日志活动。 如果数据库恢复模式未设置为 WRITETEXT 和 UPDATETEXT 操作的日志记录最少。
应分配足够的磁盘空间来满足此要求。
监视行版本控制和版本存储区
为了监视行版本控制、版本存储区和快照隔离过程的性能和问题，数据库引擎以动态管理视图 (DMV) 和性能计数器的形式提供工具。
DMV
下列 DMV 提供有关 tempdb 的当前系统状态、版本存储区以及使用行版本控制的事务的信息。
sys.dm_db_file_space_usage。 返回数据库中每个文件的空间使用情况信息。 有关详细信息，请参阅 sys.dm_db_file_space_usage (Transact-SQL)。sys.dm_db_session_space_usage。 按会话返回数据库的页分配和释放活动。 有关详细信息，请参阅 sys.dm_db_session_space_usage (Transact-SQL)。sys.dm_db_task_space_usage。 按任务返回数据库的页分配和释放活动。 有关详细信息，请参阅 sys.dm_db_task_space_usage (Transact-SQL)。sys.dm_tran_top_version_generators。 返回版本存储区中生成最多版本的对象的虚拟表。 它按 database_id 和 rowset_id 对前 256 个聚合记录长度进行分组。 使用此函数查找版本存储区的最大使用者。 仅适用于tempdb中的版本存储区。 有关详细信息，请参阅 sys.dm_tran_top_version_generators (Transact-SQL)。sys.dm_tran_version_store。 返回显示公共版本存储区中所有版本记录的虚拟表。 仅适用于tempdb中的版本存储区。 有关详细信息，请参阅 sys.dm_tran_version_store (Transact-SQL)。sys.dm_tran_version_store_space_usage。 返回一个虚拟表，该表显示每个数据库的版本存储记录使用的tempdb中的总空间。 仅适用于tempdb中的版本存储区。 有关详细信息，请参阅 sys.dm_tran_version_store_space_usage (Transact-SQL)。注意
查询
sys.dm_tran_top_version_generators和sys.dm_tran_version_store可能会非常耗费资源，因为两者都需要扫描可能很大的整个版本存储库。sys.dm_tran_version_store_space_usage运行高效且成本不高，因为它不遍历单个版本存储区记录，而是返回每个数据库在tempdb中消耗的聚合版本存储区空间。sys.dm_tran_active_snapshot_database_transactions。 返回一个虚拟表，其中包含使用行版本控制的 SQL Server 实例中的所有数据库中的所有活动事务。 系统事务不会出现在此 DMV 中。 有关详细信息，请参阅 sys.dm_tran_active_snapshot_database_transactions (Transact-SQL)。sys.dm_tran_transactions_snapshot。 返回显示每个事务获取的快照的虚拟表。 快照包含使用行版本控制的活动事务的序列号。 有关详细信息，请参阅 sys.dm_tran_transactions_snapshot (Transact-SQL)。sys.dm_tran_current_transaction。 返回显示当前会话中事务的行版本控制相关状态信息的单行。 有关详细信息，请参阅 sys.dm_tran_current_transaction (Transact-SQL)。sys.dm_tran_current_snapshot。 返回显示当前快照隔离事务开始时所有活动事务的虚拟表。 如果当前事务使用快照隔离，此函数不返回行。 DMVsys.dm_tran_current_snapshot类似于sys.dm_tran_transactions_snapshot，只不过它仅返回当前快照的活动事务。 有关详细信息，请参阅 sys.dm_tran_current_snapshot (Transact-SQL)。sys.dm_tran_persistent_version_store_stats。 返回启用加速数据库恢复时所使用的每个数据库中持久版本存储的统计信息。 有关详细信息，请参阅 sys.dm_tran_persistent_version_store_stats (Transact-SQL)。
性能计数器
以下性能计数器监视 tempdb 中的版本存储区以及使用行版本控制的事务。 性能计数器包含在 SQLServer:Transactions 性能对象中。
tempdb 中的可用空间 (KB)。 监视
tempdb数据库中的可用空间 (KB)。tempdb中必须有足够的可用空间来容纳支持快照隔离的版本存储区。以下公式提供了版本存储区大小的大致估计。 对于长时间运行的事务，监视生成和清理速率以估计版本存储区的最大大小可能很有用。
[公共版本存储区的大小] = 2 * [每分钟生成的版本存储区数据] * [事务的最长运行时间（分钟）]
事务的最长运行时间不应包括联机索引生成。 因为这些操作在非常大的表上可能需要很长时间，所以联机索引生成使用单独的版本存储区。 联机索引生成版本存储区的大致大小等于联机索引生成活动时表中修改的数据量（包括所有索引）。
版本存储区大小 (KB)。 监视
tempdb中所有版本存储区的大小（以 KB 为单位）。 此信息有助于确定版本存储区在tempdb数据库中所需的空间大小。 监视计数器一段时间，可以获得有用的信息来估计在tempdb数据库中所需的额外空间。版本生成速率 (KB/s)。 监视
tempdb中所有版本存储区的版本生成速率（以 KB/秒为单位）。版本清理速率 (KB/s)。 监视
tempdb中所有版本存储区的版本清理速率（以 KB/秒为单位）。注意
版本生成速率（KB/秒）和版本清理速率（KB/秒）的信息可以用于预测
tempdb空间要求。版本存储区单元计数。 监视版本存储区单元的计数。
版本存储区单元创建。 监视实例启动以来为存储行版本而创建的版本存储区单元总数。
版本存储区单元截断。 监视实例启动以来截断的版本存储区单元总数。 当 SQL Server 确定不需要任何存储在版本存储区单元中的版本行来运行活动事务时，版本存储区单元即被截断。
更新冲突比率。 监视存在更新冲突的更新快照事务与更新快照事务总数的比值。
最长事务运行时间。 监视使用行版本控制的任何事务的最长运行时间（以秒为单位）。 这可用于确定是否有事务运行时间异常。
事务。 监视活动事务的总数。 不包括系统事务。
快照事务。 监视活动快照事务的总数。
更新快照事务。 监视执行更新操作的活动快照事务的总数。
非快照版本事务。 监视生成版本记录的活动非快照事务的总数。
注意
更新快照事务和非快照版本事务的总和表示参与版本生成的事务总数。 快照事务与更新快照事务的差值表示只读快照事务的数量。
基于行版本控制的隔离级别示例
以下示例显示了 SNAPSHOT 隔离事务和使用行版本控制的 READ COMMITTED 事务之间的行为差异。
答： 使用 SNAPSHOT 隔离
在此示例中，在 SNAPSHOT 隔离下运行的事务读取数据，然后该数据被另一个事务修改。 SNAPSHOT 事务不会阻塞另一个事务所执行的更新操作，它会继续从版本化行读取数据，忽略数据修改。 但是，当 SNAPSHOT 事务尝试修改已被另一个事务修改的数据时，SNAPSHOT 事务会生成错误并终止。
在会话 1 上：
USE AdventureWorks2022;
GO
-- Enable snapshot isolation on the database.
ALTER DATABASE AdventureWorks2022 SET ALLOW_SNAPSHOT_ISOLATION ON;
GO
-- Start a snapshot transaction
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
GO
BEGIN TRANSACTION;
-- This SELECT statement will return
-- 48 vacation hours for the employee.
SELECT BusinessEntityID, VacationHours
FROM HumanResources.Employee
WHERE BusinessEntityID = 4;
在会话 2 上：
USE AdventureWorks2022;
GO
-- Start a transaction.
BEGIN TRANSACTION;
-- Subtract a vacation day from employee 4.
-- Update is not blocked by session 1 since
-- under snapshot isolation shared locks are
-- not requested.
UPDATE HumanResources.Employee
SET VacationHours = VacationHours - 8
WHERE BusinessEntityID = 4;
-- Verify that the employee now has 40 vacation hours.
SELECT VacationHours
FROM HumanResources.Employee
WHERE BusinessEntityID = 4;
在会话 1 上：
-- Reissue the SELECT statement - this shows
-- the employee having 48 vacation hours. The
-- snapshot transaction is still reading data from
-- the older, versioned row.
SELECT BusinessEntityID, VacationHours
FROM HumanResources.Employee
WHERE BusinessEntityID = 4;
在会话 2 上：
-- Commit the transaction; this commits the data
-- modification.
COMMIT TRANSACTION;
GO
在会话 1 上：
-- Reissue the SELECT statement - this still
-- shows the employee having 48 vacation hours
-- even after the other transaction has committed
-- the data modification.
SELECT BusinessEntityID, VacationHours
FROM HumanResources.Employee
WHERE BusinessEntityID = 4;
-- Because the data has been modified outside of the
-- snapshot transaction, any further data changes to
-- that data by the snapshot transaction will cause
-- the snapshot transaction to fail. This statement
-- will generate a 3960 error and the transaction will
-- terminate.
UPDATE HumanResources.Employee
SET SickLeaveHours = SickLeaveHours - 8
WHERE BusinessEntityID = 4;
-- Undo the changes to the database from session 1.
-- This will not undo the change from session 2.
ROLLBACK TRANSACTION;
GO
B. 使用基于行版本控制的 READ COMMITTED 隔离
在此示例中，使用行版本控制的 READ COMMITTED 事务与另一个事务并发运行。 READ COMMITTED 事务的行为与 SNAPSHOT 事务不同。 与 SNAPSHOT 事务一样，READ COMMITTED 事务即使在另一个事务修改数据后也会读取版本化行。 但与 SNAPSHOT 事务不同，READ COMMITTED 事务：
- 在另一个事务提交数据更改后读取修改的数据。
- 能够更新另一个事务所修改的数据，而
SNAPSHOT事务无法做到这一点。
在会话 1 上：
USE AdventureWorks2022;
GO
-- Enable READ_COMMITTED_SNAPSHOT on the database.
-- For this statement to succeed, this session
-- must be the only connection to the AdventureWorks2022
-- database.
ALTER DATABASE AdventureWorks2022 SET READ_COMMITTED_SNAPSHOT ON;
GO
-- Start a read-committed transaction
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
GO
BEGIN TRANSACTION;
-- This SELECT statement will return
-- 48 vacation hours for the employee.
SELECT BusinessEntityID, VacationHours
FROM HumanResources.Employee
WHERE BusinessEntityID = 4;
在会话 2 上：
USE AdventureWorks2022;
GO
-- Start a transaction.
BEGIN TRANSACTION;
-- Subtract a vacation day from employee 4.
-- Update is not blocked by session 1 since
-- under read-committed using row versioning shared locks are
-- not requested.
UPDATE HumanResources.Employee
SET VacationHours = VacationHours - 8
WHERE BusinessEntityID = 4;
-- Verify that the employee now has 40 vacation hours.
SELECT VacationHours
FROM HumanResources.Employee
WHERE BusinessEntityID = 4;
在会话 1 上：
-- Reissue the SELECT statement - this still shows
-- the employee having 48 vacation hours. The
-- read-committed transaction is still reading data
-- from the versioned row and the other transaction
-- has not committed the data changes yet.
SELECT BusinessEntityID, VacationHours
FROM HumanResources.Employee
WHERE BusinessEntityID = 4;
在会话 2 上：
-- Commit the transaction.
COMMIT TRANSACTION;
GO
在会话 1 上：
-- Reissue the SELECT statement which now shows the
-- employee having 40 vacation hours. Being
-- read-committed, this transaction is reading the
-- committed data. This is different from snapshot
-- isolation which reads from the versioned row.
SELECT BusinessEntityID, VacationHours
FROM HumanResources.Employee
WHERE BusinessEntityID = 4;
-- This statement, which caused the snapshot transaction
-- to fail, will succeed with read-committed using row versioning.
UPDATE HumanResources.Employee
SET SickLeaveHours = SickLeaveHours - 8
WHERE BusinessEntityID = 4;
-- Undo the changes to the database from session 1.
-- This will not undo the change from session 2.
ROLLBACK TRANSACTION;
GO
启用基于行版本控制的隔离级别
数据库管理员通过在 READ_COMMITTED_SNAPSHOT 语句中使用 ALLOW_SNAPSHOT_ISOLATION 和 ALTER DATABASE 数据库选项来控制数据库级别的行版本控制设置。
当 READ_COMMITTED_SNAPSHOT 数据库选项设置为 ON 时，支持该选项的机制会立即激活。 设置 READ_COMMITTED_SNAPSHOT 选项时，数据库中只允许执行 ALTER DATABASE 命令的连接。 在 ALTER DATABASE 完成之前，数据库中不能有其他打开的连接。 数据库不必一定要处于单用户模式下。
以下 Transact-SQL 语句启用 READ_COMMITTED_SNAPSHOT：
ALTER DATABASE AdventureWorks2022 SET READ_COMMITTED_SNAPSHOT ON;
当 ALLOW_SNAPSHOT_ISOLATION 数据库选项设置为 ON 时，数据库引擎实例在数据库中所有已修改数据的活动事务完成之前不会开始为修改的数据生成行版本。 如果存在活动的修改事务，数据库引擎会将该选项的状态设置为 PENDING_ON。 所有修改事务完成后，该选项的状态将更改为 ON。 在该选项变为 SNAPSHOT 之前，用户不能在数据库中启动 ON 事务。 类似地，当数据库管理员将 PENDING_OFF 选项设置为 ALLOW_SNAPSHOT_ISOLATION 时，数据库会经过 OFF 状态。
以下 Transact-SQL 语句启用 ALLOW_SNAPSHOT_ISOLATION：
ALTER DATABASE AdventureWorks2022 SET ALLOW_SNAPSHOT_ISOLATION ON;
下表列出并描述了 ALLOW_SNAPSHOT_ISOLATION 选项的状态。 将 ALTER DATABASE 与 ALLOW_SNAPSHOT_ISOLATION 选项一起使用不会阻塞当前正在访问数据库数据的用户。
当前数据库的 SNAPSHOT 隔离状态 |
说明 |
|---|---|
OFF |
不激活对 SNAPSHOT 隔离事务的支持。 不允许任何 SNAPSHOT 隔离事务。 |
PENDING_ON |
对 SNAPSHOT 隔离事务的支持处于转换状态（从 OFF 到 ON）。 打开的事务必须完成。不允许任何 SNAPSHOT 隔离事务。 |
ON |
激活对 SNAPSHOT 隔离事务的支持。允许 SNAPSHOT 事务。 |
PENDING_OFF |
对 SNAPSHOT 隔离事务的支持处于转换状态（从 ON 到 OFF）。此时之后启动的 SNAPSHOT 事务无法访问此数据库。 现有的 SNAPSHOT 事务仍可以访问此数据库。 现有的写入事务仍在此数据库中使用版本控制。 直到数据库 PENDING_OFF 隔离状态为 OFF 时启动的所有 SNAPSHOT 事务完成后，状态 SNAPSHOT 才会变为 ON。 |
使用 sys.databases 目录视图确定两个行版本控制数据库选项的状态。
对用户表和存储在 master 和 msdb 中的某些系统表的任何更新都会生成行版本。
在 master 和 msdb 数据库中，ALLOW_SNAPSHOT_ISOLATION 选项会自动设置为 ON，并且无法禁用。
用户不能在 master、tempdb 或 msdb 中将 READ_COMMITTED_SNAPSHOT 选项设置为 ON。
使用基于行版本控制的隔离级别
行版本控制框架始终启用，并被多个功能使用。 除了提供基于行版本控制的隔离级别外，它还用于支持触发器和多个活动结果集 (MARS) 会话中的修改，并支持联机索引操作的数据读取。
基于行版本控制的隔离级别在数据库级别启用。 任何从已启用数据库访问对象的应用程序都可以使用以下隔离级别运行查询：
通过将
READ COMMITTED数据库选项设置为READ_COMMITTED_SNAPSHOT来使用行版本控制的ON隔离级别，如以下代码示例所示：ALTER DATABASE AdventureWorks2022 SET READ_COMMITTED_SNAPSHOT ON;当数据库为
READ_COMMITTED_SNAPSHOT启用时，在READ COMMITTED隔离级别下运行的所有查询都使用行版本控制，这意味着读取操作不会阻塞更新操作。通过将
ALLOW_SNAPSHOT_ISOLATION数据库选项设置为ON来使用SNAPSHOT隔离级别，如以下代码示例所示：ALTER DATABASE AdventureWorks2022 SET ALLOW_SNAPSHOT_ISOLATION ON;在使用跨数据库查询时，在
SNAPSHOT隔离下运行的事务可以访问ALLOW_SNAPSHOT_ISOLATION数据库选项设置为ON的数据库中的表。 要访问ALLOW_SNAPSHOT_ISOLATION数据库选项未设置为ON的数据库中的表，必须更改隔离级别。 例如，以下代码示例显示了在SELECT事务下运行时连接两个表的SNAPSHOT语句。 一个表属于未启用SNAPSHOT隔离的数据库。 当SELECT语句在SNAPSHOT隔离下运行时，它无法成功执行。SET TRANSACTION ISOLATION LEVEL SNAPSHOT; BEGIN TRANSACTION; SELECT t1.col5, t2.col5 FROM Table1 as t1 INNER JOIN SecondDB.dbo.Table2 as t2 ON t1.col1 = t2.col2;以下代码示例显示了相同的
SELECT语句，该语句已修改为在访问特定表时将事务隔离级别更改为READ COMMITTED。 由于此更改，SELECT语句成功执行。SET TRANSACTION ISOLATION LEVEL SNAPSHOT; BEGIN TRANSACTION; SELECT t1.col5, t2.col5 FROM Table1 as t1 WITH (READCOMMITTED) INNER JOIN SecondDB.dbo.Table2 as t2 ON t1.col1 = t2.col2;
使用基于行版本控制的隔离级别事务的限制
使用基于行版本控制的隔离级别时，请考虑以下限制：
READ_COMMITTED_SNAPSHOT不能在tempdb、msdb或master中启用。全局临时表存储在
tempdb中。 在SNAPSHOT事务中访问全局临时表时，必须满足以下条件之一：- 在
tempdb中将ALLOW_SNAPSHOT_ISOLATION数据库选项设置为ON。 - 使用隔离提示更改语句的隔离级别。
- 在
当发生以下情况时，
SNAPSHOT事务失败：- 在
SNAPSHOT事务启动后但在SNAPSHOT事务访问数据库之前，数据库变为只读。 - 如果访问多个数据库，在
SNAPSHOT事务启动后但在SNAPSHOT事务访问数据库之前，数据库状态发生更改，导致数据库恢复。 例如：数据库被设置为OFFLINE然后再设置为ONLINE，由于AUTO_CLOSE选项设置为ON导致数据库自动关闭并重新打开，或者数据库被分离并重新附加。
- 在
分布式事务（包括分布式分区数据库中的查询）在
SNAPSHOT隔离下不受支持。数据库引擎不保留系统元数据的多个版本。 表和其他数据库对象（索引、视图、数据类型、存储过程和公共语言运行时函数）上的数据定义语言 (DDL) 语句会更改元数据。 如果 DDL 语句修改对象，
SNAPSHOT隔离下对该对象的任何并发引用会导致SNAPSHOT事务失败。 当READ_COMMITTED_SNAPSHOT数据库选项设置为ON时，READ COMMITTED事务没有此限制。例如，数据库管理员执行以下
ALTER INDEX语句。USE AdventureWorks2022; GO ALTER INDEX AK_Employee_LoginID ON HumanResources.Employee REBUILD; GO执行
ALTER INDEX语句时任何活动的快照事务，如果在HumanResources.Employee语句执行后尝试引用ALTER INDEX表，将收到错误。 使用行版本控制的READ COMMITTED事务不受影响。注意
BULK INSERT操作可能导致目标表元数据更改（例如，禁用约束检查时）。 当这种情况发生时，访问批量插入表的并发SNAPSHOT隔离事务会失败。
自定义锁定和行版本控制
自定义锁超时
当数据库引擎实例无法向事务授予锁，因为另一个事务已对资源拥有冲突锁时，第一个事务会被阻塞，等待现有锁被释放。 默认情况下，锁等待没有超时时间段，因此事务可能会无限期地被阻塞。
注意
使用 sys.dm_os_waiting_tasks 动态管理视图来确定某个任务是否正在被阻塞以及是什么在阻塞它。 有关详细信息和示例，请参阅了解并解决 SQL Server 阻塞问题。
LOCK_TIMEOUT 设置允许应用程序设置语句等待阻塞资源的最长时间。 当语句的等待时间超过 LOCK_TIMEOUT 设置时，被阻塞的语句会自动取消，并返回错误消息 1222 (Lock request time-out period exceeded)。 然而，包含该语句的任何事务不会回滚。 因此，应用程序必须有一个能够捕获错误消息 1222 的错误处理程序。 如果应用程序不捕获该错误，应用程序可能会在不知道事务中的单个语句已被取消但事务仍保持活动状态的情况下继续执行。 可能会发生错误，因为事务中后面的语句可能依赖于从未执行的语句。
实现一个捕获错误消息 1222 的错误处理程序，使应用程序能够处理超时情况并采取补救措施，例如：自动重新提交被阻塞的语句或回滚整个事务。
重要说明
使用显式事务并要求事务在收到错误 1222 时终止的应用程序，必须在错误处理中显式回滚事务。 如果没有这个操作，其他语句可能会在事务保持活动状态时无意地在同一会话上执行，如果事务稍后回滚，会导致事务日志无限制增长和数据丢失。
要确定当前的 LOCK_TIMEOUT 设置，请执行 @@LOCK_TIMEOUT 函数：
SELECT @@LOCK_TIMEOUT;
GO
自定义事务隔离级别
READ COMMITTED 是数据库引擎的默认隔离级别。 如果应用程序必须在不同的隔离级别下运行，它可以使用以下方法设置隔离级别：
- 运行 SET TRANSACTION ISOLATION LEVEL 语句。
- 使用
System.Data.SqlClient命名空间的 ADO.NET 应用程序可以通过使用SqlConnection.BeginTransaction方法指定IsolationLevel选项。 - 使用 ADO 的应用程序可以设置
Autocommit Isolation Levels属性。 - 启动事务时，使用 OLE DB 的应用程序可以调用
ITransactionLocal::StartTransaction，并将isoLevel设置为所需的事务隔离级别。 在自动提交模式下指定隔离级别时，使用 OLE DB 的应用程序可以将DBPROPSET_SESSION属性DBPROP_SESS_AUTOCOMMITISOLEVELS设置为所需的事务隔离级别。 - 使用 ODBC 的应用程序可以使用
SQLSetConnectAttr来设置SQL_COPT_SS_TXN_ISOLATION。
当指定隔离级别时，会话中所有查询和数据操作语言 (DML) 语句的锁定行为都在该隔离级别下运行。 隔离级别保持有效，直到会话终止或隔离级别被设置为另一个级别。
以下示例设置 SERIALIZABLE 隔离级别：
USE AdventureWorks2022;
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
GO
BEGIN TRANSACTION;
SELECT BusinessEntityID
FROM HumanResources.Employee;
COMMIT;
GO
如果需要，可以通过指定表级提示来覆盖单个查询或 DML 语句的隔离级别。 指定表级提示不会影响会话中的其他语句。
要确定当前设置的事务隔离级别，请使用如下示例所示的 DBCC USEROPTIONS 语句。 结果集可能与你的系统上的结果集不同。
USE AdventureWorks2022;
GO
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
GO
DBCC USEROPTIONS;
GO
结果集如下。
Set Option Value
---------------------------- -------------------------------------------
textsize 2147483647
language us_english
dateformat mdy
datefirst 7
... ...
Isolation level repeatable read
(14 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
锁提示
可以为 SELECT、INSERT、UPDATE、DELETE 和 MERGE 语句中的各个表引用指定锁定提示。 这些提示指定数据库引擎实例对表数据使用的锁定类型或行版本控制类型。 当需要更精细地控制在对象上获取的锁类型时，可以使用表级锁定提示。 这些锁定提示会覆盖会话的当前事务隔离级别。
注意
启用优化锁定时，不建议使用锁定提示。 尽管遵循表和查询提示，但它们会减少优化锁定的优势。 有关详细信息，请参阅 避免对优化锁定使用锁定提示。
有关特定锁定提示及其行为的详细信息，请参阅表提示 (Transact-SQL)。
注意
我们建议仅在必要时使用表级锁定提示来更改默认锁定行为。 强制设置锁定级别可能会对并发性产生不利影响。
即使在处理带有锁定提示的语句（该提示在读取数据时阻止对共享锁的请求）时，数据库引擎在读取元数据时可能仍需要获取锁。 例如，在 SELECT 隔离级别下运行或使用 READ UNCOMMITTED 提示的 NOLOCK 语句在读取数据时不会获取共享锁，但在读取系统目录视图时有时可能会请求锁。 这意味着，当并发事务正在修改表的元数据时，这样的 SELECT 语句有可能被阻塞。
如以下示例所示，如果事务隔离级别设置为 SERIALIZABLE，并且在 SELECT 语句中使用表级锁定提示 NOLOCK，则通常用于维护 SERIALIZABLE 事务的键范围锁将不会被获取。
USE AdventureWorks2022;
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
GO
BEGIN TRANSACTION;
GO
SELECT JobTitle
FROM HumanResources.Employee WITH (NOLOCK);
GO
-- Get information about the locks held by
-- the transaction.
SELECT resource_type,
resource_subtype,
request_mode
FROM sys.dm_tran_locks
WHERE request_session_id = @@SPID;
-- End the transaction.
ROLLBACK;
GO
获取的引用 HumanResources.Employee 的唯一锁是架构稳定性 (Sch-S) 锁。 在这种情况下，不再保证可序列化。
ALTER TABLE 的 LOCK_ESCALATION 选项可避免在锁升级期间使用表锁，并在分区表上启用 HoBT（分区）锁。 此选项不是锁定提示，可用于减少锁升级。 有关详细信息，请参阅 ALTER TABLE (Transact-SQL)。
自定义索引的锁定
在大多数情况下，数据库引擎使用动态锁定策略，自动为查询选择最佳锁定粒度。 我们建议你不要覆盖默认锁定级别，除非表或索引的访问模式已被充分理解且一致，并且存在需要解决的资源争用问题。 覆盖锁定级别可能会显著阻碍对表或索引的并发访问。 例如，在用户频繁访问的大型表上仅指定表级锁可能会导致瓶颈，因为用户必须等待表级锁释放后才能访问该表。
在少数情况下，如果访问模式已被充分理解且一致，禁止页级或行级锁定可能是有益的。 例如，一个数据库应用程序使用一个每周在批处理过程中更新的查找表。 并发读取者使用共享 (S) 锁访问该表，而每周的批处理更新使用排他 (X) 锁访问该表。 通过允许读取者通过共享表锁并发访问表，关闭该表的页级和行级锁定可减少整周的锁定开销。 当批处理作业运行时，它可以高效地完成更新，因为它获取了排他表锁。
由于每周批处理更新在运行时会阻止并发读取器访问表，因此关闭页锁定和行锁定可能是可取的，也可能不可取。 如果批处理作业只更改几行或几页，你可以更改锁定级别以允许行级或页级锁定，这将使其他会话能够读取该表而不会被阻塞。 如果批处理作业有大量更新，获取表的排他锁可能是确保批处理作业高效运行的最佳方式。
在某些工作负荷中，当两个并发操作在同一个表上获取行锁，然后由于都需要锁定页而相互阻塞时，可能会发生一种死锁。 禁止行锁会迫使其中一个操作等待，从而避免死锁。 有关死锁的详细信息，请参阅死锁指南。
可以使用 CREATE INDEX 和 ALTER INDEX 语句设置索引上使用的锁定粒度。 此外，CREATE TABLE 和 ALTER TABLE 语句可用于在 PRIMARY KEY 和 UNIQUE 约束上设置锁定粒度。 对于后向兼容，还可以使用 sp_indexoption 系统存储过程设置粒度。 要显示给定索引的当前锁定选项，请使用 INDEXPROPERTY 函数。 对于给定的索引，可以禁止页级锁、行级锁，或同时禁止页级和行级锁。
| 禁止的锁 | 被以下对象访问的索引 |
|---|---|
| 页面级别 | 行级和表级锁 |
| 行级别 | 页级和表级锁 |
| 页级和行级 | 表级锁 |
高级事务信息
嵌套事务
显式事务可以嵌套。 这主要是为了支持存储过程中的事务，这些存储过程可以从已在事务中的进程或从没有活动事务的进程调用。
以下示例展示了嵌套事务的使用。 如果在事务活动时调用 TransProc 过程，则 TransProc 中的嵌套事务的结果由外部事务控制，其 INSERT 语句根据外部事务的提交或回滚而提交或回滚。 如果 TransProc 由没有未完成事务的进程执行，过程末尾的 COMMIT TRANSACTION 将提交 INSERT 语句。
SET QUOTED_IDENTIFIER OFF;
GO
SET NOCOUNT OFF;
GO
CREATE TABLE TestTrans
(
ColA INT PRIMARY KEY,
ColB CHAR(3) NOT NULL
);
GO
CREATE PROCEDURE TransProc
@PriKey INT,
@CharCol CHAR(3)
AS
BEGIN TRANSACTION InProc;
INSERT INTO TestTrans VALUES (@PriKey, @CharCol);
INSERT INTO TestTrans VALUES (@PriKey + 1, @CharCol);
COMMIT TRANSACTION InProc;
GO
/* Start a transaction and execute TransProc. */
BEGIN TRANSACTION OutOfProc;
GO
EXEC TransProc 1, 'aaa';
GO
/* Roll back the outer transaction, this will
roll back TransProc's nested transaction. */
ROLLBACK TRANSACTION OutOfProc;
GO
EXECUTE TransProc 3,'bbb';
GO
/*
The following SELECT statement shows only rows 3 and 4 are
still in the table. This indicates that the commit
of the inner transaction from the first EXECUTE statement of
TransProc was overridden by the subsequent roll back of the
outer transaction.
*/
SELECT *
FROM TestTrans;
GO
当外部事务活动时，数据库引擎会忽略内部事务的提交。 事务是提交还是回滚，取决于最外层事务结束时的提交或回滚。 如果外部事务提交，内部嵌套事务也会提交。 如果外部事务回滚，那么所有内部事务也会回滚，无论内部事务是否单独提交。
每次对 COMMIT TRANSACTION 或 COMMIT WORK 的调用都适用于最后执行的 BEGIN TRANSACTION。 如果 BEGIN TRANSACTION 语句是嵌套的，那么 COMMIT 语句只适用于最后一个嵌套事务，即最内层事务。 即使嵌套事务内部的 COMMIT TRANSACTION transaction_name 语句引用外部事务的事务名称，该提交也只应用于最内部的事务。
在一组命名的嵌套事务中，不允许 ROLLBACK TRANSACTION 语句的 transaction_name 参数引用内部事务。 transaction_name 只能引用最外层事务的事务名称。 如果在一组嵌套事务的任何级别执行使用外部事务名称的 ROLLBACK TRANSACTION transaction_name 语句，所有嵌套事务都将回滚。 如果在一组嵌套事务的任何级别执行不带 transaction_name 参数的 ROLLBACK WORK 或 ROLLBACK TRANSACTION 语句，它将回滚所有嵌套事务，包括最外层事务。
@@TRANCOUNT 函数记录当前事务嵌套级别。 每个 BEGIN TRANSACTION 语句会将 @@TRANCOUNT 加 1。 每个 COMMIT TRANSACTION 或 COMMIT WORK 语句将 @@TRANCOUNT 减 1。 没有事务名称的 ROLLBACK WORK 或 ROLLBACK TRANSACTION 语句将回滚所有嵌套事务，并将 @@TRANCOUNT 递减到 0。 使用一组嵌套事务中最外层事务名称的 ROLLBACK TRANSACTION 会回滚所有嵌套事务，并将 @@TRANCOUNT 减为 0。 要确定是否已在事务中，请查询 SELECT @@TRANCOUNT 以查看其是否为 1 或更大。 如果 @@TRANCOUNT 为 0，则不在事务中。
使用绑定会话
绑定会话便于在同一服务器上的多个会话之间协调操作。 绑定会话允许两个或更多会话共享相同的事务和锁，并可以处理相同的数据而不会发生锁冲突。 绑定会话可以从同一应用程序内的多个会话或具有独立会话的多个应用程序创建。
要参与绑定会话，会话必须调用 sp_getbindtoken 或 srv_getbindtoken（通过开放式数据服务）来获取绑定令牌。 绑定令牌是一个字符串，用于唯一标识每个绑定事务。 然后将绑定令牌发送到其他会话，以与当前会话绑定。 其他会话通过调用 sp_bindsession，并使用从第一个会话中接收到的绑定令牌绑定到事务。
注意
会话必须具有活动的用户事务，sp_getbindtoken 或 srv_getbindtoken 才能成功。
绑定令牌必须从创建第一个会话的应用程序代码传输到随后将其会话绑定到第一个会话的应用程序代码。 没有应用程序可以用来获取由另一个进程启动的事务绑定令牌的 Transact-SQL 语句或 API 函数。 可用于传输绑定令牌的一些方法包括以下几种：
如果所有会话都从同一个应用程序进程启动，绑定令牌可以存储在全局内存中或作为参数传递给函数。
如果会话由单独的应用程序进程创建，绑定令牌可以使用进程间通信 (IPC) 传输，例如远程过程调用 (RPC) 或动态数据交换 (DDE)。
绑定令牌可以存储在数据库引擎实例的表中，供想要绑定到第一个会话的进程读取。
一组绑定会话中任何时候只能有一个会话处于活动状态。 如果一个会话正在实例上执行语句或有来自实例的待处理结果，绑定到同一令牌的其他任何会话都无法访问该实例，直到当前会话完成处理或取消当前语句。 如果实例正忙于处理来自另一个绑定会话的语句，会发生错误，指示事务空间正在使用，会话应稍后重试。
绑定会话时，每个会话保留其隔离级别设置。 使用 SET TRANSACTION ISOLATION LEVEL 更改一个会话的隔离级别设置不会影响绑定到同一令牌的任何其他会话的设置。
绑定会话的类型
绑定会话有两种类型：本地和分布式。
本地绑定会话 允许绑定会话在单个数据库引擎实例中共享单个事务的事务空间。
分布式绑定会话 允许绑定会话在两个或更多实例之间共享同一事务，直到整个事务通过使用 Microsoft 分布式事务协调器 (MS DTC) 提交或回滚。
分布式绑定会话不由字符串绑定令牌标识；它们由分布式事务标识号标识。 如果绑定会话参与本地事务并使用 SET REMOTE_PROC_TRANSACTIONS ON 在远程服务器上执行 RPC，则 MS DTC 会自动将本地绑定事务提升为分布式绑定事务，并启动 MS DTC 会话。
何时使用绑定会话
在早期版本的 SQL Server 中，绑定会话主要用于开发必须执行 Transact-SQL 语句（代表调用它们的进程）的扩展存储过程。 让调用进程将绑定令牌作为扩展存储过程的一个参数传入，使该过程能够加入调用进程的事务空间，从而将扩展存储过程与调用进程集成。
在数据库引擎中，使用 CLR 编写的存储过程比扩展存储过程更安全、更可伸缩且更稳定。 CLR 存储过程使用 SqlContext 对象加入调用会话的上下文，而不是 sp_bindsession。
绑定会话可用于开发三层应用程序，其中业务逻辑被合并到单独的程序中，这些程序协同处理单个业务事务。 这些程序的编码必须仔细协调它们对数据库的访问。 因为两个会话共享相同的锁，所以两个程序不得尝试同时修改相同的数据。 在任何时间点，只有一个会话可以作为事务的一部分执行工作；不能并行执行。 事务只能在定义明确的让步点之间在会话之间切换，例如当所有 DML 语句都已完成且其结果已被检索时。
编写有效的事务
保持事务尽可能短很重要。 当事务启动时，数据库管理系统 (DBMS) 必须持有许多资源直到事务结束，以保护事务的原子性、一致性、隔离性和持久性 (ACID) 属性。 如果数据被修改，修改的行必须用排他锁保护，以防止任何其他事务读取这些行，并且排他锁必须保持到事务提交或回滚。 根据事务隔离级别设置，SELECT 语句可能获取必须保持到事务提交或回滚的锁。 特别是在有许多用户的系统中，事务必须保持尽可能短，以减少并发连接之间对资源的锁定争用。 长时间运行、低效的事务在用户数量较少时可能不是问题，但在有数千用户的系统中，它们会带来严重问题。 从 SQL Server 2014 (12.x) 开始，数据库引擎支持延迟持久事务。 延迟持久事务可能提高可伸缩性和性能，但不保证持久性。 有关详细信息，请参阅控制事务持续性。
代码指南
以下是编写高效事务的准则：
事务期间不需要用户输入。 在事务开始前从用户获取所有必需的输入。 如果事务期间需要额外的用户输入，回滚当前事务，并在提供用户输入后重新启动事务。 即使用户立即响应，人类的反应时间也比计算机速度慢得多。 事务持有的所有资源都会被持有极长的时间，这有可能导致阻塞问题。 如果用户不响应，事务保持活动状态，锁定关键资源直到他们响应，这可能需要几分钟甚至几小时。
尽可能在浏览数据时不打开事务。 在完成所有初步数据分析之前，不应启动事务。
保持事务尽可能短。 在知道必须进行的修改后，启动事务，执行修改语句，然后立即提交或回滚。 在需要之前不要打开事务。
为减少阻塞，考虑对只读查询使用基于行版本控制的隔离级别。
明智地使用较低的事务隔离级别。 许多应用程序可以编码为使用
READ COMMITTED事务隔离级别。 很少有事务需要SERIALIZABLE事务隔离级别。明智地使用乐观并发选项。 在并发更新的可能性很小的系统中，处理“别人在你读取数据后更改了数据”的偶然错误的开销要比在读取数据时始终锁定行的开销小得多。
在事务中访问尽可能少的数据。 这会减少锁定的行数，从而减少事务之间的争用。
尽可能避免使用
HOLDLOCK等悲观锁定提示。HOLDLOCK或SERIALIZABLE隔离级别等提示可能导致进程即使在共享锁上也等待，并降低并发性。尽可能避免使用隐式事务。 由于其性质，隐式事务可能引入不可预测的行为。 请参阅隐式事务和并发问题。
隐式事务和避免并发及资源问题
为防止并发和资源问题，请仔细管理隐式事务。 使用隐式事务时，COMMIT 或 ROLLBACK 后的下一个 Transact-SQL 语句会自动启动一个新事务。 这可能导致在应用程序浏览数据时，甚至在需要用户输入时打开新事务。 在完成保护数据修改所需的最后一个事务后，关闭隐式事务，直到再次需要事务来保护数据修改。 此过程使数据库引擎在应用程序浏览数据和从用户获取输入时使用自动提交模式。
此外，启用 SNAPSHOT 隔离级别时，尽管新事务不会持有锁，但长时间运行的事务会阻止旧版本从版本存储区中移除。
管理长时间运行的事务
长时间运行的事务是未及时提交或回滚的活动事务。 例如，如果事务的开始和结束由用户控制，长时间运行的事务的典型原因是用户启动事务，然后在事务等待用户响应时离开。
长时间运行的事务可能给数据库造成严重问题，如下所示：
如果服务器实例在活动事务执行了许多未提交的修改后关闭，后续重启的恢复阶段可能比
recovery interval服务器配置选项或ALTER DATABASE ... SET TARGET_RECOVERY_TIME选项指定的时间长得多。 这些选项分别控制主动检查点和间接检查点。 有关检查点类型的详细信息，请参阅数据库检查点 (SQL Server)。更重要的是，尽管等待的事务可能生成很少的日志，但它会无限期地阻止日志截断，导致事务日志增长并可能填满。 如果事务日志填满，数据库无法执行更多写入。 有关详细信息，请参阅 SQL Server 事务日志体系结构和管理指南、解决事务日志已满的问题（SQL Server 错误 9002）和事务日志。
重要说明
在 Azure SQL 数据库中，空闲事务（六小时未写入事务日志的事务）会自动终止以释放资源。
发现长时间运行的事务
若要查看长时间运行的事务，请使用下列方法之一：
sys.dm_tran_database_transactions此动态管理视图返回有关数据库级事务的信息。 对于长时间运行的事务，特别关注的列包括第一个日志记录的时间 (
database_transaction_begin_time)、事务的当前状态 (database_transaction_state) 和事务日志中开始记录的日志序列号 (LSN) (database_transaction_begin_lsn)。有关详细信息，请参阅 sys.dm_tran_database_transactions (Transact-SQL)。
DBCC OPENTRAN此语句可让你识别事务所有者的用户 ID，因此你可以潜在地追踪事务的来源以进行适当的终止（提交或回滚）。 有关详细信息，请参阅 DBCC OPENTRAN (Transact-SQL)。
终止事务
要终止特定会话上的事务，请使用 KILL 语句。 但是，使用此语句时要非常小心，尤其是在运行关键进程时。 有关详细信息，请参阅 KILL (Transact-SQL)。
死锁
死锁是与锁定相关的复杂主题，但与阻止不同。
- 有关死锁（包括监视、诊断和示例）的详细信息，请参阅死锁指南。
- 有关特定于 Azure SQL 数据库的死锁的详细信息，请参阅分析和防止 Azure SQL 数据库中的死锁。