锁定设备，使事务保持隔离。

通过记录设备，保证事务持久性。 对于完全持久的事务，在其提交之前，日志记录将强制写入磁盘。 因此，即使服务器硬件、操作系统或SQL Server数据库引擎本身的实例发生故障，该实例也会在重启时使用事务日志自动将任何未完成的事务回滚到系统故障点。 提交延迟的持久事务后，该事务日志记录将强制写入磁盘。 如果在日志记录强制写入磁盘前系统出现故障，此类事务可能会丢失。 有关延迟事务持续性的详细信息，请参阅文章 事务持续性 。

事务管理特性，强制保持事务的原子性和一致性。 事务启动后，必须成功完成 (提交) ，否则SQL Server数据库引擎将撤消自事务启动以来所做的所有数据修改。 此操作称为回滚事务，因为它将数据恢复到那些更改发生前的状态。

应用程序主要通过指定事务启动和结束的时间来控制事务。 这可以通过使用 Transact-SQL 语句或数据库应用程序编程接口 (API) 函数来指定。 系统还必须能够正确处理那些在事务完成之前便终止事务的错误。 有关详细信息，请参阅 事务 、 ODBC 中的事务 以及 SQL Server Native Client (OLEDB) 中的事务 。

默认情况下，事务按连接级别进行管理。 在连接上启动事务时，在该连接上执行的所有 Transact-SQL 语句都是事务的一部分，直到该事务结束。 但是，在 MARS) 会话 (多个活动结果集下，Transact-SQL 显式或隐式事务将成为批处理级别管理的批处理范围事务。 批处理完成后，如果未提交或回滚批处理范围内的事务，则由SQL Server自动回滚。 有关详细信息，请参阅 使用多重活动结果集 (MARS) 。

使用 API 函数和 Transact-SQL 语句，可以将 SQL Server 数据库引擎实例中的事务作为显式、自动提交或隐式事务启动。

可以在显式事务中使用所有 Transact-SQL 语句，但以下语句除外：

ALTER TABLE

CREATE

FETCH

GRANT

INSERT

REVOKE

SELECT

TRUNCATE TABLE

UPDATE

批处理级事务
只能应用于多个活动结果集 (MARS)，在 MARS 会话中启动的 Transact-SQL 显式或隐式事务变为批处理级事务。 当批处理完成时没有提交或回滚的批处理级事务自动由 SQL Server 进行回滚。

分布式事务
分布式事务跨越两个或多个称为资源管理器的服务器。 称为事务管理器的服务器组件必须在资源管理器之间协调事务管理。 SQL Server 数据库引擎的每个实例都可以在事务管理器协调的分布式事务中充当资源管理器，例如 Microsoft 分布式事务处理协调器 (MS DTC) ，或其他支持开放组 XA 规范的分布式事务处理事务管理器。 有关详细信息，请参阅 MS DTC 文档。

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

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

SQL Server数据库引擎应用程序可以通过 Transact-SQL 或数据库 API 管理分布式事务。 有关详细信息，请参阅 BEGIN DISTRIBUTED TRANSACTION (Transact-SQL) 。

在为支持多个活动的结果集 (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));  
INSERT INTO TestBatch VALUES (1, 'aaa');  
INSERT INTO TestBatch VALUES (2, 'bbb');  
INSERT INTO TestBatch VALUSE (3, 'ccc');  -- Syntax error.  
SELECT * FROM TestBatch;  -- Returns no rows.  
在下面的示例中，第三个 INSERT 语句产生运行时重复主键错误。 由于前两个 INSERT 语句成功地执行并且提交，因此它们在运行时错误之后被保留下来。
CREATE TABLE TestBatch (Cola INT PRIMARY KEY, Colb CHAR(3));  
INSERT INTO TestBatch VALUES (1, 'aaa');  
INSERT INTO TestBatch VALUES (2, 'bbb');  
INSERT INTO TestBatch VALUES (1, 'ccc');  -- Duplicate key error.  
SELECT * FROM TestBatch;  -- Returns rows 1 and 2.  
SQL Server数据库引擎使用延迟名称解析，其中对象名称直到执行时间才会解析。 在下面的示例中，执行并提交了前两个 INSERT 语句，在第三个 TestBatch 语句由于引用一个不存在的表而产生运行时错误之后，这两行仍然保留在 INSERT 表中。
CREATE TABLE TestBatch (Cola INT PRIMARY KEY, Colb CHAR(3));  
INSERT INTO TestBatch VALUES (1, 'aaa');  
INSERT INTO TestBatch VALUES (2, 'bbb');  
INSERT INTO TestBch VALUES (3, 'ccc');  -- Table name error.  
SELECT * FROM TestBatch;  -- Returns rows 1 and 2.  
  锁定和行版本控制基本知识
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 操作存在限制。 在快照隔离下，以下 DDL 语句不允许出现在 BEGIN TRANSACTION 语句后：ALTER TABLE、CREATE INDEX、CREATE XML INDEX、ALTER INDEX、DROP INDEX、DBCC REINDEX、ALTER PARTITION FUNCTION、ALTER PARTITION SCHEME 或任何常用语言运行时(CLR) DDL 语句。 在隐式事务中使用快照隔离时，允许使用这些语句。 根据定义，隐式事务为单个语句，这使得它可以强制应用快照隔离的语义，即便使用 DDL 语句也是如此。 违反此原则会导致错误 3961: Snapshot isolation transaction failed in database '%.*ls' because the object accessed by the statement has been modified by a DDL statement in another concurrent transaction since the start of this transaction. It is not allowed because the metadata is not versioned. A concurrent update to metadata could lead to inconsistency if mixed with snapshot isolation.
下表显示了不同隔离级别导致的并发副作用。
不可重复读
有关每个事务隔离级别控制的特定类型的锁定或行版本控制的详细信息，请参阅 SET TRANSACTION ISOLATION LEVEL (Transact-SQL) 。
可以使用 Transact-SQL 或通过数据库 API 设置事务隔离级别。
Transact-SQL

