mysql 处理幻读

mysql 处理幻读

结论

MySQL InnoDB 引擎的可重复读隔离级别（默认隔离级），根据不同的查询方式，分别提出了避免幻读的方案：

• 针对快照读（普通 select 语句），是通过 MVCC 方式解决了幻读。
• 针对当前读（select … for update 等语句），是通过 next-key lock（记录锁+间隙锁）方式解决了幻读。

但是针对快照读和当前读，在极端情况也是会出现幻读问题

快照读

• 事务A 查询 select * from t_identity where id=3， id=3的数据不存在
• 此时事务B插入一条id=3的数据，然后提交
• 事务A 进行对 id=3的数据进行更新
• 事务A再次查询 select * from t_identity where id=3 ，结果可以查询出id=3的数据【即出现了幻读】

事务A

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

mysql> select * from t_identity;
+----+-------+-----+
| id | name  | age |
+----+-------+-----+
|  1 | jerry |  20 |
|  2 | tom   |  30 |
+----+-------+-----+
2 rows in set (0.01 sec)

mysql> select * from t_identity where id=3;
Empty set

mysql> select * from t_identity where id=3;
Empty set

mysql> update t_identity set age=1 where id=3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from t_identity where id=3;
+----+------+-----+
| id | name | age |
+----+------+-----+
|  3 | tom2 |   1 |
+----+------+-----+
1 row in set (0.01 sec)

事务B:

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

mysql> INSERT INTO `t_identity` (`id`, `name`, `age`) VALUES (3, 'tom2', 30);
Query OK, 1 row affected (0.00 sec)

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

当前读

• 事务A 先快照读 select * from t_identity where age > 2;
• 此时 事务B 插入一条 age=4的数据并提交
• 事务A 进行一次当前读 select * from t_identity where age > 2 for update; 发现读出了刚刚插入的 age=4的数据 ，【即出现了幻读】

事务A:

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

mysql> select * from t_identity where age > 2;
+----+-------+-----+
| id | name  | age |
+----+-------+-----+
|  1 | jerry |  20 |
|  2 | tom   |  10 |
|  3 | tom2  |   5 |
+----+-------+-----+
3 rows in set (0.02 sec)

mysql> select * from t_identity where age > 2 for update;
+----+---------+-----+
| id | name    | age |
+----+---------+-----+
|  1 | jerry   |  20 |
|  2 | tom     |  10 |
|  3 | tom2    |   5 |
| 11 | tomdddd |   4 |
+----+---------+-----+
4 rows in set (34.33 sec)

事务B:

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

mysql> INSERT INTO `t_identity` (`name`, `age`) VALUES ('tomdddd', 4);
Query OK, 1 row affected (0.00 sec)

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

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