SQL Server 事务锁定和行版本控制指南SQL Server Transaction Locking and Row Versioning Guide

在任意数据库中,事务管理不善常常导致用户很多的系统中出现争用和性能问题。In any database, mismanagement of transactions often leads to contention and performance problems in systems that have many users. 随着访问数据的用户数量的增加,拥有能够高效地使用事务的应用程序也变得更为重要。As the number of users that access the data increases, it becomes important to have applications that use transactions efficiently. 本指南说明 SQL Server 数据库引擎SQL Server Database Engine使用的锁定和行版本控制机制,以确保每个事务的物理完整性并提供有关应用程序如何高效控制事务的信息。This guide describes the locking and row versioning mechanisms the SQL Server 数据库引擎SQL Server Database Engine uses to ensure the physical integrity of each transaction and provides information on how applications can control transactions efficiently.

适用于:SQL Server 2005SQL Server 2005通过SQL Server 2014SQL Server 2014除非另有说明。Applies to: SQL Server 2005SQL Server 2005 through SQL Server 2014SQL Server 2014 unless noted otherwise.

本指南中In This Guide

事务基本知识Transaction Basics

锁定和行版本控制基本知识Locking and Row Versioning Basics

数据库引擎中的锁定Locking in the Database Engine

数据库引擎中基于行版本控制的隔离级别Row Versioning-based Isolation Levels in the Database Engine

自定义索引的锁定Customizing Locking for an Index

高级的事务信息Advanced Transaction Information

事务基本知识Transaction Basics

事务是作为单个逻辑工作单元执行的一系列操作。A transaction is a sequence of operations performed as a single logical unit of work. 一个逻辑工作单元必须有四个属性,称为原子性、一致性、隔离性和持久性 (ACID) 属性,只有这样才能成为一个事务。A logical unit of work must exhibit four properties, called the atomicity, consistency, isolation, and durability (ACID) properties, to qualify as a transaction.

原子性Atomicity
事务必须是原子工作单元;对于其数据修改,要么全都执行,要么全都不执行。A transaction must be an atomic unit of work; either all of its data modifications are performed, or none of them are performed.

一致性Consistency
事务在完成时,必须使所有的数据都保持一致状态。When completed, a transaction must leave all data in a consistent state. 在相关数据库中,所有规则都必须应用于事务的修改,以保持所有数据的完整性。In a relational database, all rules must be applied to the transaction's modifications to maintain all data integrity. 事务结束时,所有的内部数据结构(如 B 树索引或双向链表)都必须是正确的。All internal data structures, such as B-tree indexes or doubly-linked lists, must be correct at the end of the transaction.

隔离Isolation
由并发事务所做的修改必须与任何其他并发事务所做的修改隔离。Modifications made by concurrent transactions must be isolated from the modifications made by any other concurrent transactions. 事务识别数据时数据所处的状态,要么是另一并发事务修改它之前的状态,要么是第二个事务修改它之后的状态,事务不会识别中间状态的数据。A transaction either recognizes data in the state it was in before another concurrent transaction modified it, or it recognizes the data after the second transaction has completed, but it does not recognize an intermediate state. 这称为可串行性,因为它能够重新装载起始数据,并且重播一系列事务,以使数据结束时的状态与原始事务执行的状态相同。This is referred to as serializability because it results in the ability to reload the starting data and replay a series of transactions to end up with the data in the same state it was in after the original transactions were performed.

持久性Durability
完成完全持久的事务之后,它的影响将永久存在于系统中。After a fully durable transaction has completed, its effects are permanently in place in the system. 该修改即使出现系统故障也将一直保持。The modifications persist even in the event of a system failure. SQL Server 2014SQL Server 2014 和更高版本将启用延迟的持久事务。and later enable delayed durable transactions. 提交延迟的持久事务后,该事务日志记录将保留在磁盘上。Delayed durable transactions commit before the transaction log record is persisted to disk. 有关延迟事务持续性的详细信息,请参阅主题事务持续性For more information on delayed transaction durability see the topic Transaction Durability.

SQL 程序员要负责启动和结束事务,同时强制保持数据的逻辑一致性。SQL programmers are responsible for starting and ending transactions at points that enforce the logical consistency of the data. 程序员必须定义数据修改的顺序,使数据相对于其组织的业务规则保持一致。The programmer must define the sequence of data modifications that leave the data in a consistent state relative to the organization's business rules. 程序员将这些修改语句包括到一个事务中,使 SQL Server 数据库引擎SQL Server Database Engine 能够强制该事务的物理完整性。The programmer includes these modification statements in a single transaction so that the SQL Server 数据库引擎SQL Server Database Engine can enforce the physical integrity of the transaction.

企业数据库系统(如数据库引擎Database Engine实例)有责任提供一种机制,保证每个事务的物理完整性。It is the responsibility of an enterprise database system, such as an instance of the 数据库引擎Database Engine, to provide mechanisms ensuring the physical integrity of each transaction. 数据库引擎Database Engine提供:The 数据库引擎Database Engine provides:

  • 锁定设备,使事务保持隔离。Locking facilities that preserve transaction isolation.

  • 通过记录设备,保证事务持久性。Logging facilities ensure transaction durability. 对于完全持久的事务,在其提交之前,日志记录将强制写入磁盘。For fully durable transactions the log record is hardened to disk before the transactions commits. 因此,即使服务器硬件、操作系统或 数据库引擎Database Engine 实例自身出现故障,该实例也可以在重新启动时使用事务日志,将所有未完成的事务自动地回滚到系统出现故障的点。Thus, even if the server hardware, operating system, or the instance of the 数据库引擎Database Engine itself fails, the instance uses the transaction logs upon restart to automatically roll back any uncompleted transactions to the point of the system failure. 提交延迟的持久事务后,该事务日志记录将强制写入磁盘。Delayed durable transactions commit before the transaction log record is hardened to disk. 如果在日志记录强制写入磁盘前系统出现故障,此类事务可能会丢失。Such transactions may be lost if there is a system failure before the log record is hardened to disk. 有关延迟事务持续性的详细信息,请参阅主题事务持续性For more information on delayed transaction durability see the topic Transaction Durability.

  • 事务管理特性,强制保持事务的原子性和一致性。Transaction management features that enforce transaction atomicity and consistency. 事务启动之后,就必须成功完成(提交),否则数据库引擎Database Engine实例将撤消该事务启动之后对数据所做的所有修改。After a transaction has started, it must be successfully completed (committed), or the 数据库引擎Database Engine undoes all of the data modifications made since the transaction started. 此操作称为回滚事务,因为它将数据恢复到那些更改发生前的状态。This operation is referred to as rolling back a transaction because it returns the data to the state it was prior to those changes.

控制事务Controlling Transactions

应用程序主要通过指定事务启动和结束的时间来控制事务。Applications control transactions mainly by specifying when a transaction starts and ends. 可以使用 Transact-SQLTransact-SQL 语句或数据库应用程序编程接口 (API) 函数来指定这些时间。This can be specified by using either Transact-SQLTransact-SQL statements or database application programming interface (API) functions. 系统还必须能够正确处理那些在事务完成之前便终止事务的错误。The system must also be able to correctly handle errors that terminate a transaction before it completes. 有关详细信息,请参阅Transaction 语句(TRANSACT-SQL)ODBC 中的事务事务在 SQL Server Native Client (OLEDB).For more information, see Transaction Statements (Transact-SQL), Transactions in ODBC and Transactions in SQL Server Native Client (OLEDB).

默认情况下,事务按连接级别进行管理。By default, transactions are managed at the connection level. 在一个连接上启动一个事务后,该事务结束之前,在该连接上执行的所有 Transact-SQLTransact-SQL 语句都是该事务的一部分。When a transaction is started on a connection, all Transact-SQLTransact-SQL statements executed on that connection are part of the transaction until the transaction ends. 但是,在多个活动的结果集 (MARS) 会话中,Transact-SQLTransact-SQL 显式或隐式事务将变成批范围的事务,这种事务按批处理级别进行管理。However, under a multiple active result set (MARS) session, a Transact-SQLTransact-SQL explicit or implicit transaction becomes a batch-scoped transaction that is managed at the batch level. 当批处理完成时,如果批范围的事务还没有提交或回滚,SQL ServerSQL Server 将自动回滚该事务。When the batch completes, if the batch-scoped transaction is not committed or rolled back, it is automatically rolled back by SQL ServerSQL Server. 有关详细信息,请参阅多个活动结果集 (MARS) SQL Server 中For more information, see Multiple Active Result Sets (MARS) in SQL Server.

启动事务Starting Transactions

使用 API 函数和 Transact-SQLTransact-SQL 语句,可以在 SQL Server 数据库引擎SQL Server Database Engine实例中将事务作为显式、自动提交或隐式事务来启动。Using API functions and Transact-SQLTransact-SQL statements, you can start transactions in an instance of the SQL Server 数据库引擎SQL Server Database Engine as explicit, autocommit, or implicit transactions.

显式事务Explicit Transactions
显式事务是指这样的事务:您在其中通过 API 函数或发出 Transact-SQLTransact-SQL BEGIN TRANSACTION、COMMIT TRANSACTION、COMMIT WORK、ROLLBACK TRANSACTION 或 ROLLBACK WORK Transact-SQLTransact-SQL 语句明确定义事务的开始和结束。An explicit transaction is one in which you explicitly define both the start and end of the transaction through an API function or by issuing the Transact-SQLTransact-SQL BEGIN TRANSACTION, COMMIT TRANSACTION, COMMIT WORK, ROLLBACK TRANSACTION, or ROLLBACK WORK Transact-SQLTransact-SQL statements. 当事务结束时,连接将返回到启动显式事务前所处的事务模式,或者是隐式模式,或者是自动提交模式。When the transaction ends, the connection returns to the transaction mode it was in before the explicit transaction was started, either implicit or autocommit mode.

您可以使用显式事务中除下列语句之外的所有 Transact-SQLTransact-SQL 语句:You can use all Transact-SQLTransact-SQL statements in an explicit transaction, except for the following statements:

ALTER DATABASEALTER DATABASE CREATE DATABASECREATE DATABASE DROP FULLTEXT INDEXDROP FULLTEXT INDEX
ALTER FULLTEXT CATALOGALTER FULLTEXT CATALOG CREATE FULLTEXT CATALOGCREATE FULLTEXT CATALOG RECONFIGURERECONFIGURE
ALTER FULLTEXT INDEXALTER FULLTEXT INDEX CREATE FULLTEXT INDEXCREATE FULLTEXT INDEX RESTORERESTORE
BACKUPBACKUP DROP DATABASEDROP DATABASE 全文系统存储过程Full-text system stored procedures
CREATE DATABASECREATE DATABASE DROP FULLTEXT CATALOGDROP FULLTEXT CATALOG sp_dboption 用于设置数据库选项,或在显式事务或隐式事务内部修改 master 数据库的任何系统过程。 sp_dboption to set database options or any system procedure that modifies the master database inside explicit or implicit transactions.

备注

UPDATE STATISTICS 可在显式事务内使用。UPDATE STATISTICS can be used inside an explicit transaction. 但是,UPDATE STATISTICS 提交独立于封闭的事务,并且不能回滚。However, UPDATE STATISTICS commits independently of the enclosing transaction and cannot be rolled back.

自动提交事务Autocommit Transactions
自动提交模式是 SQL Server 数据库引擎的默认事务管理模式。Autocommit mode is the default transaction management mode of the SQL Server Database Engine. 每个 Transact-SQL 语句在完成时,都被提交或回滚。Every Transact-SQL statement is committed or rolled back when it completes. 如果一个语句成功地完成,则提交该语句;如果遇到错误,则回滚该语句。If a statement completes successfully, it is committed; if it encounters any error, it is rolled back. 只要没有显式事务或隐性事务覆盖自动提交模式,与数据库引擎实例的连接就以此默认模式操作。A connection to an instance of the Database Engine operates in autocommit mode whenever this default mode has not been overridden by either explicit or implicit transactions. 自动提交模式也是 ADO、OLE DB、ODBC 和 DB 库的默认模式。Autocommit mode is also the default mode for ADO, OLE DB, ODBC, and DB-Library.

隐式事务Implicit Transactions
当连接以隐式事务模式进行操作时,数据库引擎实例将在提交或回滚当前事务后自动启动新事务。When a connection is operating in implicit transaction mode, the instance of the Database Engine automatically starts a new transaction after the current transaction is committed or rolled back. 无须描述事务的开始,只需提交或回滚每个事务。You do nothing to delineate the start of a transaction; you only commit or roll back each transaction. 隐性事务模式生成连续的事务链。Implicit transaction mode generates a continuous chain of transactions. 通过 API 函数或 Transact-SQLTransact-SQL SET IMPLICIT_TRANSACTIONS ON 语句,将隐性事务模式设置为打开。Set implicit transaction mode on through either an API function or the Transact-SQLTransact-SQL SET IMPLICIT_TRANSACTIONS ON statement.

为连接将隐性事务模式设置为打开之后,当数据库引擎Database Engine实例首次执行下列任何语句时,都会自动启动一个事务:After implicit transaction mode has been set on for a connection, the instance of the 数据库引擎Database Engine automatically starts a transaction when it first executes any of these statements:

ALTER TABLEALTER TABLE FETCHFETCH REVOKEREVOKE
CREATECREATE GRANTGRANT SELECTSELECT
DELETEDELETE InsertINSERT TRUNCATE TABLETRUNCATE TABLE
DROPDROP OPENOPEN UPDATEUPDATE

批处理级事务Batch-scoped Transactions
只能应用于多个活动结果集 (MARS),在 MARS 会话中启动的 Transact-SQLTransact-SQL 显式或隐式事务变为批处理级事务。Applicable only to multiple active result sets (MARS), a Transact-SQLTransact-SQL explicit or implicit transaction that starts under a MARS session becomes a batch-scoped transaction. 当批处理完成时没有提交或回滚的批处理级事务自动由 SQL ServerSQL Server 进行回滚。A batch-scoped transaction that is not committed or rolled back when a batch completes is automatically rolled back by SQL ServerSQL Server.

分布式事务Distributed Transactions
分布式事务跨越两个或多个称为资源管理器的服务器。Distributed transactions span two or more servers known as resource managers. 称为事务管理器的服务器组件必须在资源管理器之间协调事务管理。The management of the transaction must be coordinated between the resource managers by a server component called a transaction manager. 如果分布式事务由 SQL Server 数据库引擎SQL Server Database Engine 分布式事务处理协调器 (MS DTC) 之类的事务管理器或其他支持 Open Group XA 分布式事务处理规范的事务管理器来协调,则在这样的分布式事务中,每个 MicrosoftMicrosoft实例都可以作为资源管理器来运行。Each instance of the SQL Server 数据库引擎SQL Server Database Engine can operate as a resource manager in distributed transactions coordinated by transaction managers, such as MicrosoftMicrosoft Distributed Transaction Coordinator (MS DTC), or other transaction managers that support the Open Group XA specification for distributed transaction processing. 有关详细信息,请参阅 MS DTC 文档。For more information, see the MS DTC documentation.

跨越两个或多个数据库的单个数据库引擎Database Engine实例中的事务实际上是分布式事务。A transaction within a single instance of the 数据库引擎Database Engine that spans two or more databases is actually a distributed transaction. 该实例对分布式事务进行内部管理;对于用户而言,其操作就像本地事务一样。The instance manages the distributed transaction internally; to the user, it operates as a local transaction.

对于应用程序而言,管理分布式事务很像管理本地事务。At the application, a distributed transaction is managed much the same as a local transaction. 当事务结束时,应用程序会请求提交或回滚事务。At the end of the transaction, the application requests the transaction to be either committed or rolled back. 不同的是,分布式提交必须由事务管理器管理,以尽量避免出现因网络故障而导致事务由某些资源管理器成功提交,但由另一些资源管理器回滚的情况。A distributed commit must be managed differently by the transaction manager to minimize the risk that a network failure may result in some resource managers successfully committing while others roll back the transaction. 通过分两个阶段(准备阶段和提交阶段)管理提交进程可避免这种情况,这称为两阶段提交 (2PC)。This is achieved by managing the commit process in two phases (the prepare phase and the commit phase), which is known as a two-phase commit (2PC).

准备阶段Prepare phase
当事务管理器收到提交请求时,它会向该事务涉及的所有资源管理器发送准备命令。When the transaction manager receives a commit request, it sends a prepare command to all of the resource managers involved in the transaction. 然后,每个资源管理器将尽力使该事务持久,并且所有保存该事务日志映像的缓冲区将被刷新到磁盘中。Each resource manager then does everything required to make the transaction durable, and all buffers holding log images for the transaction are flushed to disk. 当每个资源管理器完成准备阶段时,它会向事务管理器返回准备成功或准备失败的消息。As each resource manager completes the prepare phase, it returns success or failure of the prepare to the transaction manager. SQL Server 2014SQL Server 2014 引入了延迟的事务持续性。introduced delayed transaction durability. 在提交延迟的持久事务后,该事务的日志图像将刷入磁盘。Delayed durable transactions commit before log images for the transaction are flushed to disk. 有关延迟事务持续性的详细信息,请参阅主题事务持续性For more information on delayed transaction durability see the topic Transaction Durability.

提交阶段Commit phase
如果事务管理器从所有资源管理器收到准备成功的消息,它将向每个资源管理器发送一个提交命令。If the transaction manager receives successful prepares from all of the resource managers, it sends commit commands to each resource manager. 然后,资源管理器就可以完成提交。The resource managers can then complete the commit. 如果所有资源管理器都报告提交成功,那么事务管理器就会向应用程序发送一个成功通知。If all of the resource managers report a successful commit, the transaction manager then sends a success notification to the application. 如果任一资源管理器报告准备失败,那么事务管理器将向每个资源管理器发送一个回滚命令,并向应用程序表明提交失败。If any resource manager reported a failure to prepare, the transaction manager sends a rollback command to each resource manager and indicates the failure of the commit to the application.

数据库引擎Database Engine应用程序可以通过 Transact-SQLTransact-SQL 或数据库 API 来管理分布式事务。applications can manage distributed transactions either through Transact-SQLTransact-SQL or the database API. 有关详细信息,请参阅 BEGIN DISTRIBUTED TRANSACTION (Transact-SQL)For more information, see BEGIN DISTRIBUTED TRANSACTION (Transact-SQL).

结束事务Ending Transactions

您可以使用 COMMIT 或 ROLLBACK 语句,或者通过相应 API 函数来结束事务。You can end transactions with either a COMMIT or ROLLBACK statement, or through a corresponding API function.

COMMITCOMMIT
如果事务成功,则提交。If a transaction is successful, commit it. COMMIT 语句保证事务的所有修改在数据库中都永久有效。A COMMIT statement guarantees all of the transaction's modifications are made a permanent part of the database. COMMIT 语句还释放事务使用的资源(例如,锁)。A COMMIT also frees resources, such as locks, used by the transaction.

ROLLBACKROLLBACK
如果事务中出现错误,或用户决定取消事务,则回滚该事务。If an error occurs in a transaction, or if the user decides to cancel the transaction, then roll the transaction back. ROLLBACK 语句通过将数据返回到它在事务开始时所处的状态,来取消事务中的所有修改。A ROLLBACK statement backs out all modifications made in the transaction by returning the data to the state it was in at the start of the transaction. ROLLBACK 还释放事务占用的资源。A ROLLBACK also frees resources held by the transaction.

备注

在为支持多个活动的结果集 (MARS) 而建立的连接中,只要还有待执行的请求,就无法提交通过 API 函数启动的显式事务。Under connections enabled to support multiple active result sets (MARS), an explicit transaction started through an API function cannot be committed while there are pending requests for execution. 如果在未完成的操作还在运行时尝试提交此类事务,将导致出现错误。Any attempt to commit this type of transaction while there are outstanding operations running will result in an error.

事务处理过程中的错误Errors During Transaction Processing

如果某个错误使事务无法成功完成,SQL ServerSQL Server 会自动回滚该事务,并释放该事务占用的所有资源。If an error prevents the successful completion of a transaction, SQL ServerSQL Server automatically rolls back the transaction and frees all resources held by the transaction. 如果客户端与数据库引擎Database Engine实例的网络连接中断了,那么当网络向实例通知该中断后,该连接的所有未完成事务均会被回滚。If the client's network connection to an instance of the 数据库引擎Database Engine is broken, any outstanding transactions for the connection are rolled back when the network notifies the instance of the break. 如果客户端应用程序失败或客户端计算机崩溃或重新启动,也会中断连接,而且当网络向数据库引擎Database Engine实例通知该中断后,该实例会回滚所有未完成的连接。If the client application fails or if the client computer goes down or is restarted, this also breaks the connection, and the instance of the 数据库引擎Database Engine rolls back any outstanding connections when the network notifies it of the break. 如果客户端从该应用程序注销,所有未完成的事务也会被回滚。If the client logs off the application, any outstanding transactions are rolled back.

如果批中出现运行时语句错误(如违反约束),那么数据库引擎Database Engine中的默认行为是只回滚产生该错误的语句。If a run-time statement error (such as a constraint violation) occurs in a batch, the default behavior in the 数据库引擎Database Engine is to roll back only the statement that generated the error. 可以使用 SET XACT_ABORT 语句更改此行为。You can change this behavior using the SET XACT_ABORT statement. 在执行 SET XACT_ABORT ON 语句后,任何运行时语句错误都将导致自动回滚当前事务。After SET XACT_ABORT ON is executed, any run-time statement error causes an automatic rollback of the current transaction. 编译错误(如语法错误)不受 SET XACT_ABORT 的影响。Compile errors, such as syntax errors, are not affected by SET XACT_ABORT. 有关详细信息,请参阅 SET XACT_ABORT (Transact-SQL)For more information, see SET XACT_ABORT (Transact-SQL).

出现错误时,纠正操作(COMMIT 或 ROLLBACK)应包括在应用程序代码中。When errors occur, corrective action (COMMIT or ROLLBACK) should be included in application code. 处理错误,包括那些事务中的一种有效工具是Transact-SQLTransact-SQL尝试...捕获构造。One effective tool for handling errors, including those in transactions, is the Transact-SQLTransact-SQL TRY...CATCH construct. 有关包括事务的示例的详细信息,请参阅 TRY...CATCH (Transact-SQL)For more information with examples that include transactions, see TRY...CATCH (Transact-SQL). SQL Server 2012SQL Server 2012,可以使用 THROW 语句引发异常并传输到 TRY CATCH 块执行...捕获构造。Beginning with SQL Server 2012SQL Server 2012, you can use the THROW statement to raise an exception and transfers execution to a CATCH block of a TRY...CATCH construct. 有关详细信息,请参阅 THROW (Transact-SQL)For more information, see THROW (Transact-SQL).

自动提交模式下的编译和运行时错误Compile and Run-time Errors in Autocommit mode

在自动提交模式下,有时看起来好像数据库引擎Database Engine实例回滚了整个批处理而不是仅仅一个 SQL 语句。In autocommit mode, it sometimes appears as if an instance of the 数据库引擎Database Engine has rolled back an entire batch instead of just one SQL statement. 当遇到的错误是编译错误而非运行时错误时,会发生这种情况。This happens if the error encountered is a compile error, not a run-time error. 编译错误会阻止数据库引擎Database Engine生成执行计划,这样批处理中的任何语句都不会执行。A compile error prevents the 数据库引擎Database Engine from building an execution plan, so nothing in the batch is executed. 尽管看起来好像是回滚了产生错误的语句之前的所有语句,但该错误阻止了批处理中的所有语句的执行。Although it appears that all of the statements before the one generating the error were rolled back, the error prevented anything in the batch from being executed. 在下面的示例中,由于发生编译错误,第三个批处理中的 INSERT 语句都没有执行。In the following example, none of the INSERT statements in the third batch are executed because of a compile error. 但看起来好像是前两个 INSERT 语句没有执行便进行了回滚。It appears that the first two INSERT statements are rolled back when they are never executed.

CREATE TABLE TestBatch (Cola INT PRIMARY KEY, Colb CHAR(3));  
GO  
INSERT INTO TestBatch VALUES (1, 'aaa');  
INSERT INTO TestBatch VALUES (2, 'bbb');  
INSERT INTO TestBatch VALUSE (3, 'ccc');  -- Syntax error.  
GO  
SELECT * FROM TestBatch;  -- Returns no rows.  
GO  

在下面的示例中,第三个 INSERT 语句产生运行时重复主键错误。In the following example, the third INSERT statement generates a run-time duplicate primary key error. 由于前两个 INSERT 语句成功地执行并且提交,因此它们在运行时错误之后被保留下来。The first two INSERT statements are successful and committed, so they remain after the run-time error.

CREATE TABLE TestBatch (Cola INT PRIMARY KEY, Colb CHAR(3));  
GO  
INSERT INTO TestBatch VALUES (1, 'aaa');  
INSERT INTO TestBatch VALUES (2, 'bbb');  
INSERT INTO TestBatch VALUES (1, 'ccc');  -- Duplicate key error.  
GO  
SELECT * FROM TestBatch;  -- Returns rows 1 and 2.  
GO  

数据库引擎Database Engine使用延迟的名称解析,直到执行时才解析对象名称。The 数据库引擎Database Engine uses deferred name resolution, in which object names are not resolved until execution time. 在下面的示例中,执行并提交了前两个 INSERT 语句,在第三个 TestBatch 语句由于引用一个不存在的表而产生运行时错误之后,这两行仍然保留在 INSERT 表中。In the following example, the first two INSERT statements are executed and committed, and those two rows remain in the TestBatch table after the third INSERT statement generates a run-time error by referring to a table that does not exist.

CREATE TABLE TestBatch (Cola INT PRIMARY KEY, Colb CHAR(3));  
GO  
INSERT INTO TestBatch VALUES (1, 'aaa');  
INSERT INTO TestBatch VALUES (2, 'bbb');  
INSERT INTO TestBch VALUES (3, 'ccc');  -- Table name error.  
GO  
SELECT * FROM TestBatch;  -- Returns rows 1 and 2.  
GO  

使用顶部的链接的箭头图标中此指南Arrow icon used with Back to Top link In This Guide

锁定和行版本控制基本知识Locking and Row Versioning Basics

当多个用户同时访问数据时,SQL Server 数据库引擎SQL Server Database Engine使用以下机制确保事务的完整性和保持数据库的一致性:The SQL Server 数据库引擎SQL Server Database Engine uses the following mechanisms to ensure the integrity of transactions and maintain the consistency of databases when multiple users are accessing data at the same time:

  • 锁定Locking

    每个事务对所依赖的资源(如行、页或表)请求不同类型的锁。Each transaction requests locks of different types on the resources, such as rows, pages, or tables, on which the transaction is dependent. 锁可以阻止其他事务以某种可能会导致事务请求锁出错的方式修改资源。The locks block other transactions from modifying the resources in a way that would cause problems for the transaction requesting the lock. 当事务不再依赖锁定的资源时,它将释放锁。Each transaction frees its locks when it no longer has a dependency on the locked resources.

  • 行版本控制Row versioning

    当启用了基于行版本控制的隔离级别时,数据库引擎Database Engine将维护修改的每一行的版本。When a row versioning-based isolation level is enabled, the 数据库引擎Database Engine maintains versions of each row that is modified. 应用程序可以指定事务使用行版本查看事务或查询开始时存在的数据,而不是使用锁保护所有读取。Applications can specify that a transaction use the row versions to view data as it existed at the start of the transaction or query instead of protecting all reads with locks. 通过使用行版本控制,读取操作阻止其他事务的可能性将大大降低。By using row versioning, the chance that a read operation will block other transactions is greatly reduced.

锁定和行版本控制可以防止用户读取未提交的数据,还可以防止多个用户尝试同时更改同一数据。Locking and row versioning prevent users from reading uncommitted data and prevent multiple users from attempting to change the same data at the same time. 如果不进行锁定或行版本控制,对数据执行的查询可能会返回数据库中尚未提交的数据,从而产生意外的结果。Without locking or row versioning, queries executed against that data could produce unexpected results by returning data that has not yet been committed in the database.

应用程序可以选择事务隔离级别,为事务定义保护级别,以防被其他事务所修改。Applications can choose transaction isolation levels, which define the level of protection for the transaction from modifications made by other transactions. 可以为各个 Transact-SQLTransact-SQL 语句指定表级别的提示,进一步定制行为以满足应用程序的要求。Table-level hints can be specified for individual Transact-SQLTransact-SQL statements to further tailor behavior to fit the requirements of the application.

管理并发数据访问Managing Concurrent Data Access

同时访问一种资源的用户被视为并发访问资源。Users who access a resource at the same time are said to be accessing the resource concurrently. 并发数据访问需要某些机制,以防止多个用户试图修改其他用户正在使用的资源时产生负面影响。Concurrent data access requires mechanisms to prevent adverse effects when multiple users try to modify resources that other users are actively using.

并发影响Concurrency Effects