Transact-SQL 脚本使用 SET TRANSACTION ISOLATION LEVEL 语句。
ADO 应用程序将 IsolationLevelConnection 对象的 属性设置为 adXactReadUncommitted、 adXactReadCommitted、 adXactRepeatableRead或 adXactReadSerializable。

ADO.NET

使用System.Data.SqlClient托管命名空间 ADO.NET 应用程序可以调用 SqlConnection.BeginTransaction 方法并将 选项设置为 UnspecifiedIsolationLevel 、、Chaos、ReadCommittedReadUncommitted、RepeatableRead、 Serializable或 Snapshot。




    

OLE DB

启动事务时，使用 OLE DB 的应用程序调用ITransactionLocal::StartTransactionisoLevel设置为 ISOLATIONLEVEL_READUNCOMMITTED、ISOLATIONLEVEL_READCOMMITTED、ISOLATIONLEVEL_REPEATABLEREAD、 ISOLATIONLEVEL_SNAPSHOT或 ISOLATIONLEVEL_SERIALIZABLE。
在自动提交模式下指定事务隔离级别时，OLE DB 应用程序可以将 属性DBPROP_SESS_AUTOCOMMITISOLEVELS设置为 DBPROPSET_SESSIONDBPROPVAL_TI_CHAOS、、DBPROPVAL_TI_READUNCOMMITTED、DBPROPVAL_TI_BROWSE、DBPROPVAL_TI_READCOMMITTEDDBPROPVAL_TI_CURSORSTABILITYDBPROPVAL_TI_REPEATABLEREAD、DBPROPVAL_TI_SERIALIZABLE、 DBPROPVAL_TI_ISOLATED或 。DBPROPVAL_TI_SNAPSHOT
ODBC 应用程序调用 SQLSetConnectAttr 时 Attribute 设置为 SQL_ATTR_TXN_ISOLATION ，设置为 ValuePtrSQL_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_ATTR_TXN_ISOLATION检索SQL_COPT_SS_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数据库引擎可以锁定的资源。
共享锁（S 锁）允许并发事务在封闭式并发控制下读取 (SELECT) 资源。 资源上存在共享锁（S 锁）时，任何其他事务都不能修改数据。 读取操作一完成，就立即释放资源上的共享锁（S 锁），除非将事务隔离级别设置为可重复读或更高级别，或者在事务持续时间内用锁定提示保留共享锁（S 锁）。
更新锁（U 锁）可以防止常见的死锁。 在可重复读或可序列化事务中，此事务读取数据 [获取资源（页或行）的共享锁（S 锁）]，然后修改数据 [此操作要求锁转换为排他锁（X 锁）]。 如果两个事务获得了资源上的共享模式锁，然后试图同时更新数据，则一个事务尝试将锁转换为排他锁（X 锁）。 共享模式到排他锁的转换必须等待一段时间，因为一个事务的排他锁与其他事务的共享模式锁不兼容；发生锁等待。 第二个事务试图获取排他锁（X 锁）以进行更新。 由于两个事务都要转换为排他锁（X 锁），并且每个事务都等待另一个事务释放共享模式锁，因此发生死锁。
若要避免这种潜在的死锁问题，请使用更新锁（U 锁）。 一次只有一个事务可以获得资源的更新锁（U 锁）。 如果事务修改资源，则更新锁（U 锁）转换为排他锁（X 锁）。
有关死锁的详细信息，请参阅 死锁指南。
排他锁（X 锁）可以防止并发事务对资源进行访问。 使用排他锁（X 锁）时，任何其他事务都无法修改数据；仅在使用 NOLOCK 提示或未提交读隔离级别时才会进行读取操作。
数据修改语句（如 INSERT、UPDATE 和 DELETE）合并了修改和读取操作。 语句在执行所需的修改操作之前首先执行读取操作以获取数据。 因此，数据修改语句通常请求共享锁和排他锁。 例如，UPDATE 语句可能根据与一个表的联接修改另一个表中的行。 在此情况下，除了请求更新行上的排他锁之外，UPDATE 语句还将请求在联接表中读取的行上的共享锁。
SQL Server数据库引擎使用意向锁来保护共享 (S) 锁或排他 (x) 锁放置在锁层次结构中较低位置的资源上。 意向锁被命名为“意向锁”，因为它们是在较低级别的锁之前获取的，因此，它们表示意向将锁置于较低级别。
意向锁有两种用途：
防止其他事务以会使较低级别的锁无效的方式修改较高级别资源。
提高 SQL Server数据库引擎在更高级别的粒度下检测锁冲突的效率。
例如，在该表的页或行上请求共享锁（S 锁）之前，在表级请求共享意向锁。 在表级设置意向锁可防止另一个事务随后在包含那一页的表上获取排他锁（X 锁）。 意向锁可以提高性能，因为SQL Server数据库引擎仅在表级别检查意向锁，以确定事务是否可以安全地获取该表的锁。 而不需要检查表中的每行或每页上的锁以确定事务是否可以锁定整个表。
意向锁包括意向共享 (IS)、意向排他 (IX) 以及意向排他共享 (SIX)。

