事务锁定和行版本控制指南
适用于：
SQL ServerAzure SQL 数据库Azure SQL 托管实例Azure Synapse AnalyticsAnalytics Platform System (PDW)
在任意数据库中，事务管理不善常常导致用户很多的系统中出现争用和性能问题。 随着访问数据的用户数量的增加，拥有能够高效地使用事务的应用程序也变得更为重要。 本指南说明 SQL Server 数据库引擎使用的锁定和行版本控制机制，以确保每个事务的物理完整性并提供有关应用程序如何高效控制事务的信息。
注意
优化锁定是 2023 年引入的一项数据库引擎功能，可大幅减少锁内存和并发写入时所需的锁数量。 本文已更新，介绍了具有和不具有优化锁定的 SQL Server 数据库引擎。 目前，优化锁定仅在 Azure SQL 数据库中可用。
- 若要了解详细信息，并了解可在何处使用优化锁定，请参阅优化锁定。
- 要确定是否已在数据库上启用优化锁定，请参阅是否已启用优化锁定？
优化锁定已大幅更新本文的某些部分，包括：
事务基本知识
事务是作为单个逻辑工作单元执行的一系列操作。 一个逻辑工作单元必须有四个属性，称为原子性、一致性、隔离性和持久性 (ACID) 属性，只有这样才能成为一个事务。
原子性
事务必须是原子工作单元；对于其数据修改，要么全都执行，要么全都不执行。
一致性
事务在完成时，必须使所有的数据都保持一致状态。 在相关数据库中，所有规则都必须应用于事务的修改，以保持所有数据的完整性。 事务结束时，所有的内部数据结构（如 B 树索引或双向链接列表）都必须是正确的。
注意
SQL Server 文档在提到索引时一般使用 B 树这个术语。 在行存储索引中，SQL Server 实现了 B+ 树。 这不适用于列存储索引或内存中数据存储。 有关详细信息，请参阅 SQL Server 以及 Azure SQL 索引体系结构和设计指南。
隔离
由并发事务所做的修改必须与任何其他并发事务所做的修改隔离。 事务识别数据时数据所处的状态，要么是另一并发事务修改它之前的状态，要么是第二个事务修改它之后的状态，事务不会识别中间状态的数据。 这称为可串行性，因为它能够重新装载起始数据，并且重播一系列事务，以使数据结束时的状态与原始事务执行的状态相同。
持续性
完成完全持久的事务之后，它的影响将永久存在于系统中。 即使系统发生故障，修改也会保留。 SQL Server 2014 (12.x) 及更高版本启用延迟的持久事务。 提交延迟的持久事务后，该事务日志记录将保留在磁盘上。 有关延迟事务持续性的详细信息，请参阅文章控制事务持续性。
SQL 程序员要负责启动和结束事务，同时强制保持数据的逻辑一致性。 程序员必须定义数据修改的顺序，使数据相对于其组织的业务规则保持一致。 程序员将这些修改语句包括到一个事务中，使 SQL Server 数据库引擎能够强制该事务的物理完整性。
企业数据库系统（如 SQL Server 数据库引擎的实例）有责任提供一种机制，保证每个事务的物理完整性。 SQL Server 数据库引擎提供了：
锁定设备，使事务保持隔离。
通过记录设备，保证事务持久性。 对于完全持久的事务，在其提交之前，日志记录将强制写入磁盘。 因此，即使服务器硬件、操作系统或 SQL Server 数据库引擎的实例自身出现故障，该实例也可以在重新启动时使用事务日志，将所有未完成的事务自动地回滚到系统出现故障的点。 提交延迟的持久事务后，该事务日志记录将强制写入磁盘。 如果在日志记录强制写入磁盘前系统出现故障，此类事务可能会丢失。 有关延迟事务持续性的详细信息，请参阅文章控制事务持续性。
事务管理特性，强制保持事务的原子性和一致性。 事务启动之后，就必须成功完成（提交），否则 SQL Server 数据库引擎将撤消该事务启动之后对数据所做的所有修改。 此操作称为回滚事务，因为它将数据恢复到那些更改发生前的状态。
控制事务
应用程序主要通过指定事务启动和结束的时间来控制事务。 可以使用 Transact-SQL 语句或数据库应用程序编程接口 (API) 函数来指定这些时间。 系统还必须能够正确处理那些在事务完成之前便终止事务的错误。 有关详细信息，请参阅事务、在 ODBC 中执行事务以及 SQL Server Native Client 中的事务。
默认情况下，事务按连接级别进行管理。 在一个连接上启动一个事务后，该事务结束之前，在该连接上执行的所有 Transact-SQL 语句都是该事务的一部分。 但是，在多重活动结果集 (MARS) 会话中，Transact-SQL 显式或隐式事务将变成批范围的事务，这种事务按批处理级别进行管理。 当批处理完成时，如果批范围的事务还没有提交或回滚，SQL Server 将自动回滚该事务。 有关详细信息，请参阅使用多重活动结果集 (MARS)。
启动事务
使用 API 函数和 Transact-SQL 语句，可以在 SQL Server 数据库引擎实例中将事务作为显式、自动提交或隐式事务来启动。
显式事务
显式事务是指这样的事务：你在其中通过 API 函数或发出 Transact-SQL BEGIN TRANSACTION、COMMIT TRANSACTION、COMMIT WORK、ROLLBACK TRANSACTION 或 ROLLBACK WORK Transact-SQL 语句明确定义事务的开始和结束。 当事务结束时，连接将返回到启动显式事务前所处的事务模式，或者是隐式模式，或者是自动提交模式。
你可以使用显式事务中除以下语句之外的所有 Transact-SQL 语句：
- CREATE DATABASE
- ALTER DATABASE
- DROP DATABASE
- CREATE FULLTEXT CATALOG
- ALTER FULLTEXT CATALOG
- DROP FULLTEXT CATALOG
- DROP FULLTEXT INDEX
- ALTER FULLTEXT INDEX …
- CREATE FULLTEXT INDEX …
- BACKUP
- RESTORE
- RECONFIGURE
- 全文系统存储过程
sp_dboption用于设置数据库选项，或在显式事务或隐式事务内部修改master数据库的任何系统过程。
注意
UPDATE STATISTICS 可在显式事务内使用。 但是，UPDATE STATISTICS 提交独立于封闭的事务，并且不能回滚。
自动提交事务
自动提交模式是 SQL Server 数据库引擎的默认事务管理模式。 每个 Transact-SQL 语句在完成时，都被提交或回滚。 如果一个语句成功地完成，则提交该语句；如果遇到错误，则回滚该语句。 只要没有显式事务或隐性事务覆盖自动提交模式，与 SQL Server 数据库引擎实例的连接就以此默认模式操作。 自动提交模式也是 ADO、OLE DB、ODBC 和 DB 库的默认模式。
隐式事务
当连接以隐式事务模式进行操作时，SQL Server 数据库引擎实例将在提交或回滚当前事务后自动启动新事务。 无须描述事务的开始，只需提交或回滚每个事务。 隐性事务模式生成连续的事务链。 通过 API 函数或 Transact-SQL SET IMPLICIT_TRANSACTIONS ON 语句，将隐性事务模式设置为打开。 此模式也称为 Autocommit OFF，请参阅 setAutoCommit Method (SQLServerConnection)。
为连接将隐性事务模式设置为打开之后，当 SQL Server 数据库引擎的实例首次执行以下任何语句时，都会自动启动一个事务：
ALTER TABLE
CREATE
DELETE
DROP
FETCH
GRANT
INSERT
OPEN
REVOKE
SELECT
TRUNCATE TABLE
UPDATE
批处理级事务只能应用于多重活动结果集 (MARS)，在 MARS 会话中启动的 Transact-SQL 显式或隐式事务变为批处理级事务。 当批处理完成时没有提交或回滚的批处理级事务自动由 SQL Server 进行回滚。
分布式事务分布式事务跨越两个或多个称为资源管理器的服务器。 称为事务管理器的服务器组件必须在资源管理器之间协调事务管理。 如果分布式事务由 Microsoft 分布式事务处理协调器 (MS DTC) 之类的事务管理器或其他支持 Open Group XA 分布式事务处理规范的事务管理器来协调，则在这样的分布式事务中，每个 SQL Server 数据库引擎的实例都可以作为资源管理器来运行。 有关详细信息，请参阅 MS DTC 文档。
跨越两个或多个数据库的 SQL Server 数据库引擎的单个实例中的事务实际上是分布式事务。 该实例对分布式事务进行内部管理；对于用户而言，其操作就像本地事务一样。
对于应用程序而言，管理分布式事务很像管理本地事务。 当事务结束时，应用程序会请求提交或回滚事务。 不同的是，分布式提交必须由事务管理器管理，以尽量避免出现因网络故障而导致事务由某些资源管理器成功提交，但由另一些资源管理器回滚的情况。 通过分两个阶段（准备阶段和提交阶段）管理提交进程可避免这种情况，这称为两阶段提交 (2PC)。
准备阶段当事务管理器收到提交请求时，它会向该事务涉及的所有资源管理器发送准备命令。 然后，每个资源管理器将尽力使该事务持久，并且所有保存该事务日志映像的缓冲区将被刷新到磁盘中。 当每个资源管理器完成准备阶段时，它会向事务管理器返回准备成功或准备失败的消息。 SQL Server 2014 (12.x) 引入了延迟事务持续性。 在提交延迟的持久事务后，该事务的日志图像将刷入磁盘。 有关延迟事务持续性的详细信息，请参阅文章控制事务持续性。
提交阶段如果事务管理器从所有资源管理器收到准备成功的消息，它将向每个资源管理器发送一个提交命令。 然后，资源管理器就可以完成提交。 如果所有资源管理器都报告提交成功，那么事务管理器就会向应用程序发送一个成功通知。 如果任一资源管理器报告准备失败，那么事务管理器将向每个资源管理器发送一个回滚命令，并向应用程序表明提交失败。
SQL Server 数据库引擎应用程序可以通过 Transact-SQL 或数据库 API 来管理分布式事务。 有关详细信息，请参阅 BEGIN DISTRIBUTED TRANSACTION (Transact-SQL)。
结束事务
你可以使用 COMMIT 或 ROLLBACK 语句，或者通过相应 API 函数来结束事务。
提交如果事务成功，则提交。 COMMIT 语句保证事务的所有修改在数据库中都永久有效。 COMMIT 语句还释放事务使用的资源（例如，锁）。
回滚如果事务中出现错误，或用户决定取消事务，则回滚该事务。 ROLLBACK 语句通过将数据返回到它在事务开始时所处的状态，来取消事务中的所有修改。 ROLLBACK 还释放事务占用的资源。
注意
在为支持多个活动的结果集 (MARS) 而建立的连接中，只要还有待执行的请求，就无法提交通过 API 函数启动的显式事务。 如果在未完成的操作还在运行时尝试提交此类事务，将导致出现错误。
事务处理过程中的错误
如果某个错误使事务无法成功完成，SQL Server 会自动回滚该事务，并释放该事务占用的所有资源。 如果客户端与 SQL Server 数据库引擎实例的网络连接中断了，则当网络向实例通知该中断后，该连接的任何未完成事务均会被回滚。 如果客户端应用程序失败或客户端计算机崩溃或重新启动，也会中断连接，而且当网络向 SQL Server 数据库引擎的实例通知该中断后，该实例会回滚所有未完成的连接。 如果客户端从该应用程序注销，任何未完成的事务均会被回滚。
如果批处理中出现运行时语句错误（如违反约束），则 SQL Server 数据库引擎中的默认行为是只回滚产生该错误的语句。 可以使用 SET XACT_ABORT 语句更改此行为。 在执行 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 Server 数据库引擎的实例回滚了整个批处理而不是仅仅一个 SQL 语句。 当遇到的错误是编译错误而非运行时错误时，会发生这种情况。 编译错误会阻止 SQL Server 数据库引擎生成执行计划，这样批处理中的任何语句都不会执行。 尽管看起来好像是回滚了产生错误的语句之前的所有语句，但该错误阻止了批处理中的所有语句的执行。 在下面的示例中，由于发生编译错误，第三个批处理中的 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
SQL Server 数据库引擎使用延迟的名称解析，直到执行时才解析对象名称。 在下面的示例中，执行并提交了前两个 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
锁定和行版本控制基本知识
当多个用户同时访问数据时，SQL Server 数据库引擎使用以下机制确保事务的完整性和保持数据库的一致性：
锁定
每个事务对所依赖的资源（如行、页或表）请求不同类型的锁。 锁可以阻止其他事务以某种可能会导致事务请求锁出错的方式修改资源。 当事务不再依赖锁定的资源时，它将释放锁。
行版本控制
当启用基于行版本控制的隔离级别时，SQL Server 数据库引擎将维护修改的每一行的版本。 应用程序可以指定事务使用行版本查看事务或查询开始时存在的数据，而不是使用锁保护所有读取。 通过使用行版本控制，读取操作阻止其他事务的可能性将大大降低。
锁定和行版本控制可以防止用户读取未提交的数据，还可以防止多个用户尝试同时更改同一数据。 如果不进行锁定或行版本控制，对数据执行的查询可能会返回数据库中尚未提交的数据，从而产生意外的结果。
应用程序可以选择事务隔离级别，为事务定义保护级别，以防被其他事务所修改。 可以为各个 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级别运行的事务不会发出共享锁来防止其他事务修改当前事务读取的数据。 在 READ COMMITTED 级别运行的事务会发出共享锁，但是在读取行后会释放行锁或页锁。 无论哪种情况，在你扫描索引时，如果另一个用户在你读取期间更改行的索引键列，则在键更改将行移至你的扫描位置之前的位置时，该行可能会再次出现。 同样，在键更改将行移至你已读取的索引中的某位置时，该行将不会出现。 若要避免此问题，请使用SERIALIZABLE或HOLDLOCK提示或行版本控制。 有关详细信息，请参阅表提示 (Transact-SQL)。缺失非更新目标的一行或多行
使用
READ UNCOMMITTED时，如果使用分配顺序扫描（使用 IAM 页）查询读取行，当其他事务导致页拆分时，可能会缺失行。 当使用已提交的读取时不会发生这种情况，因为在页拆分期间将会保持表锁；当表没有聚集索引时也不会发生这种情况，因为更新不会导致页拆分。
并发类型
当许多人试图同时修改数据库中的数据时，必须实现一个控制系统，使一个人所做的修改不会对他人所做的修改产生负面影响。 这称为并发控制。
并发控制理论根据建立并发控制的方法而分为两类：
悲观并发控制
一个锁定系统，可以阻止用户以影响其他用户的方式修改数据。 如果用户执行的操作导致应用了某个锁，只有这个锁的所有者释放该锁，其他用户才能执行与该锁冲突的操作。 这种方法之所以称为悲观并发控制，是因为它主要用于数据争用激烈的环境中，以及发生并发冲突时用锁保护数据的成本低于回滚事务的成本的环境中。
乐观并发控制
在乐观并发控制中，用户读取数据时不锁定数据。 当一个用户更新数据时，系统将进行检查，查看该用户读取数据后其他用户是否又更改了该数据。 如果其他用户更新了数据，将产生一个错误。 一般情况下，收到错误信息的用户将回滚事务并重新开始。 这种方法之所以称为乐观并发控制，是由于它主要在以下环境中使用：数据争用不大且偶尔回滚事务的成本低于读取数据时锁定数据的成本。
SQL Server 支持一定范围的并发控制。 用户通过为游标上的连接或并发选项选择事务隔离级别来指定并发控制的类型。 这些特性可以使用 Transact-SQL 语句或通过数据库应用程序编程接口（API，如 ADO、ADO.NET、OLE DB 和 ODBC）的属性和特性来定义。
SQL Server 数据库引擎中的隔离级别
事务指定一个隔离级别，该隔离级别定义一个事务必须与由其他事务进行的资源或数据更改相隔离的程度。 隔离级别从允许的并发副作用（例如，脏读或虚拟读取）的角度进行描述。
事务隔离级别控制：
- 读取数据时是否获取锁以及所请求的锁类型。
- 占用读取锁的时间。
- 引用其他事务修改的行的读取操作是否：
- 在该行上的排他锁被释放之前阻塞其他事务。
- 检索在启动语句或事务时存在的行的已提交版本。
- 读取未提交的数据修改。
重要
选择事务隔离级别不影响为保护数据修改而获取的锁。 事务总是在其修改的任何数据上获取排他锁并在事务完成之前持有该锁，不管为该事务设置了什么样的隔离级别。 对于读取操作，事务隔离级别主要定义保护级别，以防受到其他事务所做更改的影响。
较低的隔离级别可以增强许多用户同时访问数据的能力，但也增加了用户可能遇到的并发副作用（例如脏读或丢失更新）的数量。 相反，较高的隔离级别减少了用户可能遇到的并发副作用的类型，但需要更多的系统资源，并增加了一个事务阻塞其他事务的可能性。 应平衡应用程序的数据完整性要求与每个隔离级别的开销，在此基础上选择相应的隔离级别。 最高隔离级别（可序列化）保证事务在每次重复读取操作时都能准确检索到相同的数据，但需要通过执行某种级别的锁定来完成此操作，而锁定可能会影响多用户系统中的其他用户。 最低隔离级别（未提交读）可以检索其他事务已经修改、但未提交的数据。 在未提交读中，所有并发副作用都可能发生，但因为没有读取锁定或版本控制，所以开销最少。
数据库引擎隔离级别
ISO 标准定义了以下隔离级别，SQL Server 数据库引擎支持所有这些隔离级别：
| 隔离级别 | 定义 |
|---|---|
| 未提交的读取 | 隔离事务的最低隔离级别，只能保证不读取物理上损坏的数据。 在此级别上，允许脏读，因此一个事务可能看见其他事务所做的尚未提交的更改。 |
| 已提交的读取 | 允许事务读取另一个事务以前读取（未修改）的数据，而不必等待第一个事务完成。 SQL Server 数据库引擎保留写入锁（在所选数据上获取）直到事务结束，但是一执行 SELECT 操作就释放读取锁。 这是 SQL Server 数据库引擎默认级别。 |
| 可重复的读取 | SQL Server 数据库引擎保留在所选数据上获取的读取锁和写入锁，直到事务结束。 但是，因为不管理范围锁，可能发生虚拟读取。 |
| 可序列化 | 隔离事务的最高级别，事务之间完全隔离。 SQL Server 数据库引擎保留在所选数据上获取的读取锁和写入锁，在事务结束时释放它们。 SELECT 操作使用分范围的 WHERE 子句时获取范围锁，主要为了避免虚拟读取。 注意：请求可序列化隔离级别时，复制的表上的 DDL 操作和事务可能失败。 这是因为复制查询使用的提示可能与可序列化隔离级别不兼容。 |
SQL Server 还支持使用行版本控制的其他两个事务隔离级别。 一个是 READ COMMITTED 隔离的实现，另一个是事务隔离级别（快照）。
| 行版本控制隔离级别 | 定义 |
|---|---|
| 读取已提交的快照 (RCSI) | 当 READ_COMMITTED_SNAPSHOT 数据库选项设置为 ON 时，READ COMMITTED 隔离使用行版本控制提供语句级读取一致性。 读取操作只需要 SCH-S 表级别的锁，不需要页锁或行锁。 即，SQL Server 数据库引擎使用行版本控制为每个语句提供一个在事务上一致的数据快照，因为该数据在语句开始时就存在。 不使用锁来防止其他事务更新数据。 用户定义的函数可以返回在包含 UDF 的语句开始后提交的数据。 如果 READ_COMMITTED_SNAPSHOT 数据库选项设置为 OFF（这是默认设置），当前事务运行读取操作时，READ COMMITTED 隔离使用共享锁来防止其他事务修改行。 共享锁还会阻止语句在其他事务完成之前读取由这些事务修改的行。 两个实现都满足 READ COMMITTED 隔离的 ISO 定义。 |
| 快照 | 快照隔离级别使用行版本控制来提供事务级别的读取一致性。 读取操作不获取页锁或行锁，只获取 SCH-S 表锁。 读取其他事务修改的行时，读取操作将检索启动事务时存在的行的版本。 当 ALLOW_SNAPSHOT_ISOLATION 数据库选项设置为 ON 时，只能对数据库使用快照隔离。 默认情况下，用户数据库的此选项设置为 OFF。注意：SQL Server 不支持对元数据进行版本控制。 因此，对于在快照隔离下运行的显式事务中可以执行的 DDL 操作存在限制。 在快照隔离下，BEGIN TRANSACTION 语句之后不允许使用任何公共语言运行时 (CLR) DDL 语句或下列 DDL 语句：ALTER TABLE、CREATE INDEX、CREATE XML INDEX、ALTER INDEX、DROP INDEX、DBCC REINDEX、ALTER PARTITION FUNCTION、ALTER PARTITION SCHEME。 在隐式事务中使用快照隔离时，允许使用这些语句。 根据定义，隐式事务为单个语句，这使得它可以强制应用快照隔离的语义，即便使用 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. |
下表显示了不同隔离级别导致的并发副作用。
| 隔离级别 | 脏读 | 不可重复读 | 虚拟读取 |
|---|---|---|---|
| 未提交的读取 | 是 | 是 | 是 |
| 已提交的读取 | 否 | 是 | 是 |
| 可重复的读取 | 否 | No | 是 |
| 快照 | 否 | No | 否 |
| 可序列化 | 否 | No | 否 |
有关每个事务隔离级别控制的特定类型的锁定或行版本控制的详细信息，请参阅 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 检索快照事务。
数据库引擎中的锁定
锁定是 SQL Server 数据库引擎用来同步多个用户同时对同一个数据块的访问的一种机制。
在事务获取数据块当前状态的依赖关系（比如通过读取或修改数据）之前，它必须保护自己不受其他事务对同一数据进行修改的影响。 事务通过请求锁定数据块来达到此目的。 锁有多种模式，如共享或排他。 锁模式定义了事务对数据所拥有的依赖关系级别。 如果某个事务已获得特定数据的锁，则其他事务不能获得会与该锁模式发生冲突的锁。 如果事务请求的锁模式与已授予同一数据的锁发生冲突，则 SQL Server 数据库引擎的实例将暂停事务请求直到第一个锁释放。
当事务修改某个数据块时，它将持有保护所做修改的特定锁直到事务结束。 事务持有（所获取的用来保护读取操作的）锁的时间长度，取决于事务隔离级别设置以及是否已启用优化锁定。
在未启用优化锁定时，写入所需的行锁和页锁将一直持有到事务结束。
启用优化锁定时，仅在事务持续时间内持有事务 ID (TID) 锁。 在默认隔离级别下，事务在事务结束之前不会持有写入所需的行锁和页锁。 这减少了所需的锁内存并减少了对锁升级的需求。 此外，在启用优化锁定时，限定后锁 (LAQ) 优化会在不获取锁定的情况下，对行的最新提交版本上的查询谓词进行评估，从而提高并发性。
一个事务持有的所有锁都在事务完成（无论是提交还是回滚）时释放。
应用程序一般不直接请求锁。 锁由的一个 SQL Server 数据库引擎部件（称为“锁管理器”）在内部管理。 当 SQL Server 数据库引擎的实例处理 Transact-SQL 语句时，SQL Server 数据库引擎查询处理器会确定要访问的资源。 查询处理器根据访问类型和事务隔离级别设置来确定保护每一资源所需的锁的类型。 然后，查询处理器将向锁管理器请求适当的锁。 如果与其他事务所持有的锁不会发生冲突，锁管理器将授予该锁。
锁粒度和层次结构
SQL Server 数据库引擎具有多粒度锁定，允许一个事务锁定不同类型的资源。 为了尽量减少锁定的开销，SQL Server 数据库引擎会自动将资源锁定在适合任务的级别。 锁定在较小的粒度（例如行）可以提高并发度，但开销较高，因为如果锁定了许多行，则需要持有更多的锁。 锁定在较大的粒度（例如表）会降低了并发度，因为锁定整个表限制了其他事务对表中任意部分的访问。 但其开销较低，因为需要维护的锁较少。
SQL Server 数据库引擎通常必须获取多粒度级别上的锁才能完整地保护资源。 这组多粒度级别上的锁称为锁层次结构。 例如，为了完整地保护对索引的读取，SQL Server 数据库引擎的实例可能必须获取行上的共享锁以及页和表上的意向共享锁。
下表显示了 SQL Server 数据库引擎可以锁定的资源。
| 资源 | 说明 |
|---|---|
| RID | 用于锁定堆中的单个行的行标识符。 |
| 键 | 索引中用于保护可序列化事务中的键范围的行锁。 |
| PAGE | 数据库中的 8 KB 页，例如数据页或索引页。 |
| EXTENT | 一组连续的八页，例如数据页或索引页。 |
| HoBT 1 | 堆或 B 树。 用于保护没有聚集索引的表中的 B 树（索引）或堆数据页的锁。 |
| TABLE 1 | 包括所有数据和索引的整个表。 |
| 文件 | 数据库文件。 |
| APPLICATION | 应用程序专用的资源。 |
| METADATA | 元数据锁。 |
| ALLOCATION_UNIT | 分配单元。 |
| DATABASE | 整个数据库。 |
| XACT 2 | 优化锁定中使用的事务 ID (TID) 锁定。 请参阅事务 ID (TID) 锁定。 |
1 使用 ALTER TABLE 的 LOCK_ESCALATION 选项会对 HoBT 和 TABLE 锁带来影响。
2 其他锁定资源可用于 XACT 锁定资源，请参阅优化锁定的诊断附加功能。
锁模式
SQL Server 数据库引擎使用不同的锁模式锁定资源，这些锁模式确定了并发事务访问资源的方式。
下表显示了 SQL Server 数据库引擎使用的资源锁模式。
| 锁模式 | 说明 |
|---|---|
| 共享 (S) | 用于不更改或不更新数据的读取操作，如 SELECT 语句。 |
| 更新 (U) | 用于可更新的资源中。 防止当多个会话在读取、锁定以及随后可能进行的资源更新时发生常见形式的死锁。 |
| 排他 (X) | 用于数据修改操作，例如 INSERT、UPDATE 或 DELETE。 确保不会同时对同一资源进行多重更新。 |
| 意向 | 用于建立锁的层次结构。 意向锁包含三种类型：意向共享 (IS)、意向排他 (IX) 和意向排他共享 (SIX)。 |
| 架构 | 在执行依赖于表架构的操作时使用。 架构锁包含两种类型：架构修改 (Sch-M) 和架构稳定性 (Sch-S)。 |
| 大容量更新 (BU) | 在将数据大容量复制到表中且指定了 TABLOCK 提示时使用。 |
| 键范围 | 当使用可序列化事务隔离级别时保护查询读取的行的范围。 确保再次运行查询时其他事务无法插入符合可序列化事务的查询的行。 |
共享锁
共享锁（S 锁）允许并发事务在封闭式并发控制下读取 (SELECT) 资源。 资源上存在共享锁（S 锁）时，任何其他事务都不能修改数据。 读取操作一完成，就立即释放资源上的共享锁（S 锁），除非将事务隔离级别设置为可重复读或更高级别，或者在事务持续时间内用锁定提示保留共享锁（S 锁）。
更新锁
数据库引擎在准备执行更新时放置更新 (U) 锁。 U 锁与 S 锁兼容，但一次只有一个事务可以在给定资源上持有 U 锁。 这是关键 - 许多并发事务可以持有 S 锁，但只有一个事务可以持有资源上的 U 锁。 更新 (U) 锁最终升级到独占锁（X）以更新行。
当在查询中指定 UPDLOCK 表提示时，不执行 UPDATE 的查询也可以获取更新 (U) 锁。 应用程序通常使用“选择行，然后更新行”模式，其中读取和写入在事务内显式分隔。 在这种情况下，如果隔离级别可重复读取或可序列化，并发更新可能会死锁。 相反，应用程序可遵循“选择带有 UPDLOCK 提示的行，然后更新行”模式。
在可重复读或可序列化事务中，此事务读取数据（获取资源的共享 (S) 锁），然后修改数据（此操作要求锁转换为排他(X) 锁）。 如果两个事务获得了资源上的共享 (S) 锁，然后试图同时更新数据，则一个事务会尝试将锁转换为排他 (X) 锁。 共享到排他锁的转换必须等待一段时间，因为一个事务的排他锁与其他事务的共享 (S) 锁不兼容；发生锁等待。 第二个事务试图获取排他锁（X 锁）以进行更新。 由于两个事务都要转换为排他 (X) 锁，并且每个事务都等待另一个事务释放共享 (S) 锁，因此发生死锁。
在默认已提交读隔离级别下，S 锁持续时间较短，使用后立即释放。 短持续时间锁不太可能导致死锁。
如果在写入中使用 UPDLOCK 提示，则事务必须有权访问该行的最新版本。 如果最新版本不再可见，则预计能够在使用 SNAPSHOT 隔离时接收
Msg 3960, Level 16, State 2 Snapshot isolation transaction aborted due to update conflict。 有关示例，请参阅使用快照隔离。
排他锁
排他锁（X 锁）可以防止并发事务对资源进行访问。 使用排他锁（X 锁）时，任何其他事务都无法修改数据；仅在使用 NOLOCK 提示或未提交读隔离级别时才会进行读取操作。
数据修改语句（如 INSERT、UPDATE 和 DELETE）合并了修改和读取操作。 语句在执行所需的修改操作之前首先执行读取操作以获取数据。 因此，数据修改语句通常请求共享锁和排他锁。 例如，UPDATE 语句可能根据与一个表的联接修改另一个表中的行。 在此情况下，除了请求更新行上的排他锁之外，UPDATE 语句还将请求在联接表中读取的行上的共享锁。
意向锁
SQL Server 数据库引擎使用意向锁来确保共享 (S) 锁或排他 (X) 锁放置在锁层次结构的底层资源上。 意向锁之所以命名为“意向锁”，是因为在较低级别锁前可获取它们，因此会通知意向将锁放置在较低级别上。
意向锁有两种用途：
- 防止其他事务以会使较低级别的锁无效的方式修改较高级别资源。
- 提高 SQL Server 数据库引擎在较高的粒度级别检测锁冲突的效率。
例如，在该表的页或行上请求共享锁（S 锁）之前，在表级请求共享意向锁。 在表级设置意向锁可防止另一个事务随后在包含那一页的表上获取排他锁（X 锁）。 意向锁可以提高性能，因为 SQL Server 数据库引擎仅在表级检查意向锁来确定事务是否可以安全地获取该表上的锁。 而不需要检查表中的每行或每页上的锁以确定事务是否可以锁定整个表。
意向锁包括意向共享 (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 锁的组合，作为分别获取这些锁并且同时持有两种锁的结果。 |
架构锁
SQL Server 数据库引擎在表数据定义语言 (DDL) 操作（例如添加列或删除表）的过程中使用架构修改 (Sch-M) 锁。 保持该锁期间，Sch-M 锁将阻止对表进行并发访问。 这意味着 Sch-M 锁在释放前将阻止所有外围操作。
某些数据操作语言 (DML) 操作（例如表截断）使用 Sch-M 锁阻止并发操作访问受影响的表。
SQL Server 数据库引擎在编译和执行查询时使用架构稳定性 (Sch-S) 锁。 Sch-S 锁不会阻止某些事务锁，其中包括排他 (X) 锁。 因此，在编译查询的过程中，其他事务（包括那些针对表使用 X 锁的事务）将继续运行。 但是，无法针对表执行获取 Sch-M 锁的并发 DDL 操作和并发 DML 操作。
批量更新锁
大容量更新锁（BU 锁）允许多个线程将数据并发地大容量加载到同一表，同时防止其他不进行大容量加载数据的进程访问该表。 SQL Server 数据库引擎在满足以下两个条件时，使用批量更新 (BU) 锁。
- 你使用 Transact-SQL BULK INSERT 语句或 OPENROWSET(BULK) 函数，或者你使用某个大容量插入 API 命令（如 .NET SqlBulkCopy）、OLEDB 快速加载 API 或 ODBC 大容量复制 API 来将数据大容量复制到表。
- 指定了 TABLOCK 提示或使用 sp_tableoption 设置 table lock on bulk load 表选项。
提示
与持有较少限制性大容量更新 (BU) 锁的 BULK INSERT 语句不同，具有 TABLOCK 提示的 INSERT INTO...SELECT 语句持有一个针对表的意向排他 (X) 锁。 也就是说你不能使用并行插入操作插入行。
键范围锁
在使用可序列化事务隔离级别时，对于 Transact-SQL 语句读取的记录集，键范围锁可以隐式保护该记录集中包含的行范围。 键范围锁可防止虚拟读取。 通过保护行之间键的范围，它还防止对事务访问的记录集进行虚拟插入或删除。
锁兼容性
锁兼容性控制多个事务能否同时获取同一资源上的锁。 如果资源已被另一事务锁定，则仅当请求锁的模式与现有锁的模式相兼容时，才会授予新的锁请求。 如果请求锁的模式与现有锁的模式不兼容，则请求新锁的事务将等待释放现有锁或等待锁超时间隔过期。 例如，没有与排他锁兼容的锁模式。 如果具有排他锁（X 锁），则在释放排他锁（X 锁）之前，其他事务均无法获取该资源的任何类型（共享、更新或排他）的锁。 另一种情况是，如果共享锁（S 锁）已应用到资源，则即使第一个事务尚未完成，其他事务也可以获取该项的共享锁或更新锁（U 锁）。 但是，在释放共享锁之前，其他事务无法获取排他锁。
| 现有的授予模式 | IS | S | U | IX | SIX | X |
|---|---|---|---|---|---|---|
| 请求的模式 | ||||||
| 意向共享 (IS) | 是 | 是 | 是 | 是 | 是 | 否 |
| 共享 (S) | 是 | 是 | 是 | 否 | No | 否 |
| 更新 (U) | 是 | 是 | 否 | No | No | 否 |
| 意向排他 (IX) | 是 | 否 | No | 是 | 否 | 否 |
| 意向排他共享 (SIX) | 是 | 否 | No | No | No | 否 |
| 排他 (X) | 否 | No | No | No | No | 否 |
注意
意向排他锁（IX 锁）与 IX 锁模式兼容，因为 IX 表示打算只更新部分行而不是所有行。 还允许其他事务尝试读取或更新部分行，只要这些行不是其他事务当前更新的行即可。 此外，如果两个事务尝试更新同一行，将在表级和页级上授予这两个事务 IX 锁。 但是，将在行级授予一个事务 X 锁。 另一个事务必须在该行级锁被删除前等待。
使用下表可以确定 SQL Server 中所有可用的锁模式的兼容性。
键范围锁
在使用可序列化事务隔离级别时，对于 Transact-SQL 语句读取的记录集，键范围锁可以隐式保护该记录集中包含的行范围。 可序列化隔离级别要求每当在事务期间执行任一查询时，该查询都必须获取相同的行集。 键范围锁可防止其他事务插入其键值位于可序列化事务读取的键值范围内的新行，从而确保满足此要求。
键范围锁可防止虚拟读取。 通过保护行之间的键范围，它还可以防止对事务访问的记录集进行虚拟插入。
键范围锁放置在索引上，指定开始键值和结束键值。 此锁将阻止任何要插入、更新或删除任何带有该范围内的键值的行的尝试，因为这些操作会首先获取索引上的锁。 例如，可序列化事务可以发出 SELECT 语句，该语句读取键值与条件 BETWEEN 'AAA' AND 'CZZ' 匹配的所有行。 从“AAA”到“CZZ”范围内的键值上的键范围锁可阻止其他事务插入带有该范围内的键值（例如“ADG”、“BBD”或“CAL”）的行。
键范围锁模式
键范围锁包括按范围-行格式指定的范围组件和行组件：
- 范围表示保护两个连续索引项之间的范围的锁模式。
- 行表示保护索引项的锁模式。
- 模式表示使用的组合锁模式。 键范围锁模式由两部分组成。 第一部分表示用于锁定索引范围 (RangeT) 的锁类型，第二部分表示用于锁定特定键 (K) 的锁类型。 这两部分用连字符 (-) 连接，例如 RangeT-K。
| 范围 | 行 | “模式” | 说明 |
|---|---|---|---|
| RangeS | S | RangeS-S | 共享范围，共享资源锁；可序列化范围扫描。 |
| RangeS | U | RangeS-U | 共享范围，更新资源锁；可串行更新扫描。 |
| RangeI | Null | RangeI-N | 插入范围，空资源锁；用于在索引中插入新键之前测试范围。 |
| RangeX | X | RangeX-X | 排他范围，排他资源锁；用于更新范围中的键。 |
注意
内部空锁模式与所有其他锁模式兼容。
键范围锁模式有一个兼容性矩阵，表示哪些锁与在重叠键和范围上获取的其他锁兼容。
| 现有的授予模式 | S | U | X | RangeS-S | RangeS-U | RangeI-N | RangeX-X |
|---|---|---|---|---|---|---|---|
| 请求的模式 | |||||||
| 共享 (S) | 是 | 是 | No | 是 | 是 | 是 | 否 |
| 更新 (U) | 是 | 否 | No | 是 | No | 是 | 否 |
| 排他 (X) | 否 | No | No | No | No | 是 | 否 |
| RangeS-S | 是 | 是 | No | 是 | 是 | 否 | 否 |
| RangeS-U | 是 | 否 | No | 是 | 否 | No | 否 |
| RangeI-N | 是 | 是 | 是 | 否 | No | 是 | 否 |
| RangeX-X | 否 | No | No | No | No | No | 否 |
转换锁
当键范围锁与其他锁重叠时，将创建转换锁。
| 锁定 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。
- 查询处理器必须使用索引来实现范围筛选谓词。 例如，SELECT 语句中的 WHERE 子句可以用以下谓词建立范围条件：ColumnX BETWEEN N**'AAA'** AND N**'CZZ'**。 仅当 ColumnX 被索引键覆盖时，才能获取键范围锁。
示例
以下表和索引用作随后的键范围锁定示例的基础。
范围扫描查询
为了确保范围扫描查询是可序列化的，每次在同一事务中执行的相同查询应返回同样的结果。 其他事务不能在范围扫描查询中插入新行；否则这些插入将成为虚拟插入。 例如，以下查询将使用上图中的表和索引：
SELECT name
FROM mytable
WHERE name BETWEEN 'A' AND 'C';
键范围锁放置在与数据行范围（名称在值 Adam 与 Dale 之间的行）对应的索引项上，以防止添加或删除满足上述查询条件的新行。 尽管此范围中的第一个名称是 Adam，但是此索引项的 RangeS-S 模式键范围锁确保了以字母 A 开头的新名称（例如 Abigail）不能添加在 Adam 之前。 同样，Dale 索引项的 RangeS-S 键范围锁确保了以字母 C 开头的新名称（例如 Clive）不能添加在 Carlos 之后。
注意
包含的 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 数据库选项和快照隔离级别还允许从之前提交状态的行版本中读取。）
可以使用三个基本锁模式执行范围删除：行锁、页锁或表锁。 行、页或表锁定策略由查询优化器确定，也可以由用户通过查询优化器提示（例如 ROWLOCK、PAGLOCK 或 TABLOCK）来指定。 当使用 PAGLOCK 或 TABLOCK 时，如果从某个索引页中删除所有行，SQL Server 数据库引擎将立即释放该索引页。 相反，当使用 ROWLOCK 时，所有删除的行只是标记为已删除；以后通过后台任务从索引页中删除它们。
具有优化锁定的删除操作
在删除事务内的值时，会增量获取和释放行锁和页锁，且在事务持续时间内不会持有行锁和页锁。 例如，对于下面的 DELETE 语句：
DELETE mytable
WHERE name = 'Bob';
在事务持续时间内，所有修改的行上都放置 TID 锁。 在与名称 Bob 对应的索引项的 TID 上获取锁。 使用优化锁定，更新时将继续获取页锁和行锁，但每行更新后，每个页锁和行锁都会释放。 TID 锁可保护行在事务完成之前不被更新。 任何试图读取、插入或删除值 Bob 的事务都将被阻止，直到删除的事务提交或回滚为止。 （READ_COMMITTED_SNAPSHOT 数据库选项和快照隔离级别还允许从之前提交状态的行版本中读取。）
否则，删除操作的锁定机制与未优化锁定相同。
没有优化锁定的插入操作
在事务中插入值时，在事务执行插入操作期间不必锁定该值所属的范围。 锁定插入的键值直至事务结束足以维护可序列化性。 例如，对于下面的 INSERT 语句：
INSERT mytable VALUES ('Dan');
RangeI-N 模式键范围锁放置在与名称 David 对应的索引项上，以测试范围。 如果已授权锁，则插入 Dan，并且排他锁（X 锁）将放置在值 Dan 上。 RangeI-N 模式键范围锁仅对测试范围是必需的，而不在执行插入操作的事务期间保留。 其他事务可以在插入的值 Dan 的前后插入或删除值。 但是，任何试图读取、插入或删除值 Dan 的事务都将被阻塞，直到插入的事务提交或回滚为止。
具有优化锁定的插入操作
在事务中插入值时，在事务执行插入操作期间不必锁定该值所属的范围。 很少获取行锁和页锁，仅当正在进行在线索引重新生成时，或者实例中存在可序列化事务时。 如果获取行锁和页锁，则会在事务持续时间内快速释放这些锁，并且不会持有。 将排他 TID 锁放在插入的键值上，直至事务结束足以维护可序列化。 例如，对于下面的 INSERT 语句：
INSERT mytable VALUES ('Dan');
通过优化锁定，仅当实例中至少有一个使用 SERIALIZABLE 隔离级别的事务时，才会获取 RangeI-N 锁。 RangeI-N 模式键范围锁放置在与名称 David 对应的索引项上，以测试范围。 如果已授权锁，则插入 Dan，并且排他锁（X 锁）将放置在值 Dan 上。 RangeI-N 模式键范围锁仅对测试范围是必需的，而不在执行插入操作的事务期间保留。 其他事务可以在插入的值 Dan 的前后插入或删除值。 但是，任何试图读取、插入或删除值 Dan 的事务都将被阻塞，直到插入的事务提交或回滚为止。
锁升级
锁升级是将许多细粒度锁转换为较少粗粒度锁的过程，从而减少系统开销，同时增加并发争用的可能性。
锁升级的行为有所不同，具体取决于是否启用优化锁定。
无优化锁定的锁升级
当 SQL Server 数据库引擎获取低级别的锁时，它还将在包含更低级别对象的对象上放置意向锁：
- 当锁定行或索引键范围时，数据库引擎将在包含这些行或键的页上放置意向锁。
- 当锁定页时，数据库引擎将在包含这些页的更高级别对象上放置意向锁。 除了对象上的意向锁外，还会对以下对象请求意向页锁：
- 非聚集索引的叶级别页
- 聚集索引的数据页
- 堆数据页
数据库引擎可以为同一语句执行行锁定和页锁定，以最大程度地减少锁的数量，并降低需要进行锁升级的可能性。 例如，数据库引擎可以在非聚集索引上放置页锁（如果在索引节点中选择了足够的连续键来满足查询），而在数据上放置行锁。
升级锁时，数据库引擎会尝试将表的意向锁改为对应的全锁，例如，将意向排他 (IX) 锁改为排他 (X) 锁，或将意向共享 (IS) 锁改为共享 (S) 锁。 如果锁升级尝试成功且获取了全表锁，则堆或索引上事务持有的所有堆或 B 树、页 (PAGE) 或行级别 (RID) 锁都会释放。 如果无法获取全锁，当时不会发生锁升级，而数据库引擎将继续获取行、键或页锁。
数据库引擎不会将行锁或键范围锁升级到页锁，而是将它们直接升级到表锁。 同样，页锁始终升级为表锁。 对于关联的分区，已分区表的锁定可以升级到 HoBT 级别，而不是表锁。 HoBT 级别锁不一定锁定分区的对齐 HoBT。
注意
HoBT 级别锁通常会增加并发性，但当锁定不同分区的事务都将排他锁扩展到其他分区时，可能会造成死锁。 在极少数情况下，表锁粒度的性能可能会更好。
如果由于并发事务所持有的锁冲突而导致锁升级尝试失败，则 数据库引擎将对事务获取的其他 1,250 个锁重试锁升级。
每个升级事件主要在单个 Transact-SQL 语句级别操作。 当事件启动时，只要活动语句满足升级阈值的要求，Transact-SQL 就会尝试升级当前事务在活动语句所引用的任何表中持有的所有锁。 如果在语句访问表之前启动升级事件，则不会尝试升级该表上的锁。 如果锁升级成功，且当前语句引用了该表并且该表包含在升级事件中，则该事务在上一个语句中获取并在事件开始时仍持有的任何锁都会升级。
例如，假设会话执行以下操作：
- 开始一个事务。
- 更新
TableA。 这会在 TableA 中生成排他行锁，这些锁将一直持有到事务完成为止。 - 更新
TableB。 这会在 TableB 中生成排他行锁，这些锁将一直持有到事务完成为止。 - 执行联接
TableA与TableC的 SELECT。 查询执行计划要求先从TableA中检索行，然后才从TableC中检索行。 - SELECT 语句在从
TableA中检索行时（此时还没有访问TableC）触发锁升级。
如果锁升级成功，只有会话在 TableA 中持有的锁才会升级。 这包括 SELECT 语句中的共享锁和上一个 UPDATE 语句中的排他锁。 由于决定是否应进行锁升级时只考虑会话的 SELECT 语句在 TableA 中获取的锁，所以一旦升级成功，会话在 TableA 中持有的所有锁都将被升级到该表的排他锁，而 TableA 的所有其他较低粒度的锁（包括意向锁）都将被释放。
不会尝试升级 TableB 的锁，因为 SELECT 语句中没有 TableB 的活动引用。 同样，也不会尝试升级 TableC 上尚未升级的锁，因为发生升级时尚未访问过该表。
具有优化锁的锁升级
优化锁定有助于减少锁内存，因为在事务持续时间内持有的锁很少。 当 SQL Server 数据库引擎获取行锁和页锁时，锁升级也会发生类似的情况，但频率要低得多。 优化锁定通常可以成功避免锁升级、减少所需的锁数量和锁内存量。
启用优化锁定，并在默认 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 中进行锁升级，因为发生升级时尚未访问该表。
数据库引擎实例的升级阈值
每当锁的数量大于锁升级的内存阈值时，数据库引擎都会触发锁升级。 内存阈值取决于锁配置选项的设置：
如果“锁”选项设置为默认值 0，当锁对象使用的内存是数据库引擎所使用内存的 24%（不包括 AWE 内存）时，将达到锁升级阈值。 用于表示锁的数据结构长度大约为 100 个字节。 该阈值是动态的，因为数据库引擎动态地获得和释放内存，以针对变化的工作负载进行调整。
如果“锁”选项设置为非 0 值，则锁升级阈值是“所”选项值的 40%（或者更低，如果存在内存压力）。
数据库引擎可以为升级选择任何会话中的活动语句，而且，只要实例中使用的锁内存保持在阈值之上，每获取 1,250 个新锁，它就会为升级选择语句。
升级混合锁类型
在发生锁升级时，为堆或索引选择的锁足够强大，以满足限制性最严格的较低级别锁的要求。
例如，假设会话：
- 开始一个事务。
- 更新包含聚集索引的表。
- 发出引用同一个表的 SELECT 语句。
UPDATE 语句会获取以下锁：
- 更新的数据行上的排他 (X) 锁。
- 包含这些行的聚集索引页上的意向排他(IX) 锁。
- 聚集索引上的 IX 锁和表上的另一个锁。
SELECT 语句会获取以下锁：
- 所读取的所有数据行上的共享 (S) 锁，除非该行已受 UPDATE 语句中的 X 锁保护。
- 包含这些行的所有聚集索引页上的意向共享锁，除非该页已受 IX 锁保护。
- 聚集索引或表上没有锁，因为它们已受 IX 锁保护。
如果 SELECT 语句获取足够的锁来触发锁升级且升级成功，则表上的 IX 锁将转换为 X 锁，并且所有行锁、页锁和索引锁都将释放。 更新和读取均受表上的 X 锁保护。
减少锁定和升级
在大多数情况下，数据库引擎在使用默认的锁定和锁升级设置进行操作时提供最佳性能。
利用优化锁定。
- 优化锁定提供改进的事务锁定机制，可减少并发事务之间的锁内存使用和阻塞。 启用优化锁定时，发生锁升级的可能性要小得多。
- 避免对优化锁定使用表提示。 表提示可能会降低优化锁定的有效性。
- 为充分利用优化锁定，应在数据库中启用 READ_COMMITTED_SNAPSHOT。 这是 Azure SQL 数据库中的默认隔离级别。
- 优化锁定要求在数据库上启用加速数据库恢复 (ADR)。
如果数据库引擎实例生成大量锁并且频繁进行锁升级，请考虑使用以下策略减少锁定数量：
对于读取操作，使用不会生成共享锁的隔离级别：
READ COMMITTED 隔离级别（在 READ_COMMITTED_SNAPSHOT 数据库选项设置为 ON 时）。
快照隔离级别。
READ UNCOMMITTED 隔离级别。 这只能用于可以进行脏读操作的系统。
注意
更改隔离级别会影响数据库引擎实例上的所有表。
使用 PAGLOCK 或 TABLOCK 表提示，使数据库引擎使用页、堆或索引锁而不是低级别锁。 但是，使用此选项会增加用户阻止其他用户尝试访问相同数据的问题，因此不应在具有多个并发用户的系统中使用此选项。
在未启用优化锁定时，对于已分区表，使用 ALTER TABLE 的 LOCK_ESCALATION 选项将锁升级到 HoBT 级别而不是表级别，或者禁用锁升级。
将大批操作分成多个小批操作。 例如，假设你运行以下查询以从审核表中删除几十万条旧记录，然后发现它导致了会阻止其他用户的锁升级：
DELETE FROM LogMessages WHERE LogDate < '2/1/2002'通过一次删除几百条记录，可以显著减少每个事务累积的锁数量，并防止锁升级。 例如：
SET ROWCOUNT 500 delete_more: DELETE FROM LogMessages WHERE LogDate < '2/1/2002' IF @@ROWCOUNT > 0 GOTO delete_more SET ROWCOUNT 0通过尽量提高查询的效率，减少查询的锁占用时间。 大型扫描或大量的书签查找可能会增加锁升级的几率；此外，它还会增加死锁的可能性，并且通常会对并发和性能产生不利影响。 找到导致锁升级的查询后，寻找机会创建新索引，或向现有索引添加列以删除索引或表扫描，并最大程度地提高索引查找的效率。 请考虑使用数据库引擎优化顾问对查询执行自动索引分析。 有关详细信息，请参阅教程：数据库引擎优化顾问。 此优化的一个目标是使索引查找尽可能少地返回行，以最大程度地降低书签查找的成本（最大程度地提高特定查询的索引选择性）。 如果数据库引擎估计书签查找逻辑运算符可能返回多个行，则它可能使用 PREFETCH 来执行书签查找。 如果数据库引擎使用 PREFETCH 进行书签查找，则它必须将部分查询的事务隔离级别提升至可重复读取部分查询。 这意味着，在已提交读隔离级别看起来类似于 SELECT 语句的内容可能会获得数千个键锁（在两个聚集索引和一个非聚集索引上），这会导致此类查询超出锁升级阈值。 如果你发现已升级的锁是共享表锁（但该锁在默认的已提交读取隔离级别并不常见），则这一点特别重要。
如果书签查找 WITH PREFETCH 子句导致升级，请考虑向索引查找中出现的非聚集索引，或查询计划中书签查找逻辑运算符下方的索引扫描逻辑运算符添加其他列。 可以创建覆盖索引（一种索引，可包含查询所使用表中的全部列），也可以至少创建一个包含联接条件或 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 提示强制执行表锁，或者管理员已使用
sp_indexoption存储过程禁用了页锁或行锁）。你还可以使用跟踪标志 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）只会更改初始锁计划。 锁提示不会阻止锁升级。
监视锁升级
使用 lock_escalation 扩展事件 (xEvent) 来监视锁升级，如以下示例中所示：
-- 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
重要
应使用 lock_escalation 扩展事件 (xEvent)，而不是 SQL 跟踪或 SQL Profiler 中的 Lock:Escalation 事件类。
动态锁定
使用低级锁（如行锁）可以降低两个事务同时在相同数据块上请求锁的可能性，从而提高并发性。 使用低级锁还会增加锁的数量以及管理锁所需的资源。 使用高级表锁或页锁可以减少开销，但代价是降低了并发性。
SQL Server 数据库引擎使用动态锁定策略确定最经济的锁。 执行查询时，SQL Server 数据库引擎会根据架构和查询的特点自动决定最合适的锁。 例如，为了缩减锁定的开销，优化器可能在执行索引扫描时在索引中选择页级锁。
动态锁定具有下列优点：
- 简化数据库管理。 数据库管理员不必调整锁升级阈值。
- 提高了性能。 SQL Server 数据库引擎通过使用适合任务的锁使系统开销减至最小。
- 应用程序开发人员可以集中精力进行开发。 SQL Server 数据库引擎会自动调整锁定。
自 SQL Server 2008 (10.0.x) 起，锁升级的行为已更改，其中引入了 LOCK_ESCALATION 选项。 有关详细信息，请参阅 ALTER TABLE 的 LOCK_ESCALATION 选项。
锁分区
对于大型计算机系统，在经常被引用的对象上放置的锁可能会变成性能瓶颈，因为获取和释放锁对内部锁资源造成了争用。 锁分区通过将单个锁资源拆分为多个锁资源而提高了锁性能。 此功能只适用于拥有 16 个或更多 CPU 的系统，它是自动启用的，而且无法禁用。 只有对象锁可以分区。 具有子类型的对象锁不会被分区。 有关详细信息，请参阅 sys.dm_tran_locks (Transact-SQL)。
了解锁分区
锁任务访问几个共享资源，其中两个通过锁分区进行优化：
调节锁。 它控制对锁资源（例如行或表）的访问。
不进行锁分区，一个调节锁就得管理单个锁资源的所有锁请求。 在具有大量活动的系统上，在锁请求等待释放调节锁时会出现资源争用的现象。 在这种情况下，获取锁可能变成了一个瓶颈，并且可能会对性能造成负面影响。
为了减少对单个锁资源的争用，锁分区将单个锁资源拆分成多个锁资源，以便将负荷分布到多个调节锁上。
内存。 它用于存储锁资源结构。
获取调节锁后，锁结构将存储在内存中，然后即可对其进行访问和可能的修改。 将锁访问分布到多个资源中有助于消除在 CPU 之间传输内存块的需要，这有助于提高性能。
实现和监视锁分区
默认情况下，对于具有 16 个或更多 CPU 的系统，锁分区是打开的。 启用锁分区后，将在 SQL Server 错误日志中记录一条信息性消息。
获取已分区资源的锁时：
只能获取单个分区的 NL、SCH-S、IS、IU 和 IX 锁模式。
对于以分区 ID 0 开始并且按照分区 ID 顺序排列的所有分区，必须获取非 NL、SCH-S、IS、IU 和 IX 模式的共享锁 (S)、排他锁 (X) 和其他锁。 已分区资源的这些锁将比相同模式中未分区资源的锁占用更多的内存，因为每个分区都是一个有效的单独锁。 内存的增加由分区数决定。 Windows 性能监视器中 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 锁与分区 ID 7 上由会话 1 中的事务持有的 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 锁。 将获取所有分区 ID 0-5 的 X 锁，但它会被为分区 ID 6 获取的 IS 锁阻塞。
对于尚未获取 X 锁的分区 ID 7-15，其他事务可以继续获取锁。
BEGIN TRANSACTION
SELECT col1
FROM TestTable
WITH (TABLOCKX, HOLDLOCK);
SQL Server 数据库引擎中基于行版本控制的隔离级别
从 SQL Server 2005 (9.x) 开始，SQL Server 数据库引擎提供现有事务隔离级别（已提交读）的实现，该实现使用行版本控制提供语句级快照。 SQL Server 数据库引擎还提供一个事务隔离级别（快照），该级别也使用行版本控制提供事务级快照。
行版本控制是 SQL Server 中的一般框架，它在修改或删除行时调用写入时复制机制。 这要求在运行事务时，行的旧版本必须可供需要早先事务一致状态的事务使用。 行版本控制可用于执行以下操作：
- 在触发器中生成插入的和删除的表。 对任何由触发器修改的行都将生成副本。 这包括由启动触发器的语句修改的行，以及由触发器进行的任何数据修改。
- 支持多个活动的结果集 (MARS)。 如果 MARS 会话在存在活动结果集的情况下发出一条数据修改语句（例如
INSERT、UPDATE或DELETE），受修改语句影响的行将进行版本控制。 - 支持指定 ONLINE 选项的索引操作。
- 支持基于行版本控制的事务隔离级别：
- 新实现的 READ COMMITTED 隔离级别，使用行版本控制提供语句级的读取一致性。
- 新快照隔离级别，提供事务级的读取一致性。
tempdb 数据库必须具有足够的空间用于版本存储区。 在 tempdb 已满的情况下，更新操作将停止生成版本，并继续执行，但是因为所需的特定行版本不再存在，读取操作可能会失败。 这将影响诸如触发器、MARS 和联机索引的操作。
已提交读和快照事务的行版本控制的使用过程分为两个步骤：
将
READ_COMMITTED_SNAPSHOT和ALLOW_SNAPSHOT_ISOLATION数据库选项之一或两者设置为 ON。在应用程序中设置相应的事务隔离级别：
- 当
READ_COMMITTED_SNAPSHOT数据库选项设置为 ON 时，设置 READ COMMITTED 隔离级别的事务使用行版本控制。 - 当
ALLOW_SNAPSHOT_ISOLATION数据库选项设置为 ON 时，事务可以设置快照隔离级别。
- 当
当 READ_COMMITTED_SNAPSHOT 或 ALLOW_SNAPSHOT_ISOLATION 数据库选项设置为 ON 时，SQL Server 数据库引擎向使用行版本控制操作数据的每个事务分配一个事务序列号 (XSN)。 事务在执行 BEGIN TRANSACTION 语句时启动。 但是，事务序列号在执行 BEGIN TRANSACTION 语句后的第一次读/写操作时开始增加。 事务序列号在每次分配时都增加 1。
当 READ_COMMITTED_SNAPSHOT 或 ALLOW_SNAPSHOT_ISOLATION 数据库选项设置为 ON 时，将维护数据库中执行的所有数据修改的逻辑副本（版本）。 特定的事务每次修改行时，SQL Server 数据库引擎 实例都存储以前提交的 tempdb 中行的图像版本。 每个版本都标记有进行此更改的事务的事务序列号。 已修改行的版本使用链接列表链接在一起。 最新的行值始终存储在当前数据库中，并与 tempdb 中存储的版本控制行链接在一起。
注意
修改大型对象 (LOB) 时，只有已更改的片段才会复制到 tempdb 中的版本存储区。
行版本将保持足够长的时间，以满足在基于行版本控制的隔离级别下运行的事务的要求。 SQL Server 数据库引擎跟踪最早的可用事务序列号，并定期删除带有比最早使用的可用序列号更低的事务序列号的所有行版本。
两个数据库选项都设置为 OFF 时，只对由触发器或 MARS 会话修改的行或由联机索引操作读取的行生成副本。 这些行版本将在不再需要时被释放。 后台线程会定期执行来删除陈旧的行版本。
注意
对于短期运行的事务，已修改行的版本将可能缓存在缓冲池中，而不会写入 tempdb 数据库的磁盘文件中。 如果只是临时需要副本行，它将只是简单地从缓冲池中删除而不会引发 I/O 开销。
读取数据时的行为
当在基于行版本控制的隔离下运行的事务读取数据时，读取操作不会获取正被读取的数据上的共享锁（S 锁），因此不会阻塞正在修改数据的事务。 同时，由于减少了所获取的锁的数量，因此最大程度地降低了锁定资源的开销。 使用行版本控制的已提交读隔离和快照隔离旨在提供副本数据的语句级或事务级读取一致性。
所有查询，包括在基于行版本控制的隔离级别下运行的事务，都在编译和执行期间获取 Sch-S（架构稳定性）锁。 因此，当并发事务持有表的 Sch-M（架构修改）锁时，将阻塞查询。 例如，数据定义语言 (DDL) 操作在修改表的架构信息之前获取 Sch-M 锁。 查询事务，包括在基于行版本控制的隔离级别下运行的事务，都会在尝试获取 Sch-S 锁时被阻塞。 相反，持有 Sch-S 锁的查询将阻塞尝试获取 Sch-M 锁的并发事务。
当使用快照隔离级别的事务启动时，SQL Server 数据库引擎实例将记录所有当前活动的事务。 当快照事务读取具有版本链的行时，SQL Server 数据库引擎按照该链检索行，其事务序列号为：
最接近但低于读取行的快照事务序列号。
不在快照事务启动时活动的事务列表中。
由快照事务执行的读取操作将检索在快照事务启动时已提交的每行的最新版本。 这提供了在事务启动时存在的数据的事务一致快照。
使用行版本控制的已提交读事务以大致相同的方式运行。 不同之处在于选择行版本时，已提交读取事务不使用其自身的事务序列号。 每次启动语句时，已提交读事务将读取为该 SQL Server 数据库引擎实例发出的最新事务序列号。 这是用于为该语句选择正确的行版本的事务序列号。 这使已提交读事务可以查看每个语句启动时存在的数据的快照。
注意
即使使用行版本控制的已提交读事务提供了在语句级别上事务一致的数据视图，但此类事务生成或访问的行版本还将保留，直到事务完成时为止。
修改数据时的行为
无论是否存在优化锁定，数据写入的行为都有明显不同。
在没有优化锁定的情况下修改数据
在使用行版本控制的已提交读事务中，使用阻塞性扫描（其中读取数据值时将在数据行上获取更新 (U) 锁完成选择要更新的行。 这与不使用行版本控制的已提交读取事务相同。 如果数据行不符合更新标准，将释放更新锁并且将锁定下一行并对其进行扫描。
在快照隔离下运行的事务对数据修改采用乐观方法：获取数据上的锁后，才执行修改以强制应用约束。 否则，直到数据修改时才获取数据上的锁。 当数据行符合更新标准时，快照事务将验证未被并发事务（在快照事务开始后提交）修改的数据行。 如果数据行已在快照事务以外修改，则将出现更新冲突，同时快照事务也将终止。 更新冲突由 SQL Server 数据库引擎处理，无法禁用更新冲突检测。
注意
当快照事务访问以下任意项目时，在快照隔离下运行的更新操作将在 READ COMMITTED 隔离下内部执行：
具有 FOREIGN KEY 约束的表。
在其他表的 FOREIGN KEY 约束中引用的表。
引用多个表的索引视图。
但是，即使是在这些条件下，更新操作仍将继续验证数据是否未经其他事务修改。 如果数据已被其他事务修改，则快照事务将遭遇更新冲突并终止。 更新冲突必须由应用程序手动处理和重试。
在优化锁定的情况下修改数据
启用优化锁定及启用 READ_COMMITTED_SNAPSHOT (RCSI) 数据库选项，并使用默认 READ COMMITTED 隔离级别，读取器不会获取任何锁，而编写器获取短持续时间的低级别锁，而不是在事务结束时过期的锁。
建议启用 RCSI，以通过优化锁定获取最高效率。 如果使用更严格的隔离级别（如可重复读取或可序列化），数据库引擎将强制为读取器和编写器持有行锁和页锁，直到事务结束，从而导致阻塞和锁内存增加。
启用 RCSI 并使用默认 READ COMMITTED 隔离级别时，编写器根据行的最新提交版本按谓词限定行，而无需获取 U 锁。 只有当行限定并且该行或页上存在活动的写入事务时，查询才会等待。 根据最新提交的版本进行限定，仅锁定限定行可减少阻塞并提高并发性。
如果使用 RCSI 和默认 READ COMMITTED 隔离级别检测到更新冲突，则会自动处理并重试这些冲突，而不会对客户工作负载产生任何影响。
启用优化锁定并使用快照隔离级别后，更新冲突的行为是相同的。 更新冲突必须由应用程序手动处理和重试。
注意
有关优化锁定的限定后锁定 (LAQ) 功能的行为更改的详细信息，请参阅使用优化锁定和 RCSI 更改查询行为。
行为摘要
下表概括了使用行版本控制的快照隔离与 READ COMMITTED 隔离之间的差异。
| properties | 使用行版本控制的已提交读隔离级别 | 快照隔离级别 |
|---|---|---|
| 必须设置为 ON 以便启用所需支持的数据库选项。 | READ_COMMITTED_SNAPSHOT | ALLOW_SNAPSHOT_ISOLATION |
| 会话如何请求特定类型的行版本控制。 | 使用默认的已提交读隔离级别，或运行 SET TRANSACTION ISOLATION LEVEL 语句来指定 READ COMMITTED 隔离级别。 这可以在事务启动后完成。 | 需要执行 SET TRANSACTION ISOLATION LEVEL 来在事务启动前指定 SNAPSHOT 隔离级别。 |
| 由语句读取的数据的版本。 | 在每条语句启动前提交的所有数据。 | 在每个事务启动前提交的所有数据。 |
| 如何处理更新。 | 没有优化锁定：从行版本恢复到实际的数据，以选择要更新的行并使用选择的数据行上的更新锁。 获取要修改的实际数据行上的排他锁。 没有更新冲突检测。 具有优化锁定：根据上次提交的版本选择行，无需获取任何锁。 如果行符合更新条件，则获取排他行锁或页锁。 如果检测到更新冲突，则自动处理和重试更新冲突。 |
使用行版本选择要更新的行。 尝试获取要修改的实际数据行上的排他锁，如果数据已被其他事务修改，则出现更新冲突，同时快照事务也将终止。 |
| 更新冲突检测 | 没有优化锁定：无。 具有优化锁定：如果检测到更新冲突，则自动处理和重试更新冲突。 |
集成支持。 无法禁用。 |
行版本控制资源的使用情况
行版本控制框架支持 SQL Server 中提供的以下功能：
- 触发器
- 多个活动的结果集 (MARS)
- 联机索引
另外，行版本控制框架还支持下列基于行版本控制的事务隔离级别（默认情况下禁用）：
READ_COMMITTED_SNAPSHOT数据库选项为 ON 时，READ_COMMITTED事务使用行版本控制提供语句级读取一致性。ALLOW_SNAPSHOT_ISOLATION数据库选项为 ON 时，SNAPSHOT事务使用行版本控制提供事务级读取一致性。
基于行版本控制的隔离级别通过消除对读取操作使用共享锁来减少事务获取的锁数目。 这样就减少了管理锁所用资源，从而提高了系统性能。 另外还减少了其他事务获取的锁阻塞事务的次数，也就提高了性能。
基于行版本控制的隔离级别增加了数据修改所需的资源。 启用这些选项会导致要复制数据库中要修改的所有数据。 即使没有使用基于行版本控制隔离的活动事务，也将修改前的数据副本存储在 tempdb 中。 修改后的数据包括一个指向存储在 tempdb 中的经过版本控制数据的指针。 对于大型对象，只将对象中更改过的部分复制到 tempdb 中。
tempdb 中使用的空间
对于每个 SQL Server 数据库引擎实例，tempdb 都必须具有足够的空间以容纳在该实例中为每个数据库生成的行版本。 数据库管理员必须确保 tempdb 具有足够的空间来支持版本存储区。 tempdb 中有以下两种版本存储区：
- 联机索引生成版本存储区，用于所有数据库中的联机索引生成操作。
- 公共版本存储区，用于所有数据库中的所有其他数据修改操作。
只要活动事务需要访问行版本，就必须存储行版本。 后台线程每隔一分钟删除一次不再需要的行版本，从而释放 tempdb 中的版本空间。 如果长时间运行的事务符合下列任何一个条件，则会阻止释放版本存储区中的空间：
- 使用基于行版本控制的隔离。
- 使用触发器、MARS 或联机索引生成操作。
- 生成行版本。
注意
在事务内部调用了触发器后，即使触发器完成后不再需要行版本，由触发器创建的行版本将仍然受到维护直到事务结束。 这也同样适用于使用行版本控制的已提交读事务。 对于这种事务类型，只有事务中的每条语句需要数据库的事务一致视图。 这表示语句完成后将不再需要在事务中为它创建的行版本。 但是，由事务中的每条语句创建的行版本将受到维护，直到事务完成。
当 tempdb 运行空间不足时，SQL Server 数据库引擎强制收缩版本存储区。 在执行收缩进程的过程中，尚未生成行版本且运行时间最长的事务被标记为牺牲品。 在错误日志中为每个作为牺牲品的事务生成消息 3967。 如果某个事务被标记为牺牲品，则该事务不能再读取版本存储区中的行版本。 当其尝试读取行版本时，会生成消息 3966 且该事务会被回滚。 如果收缩进程成功，则 tempdb 中就有可用空间。 否则，tempdb 运行空间不足，并出现下列情况：
写操作继续执行但不生成版本。 错误日志中会生成一条信息消息 (3959)，但写数据的事务不受影响。
尝试访问由于
tempdb完全回滚而未生成的行版本的事务终止，并生成错误消息 3958。
数据行中使用的空间
每个数据库行的结尾处最多可以使用 14 个字节记录行版本控制信息。 行版本控制信息包含提交版本的事务的事务序列号和指向版本行的指针。 如果符合下列任何一种条件，则第一次修改行时或插入新行时添加这 14 个字节：
READ_COMMITTED_SNAPSHOT或ALLOW_SNAPSHOT_ISOLATION选项为 ON。- 表有触发器。
- 正在使用多个活动的结果集 (MARS)。
- 当前正在对表执行联机索引生成操作。
如果符合下列所有条件，则第一次修改数据库行时，将从行中删除这 14 个字节：
READ_COMMITTED_SNAPSHOT和ALLOW_SNAPSHOT_ISOLATION选项为 OFF。- 表不再有触发器。
- 当前没有使用 MARS。
- 当前没有执行联机索引生成操作。
如果使用了行版本控制功能，则可能需要为数据库分配额外的磁盘空间，才能使每个数据库行可多使用 14 个字节。 如果当前页上没有足够的可用空间，则添加行版本控制信息会导致拆分索引页或分配新的数据页。 例如，如果平均行长度为 100 个字节，则额外的 14 个字节会导致现有表增大 14%。
减少填充因子可能有助于避免或减少索引页碎片。 若要查看表或视图的数据和索引的碎片信息，可以使用 sys.dm_db_index_physical_stats。
大型对象中使用的空间
SQL Server 数据库引擎支持以下六种数据类型（最多可以容纳大小为 2 GB 的大型字符串）：nvarchar(max)、varchar(max)、varbinary(max)、ntext、text、 和 image。 使用这些数据类型的大型字符串存储在一系列与数据行链接的数据片段中。 行版本控制信息存储在用于存储这些大型字符串的每个片段中。 数据片段是表中专用于大型对象的页集合。
新的大型值添加到数据库中时，系统会为它们分配数据片段，每个片段最多可以存储 8040 个字节的数据。 早期版本的 SQL Server 数据库引擎中，每个片段最多可以存储 8080 个字节的 ntext、text 或 image 数据。
数据库从早期版本的 SQL Server 升级到 SQL Server 时，现有的 ntext、text 和 image 大型对象 (LOB) 数据并未更新来为行版本控制信息释放一些空间。 但第一次修改 LOB 数据时，该数据会动态升级以实现版本控制信息的存储。 即使未生成行版本也是如此。 LOB 数据升级后，每个片段最多可以存储的字节数从 8080 个减少到 8040 个。 升级过程相当于先删除 LOB 值再重新插入相同值。 即使只修改 1 个字节也会升级 LOB 数据。 对于每个 ntext、text 或 image 列，这是一次性操作，但每个操作可能生成大量页分配和 I/O 活动，具体情况取决于 LOB 数据的大小。 如果完整记录修改，还会生成大量日志记录活动。 如果数据库恢复模式未设置为 FULL，则按最小方式记录 WRITETEXT 操作和 UPDATETEXT 操作。
在早期版本的 SQL Server 中不使用 nvarchar(max)、varchar(max) 和 varbinary(max) 数据类型。 因此，这些数据类型不存在升级问题。
应该分配足够的磁盘空间来满足此要求。
监视行版本控制和版本存储区
为了监视行版本控制、版本存储区和快照隔离进程以了解性能和问题，SQL Server 提供了一些工具，包括动态管理视图 (DMV) 和 Windows 系统监视器中的性能计数器。
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 位的聚合记录长度进行分组。 可以使用此函数来查找版本存储区的最大使用者。 有关详细信息，请参阅 sys.dm_tran_top_version_generators (Transact-SQL)。sys.dm_tran_version_store。 返回一个虚拟表，其中显示有公共版本存储区中的所有版本记录。 有关详细信息，请参阅 sys.dm_tran_version_store (Transact-SQL)。sys.dm_tran_version_store_space_usage。 返回一个虚拟表，该表显示每个数据库的版本存储记录使用的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)。
性能计数器
SQL Server 性能计数器提供有关受 SQL Server 进程影响的系统性能的信息。 以下性能计数器监视 tempdb、版本存储区以及使用行版本控制的事务。 这些性能计数器包含在 SQLServer:Transactions 性能对象中。
Free Space in tempdb (KB)。 监视
tempdb数据库中的可用空间 (KB)。tempdb中必须有足够的可用空间来容纳支持快照隔离的版本存储区。下列公式可以用来粗略估计版本存储区的大小。 对于长时间运行的事务，监视生成速率和清除速率对于估计版本存储区的最大大小会非常有用。
[公共版本存储区的大小] = 2 * [每分钟生成的版本存储区数据] * [事务的最长运行时间（分钟）]
事务的最长运行时间不应该包括联机索引生成时间。 对于超大型表，由于这些操作可能要花很长的时间，因此联机索引生成使用单独的版本存储区。 当联机索引生成处于活动状态时，联机索引生成版本存储区的近似大小等于表（包括所有索引）中修改的数据量。
Version Store Size (KB)。 监视所有版本存储区的大小 (KB)。 此信息有助于确定版本存储区在
tempdb数据库中所需的空间大小。 监视计数器一段时间，可以获得有用的信息来估计在tempdb数据库中所需的额外空间。Version Generation rate (KB/s)。 监视所有版本存储区中的版本生成速率（KB/秒）。
Version Cleanup rate (KB/s)。 监视所有版本存储区中的版本清除速率（KB/秒）。
注意
版本生成速率（KB/秒）和版本清理速率（KB/秒）的信息可以用于预测
tempdb空间要求。Version Store unit count。 监视版本存储区单元的计数。
Version Store unit creation。 监视自启动实例后创建用于存储行版本的版本存储区单元总数。
Version Store unit truncation。 监视自启动实例后被截断的版本存储区单元总数。 当 SQL Server 确定不需要任何存储在版本存储区单元中的版本行来运行活动事务时，版本存储区单元即被截断。
Update conflict ratio。 监视存在更新冲突的更新快照事务与更新快照事务总数的比值。
Longest Transaction Running Time。 监视使用行版本控制的事务的最长运行时间（秒）。 这可用于确定是否存在事务的运行时间不合适的情况。
Transactions。 监视活动事务的总数， 不包括系统事务。
Snapshot Transactions。 监视活动快照事务的总数。
Update Snapshot Transactions。 监视执行更新操作的活动快照事务的总数。
NonSnapshot Version Transactions。 监视生成版本记录的活动非快照事务的总数。
注意
Update Snapshot Transactions 与 NonSnapshot Version Transactions 之和表示参与版本生成的事务的总数。 Snapshot Transactions 与 Update Snapshot Transactions 之差表示只读快照事务数。
基于行版本控制的隔离级别示例
下列示例说明使用行版本控制的快照隔离事务与已提交读事务的行为差异。
A. 使用快照隔离
在此示例中，在快照隔离下运行的事务将读取数据，然后由另一事务修改此数据。 快照事务不阻塞由其他事务执行的更新操作，它忽略数据的修改继续从版本化的行读取数据。 但是，当快照事务尝试修改已由其他事务修改的数据时，快照事务将生成错误并终止。
在会话 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 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. 使用通过行版本控制的已提交读
在此示例中，使用行版本控制的已提交读事务与其他事务并发运行。 已提交读事务的行为与快照事务的行为有所不同。 与快照事务相同的是，即使其他事务修改了数据，已提交读事务也将读取版本化的行。 然而，与快照事务不同的是，已提交读将执行下列操作：
- 在其他事务提交数据更改后，读取修改的数据。
- 能够更新由其他事务修改的数据，而快照事务不能。
在会话 1 上：
USE AdventureWorks2022; -- Or any earlier version of the AdventureWorks database.
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
启用基于行版本控制的隔离级别
数据库管理员可以通过在 ALTER DATABASE 语句中使用 READ_COMMITTED_SNAPSHOT 和 ALLOW_SNAPSHOT_ISOLATION 数据库选项来控制行版本控制的数据库级别设置。
当 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 时，数据库中数据已修改的所有活动事务完成之前，SQL Server 数据库引擎实例不会为已修改的数据生成行版本。 如果存在活动的修改事务，SQL Server 将该选项的状态设置为 PENDING_ON。 所有修改事务完成后，该选项的状态更改为 ON。 在该选项完全处于 ON 状态之前，用户无法在数据库中启动快照事务。 数据库管理员将 ALLOW_SNAPSHOT_ISOLATION 选项设置为 OFF 时，数据库将跳过 PENDING_OFF 状态。
以下 Transact-SQL 语句将启用 ALLOW_SNAPSHOT_ISOLATION：
ALTER DATABASE AdventureWorks2022
SET ALLOW_SNAPSHOT_ISOLATION ON;
下表列出并说明了 ALLOW_SNAPSHOT_ISOLATION 选项的各个状态。 同时使用 ALTER DATABASE 和 ALLOW_SNAPSHOT_ISOLATION 选项不会妨碍当前正在访问数据库数据的用户。
| 当前数据库的快照隔离框架状态 | 说明 |
|---|---|
| OFF | 未启用对快照隔离事务的支持。 不允许执行快照隔离事务。 |
| PENDING_ON | 对快照隔离事务的支持处于转换状态（从 OFF 到 ON）。 打开的事务必须完成。 不允许执行快照隔离事务。 |
| ON | 已启用对快照隔离事务的支持。 允许执行快照事务。 |
| PENDING_OFF | 对快照隔离事务的支持处于转换状态（从 ON 到 OFF）。 此后启动的快照事务无法访问此数据库。 更新事务仍会导致此数据库中出现版本控制开销。 现有快照事务仍可以访问此数据库，不会遇到任何问题。 直到数据库快照隔离状态为 ON 时处于活动状态的所有快照事务完成后，状态 PENDING_OFF 才变为 OFF。 |
使用 sys.databases 目录视图可以确定两个行版本控制数据库选项的状态。
对用户表和存储在 master 和 msdb 中的某些系统表的任何更新都会生成行版本。
在 ALLOW_SNAPSHOT_ISOLATION 和 master 数据库中，msdb 选项自动设置为 ON，并且不能禁用。
在 READ_COMMITTED_SNAPSHOT、master 或 tempdb 中，用户不能将 msdb 选项设置为 ON。
使用基于行版本控制的隔离级别
行版本控制框架在 SQL Server 中始终处于启用状态，并被多个功能使用。 它除了提供基于行版本控制的隔离级别之外，还用于支持对触发器和多个活动结果集 (MARS) 会话的修改，以及 ONLINE 索引操作的数据读取。
基于行版本控制的隔离级别是在数据库级别上启用的。 访问已启用数据库的对象的任何应用程序可以使用以下隔离级别运行查询：
已提交读隔离级别，通过将
READ_COMMITTED_SNAPSHOT数据库选项设置为ON来使用行版本控制，如下面的代码示例所示：ALTER DATABASE AdventureWorks2022 SET READ_COMMITTED_SNAPSHOT ON;为
READ_COMMITTED_SNAPSHOT启用数据库后，在 READ COMMITTED 隔离级别下运行的所有查询将使用行版本控制，这意味着读取操作不会阻止更新操作。快照隔离，通过将
ALLOW_SNAPSHOT_ISOLATION数据库选项设置为ON实现，如下面的代码示例所示：ALTER DATABASE AdventureWorks2022 SET ALLOW_SNAPSHOT_ISOLATION ON;在快照隔离下运行的事务可以访问数据库中为快照启用的表。 若要访问没有为快照启用的表，则必须更改隔离级别。 例如，下面的代码示例显示了在快照事务下运行时联接两个表的
SELECT语句。 一个表属于未启用快照隔离的数据库。 当SELECT语句在快照隔离下运行时，该语句无法成功执行。SET TRANSACTION ISOLATION LEVEL SNAPSHOT; BEGIN TRAN SELECT t1.col5, t2.col5 FROM Table1 as t1 INNER JOIN SecondDB.dbo.Table2 as t2 ON t1.col1 = t2.col2;下面的代码示例显示了已修改为从事务隔离级别更改为已提交读隔离级别的相同
SELECT语句。 由于此更改，SELECT语句将成功执行。SET TRANSACTION ISOLATION LEVEL SNAPSHOT; BEGIN TRAN 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中。 访问快照事务中的全局临时表时，必须执行下列操作之一：- 在
ALLOW_SNAPSHOT_ISOLATION中将tempdb数据库选项设置为 ON。 - 使用隔离提示更改语句的隔离级别。
- 在
如果出现以下情况，快照事务将失败：
- 从快照事务启动之后到访问数据库前的期间内，数据库设置为只读。
- 如果访问多个数据库的对象，数据库状态以如下方式更改：从快照事务启动后到访问数据库前的期间内，发生数据库恢复。 例如：将数据库设置为 OFFLINE，然后设置为 ONLINE，数据库将自动关闭和打开，或数据库将分离和附加。
快照隔离不支持分布式事务，包括分布式分区数据库中的查询。
SQL Server 不会保留系统元数据的多个版本。 表中的数据定义语言 (DDL) 语句和其他数据库对象（索引、视图、数据类型、存储过程和公共语言运行时函数）会更改元数据。 如果 DDL 语句修改一个对象，那么在快照隔离下对该对象的任何并发引用都将导致快照事务失败。 READ_COMMITTED_SNAPSHOT 数据库选项为 ON 时，已提交读事务没有此限制。
例如，数据库管理员执行下面的
ALTER INDEX语句。USE AdventureWorks2022; GO ALTER INDEX AK_Employee_LoginID ON HumanResources.Employee REBUILD; GO执行
ALTER INDEX语句后，任何在执行HumanResources.Employee语句时处于活动状态的快照事务，如果试图引用ALTER INDEX表，都将收到错误。 而使用行版本控制的已提交读事务不受影响。注意
BULK INSERT 操作可能会导致对目标表元数据的更改（例如，禁用约束检查时）。 如果出现这种情况，访问大容量插入表的并发快照隔离事务将失败。
自定义锁定和行版本控制
自定义锁超时
当 Microsoft SQL Server 数据库引擎实例由于其他事务已拥有资源的冲突锁而无法将锁授予给某个事务时，将阻止第一个事务，等待现有锁释放。 默认情况下，没有强制的超时期限，并且除了尝试访问数据（有可能被无限期阻塞）外，没有其他方法可以测试某个资源是否在锁定之前已被锁定。
注意
在 SQL Server 中，使用 sys.dm_os_waiting_tasks 动态管理视图确定某个进程是否被阻塞以及被谁阻塞。 在 SQL Server 的早期版本中，使用 sp_who 系统存储过程。 有关详细信息和示例，请参阅了解并解决 SQL Server 阻塞问题。
LOCK_TIMEOUT 设置允许应用程序设置语句等待阻塞资源的最长时间。 如果某个语句等待的时间超过 LOCK_TIMEOUT 的设置时间，则被阻塞的语句自动取消，并会有错误消息 1222 (Lock request time-out period exceeded) 返回给应用程序。 但是，SQL Server 不会回滚或取消任何包含语句的事务。 因此，应用程序必须具有可以捕获错误消息 1222 的错误处理程序。 如果应用程序不能捕获错误，则会在不知道事务中已有个别语句被取消的情况下继续运行，由于事务中后面的语句可能依赖于从未执行过的语句，因此会出现错误。
实现捕获错误消息 1222 的错误处理程序后，应用程序可以处理超时情况，并采取补救措施，例如：自动重新提交被阻塞的语句或回滚整个事务。
若要确定当前的 LOCK_TIMEOUT 设置，请执行 @@LOCK_TIMEOUT 函数：
SELECT @@lock_timeout;
GO
自定义事务隔离级别
Microsoft SQL Server 数据库引擎的默认隔离级别为 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。
指定隔离级别后，SQL Server 会话中的所有查询语句和数据操作语言 (DML) 语句的锁定行为都将在该隔离级别进行操作。 隔离级别将在会话终止或将其设置为其他级别后失效。
下面的示例设置 SERIALIZABLE 隔离级别：
USE AdventureWorks2022;
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
GO
BEGIN TRANSACTION;
SELECT BusinessEntityID
FROM HumanResources.Employee;
GO
必要时，可以通过指定表级提示来替代各个查询语句或 DML 语句的隔离级别。 指定表级提示不会影响会话中的其他语句。 建议仅在确实必要时才使用表级提示更改默认行为。
读取元数据时，甚至当隔离级别设置为在读取数据时不请求共享锁的级别时，SQL Server 数据库引擎也可能需要获取锁。 例如，在未提交读隔离级别下运行的事务在读取数据时将不获取共享锁，但是在读取系统目录视图时可能会请求锁。 这意味着在查询表时如果某个并发事务正在修改该表的元数据，则未提交读事务可能会导致阻塞。
若要确定当前设置的事务隔离级别，请使用 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 语句中为单个表引用指定锁提示。 提示指定 SQL Server 数据库引擎实例用于表数据的锁类型或行版本控制。 当需要对对象所获得锁类型进行更精细控制时，可以使用表级锁提示。 这些锁提示覆盖会话的当前事务隔离级别。
注意
启用优化锁定时，不建议使用锁定提示。 尽管遵循表和查询提示，但它们会减少优化锁定的优势。 有关详细信息，请参阅 避免对优化锁定使用锁定提示。
有关特定锁定提示及其行为的详细信息，请参阅表提示 (Transact-SQL)。
注意
SQL Server 数据库引擎几乎总是会选择正确的锁定级别。 建议只在必要时才使用表级锁提示来更改默认的锁行为。 禁止锁级别反过来会影响并发。
SQL Server 数据库引擎在读取元数据时可能必须获取锁，即使是处理使用了防止在读取数据时请求共享锁的锁提示的选择。 例如，使用 NOLOCK 提示的 SELECT 在读取数据时不获取共享锁，但有时在读取系统目录视图时可能会请求锁。 这意味着可能会阻止使用 NOLOCK 的 SELECT语句。
如以下示例中所示，如果将事务隔离级别设置为 SERIALIZABLE，并且在 NOLOCK 语句中使用表级锁提示 SELECT，则不获取通常用于维护可序列化事务的键范围锁。
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) 锁。 在这种情况下，不再保证可序列化性。
在 SQL Server 中，LOCK_ESCALATION 的 ALTER TABLE 选项可以禁用表锁，并在已分区表上启用 HoBT 锁。 此选项不是一个锁提示，但是可用来减少锁升级。 有关详细信息，请参阅 ALTER TABLE (Transact-SQL)。
自定义索引的锁定
SQL Server 数据库引擎使用动态锁定策略，这种策略能够在大多数情况下自动为查询选择最佳锁定粒度。 建议你不要替代启用页锁定和行锁定的默认锁定级别，除非透彻地了解了表或索引的访问模式且这些访问模式保持一致，并且存在有待解决的资源争用问题。 替代锁定级别可以明显妨碍对表或索引的并发访问。 例如，对用户时常访问的大型表仅指定表级锁可能会造成瓶颈，因为用户必须等待表级锁释放后才能访问该表。
在为数不多的情况下，不允许页锁定或行锁定可能会有好处，但必须透彻地了解访问模式且这些访问模式保持一致。 例如，某个数据库应用程序使用的查找表在批处理进程中每周更新一次。 并发读取器使用共享锁 (S) 访问表，每周批处理更新使用排他锁 (X) 访问表。 关闭表的页锁定和行锁定可以使读取器通过共享表锁对表进行并发访问，从而在整周内降低锁定开销。 在批处理作业运行时，由于它获得了排他表锁，因此可以高效地完成更新。
由于每周批处理更新在运行时将阻止并发读取器访问表，因此关闭页锁定和行锁定可能是可取的，也可能不可取。 如果批处理作业仅更改少数几行或几页，则可以更改锁定级别以允许行级别或页级别的锁定，这将允许其他会话读取表中的数据而不会受到阻止。 如果批处理作业要进行大量更新，则获取表的排他锁可能是确保批处理作业高效完成的最佳途径。
当两个并发操作获得同一个表的行锁然后进行阻止时，偶尔会出现死锁，因为这两个操作都需要锁定该页。 如果不允许使用行锁，则会强行使其中一个操作等待，从而避免死锁。 有关死锁的详细信息，请参阅死锁指南。
使用 CREATE INDEX 和 ALTER INDEX 语句来设置索引使用的锁定粒度。 该锁设置适用于索引页和表页。 此外，CREATE TABLE 和 ALTER TABLE 语句可用于设置 PRIMARY KEY 和 UNIQUE 约束上的锁定粒度。 对于后向兼容，还可以使用 sp_indexoption 系统存储过程设置粒度。 若要显示给定索引的当前锁定选项，请使用 INDEXPROPERTY 函数。 可以禁止将页级别的锁、行级别的锁或二者的组合用于指定的索引。
| 禁止的锁 | 访问索引的锁 |
|---|---|
| 页面级别 | 行级别的锁和表级别的锁 |
| 行级别 | 页级别的锁和表级别的锁 |
| 页级别和行级别 | 表级别的锁 |
高级事务信息
嵌套事务
显式事务可以嵌套。 这主要是为了支持存储过程中的一些事务，这些事务可以从已在事务中的进程调用，也可以从没有活动事务的进程中调用。
下列示例显示了嵌套事务的用途。 TransProc 过程强制执行其事务，而不管执行事务的进程的事务模式。 如果在事务活动时调用 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 rollback. */
SELECT * FROM TestTrans;
GO
SQL Server 数据库引擎将忽略内部事务的提交。 根据最外部事务结束时获取的操作，将提交或者回滚内部事务。 如果提交外部事务，也将提交内部嵌套事务。 如果回滚外部事务，也将回滚所有内部事务，不管是否单独提交过内部事务。
对 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 语句以 1 为增量递增 @@TRANCOUNT。 每个 COMMIT TRANSACTION 或 COMMIT WORK 语句以 1 为增量递增 @@TRANCOUNT。 没有事务名称的 ROLLBACK WORK 或 ROLLBACK TRANSACTION 语句将回滚所有嵌套事务，并将 @@TRANCOUNT 递减到 0。 在一组嵌套事务中，使用最外部事务的事务名称的 ROLLBACK TRANSACTION 将回滚所有嵌套事务，并将 @@TRANCOUNT 减小到 0。 在无法确定是否已经在事务中时，可使用 SELECT @@TRANCOUNT 确定是等于 1 还是大于 1。 如果 @@TRANCOUNT 为 0，表明不在事务中。
使用绑定会话
绑定会话有利于在同一台服务器上的多个会话之间协调操作。 绑定会话允许一个或多个会话共享相同的事务和锁，并可以使用同一数据，而不会有锁冲突。 可以从同一个应用程序内的多个会话中创建绑定会话，也可以从包含不同会话的多个应用程序中创建绑定会话。
要参与绑定会话，会话必须调用 sp_getbindtoken 或 srv_getbindtoken（通过开放式数据服务）来获取绑定令牌。 绑定令牌是一个字符串，它唯一地标识每个绑定事务。 然后，将绑定令牌发送给要与当前会话绑定的其他会话。 其他会话通过调用 sp_bindsession，并使用从第一个会话中接收到的绑定令牌绑定到事务。
注意
会话必须包含活动的用户事务，sp_getbindtoken 或 srv_getbindtoken 才能成功。
必须将绑定令牌从执行第一个会话的应用程序代码传输到随后将其会话绑定到第一个会话的应用程序代码。 没有应用程序可以用来获取由另一个进程启动的事务绑定令牌的 Transact-SQL 语句或 API 函数。 可以用来传输绑定令牌的方法包括：
如果所有会话都是从同一个应用程序进程启动的，绑定令牌就可以存储在共用内存中，也可以作为参数传递到函数中。
如果会话是从不同的应用程序进程启动的，那么可以使用进程间通信 (IPC)（例如，远程过程调用 [RPC] 或动态数据交换 [DDE]）来传输绑定令牌。
可以将绑定令牌存储在 SQL Server 数据库引擎实例中的某个表中，该表可由要绑定到第一个会话的进程读取。
在一组绑定会话中，任何时候只能有一个会话是活动的。 如果有一个会话正在实例上执行一个语句，或包含从实例挂起的结果，则在当前会话完成处理或取消当前语句之前，其他绑定到该会话的会话都不能访问该实例。 如果该实例正在忙于处理来自另一个绑定会话的语句，则将出现错误，指明事务空间正在使用中，会话应稍后重试。
绑定会话后，每个会话仍保留其隔离级别设置。 使用 SET TRANSACTION ISOLATION LEVEL 更改某个会话的隔离级别设置不会影响绑定到该会话的任何其他会话的设置。
绑定会话的类型
有两种类型的绑定会话：本地绑定会话和分布式绑定会话。
本地绑定会话允许绑定会话共享 SQL Server 数据库引擎的单个实例中的单个事务的事务空间。
分布式事务允许在使用 Microsoft 分布式事务处理协调器 (MS DTC) 提交或回滚整个事务之前，绑定会话可以共享跨越两个或多个实例的同一事务。
分布式绑定会话不是用字符串绑定令牌标识，而是用分布式事务标识号标识。 如果本地事务中涉及到绑定会话，且该会话在远程服务器上使用 SET REMOTE_PROC_TRANSACTIONS ON 执行 RPC，MS DTC 将该本地绑定事务自动提升为分布式绑定事务，并且 MS DTC 会话也会启动。
何时使用绑定会话
在早期版本的 SQL Server 中，绑定会话主要用于开发必须执行 Transact-SQL 语句（代表调用它们的进程）的扩展存储过程。 让调用进程在绑定令牌中作为扩展存储过程的一个参数进行传递，可使该过程加入到调用进程的事务空间中，从而将扩展存储过程与该调用进程结合在一起。
在 SQL Server 数据库引擎中，使用 CLR 编写的存储过程比扩展存储过程更安全、具有更高的伸缩性并且更稳定。 CLR 存储过程使用 SqlContext 对象（而非 sp_bindsession）联接调用会话的上下文。
绑定会话可以用来开发三层应用程序，在这些应用程序中，业务逻辑合并到在单个业务事务上协同工作的单独程序中。 必须对这些程序进行编码，以仔细协调它们对数据库的访问。 由于两个会话共享同一个锁，因此两个程序不得同时修改同一数据。 在任何时间点，事务中只能有一个会话在执行，不存在并行执行操作。 只能在定义完善的时间点于会话之间切换事务，例如，已完成所有 DML 语句且已检索其结果时。
编写有效的事务
尽可能使事务保持简短很重要。 当事务启动后，数据库管理系统 (DBMS) 必须在事务结束之前保留很多资源，以保护事务的原子性、一致性、隔离性和持久性 (ACID) 属性。 如果修改数据，则必须用排他锁保护修改过的行，以防止任何其他事务读取这些行，并且必须将排他锁控制到提交或回滚事务时为止。 根据事务隔离级别设置，SELECT 语句可以获取必须控制到提交或回滚事务时为止的锁。 特别是在有很多用户的系统中，必须尽可能使事务保持简短以减少并发连接间的资源锁定争夺。 在有少量用户的系统中，运行时间长、效率低的事务可能不会成为问题，但是在有上千个用户的系统中，将不能忍受这样的事务。 从 SQL Server 2014 (12.x) 开始，SQL Server 支持延迟持久事务。 延迟持久事务并不保证持续性。 有关详细信息，请参阅控制事务持续性。
代码指南
以下是编写有效事务的指导原则：
不要在事务处理期间要求用户输入。 在事务启动之前，获得所有需要的用户输入。 如果在事务处理期间还需要其他用户输入，则回滚当前事务，并在提供了用户输入之后重新启动该事务。 即使用户立即响应，作为人，其反应时间也要比计算机慢得多。 事务占用的所有资源都要保留相当长的时间，这有可能会造成阻塞问题。 如果用户没有响应，事务仍然会保持活动状态，从而锁定关键资源直到用户响应为止，但是用户可能会几分钟甚至几个小时都不响应。
在浏览数据时，尽量不要打开事务。 在所有预备的数据分析完成之前，不应启动事务。
尽可能使事务保持简短。 在知道要进行的修改之后，启动事务，执行修改语句，然后立即提交或回滚。 只有在需要时才打开事务。
若要减少阻塞，请考虑针对只读查询使用基于行版本控制的隔离级别。
灵活地使用更低的事务隔离级别。 可以很容易地编写出许多使用只读事务隔离级别的应用程序。 并不是所有事务都要求可序列化的事务隔离级别。
灵活地使用更低的游标并发选项，例如开放式并发选项。 在并发更新的可能性很小的系统中，处理“别人在你读取数据后更改了数据”的偶然错误的开销要比在读取数据时始终锁定行的开销小得多。
在事务中尽量使访问的数据量最小。 这样可以减少锁定的行数，从而减少事务之间的争夺。
尽可能避免使用悲观锁定提示（如 holdlock）。 诸如 HOLDLOCK 或 SERIALIZABLE 隔离级别之类的提示可能会导致进程即使在获取共享锁时也要等待，并且会降低并发性
尽可能避免使用隐式事务。隐式事务会因其性质而导致不可预知的行为。 请参阅隐式事务和并发问题
使用缩减的填充因子设计索引。缩减填充因子可能有助于避免或减少索引页碎片，从而减少索引搜寻时间，尤其是从磁盘检索时。 要查看表或视图的数据和索引的碎片信息，可以使用
sys.dm_db_index_physical_stats。
隐式事务以及避免并发问题和资源问题
为了防止并发问题和资源问题，应小心管理隐式事务。 使用隐式事务时，COMMIT 或 ROLLBACK 后的下一个 Transact-SQL 语句会自动启动一个新事务。 这可能会在应用程序浏览数据时（甚至在需要用户输入时）打开一个新事务。 在完成保护数据修改所需的最后一个事务之后，应关闭隐性事务，直到再次需要使用事务来保护数据修改。 此过程使 SQL Server 数据库引擎能够在应用程序浏览数据以及获取用户输入时使用自动提交模式。
另外，启用快照隔离级别后，尽管新事务不会控制锁，但是长时间运行的事务将阻止从 tempdb中删除旧版本。
管理长时间运行的事务
长时间运行的事务是一个未及时提交或回滚事务的活动事务。 例如，如果事务的开始和结束由用户控制，则导致长时间运行事务的一般原因是用户在开始事务之后便离开，而事务等待用户的响应。
长时间运行的事务可能导致数据库的严重问题，如下所示：
如果服务器实例在活动事务已执行很多未提交的修改后关闭，后续重新启动的恢复阶段持续时间将远远多于恢复间隔服务器配置选项或
ALTER DATABASE ... SET TARGET_RECOVERY_TIME选项指定的时间。 这些选项分别控制活动检查点和间接检查点的频率。 有关检查点类型的详细信息，请参阅数据库检查点 (SQL Server)。更重要的是，尽管等待事务可能生成很小的日志，但是它无限期阻止日志截断，导致事务日志不断增加并可能填满。 如果事务日志填满，数据库将无法再执行任何更新。 有关详细信息，请参阅 SQL Server 事务日志体系结构和管理指南、解决事务日志已满的问题（SQL Server 错误 9002）和事务日志。
重要
在 Azure SQL 数据库中，空闲事务（6 小时内未写入事务日志的事务）会自动终止，以释放资源。
发现长时间运行的事务
若要查看长时间运行的事务，请使用下列方法之一：
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 数据库中的死锁。
相关内容
反馈
即将发布：在整个 2024 年，我们将逐步淘汰作为内容反馈机制的“GitHub 问题”，并将其取代为新的反馈系统。 有关详细信息，请参阅：https://aka.ms/ContentUserFeedback。
提交和查看相关反馈