MySQL如何解决幻读

Jayle11

已于 2023-12-04 20:46:12 修改

阅读量2.8k

点赞数 18

分类专栏：数据库文章标签： mysql 数据库

于 2023-12-02 12:11:03 首次发布

本文链接：https://blog.csdn.net/qq_44644452/article/details/134744829

版权

数据库专栏收录该内容

5 篇文章 0 订阅

订阅专栏

一、幻读的定义

幻读是什么

同一事务中，对同一条件进行多次查询，由于在多次查询的过程中
其他事务对数据进行插入或则删除操作，导致获取的数据不一致。就像出现了
幻觉一样。

举一个例子：

事务T1的查询： SELECT * FROM products WHERE price > 100; 返回一组商品记录，这时事务T1还未提交。

事务T2的插入： 在T1查询之后，另一个事务T2插入了一些新的商品，它们的价格也大于100。

事务T1的再次查询： T1再次执行 SELECT * FROM products WHERE price > 100;，此时它返回的结果集比第一次查询更大，因为T2插入的新商品也满足条件。

幻读的产生原因

原因：
1.事务的隔离级别太低了导致
2.对表进行了行的插入和行的删除操作

二、解决幻读

方案一提高隔离级别

将事务隔离级别提升到SERIALIZABLE，但是这种方案正如名字一样
串行化，就是让多个事务穿行的执行。这种方式虽然可以解决幻读问
题，但是效率太低了，一般不推荐使用。

方案二 MVCC和next-key lock

MVCC的定义：

MVCC 是一种并发控制机制，用于在多个并发事务同时读写数据库时
保持数据的一致性和隔离性。它是通过在每个数据行上维护多个版本
的数据来实现的。当一个事务要对数据库中的数据进行修改时，
MVCC 会为该事务创建一个数据快照，而不是直接修改实际的数据
行。

通俗一点说，对数据进行修改时，数据库并不会删除以前的数据，而是将数据保存起来，以实现对旧数访问。数据的保存，其实是基于undo log并不是保存真正的数据。至于为什么这么做，第一，通过对数据的undo操作，我们也能拿到数据。第二，这种方式可以为我们节约大量的内存，也不必为了维护以前的数据额外花费其他资源，因为数据库原本就保存了undo log，只需要通过一个字段保存该行undo log的地址。

下图就是MVCC中，数据库维护行MVCC中行数据的多版本维护。每一条数据都有一个回滚指针(DB_ROLL_PTR)用来记录回归日志的地址。
**MVCC中行数据的版本维护**
InnoDB实现MVCC： 离不开read view， undo log，隐藏字段这三个属性

名称	作用	结构
read view	开启事务时生成，用来记录当前事务的事务id，判断事务是否对当前事务可见	m_low_limit_id：大于等于这个 ID 的事务均不可见 m_up_limit_id: 小于这个 ID 的事务均可见 m_creator_trx_id:创建该 Read View 的事务ID m_low_limit_no：事务 Number, 小于该 Number 的 Undo Logs 均可以被 Purge m_ids：创建 Read View 时的活跃事务列表，不包括当前事务如下图1-3
undo log	当事务不可见时，通过readview和隐藏字段找到可见事务的事务id，并通过undo log生成可见事务的行数据
隐藏字段	InnoDB存储引擎为每行添加的，用来维护行的多个版本数据	DB_TRX_ID（6字节）：表示最后一次插入或更新该行的事务 id。 DB_ROLL_PTR（7字节）回滚指针，指向该行的 undo log 。如果该行未被更新，则为空 DB_ROW_ID（6字节) 如果没有设置主键且该表没有唯一非空索引时，InnoDB 会使用该 id 来生成聚簇索引

图1-3

数据可见性算法：
在讲算法之前，我先来梳理一下readview各个字段是如何创建的.
数据库系统首先会找到当前处于活跃事务，并将他们的id记录在m_ids中，然后根据m_ids中的id来生成m_low_limit_id（大于m_ids中最大值+1）和m_up_limit_id（小于m_ids中最小值）。至于m_creator_trx_id字段，在开启事务时，系统就为该事务生成了一个事务id。

如果记录 DB_TRX_ID < m_up_limit_id，那么表明最新修改该行的事务（DB_TRX_ID）在当前事务创建快照之前就提交了，所以该记录行的值对当前事务是可见的。
如果 DB_TRX_ID >= m_low_limit_id，那么表明最新修改该行的事务（DB_TRX_ID）在当前事务创建快照之后才修改该行，所以该记录行的值对当前事务不可见。跳到步骤 5
m_ids 为空，则表明在当前事务创建快照之前，修改该行的事务就已经提交了，所以该记录行的值对当前事务是可见的
如果 m_up_limit_id <= DB_TRX_ID < m_low_limit_id，表明最新修改该行的事务（DB_TRX_ID）在当前事务创建快照的时候可能处于“活动状态”或者“已提交状态”；所以就要对活跃事务列表 m_ids 进行查找（源码中是用的二分查找，因为是有序的）
如果在活跃事务列表 m_ids 中能找到 DB_TRX_ID，表明：① 在当前事务创建快照前，该记录行的值被事务 ID 为 DB_TRX_ID 的事务修改了，但没有提交；或者 ② 在当前事务创建快照后，该记录行的值被事务 ID 为 DB_TRX_ID 的事务修改了。这些情况下，这个记录行的值对当前事务都是不可见的。跳到步骤 5
在活跃事务列表中找不到，则表明“id 为 trx_id 的事务”在修改“该记录行的值”后，在“当前事务”创建快照前就已经提交了，所以记录行对当前事务可见
在该记录行的 DB_ROLL_PTR 指针所指向的 undo log 取出快照记录，用快照记录的 DB_TRX_ID 跳到步骤 1 重新开始判断，直到找到满足的快照版本或返回空

举个例子：

如上图，
1.假设当前处于T4时刻，103事务会生成一个read view，当前的事务id为事务103，此时该事务的m_ids为[101，102]，因此m_low_limit_id = 104 ，则 m_up_limit_id = 101，m_creator_trx_id =103
2.根据上面的表格，T4时刻时，103事务查询id=1的所有行（一般幻读是根据相同的条件查出了不一样的结果）。但是此时数据最新的修改者101，因此DB_TRX_ID 为 101。因为m_up_limit_id <= 101 < m_low_limit_id，所以要在 m_ids 列表中查找，发现 DB_TRX_ID 存在列表中，那么这个记录不可见。