意向排他共享 (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 锁）。 但是，在释放共享锁之前，其他事务无法获取排他锁。
下表显示了最常见的锁模式的兼容性。

现有的授予模式
意向排他锁（IX 锁）与 IX 锁模式兼容，因为 IX 表示打算只更新部分行而不是所有行。 还允许其他事务尝试读取或更新部分行，只要这些行不是其他事务当前更新的行即可。 此外，如果两个事务尝试更新同一行，将在表级和页级上授予这两个事务 IX 锁。 但是，将在行级授予一个事务 X 锁。 另一个事务必须在该行级锁被删除前等待。
使用下表确定 SQL Server 中可用的所有锁模式的兼容性。

键范围锁在使用可序列化事务隔离级别时，保护由 Transact-SQL 语句读取的记录集中隐式包含的行范围。 可序列化隔离级别要求每当在事务期间执行任一查询时，该查询都必须获取相同的行集。 键范围锁可防止其他事务插入其键值位于可序列化事务读取的键值范围内的新行，从而确保满足此要求。
键范围锁可防止虚拟读取。 通过保护行之间的键范围，它还可以防止对事务访问的记录集进行虚拟插入。
键范围锁放置在索引上，指定开始键值和结束键值。 此锁将阻止任何要插入、更新或删除任何带有该范围内的键值的行的尝试，因为这些操作会首先获取索引上的锁。 例如，可序列化事务可以发出 SELECT 语句，该语句读取键值与条件 BETWEEN 'AAA' AND 'CZZ' 匹配的所有行。 对“AAA”到“CZZ”范围内的键值进行键范围锁定，可防止其他事务在该范围中的任何位置插入具有键值的行，例如“ADG”、“BBD”或“CAL”。
  键范围锁模式
键范围锁包括按范围-行格式指定的范围组件和行组件：
范围表示保护两个连续索引项之间的范围的锁模式。
行表示保护索引项的锁模式。
模式表示使用的组合锁模式。 键范围锁模式由两部分组成。 第一部分表示用于锁定索引范围 (RangeT) 的锁类型，第二部分表示用于锁定特定键 (K) 的锁类型 。 这两部分用连字符 (-) 连接，例如 RangeT-K 。
事务隔离级别必须设置为 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 数据库选项和 SNAPSHOT 隔离级别还允许从以前提交状态的行版本读取数据。) 
可以使用三个基本锁模式执行范围删除：行锁、页锁或表锁。 行、页或表锁定策略由查询优化器确定，也可以由用户通过查询优化器提示（例如 ROWLOCK、PAGLOCK 或 TABLOCK）来指定。 使用 PAGLOCK 或 TABLOCK 时，如果从此页面中删除所有行，SQL Server数据库引擎会立即解除分配索引页。 相反，当使用 ROWLOCK 时，所有删除的行只是标记为已删除；以后通过后台任务从索引页中删除它们。
具有优化锁定的删除操作
删除事务中的值时，行锁和页锁以增量方式获取和释放，并且不会在事务期间保留。 例如，对于下面的 DELETE 语句：
DELETE mytable  
WHERE name = 'Bob';  
在事务持续时间内，所有修改的行上都设置了 TID 锁。 在对应于名称 Bob的索引条目的 TID 上获取锁。 通过优化的锁定，可以继续获取页锁和行锁进行更新，但每行更新后，每一页锁和行锁都会立即释放。 TID 锁可防止在事务完成之前更新行。 尝试读取、插入或删除该值 Bob 的任何事务都将被阻止，直到删除事务提交或回滚为止。  (READ_COMMITTED_SNAPSHOT 数据库选项和 SNAPSHOT 隔离级别还允许从以前提交状态的行版本读取数据。) 
否则，删除操作的锁定机制与未优化锁定的锁定机制相同。
 没有优化锁定的插入操作
