设置事务隔离级别 （Transact-SQL）

适用于：SQL ServerAzure SQL 数据库Azure SQL 托管实例Azure Synapse Analytics分析平台系统 （PDW）Microsoft Fabric 预览版中的 SQL 数据库

控制到 SQL Server 的连接发出的 Transact-SQL 语句的锁定行为和行版本控制行为。

Transact-SQL 语法约定

语法

Microsoft Fabric 预览版中 SQL Server、Azure SQL 数据库和 SQL 数据库的语法。

SET TRANSACTION ISOLATION LEVEL
    { READ UNCOMMITTED
    | READ COMMITTED
    | REPEATABLE READ
    | SNAPSHOT
    | SERIALIZABLE
    }

Azure Synapse Analytics 和并行数据仓库的语法。

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

参数

READ UNCOMMITTED

指定语句可以读取其他事务修改但尚未提交的行。

在 READ UNCOMMITTED 级别运行的事务不会发出共享锁，以防止其他事务修改当前事务读取的数据。 READ UNCOMMITTED 事务也不会被排他锁阻止，这些锁会阻止当前事务读取已修改但未由其他事务提交的行。 设置此选项后，可以读取未提交的修改，这些修改称为脏读取。 在事务结束之前，可以更改数据中的值，行也可以出现在数据集中或从数据集中消失。 此选项与设置 NOLOCK 事务中所有 SELECT 语句中的所有表的效果相同。 这是隔离级别中限制最少的级别。

在 SQL Server 中，还可以使用下列任意一种方法，在保护事务不脏读未提交的数据修改的同时尽量减少锁定争用：

• 数据库READ COMMITTED选项设置为ON的隔离级别READ_COMMITTED_SNAPSHOT。

• SNAPSHOT隔离级别。 有关快照隔离的详细信息，请参阅 SQL Server 中的快照隔离。

READ COMMITTED

指定语句无法读取已修改但未由其他事务提交的数据。 这样可以避免脏读。 其他事务可以在当前事务的各个语句之间更改数据，从而产生不可重复读取和虚拟数据。 该选项是 SQL Server 的默认设置。

行为 READ COMMITTED 取决于数据库选项的设置 READ_COMMITTED_SNAPSHOT ：

• 如果 READ_COMMITTED_SNAPSHOT 设置为 OFF （SQL Server 上的默认值），则数据库引擎使用共享锁来防止其他事务在当前事务运行读取作时修改行。 共享锁还会阻止语句在其他事务完成之前读取由这些事务修改的行。 共享锁类型确定何时释放。 行锁在处理下一行之前释放。 页锁在读取下一页时释放，表锁在语句完成时释放。

• 如果 READ_COMMITTED_SNAPSHOT 设置为 ON，数据库引擎使用行版本控制来显示每个语句，该语句的快照与语句开头存在的数据的事务一致快照。 锁不用于保护数据免受其他事务的更新。

  • READ_COMMITTED_SNAPSHOT ON 是 azure SQL 数据库和 Microsoft Fabric 预览版中的 SQL 数据库的默认数据库。

快照隔离支持 FILESTREAM 数据。 在快照隔离模式下，事务中任何语句读取的 FILESTREAM 数据是事务开始时存在的数据的事务一致性版本。

READ_COMMITTED_SNAPSHOT数据库选项为ON数据库选项时，可以使用READCOMMITTEDLOCK表提示请求共享锁定，而不是对在隔离级别运行的READ COMMITTED事务中的单个语句进行行版本控制。

可重复读取

指定语句无法读取已修改但尚未由其他事务提交的数据，并且其他事务无法修改当前事务读取的数据，直到当前事务完成。

对事务中的每个语句所读取的全部数据都设置了共享锁，并且该共享锁一直保持到事务完成为止。 这样可以防止其他事务修改当前事务读取的任何行。 其他事务可以插入与当前事务所发出语句的搜索条件相匹配的新行。 如果当前事务随后重试该语句，它将检索新行，这会导致虚拟读取。 由于共享锁保留在事务的末尾，而不是在每个语句结束时释放，因此并发级别低于默认 READ COMMITTED 隔离级别。 此选项只在必要时使用。

SNAPSHOT

指定事务中任何语句读取的数据是事务开始时存在的数据的事务一致性版本。 事务只能识别在其开始之前提交的数据修改。 在当前事务开始时由其他事务进行的数据修改对当前事务中执行的语句不可见。 其效果就好像事务中的语句获得了已提交数据的快照，因为该数据在事务开始时就存在。

除了恢复数据库时， SNAPSHOT 事务在读取数据时不会请求锁。 SNAPSHOT 读取数据的事务不会阻止其他事务写入数据。 写入数据的事务不会阻止 SNAPSHOT 事务读取数据。

在数据库恢复的回滚阶段，如果尝试读取被回滚的另一个事务锁定的数据， SNAPSHOT 则事务会请求锁定。 在回滚该事务之前，将 SNAPSHOT 阻止该事务。 该锁在获得授权后立即释放。

