SET TRANSACTION ISOLATION LEVEL (Transact-SQL)SET TRANSACTION ISOLATION LEVEL (Transact-SQL)

适用对象: yesSQL ServeryesAzure SQL 数据库yesAzure SQL 数据仓库yes并行数据仓库APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure SQL Data Warehouse yesParallel Data Warehouse

控制到 Transact-SQLTransact-SQL 的连接发出的 SQL ServerSQL Server 语句的锁定行为和行版本控制行为。Controls the locking and row versioning behavior of Transact-SQLTransact-SQL statements issued by a connection to SQL ServerSQL Server.

主题链接图标 TRANSACT-SQL 语法约定Topic link icon Transact-SQL Syntax Conventions

语法Syntax

-- Syntax for SQL Server and Azure SQL Database
  
SET TRANSACTION ISOLATION LEVEL
    { READ UNCOMMITTED
    | READ COMMITTED
    | REPEATABLE READ
    | SNAPSHOT
    | SERIALIZABLE
    }
-- Syntax for Azure SQL Data Warehouse and Parallel Data Warehouse
  
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

参数Arguments

READ UNCOMMITTEDREAD UNCOMMITTED
指定语句可以读取已由其他事务修改但尚未提交的行。Specifies that statements can read rows that have been modified by other transactions but not yet committed.

在 READ UNCOMMITTED 级别运行的事务,不会发出共享锁来防止其他事务修改当前事务读取的数据。Transactions running at the READ UNCOMMITTED level do not issue shared locks to prevent other transactions from modifying data read by the current transaction. READ UNCOMMITTED 事务也不会被排他锁阻塞,排他锁会禁止当前事务读取其他事务已修改但尚未提交的行。READ UNCOMMITTED transactions are also not blocked by exclusive locks that would prevent the current transaction from reading rows that have been modified but not committed by other transactions. 设置此选项之后,可以读取未提交的修改,这种读取称为脏读。When this option is set, it is possible to read uncommitted modifications, which are called dirty reads. 在事务结束之前,可以更改数据中的值,行也可以出现在数据集中或从数据集中消失。Values in the data can be changed and rows can appear or disappear in the data set before the end of the transaction. 该选项的作用与在事务内所有 SELECT 语句中的所有表上设置 NOLOCK 相同。This option has the same effect as setting NOLOCK on all tables in all SELECT statements in a transaction. 这是隔离级别中限制最少的级别。This is the least restrictive of the isolation levels.

SQL ServerSQL Server 中,您还可以使用下列任意一种方法,在保护事务不脏读未提交的数据修改的同时尽量减少锁定争用:In SQL ServerSQL Server, you can also minimize locking contention while protecting transactions from dirty reads of uncommitted data modifications using either:

  • READ COMMITTED 隔离级别,并将 READ_COMMITTED_SNAPSHOT 数据库选项设置为 ON。The READ COMMITTED isolation level with the READ_COMMITTED_SNAPSHOT database option set to ON.

  • SNAPSHOT 隔离级别。The SNAPSHOT isolation level. 有关快照隔离的详细信息,请参阅 SQL Server 中的快照隔离For more information about snapshot isolation, see Snapshot Isolation in SQL Server.

READ COMMITTEDREAD COMMITTED
指定语句不能读取已由其他事务修改但尚未提交的数据。Specifies that statements cannot read data that has been modified but not committed by other transactions. 这样可以避免脏读。This prevents dirty reads. 其他事务可以在当前事务的各个语句之间更改数据,从而产生不可重复读取和虚拟数据。Data can be changed by other transactions between individual statements within the current transaction, resulting in nonrepeatable reads or phantom data. 该选项是 SQL ServerSQL Server 的默认设置。This option is the SQL ServerSQL Server default.

