SQL Server 事务隔离级别

https://docs.microsoft.com/zh-cn/sql/t-sql/statements/set-transaction-isolation-level-transact-sql

https://msdn.microsoft.com/zh-cn/library/jj856598(v=sql.120).aspx

一、事务隔离级别控制着事务的如下表现：

读取数据时是否占用锁以及所请求的锁类型。

占用读取锁的时间。

引用其他事务修改的行的读操作是否：

• 在该行上的排他锁被释放之前阻塞其他事务。
• 检索在启动语句或事务时存在的行的已提交版本。
• 读取未提交的数据修改。

以上说明事务隔离级别主要针对读操作来说的。

二、脏读、重复读、幻读的区别：

提到事物隔离级别就不能不提这3个概念，可以说事务隔离级别就是为了避免这3种情况出现的。

脏读：读到了其他事务已修改但未提交的数据

不可重复读：同一事务中两次查询读到的数据不同

幻读：同一事务中两次查询读到的记录数不同

可能有人对幻读和不可重复读的定义不太理解，两者最大的区别实质上在于加锁的不同，后边会有讲解。

三、ANSI/ISO标准定义了下列事务隔离级别，SQL Server数据库引擎支持全部这4种隔离级别：

因此四种隔离级别与脏读、幻读、不可重复读的对应情况如下：

需要特别提醒的是：虽然Mysql、Oracle所支持的事务隔离级别也基本遵循ANSI标准，但却有很大区别：

• Oracle只支持已提交读和序列化读。

• Mysql默认的的可重复读隔离级别通过范围锁实现了避免幻读。

四、除以上4种隔离级别外SQL Server还支持使用行版本控制的其他两个事务隔离级别：

• 一个是默认的read committed隔离级别下的snapshot实现，严格来说并不算一个事务隔离级别，只是read committed的一个特殊形态。
  

• 一个是全新的事务隔离级别----快照隔离级别。

两者的开启方式为：

1、如果要开启SNAPSHOT事务隔离级别，需要预先设置ALLOW_SNAPSHOT_ISOLATION为ON，且目前只能修改会话级别的事务隔离级别。

ALTER DATABASE [dbname] SET ALLOW_SNAPSHOT_ISOLATION ON; --需要单用户模式下修改，因为要加库级别的独占锁。

 然后执行如下语句修改事务隔离级别：(修改后只在会话级别生效，无法修改全局级别的事务隔离级别)

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

2、使用READ_COMMITTED_SNAPSHOT，则直接执行下列ALTER语句修改，是在默认的READ COMMITTED隔离级别下修改的，此隔离级别修改后永久生效，使用dbcc useroptions查看可以看到事务隔离级别被全局的修改成了read committed snapshot。

ALTER DATABASE [dbname] SET READ_COMMITTED_SNAPSHOT ON;

两者的区别在于：

READ_COMMITTED_SNAPSHOT是指Select语句总是读取最新的已提交的数据，即如果有DML事务正在执行，那么select语句不会被阻塞而是读取这些DML事务预先生成的前镜像，这种读只会在表上加Sch-S锁，其他的行锁页锁全部没有。DML数据一旦提交，再次执行Select语句就会立马读到新的数据。

SNAPSHOT隔离级别与上述的区别在于，如果你在同一个事务内执行两次相同的select语句，那么即便在这两次select语句之间发生了数据更改且提交，两次读到的数据也是一样的。

用官网的一句话来描述两者区别就是：READ_COMMITTED_SNAPSHOT提供语句级的一致性，SNAPSHOT事务隔离级别提供事务级的一致性。

五、全部6种隔离级别的加锁模式：

开始说过事务隔离级别主要就是控制读操作加什么锁，锁占用多长时间的的，因此只有搞清各事务隔离级别下的加锁机制才能彻底搞清事务隔离级别的概念和他们的不同。

1.未提交读

2.已提交读

已提交读对读取的数据正常加锁，但是不等事务结束才释放锁，而是读完一个页就会释放，因此可能出现重复读和幻读。这是SQL Server和Oracle的默认事务隔离级别。Oracle的实现更加完美，对Oracle数据库来说，select操作不加锁，因此不会出现读阻塞写，但SQL Server有可能。

3.已提交读快照

SQL Server特有的隔离级别，主要是为了匹配Oracle的已提交读实现的功能，在此隔离级别下，读操作只会对表加一个Sch-S锁，因此读操作不会引发在阻塞，但是会加大tempdb的使用量。

4.快照

同上，读操作也只加Sch-S锁，唯一区别在于实现的一致性读是事务级别的，即快照在tempdb中保留的时间更长。

这里猜测已提交读快照隔离级别下快照的撤销是DML事务结束后，而快照读隔离级别下快照的撤销是select事务结束后。

5.可重复读

可重复读加的锁与已提交读完全一致，区别在于只有在整个事务完成后才会释放锁，而不是读完一个页就释放，此种加锁方式也避免了不可重复读，因为事务期间其他DML无法获取资源上的锁。

6.序列化读

序列化读加的锁与已提交读有区别，此隔离级别下读操作对索引键加的是键范围锁，而不是普通的S、U、X、IS、IU、IX等。

键范围锁的机制基本与Mysql中的范围锁一致，主要是为了防止幻读，其机制在于select操作不但会将读到的键值锁定，还会将上下键值的范围也锁定。

举例如下：

有主键为1,5,8,9,10的记录，select ... where col between 3 and 7;会使用键范围锁将5这条记录锁定，除此之外还会用一个键范围锁将346这几个虚幻的记录也锁定，这样就不能再读取操作期间插入数据了，可以防止幻读。

Ps：对于序列化加的键范围锁是否是我上边所说的那么精确，还需要具体实验，这里只是大概猜测会使用多余的一个键范围锁锁定可能造成幻读的记录，具体实验方法参见我的另一篇博客，有兴趣的可以试试。

http://www.cnblogs.com/leohahah/p/7059852.html

SQL Server 事务隔离级别

标签：csharp   sha   snapshot   microsoft   sans   tween   释放   文档   www.