这是我参与8月更文挑战的第5天，活动详情查看：8月更文挑战

SQL Server设置事务隔离级别

SQL Server默认的隔离级别是读提交(RC——READ COMMITTED)。

Azure SQL数据库的默认设置为READ_COMMITTED_SNAPSHOT

SQL Server中提供5种隔离级别的设置，分别是：READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ、SERIALIZABLE、SNAPSHOT。

可是设置隔离级别的位置有三个：

数据库（DB）——一般使用默认级别，可设置的级别只有RC和RCSI(read committed snapshot ISOLATION)
连接（Connection）——影响当前连接(会话)的所有查询。
语句（Statement）——仅影响指定的查询。

查看SQL Server的隔离级别

数据库的隔离级别

数据库的隔离级别默认为read committed，该级别的行为取决于READ_COMMITTED_SNAPSHOT数据库选项的设置(ON或OFF)。

也就是SQL Server数据库的隔离级别只能是read committed，或者启用了READ_COMMITTED_SNAPSHOT的read committed snapshot isolation(RCSI)。

查看当前使用的数据库的隔离级别：

SELECT name, is_read_committed_snapshot_on
FROM sys.databases
WHERE name = DB_NAME();

is_read_committed_snapshot_on为0，则数据库是RC，否则是RCSI。

如果READ_COMMITTED_SNAPSHOT为OFF(SQL Server的默认)。当前事务执行读操作时，数据库引擎使用共享锁阻止其他事务修改行。共享锁还会阻止语句读取被其他事务修改的行，直到其他事务结束。共享锁的类型决定了它何时释放。行锁在下一行处理之前释放；叶锁在读取下一页时释放；表锁在语句结束时释放。

如果READ_COMMITTED_SNAPSHOT为ON(Azure SQL数据库的默认)。数据库引擎使用行版本为每个语句提供事务一致的数据快照，数据是在语句开始时存在的数据，即使用行版本在语句开始处提供数据快照，用以给每个语句提供事务一致性。锁不用于保护数据免受其他事务的更新。

选择一个事务隔离级别不影响保护数据修改的锁的获取。一个事务在修改任何数据时都会获取一个排它锁，并持有该锁直到事务完成，而不管设置的事务隔离级别。此外，在READ_COMMITTED隔离级别进行的更新使用所选数据行的更新锁，而在SNAPSHOT隔离级别进行的更新使用行版本来选择要更新的行。

开启READ_COMMITTED_SNAPSHOT时，在修改数据时依然会获取排它锁，其他事务获取的数据是基于行版本的[快照数据]，但是两个事务更新快照中属于同一行的数据时，仍然会有排它锁阻塞另一事物的更新（但不会阻塞另一事物对同一数据的获取，因为它使用的是快照数据）。

也就是，快照隔离下，事务读取同一数据不阻塞另一事务写数据；事务修改同一数据不阻塞另一事物读取数据；但是事务修改同一数据会阻塞另一事物修改写数据。

==修改数据时，任何隔离级别都会获取排它锁。==

对于读取操作，事务隔离级别主要定义保护级别，以防受到其他事务所做更改的影响

当前连接的隔离级别

运行DBCC USEROPTIONS;可以返回连接的属性，其中包含隔离级别。

DBCC USEROPTIONS;

或者系统视图sys.dm_exec_sessions中查询transaction_isolation_level，查看其隔离级别的值。

SELECT 
 CASE transaction_isolation_level
  WHEN 0 THEN 'Unspecified'
  WHEN 1 THEN 'Read Uncommitted' 
  WHEN 2 THEN 'Read Committed'
  WHEN 3 THEN 'Repeatable Read'
  WHEN 4 THEN 'Serializable'
  WHEN 5 THEN 'Snapshot'
 END AS TRANSACTION_ISOLATION_LEVEL 
FROM
 sys.dm_exec_sessions 
WHERE
 session_id = @@SPID;

transaction_isolation_level的取值只由0~5，0表示未指定或不知道的级别。

查看所有session/连接的隔离级别

下面的语句可以获取所有正在执行中的连接的隔离级别和一些其他信息：

SELECT session_id, start_time, status, total_elapsed_time,
CASE transaction_isolation_level
	WHEN 1 THEN 'ReadUncomitted'
	WHEN 2 THEN 'ReadCommitted'
	WHEN 3 THEN 'Repeatable'
	WHEN 4 THEN 'Serializable'
	WHEN 5 THEN 'Snapshot'
	ELSE 'Unspecified' 
END AS transaction_isolation_level
FROM sys.dm_exec_requests;