在事务中插入值时，在事务执行插入操作期间不必锁定该值所属的范围。 锁定插入的键值直至事务结束足以维护可序列化性。 例如，对于下面的 INSERT 语句：
INSERT mytable VALUES ('Dan');  
RangeI-N 模式键范围锁放置在与名称 David 对应的索引项上以测试范围。 如果已授权锁，则插入 Dan，并且排他锁（X 锁）将放置在值 Dan 上。 RangeI-N 模式键范围锁仅对测试范围是必需的，而不在执行插入操作的事务期间保留。 其他事务可以在插入的值 Dan 的前后插入或删除值。 但是，任何试图读取、插入或删除值 Dan 的事务都将被阻塞，直到插入的事务提交或回滚为止。
使用优化锁定的插入操作
在事务中插入值时，在事务执行插入操作期间不必锁定该值所属的范围。 仅当正在进行联机索引重新生成或实例中存在可 serailiziz 事务时，很少获取行锁和页锁。 如果获取行锁和页锁，它们会快速释放，并且不会在事务期间保留。 在插入的键值上放置独占 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 级锁通常会增加并发情况，但是当锁定不同分区的每个事务都希望将其排他锁扩展到其他分区时，有可能会发生死锁。 在极少数情况下，TABLE 锁定粒度可能更适合。
如果锁升级尝试由于并发事务持有的锁冲突而失败，数据库引擎将对事务获取的每一个额外的 1，250 个锁重试锁升级。
每个升级事件主要在单个 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 锁和表上的 IX 锁。
SELECT 语句将获取下列锁：
所读取的所有数据行上的共享锁（S 锁），除非行已被来自 UPDATE 语句的 X 锁保护。
包含那些行的所有聚集索引页上的意向共享锁，除非页已被 IX 锁保护。
在聚集索引或表上不会获取锁，因为它们已被 IX 锁保护。
如果 SELECT 获取了触发锁升级的足够锁并且升级成功，表上的 IX 锁将被转换为 X 锁，而所有行、页和索引锁都将被释放。 更新和读取操作都受表上的 X 锁保护。
 减少锁定和升级