READ COMMITTED 的行为取决于 READ_COMMITTED_SNAPSHOT 数据库选项的设置:The behavior of READ COMMITTED depends on the setting of the READ_COMMITTED_SNAPSHOT database option:

  • 如果将 READ_COMMITTED_SNAPSHOT 设置为 OFF(SQL Server 上的默认设置),则 数据库引擎Database Engine 会使用共享锁防止其他事务在当前事务执行读取操作期间修改行。If READ_COMMITTED_SNAPSHOT is set to OFF (the default on SQL Server), the 数据库引擎Database Engine 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. 共享锁类型确定它将于何时释放。The shared lock type determines when it will be released. 行锁在处理下一行之前释放。Row locks are released before the next row is processed. 页锁在读取下一页时释放,表锁在语句完成时释放。Page locks are released when the next page is read, and table locks are released when the statement finishes.

  • 如果将 READ_COMMITTED_SNAPSHOT 设置为 ON(SQL Azure 数据库上的默认设置),则 数据库引擎Database Engine 会使用行版本控制为每个语句提供一个在事务上一致的数据快照,因为该数据在语句开始时就存在。If READ_COMMITTED_SNAPSHOT is set to ON (the default on SQL Azure Database), 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.

重要

选择事务隔离级别不影响为保护数据修改而获取的锁。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. 此外,在 READ_COMMITTED 隔离级别进行的更新使用所选数据行的更新锁,而在 SNAPSHOT 隔离级别进行的更新使用行版本来选择要更新的行。Additionally, an update made at the READ_COMMITTED isolation level uses update locks on the data rows selected, whereas an update made at the SNAPSHOT isolation level uses row versions to select rows to update. 对于读取操作,事务隔离级别主要定义保护级别,以防受到其他事务所做更改的影响。For read operations, transaction isolation levels primarily define the level of protection from the effects of modifications made by other transactions. 有关详细信息,请参阅事务锁定和行版本控制指南See the Transaction Locking and Row Versioning Guide for more information.

备注

快照隔离支持 FILESTREAM 数据。Snapshot isolation supports FILESTREAM data. 在快照隔离模式下,事务中任何语句读取的 FILESTREAM 数据都将是在事务开始时便存在的数据的事务性一致版本。Under snapshot isolation mode, FILESTREAM data read by any statement in a transaction will be the transactionally consistent version of the data that existed at the start of the transaction.

当 READ_COMMITTED_SNAPSHOT 数据库选项设置为 ON 时,您可以使用 READCOMMITTEDLOCK 表提示为 READ COMMITTED 隔离级别上运行的事务中的各语句请求共享锁,而不是行版本控制。When the READ_COMMITTED_SNAPSHOT database option is ON, you can use the READCOMMITTEDLOCK table hint to request shared locking instead of row versioning for individual statements in transactions running at the READ COMMITTED isolation level.

备注

设置 READ_COMMITTED_SNAPSHOT 选项时,数据库中仅允许存在执行 ALTER DATABASE 命令的连接。When you set 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.

REPEATABLE READREPEATABLE READ
指定语句不能读取已由其他事务修改但尚未提交的行,并且指定,其他任何事务都不能在当前事务完成之前修改由当前事务读取的数据。Specifies that statements cannot read data that has been modified but not yet committed by other transactions and that no other transactions can modify data that has been read by the current transaction until the current transaction completes.

对事务中的每个语句所读取的全部数据都设置了共享锁,并且该共享锁一直保持到事务完成为止。Shared locks are placed on all data read by each statement in the transaction and are held until the transaction completes. 这样可以防止其他事务修改当前事务读取的任何行。This prevents other transactions from modifying any rows that have been read by the current transaction. 其他事务可以插入与当前事务所发出语句的搜索条件相匹配的新行。Other transactions can insert new rows that match the search conditions of statements issued by the current transaction. 如果当前事务随后重试执行该语句,它会检索新行,从而产生虚拟读取。If the current transaction then retries the statement it will retrieve the new rows, which results in phantom reads. 由于共享锁一直保持到事务结束,而不是在每个语句结束时释放,因此并发级别低于默认的 READ COMMITTED 隔离级别。Because shared locks are held to the end of a transaction instead of being released at the end of each statement, concurrency is lower than the default READ COMMITTED isolation level. 此选项只在必要时使用。Use this option only when necessary.

