MySQL默认隔离级别REPEATABLE-READ并没有解决幻读问题
刷脉脉，发现一个帖子讨论幻读问题：https://maimai.cn/web/gossip_detail?src=app&webid=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJlZ2lkIjoiN2JmMjA4ZDZjNzU0MTFlYWExOTk4MDE4NDRlNTAxOTAiLCJ1IjoyMjM0MjgzMTksImlkIjoyNjc0NDU3OH0.yvtEe5Z1vjtmPE5dBwXVqD-pMWBE2--jDQuRARcWArI固做一下实验，演示下MySQL默认隔离级别REPEATABLE-READ并没有解决幻读问题
幻读演示 MySQL默认隔离级别REPEATABLE-READ（可重复读）
会话一
会话二MySQL > select from t1;+------+| id |+------+| 1 || 2 || 3 || 4 |+------+4 rows in set (0.000 sec)MySQL > select from t1;+------+| id |+------+| 1 || 2 || 3 || 4 |+------+4 rows in set (0.000 sec)MySQL > begin;Query OK, 0 rows affected (0.000 sec) 注：开启事务一MySQL > begin;Query OK, 0 rows affected (0.000 sec) 注：开启事务二MySQL > insert into t1 values(5);Query OK, 1 row affected (0.000 sec) MySQL > select * from t1;+------+| id |+------+| 1 || 2 || 3 || 4 || 5 |+------+5 rows in set (0.000 sec) 注：插入一条数据5MySQL > select from t1;+------+| id |+------+| 1 || 2 || 3 || 4 |+------+4 rows in set (0.000 sec) 注：因会话一未提交，所以在会话二事务里是看不见更改后的结果的MySQL > commit;Query OK, 0 rows affected (0.002 sec) 注：会话一执行事务提交
MySQL > select from t1;+------+| id |+------+| 1 || 2 || 3 || 4 |+------+4 rows in set (0.000 sec) MySQL > update t1 set id = id+10;Query OK, 5 rows affected (0.001 sec)Rows matched: 5 Changed: 5 Warnings: 0 注：执行全表更新，id+10
MySQL > select * from t1;+------+| id |+------+| 11 || 12 || 13 || 14 || 15 |+------+5 rows in set (0.000 sec) 注：当再次查看时，此时发现有5条数据被更改，产生幻读
MySQL > select version();+-----------+| version() |+-----------+| 8.0.21 |+-----------+1 row in set (0.000 sec)
在MySQL默认隔离级Repeatable Read下，刚才的操作，在会话二未提交的事务里，
会莫名其妙地看到第5条数据，这种现象称为幻读。
固只能通过select ... for update才能避免幻读。
页:
[1]