查看所有正在执行中的用户进程的session/连接的隔离级别，除去系统进程的连接。

SELECT r.session_id, r.start_time, r.status, r.total_elapsed_time,
CASE r.transaction_isolation_level
	WHEN 1 THEN 'ReadUncomitted'
	WHEN 2 THEN 'ReadCommitted'
	WHEN 3 THEN 'Repeatable'
	WHEN 4 THEN 'Serializable'
	WHEN 5 THEN 'Snapshot'
	ELSE 'Unspecified' 
END AS transaction_isolation_level
FROM sys.dm_exec_requests as r
JOIN sys.dm_exec_sessions as s
 on s.session_id = r.session_id
WHERE s.is_user_process = 1;

查看所有的用户进程的session/连接的隔离级别，包括没有正在运行的session

SELECT session_id, 
CASE transaction_isolation_level
	WHEN 1 THEN 'ReadUncomitted'
	WHEN 2 THEN 'ReadCommitted'
	WHEN 3 THEN 'Repeatable'
	WHEN 4 THEN 'Serializable'
	WHEN 5 THEN 'Snapshot'
	ELSE 'Unspecified' 
END AS transaction_isolation_level
FROM sys.dm_exec_sessions
WHERE is_user_process = 1;

下面是获取正在执行SQL语句的连接的隔离级别（sql_text），同时获取这些SQL语句：

SELECT session_id, start_time, status, total_elapsed_time,
CASE transaction_isolation_level
	WHEN 1 THEN 'ReadUncomitted'
	WHEN 2 THEN 'ReadCommitted'
	WHEN 3 THEN 'Repeatable'
	WHEN 4 THEN 'Serializable'
	WHEN 5 THEN 'Snapshot'
	ELSE 'Unspecified' 
END AS transaction_isolation_level,
sh.text, ph.query_plan
FROM sys.dm_exec_requests
CROSS APPLY sys.dm_exec_sql_text(sql_handle) sh
CROSS APPLY sys.dm_exec_query_plan(plan_handle) ph

设置SQL Server的隔离级别

启用数据库RCSI级别

数据库的隔离级别为RC(读提交),这是默认设置，或者，还可以启用READ_COMMITTED_SNAPSHOT。使用 SET READ_COMMITTED_SNAPSHOT ON。

如下ALTER语句，启用数据库的READ COMMITTED SNAPSHOT（RCSI）

ALTER DATABASE <DB_Name>
SET READ_COMMITTED_SNAPSHOT ON;

设置READ_COMMITTED_SNAPSHOT为ON，修改的是READ_COMMITTED隔离级别下，数据库引擎的行为，即使用行版本而不是lock加锁实现。但隔离级别仍为RC未变

关闭数据库RCSI级别

使用 SET READ_COMMITTED_SNAPSHOT OFF

ALTER DATABASE AdventureWorks2016 SET READ_COMMITTED_SNAPSHOT OFF;

关闭RCSI隔离级别时，必须不能有任何到当前数据库的其他连接。否则会一直阻塞而无法关闭。

同样，SET READ_COMMITTED_SNAPSHOT ON;开启RCSI时，也不能有其他连接存在。

设置当前连接的隔离级别SET TRANSACTION ISOLATION LEVEL

SET TRANSACTION ISOLATION LEVEL通过设置隔离级别，控制连接到SQL Server所发出的T-SQL语句的锁和行版本行为。

由定义可知，该语句定义当前连接的隔离级别(当前session)

SET TRANSACTION ISOLATION LEVEL 
    [     READ UNCOMMITTED
        | READ COMMITTED
        | REPEATABLE READ
        | SERIALIZABLE
        | SNAPSHOT
    ];

SSMS中设置隔离级别

可以直接在SSMS中设置数据库连接的隔离级别。

SSMS菜单栏中的工具，点击并打开"选项"(Options)，找到查询执行->高级，在Set Transaciton Isolation Level下拉列表中修改。

语句的隔离级别

可以为单个语句指定隔离级别。通过指定一个table hint(表提示)实现。

如下，在表上指定隔离级别为SERIALIZABLE：

SELECT a.Column1, b.column2
FROM TableA a WITH(SERIALIZABLE)
JOIN TableB b WITH(SERIALIZABLE);

由于 SQL Server 查询优化器通常会为查询选择最佳执行计划，因此建议仅在最后迫不得已的情况下才可由资深的开发人员和数据库管理员使用提示（hints）。

关于ALLOW_SNAPSHOT_ISOLATION和READ_COMMITTED_SNAPSHOT

