14-01 | MySQL 幻读如何解决？

如何解决幻读问题？

首先明确select … 和 select … for update 视为不相同的查询，前者是“快照读”，而后者是“当前读”。

假设有如下结构的表和数据：
CREATE TABLE t (
id int(11) NOT NULL,
c int(11) DEFAULT NULL,
d int(11) DEFAULT NULL,
PRIMARY KEY (id),
KEY c (c)
) ENGINE=InnoDB;

insert into t values(0,0,0),(5,5,5);

并且执行流程如下：

Time Session A
T0 mysql> begin;
Query OK, 0 rows affected (0.00 sec)
T1 mysql> select * from t where d=5;

mysql> select * from t where d=5;
±—±-----±-----+
| id | c | d |
±—±-----±-----+
| 5 | 5 | 5 |
±—±-----±-----+
1 rows in set (0.00 sec)

Time Session B
T2 mysql> insert into t values(10, 10, 5);
Query OK, 1 row affected (0.01 sec)
T3 mysql> update t set d=5 where id=0;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
T4 mysql> select * from t;

mysql> select * from t;
±—±-----±-----+
| id | c | d |
±—±-----±-----+
| 0 | 0 | 5 |
| 5 | 5 | 5 |
| 10 | 10 | 5 |
±—±-----±-----+
3 rows in set (0.01 sec)

Time Session A
T5 mysql> select * from t where d=5;

mysql> select * from t where d=5;
±—±-----±-----+
| id | c | d |
±—±-----±-----+
| 5 | 5 | 5 |
±—±-----±-----+
1 rows in set (0.00 sec)

Time Session A
T6 mysql> update t set c=15 where d=5;
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3 Changed: 3 Warnings: 0
T7 mysql> select * from t where d=5;

mysql> select * from t where d=5;
±—±-----±-----+
| id | c | d |
±—±-----±-----+
| 0 | 15 | 5 |
| 5 | 15 | 5 |
| 10 | 15 | 5 |
±—±-----±-----+
3 rows in set (0.01 sec)

可以看到 Session B 在 T2 和 T3 时刻分别导入和更新了一笔记录，由于 MVCC 机制，结果对 Session A 不可见（T5），但是当 Session A 在 T6 时刻更新 d=5 的记录时，却同时更新了 3 笔记录，这是因为所有的 update 语句都是“当前读”，使得 Session A 在更新时可以看到 Session B 提交的记录。

接下来奇怪的事情发生了，Session A 再次执行相同的查询时，却返回了三条记录。其实也不奇怪，还是由于 MVCC 机制，在事务中是能够看到自己更新过的记录的。

幻读出现了！！！

这里补充一点，一般都说幻读都是由于新增记录导致的，但是从上面的流程可以看到，Session B 在 T3 时刻更新数据也同样导致 Session A 出现“幻觉”，看到了这条本不应该看到的记录。

作者：_不想翻身的咸鱼
链接：https://www.jianshu.com/p/3f82c3281ab8
来源：简书
著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。

解决方式有两种：

1.一种就是提高数据库的隔离级别为“串行化”，那么当 Session A 开启事务后，Session B 执行的任何写操作都会被阻塞，直到 Session A 完成事务的提交，如此，Session A 在执行期间只有自己会更新数据（当前读），而且更新的数据又对自己可见（相当于当前读），也就没有了幻读。

方法简单，但是将所有的写事务都串行化，对性能的影响是巨大的，不推荐。

2.另一种是使用 select … for update 代替 select …，首先前者是“当前读”，会读取到记录的最新状态，然后还会对记录以及记录间的间隙进行加锁，也就是行锁和间隙锁，合起来又称为 next-key 锁，行锁仅仅会锁索引，next-key 锁则还包括索引之前的间隙。

MySQL 是一边遍历索引一边添加锁的，对于上面 t 表中的 d 字段，该字段没有建立索引，所以走的全表的查询，也就是全表的所有记录和其之前的间隙都被加锁了，此时任何的其它事务都无法进行写操作，也就不会出现上面幻读的情况。

但是如果查询的是拥有索引的 c 字段，则只会对 (0,5] 和 (5, +supremum] 区间进行加锁，InnoDB 给每个索引加了一个不存在的最大值 supremum，以满足前开后闭区间。