MySQL事务隔离级别、InnoDB使用MVCC+各种锁实现了RC和RR事务隔离级别、具体案例

事务隔离级别

• 脏读：一个事务读取到另一个未提交事务的更改。
• 不可重复读：一个事务在两次读取同一数据时，发现数据被另一个已提交事务修改了。
• 幻读：一个事务在读取过程中，因其他事务的插入而导致返回的行数不一致，查询到了奇怪的结果

SQL标准定义了如下四个隔离级别，注意是SQL标准，不是MYSQL标准：

• READ-UNCOMMITTED(读取 未提交) ：最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。几乎没人用，太不安全了。
• READ-COMMITTED(RC，读取 已提交) ：允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。经常用，可以确保并发的性能。
• REPEATABLE-READ(RR，可重复读) ：在事务开始（start TRANSACTION，关闭自动提交）和结束之间（commit，rollback之前），对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。也经常被用，可以确保安全。
  虽然标准SQL隔离级别定义是说RR下幻读仍然可能发生，但是在MYSQL的InnoDB下实现的RR隔离级别是解决了幻读问题的发生的，先有个印象，我一开始看也非常懵逼。
• SERIALIZABLE(可串行化) ：最高的隔离级别，完全服从 ACID 的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。几乎没人用，事务并发性能太差了。

隔离级别	脏读	不可重复读	幻读
READ-UNCOMMITTED,读未提交	√	√	√
READ-COMMITTED,读已提交	×	√	√
REPEATABLE-READ,可重复读	×	×	√
SERIALIZABLE,可串行化	×	×	×

InnoDB使用MVCC+各种锁实现了RC和RR事务隔离级别

重点当然是讲RR的实现，因为他是MySQL默认存储引擎InnoDB的默认隔离级别。下面先说明实现存在的问题，再说解决方法。

下面无特别说明都用RR事务隔离级别做例子。

一致性非锁定读

对于 一致性非锁定读（Consistent Nonlocking Reads）open in new window的实现，通常做法是加一个版本号或者时间戳字段，在更新数据的同时版本号 + 1 或者更新时间戳。查询时，将当前可见的版本号与对应记录的版本号进行比对，如果记录的版本小于可见版本，则表示该记录可见。

在InnoDB引擎中，多版本控制(multi versioning)就是对上面描述的非锁定读的实现。
如果读取的行正被其他事务执行 DELETE 或 UPDATE 操作，这时读取操作不会去等待行上锁的释放。相反地，InnoDB 存储引擎会去读取行的一个快照数据，对于这种读取历史数据的方式，我们叫它快照读 (snapshot read)。

在 Repeatable Read 和 Read Committed 两个隔离级别下，如果是执行普通的 select 语句（不包括 select ... lock in share mode ,select ... for update）则会使用 一致性非锁定读（MVCC，多版本并发控制 (Multi-Version Concurrency Control)）。并且在 Repeatable Read 下 MVCC 实现了可重复读和防止部分幻读

---

一致性非锁定读 代码举例
①对两个事务分别commit先，确保都提交了之前的工作。

②事务2虽然update修改了数据，但是可以发现事务1读取的是之前的快照

③先让事务2commit提交，可以发现事务1读取的还不是事务2修改的值

④等到事务1也commit了，下一次开启事务，查到的才是事务2修改的值

---

锁定读

如果执行的是下列三类语句，就是锁定读（Locking Reads）

• select ... lock in share mode
• select ... for update
• insert、update、delete操作

在锁定读下，读取的是数据的最新版本，这种读也被称为 当前读（current read）。锁定读 会对读取到的记录加锁：

• select ... lock in share mode：对记录加 S 锁，其它事务也可以加S锁，如果加 x 锁则会被阻塞
• select ... for update、insert、update、delete：对记录加 X 锁，且其它事务不能加任何锁
  在一致性非锁定读下，即使读取的记录已被其它事务加上 X 锁，这时记录也是可以被读取的，即读取的快照数据。上面举过例子了，在RR可重复读下，MVCC可以防止一部分情况的幻读，这一部分幻读是指在一致性非锁定读的情况下，它会根据Read View去判断数据是否可见，也就是读到的是历史数据，读的是快照。但是MVCC无法防止在当前读的情况所产生的幻读现象，当前读读取的都是最新数据。

如果单靠MVCC，在当前读的情况下，这时如果两次查询中间有其它事务插入数据，就会产生幻读。所以，InnoDB 在实现Repeatable Read时，如果执行的是当前读的 select 查询(说明执行了lock in share mode or for update)，则会对读取的记录使用 Next-key Lock 来防止其它事务在间隙间插入数据。
Next-key Lock ：（行锁record lock + 间隙锁gap lock，行锁只能锁住已经存在的行，为了避免插入新行，需要依赖间隙锁）

• 如果数据存在，使用lock in share mode or for update就是Next-key Lock用（读 or 写）行锁
• 如果数据不存在，使用使用lock in share mode or for update就是Next-key Lock用间隙锁，锁住区间，不让另一个事务在这个区间插入新数据。

---

当前读的幻读现象问题实例：
①这里使用一致性非锁定读环境下说明当前读下的幻读现象问题，因为这种情况下InnoDB只用了MVCC，没用Next-key Lock 来select查询

问题就出在事务Aselect查询时，不应该出现幻读现象，应该在 select 查询时，带上lock in share mode or for update，则会对读取的记录使用 Next-key Lock 来防止其它事务在间隙间插入数据。

把原来的表删了，重新演示：

总结一下：

事务1的连续两个快照读中出现当前读不一定会出现幻读，得看事务1的当前读有没有覆盖到其他事务新增的数据，如果没的话，则不会出现幻读。

下面是连续两个快照读中出现当前读会出现幻读，因为事务1的当前读覆盖到其他事务新增的数据。

---