在SQL Server关于snapshot隔离级别设置相关的介绍中，包括如Snapshot Isolation in SQL Server、Enable the snapshot transaction isolation level in SQL Server 2005 Analysis Services等多个官方文档的内容，都将ALLOW_SNAPSHOT_ISOLATION和READ_COMMITTED_SNAPSHOT的设置放在一起使用。

至于这两者的设置，也因此产生小小的困惑。

ALLOW_SNAPSHOT_ISOLATION and READ_COMMITTED_SNAPSHOT和READ_COMMITTED_SNAPSHOT "ON" with ALLOW_SNAPSHOT_ISOLATION "OFF"里面对两者进行了较清晰的区分。而且自己结合实际设置的执行结果，也能验证一二。

为了READ_COMMITTED的默认行为是数据库引擎使用行版本而不是锁，数据库的READ_COMMITTED_SNAPSHOT需要设置为ON。这和ALLOW_SNAPSHOT_ISOLATION的设置无关。
ALLOW_SNAPSHOT_ISOLATION设置为ON，仅仅是为了设置SET TRANSACTION ISOLATION LEVEL SNAPSHOT，当开始事务时允许快照隔离。这和READ_COMMITTED_SNAPSHOT的设置无关。
如果不设置ALLOW_SNAPSHOT_ISOLATION为ON，则无法执行SET TRANSACTION ISOLATION LEVEL SNAPSHOT。

会报错：

Snapshot isolation transaction failed accessing database 'xxx' because snapshot isolation is not allowed in this database. Use ALTER DATABASE to allow snapshot isolation.

如下，查看READ_COMMITTED_SNAPSHOT对隔离级别的影响

-- 启用READ_COMMITTED_SNAPSHOT RCSI，查看隔离级别
ALTER DATABASE AdventureWorks2016 SET READ_COMMITTED_SNAPSHOT ON;
select session_id,Transaction_Isolation_Level from sys.dm_exec_sessions
where session_id=@@spid;

-- 修改ALLOW_SNAPSHOT_ISOLATION，查看隔离级别
ALTER DATABASE AdventureWorks2016 SET ALLOW_SNAPSHOT_ISOLATION ON;
select session_id,Transaction_Isolation_Level from sys.dm_exec_sessions
where session_id=@@spid;

-- 是否启用了RCSI
SELECT name, is_read_committed_snapshot_on
FROM sys.databases
WHERE name = DB_NAME();

由于启用了RCSI，在Begin TRANSACTION是使用的数据时快照数据。

设置允许快照隔离，并设置快照隔离级别：

ALTER DATABASE AdventureWorks2016 SET ALLOW_SNAPSHOT_ISOLATION ON;
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;

设置 READ_COMMITTED_SNAPSHOT ON 选项允许在默认的READ COMMITTED隔离级别下访问版本化的行。

如果 READ_COMMITTED_SNAPSHOT 选项设置为OFF，则必须为每个会话显式设置Snapshot隔离级别才能访问版本化的行。但是，由于隔离级别的不同，在READ_COMMITTED_SNAPSHOT ON下会发生不可重复读等并发问题，而显式设置Snapshot隔离级别后则不会。

设置READ_COMMITTED_SNAPSHOT ON，虽然不会修改隔离级别，但是却影响所有的会话都是使用行版本读取修改数据。如果为OFF，则必须设置Snapshot隔离级别，才能使用行版本。

使用ALLOW_SNAPSHOT_ISOLATION ON，最好在代码中同时使用SET TRANSACTION ISOLATION LEVEL SNAPSHOT。

如果使用SET READ_COMMITTED_SNAPSHOT ON，则不需要修改任何代码，SQL Server自动使用快照数据(即行版本化的数据)。

虽然，两者的设置在行为上都有使用快照数据。但是，仅设置SET READ_COMMITTED_SNAPSHOT ON，由于是读提交隔离级别，仍然会产生不可重复读、丢失更改、幻读等问题。

而设置SET TRANSACTION ISOLATION LEVEL SNAPSHOT后，当前事务处理快照隔离，其他事务对相同数据的修改提交，不影响当前每次读取的数据，即是可重复读的。

这一点直接开启两个连接，进行不同设置并测试即可。

参考

以上主要参考自官方文档和网上一些资料，此处不一一列出，谨表示感谢！

各种隔离级别的群魔乱舞 | SQL全面教程十二：事务(6)厘清SQL Server中如何查看和设置隔离级别，详解RCSI和SNAPSHOT的不同