【MYSQL】事务隔离级别、脏读、不可重复读、幻读

参考
作者
Guide: 事务隔离级别
美团技术团队： Innodb中的事务隔离级别和锁的关系

一、介绍

数据库事务的隔离级别是指在多个事务并发执行时，数据库系统为了保证数据一致性所遵循的规定。

SQL 标准定义了四个隔离级别：

• READ-UNCOMMITTED(读取未提交) ：最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。
• READ-COMMITTED(读取已提交) ：允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。
• REPEATABLE-READ(可重复读) ：对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。
• SERIALIZABLE(可串行化) ：最高的隔离级别，完全服从 ACID 的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。

MySQL InnoDB 存储引擎的默认支持的隔离级别是 REPEATABLE-READ（可重读）。我们可以通过SELECT @@tx_isolation;命令来查看，MySQL 8.0 该命令改为SELECT @@transaction_isolation;

MySQL> SELECT @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+

二、演示

MySQL 命令行的默认配置中事务都是自动提交的，即执行 SQL 语句后就会马上执行 COMMIT 操作。如果要显式地开启一个事务需要使用命令：START TRANSACTION。
我们可以通过下面的命令来设置隔离级别。

SET [SESSION|GLOBAL] TRANSACTION ISOLATION LEVEL [READ UNCOMMITTED|READ COMMITTED|REPEATABLE READ|SERIALIZABLE]

来看一下我们在下面实际操作中使用到的一些并发控制语句:

• START TRANSACTION |BEGIN：显式地开启一个事务。
• COMMIT：提交事务，使得对数据库做的所有修改成为永久性。
• ROLLBACK：回滚会结束用户的事务，并撤销正在进行的所有未提交的修改。

脏读

脏读(读未提交)

避免脏读(读已提交)

不可重复读

还是刚才上面的读已提交的图，虽然避免了读未提交，但是却出现了，一个事务还没有结束，就发生了 不可重复读问题。
为什么叫不可重复读，事务1同样的查询，后一次和前一次的结果不一样，这就是不可重读（重新读取产生的结果不一样），这就很可能带来一些问题

可重复读

虽然事务2修改并提交了数据，但事务1多次读取查询数据的结果相同，所以说它是可重读的。

幻读

SQL 脚本 1 在第一次查询工资为 500 的记录时只有一条，SQL 脚本 2 插入了一条工资为 500 的记录，提交之后；SQL 脚本 1 在同一个事务中再次使用当前读查询发现出现了两条工资为 500 的记录这种就是幻读。

解决幻读的方式有很多，但是它们的核心思想就是一个事务在操作某张表数据的时候，另外一个事务不允许新增或者删除这张表中的数据了。

三、不可重复读和幻读的区别

1. 很多人容易搞混不可重复读和幻读，确实这两者有些相似。但不可重复读重点在于update和delete，而幻读的重点在于insert。
   幻读其实可以看作是不可重复读的一种特殊情况，单独把区分幻读的原因主要是解决幻读和不可重复读的方案不一样。

2. 如果使用锁机制来实现这两种隔离级别。在可重复读中，该sql第一次读取到数据后，就将这些数据加锁，其它事务无法修改这些数据，就可以实现可重复读了。但这种方法却无法锁住insert的数据，所以当事务A先前读取了数据，或者修改了全部数据，事务B还是可以insert数据提交，这时事务A就会发现莫名其妙多了一条之前没有的数据，这就是幻读，不能通过行锁来避免。(在innoDB的可重复读隔离级别用间隙锁和临键锁很大程度上避免了幻读) 或者Serializable隔离级别 ，读用读锁，写用写锁，读锁和写锁互斥，这么做可以有效的避免幻读、不可重复读、脏读等问题，但会极大的降低数据库的并发能力。

3. 所以说不可重复读和幻读最大的区别，就在于如何通过锁机制来解决他们产生的问题。上文说的，是使用悲观锁机制来处理这两种问题，但是MySQL、ORACLE、PostgreSQL等成熟的数据库，出于性能考虑，都是使用了以乐观锁为理论基础的MVCC（多版本并发控制）来避免这两种问题。

四、MYSQL中事务隔离级别的实现

MySQL （准确的说是MySQL中的innoDB存储引擎，因为MyISAM不支持MVCC和行级锁）的隔离级别基于锁和 MVCC （多版本并发控制）机制共同实现的。（具体实现细节见：MYSQL锁机制和MVCC的实现）
SERIALIZABLE 隔离级别是通过锁来实现的，READ-COMMITTED 和 REPEATABLE-READ 隔离级别是基于 MVCC 实现的。不过， SERIALIZABLE 之外的其他隔离级别可能也需要用到锁机制，就比如 REPEATABLE-READ 在当前读情况下需要使用加锁读来保证不会出现幻读。