修改数据的用户会影响同时读取或修改相同数据的其他用户。Users modifying data can affect other users who are reading or modifying the same data at the same time. 即这些用户可以并发访问数据。These users are said to be accessing the data concurrently. 如果数据存储系统没有并发控制,则用户可能会看到以下负面影响:If a data storage system has no concurrency control, users could see the following side effects:

  • 丢失更新Lost updates

    当两个或多个事务选择同一行,然后基于最初选定的值更新该行时,会发生丢失更新问题。Lost updates occur when two or more transactions select the same row and then update the row based on the value originally selected. 每个事务都不知道其他事务的存在。Each transaction is unaware of the other transactions. 最后的更新将覆盖由其他事务所做的更新,这将导致数据丢失。The last update overwrites updates made by the other transactions, which results in lost data.

    例如,两个编辑人员制作了同一文档的电子副本。For example, two editors make an electronic copy of the same document. 每个编辑人员独立地更改其副本,然后保存更改后的副本,这样就覆盖了原始文档。Each editor changes the copy independently and then saves the changed copy thereby overwriting the original document. 最后保存其更改副本的编辑人员覆盖另一个编辑人员所做的更改。The editor who saves the changed copy last overwrites the changes made by the other editor. 如果在一个编辑人员完成并提交事务之前,另一个编辑人员不能访问同一文件,则可避免此问题。This problem could be avoided if one editor could not access the file until the other editor had finished and committed the transaction.

  • 未提交的依赖关系(脏读)Uncommitted dependency (dirty read)

    当第二个事务选择其他事务正在更新的行时,会发生未提交的依赖关系问题。Uncommitted dependency occurs when a second transaction selects a row that is being updated by another transaction. 第二个事务正在读取的数据还没有提交并且可能由更新此行的事务所更改。The second transaction is reading data that has not been committed yet and may be changed by the transaction updating the row.

    例如,一个编辑人员正在更改电子文档。For example, an editor is making changes to an electronic document. 在更改过程中,另一个编辑人员复制了该文档(该副本包含到目前为止所做的全部更改)并将其分发给预期的用户。During the changes, a second editor takes a copy of the document that includes all the changes made so far, and distributes the document to the intended audience. 此后,第一个编辑人员认为目前所做的更改是错误的,于是删除了所做的编辑并保存了文档。The first editor then decides the changes made so far are wrong and removes the edits and saves the document. 分发给用户的文档包含不再存在的编辑内容,并且这些编辑内容应视为从未存在过。The distributed document contains edits that no longer exist and should be treated as if they never existed. 如果在第一个编辑人员保存最终更改并提交事务之前,任何人都不能读取更改的文档,则可以避免此问题。This problem could be avoided if no one could read the changed document until the first editor does the final save of modifications and commits the transaction.

  • 不一致的分析(不可重复读)Inconsistent analysis (nonrepeatable read)

    当第二个事务多次访问同一行而且每次读取不同的数据时,会发生不一致的分析问题。Inconsistent analysis occurs when a second transaction accesses the same row several times and reads different data each time. 不一致的分析与未提交的依赖关系类似,因为其他事务也是正在更改第二个事务正在读取的数据。Inconsistent analysis is similar to uncommitted dependency in that another transaction is changing the data that a second transaction is reading. 但是,在不一致的分析中,第二个事务读取的数据是由已进行了更改的事务提交的。However, in inconsistent analysis, the data read by the second transaction was committed by the transaction that made the change. 此外,不一致的分析涉及多次(两次或更多)读取同一行,而且每次信息都被其他事务更改,因此我们称之为“不可重复读”。Also, inconsistent analysis involves multiple reads (two or more) of the same row, and each time the information is changed by another transaction; thus, the term nonrepeatable read.

    例如,编辑人员两次读取同一文档,但在两次读取之间,作者重写了该文档。For example, an editor reads the same document twice, but between each reading the writer rewrites the document. 当编辑人员第二次读取文档时,文档已更改。When the editor reads the document for the second time, it has changed. 原始读取不可重复。The original read was not repeatable. 如果在编辑人员完成最后一次读取文档之前,作者不能更改文档,则可以避免此问题。This problem could be avoided if the writer could not change the document until the editor has finished reading it for the last time.

  • 虚拟读取Phantom reads

    执行两个相同的查询但第二个查询返回的行集合是不同的,此时就会发生虚拟读取。A phantom read is a situation that occurs when two identical queries are executed and the collection of rows returned by the second query is different. 下面的示例显示了何时会出现幻读。The example below shows how this may occur. 假定下面两个事务同时执行。Assume the two transactions below are executing at the same time. 由于第二个事务中的 INSERT 语句更改了两个事务所用的数据,所以第一个事务中的两个 SELECT 语句可能返回不同的结果。The two SELECT statements in the first transaction may return different results because the INSERT statement in the second transaction changes the data used by both.

    --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  
       SET name = 'New' WHERE ID = 5;  
    COMMIT;   
    
  • 由于行更新导致读取缺失和重复读Missing and double reads caused by row updates

    • 缺失一个更新行或多次看到某更新行Missing a updated row or seeing an updated row multiple times

      在 READ UNCOMMITTED 级别运行的事务不会发出共享锁来防止其他事务修改当前事务读取的数据。Transactions that are running at the READ UNCOMMITTED level do not issue shared locks to prevent other transactions from modifying data read by the current transaction. 在 READ COMMITTED 级别运行的事务会发出共享锁,但是在读取行后会释放行锁或页锁。Transactions that are running at the READ COMMITTED level do issue shared locks, but the row or page locks are released after the row is read. 无论哪种情况,在您扫描索引时,如果另一个用户在您读取期间更改行的索引键列,则在键更改将行移至您的扫描位置之前的位置时,该行可能会再次出现。In either case, when you are scanning an index, if another user changes the index key column of the row during your read, the row might appear again if the key change moved the row to a position ahead of your scan. 同样,在键更改将行移至您已读取的索引中的某位置时,该行将不会出现。Similarly, the row might not appear if the key change moved the row to a position in the index that you had already read. 若要避免这种情况,请使用 SERIALIZABLE 或 HOLDLOCK 提示,或者使用行版本控制。To avoid this, use the SERIALIZABLE or HOLDLOCK hint, or row versioning. 有关详细信息,请参阅表提示 (Transact-SQL)For more information, see Table Hints (Transact-SQL).

    • 缺失非更新目标的一行或多行Missing one or more rows that were not the target of update

      当您使用 READ UNCOMMITTED,如果您的查询读取行使用分配顺序扫描 (使用 IAM 页) 时,可能会缺失行,如果其他事务导致页拆分。When you are using READ UNCOMMITTED, if your query reads rows using an allocation order scan (using IAM pages), you might miss rows if another transaction is causing a page split. 当使用已提交的读取时不会发生这种情况,因为在页拆分期间将会保持表锁;当表没有聚集索引时也不会发生这种情况,因为更新不会导致页拆分。This cannot occur when you are using read committed because a table lock is held during a page split and does not happen if the table does not have a clustered index, because updates do not cause page splits.

并发类型Types of Concurrency

当许多人试图同时修改数据库中的数据时,必须实现一个控制系统,使一个人所做的修改不会对他人所做的修改产生负面影响。When many people attempt to modify data in a database at the same time, a system of controls must be implemented so that modifications made by one person do not adversely affect those of another person. 这称为并发控制。This is called concurrency control.

并发控制理论根据建立并发控制的方法而分为两类:Concurrency control theory has two classifications for the methods of instituting concurrency control:

  • 悲观并发控制Pessimistic concurrency control

    一个锁定系统,可以阻止用户以影响其他用户的方式修改数据。A system of locks prevents users from modifying data in a way that affects other users. 如果用户执行的操作导致应用了某个锁,只有这个锁的所有者释放该锁,其他用户才能执行与该锁冲突的操作。After a user performs an action that causes a lock to be applied, other users cannot perform actions that would conflict with the lock until the owner releases it. 这种方法之所以称为悲观并发控制,是因为它主要用于数据争用激烈的环境中,以及发生并发冲突时用锁保护数据的成本低于回滚事务的成本的环境中。This is called pessimistic control because it is mainly used in environments where there is high contention for data, where the cost of protecting data with locks is less than the cost of rolling back transactions if concurrency conflicts occur.

  • 乐观并发控制Optimistic concurrency control

    在乐观并发控制中,用户读取数据时不锁定数据。In optimistic concurrency control, users do not lock data when they read it. 当一个用户更新数据时,系统将进行检查,查看该用户读取数据后其他用户是否又更改了该数据。When a user updates data, the system checks to see if another user changed the data after it was read. 如果其他用户更新了数据,将产生一个错误。If another user updated the data, an error is raised. 一般情况下,收到错误信息的用户将回滚事务并重新开始。Typically, the user receiving the error rolls back the transaction and starts over. 这种方法之所以称为乐观并发控制,是由于它主要在以下环境中使用:数据争用不大且偶尔回滚事务的成本低于读取数据时锁定数据的成本。This is called optimistic because it is mainly used in environments where there is low contention for data, and where the cost of occasionally rolling back a transaction is lower than the cost of locking data when read.

SQL ServerSQL Server 支持一定范围的并发控制。supports a range of concurrency control. 用户通过为游标上的连接或并发选项选择事务隔离级别来指定并发控制的类型。Users specify the type of concurrency control by selecting transaction isolation levels for connections or concurrency options on cursors. 这些特性可以使用 Transact-SQLTransact-SQL 语句或通过数据库应用程序编程接口(API,如 ADO、ADO.NET、OLE DB 和 ODBC)的属性和特性来定义。These attributes can be defined using Transact-SQLTransact-SQL statements, or through the properties and attributes of database application programming interfaces (APIs) such as ADO, ADO.NET, OLE DB, and ODBC.

数据库引擎中的隔离级别Isolation Levels in the Database Engine

事务指定一个隔离级别,该隔离级别定义一个事务必须与由其他事务进行的资源或数据更改相隔离的程度。Transactions specify an isolation level that defines the degree to which one transaction must be isolated from resource or data modifications made by other transactions. 隔离级别从允许的并发副作用(例如,脏读或虚拟读取)的角度进行描述。Isolation levels are described in terms of which concurrency side-effects, such as dirty reads or phantom reads, are allowed.

事务隔离级别控制:Transaction isolation levels control:

  • 读取数据时是否占用锁以及所请求的锁类型。Whether locks are taken when data is read, and what type of locks are requested.

  • 占用读取锁的时间。How long the read locks are held.

  • 引用其他事务修改的行的读取操作是否:Whether a read operation referencing rows modified by another transaction:

    • 在该行上的排他锁被释放之前阻塞其他事务。Blocks until the exclusive lock on the row is freed.

    • 检索在启动语句或事务时存在的行的已提交版本。Retrieves the committed version of the row that existed at the time the statement or transaction started.

    • 读取未提交的数据修改。Reads the uncommitted data modification.

重要

选择事务隔离级别不影响为保护数据修改而获取的锁。Choosing a transaction isolation level does not affect the locks acquired to protect data modifications. 事务总是在其修改的任何数据上获取排他锁并在事务完成之前持有该锁,不管为该事务设置了什么样的隔离级别。A transaction always gets an exclusive lock on any data it modifies, and holds that lock until the transaction completes, regardless of the isolation level set for that transaction. 对于读取操作,事务隔离级别主要定义保护级别,以防受到其他事务所做更改的影响。For read operations, transaction isolation levels primarily define the level of protection from the effects of modifications made by other transactions.

较低的隔离级别可以增强许多用户同时访问数据的能力,但也增加了用户可能遇到的并发副作用(例如脏读或丢失更新)的数量。A lower isolation level increases the ability of many users to access data at the same time, but increases the number of concurrency effects (such as dirty reads or lost updates) users might encounter. 相反,较高的隔离级别减少了用户可能遇到的并发副作用的类型,但需要更多的系统资源,并增加了一个事务阻塞其他事务的可能性。Conversely, a higher isolation level reduces the types of concurrency effects that users may encounter, but requires more system resources and increases the chances that one transaction will block another. 应平衡应用程序的数据完整性要求与每个隔离级别的开销,在此基础上选择相应的隔离级别。Choosing the appropriate isolation level depends on balancing the data integrity requirements of the application against the overhead of each isolation level. 最高隔离级别(可序列化)保证事务在每次重复读取操作时都能准确检索到相同的数据,但需要通过执行某种级别的锁定来完成此操作,而锁定可能会影响多用户系统中的其他用户。The highest isolation level, serializable, guarantees that a transaction will retrieve exactly the same data every time it repeats a read operation, but it does this by performing a level of locking that is likely to impact other users in multi-user systems. 最低隔离级别(未提交读)可以检索其他事务已经修改、但未提交的数据。The lowest isolation level, read uncommitted, may retrieve data that has been modified but not committed by other transactions. 在未提交读中,所有并发副作用都可能发生,但因为没有读取锁定或版本控制,所以开销最少。All of the concurrency side effects can happen in read uncommitted, but there is no read locking or versioning, so overhead is minimized.

数据库引擎隔离级别Database Engine Isolation Levels

ISO 标准定义了下列隔离级别,SQL Server 数据库引擎SQL Server Database Engine支持所有这些隔离级别:The ISO standard defines the following isolation levels, all of which are supported by the SQL Server 数据库引擎SQL Server Database Engine:

隔离级别Isolation Level 定义Definition
未提交读Read uncommitted 隔离事务的最低级别,只能保证不读取物理上损坏的数据。The lowest isolation level where transactions are isolated only enough to ensure that physically corrupt data is not read. 在此级别上,允许脏读,因此一个事务可能看见其他事务所做的尚未提交的更改。In this level, dirty reads are allowed, so one transaction may see not-yet-committed changes made by other transactions.
已提交读Read committed 允许事务读取另一个事务以前读取(未修改)的数据,而不必等待第一个事务完成。Allows a transaction to read data previously read (not modified) by another transaction without waiting for the first transaction to complete. 数据库引擎Database Engine保留写锁(在所选数据上获取)直到事务结束,但是一执行 SELECT 操作就释放读锁。The 数据库引擎Database Engine keeps write locks (acquired on selected data) until the end of the transaction, but read locks are released as soon as the SELECT operation is performed. 这是数据库引擎Database Engine默认级别。This is the 数据库引擎Database Engine default level.
可重复读Repeatable read 数据库引擎Database Engine保留在所选数据上获取的读锁和写锁,直到事务结束。The 数据库引擎Database Engine keeps read and write locks that are acquired on selected data until the end of the transaction. 但是,因为不管理范围锁,可能发生虚拟读取。However, because range-locks are not managed, phantom reads can occur.
可序列化Serializable 隔离事务的最高级别,事务之间完全隔离。The highest level where transactions are completely isolated from one another. 数据库引擎Database Engine保留在所选数据上获取的读锁和写锁,在事务结束时释放它们。The 数据库引擎Database Engine keeps read and write locks acquired on selected data to be released at the end of the transaction. SELECT 操作使用分范围的 WHERE 子句时获取范围锁,主要为了避免虚拟读取。Range-locks are acquired when a SELECT operation uses a ranged WHERE clause, especially to avoid phantom reads.

注意:请求可序列化隔离级别时,DDL 操作和事务复制的表上可能会失败。Note: DDL operations and transactions on replicated tables may fail when serializable isolation level is requested. 这是因为复制查询使用的提示可能与可序列化隔离级别不兼容。This is because replication queries use hints that may be incompatible with serializable isolation level.

SQL ServerSQL Server 还支持使用行版本控制的其他两个事务隔离级别。also supports two additional transaction isolation levels that use row versioning. 一个是已提交读隔离的实现,另一个是事务隔离级别(快照)。One is an implementation of read committed isolation, and one is a transaction isolation level, snapshot.

行版本控制隔离级别Row Versioning Isolation Level 定义Definition
已提交读快照Read Committed Snapshot 当 READ_COMMITTED_SNAPSHOT 数据库选项设置为 ON 时,已提交读隔离使用行版本控制提供语句级读取一致性。When the READ_COMMITTED_SNAPSHOT database option is set ON, read committed isolation uses row versioning to provide statement-level read consistency. 读取操作只需要 SCH-S 表级别的锁,不需要页锁或行锁。Read operations require only SCH-S table level locks and no page or row locks. 即,数据库引擎使用行版本控制为每个语句提供一个在事务上一致的数据快照,因为该数据在语句开始时就存在。That is, the Database Engine uses row versioning to present each statement with a transactionally consistent snapshot of the data as it existed at the start of the statement. 不使用锁来防止其他事务更新数据。Locks are not used to protect the data from updates by other transactions. 用户定义的函数可以返回在包含 UDF 的语句开始后提交的数据。A user-defined function can return data that was committed after the time the statement containing the UDF began.

如果 READ_COMMITTED_SNAPSHOT 数据库选项设置为 OFF(这是默认设置),当当前事务在执行读操作时,已提交读隔离使用共享锁来防止其他事务修改行。When the READ_COMMITTED_SNAPSHOT database option is set OFF, which is the default setting, read committed isolation uses shared locks to prevent other transactions from modifying rows while the current transaction is running a read operation. 共享锁还会阻止语句在其他事务完成之前读取由这些事务修改的行。The shared locks also block the statement from reading rows modified by other transactions until the other transaction is completed. 两个实现都满足已提交读隔离的 ISO 定义。Both implementations meet the ISO definition of read committed isolation.
快照Snapshot 快照隔离级别使用行版本控制来提供事务级别的读取一致性。The snapshot isolation level uses row versioning to provide transaction-level read consistency. 读取操作不获取页锁或行锁,只获取 SCH-S 表锁。Read operations acquire no page or row locks; only SCH-S table locks are acquired. 读取其他事务修改的行时,读取操作将检索启动事务时存在的行的版本。When reading rows modified by another transaction, they retrieve the version of the row that existed when the transaction started. 当 ALLOW_SNAPSHOT_ISOLATION 数据库选项设置为 ON 时,只能对数据库使用快照隔离。You can only use Snapshot isolation against a database when the ALLOW_SNAPSHOT_ISOLATION database option is set ON. 默认情况下,用户数据库的此选项设置为 OFF。By default, this option is set OFF for user databases.

注意:SQL ServerSQL Server 不支持元数据的版本控制。Note: SQL ServerSQL Server does not support versioning of metadata. 因此,对于在快照隔离下运行的显式事务中可以执行的 DDL 操作存在限制。For this reason, there are restrictions on what DDL operations can be performed in an explicit transaction that is running under snapshot isolation. 以下 DDL 语句不允许 BEGIN TRANSACTION 语句之后的快照隔离下:ALTER TABLE、 CREATE INDEX、 CREATE XML INDEX、 ALTER INDEX、 DROP INDEX、 DBCC REINDEX、 ALTER PARTITION FUNCTION、 ALTER PARTITION SCHEME 或任何公共语言运行时 (CLR) DDL 语句。The following DDL statements are not permitted under snapshot isolation after a BEGIN TRANSACTION statement: ALTER TABLE, CREATE INDEX, CREATE XML INDEX, ALTER INDEX, DROP INDEX, DBCC REINDEX, ALTER PARTITION FUNCTION, ALTER PARTITION SCHEME, or any common language runtime (CLR) DDL statement. 在隐式事务中使用快照隔离时允许使用这些语句。These statements are permitted when you are using snapshot isolation within implicit transactions. 根据定义,隐式事务为单个语句,这使得它可以强制应用快照隔离的语义,即便使用 DDL 语句也是如此。An implicit transaction, by definition, is a single statement that makes it possible to enforce the semantics of snapshot isolation, even with DDL statements. 违反此原则会导致错误 3961:"快照隔离事务失败,在数据库 ' %.* ls' 因为该语句所访问的对象是否已被修改此事务开始以来的其他并发事务中的 DDL 语句。Violations of this principle can cause error 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."

下表显示了不同隔离级别导致的并发副作用。The following table shows the concurrency side effects enabled by the different isolation levels.

隔离级别Isolation level 脏读Dirty read 不可重复读Nonrepeatable read 虚拟读取Phantom
未提交的读取Read uncommitted Yes Yes Yes
已提交的读取Read committed No Yes Yes
可重复的读取Repeatable read No No Yes
快照Snapshot No No No
可序列化Serializable No No No

有关每个事务隔离级别控制的特定类型的锁定或行版本控制的详细信息,请参阅 SET TRANSACTION ISOLATION LEVEL (Transact-SQL)For more information about the specific types of locking or row versioning controlled by each transaction isolation level, see SET TRANSACTION ISOLATION LEVEL (Transact-SQL).

可以使用 Transact-SQLTransact-SQL 或通过数据库 API 来设置事务隔离级别。Transaction isolation levels can be set using Transact-SQLTransact-SQL or through a database API.

Transact-SQLTransact-SQL

Transact-SQLTransact-SQL 脚本使用 SET TRANSACTION ISOLATION LEVEL 语句。scripts use the SET TRANSACTION ISOLATION LEVEL statement.

ADOADO
ADO 应用程序将 Connection 对象的 IsolationLevel 属性设置为 adXactReadUncommitted、adXactReadCommitted、adXactRepeatableRead 或 adXactReadSerializable。ADO applications set the IsolationLevel property of the Connection object to adXactReadUncommitted, adXactReadCommitted, adXactRepeatableRead, or adXactReadSerializable.

ADO.NETADO.NET
使用 System.Data.SqlClient 管理命名空间的 ADO.NET 应用程序可以调用 SqlConnection.BeginTransaction 方法,并将 IsolationLevel 选项设置为 Unspecified、Chaos、ReadUncommitted、ReadCommitted、RepeatableRead、Serializable 和 Snapshot。ADO.NET applications using the System.Data.SqlClient managed namespace can call the SqlConnection.BeginTransaction method and set the IsolationLevel option to Unspecified, Chaos, ReadUncommitted, ReadCommitted, RepeatableRead, Serializable, and Snapshot.

OLE DBOLE DB
开始事务时,使用 OLE DB 的应用程序调用 ITransactionLocal::StartTransaction,其中 isoLevel 设置为 ISOLATIONLEVEL_READUNCOMMITTED、ISOLATIONLEVEL_READCOMMITTED、ISOLATIONLEVEL_REPEATABLEREAD、ISOLATIONLEVEL_SNAPSHOT 或 ISOLATIONLEVEL_SERIALIZABLE。When starting a transaction, applications using OLE DB call ITransactionLocal::StartTransaction with isoLevel set to ISOLATIONLEVEL_READUNCOMMITTED, ISOLATIONLEVEL_READCOMMITTED, ISOLATIONLEVEL_REPEATABLEREAD, ISOLATIONLEVEL_SNAPSHOT, or ISOLATIONLEVEL_SERIALIZABLE.

在自动提交模式下指定事务隔离级别时,OLE DB 应用程序可以将 DBPROPSET_SESSION 属性 DBPROP_SESS_AUTOCOMMITISOLEVELS 设置为 DBPROPVAL_TI_CHAOS、DBPROPVAL_TI_READUNCOMMITTED、DBPROPVAL_TI_BROWSE、DBPROPVAL_TI_CURSORSTABILITY、DBPROPVAL_TI_READCOMMITTED、DBPROPVAL_TI_REPEATABLEREAD、DBPROPVAL_TI_SERIALIZABLE、DBPROPVAL_TI_ISOLATED 或 DBPROPVAL_TI_SNAPSHOT。When specifying the transaction isolation level in autocommit mode, OLE DB applications can set the DBPROPSET_SESSION property DBPROP_SESS_AUTOCOMMITISOLEVELS to DBPROPVAL_TI_CHAOS, DBPROPVAL_TI_READUNCOMMITTED, DBPROPVAL_TI_BROWSE, DBPROPVAL_TI_CURSORSTABILITY, DBPROPVAL_TI_READCOMMITTED, DBPROPVAL_TI_REPEATABLEREAD, DBPROPVAL_TI_SERIALIZABLE, DBPROPVAL_TI_ISOLATED, or DBPROPVAL_TI_SNAPSHOT.

ODBCODBC
ODBC 应用程序调用 SQLSetConnectAttr,其中 Attribute 设置为 SQL_ATTR_TXN_ISOLATION,ValuePtr 设置为 SQL_TXN_READ_UNCOMMITTED、SQL_TXN_READ_COMMITTED、SQL_TXN_REPEATABLE_READ 或 SQL_TXN_SERIALIZABLE。ODBC applications call SQLSetConnectAttr with Attribute set to SQL_ATTR_TXN_ISOLATION and ValuePtr set to SQL_TXN_READ_UNCOMMITTED, SQL_TXN_READ_COMMITTED, SQL_TXN_REPEATABLE_READ, or SQL_TXN_SERIALIZABLE.

对于快照事务,应用程序调用 SQLSetConnectAttr,其中 Attribute 设置为 SQL_COPT_SS_TXN_ISOLATION,ValuePtr 设置为 SQL_TXN_SS_SNAPSHOT。For snapshot transactions, applications call SQLSetConnectAttr with Attribute set to SQL_COPT_SS_TXN_ISOLATION and ValuePtr set to SQL_TXN_SS_SNAPSHOT. 可以使用 SQL_COPT_SS_TXN_ISOLATION 或 SQL_ATTR_TXN_ISOLATION 检索快照事务。A snapshot transaction can be retrieved using either SQL_COPT_SS_TXN_ISOLATION or SQL_ATTR_TXN_ISOLATION.

使用顶部的链接的箭头图标中此指南Arrow icon used with Back to Top link In This Guide

数据库引擎中的锁定Locking in the Database Engine

锁定是 SQL Server 数据库引擎SQL Server Database Engine用来同步多个用户同时对同一个数据块的访问的一种机制。Locking is a mechanism used by the SQL Server 数据库引擎SQL Server Database Engine to synchronize access by multiple users to the same piece of data at the same time.

在事务获取数据块当前状态的依赖关系(比如通过读取或修改数据)之前,它必须保护自己不受其他事务对同一数据进行修改的影响。Before a transaction acquires a dependency on the current state of a piece of data, such as by reading or modifying the data, it must protect itself from the effects of another transaction modifying the same data. 事务通过请求锁定数据块来达到此目的。The transaction does this by requesting a lock on the piece of data. 锁有多种模式,如共享或排他。Locks have different modes, such as shared or exclusive. 锁模式定义了事务对数据所拥有的依赖关系级别。The lock mode defines the level of dependency the transaction has on the data. 如果某个事务已获得特定数据的锁,则其他事务不能获得会与该锁模式发生冲突的锁。No transaction can be granted a lock that would conflict with the mode of a lock already granted on that data to another transaction. 如果事务请求的锁模式与已授予同一数据的锁发生冲突,则数据库引擎Database Engine实例将暂停事务请求直到第一个锁释放。If a transaction requests a lock mode that conflicts with a lock that has already been granted on the same data, the instance of the 数据库引擎Database Engine will pause the requesting transaction until the first lock is released.

当事务修改某个数据块时,它将持有保护所做修改的锁直到事务结束。When a transaction modifies a piece of data, it holds the lock protecting the modification until the end of the transaction. 事务持有(所获取的用来保护读取操作的)锁的时间长度,取决于事务隔离级别设置。How long a transaction holds the locks acquired to protect read operations depends on the transaction isolation level setting. 一个事务持有的所有锁都在事务完成(无论是提交还是回滚)时释放。All locks held by a transaction are released when the transaction completes (either commits or rolls back).

应用程序一般不直接请求锁。Applications do not typically request locks directly. 锁由数据库引擎Database Engine的一个部件(称为“锁管理器”)在内部管理。Locks are managed internally by a part of the 数据库引擎Database Engine called the lock manager. 数据库引擎Database Engine实例处理 Transact-SQLTransact-SQL 语句时,数据库引擎Database Engine查询处理器会决定将要访问哪些资源。When an instance of the 数据库引擎Database Engine processes a Transact-SQLTransact-SQL statement, the 数据库引擎Database Engine query processor determines which resources are to be accessed. 查询处理器根据访问类型和事务隔离级别设置来确定保护每一资源所需的锁的类型。The query processor determines what types of locks are required to protect each resource based on the type of access and the transaction isolation level setting. 然后,查询处理器将向锁管理器请求适当的锁。The query processor then requests the appropriate locks from the lock manager. 如果与其他事务所持有的锁不会发生冲突,锁管理器将授予该锁。The lock manager grants the locks if there are no conflicting locks held by other transactions.

锁粒度和层次结构Lock Granularity and Hierarchies

SQL Server 数据库引擎SQL Server Database Engine具有多粒度锁定,允许一个事务锁定不同类型的资源。The SQL Server 数据库引擎SQL Server Database Engine has multigranular locking that allows different types of resources to be locked by a transaction. 为了尽量减少锁定的开销,数据库引擎Database Engine自动将资源锁定在适合任务的级别。To minimize the cost of locking, the 数据库引擎Database Engine locks resources automatically at a level appropriate to the task. 锁定在较小的粒度(例如行)可以提高并发度,但开销较高,因为如果锁定了许多行,则需要持有更多的锁。Locking at a smaller granularity, such as rows, increases concurrency but has a higher overhead because more locks must be held if many rows are locked. 锁定在较大的粒度(例如表)会降低了并发度,因为锁定整个表限制了其他事务对表中任意部分的访问。Locking at a larger granularity, such as tables, are expensive in terms of concurrency because locking an entire table restricts access to any part of the table by other transactions. 但其开销较低,因为需要维护的锁较少。However, it has a lower overhead because fewer locks are being maintained.

数据库引擎Database Engine通常必须获取多粒度级别上的锁才能完整地保护资源。The 数据库引擎Database Engine often has to acquire locks at multiple levels of granularity to fully protect a resource. 这组多粒度级别上的锁称为锁层次结构。This group of locks at multiple levels of granularity is called a lock hierarchy. 例如,为了完整地保护对索引的读取,数据库引擎Database Engine实例可能必须获取行上的共享锁以及页和表上的意向共享锁。For example, to fully protect a read of an index, an instance of the 数据库引擎Database Engine may have to acquire share locks on rows and intent share locks on the pages and table.

下表列出了数据库引擎Database Engine可以锁定的资源。The following table shows the resources that the 数据库引擎Database Engine can lock.

资源Resource DescriptionDescription
RIDRID 用于锁定堆中的单个行的行标识符。A row identifier used to lock a single row within a heap.
KEYKEY 索引中用于保护可序列化事务中的键范围的行锁。A row lock within an index used to protect key ranges in serializable transactions.
PAGEPAGE 数据库中的 8 KB 页,例如数据页或索引页。An 8-kilobyte (KB) page in a database, such as data or index pages.
EXTENTEXTENT 一组连续的八页,例如数据页或索引页。A contiguous group of eight pages, such as data or index pages.
HoBTHoBT 堆或 B 树。A heap or B-tree. 用于保护没有聚集索引的表中的 B 树(索引)或堆数据页的锁。A lock protecting a B-tree (index) or the heap data pages in a table that does not have a clustered index.
TABLETABLE 包括所有数据和索引的整个表。The entire table, including all data and indexes.
FILEFILE 数据库文件。A database file.
APPLICATIONAPPLICATION 应用程序专用的资源。An application-specified resource.
METADATAMETADATA 元数据锁。Metadata locks.
ALLOCATION_UNITALLOCATION_UNIT 分配单元。An allocation unit.
DATABASEDATABASE 整个数据库。The entire database.

备注

使用 ALTER TABLE 的 LOCK_ESCALATION 选项会对 HoBT 和 TABLE 锁带来影响。HoBT and TABLE locks can be affected by the LOCK_ESCALATION option of ALTER TABLE.

锁模式Lock Modes

SQL Server 数据库引擎SQL Server Database Engine使用不同的锁模式锁定资源,这些锁模式确定了并发事务访问资源的方式。The SQL Server 数据库引擎SQL Server Database Engine locks resources using different lock modes that determine how the resources can be accessed by concurrent transactions.

下表显示了数据库引擎Database Engine使用的资源锁模式。The following table shows the resource lock modes that the 数据库引擎Database Engine uses.