SNAPSHOTSNAPSHOT
指定事务中任何语句读取的数据都将是在事务开始时便存在的数据的事务上一致的版本。Specifies that data read by any statement in a transaction will be the transactionally consistent version of the data that existed at the start of the transaction. 事务只能识别在其开始之前提交的数据修改。The transaction can only recognize data modifications that were committed before the start of the transaction. 在当前事务中执行的语句将看不到在当前事务开始以后由其他事务所做的数据修改。Data modifications made by other transactions after the start of the current transaction are not visible to statements executing in the current transaction. 其效果就好像事务中的语句获得了已提交数据的快照,因为该数据在事务开始时就存在。The effect is as if the statements in a transaction get a snapshot of the committed data as it existed at the start of the transaction.

除非正在恢复数据库,否则 SNAPSHOT 事务不会在读取数据时请求锁。Except when a database is being recovered, SNAPSHOT transactions do not request locks when reading data. 读取数据的 SNAPSHOT 事务不会阻止其他事务写入数据。SNAPSHOT transactions reading data do not block other transactions from writing data. 写入数据的事务也不会阻止 SNAPSHOT 事务读取数据。Transactions writing data do not block SNAPSHOT transactions from reading data.

在数据库恢复的回滚阶段,如果尝试读取由其他正在回滚的事务锁定的数据,则 SNAPSHOT 事务将请求一个锁。During the roll-back phase of a database recovery, SNAPSHOT transactions will request a lock if an attempt is made to read data that is locked by another transaction that is being rolled back. 在事务完成回滚之前,SNAPSHOT 事务会一直被阻塞。The SNAPSHOT transaction is blocked until that transaction has been rolled back. 当事务取得授权之后,便会立即释放锁。The lock is released immediately after it has been granted.

必须将 ALLOW_SNAPSHOT_ISOLATION 数据库选项设置为 ON,才能开始一个使用 SNAPSHOT 隔离级别的事务。The ALLOW_SNAPSHOT_ISOLATION database option must be set to ON before you can start a transaction that uses the SNAPSHOT isolation level. 如果使用 SNAPSHOT 隔离级别的事务访问多个数据库中的数据,则必须在每个数据库中将 ALLOW_SNAPSHOT_ISOLATION 都设置为 ON。If a transaction using the SNAPSHOT isolation level accesses data in multiple databases, ALLOW_SNAPSHOT_ISOLATION must be set to ON in each database.

不能将通过其他隔离级别开始的事务设置为 SNAPSHOT 隔离级别,否则将导致事务中止。A transaction cannot be set to SNAPSHOT isolation level that started with another isolation level; doing so will cause the transaction to abort. 如果一个事务在 SNAPSHOT 隔离级别开始,则可以将它更改为另一个隔离级别,然后再返回 SNAPSHOT。If a transaction starts in the SNAPSHOT isolation level, you can change it to another isolation level and then back to SNAPSHOT. 事务在第一次访问数据时启动。A transaction starts the first time it accesses data.

在 SNAPSHOT 隔离级别下运行的事务可以查看由该事务所做的更改。A transaction running under SNAPSHOT isolation level can view changes made by that transaction. 例如,如果事务对表执行 UPDATE,然后对同一个表发出 SELECT 语句,则修改后的数据将包含在结果集中。For example, if the transaction performs an UPDATE on a table and then issues a SELECT statement against the same table, the modified data will be included in the result set.

备注

在快照隔离模式下,事务中任何语句读取的 FILESTREAM 数据都将是在事务开始(而非语句开始)时便存在的数据的事务性一致版本。Under snapshot isolation mode, FILESTREAM data read by any statement in a transaction will be the transactionally consistent version of the data that existed at the start of the transaction, not at the start of the statement.

SERIALIZABLESERIALIZABLE
请指定下列内容:Specifies the following:

  • 语句不能读取已由其他事务修改但尚未提交的数据。Statements cannot read data that has been modified but not yet committed by other transactions.

  • 任何其他事务都不能在当前事务完成之前修改由当前事务读取的数据。No other transactions can modify data that has been read by the current transaction until the current transaction completes.

  • 在当前事务完成之前,其他事务不能使用当前事务中任何语句读取的键值插入新行。Other transactions cannot insert new rows with key values that would fall in the range of keys read by any statements in the current transaction until the current transaction completes.

