mysql 并没有幻读_MySQL默认隔离级别REPEATABLE-READ并没有解决幻读问题

刷脉脉，发现一个帖子讨论幻读问题：

https://maimai.cn/web/gossip_detail?src=app&webid=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJlZ2lkIjoiN2JmMjA4ZDZjNzU0MTFlYWExOTk4MDE4NDRlNTAxOTAiLCJ1IjoyMjM0MjgzMTksImlkIjoyNjc0NDU3OH0.yvtEe5Z1vjtmPE5dBwXVqD-pMWBE2--jDQuRARcWArI

固做一下实验，演示下MySQL默认隔离级别REPEATABLE-READ并没有解决幻读问题

幻读演示

MySQL默认隔离级别REPEATABLE-READ(可重复读)会话一会话二

MySQL [test]> select * from t1;

+------+

| id |

+------+

| 1 |

| 2 |

| 3 |

| 4 |

+------+

4 rows in set (0.000 sec)MySQL [test]> select * from t1;

+------+

| id |

+------+

| 1 |

| 2 |

| 3 |

| 4 |

+------+

4 rows in set (0.000 sec)

MySQL [test]> begin;

Query OK, 0 rows affected (0.000 sec)

注：开启事务一MySQL [test]> begin;

Query OK, 0 rows affected (0.000 sec)

注：开启事务二

MySQL [test]> insert into t1 values(5);

Query OK, 1 row affected (0.000 sec)

MySQL [test]> select * from t1;

+------+

| id |

+------+

| 1 |

| 2 |

| 3 |

| 4 |

| 5 |

+------+

5 rows in set (0.000 sec)

注：插入一条数据5MySQL [test]> select * from t1;

+------+

| id |

+------+

| 1 |

| 2 |

| 3 |

| 4 |

+------+

4 rows in set (0.000 sec)

注：因会话一未提交，所以在会话二事务里是看不见更改后的结果的

MySQL [test]> commit;

Query OK, 0 rows affected (0.002 sec)

注：会话一执行事务提交

MySQL [test]> select * from t1;

+------+

| id |

+------+

| 1 |

| 2 |

| 3 |

| 4 |

+------+

4 rows in set (0.000 sec)

MySQL [test]> update t1 set id = id+10;

Query OK, 5 rows affected (0.001 sec)

Rows matched: 5 Changed: 5

Warnings: 0

注：执行全表更新，id+10

MySQL [test]> select * from t1;

+------+

| id |

+------+

| 11 |

| 12 |

| 13 |

| 14 |

| 15 |

+------+

5 rows in set (0.000 sec)

注：当再次查看时，此时发现有5条数据被更改，产生幻读

MySQL [test]> select version();

+-----------+

| version() |

+-----------+

| 8.0.21 |

+-----------+

1 row in set (0.000 sec)

在MySQL默认隔离级Repeatable Read下，刚才的操作，在会话二未提交的事务里，

会莫名其妙地看到第5条数据，这种现象称为幻读。

固只能通过select ... for update才能避免幻读。