SQL Server 事务隔离级别

2020-07-30 14:49:11 浏览数 (1)

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

  1. 读取数据时是否占用锁以及所请求的锁类型。
  2. 占用读取锁的时间。
  3. 引用其他事务修改的行的读操作是否：
    • 在该行上的排他锁被释放之前阻塞其他事务。
    • 检索在启动语句或事务时存在的行的已提交版本。
    • 读取未提交的数据修改。

以上说明事务隔离级别主要针对读操作来说的。(DML语句我们可以不考虑事务隔离级别，因为任何事物隔离级别下DML的加锁都很严格，属于得不到就等待的类型)

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

提到事物隔离级别就不能不提这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，且目前只能修改会话级别的事务隔离级别。

代码语言：javascript复制
ALTER DATABASE [dbname] SET ALLOW_SNAPSHOT_ISOLATION ON; --需要单用户模式下修改，因为要加库级别的独占锁。
代码语言：javascript复制
然后执行如下语句修改事务隔离级别：(修改后只在会话级别生效，无法修改全局级别的事务隔离级别)
代码语言：javascript复制
SET TRANSACTION ISOLATION LEVEL
{ READ UNCOMMITTED
| READ COMMITTED
| REPEATABLE READ
| SNAPSHOT
| SERIALIZABLE
}

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

代码语言：javascript复制
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.未提交读

select不对读取的数据加锁，会有脏读出现，相当于为select语句添加了with nolock选项。DML语句正常加锁。

2.已提交读

select语句对读取的数据正常加锁，但是不等事务结束才释放锁，而是读完一个页就会释放，会出现不可重复读和幻读。DML语句正常加锁。

3.已提交读快照

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

4.快照

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

这里猜测快照读隔离级别下会在事务第一次select时在tempdb中建立一个完整的快照，而不是仅依赖于MVCC的行版本机制。

5.可重复读

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

此隔离级别下可以避免不可重复读，但是不可避免幻读。DML正常加锁。

6.序列化读

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

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

举例如下：

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

Ps：对于序列化加的键范围锁是否是我上边所说的那么精确，还需要具体实验，这里只是根据官网猜测会使用多余的一个键范围锁锁定可能造成幻读的记录（总的键范围锁数目为n 1个，n为满足查询条件的行数），具体实验方法参见我的另一篇博客，有兴趣的可以试试。

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

总结：

可以看到SQL Server通过MVCC多版本控制机制在3、4两种隔离级别下实现select语句的不加锁读取，避免了阻塞。在已提交读快照隔离级别下通过mvcc实现了select不阻塞，但是依然会有不可重复读和幻读现象。在快照隔离级别下通过mvcc实现了select不阻塞，同时由于是事务级的快照，也顺带避免了不可重复读和幻读。因此可以发现幻读的解决方式目前只有两种：1是键范围锁，2是mvcc机制的事务级镜像（即snapshot隔离级别的方式）。

Ps:关于Mvcc机制的实现方式参考https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/snapshot-isolation-in-sql-server，但是此文中关于snapshot和read_committed_snapshot的解释有些矛盾，文中把这两种隔离级别混淆了，但是事实上通过试验可以看到这两种isolation level的差别与Mysql中RR和RC下一致性读的差别是很相似的。正如我之前所写的snapshot实现的是事务级的一致性，而read_committed_snapshot实现的是语句级的一致性。

参考文档：

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

文件存储 sql sqlserver 数据库 云数据库SQLServer

0 人点赞