- 新实现的 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 隔离级别检测到更新冲突，则会自动处理并重试这些冲突，而不会对客户工作负载产生任何影响。
启用优化锁定并使用 SNAPSHOT 隔离级别后，更新冲突的行为是相同的。 更新冲突必须由应用程序手动处理和重试。
有关优化锁定的限定后锁定 (LAQ) 功能的行为更改的详细信息，请参阅使用优化锁定和 RCSI 更改查询行为。
下表概括了使用行版本控制的快照隔离与 READ COMMITTED 隔离之间的差异。
properties
使用行版本控制的已提交读隔离级别
快照隔离级别
会话如何请求特定类型的行版本控制。
使用默认的已提交读隔离级别，或运行 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 提供有关 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。 返回一个虚拟表，其中显示有当前快照隔离事务启动时的所有活动事务。 如果当前事务正在使用快照隔离，则该函数不返回行。 DMV sys.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;
-- Enable snapshot isolation on the database.
ALTER DATABASE AdventureWorks2022
SET ALLOW_SNAPSHOT_ISOLATION ON;
-- Start a snapshot transaction
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
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;
-- 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;
在会话 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
B. 使用通过行版本控制的已提交读
在此示例中，使用行版本控制的已提交读事务与其他事务并发运行。 已提交读事务的行为与快照事务的行为有所不同。 与快照事务相同的是，即使其他事务修改了数据，已提交读事务也将读取版本化的行。 然而，与快照事务不同的是，已提交读将执行下列操作：
- 在其他事务提交数据更改后，读取修改的数据。
- 能够更新由其他事务修改的数据，而快照事务不能。
在会话 1 上：
USE AdventureWorks2022; -- Or any earlier version of the AdventureWorks database.
-- 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;
-- Start a read-committed transaction
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
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;
-- 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;
在会话 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;
启用基于行版本控制的隔离级别
数据库管理员可以通过在 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 选项不会妨碍当前正在访问数据库数据的用户。
当前数据库的快照隔离框架状态
使用 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;
ALTER INDEX AK_Employee_LoginID
ON HumanResources.Employee REBUILD;
执行 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;
自定义事务隔离级别
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;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;
SELECT BusinessEntityID
FROM HumanResources.Employee;
必要时，可以通过指定表级提示来替代各个查询语句或 DML 语句的隔离级别。 指定表级提示不会影响会话中的其他语句。 建议仅在确实必要时才使用表级提示更改默认行为。
读取元数据时，甚至当隔离级别设置为在读取数据时不请求共享锁的级别时，SQL Server 数据库引擎也可能需要获取锁。 例如，在未提交读隔离级别下运行的事务在读取数据时将不获取共享锁，但是在读取系统目录视图时可能会请求锁。 这意味着在查询表时如果某个并发事务正在修改该表的元数据，则未提交读事务可能会导致阻塞。
若要确定当前设置的事务隔离级别，请使用 DBCC USEROPTIONS 语句，如下面的示例所示。 该结果集可能与系统的结果集不同。
USE AdventureWorks2022;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
DBCC USEROPTIONS;
下面是结果集。
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;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;
SELECT JobTitle
FROM HumanResources.Employee WITH (NOLOCK);
-- 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;
引用 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;
SET NOCOUNT OFF;
CREATE TABLE TestTrans(Cola INT PRIMARY KEY,
Colb CHAR(3) NOT NULL);
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;
/* Start a transaction and execute TransProc. */
BEGIN TRANSACTION OutOfProc;
EXEC TransProc 1, 'aaa';
/* Roll back the outer transaction, this will
roll back TransProc's nested transaction. */
ROLLBACK TRANSACTION OutOfProc;
EXECUTE TransProc 3,'bbb';
/* 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;
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）和事务日志 (SQL Server)。
发现长时间运行的事务
若要查看长时间运行的事务，请使用下列方法之一：
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)。
死锁是与锁定相关的复杂主题，但与阻止不同。
- 有关死锁（包括监视、诊断和示例）的详细信息，请参阅 SQL Server 数据库引擎的死锁指南。
- 有关特定于 Azure SQL 数据库的死锁的详细信息，请参阅分析和防止 Azure SQL 数据库中的死锁。
- 行版本控制的系统开销
- sys.dm_tran_locks (Transact-SQL)
- 动态管理视图和函数 (Transact-SQL)
- 与事务有关的动态管理视图和函数 (Transact-SQL)
- 了解和解决 SQL Server 阻塞问题
- 了解并解决 Azure SQL 数据库阻塞问题