锁模式Lock mode DescriptionDescription
共享 (S)Shared (S) 用于不更改或不更新数据的读取操作,如 SELECT 语句。Used for read operations that do not change or update data, such as a SELECT statement.
更新 (U)Update (U) 用于可更新的资源中。Used on resources that can be updated. 防止当多个会话在读取、锁定以及随后可能进行的资源更新时发生常见形式的死锁。Prevents a common form of deadlock that occurs when multiple sessions are reading, locking, and potentially updating resources later.
排他 (X)Exclusive (X) 用于数据修改操作,例如 INSERT、UPDATE 或 DELETE。Used for data-modification operations, such as INSERT, UPDATE, or DELETE. 确保不会同时对同一资源进行多重更新。Ensures that multiple updates cannot be made to the same resource at the same time.
意向Intent 用于建立锁的层次结构。Used to establish a lock hierarchy. 意向锁包含三种类型:意向共享 (IS)、意向排他 (IX) 和意向排他共享 (SIX)。The types of intent locks are: intent shared (IS), intent exclusive (IX), and shared with intent exclusive (SIX).
架构Schema 在执行依赖于表架构的操作时使用。Used when an operation dependent on the schema of a table is executing. 架构锁包含两种类型:架构修改 (Sch-M) 和架构稳定性 (Sch-S)。The types of schema locks are: schema modification (Sch-M) and schema stability (Sch-S).
大容量更新 (BU)Bulk Update (BU) 时使用大容量复制数据到表和TABLOCK指定提示。Used when bulk copying data into a table and the TABLOCK hint is specified.
键范围Key-range 当使用可序列化事务隔离级别时保护查询读取的行的范围。Protects the range of rows read by a query when using the serializable transaction isolation level. 确保再次运行查询时其他事务无法插入符合可序列化事务的查询的行。Ensures that other transactions cannot insert rows that would qualify for the queries of the serializable transaction if the queries were run again.

共享锁Shared Locks

共享锁(S 锁)允许并发事务在封闭式并发控制下读取 (SELECT) 资源。Shared (S) locks allow concurrent transactions to read (SELECT) a resource under pessimistic concurrency control. 资源上存在共享锁(S 锁)时,任何其他事务都不能修改数据。No other transactions can modify the data while shared (S) locks exist on the resource. 读取操作一完成,就立即释放资源上的共享锁(S 锁),除非将事务隔离级别设置为可重复读或更高级别,或者在事务持续时间内用锁定提示保留共享锁(S 锁)。Shared (S) locks on a resource are released as soon as the read operation completes, unless the transaction isolation level is set to repeatable read or higher, or a locking hint is used to retain the shared (S) locks for the duration of the transaction.

更新锁Update Locks

更新锁(U 锁)可以防止常见的死锁。Update (U) locks prevent a common form of deadlock. 在可重复读或可序列化事务中,此事务读取数据 [获取资源(页或行)的共享锁(S 锁)],然后修改数据 [此操作要求锁转换为排他锁(X 锁)]。In a repeatable read or serializable transaction, the transaction reads data, acquiring a shared (S) lock on the resource (page or row), and then modifies the data, which requires lock conversion to an exclusive (X) lock. 如果两个事务获得了资源上的共享模式锁,然后试图同时更新数据,则一个事务尝试将锁转换为排他锁(X 锁)。If two transactions acquire shared-mode locks on a resource and then attempt to update data concurrently, one transaction attempts the lock conversion to an exclusive (X) lock. 共享模式到排他锁的转换必须等待一段时间,因为一个事务的排他锁与其他事务的共享模式锁不兼容;发生锁等待。The shared-mode-to-exclusive lock conversion must wait because the exclusive lock for one transaction is not compatible with the shared-mode lock of the other transaction; a lock wait occurs. 第二个事务试图获取排他锁(X 锁)以进行更新。The second transaction attempts to acquire an exclusive (X) lock for its update. 由于两个事务都要转换为排他锁(X 锁),并且每个事务都等待另一个事务释放共享模式锁,因此发生死锁。Because both transactions are converting to exclusive (X) locks, and they are each waiting for the other transaction to release its shared-mode lock, a deadlock occurs.

若要避免这种潜在的死锁问题,请使用更新锁(U 锁)。To avoid this potential deadlock problem, update (U) locks are used. 一次只有一个事务可以获得资源的更新锁(U 锁)。Only one transaction can obtain an update (U) lock to a resource at a time. 如果事务修改资源,则更新锁(U 锁)转换为排他锁(X 锁)。If a transaction modifies a resource, the update (U) lock is converted to an exclusive (X) lock.

排他锁Exclusive Locks

排他锁(X 锁)可以防止并发事务对资源进行访问。Exclusive (X) locks prevent access to a resource by concurrent transactions. 使用排他锁(X 锁)时,任何其他事务都无法修改数据;仅在使用 NOLOCK 提示或未提交读隔离级别时才会进行读取操作。With an exclusive (X) lock, no other transactions can modify data; read operations can take place only with the use of the NOLOCK hint or read uncommitted isolation level.

数据修改语句(如 INSERT、UPDATE 和 DELETE)合并了修改和读取操作。Data modification statements, such as INSERT, UPDATE, and DELETE combine both modification and read operations. 语句在执行所需的修改操作之前首先执行读取操作以获取数据。The statement first performs read operations to acquire data before performing the required modification operations. 因此,数据修改语句通常请求共享锁和排他锁。Data modification statements, therefore, typically request both shared locks and exclusive locks. 例如,UPDATE 语句可能根据与一个表的联接修改另一个表中的行。For example, an UPDATE statement might modify rows in one table based on a join with another table. 在此情况下,除了请求更新行上的排他锁之外,UPDATE 语句还将请求在联接表中读取的行上的共享锁。In this case, the UPDATE statement requests shared locks on the rows read in the join table in addition to requesting exclusive locks on the updated rows.

意向锁Intent Locks

数据库引擎Database Engine使用意向锁来保护共享锁(S 锁)或排他锁(X 锁)放置在锁层次结构的底层资源上。The 数据库引擎Database Engine uses intent locks to protect placing a shared (S) lock or exclusive (X) lock on a resource lower in the lock hierarchy. 意向锁之所以命名为意向锁,是因为在较低级别锁前可获取它们,因此会通知意向将锁放置在较低级别上。Intent locks are named intent locks because they are acquired before a lock at the lower level, and therefore signal intent to place locks at a lower level.

意向锁有两种用途:Intent locks serve two purposes:

  • 防止其他事务以会使较低级别的锁无效的方式修改较高级别资源。To prevent other transactions from modifying the higher-level resource in a way that would invalidate the lock at the lower level.

  • 提高数据库引擎Database Engine在较高的粒度级别检测锁冲突的效率。To improve the efficiency of the 数据库引擎Database Engine in detecting lock conflicts at the higher level of granularity.

例如,在该表的页或行上请求共享锁(S 锁)之前,在表级请求共享意向锁。For example, a shared intent lock is requested at the table level before shared (S) locks are requested on pages or rows within that table. 在表级设置意向锁可防止另一个事务随后在包含那一页的表上获取排他锁(X 锁)。Setting an intent lock at the table level prevents another transaction from subsequently acquiring an exclusive (X) lock on the table containing that page. 意向锁可以提高性能,因为数据库引擎Database Engine仅在表级检查意向锁来确定事务是否可以安全地获取该表上的锁。Intent locks improve performance because the 数据库引擎Database Engine examines intent locks only at the table level to determine if a transaction can safely acquire a lock on that table. 而不需要检查表中的每行或每页上的锁以确定事务是否可以锁定整个表。This removes the requirement to examine every row or page lock on the table to determine if a transaction can lock the entire table.

意向锁包括意向共享 (IS)、意向排他 (IX) 以及意向排他共享 (SIX)。Intent locks include intent shared (IS), intent exclusive (IX), and shared with intent exclusive (SIX).

锁模式Lock mode DescriptionDescription
意向共享 (IS)Intent shared (IS) 保护针对层次结构中某些(而并非所有)低层资源请求或获取的共享锁。Protects requested or acquired shared locks on some (but not all) resources lower in the hierarchy.
意向排他 (IX)Intent exclusive (IX) 保护针对层次结构中某些(而并非所有)低层资源请求或获取的排他锁。Protects requested or acquired exclusive locks on some (but not all) resources lower in the hierarchy. IX 是 IS 的超集,它也保护针对低层级别资源请求的共享锁。IX is a superset of IS, and it also protects requesting shared locks on lower level resources.
意向排他共享 (SIX)Shared with intent exclusive (SIX) 保护针对层次结构中某些(而并非所有)低层资源请求或获取的共享锁以及针对某些(而并非所有)低层资源请求或获取的意向排他锁。Protects requested or acquired shared locks on all resources lower in the hierarchy and intent exclusive locks on some (but not all) of the lower level resources. 顶级资源允许使用并发 IS 锁。Concurrent IS locks at the top-level resource are allowed. 例如,获取表上的 SIX 锁也将获取正在修改的页上的意向排他锁以及修改的行上的排他锁。For example, acquiring a SIX lock on a table also acquires intent exclusive locks on the pages being modified and exclusive locks on the modified rows. 虽然每个资源在一段时间内只能有一个 SIX 锁,以防止其他事务对资源进行更新,但是其他事务可以通过获取表级的 IS 锁来读取层次结构中的低层资源。There can be only one SIX lock per resource at one time, preventing updates to the resource made by other transactions, although other transactions can read resources lower in the hierarchy by obtaining IS locks at the table level.
意向更新 (IU)Intent update (IU) 保护针对层次结构中所有低层资源请求或获取的更新锁。Protects requested or acquired update locks on all resources lower in the hierarchy. 仅在页资源上使用 IU 锁。IU locks are used only on page resources. 如果进行了更新操作,IU 锁将转换为 IX 锁。IU locks are converted to IX locks if an update operation takes place.
共享意向更新 (SIU)Shared intent update (SIU) S 锁和 IU 锁的组合,作为分别获取这些锁并且同时持有两种锁的结果。A combination of S and IU locks, as a result of acquiring these locks separately and simultaneously holding both locks. 例如,事务执行带有 PAGLOCK 提示的查询,然后执行更新操作。For example, a transaction executes a query with the PAGLOCK hint and then executes an update operation. 带有 PAGLOCK 提示的查询将获取 S 锁,更新操作将获取 IU 锁。The query with the PAGLOCK hint acquires the S lock, and the update operation acquires the IU lock.
更新意向排他 (UIX)Update intent exclusive (UIX) U 锁和 IX 锁的组合,作为分别获取这些锁并且同时持有两种锁的结果。A combination of U and IX locks, as a result of acquiring these locks separately and simultaneously holding both locks.

架构锁Schema Locks

数据库引擎Database Engine在表数据定义语言 (DDL) 操作(例如添加列或删除表)的过程中使用架构修改 (Sch-M) 锁。The 数据库引擎Database Engine uses schema modification (Sch-M) locks during a table data definition language (DDL) operation, such as adding a column or dropping a table. 保持该锁期间,Sch-M 锁将阻止对表进行并发访问。During the time that it is held, the Sch-M lock prevents concurrent access to the table. 这意味着 Sch-M 锁在释放前将阻止所有外围操作。This means the Sch-M lock blocks all outside operations until the lock is released.

某些数据操作语言 (DML) 操作(例如表截断)使用 Sch-M 锁阻止并发操作访问受影响的表。Some data manipulation language (DML) operations, such as table truncation, use Sch-M locks to prevent access to affected tables by concurrent operations.

数据库引擎Database Engine在编译和执行查询时使用架构稳定性 (Sch-S) 锁。The 数据库引擎Database Engine uses schema stability (Sch-S) locks when compiling and executing queries. Sch-S 锁不会阻止某些事务锁,其中包括排他 (X) 锁。Sch-S locks do not block any transactional locks, including exclusive (X) locks. 因此,在编译查询的过程中,其他事务(包括那些针对表使用 X 锁的事务)将继续运行。Therefore, other transactions, including those with X locks on a table, continue to run while a query is being compiled. 但是,无法针对表执行获取 Sch-M 锁的并发 DDL 操作和并发 DML 操作。However, concurrent DDL operations, and concurrent DML operations that acquire Sch-M locks, cannot be performed on the table.

大容量更新锁Bulk Update Locks

大容量更新锁(BU 锁)允许多个线程将数据并发地大容量加载到同一表,同时防止其他不进行大容量加载数据的进程访问该表。Bulk update (BU) locks allow multiple threads to bulk load data concurrently into the same table while preventing other processes that are not bulk loading data from accessing the table. 在满足以下两个条件时,数据库引擎Database Engine使用大容量更新 (BU) 锁。The 数据库引擎Database Engine uses bulk update (BU) locks when both of the following conditions are true.

  • 您使用 Transact-SQL BULK INSERT 语句或 OPENROWSET(BULK) 函数,或者您使用某个大容量插入 API 命令(如 .NET SqlBulkCopy)、OLEDB 快速加载 API 或 ODBC 大容量复制 API 来将数据大容量复制到表。You use the Transact-SQL BULK INSERT statement, or the OPENROWSET(BULK) function, or you use one of the Bulk Insert API commands such as .NET SqlBulkCopy, OLEDB Fast Load APIs, or the ODBC Bulk Copy APIs to bulk copy data into a table.

  • 指定了 TABLOCK 提示或使用 sp_tableoption 设置 table lock on bulk load 表选项。The TABLOCK hint is specified or the table lock on bulk load table option is set using sp_tableoption.

提示

与持有较少限制性大容量更新锁的 BULK INSERT 语句不同,具有 TABLOCK 提示的 INSERT INTO…SELECT 语句持有一个针对表的排他 (X) 锁。Unlike the BULK INSERT statement, which holds a less restrictive Bulk Update lock, INSERT INTO...SELECT with the TABLOCK hint holds an exclusive (X) lock on the table. 也就是说您不能使用并行插入操作插入行。This means that you cannot insert rows using parallel insert operations.

键范围锁Key-Range Locks

在使用可序列化事务隔离级别时,对于 Transact-SQLTransact-SQL 语句读取的记录集,键范围锁可以隐式保护该记录集中包含的行范围。Key-range locks protect a range of rows implicitly included in a record set being read by a Transact-SQLTransact-SQL statement while using the serializable transaction isolation level. 键范围锁可防止虚拟读取。Key-range locking prevents phantom reads. 通过保护行之间键的范围,它还防止对事务访问的记录集进行虚拟插入或删除。By protecting the ranges of keys between rows, it also prevents phantom insertions or deletions into a record set accessed by a transaction.

锁兼容性Lock Compatibility

锁兼容性控制多个事务能否同时获取同一资源上的锁。Lock compatibility controls whether multiple transactions can acquire locks on the same resource at the same time. 如果资源已被另一事务锁定,则仅当请求锁的模式与现有锁的模式相兼容时,才会授予新的锁请求。If a resource is already locked by another transaction, a new lock request can be granted only if the mode of the requested lock is compatible with the mode of the existing lock. 如果请求锁的模式与现有锁的模式不兼容,则请求新锁的事务将等待释放现有锁或等待锁超时间隔过期。If the mode of the requested lock is not compatible with the existing lock, the transaction requesting the new lock waits for the existing lock to be released or for the lock timeout interval to expire. 例如,没有与排他锁兼容的锁模式。For example, no lock modes are compatible with exclusive locks. 如果具有排他锁(X 锁),则在释放排他锁(X 锁)之前,其他事务均无法获取该资源的任何类型(共享、更新或排他)的锁。While an exclusive (X) lock is held, no other transaction can acquire a lock of any kind (shared, update, or exclusive) on that resource until the exclusive (X) lock is released. 另一种情况是,如果共享锁(S 锁)已应用到资源,则即使第一个事务尚未完成,其他事务也可以获取该项的共享锁或更新锁(U 锁)。Alternatively, if a shared (S) lock has been applied to a resource, other transactions can also acquire a shared lock or an update (U) lock on that item even if the first transaction has not completed. 但是,在释放共享锁之前,其他事务无法获取排他锁。However, other transactions cannot acquire an exclusive lock until the shared lock has been released.

下表显示了最常见的锁模式的兼容性。The following table shows the compatibility of the most commonly encountered lock modes.

现有的授予模式Existing granted mode
请求的模式Requested mode ISIS SS UU IXIX SIXSIX XX
意向共享 (IS)Intent shared (IS) Yes Yes Yes Yes Yes No
共享 (S)Shared (S) Yes Yes Yes No No No
更新 (U)Update (U) Yes Yes No No No No
意向排他 (IX)Intent exclusive (IX) Yes No No Yes No No
意向排他共享 (SIX)Shared with intent exclusive (SIX) Yes No No No No No
排他 (X)Exclusive (X) No No No No No No

备注

意向排他锁(IX 锁)与 IX 锁模式兼容,因为 IX 表示打算只更新部分行而不是所有行。An intent exclusive (IX) lock is compatible with an IX lock mode because IX means the intention is to update only some of the rows rather than all of them. 还允许其他事务尝试读取或更新部分行,只要这些行不是其他事务当前更新的行即可。Other transactions that attempt to read or update some of the rows are also permitted as long as they are not the same rows being updated by other transactions. 此外,如果两个事务尝试更新同一行,将在表级和页级上授予这两个事务 IX 锁。Further, if two transactions attempt to update the same row, both transactions will be granted an IX lock at table and page level. 但是,将在行级授予一个事务 X 锁。However, one transaction will be granted an X lock at row level. 另一个事务必须在该行级锁被删除前等待。The other transaction must wait until the row-level lock is removed.

使用下表可以确定 SQL ServerSQL Server 中所有可用的锁模式的兼容性。Use the following table to determine the compatibility of all the lock modes available in SQL ServerSQL Server.

关系图显示锁兼容性矩阵Diagram showing lock compatibility matrix

键范围锁定Key-Range Locking

在使用可序列化事务隔离级别时,对于 Transact-SQLTransact-SQL 语句读取的记录集,键范围锁可以隐式保护该记录集中包含的行范围。Key-range locks protect a range of rows implicitly included in a record set being read by a Transact-SQLTransact-SQL statement while using the serializable transaction isolation level. 可序列化隔离级别要求每当在事务期间执行任一查询时,该查询都必须获取相同的行集。The serializable isolation level requires that any query executed during a transaction must obtain the same set of rows every time it is executed during the transaction. 键范围锁可防止其他事务插入其键值位于可序列化事务读取的键值范围内的新行,从而确保满足此要求。A key range lock protects this requirement by preventing other transactions from inserting new rows whose keys would fall in the range of keys read by the serializable transaction.

键范围锁可防止虚拟读取。Key-range locking prevents phantom reads. 通过保护行之间的键范围,它还可以防止对事务访问的记录集进行虚拟插入。By protecting the ranges of keys between rows, it also prevents phantom insertions into a set of records accessed by a transaction.

键范围锁放置在索引上,指定开始键值和结束键值。A key-range lock is placed on an index, specifying a beginning and ending key value. 此锁将阻止任何要插入、更新或删除任何带有该范围内的键值的行的尝试,因为这些操作会首先获取索引上的锁。This lock blocks any attempt to insert, update, or delete any row with a key value that falls in the range because those operations would first have to acquire a lock on the index. 例如,可序列化事务可能发出了一个 SELECT 语句,读取键值介于“AAA”与“CZZ”之间的所有行。For example, a serializable transaction could issue a SELECT statement that reads all rows whose key values are between ' AAA ' and ' CZZ '. 从“AAA”到“CZZ”范围内的键值上的键范围锁可阻止其他事务插入带有该范围内的键值(例如“ADG”、“BBD”或“CAL”)的行。A key-range lock on the key values in the range from ' AAA ' to ' CZZ ' prevents other transactions from inserting rows with key values anywhere in that range, such as ' ADG ', ' BBD ', or ' CAL '.

键范围锁模式Key-Range Lock Modes

键范围锁包括按范围-行格式指定的范围组件和行组件:Key-range locks include both a range and a row component specified in range-row format:

  • 范围表示保护两个连续索引项之间的范围的锁模式。Range represents the lock mode protecting the range between two consecutive index entries.

  • 行表示保护索引项的锁模式。Row represents the lock mode protecting the index entry.

  • 模式表示使用的组合锁模式。Mode represents the combined lock mode used. 键范围锁模式由两部分组成。Key-range lock modes consist of two parts. 第一部分表示用于锁定索引范围 (RangeT) 的锁类型,第二部分表示用于锁定特定键 (K) 的锁类型。The first represents the type of lock used to lock the index range (RangeT) and the second represents the lock type used to lock a specific key (K). 这两部分用连字符 (-) 连接,例如 RangeT-K。The two parts are connected with a hyphen (-), such as RangeT-K.

    范围Range Row 模式Mode DescriptionDescription
    RangeSRangeS SS RangeS-SRangeS-S 共享范围,共享资源锁;可序列化范围扫描。Shared range, shared resource lock; serializable range scan.
    RangeSRangeS UU RangeS-URangeS-U 共享范围,更新资源锁;可串行更新扫描。Shared range, update resource lock; serializable update scan.
    RangeIRangeI NullNull RangeI-NRangeI-N 插入范围,空资源锁;用于在索引中插入新键之前测试范围。Insert range, null resource lock; used to test ranges before inserting a new key into an index.
    RangeXRangeX XX RangeX-XRangeX-X 排他范围,排他资源锁;用于更新范围中的键。Exclusive range, exclusive resource lock; used when updating a key in a range.

备注

内部空锁模式与所有其他锁模式兼容。The internal Null lock mode is compatible with all other lock modes.

键范围锁模式有一个兼容性矩阵,表示哪些锁与在重叠键和范围上获取的其他锁兼容。Key-range lock modes have a compatibility matrix that shows which locks are compatible with other locks obtained on overlapping keys and ranges.

现有的授予模式Existing granted mode
请求的模式Requested mode SS UU XX RangeS-SRangeS-S RangeS-URangeS-U RangeI-NRangeI-N RangeX-XRangeX-X
共享 (S)Shared (S) Yes Yes No Yes Yes Yes No
更新 (U)Update (U) Yes No No Yes No Yes No
排他 (X)Exclusive (X) No No No No No Yes No
RangeS-SRangeS-S Yes Yes No Yes Yes No No
RangeS-URangeS-U Yes No No Yes No No No
RangeI-NRangeI-N Yes Yes Yes No No Yes No
RangeX-XRangeX-X No No No No No No No

转换锁Conversion Locks

当键范围锁与其他锁重叠时,将创建转换锁。Conversion locks are created when a key-range lock overlaps another lock.

锁定 1Lock 1 锁 2Lock 2 转换锁Conversion lock
SS RangeI-NRangeI-N RangeI-SRangeI-S
UU RangeI-NRangeI-N RangeI-URangeI-U
XX RangeI-NRangeI-N RangeI-XRangeI-X
RangeI-NRangeI-N RangeS-SRangeS-S RangeX-SRangeX-S
RangeI-NRangeI-N RangeS-URangeS-U RangeX-URangeX-U

在不同的复杂环境下(有时是在运行并发进程时),可以在一小段时间内观察到转换锁。Conversion locks can be observed for a short period of time under different complex circumstances, sometimes while running concurrent processes.

可序列化范围扫描、单独提取、删除和插入Serializable Range Scan, Singleton Fetch, Delete, and Insert

键范围锁定确保以下操作是可序列化的:Key-range locking ensures that the following operations are serializable:

  • 范围扫描查询Range scan query

  • 对不存在的行的单独提取Singleton fetch of nonexistent row

  • 删除操作Delete operation

  • 插入操作Insert operation

必须满足下列条件才能发生键范围锁定:Before key-range locking can occur, the following conditions must be satisfied:

  • 事务隔离级别必须设置为 SERIALIZABLE。The transaction-isolation level must be set to SERIALIZABLE.

  • 查询处理器必须使用索引来实现范围筛选谓词。The query processor must use an index to implement the range filter predicate. 例如,SELECT 语句中的 WHERE 子句可以建立范围条件用以下谓词:ColumnX BETWEEN N ' AAA ' AND N ' CZZ 'For example, the WHERE clause in a SELECT statement could establish a range condition with this predicate: ColumnX BETWEEN N ' AAA ' AND N ' CZZ '. 仅当 ColumnX 被索引键覆盖时,才能获取键范围锁。A key-range lock can only be acquired if ColumnX is covered by an index key.

示例Examples

以下表和索引用作随后的键范围锁定示例的基础。The following table and index are used as a basis for the key-range locking examples that follow.

数据库表具有索引 b 树插图Database table with index b-tree illustration

范围扫描查询Range Scan Query

为了确保范围扫描查询是可序列化的,每次在同一事务中执行的相同查询应返回同样的结果。To ensure a range scan query is serializable, the same query should return the same results each time it is executed within the same transaction. 其他事务不能在范围扫描查询中插入新行;否则这些插入将成为虚拟插入。New rows must not be inserted within the range scan query by other transactions; otherwise, these become phantom inserts. 例如,以下查询将使用上图中的表和索引:For example, the following query uses the table and index in the previous illustration:

SELECT name  
    FROM mytable  
    WHERE name BETWEEN 'A' AND 'C';  

键范围锁放置在与数据行范围(名称在值 Adam 与 Dale 之间的行)对应的索引项上,以防止添加或删除满足上述查询条件的新行。Key-range locks are placed on the index entries corresponding to the range of data rows where the name is between the values Adam and Dale, preventing new rows qualifying in the previous query from being added or deleted. 尽管此范围中的第一个名称是 Adam,但是此索引项上的 RangeS-S 模式键范围锁确保了以字母 A 开头的新名称(例如 Abigail)不能添加在 Adam 之前。Although the first name in this range is Adam, the RangeS-S mode key-range lock on this index entry ensures that no new names beginning with the letter A can be added before Adam, such as Abigail. 同样,Dale 索引项上的 RangeS-S 键范围锁确保了以字母 C 开头的新名称(例如 Clive)不能添加在 Carlos 之后。Similarly, the RangeS-S key-range lock on the index entry for Dale ensures that no new names beginning with the letter C can be added after Carlos, such as Clive.

备注

包含的 RangeS-S 锁数量为 n+1,此处 n 是满足查询条件的行数。The number of RangeS-S locks held is n+1, where n is the number of rows that satisfy the query.

对不存在的数据的单独提取Singleton Fetch of Nonexistent Data

如果事务中的查询试图选择不存在的行,则以后在相同的事务中发出这一查询时,必须返回相同的结果。If a query within a transaction attempts to select a row that does not exist, issuing the query at a later point within the same transaction has to return the same result. 不允许其他事务插入不存在的行。No other transaction can be allowed to insert that nonexistent row. 例如,对于下面的查询:For example, given this query:

SELECT name  
    FROM mytable  
    WHERE name = 'Bill';  

键范围锁放置在与从 BenBing 的名称范围对应的索引项上,因为名称 Bill 将插入到这两个相邻的索引项之间。A key-range lock is placed on the index entry corresponding to the name range from Ben to Bing because the name Bill would be inserted between these two adjacent index entries. RangeS-S 模式键范围锁放置在索引项 Bing 上。The RangeS-S mode key-range lock is placed on the index entry Bing. 这样可阻止其他任何事务在索引项 BillBen 之间插入值(例如 Bing)。This prevents any other transaction from inserting values, such as Bill, between the index entries Ben and Bing.

删除操作Delete Operation

在事务中删除值时,在事务执行删除操作期间不必锁定该值所属的范围。When deleting a value within a transaction, the range the value falls into does not have to be locked for the duration of the transaction performing the delete operation. 锁定删除的键值直至事务结束足以保持可序列化性。Locking the deleted key value until the end of the transaction is sufficient to maintain serializability. 例如,对于下面的 DELETE 语句:For example, given this DELETE statement:

DELETE mytable  
    WHERE name = 'Bob';  

排他锁(X 锁)放置在与名称 Bob 对应的索引项上。An exclusive (X) lock is placed on the index entry corresponding to the name Bob. 其他事务可以在删除的值 Bob 的前后插入或删除值。Other transactions can insert or delete values before or after the deleted value Bob. 但是任何试图读取、插入或删除值 Bob 的事务都将被阻塞,直到删除的事务提交或回滚为止。However, any transaction that attempts to read, insert, or delete the value Bob will be blocked until the deleting transaction either commits or rolls back.

可以使用三个基本锁模式执行范围删除:行锁、页锁或表锁。Range delete can be executed using three basic lock modes: row, page, or table lock. 行、页或表锁定策略由查询优化器确定,或者可以由用户通过优化程序提示(例如 ROWLOCK、PAGLOCK 或 TABLOCK)来指定。The row, page, or table locking strategy is decided by query optimizer or can be specified by the user through optimizer hints such as ROWLOCK, PAGLOCK, or TABLOCK. 当使用 PAGLOCK 或 TABLOCK 时,如果从某个索引页中删除所有的行,则数据库引擎Database Engine将立即释放该索引页。When PAGLOCK or TABLOCK is used, the 数据库引擎Database Engine immediately deallocates an index page if all rows are deleted from this page. 相反,当使用 ROWLOCK 时,所有删除的行只是标记为已删除;以后通过后台任务从索引页中删除它们。In contrast, when ROWLOCK is used, all deleted rows are marked only as deleted; they are removed from the index page later using a background task.

插入操作Insert Operation

在事务中插入值时,在事务执行插入操作期间不必锁定该值所属的范围。When inserting a value within a transaction, the range the value falls into does not have to be locked for the duration of the transaction performing the insert operation. 锁定插入的键值直至事务结束足以维护可序列化性。Locking the inserted key value until the end of the transaction is sufficient to maintain serializability. 例如,对于下面的 INSERT 语句:For example, given this INSERT statement:

INSERT mytable VALUES ('Dan');  

RangeI-N 模式键范围锁放置在与名称 David 对应的索引项上,以测试范围。The RangeI-N mode key-range lock is placed on the index entry corresponding to the name David to test the range. 如果已授权锁,则插入 Dan,并且排他锁(X 锁)将放置在值 Dan 上。If the lock is granted, Dan is inserted and an exclusive (X) lock is placed on the value Dan. RangeI-N 模式键范围锁仅对测试范围是必需的,而不在执行插入操作的事务期间保留。The RangeI-N mode key-range lock is necessary only to test the range and is not held for the duration of the transaction performing the insert operation. 其他事务可以在插入的值 Dan 的前后插入或删除值。Other transactions can insert or delete values before or after the inserted value Dan. 但是,任何试图读取、插入或删除值 Dan 的事务都将被阻塞,直到插入的事务提交或回滚为止。However, any transaction attempting to read, insert, or delete the value Dan will be locked until the inserting transaction either commits or rolls back.

动态锁定Dynamic Locking