范围锁处于与事务中执行的每个语句的搜索条件相匹配的键值范围之内。Range locks are placed in the range of key values that match the search conditions of each statement executed in a transaction. 这样可以阻止其他事务更新或插入任何行,从而限定当前事务所执行的任何语句。This blocks other transactions from updating or inserting any rows that would qualify for any of the statements executed by the current transaction. 这意味着如果再次执行事务中的任何语句,则这些语句便会读取同一组行。This means that if any of the statements in a transaction are executed a second time, they will read the same set of rows. 在事务完成之前将一直保持范围锁。The range locks are held until the transaction completes. 这是限制最多的隔离级别,因为它锁定了键的整个范围,并在事务完成之前一直保持范围锁。This is the most restrictive of the isolation levels because it locks entire ranges of keys and holds the locks until the transaction completes. 因为并发级别较低,所以应只在必要时才使用该选项。Because concurrency is lower, use this option only when necessary. 该选项的作用与在事务内所有 SELECT 语句中的所有表上设置 HOLDLOCK 相同。This option has the same effect as setting HOLDLOCK on all tables in all SELECT statements in a transaction.

RemarksRemarks

一次只能设置一个隔离级别选项,而且设置的选项将一直对那个连接始终有效,直到显式更改该选项为止。Only one of the isolation level options can be set at a time, and it remains set for that connection until it is explicitly changed. 事务中执行的所有读取操作都会在指定的隔离级别的规则下运行,除非语句的 FROM 子句中的表提示为表指定了其他锁定行为或版本控制行为。All read operations performed within the transaction operate under the rules for the specified isolation level unless a table hint in the FROM clause of a statement specifies different locking or versioning behavior for a table.

事务隔离级别定义了可为读取操作获取的锁类型。The transaction isolation levels define the type of locks acquired on read operations. 针对 READ COMMITTED 或 REPEATABLE READ 获取的共享锁通常为行锁,尽管当读取引用了页或表中大量的行时,行锁可以升级为页锁或表锁。Shared locks acquired for READ COMMITTED or REPEATABLE READ are generally row locks, although the row locks can be escalated to page or table locks if a significant number of the rows in a page or table are referenced by the read. 如果某行在被读取之后由事务进行了修改,则该事务会获取一个用于保护该行的排他锁,并且该排他锁在事务完成之前将一直保持。If a row is modified by the transaction after it has been read, the transaction acquires an exclusive lock to protect that row, and the exclusive lock is retained until the transaction completes. 例如,如果 REPEATABLE READ 事务具有用于某行的共享锁,并且该事务随后修改了该行,则共享行锁便会转换为排他行锁。For example, if a REPEATABLE READ transaction has a shared lock on a row, and the transaction then modifies the row, the shared row lock is converted to an exclusive row lock.

在事务进行期间,可以随时将事务从一个隔离级别切换到另一个隔离级别,但有一种情况例外。With one exception, you can switch from one isolation level to another at any time during a transaction. 即在从任一隔离级别更改到 SNAPSHOT 隔离时,不能进行上述操作。The exception occurs when changing from any isolation level to SNAPSHOT isolation. 否则会导致事务失败并回滚。Doing this causes the transaction to fail and roll back. 但是,可以将在 SNAPSHOT 隔离中启动的事务更改为任何其他隔离级别。However, you can change a transaction started in SNAPSHOT isolation to any other isolation level.

将事务从一个隔离级别更改为另一个隔离级别之后,便会根据新级别的规则对更改后读取的资源执行保护。When you change a transaction from one isolation level to another, resources that are read after the change are protected according to the rules of the new level. 在更改前读取的资源将继续按照以前级别的规则受到保护。Resources that are read before the change continue to be protected according to the rules of the previous level. 例如,如果某个事务从 READ COMMITTED 更改为 SERIALIZABLE,则在该事务结束前,更改后所获取的共享锁将一直处于保留状态。For example, if a transaction changed from READ COMMITTED to SERIALIZABLE, the shared locks acquired after the change are now held until the end of the transaction.

