mysql什么场景下要防止幻读,MySQL 什么是幻读？如何解决？

何为幻读？

先看看MySQL官方的介绍：

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.

后面还有一部分内容，推荐阅读。

幻读，即 同一个事务 中 不同时间的两次相同查询 返回 不同行数的结果。

本文还有一个前提，事务的隔离级别为 可重复读 (RR)。

我们来仔细分析一下这个定义，首先是“同一个事务”，这个是前提，没有疑问。之后是“不同时间的两次相同查询”，“不同时间”也是前提，但是由于没有更多的限制，也就是说两次查询之间任何的增删操作都是允许的，但是有个问题，“两次相同查询”该如何理解？

假设有如下场景：

Time

Session A

Session B

T0

mysql> begin;

Query OK, 0 rows affected (0.00 sec)

T1

mysql> select * from t;

Empty set (0.00 sec)

T2

mysql> insert into t values(1);

Query OK, 1 row affected (0.01 sec)

T3

mysql> select * from t;

Empty set (0.00 sec)

T4

mysql> select * from t for update;

mysql> select * from t for update;

+------+

| id |

+------+

| 1 |

+------+

1 row in set (0.00 sec)

从表格中可以看出，Session A 在 T3 时刻的查询结果和 T1 时刻的一致，这是因为有 InnoDB MVCC的支持，而在 T4 时刻通过“当前读”查询到 Session B 插入的数据。

如果我们将 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

Session B

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 A

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

Session B

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

Session B

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 出现“幻觉”，看到了这条本不应该看到的记录。

如何解决幻读问题？

解决方式有两种：

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，以满足前开后闭区间。