当前位置：首页 > article >正文

Sqlserver事务行版本控制指南

article 2024/11/16 23:36:05

1. 本文内容

事务基本知识
锁定和行版本控制基本知识
数据库引擎中的锁定
锁粒度和层次结构
锁模式
锁兼容性
键范围锁
锁升级
无优化锁定的锁升级
具有优化锁的锁升级
动态锁定
锁分区
SQL Server 数据库引擎中基于行版本控制的隔离级别
自定义锁定和行版本控制
高级事务信息

适用于：

SQL Server
Azure SQL 数据库
Azure SQL 托管实例
Azure Synapse Analytics
Analytics Platform System (PDW)

在任意数据库中，事务管理不善常常导致用户很多的系统中出现争用和性能问题。随着访问数据的用户数量的增加，拥有能够高效地使用事务的应用程序也变得更为重要。本指南说明 SQL Server 数据库引擎使用的锁定和行版本控制机制，以确保每个事务的物理完整性并提供有关应用程序如何高效控制事务的信息。

2. 事务基本知识

事务是作为单个逻辑工作单元执行的一系列操作。一个逻辑工作单元必须有四个属性，称为原子性、一致性、隔离性和持久性 (ACID) 属性，只有这样才能成为一个事务。

原子性
事务必须是原子工作单元；对于其数据修改，要么全都执行，要么全都不执行。
一致性
事务在完成时，必须使所有的数据都保持一致状态。在相关数据库中，所有规则都必须应用于事务的修改，以保持所有数据的完整性。事务结束时，所有的内部数据结构（如 B 树索引或双向链接列表）都必须是正确的。

SQL Server 文档在提到索引时一般使用 B 树这个术语。在行存储索引中，SQL Server 实现了 B+ 树。这不适用于列存储索引或内存中数据存储。

隔离
由并发事务所做的修改必须与任何其他并发事务所做的修改隔离。事务识别数据时数据所处的状态，要么是另一并发事务修改它之前的状态，要么是第二个事务修改它之后的状态，事务不会识别中间状态的数据。这称为可串行性，因为它能够重新装载起始数据，并且重播一系列事务，以使数据结束时的状态与原始事务执行的状态相同。
持久性
完成完全持久的事务之后，它的影响将永久存在于系统中。即使系统发生故障，修改也会保留。 SQL Server 2014 (12.x) 及更高版本启用延迟的持久事务。提交延迟的持久事务后，该事务日志记录将保留在磁盘上。有关延迟事务持续性的详细信息，请参阅文章控制事务持续性。
https://learn.microsoft.com/en-us/sql/relational-databases/logs/control-transaction-durability?view=sql-server-ver15

SQL 程序员要负责启动和结束事务，同时强制保持数据的逻辑一致性。程序员必须定义数据修改的顺序，使数据相对于其组织的业务规则保持一致。程序员将这些修改语句包括到一个事务中，使 SQL Server 数据库引擎能够强制该事务的物理完整性。

企业数据库系统（如 SQL Server 数据库引擎的实例）有责任提供一种机制，保证每个事务的物理完整性。 SQL Server 数据库引擎提供了：

锁定设备，使事务保持隔离。
通过记录设备，保证事务持久性。对于完全持久的事务，在其提交之前，日志记录将强制写入磁盘。因此，即使服务器硬件、操作系统或 SQL Server 数据库引擎的实例自身出现故障，该实例也可以在重新启动时使用事务日志，将所有未完成的事务自动地回滚到系统出现故障的点。提交延迟的持久事务后，该事务日志记录将强制写入磁盘。如果在日志记录强制写入磁盘前系统出现故障，此类事务可能会丢失。有关延迟事务持续性的详细信息，请参阅文章控制事务持续性。
事务管理特性，强制保持事务的原子性和一致性。事务启动之后，就必须成功完成（提交），否则 SQL Server 数据库引擎将撤消该事务启动之后对数据所做的所有修改。此操作称为回滚事务，因为它将数据恢复到那些更改发生前的状态。

控制事务
应用程序主要通过指定事务启动和结束的时间来控制事务。可以使用 Transact-SQL 语句或数据库应用程序编程接口 (API) 函数来指定这些时间。系统还必须能够正确处理那些在事务完成之前便终止事务的错误。

默认情况下，事务按连接级别进行管理。在一个连接上启动一个事务后，该事务结束之前，在该连接上执行的所有 Transact-SQL 语句都是该事务的一部分。但是，在多重活动结果集 (MARS) 会话中，Transact-SQL 显式或隐式事务将变成批范围的事务，这种事务按批处理级别进行管理。当批处理完成时，如果批范围的事务还没有提交或回滚，SQL Server 将自动回滚该事务。

可以使用显式事务中除以下语句之外的所有 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 语句，将隐性事务模式设置为打开。

为连接将隐性事务模式设置为打开之后，当 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 数据库引擎的实例都可以作为资源管理器来运行。

跨越两个或多个数据库的 SQL Server 数据库引擎的单个实例中的事务实际上是分布式事务。该实例对分布式事务进行内部管理；对于用户而言，其操作就像本地事务一样。

