mysql 实践事务隔离级别_实践MySQL事务隔离级别

本文通过实践，来学习下MySQL的事务隔离级别。

0. 测试表准备

首先创建一张用于测试的表tb_test：

然后插入3条测试记录：

1. READ UNCOMMITTED级别

开启终端A，将session的隔离级别设置为READ UNCOMMITTED级别，使用BEGIN语句开启一个事务，然后读取id=1的记录：

查询结果：

然后开启终端B，使用BEGIN语句开启一个事务，并更新id=1的记录的text字段：

可看到执行成功：

切换回终端A，然后重新查询id=1的记录：

查询结果如下：

可以看到，虽然终端B中的事务尚未最终提交，但是也查询到了新的字段值，这就是脏读。这是非常危险的一种模式，因为终端B中的事务有可能会滚，这时就存在数据不一致性。

扫尾工作，将终端A和终端B的事务都正式提交：

总结：

为了解决脏读问题，我们可以将隔离级别提高到READ COMITTED级别。

2. READ COMMITTED级别

在进行该隔离级别测试之前，我们先看下当前表中的数据现状：

下面开始进行测试验证。

打开终端A，将该session的隔离级别设置为READ COMMITTED，然后执行查询：

查询结果如下：

然后打开终端B，更新id=1的记录的字段text的值：

可以看到执行成功：

切换回终端A，重新查询：

查询结果如下：

可以看到终端B中的更新未查询出来。现在我们将终端B的事务提交：

然后再切换回终端A重新查询：

可以看到查出了终端B提交的更新：

可以看出来，READ COMMITTED隔离级别能避免脏读，但是也存在另一个问题，就是终端A的这个事务，在同一个事务内进行的相同查询，查询出来的结果会不一样。这种不可重复读一般称为幻读，这种模式在某些业务场景下也是难以接受的。

扫尾工作，将终端A和终端B的事务都正式提交：

总结：

1、 在MySQL底层，脏读问题到底是如何解决的？为什么无法避免幻读？

上面的`SELECT * FROM tb_test;`语句其实是一种普通的`无锁读`语句，在MySQL官方文档中称为`Consistent Nonlocking Reads`，这种语句防止`脏读`的原理是基于`MVCC`，即`多版本并发控制`，简单讲就是每个提交的事务都对应一个版本。而在`READ COMMITTED`的隔离级别中，每次都读取最近已完成提交的那次事务快照，即可保证避免脏读。但是由于每次执行这种`SELECT * FROM tb_test;`时，都是读取的`最新`的已提交事务快照，因此无法避免`幻读`。

2、 对于READ COMMITTED隔离级别，事务内部执行的语句除了普通的无锁读，肯定还存在带锁读，MySQL官方文档中称为locking reads，比如SELECT FOR UPDATE语句、UPDATE语句、DELETE语句，这种带锁读对阻塞其他事务的INSERT么？

答案是不阻塞，因为该隔离级别未开启`间隙锁`，也就是说不阻塞其他事务在查询的记录间隙(比如`SELECT * FROM tb_test where id >= 10 and id <=20 FOR UPDATE;`，这里的间隙就是`10~20`)插入新记录。这里之所以提到这一点是为了跟后面的`REPEATABLE READ`做对比，此隔离级别是开启`间隙锁`的，也就是防止在间隙内插入记录，这样的好处是可以避免`幻读`问题。

3、 为了解决幻读的问题，我们可以将事务隔离级别继续升级为REPEATABLE READ。

3. REPEATABLE READ级别

SQL标准中REPEATABLE READ是不要求防止幻读的，但是MySQL实现的更严格一些，做到了防止幻读。

幻读定义：事务A 按照一定条件进行数据读取， 期间事务B 插入了相同搜索条件的新数据，事务A再次按照原先条件进行读取时，发现了事务B 新插入的数据称为幻读。如果事务A 按一定条件搜索， 期间事务B 删除了符合条件的某一条数据，导致事务A 再次读取时数据少了一条，这种情况归为不可重复读，不归为幻读。

开始测试前，我们先准备下测试数据：

下面我们开始测试。打开终端A，将事务调整到REPEATABLE READ级别，并执行如下查询：

注意我这里特意查询了一个范围：id >= 10 and id <=20。查询结果如下：

然后切换到终点B，插入一条id=15的记录：

可以看到执行成功：

然后切换回终端A，重新执行前面查询：

查询结果如下，没有变化：

可见避免了幻读。那么我们在终端B中如果删除id=20的记录，那么终端A能查到吗？可以继续试试。切换到终端B执行：

再切换到终端A执行：

查询结果依然没有变化：

可见也避免了删除场景的不可重复读问题。

扫尾工作：将终端A的事务进行提交。

然后执行查询SELECT * FROM tb_test where id >= 10 and id <=20;看到了终端B的更新记录：

总结：

1、 MySQL REPEATABLE READ隔离级别如何防止脏读和幻读？

对于普通的`无锁读`语句， `REPEATABLE READ`隔离级别防止`脏读`的原理和`REPEATABLE READ`是一样的，都是基于`MVCC`，只不过 `REPEATABLE READ`事务内的`SELECT`语句每次不是读取最新的已提交快照，而是读取的第一次已提交快照，因此也防止了`幻读`。

2、 对于REPEATABLE READ隔离级别，事务内部执行的语句除了普通的无锁读，肯定也还存在带锁读，比如SELECT FOR UPDATE语句、UPDATE语句、DELETE语句，这种带锁读对阻塞其他事务的INSERT么？

答案是阻塞，该隔离级别开启了`间隙锁`，因此会阻塞对`SELECT FOR UPDATE`语句选中的记录进行更新、删除，阻塞对选中的范围间隙进行插入。

参考资料：