ALLOW_SNAPSHOT_ISOLATION必须先将ON数据库选项设置为，然后才能启动使用SNAPSHOT隔离级别的事务。 如果使用隔离级别的事务 SNAPSHOT 访问多个数据库中的数据， ALLOW_SNAPSHOT_ISOLATION 则必须在每个数据库中设置为 ON 。

事务不能设置为 SNAPSHOT 从另一个隔离级别开始的隔离级别;这样做会导致事务中止。 如果事务在隔离级别启动 SNAPSHOT ，则可以将其更改为另一个隔离级别，然后返回到 SNAPSHOT。 事务在第一次访问数据时启动。

在隔离级别下 SNAPSHOT 运行的事务可以查看该事务所做的更改。 例如，如果事务对表执行作 UPDATE ，然后针对同一 SELECT 表发出语句，则修改后的数据将包含在结果集中。

SERIALIZABLE

指定以下条件：

• 语句无法读取已修改但尚未由其他事务提交的数据。

• 其他任何事务都无法修改当前事务读取的数据，直到当前事务完成。

• 其他事务不能插入键值的新行，这些键值将属于当前事务中任何语句读取的键范围，直到当前事务完成。

范围锁处于与事务中执行的每个语句的搜索条件相匹配的键值范围之内。 这样可以阻止其他事务更新或插入任何行，从而限定当前事务所执行的任何语句。 这意味着，如果事务中的任何语句第二次执行，则会读取相同的行集。 在事务完成之前将一直保持范围锁。 这是限制最多的隔离级别，因为它锁定了键的整个范围，并在事务完成之前一直保持范围锁。 因为并发级别较低，所以应只在必要时才使用该选项。 此选项与设置 HOLDLOCK 事务中所有 SELECT 语句中的所有表的效果相同。

备注

一次只能设置一个隔离级别选项，并且该选项将一直设置为该连接，直到显式更改。 除非语句的子句中的 FROM 表提示为表指定不同的锁定或版本控制行为，否则在事务中执行的所有读取作都按照指定隔离级别的规则进行作。

事务隔离级别定义了可为读取操作获取的锁类型。 为或REPEATABLE READ通常是行锁获取READ COMMITTED的共享锁，但如果读取引用了大量页或表中的行，则行锁可以升级为页锁或表锁。 如果事务在读取后修改了某一行，则事务将获取一个独占锁来保护该行，并在事务完成之前保留排他锁。 例如，如果事务 REPEATABLE READ 对行具有共享锁，并且事务随后修改该行，则共享行锁将转换为排他行锁。

在事务进行期间，可以随时将事务从一个隔离级别切换到另一个隔离级别，但有一种情况例外。 从任何隔离级别更改为 SNAPSHOT 隔离时，会发生异常。 否则会导致事务失败并回滚。 但是，可以将以隔离方式启动的 SNAPSHOT 事务更改为任何其他隔离级别。

将事务从一个隔离级别更改为另一个隔离级别之后，便会根据新级别的规则对更改后读取的资源执行保护。 在更改前读取的资源将继续按照以前级别的规则受到保护。 例如，如果事务已从 READ COMMITTED 此更改为 SERIALIZABLE，则更改后获取的共享锁将一直保留到事务结束。

如果在存储过程或触发器中发出问题 SET TRANSACTION ISOLATION LEVEL ，当对象返回控件时，隔离级别在调用对象时将重置为有效级别。 例如，如果在批处理中设置 REPEATABLE READ ，然后批处理将调用将隔离级别 SERIALIZABLE设置为的存储过程，则存储过程返回对批处理的控制时，隔离级别设置将恢复 REPEATABLE READ 为。

用于 sp_bindsession 绑定两个会话时，每个会话都会保留其隔离级别设置。 用于 SET TRANSACTION ISOLATION LEVEL 更改一个会话的隔离级别设置不会影响绑定到该会话的任何其他会话的设置。

SET TRANSACTION ISOLATION LEVEL 在执行或运行时生效，而不是在分析时生效。

针对堆的优化大容量负载操作阻塞了运行在以下隔离级别下面的查询：

• SNAPSHOT
• READ UNCOMMITTED
• READ COMMITTED 使用行版本控制

相反，运行在这些隔离级别下面的查询阻塞了针对堆的优化大容量负载操作。 有关大容量加载操作的详细信息，请参阅批量导入和导出数据 (SQL Server)。

已启用 FILESTREAM 的数据库支持下列事务隔离级别。

示例

以下示例为会话设置了 TRANSACTION ISOLATION LEVEL。 对于每个后续 Transact-SQL 语句，SQL Server 将所有共享锁一直保持到事务结束为止。

USE AdventureWorks2022;
GO

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
GO

BEGIN TRANSACTION;
GO

SELECT *
FROM HumanResources.EmployeePayHistory;
GO

SELECT *
FROM HumanResources.Department;
GO

COMMIT TRANSACTION;
GO

相关内容

• ALTER DATABASE （Transact-SQL）
• DBCC USEROPTIONS （Transact-SQL）
• SELECT （Transact-SQL）
• SET 语句 (Transact-SQL)
• 表提示 （Transact-SQL）