对于应用程序而言，管理分布式事务很像管理本地事务。当事务结束时，应用程序会请求提交或回滚事务。不同的是，分布式提交必须由事务管理器管理，以尽量避免出现因网络故障而导致事务由某些资源管理器成功提交，但由另一些资源管理器回滚的情况。通过分两个阶段（准备阶段和提交阶段）管理提交进程可避免这种情况，这称为两阶段提交 (2PC)。

准备阶段当事务管理器收到提交请求时，它会向该事务涉及的所有资源管理器发送准备命令。然后，每个资源管理器将尽力使该事务持久，并且所有保存该事务日志映像的缓冲区将被刷新到磁盘中。当每个资源管理器完成准备阶段时，它会向事务管理器返回准备成功或准备失败的消息。 SQL Server 2014 (12.x) 引入了延迟事务持续性。在提交延迟的持久事务后，该事务的日志图像将刷入磁盘。有关延迟事务持续性的详细信息，请参阅文章控制事务持续性。

提交阶段如果事务管理器从所有资源管理器收到准备成功的消息，它将向每个资源管理器发送一个提交命令。然后，资源管理器就可以完成提交。如果所有资源管理器都报告提交成功，那么事务管理器就会向应用程序发送一个成功通知。如果任一资源管理器报告准备失败，那么事务管理器将向每个资源管理器发送一个回滚命令，并向应用程序表明提交失败。

SQL Server 数据库引擎应用程序可以通过 Transact-SQL 或数据库 API 来管理分布式事务。

3. 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 中存储的版本控制行链接在一起。

读取数据时的行为

当在基于行版本控制的隔离下运行的事务读取数据时，读取操作不会获取正被读取的数据上的共享锁（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 隔离级别检测到更新冲突，则会自动处理并重试这些冲突，而不会对客户工作负载产生任何影响。

启用优化锁定并使用快照隔离级别后，更新冲突的行为是相同的。更新冲突必须由应用程序手动处理和重试。

行为摘要

下表概括了使用行版本控制的快照隔离与 READ COMMITTED 隔离之间的差异。
在这里插入图片描述

行版本控制资源的使用情况

行版本控制框架支持 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。

基于行版本控制的隔离级别示例

下列示例说明使用行版本控制的快照隔离事务与已提交读事务的行为差异。

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

使用基于行版本控制的隔离级别

行版本控制框架在 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 操作可能会导致对目标表元数据的更改（例如，禁用约束检查时）。如果出现这种情况，访问大容量插入表的并发快照隔离事务将失败。

4. 自定义事务隔离级别

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 数据库引擎实例用于表数据的锁类型或行版本控制。当需要对对象所获得锁类型进行更精细控制时，可以使用表级锁提示。这些锁提示覆盖会话的当前事务隔离级别。

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 锁。此选项不是一个锁提示，但是可用来减少锁升级。

5. 高级事务信息

嵌套事务

显式事务可以嵌套。这主要是为了支持存储过程中的一些事务，这些事务可以从已在事务中的进程调用，也可以从没有活动事务的进程中调用。

下列示例显示了嵌套事务的用途。 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，表明不在事务中。

以下是编写有效事务的指导原则：

不要在事务处理期间要求用户输入。在事务启动之前，获得所有需要的用户输入。如果在事务处理期间还需要其他用户输入，则回滚当前事务，并在提供了用户输入之后重新启动该事务。即使用户立即响应，作为人，其反应时间也要比计算机慢得多。事务占用的所有资源都要保留相当长的时间，这有可能会造成阻塞问题。如果用户没有响应，事务仍然会保持活动状态，从而锁定关键资源直到用户响应为止，但是用户可能会几分钟甚至几个小时都不响应。

在浏览数据时，尽量不要打开事务。在所有预备的数据分析完成之前，不应启动事务。

尽可能使事务保持简短。在知道要进行的修改之后，启动事务，执行修改语句，然后立即提交或回滚。只有在需要时才打开事务。

若要减少阻塞，请考虑针对只读查询使用基于行版本控制的隔离级别。

灵活地使用更低的事务隔离级别。可以很容易地编写出许多使用只读事务隔离级别的应用程序。并不是所有事务都要求可序列化的事务隔离级别。

灵活地使用更低的游标并发选项，例如开放式并发选项。在并发更新的可能性很小的系统中，处理“别人在你读取数据后更改了数据”的偶然错误的开销要比在读取数据时始终锁定行的开销小得多。

在事务中尽量使访问的数据量最小。这样可以减少锁定的行数，从而减少事务之间的争夺。

尽可能避免使用悲观锁定提示（如 holdlock）。诸如 HOLDLOCK 或 SERIALIZABLE 隔离级别之类的提示可能会导致进程即使在获取共享锁时也要等待，并且会降低并发性

尽可能避免使用隐式事务。隐式事务会因其性质而导致不可预知的行为。请参阅隐式事务和并发问题

使用缩减的填充因子设计索引。缩减填充因子可能有助于避免或减少索引页碎片，从而减少索引搜寻时间，尤其是从磁盘检索时。要查看表或视图的数据和索引的碎片信息，可以使用 sys.dm_db_index_physical_stats。

查看全文

http://www.kler.cn/a/321675.html