在大多数情况下，数据库引擎在使用用于锁定和锁定升级的默认设置进行操作时，可提供最佳性能。
在 可用的情况下利用优化的锁定。
优化锁定 提供改进的事务锁定机制，可减少并发事务的锁内存消耗和阻塞。 启用优化锁定后，锁升级的可能性要小得多。
避免使用 具有优化锁定的表提示。 表提示可能会降低优化锁定的有效性。
在数据库中启用 READ_COMMITTED_SNAPSHOT ，以充分利用优化的锁定。 这是 Azure SQL 数据库中的默认隔离级别。
优化锁定要求在数据库上启用 加速数据库恢复 (ADR)  。
如果数据库引擎的实例生成了大量锁，并且锁升级频繁，请考虑使用以下策略减少锁定量：
使用不为读取操作生成共享锁的隔离级别：
当 READ_COMMITTED_SNAPSHOT 数据库选项为 ON 时，使用 READ COMMITTED 隔离级别。
使用 SNAPSHOT 隔离级别。
使用 READ UNCOMMITTED 隔离级别。 此隔离级别只能用于能对脏读进行操作的系统。
更改隔离级别会影响数据库引擎实例上的所有表。
使用 PAGLOCK 或 TABLOCK 表提示让数据库引擎使用页锁、堆锁或索引锁，而不是低级别锁。 但是，使用此选项增加了用户阻止其他用户尝试访问相同数据的问题，对于并发用户较多的系统，不应使用此选项。
如果未启用优化锁定，则对于已分区表，请使用 ALTER TABLE 的LOCK_ESCALATION选项将锁升级到 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.
在出现错误 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')
lock_escalation应使用扩展事件 (xEvent) ，而不是 SQL 跟踪或 SQL 探查器中的 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);  
-- Create a clustered index on the table.  
CREATE CLUSTERED INDEX ci_TestTable   
    ON TestTable (col1);  
