MySQL 幻读和不可重复读的区别

背景

最近在学习 MySQL 的事务，幻读和不可重复读很容易搞混。故做此记录总结。先给出两者的定义。

• 不可重复读
  如果一个事务修改了另一个未提交事务读取的数据，就意味着发生了不可重复读现象。
  r1[x]…w2[x]…((c1 or a1) and (c2 or a2) in any order)
  解释下：r1[x] 表示事务 T1 读字段 x 的值，然后 w2[x] 表示事务 T2 修改了 x 的值
• 幻读
  如果一个事务先根据某些搜索条件查询出一些记录，在该事物未提交的时候，另一个事务写入了一些符合那些搜索条件的记录（INSERT、DELETE、UPDATE 操作），就意味着发生了幻读操作。

MySQL 的 REPEATABLE READ 可重复读，下文简称为 RR 通过 MVCC 版本链解决了幻读问题，还可以避免可重复读问题。下面看实例。

客户端 1: 开启事务，从表中读数据

mysql> begin;

mysql> select * from user;
+----+----------+--------+-------+
| id | userName | gender | email |
+----+----------+--------+-------+
|  1 | aaron    | M      | sunne |
|  2 | abin     | W      | NULL  |
|  3 | cool     | M      | NULL  |
|  4 | fourth   | W      | ????? |
|  5 | fifth    | W      | abcde |
|  6 | six      | W      | NULL  |
+----+----------+--------+-------+
6 rows in set (0.01 sec)

客户端 2：插入数据，隐式提交事务

mysql> insert into user values(null, 'seven', 'W', 'alll');

客户端 1：再次查询

mysql> select * from user;
+----+----------+--------+-------+
| id | userName | gender | email |
+----+----------+--------+-------+
|  1 | aaron    | M      | sunne |
|  2 | abin     | W      | NULL  |
|  3 | cool     | M      | NULL  |
|  4 | fourth   | W      | ????? |
|  5 | fifth    | W      | abcde |
|  6 | six      | W      | NULL  |
+----+----------+--------+-------+
6 rows in set (0.01 sec)

可以看到 RR 隔离级别避免了幻读。至于可重复读，我们可以修改比如 id = 5 的数据来复现，就不做赘述了。

修改隔离级别

我们修改隔离级别为 READ COMMITTED：已提交读，下位简称为 RC，来复现可重复读出现的问题。
首先修改客户端 1 和客户端 2 的隔离级别：

mysql> set session transaction isolation level read committed;
mysql> select  @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| READ-COMMITTED          |
+-------------------------+
1 row in set (0.00 sec)

客户端 1 开启事务读表：

mysql> begin;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from user;
+----+----------+--------+-------+
| id | userName | gender | email |
+----+----------+--------+-------+
|  1 | aaron    | M      | sunne |
|  2 | abin     | W      | NULL  |
|  3 | cool     | M      | NULL  |
|  4 | fourth   | W      | ????? |
|  5 | fifth    | W      | abcde |
|  6 | six      | W      | NULL  |
|  7 | seven    | W      | alll  |
|  8 | seven    | M      | hah   |
+----+----------+--------+-------+
8 rows in set (0.00 sec)

客户端 2 修改表数据隐式提交：

mysql> update user set userName = '7' where id = 7;

客户端 1 再次读取数据：

mysql> select * from user;
+----+----------+--------+-------+
| id | userName | gender | email |
+----+----------+--------+-------+
|  1 | aaron    | M      | sunne |
|  2 | abin     | W      | NULL  |
|  3 | cool     | M      | NULL  |
|  4 | fourth   | W      | ????? |
|  5 | fifth    | W      | abcde |
|  6 | six      | W      | NULL  |
|  7 | 7        | W      | alll  |
|  8 | seven    | M      | hah   |
+----+----------+--------+-------+
8 rows in set (0.00 sec)

此时客户端 1 读到 id = 7 的 userName = 7，和第一次读取时不一样，出现了不可重复读。