使用低级锁(如行锁)可以降低两个事务同时在相同数据块上请求锁的可能性,从而提高并发性。Using low-level locks, such as row locks, increases concurrency by decreasing the probability that two transactions will request locks on the same piece of data at the same time. 使用低级锁还会增加锁的数量以及管理锁所需的资源。Using low-level locks also increases the number of locks and the resources needed to manage them. 使用高级表锁或页锁可以减少开销,但代价是降低了并发性。Using high-level table or page locks lowers overhead, but at the expense of lowering concurrency.

关系图显示成本与粒度Diagram showing cost versus granularity

MicrosoftMicrosoft SQL Server 数据库引擎SQL Server Database Engine使用动态锁定策略确定最经济的锁。The MicrosoftMicrosoft SQL Server 数据库引擎SQL Server Database Engine uses a dynamic locking strategy to determine the most cost-effective locks. 执行查询时,数据库引擎Database Engine会根据架构和查询的特点自动决定最合适的锁。The 数据库引擎Database Engine automatically determines what locks are most appropriate when the query is executed, based on the characteristics of the schema and query. 例如,为了缩减锁定的开销,优化器可能在执行索引扫描时在索引中选择页级锁。For example, to reduce the overhead of locking, the optimizer may choose page-level locks in an index when performing an index scan.

动态锁定具有下列优点:Dynamic locking has the following advantages:

  • 简化数据库管理。Simplified database administration. 数据库管理员不必调整锁升级阈值。Database administrators do not have to adjust lock escalation thresholds.

  • 提高性能。Increased performance. 数据库引擎Database Engine通过使用适合任务的锁使系统开销减至最小。The 数据库引擎Database Engine minimizes system overhead by using locks appropriate to the task.

  • 应用程序开发人员可以集中精力进行开发。Application developers can concentrate on development. 数据库引擎Database Engine将自动调整锁定。The 数据库引擎Database Engine adjusts locking automatically.

SQL Server 2008SQL Server 2008和更高版本,锁升级的行为已更改与引入了 LOCK_ESCALATION 选项。In SQL Server 2008SQL Server 2008 and later versions, the behavior of lock escalation has changed with the introduction of the LOCK_ESCALATION option. 有关详细信息,请参阅的 LOCK_ESCALATION 选项ALTER TABLEFor more information, see the LOCK_ESCALATION option of ALTER TABLE.

死锁Deadlocking

在两个或多个任务中,如果每个任务锁定了其他任务试图锁定的资源,此时会造成这些任务永久阻塞,从而出现死锁。A deadlock occurs when two or more tasks permanently block each other by each task having a lock on a resource which the other tasks are trying to lock. 例如:For example:

  • 事务 A 获取了行 1 的共享锁。Transaction A acquires a share lock on row 1.

  • 事务 B 获取了行 2 的共享锁。Transaction B acquires a share lock on row 2.

  • 现在,事务 A 请求行 2 的排他锁,但在事务 B 完成并释放其对行 2 持有的共享锁之前被阻塞。Transaction A now requests an exclusive lock on row 2, and is blocked until transaction B finishes and releases the share lock it has on row 2.

  • 现在,事务 B 请求行 1 的排他锁,但在事务 A 完成并释放其对行 1 持有的共享锁之前被阻塞。Transaction B now requests an exclusive lock on row 1, and is blocked until transaction A finishes and releases the share lock it has on row 1.

事务 A 才能完成事务 B 完成,但是事务 B 由事务 A 阻塞此条件也称为循环依赖关系:事务 A 依赖于事务 B,此外,事务 B 由事务 A.上具有依赖关系关闭循环Transaction A cannot complete until transaction B completes, but transaction B is blocked by transaction A. This condition is also called a cyclic dependency: Transaction A has a dependency on transaction B, and transaction B closes the circle by having a dependency on transaction A.

除非某个外部进程断开死锁,否则死锁中的两个事务都将无限期等待下去。Both transactions in a deadlock will wait forever unless the deadlock is broken by an external process. SQL Server 数据库引擎SQL Server Database Engine 死锁监视器定期检查陷入死锁的任务。The SQL Server 数据库引擎SQL Server Database Engine deadlock monitor periodically checks for tasks that are in a deadlock. 如果监视器检测到循环依赖关系,将选择其中一个任务作为牺牲品,然后终止其事务并提示错误。If the monitor detects a cyclic dependency, it chooses one of the tasks as a victim and terminates its transaction with an error. 这样,其他任务就可以完成其事务。This allows the other task to complete its transaction. 对于事务以错误终止的应用程序,它还可以重试该事务,但通常要等到与它一起陷入死锁的其他事务完成后执行。The application with the transaction that terminated with an error can retry the transaction, which usually completes after the other deadlocked transaction has finished.

死锁经常与正常阻塞混淆。Deadlocking is often confused with normal blocking. 事务请求被其他事务锁定的资源的锁时,发出请求的事务一直等到该锁被释放。When a transaction requests a lock on a resource locked by another transaction, the requesting transaction waits until the lock is released. 默认情况下,除非设置了 LOCK_TIMEOUT,否则 SQL ServerSQL Server 事务不会超时。By default, SQL ServerSQL Server transactions do not time out, unless LOCK_TIMEOUT is set. 因为发出请求的事务未执行任何操作来阻塞拥有锁的事务,所以该事务是被阻塞,而不是陷入了死锁。The requesting transaction is blocked, not deadlocked, because the requesting transaction has not done anything to block the transaction owning the lock. 最后,拥有锁的事务将完成并释放锁,然后发出请求底事务将获取锁并继续执行。Eventually, the owning transaction will complete and release the lock, and then the requesting transaction will be granted the lock and proceed.

死锁有时称为抱死。Deadlocks are sometimes called a deadly embrace.

不只是关系数据库管理系统,任何多线程系统上都会发生死锁,并且对于数据库对象的锁之外的资源也会发生死锁。Deadlock is a condition that can occur on any system with multiple threads, not just on a relational database management system, and can occur for resources other than locks on database objects. 例如,多线程操作系统中的一个线程要获取一个或多个资源(例如,内存块)。For example, a thread in a multithreaded operating system might acquire one or more resources, such as blocks of memory. 如果要获取的资源当前为另一线程所拥有,则第一个线程可能必须等待拥有线程释放目标资源。If the resource being acquired is currently owned by another thread, the first thread may have to wait for the owning thread to release the target resource. 这就是说,对于该特定资源,等待线程依赖于拥有线程。The waiting thread is said to have a dependency on the owning thread for that particular resource. 数据库引擎Database Engine实例中,当获取非数据库资源(例如,内存或线程)时,会话会死锁。In an instance of the 数据库引擎Database Engine, sessions can deadlock when acquiring nondatabase resources, such as memory or threads.

关系图显示事务死锁Diagram showing transaction deadlock

在示例中,对于 Part 表锁资源,事务 T1 依赖于事务 T2。In the illustration, transaction T1 has a dependency on transaction T2 for the Part table lock resource. 同样,对于 Supplier 表锁资源,事务 T2 依赖于事务 T1。Similarly, transaction T2 has a dependency on transaction T1 for the Supplier table lock resource. 因为这些依赖关系形成了一个循环,所以在事务 T1 和事务 T2 之间存在死锁。Because these dependencies form a cycle, there is a deadlock between transactions T1 and T2.

当表进行了分区并且 ALTER TABLE 的 LOCK_ESCALATION 设置设为 AUTO 时也会发生死锁。Deadlocks can also occur when a table is partitioned and the LOCK_ESCALATION setting of ALTER TABLE is set to AUTO. 当 LOCK_ESCALATION 设置为自动时,会增加并发情况,从而数据库引擎Database Engine在 HoBT 级别而不是 TABLE 级别锁定表分区。When LOCK_ESCALATION is set to AUTO, concurrency increases by allowing the 数据库引擎Database Engine to lock table partitions at the HoBT level instead of at the TABLE level. 但是,当单独的事务在某个表中持有分区锁并希望在其他事务分区上的某处持有锁时,会导致发生死锁。However, when separate transactions hold partition locks in a table and want a lock somewhere on the other transactions partition, this causes a deadlock. 通过将 lock_escalation 设为表; 可以避免这种类型的死锁尽管此设置会减少并发情况强制为分区,以等待某个表锁的大量更新。This type of deadlock can be avoided by setting LOCK_ESCALATION to TABLE; although this setting will reduce concurrency by forcing large updates to a partition to wait for a table lock.

检测和结束死锁Detecting and Ending Deadlocks

在两个或多个任务中,如果每个任务锁定了其他任务试图锁定的资源,此时会造成这些任务永久阻塞,从而出现死锁。A deadlock occurs when two or more tasks permanently block each other by each task having a lock on a resource which the other tasks are trying to lock. 下图清楚地显示了死锁状态,其中:The following graph presents a high level view of a deadlock state where:

  • 任务 T1 具有资源 R1 的锁(通过从 R1 指向 T1 的箭头指示),并请求资源 R2 的锁(通过从 T1 指向 R2 的箭头指示)。Task T1 has a lock on resource R1 (indicated by the arrow from R1 to T1) and has requested a lock on resource R2 (indicated by the arrow from T1 to R2).

  • 任务 T2 具有资源 R2 的锁(通过从 R2 指向 T2 的箭头指示),并请求资源 R1 的锁(通过从 T2 指向 R1 的箭头指示)。Task T2 has a lock on resource R2 (indicated by the arrow from R2 to T2) and has requested a lock on resource R1 (indicated by the arrow from T2 to R1).

  • 因为这两个任务都需要有资源可用才能继续,而这两个资源又必须等到其中一个任务继续才会释放出来,所以陷入了死锁状态。Because neither task can continue until a resource is available and neither resource can be released until a task continues, a deadlock state exists.

显示处于死锁状态的任务的关系图Diagram showing tasks in a deadlock state

SQL Server 数据库引擎SQL Server Database Engine自动检测 SQL ServerSQL Server 中的死锁循环。The SQL Server 数据库引擎SQL Server Database Engine automatically detects deadlock cycles within SQL ServerSQL Server. 数据库引擎Database Engine选择一个会话作为死锁牺牲品,然后终止当前事务(出现错误)来打断死锁。The 数据库引擎Database Engine chooses one of the sessions as a deadlock victim and the current transaction is terminated with an error to break the deadlock.

可以死锁的资源Resources That Can Deadlock

每个用户会话可能有一个或多个代表它运行的任务,其中每个任务可能获取或等待获取各种资源。Each user session might have one or more tasks running on its behalf where each task might acquire or wait to acquire a variety of resources. 以下类型的资源可能会造成阻塞,并最终导致死锁。The following types of resources can cause blocking that could result in a deadlock.

  • Locks. 等待获取资源(如对象、页、行、元数据和应用程序)的锁可能导致死锁。Waiting to acquire locks on resources, such as objects, pages, rows, metadata, and applications can cause deadlock. 例如,事务 T1 在行 r1 上有共享锁(S 锁)并等待获取行 r2 的排他锁(X 锁)。For example, transaction T1 has a shared (S) lock on row r1 and is waiting to get an exclusive (X) lock on r2. 事务 T2 在行 r2 上有共享锁(S 锁)并等待获取行 r1 的排他锁(X 锁)。Transaction T2 has a shared (S) lock on r2 and is waiting to get an exclusive (X) lock on row r1. 这将导致一个锁循环,其中,T1 和 T2 都等待对方释放已锁定的资源。This results in a lock cycle in which T1 and T2 wait for each other to release the locked resources.

  • 工作线程Worker threads. 排队等待可用工作线程的任务可能导致死锁。A queued task waiting for an available worker thread can cause deadlock. 如果排队等待的任务拥有阻塞所有工作线程的资源,则将导致死锁。If the queued task owns resources that are blocking all worker threads, a deadlock will result. 例如,会话 S1 启动事务并获取行 r1 的共享锁(S 锁)后,进入睡眠状态。For example, session S1 starts a transaction and acquires a shared (S) lock on row r1 and then goes to sleep. 在所有可用工作线程上运行的活动会话正尝试获取行 r1 的排他锁(X 锁)。Active sessions running on all available worker threads are trying to acquire exclusive (X) locks on row r1. 因为会话 S1 无法获取工作线程,所以无法提交事务并释放行 r1 的锁。Because session S1 cannot acquire a worker thread, it cannot commit the transaction and release the lock on row r1. 这将导致死锁。This results in a deadlock.

  • 内存Memory. 当并发请求等待获得内存,而当前的可用内存无法满足其需要时,可能发生死锁。When concurrent requests are waiting for memory grants that cannot be satisfied with the available memory, a deadlock can occur. 例如,两个并发查询(Q1 和 Q2)作为用户定义函数执行,分别获取 10MB 和 20MB 的内存。For example, two concurrent queries, Q1 and Q2, execute as user-defined functions that acquire 10MB and 20MB of memory respectively. 如果每个查询需要 30MB 而可用总内存为 20MB,则 Q1 和 Q2 必须等待对方释放内存,这将导致死锁。If each query needs 30MB and the total available memory is 20MB, then Q1 and Q2 must wait for each other to release memory, and this results in a deadlock.

  • 并行查询执行的相关资源。通常与交换端口关联的处理协调器、发生器或使用者线程至少包含一个不属于并行查询的进程时,可能会相互阻塞,从而导致死锁。Parallel query execution-related resources Coordinator, producer, or consumer threads associated with an exchange port may block each other causing a deadlock usually when including at least one other process that is not a part of the parallel query. 此外,当并行查询启动执行时,SQL ServerSQL Server 将根据当前的工作负荷确定并行度或工作线程数。Also, when a parallel query starts execution, SQL ServerSQL Server determines the degree of parallelism, or the number of worker threads, based upon the current workload. 如果系统工作负荷发生意外更改,例如,当新查询开始在服务器中运行或系统用完工作线程时,则可能发生死锁。If the system workload unexpectedly changes, for example, where new queries start running on the server or the system runs out of worker threads, then a deadlock could occur.

  • 多重活动结果集 (MARS) 资源Multiple Active Result Sets (MARS) resources. 这些资源用于控制在 MARS 下交叉执行多个活动请求。These resources are used to control interleaving of multiple active requests under MARS. 有关详细信息,请参阅多个活动结果集 (MARS) SQL Server 中For more information, see Multiple Active Result Sets (MARS) in SQL Server.

    • 用户资源User resource. 线程等待可能被用户应用程序控制的资源时,该资源将被视为外部资源或用户资源,并将按锁进行处理。When a thread is waiting for a resource that is potentially controlled by a user application, the resource is considered to be an external or user resource and is treated like a lock.

    • 会话互斥体Session mutex. 在一个会话中运行的任务是交叉的,意味着在某一给定时间只能在该会话中运行一个任务。The tasks running in one session are interleaved, meaning that only one task can run under the session at a given time. 任务必须独占访问会话互斥体,才能运行。Before the task can run, it must have exclusive access to the session mutex.

    • 事务互斥体Transaction mutex. 在一个事务中运行的所有任务是交叉的,意味着在某一给定时间只能在该事务中运行一个任务。All tasks running in one transaction are interleaved, meaning that only one task can run under the transaction at a given time. 任务必须独占访问事务互斥体,才能运行。Before the task can run, it must have exclusive access to the transaction mutex.

    任务必须获取会话互斥体,才能在 MARS 下运行。In order for a task to run under MARS, it must acquire the session mutex. 如果任务在事务下运行,则它必须获取事务互斥体。If the task is running under a transaction, it must then acquire the transaction mutex. 这将确保在某一给定会话和给定事务中一次仅有一个任务处于活动状态。This guarantees that only one task is active at one time in a given session and a given transaction. 获取所需互斥体后,任务就可以执行了。Once the required mutexes have been acquired, the task can execute. 任务完成或在请求过程中生成时,它将按获取的相反顺序先释放事务互斥体,然后释放会话互斥体。When the task finishes, or yields in the middle of the request, it will first release transaction mutex followed by the session mutex in reverse order of acquisition. 但是,这些资源可能导致死锁。However, deadlocks can occur with these resources. 在下面的代码示例中,两个任务(用户请求 U1 和用户请求 U2)在同一会话中运行。In the following code example, two tasks, user request U1 and user request U2, are running in the same session.

    U1:    Rs1=Command1.Execute("insert sometable EXEC usp_someproc");  
    U2:    Rs2=Command2.Execute("select colA from sometable");  
    

    用户请求 U1 执行的存储过程已获取会话互斥体。The stored procedure executing from user request U1 has acquired the session mutex. 如果执行该存储过程花费了很长时间,则数据库引擎Database Engine会认为存储过程正在等待用户的输入。If the stored procedure takes a long time to execute, it is assumed by the 数据库引擎Database Engine that the stored procedure is waiting for input from the user. 用户等待 U2 的结果集时,用户请求 U2 正在等待会话互斥体,U1 正在等待用户资源。User request U2 is waiting for the session mutex while the user is waiting for the result set from U2, and U1 is waiting for a user resource. 死锁状态的逻辑说明如下:This is deadlock state logically illustrated as:

逻辑关系图显示用户进程死锁。Logic diagram showing user process deadlock.

死锁检测Deadlock Detection

上面列出的所有资源均参与数据库引擎Database Engine死锁检测方案。All of the resources listed in the section above participate in the 数据库引擎Database Engine deadlock detection scheme. 死锁检测是由锁监视器线程执行的,该线程定期搜索数据库引擎Database Engine实例的所有任务。Deadlock detection is performed by a lock monitor thread that periodically initiates a search through all of the tasks in an instance of the 数据库引擎Database Engine. 以下几点说明了搜索进程:The following points describe the search process:

  • 默认时间间隔为 5 秒。The default interval is 5 seconds.

  • 如果锁监视器线程查找死锁,根据死锁的频率,死锁检测时间间隔将从 5 秒开始减小,最小为 100 毫秒。If the lock monitor thread finds deadlocks, the deadlock detection interval will drop from 5 seconds to as low as 100 milliseconds depending on the frequency of deadlocks.

  • 如果锁监视器线程停止查找死锁,数据库引擎Database Engine将两个搜索间的时间间隔增加到 5 秒。If the lock monitor thread stops finding deadlocks, the 数据库引擎Database Engine increases the intervals between searches to 5 seconds.

  • 如果刚刚检测到死锁,则假定必须等待锁的下一个线程正进入死锁循环。If a deadlock has just been detected, it is assumed that the next threads that must wait for a lock are entering the deadlock cycle. 检测到死锁后,第一对锁等待将立即触发死锁搜索,而不是等待下一个死锁检测时间间隔。The first couple of lock waits after a deadlock has been detected will immediately trigger a deadlock search rather than wait for the next deadlock detection interval. 例如,如果当前时间间隔为 5 秒且刚刚检测到死锁,则下一个锁等待将立即触发死锁检测器。For example, if the current interval is 5 seconds, and a deadlock was just detected, the next lock wait will kick off the deadlock detector immediately. 如果锁等待是死锁的一部分,则将会立即检测它,而不是在下一个搜索期间才检测。If this lock wait is part of a deadlock, it will be detected right away rather than during next deadlock search.

通常,数据库引擎Database Engine仅定期执行死锁检测。The 数据库引擎Database Engine typically performs periodic deadlock detection only. 因为系统中遇到的死锁数通常很少,定期死锁检测有助于减少系统中死锁检测的开销。Because the number of deadlocks encountered in the system is usually small, periodic deadlock detection helps to reduce the overhead of deadlock detection in the system.

锁监视器对特定线程启动死锁搜索时,会标识线程正在等待的资源。When the lock monitor initiates deadlock search for a particular thread, it identifies the resource on which the thread is waiting. 然后,锁监视器查找特定资源的所有者,并递归地继续执行对那些线程的死锁搜索,直到找到一个循环。The lock monitor then finds the owner(s) for that particular resource and recursively continues the deadlock search for those threads until it finds a cycle. 用这种方式标识的循环形成一个死锁。A cycle identified in this manner forms a deadlock.

检测到死锁后,数据库引擎Database Engine通过选择其中一个线程作为死锁牺牲品来结束死锁。After a deadlock is detected, the 数据库引擎Database Engine ends a deadlock by choosing one of the threads as a deadlock victim. 数据库引擎Database Engine终止正为线程执行的当前批处理,回滚死锁牺牲品的事务并将 1205 错误返回到应用程序。The 数据库引擎Database Engine terminates the current batch being executed for the thread, rolls back the transaction of the deadlock victim, and returns a 1205 error to the application. 回滚死锁牺牲品的事务会释放事务持有的所有锁。Rolling back the transaction for the deadlock victim releases all locks held by the transaction. 这将使其他线程的事务解锁,并继续运行。This allows the transactions of the other threads to become unblocked and continue. 1205 死锁牺牲品错误将有关死锁涉及的线程和资源的信息记录在错误日志中。The 1205 deadlock victim error records information about the threads and resources involved in a deadlock in the error log.

默认情况下,数据库引擎Database Engine选择运行回滚的开销最小的事务的会话作为死锁牺牲品。By default, the 数据库引擎Database Engine chooses as the deadlock victim the session running the transaction that is least expensive to roll back. 此外,用户也可以使用 SET DEADLOCK_PRIORITY 语句指定死锁情况下会话的优先级。Alternatively, a user can specify the priority of sessions in a deadlock situation using the SET DEADLOCK_PRIORITY statement. 可以将 DEADLOCK_PRIORITY 设置为 LOW、NORMAL 或 HIGH,也可以将其设置为范围(-10 到 10)间的任一整数值。DEADLOCK_PRIORITY can be set to LOW, NORMAL, or HIGH, or alternatively can be set to any integer value in the range (-10 to 10). 死锁优先级的默认设置为 NORMAL。The deadlock priority defaults to NORMAL. 如果两个会话的死锁优先级不同,则会选择优先级较低的会话作为死锁牺牲品。If two sessions have different deadlock priorities, the session with the lower priority is chosen as the deadlock victim. 如果两个会话的死锁优先级相同,则会选择回滚的开销最低的事务的会话作为死锁牺牲品。If both sessions have the same deadlock priority, the session with the transaction that is least expensive to roll back is chosen. 如果死锁循环中会话的死锁优先级和开销都相同,则会随机选择死锁牺牲品。If sessions involved in the deadlock cycle have the same deadlock priority and the same cost, a victim is chosen randomly.

使用 CLR 时,死锁监视器将自动检测托管过程中访问的同步资源(监视器、读取器/编写器锁和线程联接)的死锁。When working with CLR, the deadlock monitor automatically detects deadlock for synchronization resources (monitors, reader/writer lock and thread join) accessed inside managed procedures. 但是,死锁是通过在已选为死锁牺牲品的过程中引发异常来解决的。However, the deadlock is resolved by throwing an exception in the procedure that was selected to be the deadlock victim. 因此,请务必理解异常不会自动释放牺牲品当前拥有的资源;必须显式释放资源。It is important to understand that the exception does not automatically release resources currently owned by the victim; the resources must be explicitly released. 用于标识死锁牺牲品的异常与异常行为一样,也会被捕获和解除。Consistent with exception behavior, the exception used to identify a deadlock victim can be caught and dismissed.

死锁信息工具Deadlock Information Tools

为了查看死锁信息,数据库引擎Database Engine提供了监视工具,分别为两个跟踪标志以及 SQL Server ProfilerSQL Server Profiler 中的死锁图形事件。To view deadlock information, the 数据库引擎Database Engine provides monitoring tools in the form of two trace flags, and the deadlock graph event in SQL Server ProfilerSQL Server Profiler.

跟踪标志 1204 和跟踪标志 1222Trace Flag 1204 and Trace Flag 1222

发生死锁时,跟踪标志 1204 和跟踪标志 1222 会返回在 SQL ServerSQL Server 错误日志中捕获的信息。When deadlocks occur, trace flag 1204 and trace flag 1222 return information that is captured in the SQL ServerSQL Server error log. 跟踪标志 1204 会报告由死锁所涉及的每个节点设置格式的死锁信息。Trace flag 1204 reports deadlock information formatted by each node involved in the deadlock. 跟踪标志 1222 会设置死锁信息的格式,顺序为先按进程,然后按资源。Trace flag 1222 formats deadlock information, first by processes and then by resources. 可以同时启用这两个跟踪标志,以获取同一个死锁事件的两种表示形式。It is possible to enable both trace flags to obtain two representations of the same deadlock event.

除了定义跟踪标志 1204 和 1222 的属性之外,下表还显示了它们之间的相似之处和不同之处。In addition to defining the properties of trace flag 1204 and 1222, the following table also shows the similarities and differences.

属性Property 跟踪标志 1204 和跟踪标志 1222Trace Flag 1204 and Trace Flag 1222 仅跟踪标志 1204Trace Flag 1204 only 仅跟踪标志 1222Trace Flag 1222 only
输出格式Output format SQL ServerSQL Server 错误日志中捕获输出。Output is captured in the SQL ServerSQL Server error log. 主要针对死锁所涉及的节点。Focused on the nodes involved in the deadlock. 每个节点都有一个专用部分,并且最后一部分说明死锁牺牲品。Each node has a dedicated section, and the final section describes the deadlock victim. 返回采用不符合 XML 架构定义 (XSD) 架构的类 XML 格式的信息。Returns information in an XML-like format that does not conform to an XML Schema Definition (XSD) schema. 该格式有三个主要部分。The format has three major sections. 第一部分声明死锁牺牲品;The first section declares the deadlock victim. 第二部分说明死锁所涉及的每个进程;The second section describes each process involved in the deadlock. 第三部分说明与跟踪标志 1204 中的节点同义的资源。The third section describes the resources that are synonymous with nodes in trace flag 1204.
标识属性Identifying attributes SPID:<x > ECID:<x >。SPID:<x> ECID:<x>. 标识并行进程中的系统进程 ID 线程。Identifies the system process ID thread in cases of parallel processes. 该条目SPID:<x> ECID:0,其中<x > 将替换为 SPID 值,表示主线程。The entry SPID:<x> ECID:0, where <x> is replaced by the SPID value, represents the main thread. 该条目SPID:<x> ECID:<y>,其中<x > 将替换为 SPID 值和<y > 大于 0,表示具有相同 spid 的子线程。The entry SPID:<x> ECID:<y>, where <x> is replaced by the SPID value and <y> is greater than 0, represents the sub-threads for the same SPID.

BatchID(对于跟踪标志 1222 为 sbid)。BatchID (sbid for trace flag 1222). 标识代码执行从中请求锁或持有锁的批处理。Identifies the batch from which code execution is requesting or holding a lock. 多个活动的结果集 (MARS) 禁用后,BatchID 值为 0。When Multiple Active Result Sets (MARS) is disabled, the BatchID value is 0. MARS 启用后,活动批处理的值为 1 到 n。When MARS is enabled, the value for active batches is 1 to n. 如果会话中没有活动的批处理,则 BatchID 为 0。If there are no active batches in the session, BatchID is 0.

模式Mode. 指定线程所请求的、获得的或等待的特定资源的锁的类型。Specifies the type of lock for a particular resource that is requested, granted, or waited on by a thread. 模式可以为 IS(意向共享)、S(共享)、U(更新)、IX(意向排他)、SIX(意向排他共享)和 X(排他)。Mode can be IS (Intent Shared), S (Shared), U (Update), IX (Intent Exclusive), SIX (Shared with Intent Exclusive), and X (Exclusive).

行编号(对于跟踪标志 1222 为行)。Line # (line for trace flag 1222). 列出发生死锁时当前批处理中正在执行的语句的行数。Lists the line number in the current batch of statements that was being executed when the deadlock occurred.

Input Buf(对于跟踪标志 1222 为 inputbuf)。Input Buf (inputbuf for trace flag 1222). 列出当前批处理中的所有语句。Lists all the statements in the current batch.
NodeNode. 表示死锁链中的项数。Represents the entry number in the deadlock chain.

ListLists. 锁所有者可能属于以下列表:The lock owner can be part of these lists:

Grant ListGrant List. 枚举资源的当前所有者。Enumerates the current owners of the resource.

Convert ListConvert List. 枚举尝试将其锁转换为较高级别的当前所有者。Enumerates the current owners that are trying to convert their locks to a higher level.

Wait ListWait List. 枚举对资源的当前新锁请求。Enumerates current new lock requests for the resource.

Statement TypeStatement Type. 说明线程对其具有权限的 DML 语句的类型(SELECT、INSERT、UPDATE 或 DELETE)。Describes the type of DML statement (SELECT, INSERT, UPDATE, or DELETE) on which the threads have permissions.

Victim Resource OwnerVictim Resource Owner. 指定 SQL ServerSQL Server 选择作为牺牲品来中断死锁循环的参与线程。Specifies the participating thread that SQL ServerSQL Server chooses as the victim to break the deadlock cycle. 选定的线程和所有的现有子线程都将终止。The chosen thread and all existing sub-threads are terminated.

Next BranchNext Branch. 表示死锁循环中涉及的两个或多个具有相同 SPID 的子线程。Represents the two or more sub-threads from the same SPID that are involved in the deadlock cycle.
deadlock victimdeadlock victim. 表示选为死锁牺牲品的任务的物理内存地址(请参阅 sys.dm_os_tasks (Transact-SQL))。Represents the physical memory address of the task (see sys.dm_os_tasks (Transact-SQL)) that was selected as a deadlock victim. 如果任务为无法解析的死锁,则它可能为 0(零)。It may be 0 (zero) in the case of an unresolved deadlock. 不能选择正在回滚的任务作为死锁牺牲品。A task that is rolling back cannot be chosen as a deadlock victim.

executionstackexecutionstack. 表示发生死锁时正在执行的 Transact-SQLTransact-SQL 代码。Represents Transact-SQLTransact-SQL code that is being executed at the time the deadlock occurs.

Prioritypriority. 表示死锁优先级。Represents deadlock priority. 在某些情况下,数据库引擎Database Engine可能在短时间内改变死锁优先级以更好地实现并发。In certain cases, the 数据库引擎Database Engine may opt to alter the deadlock priority for a short duration to achieve better concurrency.

logusedlogused. 任务使用的日志空间。Log space used by the task.

owner id。可控制请求的事务的 ID。owner id. The ID of the transaction that has control of the request.

statusstatus. 任务的状态。State of the task. 为下列值之一:It is one of the following values:

>> pending>> pending. 正在等待工作线程。Waiting for a worker thread.

>> runnable>> runnable. 可以运行,但正在等待量程。Ready to run but waiting for a quantum.

>> running>> running. 当前正在计划程序上运行。Currently running on the scheduler.

>> suspended>> suspended. 执行已挂起。Execution is suspended.

>> done>> done. 任务已完成。Task has completed.

>> spinloop>> spinloop. 正在等待自旋锁释放。Waiting for a spinlock to become free.

waitresourcewaitresource. 任务需要的资源。The resource needed by the task.

