老生常谈：聊聊mysql幻读问题？

之前有位小伙伴美团三面一直被追求「幻读是否被 MySQL 可重复度隔离级别彻底解决了」之前我也提到过MySQL InnoDB 引擎的默认隔离级别虽然是「可重复读」但是它很大程度上避免幻读现象并不是完全解决了解决的方案有两种针对快照读普通 select 语句是通过 MVCC 方式解决了幻读因为可重复读隔离级别下事务执行过程中看到的数据一直跟这个事务启动时看到的数据是一致的即使中途有其他事务插入了一条数据是查询不出来这条数据的所以就很好了避免幻读问题。针对当前读select ... for update 等语句是通过 next-key lock记录锁间隙锁方式解决了幻读因为当执行 select ... for update 语句的时候会加上 next-key lock如果有其他事务在 next-key lock 锁范围内插入了一条记录那么这个插入语句就会被阻塞无法成功插入所以就很好了避免幻读问题。这次我会举例两个实验场景来说明 MySQL InnoDB 引擎的可重复读隔离级别发生幻读的问题。好了发车什么是幻读首先来看看 MySQL 文档是怎么定义幻读Phantom Read的:The so-called phantom problem occurs within a transaction when the same query produces different sets of rows at different times. For example, if a SELECT is executed twice, but returns a row the second time that was not returned the first time, the row is a “phantom” row.翻译当同一个查询在不同的时间产生不同的结果集时事务中就会出现所谓的幻象问题。例如如果 SELECT 执行了两次但第二次返回了第一次没有返回的行则该行是“幻像”行。举个例子假设一个事务在 T1 时刻和 T2 时刻分别执行了下面查询语句途中没有执行其他任何语句SELECT * FROM t_test WHERE id 100;只要 T1 和 T2 时刻执行产生的结果集是不相同的那就发生了幻读的问题比如T1 时间执行的结果是有 5 条行记录而 T2 时间执行的结果是有 6 条行记录那就发生了幻读的问题。T1 时间执行的结果是有 5 条行记录而 T2 时间执行的结果是有 4 条行记录也是发生了幻读的问题。隔离级别当多个事务并发执行时可能会遇到「脏读、不可重复读、幻读」的现象这些现象会对事务的一致性产生不同程序的影响。脏读读到其他事务未提交的数据不可重复读前后读取的数据不一致幻读前后读取的记录数量不一致。这三个现象的严重性排序如下图片SQL 标准提出了四种隔离级别来规避这些现象隔离级别越高性能效率就越低这四个隔离级别如下读未提交read uncommitted指一个事务还没提交时它做的变更就能被其他事务看到读提交read committed指一个事务提交之后它做的变更才能被其他事务看到可重复读repeatable read指一个事务执行过程中看到的数据一直跟这个事务启动时看到的数据是一致的MySQL InnoDB 引擎的默认隔离级别串行化serializable 会对记录加上读写锁在多个事务对这条记录进行读写操作时如果发生了读写冲突的时候后访问的事务必须等前一个事务执行完成才能继续执行针对不同的隔离级别并发事务时可能发生的现象也会不同。图片也就是说在「读未提交」隔离级别下可能发生脏读、不可重复读和幻读现象在「读提交」隔离级别下可能发生不可重复读和幻读现象但是不可能发生脏读现象在「可重复读」隔离级别下可能发生幻读现象但是不可能脏读和不可重复读现象在「串行化」隔离级别下脏读、不可重复读和幻读现象都不可能会发生。所以要解决脏读现象就要升级到「读提交」以上的隔离级别要解决不可重复读现象就要升级到「可重复读」的隔离级别要解决幻读现象不建议将隔离级别升级到「串行化」。不同的数据库厂商对 SQL 标准中规定的 4 种隔离级别的支持不一样有的数据库只实现了其中几种隔离级别我们讨论的 MySQL 虽然支持 4 种隔离级别但是与SQL 标准中规定的各级隔离级别允许发生的现象却有些出入。MySQL 在「可重复读」隔离级别下可以很大程度上避免幻读现象的发生注意是很大程度避免并不是彻底避免所以 MySQL 并不会使用「串行化」隔离级别来避免幻读现象的发生因为使用「串行化」隔离级别会影响性能。MySQL InnoDB 引擎的默认隔离级别虽然是「可重复读」但是它很大程度上避免幻读现象并不是完全解决了解决的方案有两种针对快照读普通 select 语句是通过 MVCC 方式解决了幻读因为可重复读隔离级别下事务执行过程中看到的数据一直跟这个事务启动时看到的数据是一致的即使中途有其他事务插入了一条数据是查询不出来这条数据的所以就很好了避免幻读问题。针对当前读select ... for update 等语句是通过 next-key lock记录锁间隙锁方式解决了幻读因为当执行 select ... for update 语句的时候会加上 next-key lock如果有其他事务在 next-key lock 锁范围内插入了一条记录那么这个插入语句就会被阻塞无法成功插入所以就很好了避免幻读问题。快照读是如何避免幻读的可重复读隔离级是由 MVCC多版本并发控制实现的实现的方式是启动事务后在执行第一个查询语句后会创建一个 Read View后续的查询语句利用这个 Read View通过这个 Read View 就可以在 undo log 版本链找到事务开始时的数据所以事务过程中每次查询的数据都是一样的即使中途有其他事务插入了新纪录是查询不出来这条数据的所以就很好了避免幻读问题。做个实验数据库表 t_stu 如下其中 id 为主键。然后在可重复读隔离级别下有两个事务的执行顺序如下从这个实验结果可以看到即使事务 B 中途插入了一条记录事务 A 前后两次查询的结果集都是一样的并没有出现所谓的幻读现象。当前读是如何避免幻读的MySQL 里除了普通查询是快照读其他都是当前读比如 update、insert、delete这些语句执行前都会查询最新版本的数据然后再做进一步的操作。这很好理解假设你要 update 一个记录另一个事务已经 delete 这条记录并且提交事务了这样不是会产生冲突吗所以 update 的时候肯定要知道最新的数据。另外select ... for update 这种查询语句是当前读每次执行的时候都是读取最新的数据。接下来我们假设select ... for update当前读是不会加锁的实际上是会加锁的在做一遍实验。这时候事务 B 插入的记录就会被事务 A 的第二条查询语句查询到因为是当前读这样就会出现前后两次查询的结果集合不一样这就出现了幻读。所以Innodb 引擎为了解决「可重复读」隔离级别使用「当前读」而造成的幻读问题就引出了间隙锁。额外提一句读提交隔离级别是没有间隙锁的只有记录锁假设表中有一个范围 id 为35间隙锁那么其他事务就无法插入 id 4 这条记录了这样就有效的防止幻读现象的发生。举个具体例子场景如下事务 A 执行了这条当前读语句后就在对表中的记录加上 id 范围为 (2, ∞] 的 next-key locknext-key lock 是间隙锁记录锁的组合。然后事务 B 在执行插入语句的时候判断到插入的位置被事务 A 加了 next-key lock于是事物 B 会生成一个插入意向锁同时进入等待状态直到事务 A 提交了事务。这就避免了由于事务 B 插入新记录而导致事务 A 发生幻读的现象。幻读被彻底解决了吗可重复读隔离级别下虽然很大程度上避免了幻读但是还是没有能完全解决幻读。我举例两个可重复读隔离级别发生幻读现象的场景。第一个发生幻读现象的场景还是以这张表作为例子事务 A 执行查询 id 5 的记录此时表中是没有该记录的所以查询不出来。# 事务 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 插入一条 id 5 的记录并且提交了事务。# 事务 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 更新 id 5 这条记录对没错事务 A 看不到 id 5 这条记录但是他去更新了这条记录这场景确实很违和然后再次查询 id 5 的记录事务 A 就能看到事务 B 插入的纪录了幻读就是发生在这种违和的场景。# 事务 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)整个发生幻读的时序图如下在可重复读隔离级别下事务 A 第一次执行普通的 select 语句时生成了一个 ReadView之后事务 B 向表中新插入了一条 id 5 的记录并提交。接着事务 A 对 id 5 这条记录进行了更新操作在这个时刻这条新记录的 trx_id 隐藏列的值就变成了事务 A 的事务 id之后事务 A 再使用普通 select 语句去查询这条记录时就可以看到这条记录了于是就发生了幻读。因为这种特殊现象的存在所以我们认为MySQL Innodb 中的 MVCC 并不能完全避免幻读现象。第二个发生幻读现象的场景除了上面这一种场景会发生幻读现象之外还有下面这个场景也会发生幻读现象。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从而避免其他事务插入一条新记录。小结MySQL InnoDB 引擎的可重复读隔离级别默认隔离级根据不同的查询方式分别提出了避免幻读的方案针对快照读普通 select 语句是通过 MVCC 方式解决了幻读。针对当前读select ... for update 等语句是通过 next-key lock记录锁间隙锁方式解决了幻读。我举例了两个发生幻读场景的例子。第一个例子对于快照读 MVCC 并不能完全避免幻读现象。因为当事务 A 更新了一条事务 B 插入的记录那么事务 A 前后两次查询的记录条目就不一样了所以就发生幻读。第二个例子对于当前读如果事务开启后并没有执行当前读而是先快照读然后这期间如果其他事务插入了一条记录那么事务后续使用当前读进行查询的时候就会发现两次查询的记录条目就不一样了所以就发生幻读。所以MySQL 可重复读隔离级别并没有彻底解决幻读只是很大程度上避免了幻读现象的发生。要避免这类特殊场景下发生幻读的现象的话就是尽量在开启事务之后马上执行 select ... for update 这类当前读的语句因为它会对记录加 next-key lock从而避免其他事务插入一条新记录。