-- Populate the table.  
INSERT INTO TestTable VALUES (1);  
会话 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);  
会话 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 锁。 仅当行限定并且该行或页面上有活动的写入事务时，查询才会等待。 基于最新提交的版本进行限定并仅锁定限定行可减少阻塞并提高并发性。
如果在默认 READ COMMITTED 隔离级别使用 RCSI 检测到更新冲突，系统会自动处理并重试这些冲突，而不会对客户工作负载造成任何影响。
启用优化锁定后，使用 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 (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 操作。
nvarchar(max)、 varchar(max)和 varbinary(max) 数据类型在 SQL Server 的早期版本中不可用。 因此，这些数据类型不存在升级问题。
应该分配足够的磁盘空间来满足此要求。
 监视行版本控制以及版本存储
为了监视行版本控制、版本存储和快照隔离过程的性能和问题，SQL Server在 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。 返回一个虚拟表，其中显示有当前快照隔离事务启动时的所有活动事务。 如果当前事务正在使用快照隔离，则该函数不返回行。 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 (KB) 为单位）。 中tempdb必须有足够的可用空间来处理支持快照隔离的版本存储。
下列公式可以用来粗略估计版本存储区的大小。 对于长时间运行的事务，监视生成速率和清除速率对于估计版本存储区的最大大小会非常有用。
[公共版本存储区的大小] = 2 * [每分钟生成的版本存储区数据] * [事务的最长运行时间（分钟）]
事务的最长运行时间不应该包括联机索引生成时间。 对于超大型表，由于这些操作可能要花很长的时间，因此联机索引生成使用单独的版本存储区。 当联机索引生成处于活动状态时，联机索引生成版本存储区的近似大小等于表（包括所有索引）中修改的数据量。
Version Store Size (KB) 。 监视所有版本存储区的大小 (KB)。 此信息有助于确定数据库中的版本存储所需的 tempdb 空间量。 在一段时间内监视此计数器可对 所需的额外空间进行 tempdb有用的估计。
Version Generation rate (KB/s)。 监视所有版本存储区中的版本生成速率（KB/秒）。
Version Cleanup rate (KB/s)。 监视所有版本存储区中的版本清除速率（KB/秒）。
版本生成速率 (KB/s) 和版本清理速率 (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 AdventureWorks2019;  
-- Enable snapshot isolation on the database.  
ALTER DATABASE AdventureWorks2019  
    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 AdventureWorks2019;  
-- 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 AdventureWorks2019;  -- 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 AdventureWorks2019  
-- database.  
ALTER DATABASE AdventureWorks2019  
    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 AdventureWorks2019;  
-- 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 AdventureWorks2019  
    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 AdventureWorks2019  
    SET ALLOW_SNAPSHOT_ISOLATION ON;  
下表列出并说明了 ALLOW_SNAPSHOT_ISOLATION 选项的各个状态。 同时使用 ALTER DATABASE 和 ALLOW_SNAPSHOT_ISOLATION 选项不会妨碍当前正在访问数据库数据的用户。
当前数据库的快照隔离框架状态
使用 sys.databases 目录视图可以确定两个行版本控制数据库选项的状态。
对存储在 中的 master 用户表和某些系统表的所有更新并 msdb 生成行版本。
选项ALLOW_SNAPSHOT_ISOLATION在 和 msdb 数据库中master自动设置为 ON，并且无法禁用。
用户不能在 READ_COMMITTED_SNAPSHOT 、 tempdb或 msdb中master设置 ON 选项。
 使用基于行版本控制的隔离级别
行版本控制框架始终在 SQL Server 中启用，并由多个功能使用。 它除了提供基于行版本控制的隔离级别之外，还用于支持对触发器和多个活动结果集 (MARS) 会话的修改，以及 ONLINE 索引操作的数据读取。
基于行版本控制的隔离级别是在数据库级别上启用的。 访问已启用数据库的对象的任何应用程序可以使用以下隔离级别运行查询：
已提交读隔离级别，通过将 READ_COMMITTED_SNAPSHOT 数据库选项设置为 ON 来使用行版本控制，如下面的代码示例所示：
ALTER DATABASE AdventureWorks2019  
    SET READ_COMMITTED_SNAPSHOT ON;  
为 READ_COMMITTED_SNAPSHOT启用数据库时，在 READ COMMITTED 隔离级别下运行的所有查询都使用行版本控制，这意味着读取操作不会阻止更新操作。
快照隔离，通过将 ALLOW_SNAPSHOT_ISOLATION 数据库选项设置为 ON 实现，如下面的代码示例所示：
ALTER DATABASE AdventureWorks2019  
    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无法在 、 msdb或 master中tempdb启用。
全局临时表存储在 中 tempdb。 访问快照事务中的全局临时表时，必须执行下列操作之一：
在 中tempdb设置ALLOW_SNAPSHOT_ISOLATION数据库选项 ON。
使用隔离提示更改语句的隔离级别。
如果出现以下情况，快照事务将失败：
从快照事务启动之后到访问数据库前的期间内，数据库设置为只读。
如果访问多个数据库的对象，数据库状态以如下方式更改：从快照事务启动后到访问数据库前的期间内，发生数据库恢复。 例如：将数据库设置为 OFFLINE，然后设置为 ONLINE，数据库将自动关闭和打开，或数据库将分离和附加。
快照隔离不支持分布式事务，包括分布式分区数据库中的查询。
SQL Server不保留多个版本的系统元数据。 表中的数据定义语言 (DDL) 语句和其他数据库对象（索引、视图、数据类型、存储过程和公共语言运行时函数）会更改元数据。 如果 DDL 语句修改一个对象，那么在快照隔离下对该对象的任何并发引用都将导致快照事务失败。 READ_COMMITTED_SNAPSHOT 数据库选项为 ON 时，已提交读事务没有此限制。
例如，数据库管理员执行下面的 ALTER INDEX 语句。
USE AdventureWorks2019;  
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;  
 自定义事务隔离级别
READ COMMITTED 是Microsoft SQL Server 数据库引擎的默认隔离级别。 如果应用程序必须在其他隔离级别运行，则它可以使用以下方法设置隔离级别：
运行 SET TRANSACTION ISOLATION LEVEL 语句。
使用 System.Data.SqlClient 托管命名空间 ADO.NET 应用程序可以使用 方法指定 IsolationLevel 选项 SqlConnection.BeginTransaction 。
使用了 ADO 的应用程序可以设置 Autocommit Isolation Levels 属性。
启动事务时，使用 OLE DB 的应用程序可以调用 ITransactionLocal::StartTransaction ，并将 isoLevel 设置为所需的事务隔离级别。 在自动提交模式下指定隔离级别时，使用 OLE DB 的应用程序可以将 属性DBPROP_SESS_AUTOCOMMITISOLEVELS设置为DBPROPSET_SESSION所需的事务隔离级别。
使用 ODBC 的应用程序可以使用 设置 SQL_COPT_SS_TXN_ISOLATION 属性 SQLSetConnectAttr。
指定隔离级别后，SQL Server会话中所有查询和数据操作语言 (DML) 语句的锁定行为都会在该隔离级别运行。 隔离级别将在会话终止或将其设置为其他级别后失效。
下面的示例设置 SERIALIZABLE 隔离级别：
USE AdventureWorks2019;  
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;  
BEGIN TRANSACTION;  
SELECT BusinessEntityID  
    FROM HumanResources.Employee;  
必要时，可以通过指定表级提示来替代各个查询语句或 DML 语句的隔离级别。 指定表级提示不会影响会话中的其他语句。 建议仅在确实必要时才使用表级提示更改默认行为。
即使隔离级别设置为读取数据时不请求共享锁的级别，SQL Server数据库引擎在读取元数据时可能必须获取锁。 例如，在未提交读隔离级别下运行的事务在读取数据时将不获取共享锁，但是在读取系统目录视图时可能会请求锁。 这意味着在查询表时如果某个并发事务正在修改该表的元数据，则未提交读事务可能会导致阻塞。
若要确定当前设置的事务隔离级别，请使用 DBCC USEROPTIONS 语句，如下面的示例所示。 该结果集可能与系统的结果集不同。
USE AdventureWorks2019;  
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 AdventureWorks2019;  
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，表明不在事务中。
 使用绑定会话
绑定会话有利于在同一台服务器上的多个会话之间协调操作。 绑定会话允许一个或多个会话共享相同的事务和锁，并可以使用同一数据，而不会有锁冲突。 可以从同一个应用程序内的多个会话中创建绑定会话，也可以从包含不同会话的多个应用程序中创建绑定会话。
若要参与绑定会话，会话通过 Open Data Services ) 调用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 语句可以获取必须控制到提交或回滚事务时为止的锁。 特别是在有很多用户的系统中，必须尽可能使事务保持简短以减少并发连接间的资源锁定争夺。 在有少量用户的系统中，运行时间长、效率低的事务可能不会成为问题，但是在有上千个用户的系统中，将不能忍受这样的事务。 从 2014 SQL Server 开始， (12.x) SQL Server支持延迟持久事务。 延迟持久事务并不保证持续性。 有关详细信息，请参阅 事务持续性。
以下是编写有效事务的指导原则：
不要在事务处理期间要求用户输入。

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

在所有预备的数据分析完成之前，不应启动事务。
尽可能使事务保持简短。

在知道要进行的修改之后，启动事务，执行修改语句，然后立即提交或回滚。 只有在需要时才打开事务。
若要减少阻塞，请考虑针对只读查询使用基于行版本控制的隔离级别。
灵活地使用更低的事务隔离级别。

可以很容易地编写出许多使用只读事务隔离级别的应用程序。 并不是所有事务都要求可序列化的事务隔离级别。
灵活地使用更低的游标并发选项，例如开放式并发选项。

在并发更新的可能性很小的系统中，处理“别人在您读取数据后更改了数据”的偶然错误的开销要比在读取数据时始终锁定行的开销小得多。
在事务中尽量使访问的数据量最小。

这样可以减少锁定的行数，从而减少事务之间的争夺。
尽可能避免使用悲观锁定提示（如 holdlock）。
诸如 HOLDLOCK 或 SERIALIZABLE 隔离级别之类的提示可能会导致进程即使在获取共享锁时也要等待，并且会降低并发性
尽可能避免使用隐式事务。隐式事务会因其性质而导致不可预知的行为。 请参阅隐式事务和并发问题
使用缩减的填充因子设计索引。缩减填充因子可能有助于避免或减少索引页碎片，从而减少索引搜寻时间，尤其是从磁盘检索时。 若要查看表或视图的数据和索引的碎片信息，可以使用 sys.dm_db_index_physical_stats。
隐式事务以及避免并发问题和资源问题
为了防止并发问题和资源问题，应小心管理隐式事务。 使用隐式事务时，或 之后COMMITROLLBACK的下一个 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 数据库阻塞问题