MySQL 事务隔离级别详解

MySQL 事务隔离级别详解

事务的隔离级别

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 |
+-----------------+

从上面对 SQL 标准定义了四个隔离级别的介绍可以看出，标准的 SQL 隔离级别定义里，REPEATABLE-READ(可重复读)是不可以防止幻读的。

但是！InnoDB 实现的 REPEATABLE-READ 隔离级别其实是可以解决幻读问题发生的，主要有下面两种情况：

• 快照读 ：由 MVCC 机制来保证不出现幻读。
• 当前读 ： 使用 Next-Key Lock 进行加锁来保证不出现幻读，Next-Key Lock 是行锁（Record Lock）和间隙锁（Gap Lock）的结合，行锁只能锁住已经存在的行，为了避免插入新行，需要依赖间隙锁。

因为隔离级别越低，事务请求的锁越少，所以大部分数据库系统的隔离级别都是 READ-COMMITTED ，但是你要知道的是 InnoDB 存储引擎默认使用 REPEATABLE-READ 并不会有任何性能损失。

对于脏读、不可重复读、幻读的理解

• **脏读：**也就是只在读未提交会出现的情况，其实就是在一个事务中读取了别的事务中修改了但未提交的数据，后期回滚了也就是另一个事务结束后没有对数据进行更改，但是进行中修改了，而第一个事务读取到了第二个修改的数据，也就是所谓的脏数据。
• **不可重复读：**对读已提交的机制来说，读已提交很好的解决了脏读的问题，因为他只会读取到事务提交后的数据，但是某个事务在进行读取的操作时，就会读到另一个事务修改过的且已经提交的数据，这时在一个事务中，多次读取就会造成不一样的数据，造成了数据不一致，所以不可重复读。
• **幻读：**幻读其实和不可重复读有点类似都是在一次事务中多次读取而会读取到别的事物已经提交过的数据，但是幻读针对的时数据整体，比如插入操作，而不可重复读针对的是某一行的数据，针对的是更新和删除操作。

InnoDB解决幻读的机制

• **快照读：**针对的是单纯的使用select操作的情况，使用MVCC来解决幻读的情况，其实就是每次开始一个事务的第一条select操作时，其实看到的是MVCC保存的数据快照，这个快照是在第一次执行select的时候生成的，在之后的select中都是查找的这个快照的数据直到事务结束（也有个例如下幻读例子）。
• **当前读：**主要针对的时update、delete、insert、select…for update等需要操作最新记录的操作时使用记录锁+间隙锁（具体见下面）的组合（next-key lock）来解决。

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

1. 将事务隔离级别调整为 SERIALIZABLE 。
2. 在可重复读的事务级别下，给事务操作的这张表添加表锁。
3. 在可重复读的事务级别下，给事务操作的这张表添加 Next-key Lock（Record Lock+Gap Lock）。

MySQL间隙锁机制

当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP)”，InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁（GAP LOCK）。举例来说，假如user表中只有101条记录，其empid的值分别是 1,2,…,100,101，下面的SQL：

select * from  user where user_id > 100 for update;
1

这是一个范围条件的检索且要求加上排他锁，InnoDB不仅会对符合条件的user_id值为101的记录加锁，也会对user_id大于101（这些记录并不存在）的“间隙”加锁。

InnoDB使用间隙锁的目的，一方面是为了防止幻读（为了防止幻读去锁表则影响太大，会影响效率），以满足相关隔离级别的要求，对于上面的例子，要是不使用间隙锁，如果其他事务插入了user_id大于100的任何记录，那么本事务如果再次执行上述语句，就会发生幻读；另外一方面，是为了满足其恢复和复制的需要（发生幻读时的binlog，如果直接拿到备库去执行会发生了主备数据不一致的严重问题）。

很显然，在使用范围条件检索并锁定记录时，InnoDB这种加锁机制会阻塞符合条件范围内键值的并发插入，这往往会造成严重的锁等待。因此，在实际应用开发中，尤其是并发插入比较多的应用，我们要尽量优化业务逻辑，尽量使用相等条件来访问更新数据，避免使用范围条件；当然，对一条不存在的记录加锁，也会有间隙锁的问题。

间隙锁在InnoDB的唯一作用就是防止其它事务的插入操作，以此来达到防止幻读的发生，所以间隙锁不分什么共享锁与排它锁。如果InnoDB扫描的是一个主键、或是一个唯一索引的话，那InnoDB只会采用行锁方式来加锁，而不会使用Next-Key Lock的方式，也就是说不会对索引之间的间隙加锁。

要禁止间隙锁的话，可以把隔离级别降为读已提交，或者开启参数innodb_locks_unsafe_for_binlog。

幻读并没有被完全解决

案例一：

事务一首先查询一条id为5的数据，实际上此条数据并不存在，此时事务二新增了一条id为5的数据并提交事务，此时由于MVCC机制，事务一继续查5依然为空，但是如果事务一对id为5的数据进行修改之后，再查id为5的数据就能查到了。

其原因就是使用update之后，updo log中新记录的修改了DB_TRX_ID的值，导致后面查询的时候的快照中的DB_TRX_ID为事务A的值，所以可以看到记录，发生幻读。

为什么会这样，首先我们需要知道InnoDB对MVCC的实现，详细见InnoDB存储引擎对MVCC的实现

了解了MVCC的实现之后，我们就可以分析假设事务一的事务id为101，此时m_low_limit_id为102，没有其他活跃列表所以m_up_limit_id=m_low_limit_id=102，此时在updo log中就会出现一条DB_TRX_ID为101的数据，再去查询时，由于之前的readView不变，101<102,所以此时可以看到新增的数据，造成幻读。

# 事务 A
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t_stu where id = 5;
Empty set (0.01 sec)

# 事务 B
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t_stu values(5, '小美', 18);
Query OK, 1 row affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

# 事务 A
mysql> update t_stu set name = '小林coding' where id = 5;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from t_stu where id = 5;
+----+--------------+------+
| id | name         | age  |
+----+--------------+------+
|  5 | 小林coding   |   18 |
+----+--------------+------+
1 row in set (0.00 sec)

案例二：

除了上面这一种场景会发生幻读现象之外，还有下面这个场景也会发生幻读现象。

• T1 时刻：事务 A 先执行「快照读语句」：select * from t_test where id > 100 得到了 3 条记录。
• T2 时刻：事务 B 往插入一个 id= 200 的记录并提交；
• T3 时刻：事务 A 再执行「当前读语句」 select * from t_test where id > 100 for update 就会得到 4 条记录，此时也发生了幻读现象。

要避免这类特殊场景下发生幻读的现象的话，就是尽量在开启事务之后，马上执行 select … for update 这类当前读的语句，因为它会对记录加 next-key lock，从而避免其他事务插入一条新记录。