waittimewaittime. 等待资源的时间(毫秒)。Time in milliseconds waiting for the resource.

scheduleridschedulerid. 与此任务关联的计划程序。Scheduler associated with this task. 请参阅 sys.dm_os_schedulers (Transact-SQL)See sys.dm_os_schedulers (Transact-SQL).

hostnamehostname. 工作站的名称。The name of the workstation.

isolationlevelisolationlevel. 当前事务隔离级别。The current transaction isolation level.

XactidXactid. 可控制请求的事务的 ID。The ID of the transaction that has control of the request.

currentdbcurrentdb. 数据库的 ID。The ID of the database.

lastbatchstartedlastbatchstarted. 客户端进程上次启动批处理执行的时间。The last time a client process started batch execution.

lastbatchcompletedlastbatchcompleted. 客户端进程上次完成批处理执行的时间。The last time a client process completed batch execution.

clientoption1 和 clientoption2clientoption1 and clientoption2. 此客户端连接上的 Set 选项。Set options on this client connection. 这是一个位掩码,包含有关 SET 语句(如 SET NOCOUNT 和 SET XACTABORT)通常控制的选项的信息。This is a bitmask that includes information about options usually controlled by SET statements such as SET NOCOUNT and SET XACTABORT.

associatedObjectIdassociatedObjectId. 表示 HoBT(堆或 b 树)ID。Represents the HoBT (heap or b-tree) ID.
资源属性Resource attributes RIDRID. 标识持有锁或请求锁的表中的单行。Identifies the single row within a table on which a lock is held or requested. RID 表示为 RID: db_id:file_id:page_no:row_no。RID is represented as RID: db_id:file_id:page_no:row_no. 例如,RID: 6:1:20789:0For example, RID: 6:1:20789:0.

OBJECTOBJECT. 标识持有锁或请求锁的表。Identifies the table on which a lock is held or requested. OBJECT 表示为 OBJECT: db_id:object_id。OBJECT is represented as OBJECT: db_id:object_id. 例如,TAB: 6:2009058193For example, TAB: 6:2009058193.

KEYKEY. 标识持有锁或请求锁的索引中的键范围。Identifies the key range within an index on which a lock is held or requested. KEY 表示为 KEY: db_id:hobt_id(索引键哈希值)。KEY is represented as KEY: db_id:hobt_id (index key hash value). 例如,KEY: 6:72057594057457664 (350007a4d329)For example, KEY: 6:72057594057457664 (350007a4d329).

PAGPAG. 标识持有锁或请求锁的页资源。Identifies the page resource on which a lock is held or requested. PAG 表示为 PAG: db_id:file_id:page_no。PAG is represented as PAG: db_id:file_id:page_no. 例如,PAG: 6:1:20789For example, PAG: 6:1:20789.

EXTEXT. 标识区结构。Identifies the extent structure. EXT 表示为 EXT: db_id:file_id:extent_no。EXT is represented as EXT: db_id:file_id:extent_no. 例如,EXT: 6:1:9For example, EXT: 6:1:9.

DBDB. 标识数据库锁。Identifies the database lock. DB 以下列方式之一表示:DB is represented in one of the following ways:

DB: db_idDB: db_id

DB: db_id[BULK-OP-DB],这标识备份数据库持有的数据库锁。DB: db_id[BULK-OP-DB], which identifies the database lock taken by the backup database.

DB: db_id[BULK-OP-LOG],这标识此特定数据库的备份日志持有的锁。DB: db_id[BULK-OP-LOG], which identifies the lock taken by the backup log for that particular database.

APPAPP. 标识应用程序资源持有的锁。Identifies the lock taken by an application resource. APP 表示为 APP: lock_resource。APP is represented as APP: lock_resource. 例如,APP: Formf370f478For example, APP: Formf370f478.

METADATAMETADATA. 表示死锁所涉及的元数据资源。Represents metadata resources involved in a deadlock. 由于 METADATA 具有许多子资源,因此,返回的值取决于已发生死锁的子资源。Because METADATA has many subresources, the value returned depends upon the subresource that has deadlocked. 例如,元数据。返回 USER_TYPE user_type_id = < integer_value>。For example, METADATA.USER_TYPE returns user_type_id = <integer_value>. 有关 METADATA 资源和子资源的详细信息,请参阅 sys.dm_tran_locks (Transact-SQL)For more information about METADATA resources and subresources, see sys.dm_tran_locks (Transact-SQL).

HOBTHOBT. 表示死锁所涉及的堆或 b 树。Represents a heap or b-tree involved in a deadlock.
此跟踪标志没有任何排他。None exclusive to this trace flag. 此跟踪标志没有任何排他。None exclusive to this trace flag.
跟踪标志 1204 示例Trace Flag 1204 Example

下面的示例显示启用跟踪标志 1204 时的输出。The following example shows the output when trace flag 1204 is turned on. 在此示例中,节点 1 中的表为没有索引的堆,节点 2 中的表为具有非聚集索引的堆。In this case, the table in Node 1 is a heap with no indexes, and the table in Node 2 is a heap with a nonclustered index. 节点 2 中索引键在发生死锁时正在进行更新。The index key in Node 2 is being updated when the deadlock occurs.

Deadlock encountered .... Printing deadlock information  
Wait-for graph  
  
Node:1  
  
RID: 6:1:20789:0               CleanCnt:3 Mode:X Flags: 0x2  
 Grant List 0:  
   Owner:0x0315D6A0 Mode: X          
     Flg:0x0 Ref:0 Life:02000000 SPID:55 ECID:0 XactLockInfo: 0x04D9E27C  
   SPID: 55 ECID: 0 Statement Type: UPDATE Line #: 6  
   Input Buf: Language Event:   
BEGIN TRANSACTION  
   EXEC usp_p2  
 Requested By:   
   ResType:LockOwner Stype:'OR'Xdes:0x03A3DAD0   
     Mode: U SPID:54 BatchID:0 ECID:0 TaskProxy:(0x04976374) Value:0x315d200 Cost:(0/868)  
  
Node:2  
  
KEY: 6:72057594057457664 (350007a4d329) CleanCnt:2 Mode:X Flags: 0x0  
 Grant List 0:  
   Owner:0x0315D140 Mode: X          
     Flg:0x0 Ref:0 Life:02000000 SPID:54 ECID:0 XactLockInfo: 0x03A3DAF4  
   SPID: 54 ECID: 0 Statement Type: UPDATE Line #: 6  
   Input Buf: Language Event:   
     BEGIN TRANSACTION  
       EXEC usp_p1  
 Requested By:   
   ResType:LockOwner Stype:'OR'Xdes:0x04D9E258   
     Mode: U SPID:55 BatchID:0 ECID:0 TaskProxy:(0x0475E374) Value:0x315d4a0 Cost:(0/380)  
  
Victim Resource Owner:  
 ResType:LockOwner Stype:'OR'Xdes:0x04D9E258   
     Mode: U SPID:55 BatchID:0 ECID:0 TaskProxy:(0x0475E374) Value:0x315d4a0 Cost:(0/380)  
跟踪标志 1222 示例Trace Flag 1222 Example

下面的示例显示启用跟踪标志 1222 时的输出。The following example shows the output when trace flag 1222 is turned on. 在此示例中,一个表为没有索引的堆,另一个表为具有非聚集索引的堆。In this case, one table is a heap with no indexes, and the other table is a heap with a nonclustered index. 在第二个表中,索引键在发生死锁时正在进行更新。In the second table, the index key is being updated when the deadlock occurs.

deadlock-list  
 deadlock victim=process689978  
  process-list  
   process id=process6891f8 taskpriority=0 logused=868   
   waitresource=RID: 6:1:20789:0 waittime=1359 ownerId=310444   
   transactionname=user_transaction   
   lasttranstarted=2005-09-05T11:22:42.733 XDES=0x3a3dad0   
   lockMode=U schedulerid=1 kpid=1952 status=suspended spid=54   
   sbid=0 ecid=0 priority=0 transcount=2   
   lastbatchstarted=2005-09-05T11:22:42.733   
   lastbatchcompleted=2005-09-05T11:22:42.733   
   clientapp=Microsoft SQL Server Management Studio - Query   
   hostname=TEST_SERVER hostpid=2216 loginname=DOMAIN\user   
   isolationlevel=read committed (2) xactid=310444 currentdb=6   
   lockTimeout=4294967295 clientoption1=671090784 clientoption2=390200  
    executionStack  
     frame procname=AdventureWorks2012.dbo.usp_p1 line=6 stmtstart=202   
     sqlhandle=0x0300060013e6446b027cbb00c69600000100000000000000  
     UPDATE T2 SET COL1 = 3 WHERE COL1 = 1;       
     frame procname=adhoc line=3 stmtstart=44   
     sqlhandle=0x01000600856aa70f503b8104000000000000000000000000  
     EXEC usp_p1       
    inputbuf  
      BEGIN TRANSACTION  
       EXEC usp_p1  
   process id=process689978 taskpriority=0 logused=380   
   waitresource=KEY: 6:72057594057457664 (350007a4d329)     
   waittime=5015 ownerId=310462 transactionname=user_transaction   
   lasttranstarted=2005-09-05T11:22:44.077 XDES=0x4d9e258 lockMode=U   
   schedulerid=1 kpid=3024 status=suspended spid=55 sbid=0 ecid=0   
   priority=0 transcount=2 lastbatchstarted=2005-09-05T11:22:44.077   
   lastbatchcompleted=2005-09-05T11:22:44.077   
   clientapp=Microsoft SQL Server Management Studio - Query   
   hostname=TEST_SERVER hostpid=2216 loginname=DOMAIN\user   
   isolationlevel=read committed (2) xactid=310462 currentdb=6   
   lockTimeout=4294967295 clientoption1=671090784 clientoption2=390200  
    executionStack  
     frame procname=AdventureWorks2012.dbo.usp_p2 line=6 stmtstart=200   
     sqlhandle=0x030006004c0a396c027cbb00c69600000100000000000000  
     UPDATE T1 SET COL1 = 4 WHERE COL1 = 1;       
     frame procname=adhoc line=3 stmtstart=44   
     sqlhandle=0x01000600d688e709b85f8904000000000000000000000000  
     EXEC usp_p2       
    inputbuf  
      BEGIN TRANSACTION  
        EXEC usp_p2      
  resource-list  
   ridlock fileid=1 pageid=20789 dbid=6 objectname=AdventureWorks2012.dbo.T2   
   id=lock3136940 mode=X associatedObjectId=72057594057392128  
    owner-list  
     owner id=process689978 mode=X  
    waiter-list  
     waiter id=process6891f8 mode=U requestType=wait  
   keylock hobtid=72057594057457664 dbid=6 objectname=AdventureWorks2012.dbo.T1   
   indexname=nci_T1_COL1 id=lock3136fc0 mode=X   
   associatedObjectId=72057594057457664  
    owner-list  
     owner id=process6891f8 mode=X  
    waiter-list  
     waiter id=process689978 mode=U requestType=wait  
事件探查器死锁图形事件Profiler Deadlock Graph Event

这是 SQL Server ProfilerSQL Server Profiler中表示死锁所涉及的任务和资源的图形描述的事件。This is an event in SQL Server ProfilerSQL Server Profiler that presents a graphical depiction of the tasks and resources involved in a deadlock. 下面的示例显示启用死锁图形事件时 SQL Server ProfilerSQL Server Profiler 的输出。The following example shows the output from SQL Server ProfilerSQL Server Profiler when the deadlock graph event is turned on.

逻辑流关系图显示用户进程死锁。Logic flow diagram showing user process deadlock.

有关运行详细信息SQL Server ProfilerSQL Server Profiler死锁图,请参阅保存死锁图形(SQL Server Profiler)For more information about running the SQL Server ProfilerSQL Server Profiler deadlock graph, see Save Deadlock Graphs (SQL Server Profiler).

处理死锁Handling Deadlocks

SQL Server 数据库引擎SQL Server Database Engine 实例选择某事务作为死锁牺牲品时,将终止当前批处理,回滚事务并将错误消息 1205 返回应用程序。When an instance of the SQL Server 数据库引擎SQL Server Database Engine chooses a transaction as a deadlock victim, it terminates the current batch, rolls back the transaction, and returns error message 1205 to the application.

