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

原创

贺春旸的技术博客

发布于 2023-09-27 09:27:52

3200

发布于 2023-09-27 09:27:52

文章被收录于专栏：DBA 平台和工具DBA 平台和工具

前言

MySQL默认的隔离级别是REPEATABLE-READ（可重复读）。虽然它可以提供一定程度上的数据一致性和隔离性，但并不能完全解决幻读问题。

幻读是指在一个事务内，由于其他事务的插入操作，导致当前事务中的查询结果发生了变化。在REPEATABLE-READ隔离级别下，只能保证在同一事务中相同的查询语句返回相同的结果，但无法防止其他事务插入新的数据，从而导致当前事务的查询结果发生变化。

为了解决幻读问题，可以将隔离级别设置为SERIALIZABLE（串行化）级别。在该级别下，MySQL会确保每个事务执行的时间顺序与提交的顺序一致，从而避免了幻读的问题。但是，这也会增加并发性能开销，因为它要求事务之间必须按顺序依次执行。

除了调整隔离级别，还可以使用锁机制来解决幻读问题。例如，通过使用行级锁或表级锁，可以在操作期间对数据进行锁定，从而避免其他事务对数据的修改。不过，需要注意在使用锁机制时要小心处理死锁等并发问题。

幻读演示

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)` 注：插入一条数据5	`MySQL [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)`

总结

在REPEATABLE-READ隔离级别下，通过使用多版本并发控制（MVCC）机制来解决快照读的幻读问题。该机制会为每个事务创建一个一致性视图，确保在事务执行过程中，读取的数据都是一致的快照，并且不受其他事务的影响。

然而，当前读（例如使用SELECT ... FOR UPDATE语句进行的读取操作）在REPEATABLE-READ隔离级别下仍然可能遇到幻读。因为当前读会获取行级锁来保证数据的一致性，但如果其他事务在当前读操作之后（但在当前事务提交之前）插入了新的数据行，那么当前事务再次执行相同的查询时，就会发现新增了一些未被读取到的新数据行，就像出现了幻觉一样，就像刚才的操作，在会话二未提交的事务里，会莫名其妙地看到第5条数据，这就是幻读问题。

原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。

如有侵权，请联系 cloudcommunity@tencent.com 删除。

mysql

事务

原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。

如有侵权，请联系 cloudcommunity@tencent.com 删除。

mysql

事务

登录后参与评论

0 条评论

热度

会话一	会话二
`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)` 注：插入一条数据5	`MySQL [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)`