解决MySQL中的幻读问题

bcd109

已于 2024-07-22 15:02:08 修改

阅读量799

点赞数 11

文章标签： mysql 数据库

于 2024-07-22 14:59:56 首次发布

本文链接：https://blog.csdn.net/qq_70186387/article/details/140609824

版权

MySQL 使用InnoDB 存储引擎的默认支持的隔离级别是 REPEATABLE-READ（可重复读），通过MVCC和Next-key锁机制来防止幻读。

解决幻读的方法

1. 快照读

如果是普通的 select 语句：使用MVCC（快照读）。MVCC依赖使用Read View和聚簇索引记录中的两个隐藏列工作。

Read View 四个重要的字段：

creator_trx_id ：指的是创建该 Read View 的事务的事务 id。
m_ids ：指的是在创建 Read View 时，当前数据库中「活跃事务」的事务 id 列表，注意是一个列表，“活跃事务”指的就是，启动了但还没提交的事务。
min_trx_id ：指的是在创建 Read View 时，当前数据库中「活跃事务」中事务 id 最小的事务，也就是 m_ids 的最小值。
max_trx_id ：这个并不是 m_ids 的最大值，而是创建 Read View 时当前数据库中应该给下一个事务的 id 值，也就是全局事务中最大的事务 id 值 + 1；

两个隐藏列：对于使用 InnoDB 存储引擎的数据库表，它的聚簇索引记录中都包含下面两个隐藏列：
行记录中的隐藏列

trx_id：当一个事务对某条聚簇索引记录进行改动时，就会把该事务的事务 id 记录在 trx_id 隐藏列里；
roll_pointer：每次对某条聚簇索引记录进行改动时，都会把旧版本的记录写入到 undo 日志中，这个隐藏列是个指针，指向每一个旧版本记录，于是就可以通过它找到修改前的记录。

在事务启动创建 Read View 后，我们可以将记录中的 trx_id 划分这三种情况：

一个事务去访问记录的时候，除了自己的更新记录总是可见之外，还有这几种情况：

如果记录的 trx_id 值小于 Read View 中的 min_trx_id 值，表示这个版本的记录是在创建 Read View 前已经提交的事务生成的，所以该版本的记录对当前事务可见。
如果记录的 trx_id 值大于等于 Read View 中的 max_trx_id 值，表示这个版本的记录是在创建 Read View 后才启动的事务生成的，所以该版本的记录对当前事务不可见。
如果记录的 trx_id 值在 Read View 的 min_trx_id 和max_trx_id之间（min_trx_id <= trx_id < max_trx_id），需要判断 trx_id 是否在 m_ids 列表中：
- 如果记录的 trx_id 在 m_ids 列表中，表示生成该版本记录的活跃事务依然活跃着（还没提交事务），所以该版本的记录对当前事务不可见。
- 如果记录的 trx_id 不在 m_ids列表中，表示生成该版本记录的活跃事务已经被提交，所以该版本的记录对当前事务可见。

2. 当前读

如果执行的是下列语句，就是当前读（锁定读）

select ... lock in share mode、select ... for update
insert、update、delete 操作

在当前读下，读取的是数据的最新版本，当前读会对读取到的记录加Next-key Lock 锁，来防止其它事务在间隙间插入数据：

select ... lock in share mode：对记录加 S 锁，其它事务也可以加S锁，如果加 x 锁则会被阻塞。
select ... for update、insert、update、delete：对记录加 X 锁，且其它事务不能加任何锁。

尽管有以上两种方法，仍然不能完全解决幻读问题，两个发生幻读的场景：

对于快照读， MVCC 并不能完全避免幻读现象。因为当事务 A 更新了一条事务 B 插入的记录，那么事务 A 前后两次查询的记录条目就不一样了，所以就发生幻读。
对于当前读，如果事务开启后，并没有执行当前读，而是先快照读，然后这期间如果其他事务插入了一条记录，那么事务后续使用当前读进行查询的时候，就会发现两次查询的记录条目就不一样了，所以就发生幻读。
- T1 时刻：事务 A 先执行「快照读语句」：select * from t_test where id > 100 得到了 3 条记录。
- T2 时刻：事务 B 往插入一个 id= 200 的记录并提交；
- T3 时刻：事务 A 再执行「当前读语句」 select * from t_test where id > 100 for update 就会得到 4 条记录，此时也发生了幻读现象。

所以，MySQL 可重复读隔离级别并没有彻底解决幻读，只是很大程度上而不能完全避免幻读现象的发生。要避免这类特殊场景下发生幻读的现象的话，就是尽量在开启事务之后，马上执行 select … for update 这类当前读的语句，因为它会对记录加 next-key lock，从而避免其他事务插入一条新记录。