Your transaction (process ID #52) was deadlocked on {lock | communication buffer | thread} resources with another process and has been chosen as the deadlock victim. Rerun your transaction.

因为任何提交 Transact-SQLTransact-SQL 查询的应用程序可被选为死锁牺牲品,应用程序应具有可处理错误消息 1205 的错误处理程序。Because any application submitting Transact-SQLTransact-SQL queries can be chosen as the deadlock victim, applications should have an error handler that can trap error message 1205. 如果应用程序不处理该错误,可以继续操作,但是不知道自己的事务已回滚而且可能出错。If an application does not trap the error, the application can proceed unaware that its transaction has been rolled back and errors can occur.

通过实现捕获 1205 号错误消息的错误处理程序,使应用程序得以处理该死锁情况并采取补救措施(例如,可以自动重新提交陷入死锁中的查询)。Implementing an error handler that traps error message 1205 allows an application to handle the deadlock situation and take remedial action (for example, automatically resubmitting the query that was involved in the deadlock). 通过自动重新提交查询,用户不必知道发生了死锁。By resubmitting the query automatically, the user does not need to know that a deadlock occurred.

应用程序在重新提交其查询前应短暂暂停。The application should pause briefly before resubmitting its query. 这样会给死锁涉及的另一个事务一个机会来完成并释放构成死锁循环一部分的该事务的锁。This gives the other transaction involved in the deadlock a chance to complete and release its locks that formed part of the deadlock cycle. 这将把重新提交的查询请求其锁时,死锁重新发生的可能性降到最低。This minimizes the likelihood of the deadlock reoccurring when the resubmitted query requests its locks.

将死锁减至最少Minimizing Deadlocks

尽管死锁不能完全避免,但遵守特定的编码惯例可以将发生死锁的机会降至最低。Although deadlocks cannot be completely avoided, following certain coding conventions can minimize the chance of generating a deadlock. 将死锁减至最少可以增加事务的吞吐量并减少系统开销,因为只有很少的事务:Minimizing deadlocks can increase transaction throughput and reduce system overhead because fewer transactions are:

  • 回滚,撤消事务执行的所有工作。Rolled back, undoing all the work performed by the transaction.

  • 由于死锁时回滚而由应用程序重新提交。Resubmitted by applications because they were rolled back when deadlocked.

下列方法有助于将死锁减至最少:To help minimize deadlocks:

  • 按同一顺序访问对象。Access objects in the same order.

  • 避免事务中的用户交互。Avoid user interaction in transactions.

  • 保持事务简短并处于一个批处理中。Keep transactions short and in one batch.

  • 使用较低的隔离级别。Use a lower isolation level.

  • 使用基于行版本控制的隔离级别。Use a row versioning-based isolation level.

    • 将 READ_COMMITTED_SNAPSHOT 数据库选项设置为 ON,使得已提交读事务使用行版本控制。Set READ_COMMITTED_SNAPSHOT database option ON to enable read-committed transactions to use row versioning.

    • 使用快照隔离。Use snapshot isolation.

  • 使用绑定连接。Use bound connections.

按同一顺序访问对象Access Objects in the Same Order

如果所有并发事务按同一顺序访问对象,则发生死锁的可能性会降低。If all concurrent transactions access objects in the same order, deadlocks are less likely to occur. 例如,如果两个并发事务先获取 Supplier 表上的锁,然后获取 Part 表上的锁,则在其中一个事务完成之前,另一个事务将在 Supplier 表上被阻塞。For example, if two concurrent transactions obtain a lock on the Supplier table and then on the Part table, one transaction is blocked on the Supplier table until the other transaction is completed. 当第一个事务提交或回滚之后,第二个事务将继续执行,这样就不会发生死锁。After the first transaction commits or rolls back, the second continues, and a deadlock does not occur. 将存储过程用于所有数据修改可以使对象的访问顺序标准化。Using stored procedures for all data modifications can standardize the order of accessing objects.

显示避免死锁的关系图Diagram showing deadlock avoidance

避免事务中的用户交互Avoid User Interaction in Transactions

避免编写包含用户交互的事务,因为没有用户干预的批处理的运行速度远快于用户必须手动响应查询时的速度(例如回复输入应用程序请求的参数的提示)。Avoid writing transactions that include user interaction, because the speed of batches running without user intervention is much faster than the speed at which a user must manually respond to queries, such as replying to a prompt for a parameter requested by an application. 例如,如果事务正在等待用户输入,而用户去吃午餐或甚至回家过周末了,则用户就耽误了事务的完成。For example, if a transaction is waiting for user input and the user goes to lunch or even home for the weekend, the user delays the transaction from completing. 这将降低系统的吞吐量,因为事务持有的任何锁只有在事务提交或回滚后才会释放。This degrades system throughput because any locks held by the transaction are released only when the transaction is committed or rolled back. 即使不出现死锁的情况,在占用资源的事务完成之前,访问同一资源的其他事务也会被阻塞。Even if a deadlock situation does not arise, other transactions accessing the same resources are blocked while waiting for the transaction to complete.

保持事务简短并处于一个批处理中Keep Transactions Short and in One Batch

在同一数据库中并发执行多个需要长时间运行的事务时通常会发生死锁。A deadlock typically occurs when several long-running transactions execute concurrently in the same database. 事务的运行时间越长,它持有排他锁或更新锁的时间也就越长,从而会阻塞其他活动并可能导致死锁。The longer the transaction, the longer the exclusive or update locks are held, blocking other activity and leading to possible deadlock situations.

保持事务处于一个批处理中可以最小化事务中的网络通信往返量,减少完成事务和释放锁可能遭遇的延迟。Keeping transactions in one batch minimizes network roundtrips during a transaction, reducing possible delays in completing the transaction and releasing locks.

使用较低的隔离级别Use a Lower Isolation Level

确定事务是否能在较低的隔离级别上运行。Determine whether a transaction can run at a lower isolation level. 实现已提交读允许事务读取另一个事务已读取(未修改)的数据,而不必等待第一个事务完成。Implementing read committed allows a transaction to read data previously read (not modified) by another transaction without waiting for the first transaction to complete. 使用较低的隔离级别(例如已提交读)比使用较高的隔离级别(例如可序列化)持有共享锁的时间更短。Using a lower isolation level, such as read committed, holds shared locks for a shorter duration than a higher isolation level, such as serializable. 这样就减少了锁争用。This reduces locking contention.

使用基于行版本控制的隔离级别Use a Row Versioning-based Isolation Level

如果将 READ_COMMITTED_SNAPSHOT 数据库选项设置为 ON,则在已提交读隔离级别下运行的事务在读操作期间将使用行版本控制而不是共享锁。When the READ_COMMITTED_SNAPSHOT database option is set ON, a transaction running under read committed isolation level uses row versioning rather than shared locks during read operations.

备注

某些应用程序依赖于已提交读隔离的锁定和阻塞行为。Some applications rely upon locking and blocking behavior of read committed isolation. 对于这些应用程序,要启用此选项必须进行一些更改。For these applications, some change is required before this option can be enabled.

快照隔离也使用行版本控制,该级别在读操作期间不使用共享锁。Snapshot isolation also uses row versioning, which does not use shared locks during read operations. 必须将 ALLOW_SNAPSHOT_ISOLATION 数据库选项设置为 ON,事务才能在快照隔离下运行。Before a transaction can run under snapshot isolation, the ALLOW_SNAPSHOT_ISOLATION database option must be set ON.

实现这些隔离级别可使得在读写操作之间发生死锁的可能性降至最低。Implement these isolation levels to minimize deadlocks that can occur between read and write operations.

使用绑定连接Use Bound Connections

使用绑定连接,同一应用程序打开的两个或多个连接可以相互合作。Using bound connections, two or more connections opened by the same application can cooperate with each other. 可以像主连接获取的锁那样持有次级连接获取的任何锁,反之亦然。Any locks acquired by the secondary connections are held as if they were acquired by the primary connection, and vice versa. 这样它们就不会互相阻塞。Therefore they do not block each other.

锁分区Lock Partitioning

对于大型计算机系统,在经常被引用的对象上放置的锁可能会变成性能瓶颈,因为获取和释放锁对内部锁资源造成了争用。For large computer systems, locks on frequently referenced objects can become a performance bottleneck as acquiring and releasing locks place contention on internal locking resources. 锁分区通过将单个锁资源拆分为多个锁资源而提高了锁性能。Lock partitioning enhances locking performance by splitting a single lock resource into multiple lock resources. 此功能只适用于拥有 16 个或更多 CPU 的系统,它是自动启用的,而且无法禁用。This feature is only available for systems with 16 or more CPUs, and is automatically enabled and cannot be disabled. 只有对象锁可以分区。拥有子类型的对象锁不能分区。Only object locks can be partitioned.Object locks that have a subtype are not partitioned. 有关详细信息,请参阅 sys.dm_tran_locks (Transact-SQL)For more information, see sys.dm_tran_locks (Transact-SQL).

了解锁分区Understanding Lock Partitioning

锁任务访问几个共享资源,其中两个通过锁分区进行优化:Locking tasks access several shared resources, two of which are optimized by lock partitioning:

  • 调节锁Spinlock. 它控制对锁资源(例如行或表)的访问。This controls access to a lock resource, such as a row or a table.

    不进行锁分区,一个调节锁就得管理单个锁资源的所有锁请求。Without lock partitioning, one spinlock manages all lock requests for a single lock resource. 在具有大量活动的系统上,在锁请求等待释放调节锁时会出现资源争用的现象。On systems that experience a large volume of activity, contention can occur as lock requests wait for the spinlock to become available. 在这种情况下,获取锁可能变成了一个瓶颈,并且可能会对性能造成负面影响。Under this situation, acquiring locks can become a bottleneck and can negatively impact performance.

    为了减少对单个锁资源的争用,锁分区将单个锁资源拆分成多个锁资源,以便将负荷分布到多个调节锁上。To reduce contention on a single lock resource, lock partitioning splits a single lock resource into multiple lock resources to distribute the load across multiple spinlocks.

  • 内存Memory. 它用于存储锁资源结构。This is used to store the lock resource structures.

    获取调节锁后,锁结构将存储在内存中,然后即可对其进行访问和可能的修改。Once the spinlock is acquired, lock structures are stored in memory and then accessed and possibly modified. 将锁访问分布到多个资源中有助于消除在 CPU 之间传输内存块的需要,这有助于提高性能。Distributing lock access across multiple resources helps to eliminate the need to transfer memory blocks between CPUs, which will help to improve performance.

实现和监视锁分区Implementing and Monitoring Lock Partitioning

默认情况下,对于具有 16 个或更多 CPU 的系统,锁分区是打开的。Lock partitioning is turned on by default for systems with 16 or more CPUs. 启用锁分区后,将在 SQL ServerSQL Server 错误日志中记录一条信息性消息。When lock partitioning is enabled, an informational message is recorded in the SQL ServerSQL Server error log.

获取已分区资源的锁时:When acquiring locks on a partitioned resource:

  • 只能获取单个分区的 NL、SCH-S、IS、IU 和 IX 锁模式。Only NL, SCH-S, IS, IU, and IX lock modes are acquired on a single partition.

  • 对于以分区 ID 0 开始并且按照分区 ID 顺序排列的所有分区,必须获取非 NL、SCH-S、IS、IU 和 IX 模式的共享锁 (S)、排他锁 (X) 和其他锁。Shared (S), exclusive (X), and other locks in modes other than NL, SCH-S, IS, IU, and IX must be acquired on all partitions starting with partition ID 0 and following in partition ID order. 已分区资源的这些锁将比相同模式中未分区资源的锁占用更多的内存,因为每个分区都是一个有效的单独锁。These locks on a partitioned resource will use more memory than locks in the same mode on a non-partitioned resource since each partition is effectively a separate lock. 内存的增加由分区数决定。The memory increase is determined by the number of partitions. Windows 性能监视器中 SQL ServerSQL Server 锁计数器将显示已分区和未分区锁所使用内存信息。The SQL ServerSQL Server lock counters in the Windows Performance Monitor will display information about memory used by partitioned and non-partitioned locks.

启动一个事务时,它将被分配给一个分区。A transaction is assigned to a partition when the transaction starts. 对于此事务,可以分区的所有锁请求都使用分配给该事务的分区。For the transaction, all lock requests that can be partitioned use the partition assigned to that transaction. 按照此方法,不同事务对相同对象的锁资源的访问被分布到不同的分区中。By this method, access to lock resources of the same object by different transactions is distributed across different partitions.

sys.dm_tran_locks 动态管理视图中的 resource_lock_partition 列为锁分区资源提供锁分区 ID。The resource_lock_partition column in the sys.dm_tran_locks Dynamic Management View provides the lock partition ID for a lock partitioned resource. 有关详细信息,请参阅 sys.dm_tran_locks (Transact-SQL)For more information, see sys.dm_tran_locks (Transact-SQL).

SQL Server ProfilerSQL Server Profiler 中的 Locks 事件下,BigintData1 列为每个锁分区资源提供锁分区 ID。Under the Locks event in SQL Server ProfilerSQL Server Profiler, the BigintData1 column provides the lock partition ID for a lock partitioned resource.

使用锁分区Working with Lock Partitioning

以下代码示例说明了锁分区。The following code examples illustrate lock partitioning. 在这些示例中,为了显示一个具有 16 个 CPU 的计算机系统上的锁分区行为,在两个不同的会话中执行了两个事务。In the examples, two transactions are executed in two different sessions in order to show lock partitioning behavior on a computer system with 16 CPUs.

这些 Transact-SQLTransact-SQL 语句创建了后续示例中使用的测试对象。These Transact-SQLTransact-SQL statements create test objects that are used in the examples that follow.

-- Create a test table.  
CREATE TABLE TestTable  
    (col1        int);  
GO  
  
-- Create a clustered index on the table.  
CREATE CLUSTERED INDEX ci_TestTable   
    ON TestTable (col1);  
GO  
  
-- Populate the table.  
INSERT INTO TestTable VALUES (1);  
GO  
示例 AExample A

会话 1:Session 1:

在一个事务中执行一个 SELECT 语句。A SELECT statement is executed under a transaction. 由于 HOLDLOCK 锁提示的原因,此语句将获取并保留一个对此表的意向共享锁(IS 锁)(此例中忽略行锁和页锁)。Because of the HOLDLOCK lock hint, this statement will acquire and retain an Intent shared (IS) lock on the table (for this illustration, row and page locks are ignored). IS 锁只能在分配给事务的分区中获取。The IS lock will be acquired only on the partition assigned to the transaction. 对于此示例,假定 IS 锁是在 ID 为 7 的分区中获取的。For this example, it is assumed that the IS lock is acquired on partition 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:Session 2:

启动事务,在此事务下运行 SELECT 语句将获取共享锁(S 锁)并将其保留在表中。A transaction is started, and the SELECT statement running under this transaction will acquire and retain a shared (S) lock on the table. 将获取所有分区的 S 锁,这将产生多个表锁,每个分区一个。The S lock will be acquired on all partitions which results in multiple table locks, one for each partition. 例如,在具有 16 个 cpu 的系统上,将对锁分区 ID 为 0-15 的锁分区发出 16 个 S 锁。For example, on a 16-cpu system, 16 S locks will be issued across lock partition IDs 0-15. 由于 S 锁与分区 ID 7 上由会话 1 中的事务持有的 IS 锁兼容,因此事务之间没有阻塞。Because the S lock is compatible with the IS lock being held on partition ID 7 by the transaction in session 1, there is no blocking between transactions.

BEGIN TRANSACTION  
    SELECT col1  
        FROM TestTable  
        WITH (TABLOCK, HOLDLOCK);  

会话 1:Session 1:

将在会话 1 下仍处于活动状态的事务下执行以下 SELECT 语句。The following SELECT statement is executed under the transaction that is still active under session 1. 由于排他 (X) 表锁提示,因此事务将尝试获取该表的 X 锁。Because of the exclusive (X) table lock hint, the transaction will attempt to acquire an X lock on the table. 但是,由会话 2 中的事务持有的 S 锁将阻塞分区 ID 0 的 X 锁。However, the S lock that is being held by the transaction in session 2 will block the X lock at partition ID 0.

SELECT col1  
    FROM TestTable  
    WITH (TABLOCKX);  
示例 BExample B

会话 1:Session 1:

在一个事务中执行一个 SELECT 语句。A SELECT statement is executed under a transaction. 由于 HOLDLOCK 锁提示的原因,此语句将获取并保留一个对此表的意向共享锁(IS 锁)(此例中忽略行锁和页锁)。Because of the HOLDLOCK lock hint, this statement will acquire and retain an Intent shared (IS) lock on the table (for this illustration, row and page locks are ignored). IS 锁只能在分配给事务的分区中获取。The IS lock will be acquired only on the partition assigned to the transaction. 对于此示例,假定 IS 锁是在 ID 为 6 的分区中获取的。For this example, it is assumed that the IS lock is acquired on partition 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:Session 2:

在一个事务中执行一个 SELECT 语句。A SELECT statement is executed under a transaction. 由于 TABLOCKX 锁提示,事务将尝试获取表的排他锁(X 锁)。Because of the TABLOCKX lock hint, the transaction tries to acquire an exclusive (X) lock on the table. 请记住,必须获取从分区 ID 0 开始的所有分区的 X 锁。Remember that the X lock must be acquired on all partitions starting with partition ID 0. 将获取所有分区 ID 0-5 的 X 锁,但它会被为分区 ID 6 获取的 IS 锁阻塞。The X lock will be acquired on all partitions IDs 0-5 but will be blocked by the IS lock that is acquired on partition ID 6.

对于尚未获取 X 锁的分区 ID 7-15,其他事务可以继续获取锁。On partition IDs 7-15 that the X lock has not yet reached, other transactions can continue to acquire locks.

BEGIN TRANSACTION  
    SELECT col1  
        FROM TestTable  
        WITH (TABLOCKX, HOLDLOCK);  

使用顶部的链接的箭头图标中此指南Arrow icon used with Back to Top link In This Guide

数据库引擎中的行基于版本控制的隔离级别Row Versioning-based Isolation Levels in the Database Engine

从 SQL Server 2005 开始,数据库引擎提供现有事务隔离级别(已提交读)的实现,该实现使用行版本控制提供语句级快照。Starting with SQL Server 2005, the Database Engine offers an implementation of an existing transaction isolation level, read committed, that provides a statement level snapshot using row versioning. SQL Server 数据库引擎还提供一个事务隔离级别(快照),该级别也使用行版本控制提供事务级快照。SQL Server Database Engine also offers a transaction isolation level, snapshot, that provides a transaction level snapshot also using row versioning.

行版本控制是 SQL ServerSQL Server 中的一般框架,它在修改或删除行时调用写入时复制机制。Row versioning is a general framework in SQL ServerSQL Server that invokes a copy-on-write mechanism when a row is modified or deleted. 这要求在运行事务时,行的旧版本必须可供需要早先事务一致状态的事务使用。This requires that while the transaction is running, the old version of the row must be available for transactions that require an earlier transactionally consistent state. 行版本控制可用于执行以下操作:Row versioning is used to do the following:

  • 在触发器中生成插入的和删除的表。Build the inserted and deleted tables in triggers. 对任何由触发器修改的行都将生成副本。Any rows modified by the trigger are versioned. 这包括由启动触发器的语句修改的行,以及由触发器进行的任何数据修改。This includes the rows modified by the statement that launched the trigger, as well as any data modifications made by the trigger.

  • 支持多个活动的结果集 (MARS)。Support Multiple Active Result Sets (MARS). 如果 MARS 会话发出一条数据修改语句(例如 INSERT、UPDATE 或 DELETE)时存在活动的结果集,则对受修改语句影响的行都将生成副本。If a MARS session issues a data modification statement (such as INSERT, UPDATE, or DELETE) at a time there is an active result set, the rows affected by the modification statement are versioned.

  • 支持指定 ONLINE 选项的索引操作。Support index operations that specify the ONLINE option.

  • 支持基于行版本控制的事务隔离级别:Support row versioning-based transaction isolation levels:

    • 新实现的已提交读隔离级别,使用行版本控制提供语句级的读取一致性。A new implementation of read committed isolation level that uses row versioning to provide statement-level read consistency.

    • 新快照隔离级别,提供事务级的读取一致性。A new isolation level, snapshot, to provide transaction-level read consistency.

tempdb 数据库必须具有足够的空间用于版本存储区。The tempdb database must have enough space for the version store. tempdb 已满的情况下,更新操作将停止生成版本,并继续执行,但是因为所需的特定行版本不再存在,读取操作可能会失败。When tempdb is full, update operations will stop generating versions and continue to succeed, but read operations might fail because a particular row version that is needed no longer exists. 这将影响诸如触发器、MARS 和联机索引的操作。This affects operations like triggers, MARS, and online indexing.

已提交读和快照事务的行版本控制的使用过程分为两个步骤:Using row versioning for read-committed and snapshot transactions is a two-step process:

  1. 将 READ_COMMITTED_SNAPSHOT 和/或 ALLOW_SNAPSHOT_ISOLATION 数据库选项设置为 ON。Set either or both the READ_COMMITTED_SNAPSHOT and ALLOW_SNAPSHOT_ISOLATION database options ON.

  2. 在应用程序中设置相应的事务隔离级别:Set the appropriate transaction isolation level in an application:

    • 当 READ_COMMITTED_SNAPSHOT 数据库选项设置为 ON 时,设置已提交读隔离级别的事务使用行版本控制。When the READ_COMMITTED_SNAPSHOT database option is ON, transactions setting the read committed isolation level use row versioning.

    • 当 ALLOW_SNAPSHOT_ISOLATION 数据库选项设置为 ON 时,事务可以设置快照隔离级别。When the ALLOW_SNAPSHOT_ISOLATION database option is ON, transactions can set the snapshot isolation level.

当 READ_COMMITTED_SNAPSHOT 或 ALLOW_SNAPSHOT_ISOLATION 数据库选项设置为 ON 时,SQL Server 数据库引擎SQL Server Database Engine向使用行版本控制操作数据的每个事务分配一个事务序列号 (XSN)。When either READ_COMMITTED_SNAPSHOT or ALLOW_SNAPSHOT_ISOLATION database option is set ON, the SQL Server 数据库引擎SQL Server Database Engine assigns a transaction sequence number (XSN) to each transaction that manipulates data using row versioning. 事务在执行 BEGIN TRANSACTION 语句时启动。Transactions start at the time a BEGIN TRANSACTION statement is executed. 但是,事务序列号在执行 BEGIN TRANSACTION 语句后的第一次读/写操作时开始增加。However, the transaction sequence number starts with the first read or write operation after the BEGIN TRANSACTION statement. 事务序列号在每次分配时都增加 1。The transaction sequence number is incremented by one each time it is assigned.

当 READ_COMMITTED_SNAPSHOT 或 ALLOW_SNAPSHOT_ISOLATION 数据库选项设置为 ON 时,将维护所有在数据库中执行的数据修改的逻辑副本(版本)。When either the READ_COMMITTED_SNAPSHOT or ALLOW_SNAPSHOT_ISOLATION database options are ON, logical copies (versions) are maintained for all data modifications performed in the database. 特定的事务每次修改行时,数据库引擎Database Engine 实例都存储以前提交的tempdb 中行的图像版本。Every time a row is modified by a specific transaction, the instance of the 数据库引擎Database Engine stores a version of the previously committed image of the row in tempdb. 每个版本都标记有进行此更改的事务的事务序列号。Each version is marked with the transaction sequence number of the transaction that made the change. 已修改行的版本使用链接列表链接在一起。The versions of modified rows are chained using a link list. 最新的行值始终存储在当前数据库中,并与 tempdb 中存储的版本控制行链接在一起。The newest row value is always stored in the current database and chained to the versioned rows stored in tempdb.

备注

修改大型对象 (LOB) 时,只有已更改的片段才会复制到 tempdb 中的版本存储区。For modification of large objects (LOBs), only the changed fragment is copied to the version store in tempdb.

行版本将保持足够长的时间,以满足在基于行版本控制的隔离级别下运行的事务的要求。Row versions are held long enough to satisfy the requirements of transactions running under row versioning-based isolation levels. 数据库引擎Database Engine跟踪最早的可用事务序列号,并定期删除带有比最早使用的可用序列号更低的事务序列号的所有行版本。The 数据库引擎Database Engine tracks the earliest useful transaction sequence number and periodically deletes all row versions stamped with transaction sequence numbers that are lower than the earliest useful sequence number.

两个数据库选项都设置为 OFF 时,只对由触发器或 MARS 会话修改的行或由联机索引操作读取的行生成副本。When both database options are set to OFF, only rows modified by triggers or MARS sessions, or read by ONLINE index operations, are versioned. 这些行版本将在不再需要时被释放。Those row versions are released when no longer needed. 后台线程会定期执行来删除陈旧的行版本。A background thread periodically executes to remove stale row versions.

备注

对于短期运行的事务,已修改行的版本将可能缓存在缓冲池中,而不会写入 tempdb 数据库的磁盘文件中。For short-running transactions, a version of a modified row may get cached in the buffer pool without getting written into the disk files of the tempdb database. 如果只是临时需要副本行,它将只是简单地从缓冲池中删除而不会引发 I/O 开销。If the need for the versioned row is short-lived, it will simply get dropped from the buffer pool and may not necessarily incur I/O overhead.

读取数据时的行为Behavior When Reading Data

当在基于行版本控制的隔离下运行的事务读取数据时,读取操作不会获取正被读取的数据上的共享锁(S 锁),因此不会阻塞正在修改数据的事务。When transactions running under row versioning-based isolation read data, the read operations do not acquire shared (S) locks on the data being read, and therefore do not block transactions that are modifying data. 同时,由于减少了所获取的锁的数量,因此最大程度地降低了锁定资源的开销。Also, the overhead of locking resources is minimized as the number of locks acquired is reduced. 使用行版本控制的已提交读隔离和快照隔离旨在提供副本数据的语句级或事务级读取一致性。Read committed isolation using row versioning and snapshot isolation are designed to provide statement-level or transaction-level read consistencies of versioned data.

所有查询,包括在基于行版本控制的隔离级别下运行的事务,都在编译和执行期间获取 Sch-S(架构稳定性)锁。All queries, including transactions running under row versioning-based isolation levels, acquire Sch-S (schema stability) locks during compilation and execution. 因此,当并发事务持有表的 Sch-M(架构修改)锁时,将阻塞查询。Because of this, queries are blocked when a concurrent transaction holds a Sch-M (schema modification) lock on the table. 例如,数据定义语言 (DDL) 操作在修改表的架构信息之前获取 Sch-M 锁。For example, a data definition language (DDL) operation acquires a Sch-M lock before it modifies the schema information of the table. 查询事务,包括在基于行版本控制的隔离级别下运行的事务,都会在尝试获取 Sch-S 锁时被阻塞。Query transactions, including those running under a row versioning-based isolation level, are blocked when attempting to acquire a Sch-S lock. 相反,持有 Sch-S 锁的查询将阻塞尝试获取 Sch-M 锁的并发事务。Conversely, a query holding a Sch-S lock blocks a concurrent transaction that attempts to acquire a Sch-M lock.

当使用快照隔离级别的事务启动时,数据库引擎Database Engine实例将记录所有当前活动的事务。When a transaction using the snapshot isolation level starts, the instance of the 数据库引擎Database Engine records all of the currently active transactions. 当快照事务读取具有版本链的行时,数据库引擎Database Engine按照该链检索行,其事务序列号为:When the snapshot transaction reads a row that has a version chain, the 数据库引擎Database Engine follows the chain and retrieves the row where the transaction sequence number is:

  • 最接近但低于读取行的快照事务序列号。Closest to but lower than the sequence number of the snapshot transaction reading the row.

  • 不在快照事务启动时活动的事务列表中。Not in the list of the transactions active when the snapshot transaction started.

由快照事务执行的读取操作将检索在快照事务启动时已提交的每行的最新版本。Read operations performed by a snapshot transaction retrieve the last version of each row that had been committed at the time the snapshot transaction started. 这提供了在事务启动时存在的数据的事务一致快照。This provides a transactionally consistent snapshot of the data as it existed at the start of the transaction.

使用行版本控制的已提交读事务以大致相同的方式运行。Read-committed transactions using row versioning operate in much the same way. 不同之处在于选择行版本时,已提交读事务不使用其自身的事务序列号。The difference is that the read-committed transaction does not use its own transaction sequence number when choosing row versions. 每次启动语句时,已提交读事务将读取为数据库引擎Database Engine实例发出的最新事务序列号。Each time a statement is started, the read-committed transaction reads the latest transaction sequence number issued for that instance of the 数据库引擎Database Engine. 这是用于为该语句选择正确的行版本的事务序列号。This is the transaction sequence number used to select the correct row versions for that statement. 这使已提交读事务可以查看每个语句启动时存在的数据的快照。This allows read-committed transactions to see a snapshot of the data as it exists at the start of each statement.

备注

即使使用行版本控制的已提交读事务提供了在语句级别上事务一致的数据视图,但此类事务生成或访问的行版本还将保留,直到事务完成时为止。Even though read-committed transactions using row versioning provides a transactionally consistent view of the data at a statement level, row versions generated or accessed by this type of transaction are maintained until the transaction completes.

修改数据时的行为Behavior When Modifying Data

在使用行版本控制的已提交读事务中,使用阻塞性扫描(其中读取数据值时将在数据行上采用更新锁(U 锁)完成选择要更新的行。In a read-committed transaction using row versioning, the selection of rows to update is done using a blocking scan where an update (U) lock is taken on the data row as data values are read. 这与不使用行版本控制的已提交读事务相同。This is the same as a read-committed transaction that does not use row versioning. 如果数据行不符合更新标准,在该行上将释放更新锁并且将锁定下一行并对其进行扫描。If the data row does not meet the update criteria, the update lock is released on that row and the next row is locked and scanned.

在快照隔离下运行的事务对数据修改采用乐观方法:获取数据上的锁后,才执行修改以强制应用约束。Transactions running under snapshot isolation take an optimistic approach to data modification by acquiring locks on data before performing the modification only to enforce constraints. 否则,直到数据修改时才获取数据上的锁。Otherwise, locks are not acquired on data until the data is to be modified. 当数据行符合更新标准时,快照事务将验证未被并发事务(在快照事务开始后提交)修改的数据行。When a data row meets the update criteria, the snapshot transaction verifies that the data row has not been modified by a concurrent transaction that committed after the snapshot transaction began. 如果数据行已在快照事务以外修改,则将出现更新冲突,同时快照事务也将终止。If the data row has been modified outside of the snapshot transaction, an update conflict occurs and the snapshot transaction is terminated. 更新冲突由数据库引擎Database Engine处理,无法禁用更新冲突检测。The update conflict is handled by the 数据库引擎Database Engine and there is no way to disable the update conflict detection.

备注

当快照事务访问以下任意项目时,在快照隔离下运行的更新操作将在已提交读隔离下内部执行:Update operations running under snapshot isolation internally execute under read committed isolation when the snapshot transaction accesses any of the following:

具有 FOREIGN KEY 约束的表。A table with a FOREIGN KEY constraint.

在其他表的 FOREIGN KEY 约束中引用的表。A table that is referenced in the FOREIGN KEY constraint of another table.

引用多个表的索引视图。An indexed view referencing more than one table.

但是,即使是在这些条件下,更新操作仍将继续验证数据是否未经其他事务修改。However, even under these conditions the update operation will continue to verify that the data has not been modified by another transaction. 如果数据已被其他事务修改,则快照事务将遭遇更新冲突并终止。If data has been modified by another transaction, the snapshot transaction encounters an update conflict and is terminated.

行为摘要Behavior in Summary

下表概括了使用行版本控制的快照隔离与已提交读隔离之间的差异。The following table summarizes the differences between snapshot isolation and read committed isolation using row versioning.

属性Property 使用行版本控制的已提交读隔离级别Read-committed isolation level using row versioning 快照隔离级别Snapshot isolation level
必须设置为 ON 以便启用所需支持的数据库选项。The database option that must be set to ON to enable the required support. READ_COMMITTED_SNAPSHOTREAD_COMMITTED_SNAPSHOT ALLOW_SNAPSHOT_ISOLATIONALLOW_SNAPSHOT_ISOLATION
会话如何请求特定类型的行版本控制。How a session requests the specific type of row versioning. 使用默认的已提交读隔离级别,或运行 SET TRANSACTION ISOLATION LEVEL 语句来指定 READ COMMITTED 隔离级别。Use the default read-committed isolation level, or run the SET TRANSACTION ISOLATION LEVEL statement to specify the READ COMMITTED isolation level. 这可以在事务启动后完成。This can be done after the transaction starts. 需要执行 SET TRANSACTION ISOLATION LEVEL 来在事务启动前指定 SNAPSHOT 隔离级别。Requires the execution of SET TRANSACTION ISOLATION LEVEL to specify the SNAPSHOT isolation level before the start of the transaction.
由语句读取的数据的版本。The version of data read by statements. 在每条语句启动前提交的所有数据。All data that was committed before the start of each statement. 在每个事务启动前提交的所有数据。All data that was committed before the start of each transaction.
如何处理更新。How updates are handled. 从行版本恢复到实际的数据,以选择要更新的行并使用选择的数据行上的更新锁。Reverts from row versions to actual data to select rows to update and uses update locks on the data rows selected. 获取要修改的实际数据行上的排他锁。Acquires exclusive locks on actual data rows to be modified. 没有更新冲突检测。No update conflict detection. 使用行版本选择要更新的行。Uses row versions to select rows to update. 尝试获取要修改的实际数据行上的排他锁,如果数据已被其他事务修改,则出现更新冲突,同时快照事务也将终止。Tries to acquire an exclusive lock on the actual data row to be modified, and if the data has been modified by another transaction, an update conflict occurs and the snapshot transaction is terminated.
有更新冲突检测。Update conflict detection. 无。None. 集成支持。Integrated support. 无法禁用。Cannot be disabled.

行版本控制资源的使用情况Row Versioning Resource Usage

行版本控制框架支持 SQL ServerSQL Server 中提供的下列功能:The row versioning framework supports the following features available in SQL ServerSQL Server:

  • TriggersTriggers

  • 多个活动的结果集 (MARS)Multiple Active Results Sets (MARS)

  • 联机索引Online indexing

另外,行版本控制框架还支持下列基于行版本控制的事务隔离级别(默认情况下禁用):The row versioning framework also supports the following row versioning-based transaction isolation levels, which by default are not enabled:

  • READ_COMMITTED_SNAPSHOT 数据库选项为 ON 时,READ_COMMITTED 事务通过使用行版本控制提供语句级读取一致性。When the READ_COMMITTED_SNAPSHOT database option is ON, READ_COMMITTED transactions provide statement-level read consistency using row versioning.

  • ALLOW_SNAPSHOT_ISOLATION 数据库选项为 ON 时,SNAPSHOT 事务通过使用行版本控制提供事务级读取一致性。When the ALLOW_SNAPSHOT_ISOLATION database option is ON, SNAPSHOT transactions provide transaction-level read consistency using row versioning.

基于行版本控制的隔离级别通过消除对读取操作使用共享锁来减少事务获取的锁数目。Row versioning-based isolation levels reduce the number of locks acquired by transaction by eliminating the use of shared locks on read operations. 这样就减少了管理锁所用资源,从而提高了系统性能。This increases system performance by reducing the resources used to manage locks. 另外还减少了其他事务获取的锁阻塞事务的次数,也就提高了性能。Performance is also increased by reducing the number of times a transaction is blocked by locks acquired by other transactions.

基于行版本控制的隔离级别增加了数据修改所需的资源。Row versioning-based isolation levels increase the resources needed by data modifications. 启用这些选项会导致要复制数据库中要修改的所有数据。Enabling these options causes all data modifications for the database to be versioned. 即使没有使用基于行版本控制隔离的活动事务,也将修改前的数据备份在 tempdb 中。A copy of the data before modification is stored in tempdb even when there are no active transactions using row versioning-based isolation. 修改后的数据包括一个指向存储在 tempdb 中的修改前的数据的指针。The data after modification includes a pointer to the versioned data stored in tempdb. 对于大型对象,只将对象中更改过的部分复制到 tempdb 中。For large objects, only part of the object that changed is copied to tempdb.

tempdb 中使用的空间Space Used in tempdb

对于每个数据库引擎Database Engine实例,tempdb 都必须具有足够的空间以容纳在该实例中为每个数据库生成的行版本。For each instance of the 数据库引擎Database Engine, tempdb must have enough space to hold the row versions generated for every database in the instance. 数据库管理员必须确保 tempdb 具有足够的空间来支持版本存储区。The database administrator must ensure that tempdb has ample space to support the version store. tempdb 中有两种版本存储区:There are two version stores in tempdb:

  • 联机索引生成版本存储区,用于所有数据库中的联机索引生成操作。The online index build version store is used for online index builds in all databases.

  • 公共版本存储区,用于所有数据库中的所有其他数据修改操作。The common version store is used for all other data modification operations in all databases.

只要活动事务需要访问行版本,就必须存储行版本。Row versions must be stored for as long as an active transaction needs to access it. 后台线程每隔一分钟删除一次不再需要的行版本,从而释放 tempdb 中的版本空间。Once every minute, a background thread removes row versions that are no longer needed and frees up the version space in tempdb. 如果长时间运行的事务符合下列任何一个条件,则会阻止释放版本存储区中的空间:A long-running transaction prevents space in the version store from being released if it meets any of the following conditions:

  • 使用基于行版本控制的隔离。It uses row versioning-based isolation.

  • 使用触发器、MARS 或联机索引生成操作。It uses triggers, MARS, or online index build operations.

  • 生成行版本。It generates row versions.

备注

在事务内部调用了触发器后,即使触发器完成后不再需要行版本,由触发器创建的行版本将仍然受到维护直到事务结束。When a trigger is invoked inside a transaction, the row versions created by the trigger are maintained until the end of the transaction, even though the row versions are no longer needed after the trigger completes. 这也同样适用于使用行版本控制的已提交读事务。This also applies to read-committed transactions that use row versioning. 对于这种事务类型,只有事务中的每条语句需要数据库的事务一致视图。With this type of transaction, a transactionally consistent view of the database is needed only for each statement in the transaction. 这表示语句完成后将不再需要在事务中为它创建的行版本。This means that the row versions created for a statement in the transaction are no longer needed after the statement completes. 但是,由事务中的每条语句创建的行版本将受到维护,直到事务完成。However, row versions created by each statement in the transaction are maintained until the transaction completes.

当 tempdb 运行空间不足时,数据库引擎Database Engine强制收缩版本存储区。When tempdb runs out of space, the 数据库引擎Database Engine forces the version stores to shrink. 在执行收缩进程的过程中,尚未生成行版本且运行时间最长的事务被标记为牺牲品。During the shrink process, the longest running transactions that have not yet generated row versions are marked as victims. 在错误日志中为每个作为牺牲品的事务生成消息 3967。A message 3967 is generated in the error log for each victim transaction. 如果某个事务被标记为牺牲品,则该事务不能再读取版本存储区中的行版本。If a transaction is marked as a victim, it can no longer read the row versions in the version store. 当其尝试读取行版本时,会生成消息 3966 且该事务会被回滚。When it attempts to read row versions, message 3966 is generated and the transaction is rolled back. 如果收缩进程成功,则 tempdb 中就有可用空间。If the shrinking process succeeds, space becomes available in tempdb. 否则 tempdb 运行空间不足,并出现下列情况:Otherwise, tempdb runs out of space and the following occurs:

  • 写操作继续执行但不生成版本。Write operations continue to execute but do not generate versions. 错误日志中会生成一条信息消息 (3959),但写数据的事务不受影响。An information message (3959) appears in the error log, but the transaction that writes data is not affected.

  • 尝试访问由于 tempdb 完全回滚而未生成的行版本的事务终止,并生成错误消息 3958。Transactions that attempt to access row versions that were not generated because of a tempdb full rollback terminate with an error 3958.

数据行中使用的空间Space Used in Data Rows

每个数据库行的结尾处最多可以使用 14 个字节记录行版本控制信息。Each database row may use up to 14 bytes at the end of the row for row versioning information. 行版本控制信息包含提交版本的事务的事务序列号和指向版本行的指针。The row versioning information contains the transaction sequence number of the transaction that committed the version and the pointer to the versioned row. 如果符合下列任何一种条件,则第一次修改行时或插入新行时添加这 14 个字节:These 14 bytes are added the first time the row is modified, or when a new row is inserted, under any of these conditions:

  • READ_COMMITTED_SNAPSHOT 或 ALLOW_SNAPSHOT_ISOLATION 选项为 ON。READ_COMMITTED_SNAPSHOT or ALLOW_SNAPSHOT_ISOLATION options are ON.

  • 表有触发器。The table has a trigger.

  • 正在使用多个活动的结果集 (MARS)。Multiple Active Results Sets (MARS) is being used.

  • 当前正在对表执行联机索引生成操作。Online index build operations are currently running on the table.

如果符合下列所有条件,则第一次修改数据库行时,将从行中删除这 14 个字节:These 14 bytes are removed from the database row the first time the row is modified under all of these conditions:

  • READ_COMMITTED_SNAPSHOT 和 ALLOW_SNAPSHOT_ISOLATION 选项为 OFF。READ_COMMITTED_SNAPSHOT and ALLOW_SNAPSHOT_ISOLATION options are OFF.

  • 表不再有触发器。The trigger no longer exists on the table.

  • 当前没有使用 MARS。MARS is not being used.

  • 当前没有执行联机索引生成操作。Online index build operations are not currently running.

如果使用了行版本控制功能,则可能需要为数据库分配额外的磁盘空间,才能使每个数据库行可多使用 14 个字节。If you use any of the row versioning features, you might need to allocate additional disk space for the database to accommodate the 14 bytes per database row. 如果当前页上没有足够的可用空间,则添加行版本控制信息会导致拆分索引页或分配新的数据页。Adding the row versioning information can cause index page splits or the allocation of a new data page if there is not enough space available on the current page. 例如,如果平均行长度为 100 个字节,则额外的 14 个字节会导致现有表增大 14%。For example, if the average row length is 100 bytes, the additional 14 bytes cause an existing table to grow up to 14 percent.

减少填充因子可能有助于避免或减少索引页碎片。Decreasing the fill factor might help to prevent or decrease fragmentation of index pages. 若要查看的数据和索引的表或视图的碎片信息,可以使用DBCC SHOWCONTIGTo view fragmentation information for the data and indexes of a table or view, you can use DBCC SHOWCONTIG.

大型对象中使用的空间Space Used in Large Objects

SQL Server 数据库引擎SQL Server Database Engine支持六种数据类型(最多可以容纳大小为 2 GB 的大型字符串):nvarchar(max)varchar(max)varbinary(max)ntexttextimageThe SQL Server 数据库引擎SQL Server Database Engine supports six data types that can hold large strings up to 2 gigabytes (GB) in length: nvarchar(max), varchar(max), varbinary(max), ntext, text, and image. 使用这些数据类型的大型字符串存储在一系列与数据行链接的数据片段中。Large strings stored using these data types are stored in a series of data fragments that are linked to the data row. 行版本控制信息存储在用于存储这些大型字符串的每个片段中。Row versioning information is stored in each fragment used to store these large strings. 数据片段是表中专用于大型对象的页集合。Data fragments are a collection of pages dedicated to large objects in a table.

新的大型值添加到数据库中时,系统会为它们分配数据片段,每个片段最多可以存储 8040 个字节的数据。As new large values are added to a database, they are allocated using a maximum of 8040 bytes of data per fragment. 早期版本的数据库引擎Database Engine中,每个片段最多可以存储 8080 个字节的 ntexttextimage 数据。Earlier versions of the 数据库引擎Database Engine stored up to 8080 bytes of ntext, text, or image data per fragment.

数据库从早期版本的 ntext 升级到 text 时,现有的 imageSQL ServerSQL ServerSQL ServerSQL Server 大型对象 (LOB) 数据并未更新来为行版本控制信息释放一些空间。Existing ntext, text, and image large object (LOB) data is not updated to make space for the row versioning information when a database is upgraded to SQL ServerSQL Server from an earlier version of SQL ServerSQL Server. 但第一次修改 LOB 数据时,该数据会动态升级以实现版本控制信息的存储。However, the first time the LOB data is modified, it is dynamically upgraded to enable storage of versioning information. 即使未生成行版本也是如此。This will happen even if row versions are not generated. LOB 数据升级后,每个片段最多可以存储的字节数从 8080 个减少到 8040 个。After the LOB data is upgraded, the maximum number of bytes stored per fragment is reduced from 8080 bytes to 8040 bytes. 升级过程相当于先删除 LOB 值再重新插入相同值。The upgrade process is equivalent to deleting the LOB value and reinserting the same value. 即使只修改一个字节也会升级 LOB 数据。The LOB data is upgraded even if only one byte is modified. 对于每个 ntexttextimage 列,这是一次性操作,但每个操作可能生成大量页分配和 I/O 活动,具体情况取决于 LOB 数据的大小。This is a one-time operation for each ntext, text, or image column, but each operation may generate a large amount of page allocations and I/O activity depending upon the size of the LOB data. 如果完整记录修改,还会生成大量日志记录活动。It may also generate a large amount of logging activity if the modification is fully logged. 如果数据库恢复模式未设置为 FULL,则按最小方式记录 WRITETEXT 操作和 UPDATETEXT 操作。WRITETEXT and UPDATETEXT operations are minimally logged if database recovery mode is not set to FULL.

在早期版本的 SQL ServerSQL Server 中不使用 nvarchar(max)varchar(max)varbinary(max) 数据类型。The nvarchar(max), varchar(max), and varbinary(max) data types are not available in earlier versions of SQL ServerSQL Server. 因此,这些数据类型不存在升级问题。Therefore, they have no upgrade issues.

应该分配足够的磁盘空间来满足此要求。Enough disk space should be allocated to accommodate this requirement.

监视行版本控制和版本存储区Monitoring Row Versioning and the Version Store

为了监视行版本控制、版本存储区和快照隔离进程以了解性能和问题,SQL ServerSQL Server 提供了一些工具,包括动态管理视图 (DMV) 和 Windows 系统监视器中的性能计数器。For monitoring row versioning, version store, and snapshot isolation processes for performance and problems, SQL ServerSQL Server provides tools in the form of Dynamic Management Views (DMVs) and performance counters in Windows System Monitor.

DMVDMVs

下列 DMV 提供有关 tempdb 的当前系统状态、版本存储区以及使用行版本控制的事务的信息。The following DMVs provide information about the current system state of tempdb and the version store, as well as transactions using row versioning.

sys.dm_db_file_space_usage。sys.dm_db_file_space_usage. 返回数据库中每个文件的空间使用信息。Returns space usage information for each file in the database. 有关详细信息,请参阅 sys.dm_db_file_space_usage (Transact-SQL)For more information, see sys.dm_db_file_space_usage (Transact-SQL).

sys.dm_db_session_space_usage。sys.dm_db_session_space_usage. 返回会话为数据库进行的页分配和释放活动。Returns page allocation and deallocation activity by session for the database. 有关详细信息,请参阅 sys.dm_db_session_space_usage (Transact-SQL)For more information, see sys.dm_db_session_space_usage (Transact-SQL).

sys.dm_db_task_space_usage。sys.dm_db_task_space_usage. 返回任务为数据库进行的页分配和释放活动。Returns page allocation and deallocation activity by task for the database. 有关详细信息,请参阅 sys.dm_db_task_space_usage (Transact-SQL)For more information, see sys.dm_db_task_space_usage (Transact-SQL).

sys.dm_tran_top_version_generators。sys.dm_tran_top_version_generators. 返回一个虚拟表,其中包含生成的版本是版本存储区中最多的对象。Returns a virtual table for the objects producing the most versions in the version store. 该表按 database_id 和 rowset_id 对前 256 位的聚合记录长度进行分组。It groups the top 256 aggregated record lengths by database_id and rowset_id. 可以使用此函数来查找版本存储区的最大使用者。Use this function to find the largest consumers of the version store. 有关详细信息,请参阅 sys.dm_tran_top_version_generators (Transact-SQL)For more information, see sys.dm_tran_top_version_generators (Transact-SQL).

sys.dm_tran_version_store。sys.dm_tran_version_store. 返回一个虚拟表,其中显示有公共版本存储区中的所有版本记录。Returns a virtual table that displays all version records in the common version store. 有关详细信息,请参阅 sys.dm_tran_version_store (Transact-SQL)For more information, see sys.dm_tran_version_store (Transact-SQL).

备注

由于 sys.dm_tran_top_version_generators 和 sys.dm_tran_version_store 都查询整个版本存储区(可能非常大),因此运行这两者时可能要占用大量资源。sys.dm_tran_top_version_generators and sys.dm_tran_version_store are potentially very expensive functions to run, since both query the entire version store, which could be very large.

sys.dm_tran_active_snapshot_database_transactions。sys.dm_tran_active_snapshot_database_transactions. 返回一个虚拟表,其中包含使用行版本控制的 SQL ServerSQL Server 实例中的所有数据库中的所有活动事务。Returns a virtual table for all active transactions in all databases within the SQL ServerSQL Server instance that use row versioning. 但系统事务不会显示在此 DMV 中。System transactions do not appear in this DMV. 有关详细信息,请参阅 sys.dm_tran_active_snapshot_database_transactions (Transact-SQL)For more information, see sys.dm_tran_active_snapshot_database_transactions (Transact-SQL).

sys.dm_tran_transactions_snapshot。sys.dm_tran_transactions_snapshot. 返回一个虚拟表,其中显示有每个事务使用的快照。Returns a virtual table that displays snapshots taken by each transaction. 该快照包含了使用行版本控制的活动事务的序列号。The snapshot contains the sequence number of the active transactions that use row versioning. 有关详细信息,请参阅 sys.dm_tran_transactions_snapshot (Transact-SQL)For more information, see sys.dm_tran_transactions_snapshot (Transact-SQL).

sys.dm_tran_current_transaction。sys.dm_tran_current_transaction. 返回一行,其中显示有当前会话中与行版本控制相关的事务状态信息。Returns a single row that displays row versioning-related state information of the transaction in the current session. 有关详细信息,请参阅 sys.dm_tran_current_transaction (Transact-SQL)For more information, see sys.dm_tran_current_transaction (Transact-SQL).

sys.dm_tran_current_snapshot。sys.dm_tran_current_snapshot. 返回一个虚拟表,其中显示有当前快照隔离事务启动时的所有活动事务。Returns a virtual table that displays all active transactions at the time the current snapshot isolation transaction starts. 如果当前事务正在使用快照隔离,则该函数不返回行。If the current transaction is using snapshot isolation, this function returns no rows. sys.dm_tran_current_snapshot 类似于 sys.dm_tran_transactions_snapshot,只不过它仅返回当前快照的活动事务。sys.dm_tran_current_snapshot is similar to sys.dm_tran_transactions_snapshot, except that it returns only the active transactions for the current snapshot. 有关详细信息,请参阅 sys.dm_tran_current_snapshot (Transact-SQL)For more information, see sys.dm_tran_current_snapshot (Transact-SQL).

性能计数器Performance Counters

SQL ServerSQL Server 性能计数器提供有关受 SQL ServerSQL Server 进程影响的系统性能的信息。performance counters provide information about the system performance impacted by SQL ServerSQL Server processes. 下列性能计数器监视 tempdb、版本存储区以及使用行版本控制的事务。The following performance counters monitor tempdb and the version store, as well as transactions using row versioning. 这些性能计数器包含在 SQLServer:Transactions 性能对象中。The performance counters are contained in the SQLServer:Transactions performance object.

Free Space in tempdb (KB)Free Space in tempdb (KB). 监视 tempdb 数据库中的可用空间 (KB)。Monitors the amount, in kilobytes (KB), of free space in the tempdb database. tempdb 中必须有足够的可用空间来容纳支持快照隔离的版本存储区。There must be enough free space in tempdb to handle the version store that supports snapshot isolation.

下列公式可以用来粗略估计版本存储区的大小。The following formula provides a rough estimate of the size of the version store. 对于长时间运行的事务,监视生成速率和清除速率对于估计版本存储区的最大大小会非常有用。For long-running transactions, it may be useful to monitor the generation and cleanup rate to estimate the maximum size of the version store.

[公共版本存储区的大小] = 2 * [每分钟生成的版本存储区数据] * [事务的最长运行时间(分钟)][size of common version store] = 2 * [version store data generated per minute] * [longest running time (minutes) of the transaction]

事务的最长运行时间不应该包括联机索引生成时间。The longest running time of transactions should not include online index builds. 对于超大型表,由于这些操作可能要花很长的时间,因此联机索引生成使用单独的版本存储区。Because these operations may take a long time on very large tables, online index builds use a separate version store. 当联机索引生成处于活动状态时,联机索引生成版本存储区的近似大小等于表(包括所有索引)中修改的数据量。The approximate size of the online index build version store equals the amount of data modified in the table, including all indexes, while the online index build is active.

Version Store Size (KB)Version Store Size (KB). 监视所有版本存储区的大小 (KB)。Monitors the size in KB of all version stores. 此信息有助于确定版本存储区在 tempdb 数据库中所需的空间大小。This information helps determine the amount of space needed in the tempdb database for the version store. 监视计数器一段时间,可以获得有用的信息来估计在 tempdb 数据库中所需的额外空间。Monitoring this counter over a period of time provides a useful estimate of additional space needed for tempdb.

Version Generation rate (KB/s) 的用户。Version Generation rate (KB/s). 监视所有版本存储区中的版本生成速率(KB/秒)。Monitors the version generation rate in KB per second in all version stores.

Version Cleanup rate (KB/s) 的用户。Version Cleanup rate (KB/s). 监视所有版本存储区中的版本清除速率(KB/秒)。Monitors the version cleanup rate in KB per second in all version stores.

备注

Version Generation rate (KB/s) 和 Version Cleanup rate (KB/s) 的信息可以用于预测 tempdb 空间要求。Information from Version Generation rate (KB/s) and Version Cleanup rate (KB/s) can be used to predict tempdb space requirements.

Version Store unit countVersion Store unit count. 监视版本存储区单元的计数。Monitors the count of version store units.

Version Store unit creationVersion Store unit creation. 监视自启动实例后创建用于存储行版本的版本存储区单元总数。Monitors the total number of version store units created to store row versions since the instance was started.

Version Store unit truncationVersion Store unit truncation. 监视自启动实例后被截断的版本存储区单元总数。Monitors the total number of version store units truncated since the instance was started. SQL ServerSQL Server 确定不需要任何存储在版本存储区单元中的版本行来运行活动事务时,版本存储区单元即被截断。A version store unit is truncated when SQL ServerSQL Server determines that none of the version rows stored in the version store unit are needed to run active transactions.

Update conflict ratioUpdate conflict ratio. 监视存在更新冲突的更新快照事务与更新快照事务总数的比值。Monitors the ratio of update snapshot transaction that have update conflicts to the total number of update snapshot transactions.

Longest Transaction Running TimeLongest Transaction Running Time. 监视使用行版本控制的事务的最长运行时间(秒)。Monitors the longest running time in seconds of any transaction using row versioning. 这可用于确定是否存在事务的运行时间不合适的情况。This can be used to determine if any transaction is running for an unreasonable amount of time.

TransactionsTransactions. 监视活动事务的总数,Monitors the total number of active transactions. 不包括系统事务。This does not include system transactions.

Snapshot Transactions 的用户。Snapshot Transactions. 监视活动快照事务的总数。Monitors the total number of active snapshot transactions.

Update Snapshot Transactions 的用户。Update Snapshot Transactions. 监视执行更新操作的活动快照事务的总数。Monitors the total number of active snapshot transactions that perform update operations.

NonSnapshot Version Transactions 的用户。NonSnapshot Version Transactions. 监视生成版本记录的活动非快照事务的总数。Monitors the total number of active non-snapshot transactions that generate version records.

备注

Update Snapshot Transactions 与 NonSnapshot Version Transactions 之和表示参与版本生成的事务的总数。The sum of Update Snapshot Transactions and NonSnapshot Version Transactions represents the total number of transactions that participate in version generation. Snapshot Transactions 与 Update Snapshot Transactions 之差表示只读快照事务数。The difference of Snapshot Transactions and Update Snapshot Transactions reports the number of read-only snapshot transactions.

基于行版本控制的隔离级别示例Row Versioning-based Isolation Level Example

下列示例说明使用行版本控制的快照隔离事务与已提交读事务的行为差异。The following examples show the differences in behavior between snapshot isolation transactions and read-committed transactions that use row versioning.

A.A. 使用快照隔离Working with snapshot isolation

在此示例中,在快照隔离下运行的事务将读取数据,然后由另一事务修改此数据。In this example, a transaction running under snapshot isolation reads data that is then modified by another transaction. 快照事务不阻塞由其他事务执行的更新操作,它忽略数据的修改继续从版本化的行读取数据。The snapshot transaction does not block the update operation executed by the other transaction, and it continues to read data from the versioned row, ignoring the data modification. 但是,当快照事务尝试修改已由其他事务修改的数据时,快照事务将生成错误并终止。However, when the snapshot transaction attempts to modify the data that has already been modified by the other transaction, the snapshot transaction generates an error and is terminated.

在会话 1 上:On session 1:

USE AdventureWorks2012;  -- Or the 2008 or 2008R2 version of the AdventureWorks database.  
GO  
  
-- Enable snapshot isolation on the database.  
ALTER DATABASE AdventureWorks2012  
    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 上:On session 2:

USE AdventureWorks2012;  
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 上:On session 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 上:On session 2:

-- Commit the transaction; this commits the data  
-- modification.  
COMMIT TRANSACTION;  
GO  

在会话 1 上:On session 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.B. 使用通过行版本控制的已提交读Working with read-committed using row versioning

在此示例中,使用行版本控制的已提交读事务与其他事务并发运行。In this example, a read-committed transaction using row versioning runs concurrently with another transaction. 已提交读事务的行为与快照事务的行为有所不同。The read-committed transaction behaves differently than a snapshot transaction. 与快照事务相同的是,即使其他事务修改了数据,已提交读事务也将读取版本化的行。Like a snapshot transaction, the read-committed transaction will read versioned rows even after the other transaction has modified data. 然而,与快照事务不同的是,已提交读将执行下列操作:However, unlike a snapshot transaction, the read-committed transaction will:

  • 在其他事务提交数据更改后,读取修改的数据。Read the modified data after the other transaction commits the data changes.

  • 能够更新由其他事务修改的数据,而快照事务不能。Be able to update the data modified by the other transaction where the snapshot transaction could not.

在会话 1 上:On session 1:

USE AdventureWorks2012;  -- 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 AdventureWorks2012  
-- database.  
ALTER DATABASE AdventureWorks2012  
    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 上:On session 2:

USE AdventureWorks2012;  
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 上:On session 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 上:On session 2:

-- Commit the transaction.  
COMMIT TRANSACTION;  
GO  
  

在会话 1 上:On session 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  

启用基于行版本控制的隔离级别Enabling Row Versioning-Based Isolation Levels

数据库管理员可以通过在 ALTER DATABASE 语句中使用 READ_COMMITTED_SNAPSHOT 和 ALLOW_SNAPSHOT_ISOLATION 数据库选项来控制行版本控制的数据库级别设置。Database administrators control the database-level settings for row versioning by using the READ_COMMITTED_SNAPSHOT and ALLOW_SNAPSHOT_ISOLATION database options in the ALTER DATABASE statement.

将 READ_COMMITTED_SNAPSHOT 数据库选项设置为 ON 后,用于支持该选项的机制将立即激活。When the READ_COMMITTED_SNAPSHOT database option is set ON, the mechanisms used to support the option are activated immediately. 设置 READ_COMMITTED_SNAPSHOT 选项时,数据库中只允许存在执行 ALTER DATABASE 命令的连接。When setting the READ_COMMITTED_SNAPSHOT option, only the connection executing the ALTER DATABASE command is allowed in the database. 在 ALTER DATABASE 完成之前,数据库中不允许有其他打开的连接。There must be no other open connection in the database until ALTER DATABASE is complete. 数据库不必处于单用户模式。The database does not have to be in single-user mode.

下面的 Transact-SQLTransact-SQL 语句将启用 READ_COMMITTED_SNAPSHOT:The following Transact-SQLTransact-SQL statement enables READ_COMMITTED_SNAPSHOT:

ALTER DATABASE AdventureWorks2012  
    SET READ_COMMITTED_SNAPSHOT ON;  

如果 ALLOW_SNAPSHOT_ISOLATION 数据库选项设置为 ON,则数据库中数据已修改的所有活动事务完成之前,SQL Server 数据库引擎SQL Server Database Engine实例不会为已修改的数据生成行版本。When the ALLOW_SNAPSHOT_ISOLATION database option is set ON, the instance of the SQL Server 数据库引擎SQL Server Database Engine does not generate row versions for modified data until all active transactions that have modified data in the database complete. 如果存在活动的修改事务,SQL ServerSQL Server 将把该选项的状态设置为 PENDING_ON。If there are active modification transactions, SQL ServerSQL Server sets the state of the option to PENDING_ON. 所有修改事务完成后,该选项的状态更改为 ON。After all of the modification transactions complete, the state of the option is changed to ON. 在该选项完全处于 ON 状态之前,用户无法在数据库中启动快照事务。Users cannot start a snapshot transaction in that database until the option is fully ON. 数据库管理员将 ALLOW_SNAPSHOT_ISOLATION 选项设置为 OFF 后,数据库将跳过 PENDING_OFF 状态。The database passes through a PENDING_OFF state when the database administrator sets the ALLOW_SNAPSHOT_ISOLATION option to OFF.

下面的 Transact-SQLTransact-SQL 语句将启用 ALLOW_SNAPSHOT_ISOLATION:The following Transact-SQLTransact-SQL statement will enable ALLOW_SNAPSHOT_ISOLATION:

ALTER DATABASE AdventureWorks2012  
    SET ALLOW_SNAPSHOT_ISOLATION ON;  

下表列出并说明了 ALLOW_SNAPSHOT_ISOLATION 选项的各个状态。The following table lists and describes the states of the ALLOW_SNAPSHOT_ISOLATION option. 同时使用 ALTER DATABASE 和 ALLOW_SNAPSHOT_ISOLATION 选项不会妨碍当前正在访问数据库数据的用户。Using ALTER DATABASE with the ALLOW_SNAPSHOT_ISOLATION option does not block users who are currently accessing the database data.

当前数据库的快照隔离框架状态State of snapshot isolation framework for current database DescriptionDescription
OFFOFF 未启用对快照隔离事务的支持。The support for snapshot isolation transactions is not activated. 不允许执行快照隔离事务。No snapshot isolation transactions are allowed.
PENDING_ONPENDING_ON 对快照隔离事务的支持处于转换状态(从 OFF 到 ON)。The support for snapshot isolation transactions is in transition state (from OFF to ON). 打开的事务必须完成。Open transactions must complete.

不允许执行快照隔离事务。No snapshot isolation transactions are allowed.
ONON 已启用对快照隔离事务的支持。The support for snapshot isolation transactions is activated.

允许执行快照事务。Snapshot transactions are allowed.
PENDING_OFFPENDING_OFF 对快照隔离事务的支持处于转换状态(从 ON 到 OFF)。The support for snapshot isolation transactions is in transition state (from ON to OFF).

此后启动的快照事务无法访问此数据库。Snapshot transactions started after this time cannot access this database. 更新事务仍会导致此数据库中出现版本控制开销。Update transactions still pay the cost of versioning in this database. 现有快照事务仍可以访问此数据库,不会遇到任何问题。Existing snapshot transactions can still access this database without a problem. 直到数据库快照隔离状态为 ON 时处于活动状态的所有快照事务完成后,状态 PENDING_OFF 才变为 OFF。The state PENDING_OFF does not become OFF until all snapshot transactions that were active when the database snapshot isolation state was ON finish.

使用 sys.databases 目录视图可以确定两个行版本控制数据库选项的状态。Use the sys.databases catalog view to determine the state of both row versioning database options.

对用户表和存储在 master 和 msdb 中的某些系统表的任何更新都会生成行版本。All updates to user tables and some system tables stored in master and msdb generate row versions.

在 master 和 msdb 数据库中,ALLOW_SNAPSHOT_ISOLATION 选项自动设置为 ON,并且不能禁用。The ALLOW_SNAPSHOT_ISOLATION option is automatically set ON in the master and msdb databases, and cannot be disabled.

在 master 数据库、tempdb 数据库或 msdb 数据库中,用户不能将 READ_COMMITTED_SNAPSHOT 选项设置为 ON。Users cannot set the READ_COMMITTED_SNAPSHOT option ON in master, tempdb, or msdb.

使用基于行版本控制的隔离级别Using Row Versioning-based Isolation Levels

行版本控制框架在 SQL ServerSQL Server 中始终处于启用状态,并被多个功能使用。The row versioning framework is always enabled in SQL ServerSQL Server, and is used by multiple features. 它除了提供基于行版本控制的隔离级别之外,还用于支持对触发器和多个活动结果集 (MARS) 会话的修改,以及 ONLINE 索引操作的数据读取。Besides providing row versioning-based isolation levels, it is used to support modifications made in triggers and multiple active result sets (MARS) sessions, and to support data reads for ONLINE index operations.

基于行版本控制的隔离级别是在数据库级别上启用的。Row versioning-based isolation levels are enabled at the database level. 访问已启用数据库的对象的任何应用程序可以使用以下隔离级别运行查询:Any application accessing objects from enabled databases can run queries using the following isolation levels:

  • 已提交读隔离级别,通过将 READ_COMMITTED_SNAPSHOT 数据库选项设置为 ON 来使用行版本控制,如下面的代码示例所示:Read-committed that uses row versioning by setting the READ_COMMITTED_SNAPSHOT database option to ON as shown in the following code example:

    ALTER DATABASE AdventureWorks2012  
        SET READ_COMMITTED_SNAPSHOT ON;  
    

    为 READ_COMMITTED_SNAPSHOT 启用数据库后,在已提交读隔离级别下运行的所有查询将使用行版本控制,这意味着读取操作不会阻止更新操作。When the database is enabled for READ_COMMITTED_SNAPSHOT, all queries running under the read committed isolation level use row versioning, which means that read operations do not block update operations.

  • 快照隔离,通过将 ALLOW_SNAPSHOT_ISOLATION 数据库选项设置为 ON 实现,如下面的代码示例所示:Snapshot isolation by setting the ALLOW_SNAPSHOT_ISOLATION database option to ON as shown in the following code example:

    ALTER DATABASE AdventureWorks2012  
        SET ALLOW_SNAPSHOT_ISOLATION ON;  
    

    在快照隔离下运行的事务可以访问数据库中为快照启用的表。A transaction running under snapshot isolation can access tables in the database that have been enabled for snapshot. 若要访问没有为快照启用的表,则必须更改隔离级别。To access tables that have not been enabled for snapshot, the isolation level must be changed. 例如,下面的代码示例显示了在快照事务下运行时联接两个表的 SELECT 语句。For example, the following code example shows a SELECT statement that joins two tables while running under a snapshot transaction. 一个表属于未启用快照隔离的数据库。One table belongs to a database in which snapshot isolation is not enabled. SELECT 语句在快照隔离下运行时,该语句无法成功执行。When the SELECT statement runs under snapshot isolation, it fails to execute successfully.

    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 语句。The following code example shows the same SELECT statement that has been modified to change the transaction isolation level to read-committed. 由于此更改,SELECT 语句将成功执行。Because of this change, the SELECT statement executes successfully.

    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;  
    

使用基于行版本控制的隔离级别的事务的限制Limitations of Transactions Using Row Versioning-based Isolation Levels

使用基于行版本控制的隔离级别时,请考虑下列限制:Consider the following limitations when working with row versioning-based isolation levels:

  • READ_COMMITTED_SNAPSHOT 无法在 tempdb、msdb 或 master 中启用。READ_COMMITTED_SNAPSHOT cannot be enabled in tempdb, msdb, or master.

  • 全局临时表存储在 tempdb 中。Global temp tables are stored in tempdb. 访问快照事务中的全局临时表时,必须执行下列操作之一:When accessing global temp tables inside a snapshot transaction, one of the following must happen:

    • 在 tempdb 中将 ALLOW_SNAPSHOT_ISOLATION 数据库选项设置为 ON。Set the ALLOW_SNAPSHOT_ISOLATION database option ON in tempdb.

    • 使用隔离提示更改语句的隔离级别。Use an isolation hint to change the isolation level for the statement.

  • 如果出现以下情况,快照事务将失败:Snapshot transactions fail when:

    • 从快照事务启动之后到访问数据库前的期间内,数据库设置为只读。A database is made read-only after the snapshot transaction starts, but before the snapshot transaction accesses the database.

    • 如果访问多个数据库的对象,数据库状态以如下方式更改:从快照事务启动后到访问数据库前的期间内,发生数据库恢复。If accessing objects from multiple databases, a database state was changed in such a way that database recovery occurred after a snapshot transaction starts, but before the snapshot transaction accesses the database. 例如:将数据库设置为 OFFLINE,然后设置为 ONLINE,数据库将自动关闭和打开,或数据库将分离和附加。For example: the database was set to OFFLINE and then to ONLINE, database autoclose and open, or database detach and attach.

  • 快照隔离不支持分布式事务,包括分布式分区数据库中的查询。Distributed transactions, including queries in distributed partitioned databases, are not supported under snapshot isolation.

  • SQL ServerSQL Server 不会保留多个版本的系统元数据。does not keep multiple versions of system metadata. 表中的数据定义语言 (DDL) 语句和其他数据库对象(索引、视图、数据类型、存储过程和公共语言运行时函数)会更改元数据。Data definition language (DDL) statements on tables and other database objects (indexes, views, data types, stored procedures, and common language runtime functions) change metadata. 如果 DDL 语句修改一个对象,那么在快照隔离下对该对象的任何并发引用都将导致快照事务失败。If a DDL statement modifies an object, any concurrent reference to the object under snapshot isolation causes the snapshot transaction to fail. READ_COMMITTED_SNAPSHOT 数据库选项为 ON 时,已提交读事务没有此限制。Read-committed transactions do not have this limitation when the READ_COMMITTED_SNAPSHOT database option is ON.

    例如,数据库管理员执行下面的 ALTER INDEX 语句。For example, a database administrator executes the following ALTER INDEX statement.

    USE AdventureWorks2012;  
    GO  
    ALTER INDEX AK_Employee_LoginID  
        ON HumanResources.Employee REBUILD;  
    GO  
    

    执行 ALTER INDEX 语句后,任何在执行 HumanResources.Employee 语句时处于活动状态的快照事务,如果试图引用 ALTER INDEX 表,都将收到错误。Any snapshot transaction that is active when the ALTER INDEX statement is executed receives an error if it attempts to reference the HumanResources.Employee table after the ALTER INDEX statement is executed. 而使用行版本控制的已提交读事务不受影响。Read-committed transactions using row versioning are not affected.

    备注

    BULK INSERT 操作可能会导致对目标表元数据的更改(例如,禁用约束检查时)。BULK INSERT operations may cause changes to target table metadata (for example, when disabling constraint checks). 如果出现这种情况,访问大容量插入表的并发快照隔离事务将失败。When this happens, concurrent snapshot isolation transactions accessing bulk inserted tables fail.

使用顶部的链接的箭头图标中此指南Arrow icon used with Back to Top link In This Guide

自定义锁定和行版本控制Customizing Locking and Row Versioning

自定义锁超时Customizing the Lock Time-Out

MicrosoftMicrosoftSQL Server 数据库引擎SQL Server Database Engine实例由于其他事务已拥有资源的冲突锁而无法将锁授予给某个事务时,将阻塞第一个事务,等待现有锁被释放。When an instance of the MicrosoftMicrosoft SQL Server 数据库引擎SQL Server Database Engine cannot grant a lock to a transaction because another transaction already owns a conflicting lock on the resource, the first transaction becomes blocked waiting for the existing lock to be released. 默认情况下,没有强制的超时期限,并且除了尝试访问数据(有可能被无限期阻塞)外,没有其他方法可以测试某个资源是否在锁定之前已被锁定。By default, there is no mandatory time-out period and no way to test whether a resource is locked before locking it, except to attempt to access the data (and potentially get blocked indefinitely).

备注

SQL ServerSQL Server 中,使用 sys.dm_os_waiting_tasks 动态管理视图确定某个进程是否被阻塞以及被谁阻塞。In SQL ServerSQL Server, use the sys.dm_os_waiting_tasks dynamic management view to determine whether a process is being blocked and who is blocking it. SQL ServerSQL Server 的早期版本中,使用 sp_who 系统存储过程。In earlier versions of SQL ServerSQL Server, use the sp_who system stored procedure.

LOCK_TIMEOUT 设置允许应用程序设置语句等待阻塞资源的最长时间。The LOCK_TIMEOUT setting allows an application to set a maximum time that a statement waits on a blocked resource. 如果某个语句等待的时间超过 LOCK_TIMEOUT 的设置时间,则被阻塞的语句自动取消,并会有错误消息 1222 (Lock request time-out period exceeded) 返回给应用程序。When a statement has waited longer than the LOCK_TIMEOUT setting, the blocked statement is canceled automatically, and error message 1222 (Lock request time-out period exceeded) is returned to the application. 但是,SQL ServerSQL Server 不会回滚或取消任何包含语句的事务。Any transaction containing the statement, however, is not rolled back or canceled by SQL ServerSQL Server. 因此,应用程序必须具有可以捕获错误消息 1222 的错误处理程序。Therefore, the application must have an error handler that can trap error message 1222. 如果应用程序不能捕获错误,则会在不知道事务中已有个别语句被取消的情况下继续运行,由于事务中后面的语句可能依赖于从未执行过的语句,因此会出现错误。If an application does not trap the error, the application can proceed unaware that an individual statement within a transaction has been canceled, and errors can occur because statements later in the transaction might depend on the statement that was never executed.

实现捕获错误消息 1222 的错误处理程序后,应用程序可以处理超时情况,并采取补救措施,例如:自动重新提交被阻塞的语句或回滚整个事务。Implementing an error handler that traps error message 1222 allows an application to handle the time-out situation and take remedial action, such as: automatically resubmitting the statement that was blocked or rolling back the entire transaction.

若要确定当前 LOCK_TIMEOUT 设置,请执行 @@LOCK_TIMEOUT函数:To determine the current LOCK_TIMEOUT setting, execute the @@LOCK_TIMEOUT function:

SELECT @@lock_timeout;  
GO  

自定义事务隔离级别Customizing Transaction Isolation Level

MicrosoftMicrosoft SQL Server 数据库引擎SQL Server Database Engine的默认隔离级别为 READ COMMITTED。READ COMMITTED is the default isolation level for the MicrosoftMicrosoft SQL Server 数据库引擎SQL Server Database Engine. 如果应用程序必须在其他隔离级别运行,则它可以使用以下方法设置隔离级别:If an application must operate at a different isolation level, it can use the following methods to set the isolation level:

  • 运行 SET TRANSACTION ISOLATION LEVEL 语句。Run the SET TRANSACTION ISOLATION LEVEL statement.

  • 使用 System.Data.SqlClient 托管命名空间的 ADO.NET 应用程序可以使用 SqlConnection.BeginTransaction 方法来指定 IsolationLevel 选项。ADO.NET applications that use the System.Data.SqlClient managed namespace can specify an IsolationLevel option by using the SqlConnection.BeginTransaction method.

  • 使用了 ADO 的应用程序可以设置 Autocommit Isolation Levels 属性。Applications that use ADO can set the Autocommit Isolation Levels property.

  • 启动事务时,使用 OLE DB 的应用程序可以调用 ITransactionLocal::StartTransaction,并在调用时将 isoLevel 设置为所需的事务隔离级别。When starting a transaction, applications using OLE DB can call ITransactionLocal::StartTransaction with isoLevel set to the desired transaction isolation level. 在自动提交模式下指定隔离级别时,使用 OLE DB 的应用程序可以将 DBPROPSET_SESSION 属性 DBPROP_SESS_AUTOCOMMITISOLEVELS 设置为所需的事务隔离级别。When specifying the isolation level in autocommit mode, applications that use OLE DB can set the DBPROPSET_SESSION property DBPROP_SESS_AUTOCOMMITISOLEVELS to the desired transaction isolation level.

  • 使用 ODBC 的应用程序可以使用 SQLSetConnectAttr 设置 SQL_COPT_SS_TXN_ISOLATION 属性。Applications that use ODBC can set the SQL_COPT_SS_TXN_ISOLATION attribute by using SQLSetConnectAttr.

指定隔离级别后,SQL ServerSQL Server 会话中的所有查询语句和数据操作语言 (DML) 语句的锁定行为都将在该隔离级别进行操作。When the isolation level is specified, the locking behavior for all queries and data manipulation language (DML) statements in the SQL ServerSQL Server session operates at that isolation level. 隔离级别将在会话终止或将其设置为其他级别后失效。The isolation level remains in effect until the session terminates or until the isolation level is set to another level.

下面的示例设置 SERIALIZABLE 隔离级别:The following example sets the SERIALIZABLE isolation level:

USE AdventureWorks2012;  
GO  
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;  
GO  
BEGIN TRANSACTION;  
SELECT BusinessEntityID  
    FROM HumanResources.Employee;  
GO  

必要时,可以通过指定表级提示来替代各个查询语句或 DML 语句的隔离级别。The isolation level can be overridden for individual query or DML statements, if necessary, by specifying a table-level hint. 指定表级提示不会影响会话中的其他语句。Specifying a table-level hint does not affect other statements in the session. 建议仅在确实必要时才使用表级提示更改默认行为。We recommend that table-level hints be used to change the default behavior only when absolutely necessary.

读取元数据时,甚至当隔离级别被设置为在读取数据时不请求共享锁的级别时,数据库引擎Database Engine也可能需要获取锁。The 数据库引擎Database Engine might have to acquire locks when reading metadata even when the isolation level is set to a level where share locks are not requested when reading data. 例如,在未提交读隔离级别下运行的事务在读取数据时将不获取共享锁,但是在读取系统目录视图时可能会请求锁。For example, a transaction running at the read-uncommitted isolation level does not acquire share locks when reading data, but might sometime request locks when reading a system catalog view. 这意味着在查询表时如果某个并发事务正在修改该表的元数据,则未提交读事务可能会导致阻塞。This means it is possible for a read uncommitted transaction to cause blocking when querying a table when a concurrent transaction is modifying the metadata of that table.

若要确定当前设置的事务隔离级别,请使用 DBCC USEROPTIONS 语句,如下面的示例所示。To determine the transaction isolation level currently set, use the DBCC USEROPTIONS statement as shown in the following example. 该结果集可能与系统的结果集不同。The result set may vary from the result set on your system.

USE AdventureWorks2012;  
GO  
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;  
GO  
DBCC USEROPTIONS;  
GO  

下面是结果集:Here is the result set.

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.

锁提示Locking Hints

可以在 SELECT、INSERT、UPDATE 及 DELETE 语句中为单个表引用指定锁提示。Locking hints can be specified for individual table references in the SELECT, INSERT, UPDATE, and DELETE statements. 提示指定 SQL Server 数据库引擎SQL Server Database Engine实例用于表数据的锁类型或行版本控制。The hints specify the type of locking or row versioning the instance of the SQL Server 数据库引擎SQL Server Database Engine uses for the table data. 当需要对对象所获得锁类型进行更精细控制时,可以使用表级锁提示。Table-level locking hints can be used when a finer control of the types of locks acquired on an object is required. 这些锁提示覆盖会话的当前事务隔离级别。These locking hints override the current transaction isolation level for the session.

有关特定锁提示及其行为的详细信息,请参阅表提示 (Transact-SQL)For more information about the specific locking hints and their behaviors, see Table Hints (Transact-SQL).

备注

数据库引擎Database Engine查询优化器几乎总是会选择正确的锁级别。The 数据库引擎Database Engine query optimizer almost always chooses the correct locking level. 建议只在必要时才使用表级锁提示来更改默认的锁行为。We recommend that table-level locking hints be used to change the default locking behavior only when necessary. 禁止锁级别反过来会影响并发。Disallowing a locking level can adversely affect concurrency.

数据库引擎Database Engine在读取元数据时可能必须获取锁,即使是处理使用了防止在读取数据时请求共享锁的锁提示的选择。The 数据库引擎Database Engine might have to acquire locks when reading metadata, even when processing a select with a locking hint that prevents requests for share locks when reading data. 例如,使用 NOLOCK 提示的 SELECT 在读取数据时不获取共享锁,但有时在读取系统目录视图时可能会请求锁。For example, a SELECT using the NOLOCK hint does not acquire share locks when reading data, but might sometime request locks when reading a system catalog view. 这意味着可能会阻止使用 NOLOCK 的 SELECT 语句。This means it is possible for a SELECT statement using NOLOCK to be blocked.

如下面的示例中所示,如果将事务隔离级别设置为 SERIALIZABLE,并且在 NOLOCK 语句中使用表级锁提示 SELECT,则不采用通常用于维护可序列化事务的键范围锁。As shown in the following example, if the transaction isolation level is set to SERIALIZABLE, and the table-level locking hint NOLOCK is used with the SELECT statement, key-range locks typically used to maintain serializable transactions are not taken.

USE AdventureWorks2012;  
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 锁)。The only lock taken that references HumanResources.Employee is a schema stability (Sch-S) lock. 在这种情况下,不再保证可序列化性。In this case, serializability is no longer guaranteed.

SQL Server 2014SQL Server 2014 中,ALTER TABLE 的 LOCK_ESCALATION 选项可以禁用表锁,并对已分区表启用 HoBT 锁。In SQL Server 2014SQL Server 2014, the LOCK_ESCALATION option of ALTER TABLE can disfavor table locks, and enable HoBT locks on partitioned tables. 此选项不是一个锁提示,但是可用来减少锁升级。This option is not a locking hint, but can but used to reduce lock escalation. 有关详细信息,请参阅 ALTER TABLE (Transact-SQL)For more information, see ALTER TABLE (Transact-SQL).

自定义索引的锁定Customizing Locking for an Index

SQL Server 数据库引擎SQL Server Database Engine使用动态锁定策略,这种策略能够在大多数情况下自动为查询选择最佳锁定粒度。The SQL Server 数据库引擎SQL Server Database Engine uses a dynamic locking strategy that automatically chooses the best locking granularity for queries in most cases. 建议您不要替代启用页锁定和行锁定的默认锁定级别,除非透彻地了解了表或索引的访问模式且这些访问模式保持一致,并且存在有待解决的资源争用问题。We recommend that you do not override the default locking levels, which have page and row locking on, unless table or index access patterns are well understood and consistent, and there is a resource contention problem to solve. 替代锁定级别可以明显妨碍对表或索引的并发访问。Overriding a locking level can significantly impede concurrent access to a table or index. 例如,对用户时常访问的大型表仅指定表级锁可能会造成瓶颈,因为用户必须等待表级锁释放后才能访问该表。For example, specifying only table-level locks on a large table that users access heavily can cause bottlenecks because users must wait for the table-level lock to be released before accessing the table.

在为数不多的情况下,不允许页锁定或行锁定可能会有好处,但必须透彻地了解访问模式且这些访问模式保持一致。There are a few cases where disallowing page or row locking can be beneficial, if the access patterns are well understood and consistent. 例如,某个数据库应用程序使用的查找表在批处理进程中每周更新一次。For example, a database application uses a lookup table that is updated weekly in a batch process. 并发读取器使用共享锁 (S) 访问表,每周批处理更新使用排他锁 (X) 访问表。Concurrent readers access the table with a shared (S) lock and the weekly batch update accesses the table with an exclusive (X) lock. 关闭表的页锁定和行锁定可以使读取器通过共享表锁对表进行并发访问,从而在整周内降低锁定开销。Turning off page and row locking on the table reduces the locking overhead throughout the week by allowing readers to concurrently access the table through shared table locks. 在批处理作业运行时,由于它获得了排他表锁,因此可以高效地完成更新。When the batch job runs, it can complete the update efficiently because it obtains an exclusive table lock.

由于每周批处理更新在运行时将阻止并发读取器访问表,因此关闭页锁定和行锁定可能是可取的,也可能不可取。Turning off page and row locking might or might not be acceptable because the weekly batch update will block the concurrent readers from accessing the table while the update runs. 如果批处理作业仅更改少数几行或几页,则可以更改锁定级别以允许行级别或页级别的锁定,这将允许其他会话读取表中的数据而不会受到阻止。If the batch job only changes a few rows or pages, you can change the locking level to allow row or page level locking, which will enable other sessions to read from the table without blocking. 如果批处理作业要进行大量更新,则获取表的排他锁可能是确保批处理作业高效完成的最佳途径。If the batch job has a large number of updates, obtaining an exclusive lock on the table may be the best way to ensure the batch job finishes efficiently.

当两个并发操作获得同一个表的行锁然后进行阻止时,偶尔会出现死锁,因为这两个操作都需要锁定该页。Occasionally a deadlock occurs when two concurrent operations acquire row locks on the same table and then block because they both need to lock the page. 如果不允许使用行锁,则会强行使其中一个操作等待,从而避免死锁。Disallowing row locks forces one of the operations to wait, avoiding the deadlock.

可以使用 CREATE INDEX 和 ALTER INDEX 语句来设置索引使用的锁定粒度。The granularity of locking used on an index can be set using the CREATE INDEX and ALTER INDEX statements. 该锁设置适用于索引页和表页。The lock settings apply to both the index pages and the table pages. 另外,CREATE TABLE 和 ALTER TABLE 语句还可用于设置 PRIMARY KEY 和 UNIQUE 约束的锁定粒度。In addition, the CREATE TABLE and ALTER TABLE statements can be used to set locking granularity on PRIMARY KEY and UNIQUE constraints. 有关向后兼容性sp_indexoption系统存储过程还可以设置粒度。For backwards compatibility, the sp_indexoption system stored procedure can also set the granularity. 若要显示给定索引的当前锁定选项,请使用 INDEXPROPERTY 函数。To display the current locking option for a given index, use the INDEXPROPERTY function. 可以禁止将页级别的锁、行级别的锁或二者的组合用于指定的索引。Page-level locks, row-level locks, or a combination of page-level and row-level locks can be disallowed for a given index.

禁止的锁Disallowed locks 访问索引的锁Index accessed by
页级别Page level 行级别的锁和表级别的锁Row-level and table-level locks
行级别Row level 页级别的锁和表级别的锁Page-level and table-level locks
页级别和行级别Page level and row level 表级别的锁Table-level locks

使用顶部的链接的箭头图标中此指南Arrow icon used with Back to Top link In This Guide

高级事务信息Advanced Transaction Information

嵌套事务Nesting Transactions

显式事务可以嵌套。Explicit transactions can be nested. 这主要是为了支持存储过程中的一些事务,这些事务可以从已在事务中的进程调用,也可以从没有活动事务的进程中调用。This is primarily intended to support transactions in stored procedures that can be called either from a process already in a transaction or from processes that have no active transaction.

下列示例显示了嵌套事务的用途。The following example shows the intended use of nested transactions. TransProc 过程强制执行其事务,而不管执行事务的进程的事务模式。The procedure TransProc enforces its transaction regardless of the transaction mode of any process that executes it. 如果在事务活动时调用 TransProc,很可能会忽略 TransProc 中的嵌套事务,而根据对外部事务采取的最终操作提交或回滚其 INSERT 语句。If TransProc is called when a transaction is active, the nested transaction in TransProc is largely ignored, and its INSERT statements are committed or rolled back based on the final action taken for the outer transaction. 如果由不含未完成事务的进程执行 TransProc,则在该过程结束时,COMMIT TRANSACTION 将有效地提交 INSERT 语句。If TransProc is executed by a process that does not have an outstanding transaction, the COMMIT TRANSACTION at the end of the procedure effectively commits the INSERT statements.

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 数据库引擎SQL Server Database Engine将忽略内部事务的提交。Committing inner transactions is ignored by the SQL Server 数据库引擎SQL Server Database Engine. 根据最外部事务结束时采取的操作,将提交或者回滚内部事务。The transaction is either committed or rolled back based on the action taken at the end of the outermost transaction. 如果提交外部事务,也将提交内部嵌套事务。If the outer transaction is committed, the inner nested transactions are also committed. 如果回滚外部事务,也将回滚所有内部事务,不管是否单独提交过内部事务。If the outer transaction is rolled back, then all inner transactions are also rolled back, regardless of whether or not the inner transactions were individually committed.

对 COMMIT TRANSACTION 或 COMMIT WORK 的每个调用都应用于最后执行的 BEGIN TRANSACTION。Each call to COMMIT TRANSACTION or COMMIT WORK applies to the last executed BEGIN TRANSACTION. 如果嵌套 BEGIN TRANSACTION 语句,那么 COMMIT 语句只应用于最后一个嵌套的事务,也就是在最内部的事务。If the BEGIN TRANSACTION statements are nested, then a COMMIT statement applies only to the last nested transaction, which is the innermost transaction. 即使 COMMIT TRANSACTION transaction_name嵌套事务中的语句引用外部事务的事务名称,提交仅适用于最内部的事务。Even if a COMMIT TRANSACTION transaction_name statement within a nested transaction refers to the transaction name of the outer transaction, the commit applies only to the innermost transaction.

它对于是不合法transaction_name来指代一组的内部事务的 ROLLBACK TRANSACTION 语句的参数名为嵌套的事务。It is not legal for the transaction_name parameter of a ROLLBACK TRANSACTION statement to refer to the inner transactions of a set of named nested transactions. transaction_name 只能引用最外部事务的事务名称。transaction_name can refer only to the transaction name of the outermost transaction. 如果在一组嵌套事务的任意级别执行使用外部事务名称的 ROLLBACK TRANSACTION transaction_name 语句,那么所有嵌套事务都将回滚。If a ROLLBACK TRANSACTION transaction_name statement using the name of the outer transaction is executed at any level of a set of nested transactions, all of the nested transactions are rolled back. 如果不带的 ROLLBACK WORK 或 ROLLBACK TRANSACTION 语句transaction_name的一组嵌套事务的任意级别执行参数,则回滚所有嵌套事务,包括最外部事务。If a ROLLBACK WORK or ROLLBACK TRANSACTION statement without a transaction_name parameter is executed at any level of a set of nested transaction, it rolls back all of the nested transactions, including the outermost transaction.

@@TRANCOUNT函数记录当前事务嵌套级别。The @@TRANCOUNT function records the current transaction nesting level. 每个 BEGIN TRANSACTION 语句使@TRANCOUNT1。Each BEGIN TRANSACTION statement increments @@TRANCOUNT by one. @ 每个 COMMIT TRANSACTION 或 COMMIT WORK 语句递减@TRANCOUNT1。Each COMMIT TRANSACTION or COMMIT WORK statement decrements @@TRANCOUNT by one. ROLLBACK WORK 或没有事务名称的 ROLLBACK TRANSACTION 语句将回滚所有嵌套的事务和 @ 递减@TRANCOUNT为 0。A ROLLBACK WORK or a ROLLBACK TRANSACTION statement that does not have a transaction name rolls back all nested transactions and decrements @@TRANCOUNT to 0. 在一组嵌套事务中使用最外部事务的事务名称的 ROLLBACK TRANSACTION 回滚所有嵌套的事务,并将 @@TRANCOUNT为 0。A ROLLBACK TRANSACTION that uses the transaction name of the outermost transaction in a set of nested transactions rolls back all of the nested transactions and decrements @@TRANCOUNT to 0. 如果要在无法确定是否已在事务中,选择@TRANCOUNT以确定它是一个或多个。When you are unsure if you are already in a transaction, SELECT @@TRANCOUNT to determine if it is 1 or more. 如果 @@TRANCOUNT为 0,则不在事务中。If @@TRANCOUNT is 0, you are not in a transaction.

使用绑定会话Using Bound Sessions

绑定会话有利于在同一台服务器上的多个会话之间协调操作。Bound sessions ease the coordination of actions across multiple sessions on the same server. 绑定会话允许一个或多个会话共享相同的事务和锁,并可以使用同一数据,而不会有锁冲突。Bound sessions allow two or more sessions to share the same transaction and locks, and can work on the same data without lock conflicts. 可以从同一个应用程序内的多个会话中创建绑定会话,也可以从包含不同会话的多个应用程序中创建绑定会话。Bound sessions can be created from multiple sessions within the same application or from multiple applications with separate sessions.

若要参与绑定会话,会话必须调用sp_getbindtokensrv_getbindtoken (通过开放式数据服务) 来获取绑定令牌。To participate in a bound session, a session calls sp_getbindtoken or srv_getbindtoken (through Open Data Services) to get a bind token. 绑定令牌是一个字符串,它唯一地标识每个绑定事务。A bind token is a character string that uniquely identifies each bound transaction. 然后,将绑定令牌发送给要与当前会话绑定的其他会话。The bind token is then sent to the other sessions to be bound with the current session. 其他会话通过调用 sp_bindsession,并使用从第一个会话中接收到的绑定令牌绑定到事务。The other sessions bind to the transaction by calling sp_bindsession, using the bind token received from the first session.

备注

会话必须具有活动的用户事务以便sp_getbindtokensrv_getbindtoken才能成功。A session must have an active user transaction in order for sp_getbindtoken or srv_getbindtoken to succeed.

必须将绑定令牌从执行第一个会话的应用程序代码传输到随后将其会话绑定到第一个会话的应用程序代码。Bind tokens must be transmitted from the application code that makes the first session to the application code that subsequently binds their sessions to the first session. 没有应用程序可以用来获取由另一个进程启动的事务绑定令牌的 Transact-SQLTransact-SQL 语句或 API 函数。There is no Transact-SQLTransact-SQL statement or API function that an application can use to get the bind token for a transaction started by another process. 可以用来传输绑定令牌的方法包括:Some of the methods that can be used to transmit a bind token include the following:

  • 如果所有会话都是从同一个应用程序进程启动的,绑定令牌就可以存储在共用内存中,也可以作为参数传递到函数中。If the sessions are all initiated from the same application process, bind tokens can be stored in global memory or passed into functions as a parameter.

  • 如果会话是从不同的应用程序进程启动的,那么可以使用进程间通信 (IPC)(例如,远程过程调用 [RPC] 或动态数据交换 [DDE])来传输绑定令牌。If the sessions are made from separate application processes, bind tokens can be transmitted using interprocess communication (IPC), such as a remote procedure call (RPC) or dynamic data exchange (DDE).

  • 可以将绑定令牌存储在 SQL Server 数据库引擎SQL Server Database Engine实例中的某个表中,该表可由要绑定到第一个会话的进程读取。Bind tokens can be stored in a table in an instance of the SQL Server 数据库引擎SQL Server Database Engine that can be read by processes wanting to bind to the first session.

在一组绑定会话中,任何时候只能有一个会话是活动的。Only one session in a set of bound sessions can be active at any time. 如果有一个会话正在实例上执行一个语句,或包含从实例挂起的结果,则在当前会话完成处理或取消当前语句之前,其他绑定到该会话的会话都不能访问该实例。If one session is executing a statement on the instance or has results pending from the instance, no other session bound to it can access the instance until the current session finishes processing or cancels the current statement. 如果该实例正在忙于处理来自另一个绑定会话的语句,则将出现错误,指明事务空间正在使用中,会话应稍后重试。If the instance is busy processing a statement from another of the bound sessions, an error occurs indicating that the transaction space is in use and the session should retry later.

绑定会话后,每个会话仍保留其隔离级别设置。When you bind sessions, each session retains its isolation level setting. 使用 SET TRANSACTION ISOLATION LEVEL 更改某个会话的隔离级别设置不会影响绑定到该会话的任何其他会话的设置。Using SET TRANSACTION ISOLATION LEVEL to change the isolation level setting of one session does not affect the setting of any other session bound to it.

绑定会话的类型Types of Bound Sessions

有两种类型的绑定会话:本地绑定会话和分布式绑定会话。The two types of bound sessions are local and distributed.

  • 本地绑定会话Local bound session

    允许绑定会话共享单个数据库引擎Database Engine实例中的单个事务的事务空间。Allows bound sessions to share the transaction space of a single transaction in a single instance of the 数据库引擎Database Engine.

  • 分布式绑定会话Distributed bound session

    允许在使用 MicrosoftMicrosoft 分布式事务处理协调器 (MS DTC) 提交或回滚整个事务之前,绑定会话可以共享跨越两个或多个实例的同一事务。Allows bound sessions to share the same transaction across two or more instances until the entire transaction is either committed or rolled back by using MicrosoftMicrosoft Distributed Transaction Coordinator (MS DTC).

分布式绑定会话不是用字符串绑定令牌标识,而是用分布式事务标识号标识。Distributed bound sessions are not identified by a character string bind token; they are identified by distributed transaction identification numbers. 如果某个本地事务中涉及到某个绑定会话,且该会话使用 SET REMOTE_PROC_TRANSACTIONS ON 在远程服务器上执行 RPC,则 MS DTC 将该本地绑定事务自动提升为分布式绑定事务,并且 MS DTC 会话也会启动。If a bound session is involved in a local transaction and executes an RPC on a remote server with SET REMOTE_PROC_TRANSACTIONS ON, the local bound transaction is automatically promoted to a distributed bound transaction by MS DTC and an MS DTC session is started.

何时使用绑定会话When to Use Bound Sessions

在早期版本的 SQL ServerSQL Server 中,绑定会话主要用于开发必须执行 Transact-SQLTransact-SQL 语句(代表调用它们的进程)的扩展存储过程。In earlier versions of SQL ServerSQL Server, bound sessions were primarily used in developing extended stored procedures that must execute Transact-SQLTransact-SQL statements on behalf of the process that calls them. 让调用进程在绑定令牌中作为扩展存储过程的一个参数进行传递,可使该过程加入到调用进程的事务空间中,从而将扩展存储过程与该调用进程结合在一起。Having the calling process pass in a bind token as one parameter of the extended stored procedure allows the procedure to join the transaction space of the calling process, thereby integrating the extended stored procedure with the calling process.

SQL Server 数据库引擎SQL Server Database Engine中,使用 CLR 编写的存储过程比扩展存储过程更安全、具有更高的伸缩性并且更稳定。In the SQL Server 数据库引擎SQL Server Database Engine, stored procedures written using CLR are more secure, scalable, and stable than extended stored procedures. 使用 CLR 存储过程SqlContext对象不联接调用会话的上下文sp_bindsessionCLR-stored procedures use the SqlContext object to join the context of the calling session, not sp_bindsession.

绑定会话可以用来开发三层应用程序,在这些应用程序中,业务逻辑合并到在单个业务事务上协同工作的单独程序中。Bound sessions can be used to develop three-tier applications in which business logic is incorporated into separate programs that work cooperatively on a single business transaction. 必须对这些程序进行编码,以仔细协调它们对数据库的访问。These programs must be coded to carefully coordinate their access to a database. 由于两个会话共享同一个锁,因此两个程序不得同时修改同一数据。Because the two sessions share the same locks, the two programs must not try to modify the same data at the same time. 在任何时间点,事务中只能有一个会话在执行,不存在并行执行操作。At any point in time, only one session can be doing work as part of the transaction; there can be no parallel execution. 只能在定义完善的时间点于会话之间切换事务,例如,已完成所有 DML 语句且已检索其结果时。The transaction can only be switched between sessions at well-defined yield points, such as when all DML statements have completed and their results have been retrieved.

编写有效的事务Coding Efficient Transactions

尽可能使事务保持简短很重要。It is important to keep transactions as short as possible. 当事务启动后,数据库管理系统 (DBMS) 必须在事务结束之前保留很多资源,以保护事务的原子性、一致性、隔离性和持久性 (ACID) 属性。When a transaction is started, a database management system (DBMS) must hold many resources until the end of the transaction to protect the atomicity, consistency, isolation, and durability (ACID) properties of the transaction. 如果修改数据,则必须用排他锁保护修改过的行,以防止任何其他事务读取这些行,并且必须将排他锁控制到提交或回滚事务时为止。If data is modified, the modified rows must be protected with exclusive locks that prevent any other transaction from reading the rows, and exclusive locks must be held until the transaction is committed or rolled back. 根据事务隔离级别设置,SELECT 语句可以获取必须控制到提交或回滚事务时为止的锁。Depending on transaction isolation level settings, SELECT statements may acquire locks that must be held until the transaction is committed or rolled back. 特别是在有很多用户的系统中,必须尽可能使事务保持简短以减少并发连接间的资源锁定争夺。Especially in systems with many users, transactions must be kept as short as possible to reduce locking contention for resources between concurrent connections. 在有少量用户的系统中,运行时间长、效率低的事务可能不会成为问题,但是在有上千个用户的系统中,将不能忍受这样的事务。Long-running, inefficient transactions may not be a problem with small numbers of users, but they are intolerable in a system with thousands of users. SQL Server 2014SQL Server 2014SQL ServerSQL Server 开始支持延迟持久事务。Beginning with SQL Server 2014SQL Server 2014SQL ServerSQL Server supports delayed durable transactions. 延迟持久事务并不保证持续性。Delayed durable transactions do not guarantee durability. 有关详细信息,请参阅主题事务持续性See the topic Transaction Durability for more information.

编码指导原则Coding Guidelines

以下是编写有效事务的指导原则:These are guidelines for coding efficient transactions:

  • 不要在事务处理期间要求用户输入。Do not require input from users during a transaction.

    在事务启动之前,获得所有需要的用户输入。Get all required input from users before a transaction is started. 如果在事务处理期间还需要其他用户输入,则回滚当前事务,并在提供了用户输入之后重新启动该事务。If additional user input is required during a transaction, roll back the current transaction and restart the transaction after the user input is supplied. 即使用户立即响应,作为人,其反应时间也要比计算机慢得多。Even if users respond immediately, human reaction times are vastly slower than computer speeds. 事务占用的所有资源都要保留相当长的时间,这有可能会造成阻塞问题。All resources held by the transaction are held for an extremely long time, which has the potential to cause blocking problems. 如果用户没有响应,事务仍然会保持活动状态,从而锁定关键资源直到用户响应为止,但是用户可能会几分钟甚至几个小时都不响应。If users do not respond, the transaction remains active, locking critical resources until they respond, which may not happen for several minutes or even hours.

  • 在浏览数据时,尽量不要打开事务。Do not open a transaction while browsing through data, if at all possible.

    在所有预备的数据分析完成之前,不应启动事务。Transactions should not be started until all preliminary data analysis has been completed.

  • 尽可能使事务保持简短。Keep the transaction as short as possible.

    在知道要进行的修改之后,启动事务,执行修改语句,然后立即提交或回滚。After you know the modifications that have to be made, start a transaction, execute the modification statements, and then immediately commit or roll back. 只有在需要时才打开事务。Do not open the transaction before it is required.

  • 若要减少阻塞,请考虑针对只读查询使用基于行版本控制的隔离级别。To reduce blocking, consider using a row versioning-based isolation level for read-only queries.

  • 灵活地使用更低的事务隔离级别。Make intelligent use of lower transaction isolation levels.

    可以很容易地编写出许多使用只读事务隔离级别的应用程序。Many applications can be readily coded to use a read-committed transaction isolation level. 并不是所有事务都要求可序列化的事务隔离级别。Not all transactions require the serializable transaction isolation level.

  • 灵活地使用更低的游标并发选项,例如开放式并发选项。Make intelligent use of lower cursor concurrency options, such as optimistic concurrency options.

    在并发更新的可能性很小的系统中,处理“别人在您读取数据后更改了数据”的偶然错误的开销要比在读取数据时始终锁定行的开销小得多。In a system with a low probability of concurrent updates, the overhead of dealing with an occasional "somebody else changed your data after you read it" error can be much lower than the overhead of always locking rows as they are read.

  • 在事务中尽量使访问的数据量最小。Access the least amount of data possible while in a transaction.

    这样可以减少锁定的行数,从而减少事务之间的争夺。This lessens the number of locked rows, thereby reducing contention between transactions.

避免并发问题和资源问题Avoiding Concurrency and Resource Problems

为了防止并发问题和资源问题,应小心管理隐式事务。To prevent concurrency and resource problems, manage implicit transactions carefully. 使用隐式事务时,COMMIT 或 ROLLBACK 后的下一个 Transact-SQLTransact-SQL 语句会自动启动一个新事务。When using implicit transactions, the next Transact-SQLTransact-SQL statement after COMMIT or ROLLBACK automatically starts a new transaction. 这可能会在应用程序浏览数据时(甚至在需要用户输入时)打开一个新事务。This can cause a new transaction to be opened while the application browses through data, or even when it requires input from the user. 在完成保护数据修改所需的最后一个事务之后,应关闭隐性事务,直到再次需要使用事务来保护数据修改。After completing the last transaction required to protect data modifications, turn off implicit transactions until a transaction is once again required to protect data modifications. 此过程使 SQL Server 数据库引擎SQL Server Database Engine 能够在应用程序浏览数据以及获取用户输入时使用自动提交模式。This process lets the SQL Server 数据库引擎SQL Server Database Engine use autocommit mode while the application is browsing data and getting input from the user.

另外,启用快照隔离级别后,尽管新事务不会控制锁,但是长时间运行的事务将阻止从 tempdb中删除旧版本。In addition, when the snapshot isolation level is enabled, although a new transaction will not hold locks, a long-running transaction will prevent the old versions from being removed from tempdb.

管理长时间运行的事务Managing Long-Running Transactions

“长时间运行的事务”是一个未及时提交或回滚事务的活动事务。A long-running transaction is an active transaction that has not been committed or roll backed the transaction in a timely manner. 例如,如果事务的开始和结束由用户控制,则导致长时间运行事务的一般原因是用户在开始事务之后便离开,而事务等待用户的响应。For example, if the beginning and end of a transaction is controlled by the user, a typical cause of a long-running transaction is a user starting a transaction and then leaving while the transaction waits for a response from the user.

长时间运行的事务可能导致数据库的严重问题,如下所示:A long running transaction can cause serious problems for a database, as follows:

  • 如果服务器实例已关闭的活动事务已执行很多未提交的修改后,后续重新启动的恢复阶段可能需要更长的时间比指定的时间恢复间隔服务器配置选项或 ALTER database...SET TARGET_RECOVERY_TIME 选项。If a server instance is shut down after an active transaction has performed many uncommitted modifications, the recovery phase of the subsequent restart can take much longer than the time specified by the recovery interval server configuration option or by the ALTER DATABASE... SET TARGET_RECOVERY_TIME option. 这些选项分别控制活动检查点和间接检查点的频率。These options control the frequency of active and indirect checkpoints, respectively. 有关检查点类型的详细信息,请参阅数据库检查点 (SQL Server)For more information about the types of checkpoints, see Database Checkpoints (SQL Server).

  • 更重要的是,尽管等待事务可能生成很小的日志,但是它无限期阻止日志截断,导致事务日志不断增加并可能填满。More importantly, although a waiting transaction might generate very little log, it holds up log truncation indefinitely, causing the transaction log to grow and possibly fill up. 如果事务日志填满,数据库将无法再执行任何更新。If the transaction log fills up, the database cannot perform any more updates. 有关详细信息,请参阅解决事务日志已满(SQL Server 错误 9002),和的事务日志的(SQL Server)For more information, see Troubleshoot a Full Transaction Log (SQL Server Error 9002), and The Transaction Log (SQL Server).

查看长时间运行的事务Discovering Long-Running Transactions

若要查看长时间运行的事务,请使用下列方法之一:To look for long-running transactions, use one of the following:

  • sys.dm_tran_database_transactionssys.dm_tran_database_transactions

    此动态管理视图返回有关数据库级事务的信息。This dynamic management view returns information about transactions at the database level. 对于长时间运行的事务,最需要注意的列包括:第一条日志记录的时间 (database_transaction_begin_time)、事务的当前状态 (database_transaction_state) 和事务日志中开始记录的日志序列号 (LSN) (database_transaction_begin_lsn)。For a long-running transaction, columns of particular interest include the time of the first log record (database_transaction_begin_time), the current state of the transaction (database_transaction_state), and the log sequence number (LSN) of the begin record in the transaction log (database_transaction_begin_lsn).

    有关详细信息,请参阅 sys.dm_tran_database_transactions (Transact-SQL)For more information, see sys.dm_tran_database_transactions (Transact-SQL).

  • DBCC OPENTRANDBCC OPENTRAN

    通过此语句,您可以标识该事务所有者的用户 ID,因此可以隐性地跟踪该事务的源以得到更加有序的终止(将其提交而非回滚)。This statement lets you identify the user ID of the owner of the transaction, so you can potentially track down the source of the transaction for a more orderly termination (committing it rather than rolling it back). 有关详细信息,请参阅 DBCC OPENTRAN (Transact-SQL)For more information, see DBCC OPENTRAN (Transact-SQL).

停止事务Stopping a Transaction

您可能必须使用 KILL 语句。You may have to use the KILL statement. 但是,在使用此语句时请务必小心,特别是在运行重要的进程时。Use this statement very carefully, however, especially when critical processes are running. 有关详细信息,请参阅 KILL (Transact-SQL)For more information, see KILL (Transact-SQL).

使用顶部的链接的箭头图标中此指南Arrow icon used with Back to Top link In This Guide

请参阅See Also

SQL Server 2005 行版本控制基于事务隔离 SQL Server 2005 Row Versioning-Based Transaction Isolation
行版本控制的系统开销 Overhead of Row Versioning
如何在 SQL Server 2008 中创建自治事务How to create an autonomous transaction in SQL Server 2008