如果在存储过程或触发器中发出 SET TRANSACTION ISOLATION LEVEL,则当对象返回控制时,隔离级别会重设为在调用对象时有效的级别。If you issue SET TRANSACTION ISOLATION LEVEL in a stored procedure or trigger, when the object returns control the isolation level is reset to the level in effect when the object was invoked. 例如,如果在批处理中设置 REPEATABLE READ,并且该批处理调用一个将隔离级别设置为 SERIALIZABLE 的存储过程,则当该存储过程将控制返回给该批处理时,隔离级别就会恢复为 REPEATABLE READ。For example, if you set REPEATABLE READ in a batch, and the batch then calls a stored procedure that sets the isolation level to SERIALIZABLE, the isolation level setting reverts to REPEATABLE READ when the stored procedure returns control to the batch.

备注

用户定义的函数和公共语言运行时 (CLR) 用户定义的类型无法执行 SET TRANSACTION ISOLATION LEVEL。User-defined functions and common language runtime (CLR) user-defined types cannot execute SET TRANSACTION ISOLATION LEVEL. 但是,可使用表提示来重写隔离级别。However, you can override the isolation level by using a table hint. 有关详细信息,请参阅表提示 (Transact-SQL)For more information, see Table Hints (Transact-SQL).

当您使用 sp_bindsession 绑定两个会话时,每个会话都会保留它自身的隔离级别设置。When you use sp_bindsession to bind two 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 sessions bound to it.

SET TRANSACTION ISOLATION LEVEL 会在执行或运行时生效,而不是在分析时生效。SET TRANSACTION ISOLATION LEVEL takes effect at execute or run time, and not at parse time.

针对堆的优化大容量负载操作阻塞了运行在以下隔离级别下面的查询:Optimized bulk load operations on heaps block queries that are running under the following isolation levels:

  • SNAPSHOTSNAPSHOT

  • READ UNCOMMITTEDREAD UNCOMMITTED

  • 使用行版本控制的 READ COMMITTEDREAD COMMITTED using row versioning

相反,运行在这些隔离级别下面的查询阻塞了针对堆的优化大容量负载操作。Conversely, queries that run under these isolation levels block optimized bulk load operations on heaps. 有关大容量加载操作的详细信息,请参阅批量导入和导出数据 (SQL Server)For more information about bulk load operations, see Bulk Import and Export of Data (SQL Server).

已启用 FILESTREAM 的数据库支持下列事务隔离级别。FILESTREAM-enabled databases support the following transaction isolation levels.

隔离级别Isolation level Transact SQL 访问Transact SQL access 文件系统访问File system access
未提交读Read uncommitted SQL Server 2017SQL Server 2017 不支持Unsupported
已提交读Read committed SQL Server 2017SQL Server 2017 SQL Server 2017SQL Server 2017
可重复读Repeatable read SQL Server 2017SQL Server 2017 不支持Unsupported
可序列化Serializable SQL Server 2017SQL Server 2017 不支持Unsupported
读提交的快照Read committed snapshot SQL Server 2017SQL Server 2017 SQL Server 2017SQL Server 2017
快照Snapshot SQL Server 2017SQL Server 2017 SQL Server 2017SQL Server 2017

示例Examples

以下示例为会话设置了 TRANSACTION ISOLATION LEVELThe following example sets the TRANSACTION ISOLATION LEVEL for the session. 对于每个后续 Transact-SQLTransact-SQL 语句,SQL ServerSQL Server 将所有共享锁一直保持到事务结束为止。For each Transact-SQLTransact-SQL statement that follows, SQL ServerSQL Server holds all of the shared locks until the end of the transaction.

USE AdventureWorks2012;  
GO  
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;  
GO  
BEGIN TRANSACTION;  
GO  
SELECT *   
    FROM HumanResources.EmployeePayHistory;  
GO  
SELECT *   
    FROM HumanResources.Department;  
GO  
COMMIT TRANSACTION;  
GO  

另请参阅See Also

ALTER DATABASE (Transact-SQL) ALTER DATABASE (Transact-SQL)
DBCC USEROPTIONS (Transact-SQL) DBCC USEROPTIONS (Transact-SQL)
SELECT (Transact-SQL) SELECT (Transact-SQL)
SET 语句 (Transact-SQL) SET Statements (Transact-SQL)
表提示 (Transact-SQL)Table Hints (Transact-SQL)