MySQL在RR级别下到底有没有修复幻读


微信搜索“coder-home”或扫一扫下面的二维码,关注公众号,第一时间了解更多干货分享,还有各类视频教程资源。扫描它,带走我


背景

我最近一直被一个问题困扰着:MySQL在其默认的可重复读(Read-Committed)RR隔离级别下,到底有没有解决幻读的问题?

看了网上很多的贴子,感觉每一个帖子都有一个观点,越看越感觉混乱。有的说是在RR级别下,MySQL的幻读不会发生;有的说RR级别下,MySQL中幻读会发生。各执一词,并且每一个观点都不能给出一些有说服性的例子。所以,打算自己去分析一下到底是否解决了幻读的问题。

疑问点

我的疑惑只要有以下几点:

  1. 什么是快照读?
  2. 什么是当前读?
  3. 什么是幻读?
  4. 如果说MySQL解决了幻读,那为什么在当前读下面还会发生幻读的问题?
  5. 幻读的解决是不是需要在SQL中去使用某种方式才可以“启用”避免幻功能,会不会是默认不启动的?

分析

什么是快照读

说到快照读,就得先说一下快照,而说到快照,就得说说MVCC。

快照是属于MVCC中的一个概念。在RR级别下,MySQL通过MVCC的技术会给每一个事务在启动的时候,创建一个一致性的快照视图,这个快照中的所有数据内容就是这个事务在启动时刻的生成的,后续数据库中的数据再怎么变化,这个快照的数据内容都不受它们的影响。而这个快照一直会伴随着整个事务的生命周期。在这个事务运行过程中的,所有的普通查询都会从这个快照中去获取数据,事务中的这些普通的查询就属于快照读。

举例说明一下什么是快照读,下面事务中的几个查询语句都是属于快照读。

start transaction with consistent snapshot; -- begin/start transaction命令也可以
do something...
select * from t; -- 快照读
do something...
select * from t where id = 1; -- 快照读
do something...
select * from t where name = 'zhangsan'; -- 快照读
do something...
commit;
开启事务命令的区别

说明:上面的开启事务的方式,没有使用begin或start transaction命令,而是使用了start transaction with consistent snapshot命令启动的事务。其实,这两个命名都是可以开启事务的。但是它们之间有一点点区别。

start transaction with consistent snapshot命令会在事务开启之后马上就创建MVCC一致性视图。而使用begin或start transaction命令启动的事务,会在开启事务后,第一次操作InnoDB表的SQL语句后,才会创建MVCC一致性视图,这里的操作InnoDB的SQL语句可以是insert、update、delete、select中的任何一个,但是要求是操作的InnoDB存储引擎的表,不能是其他引擎的表。

通过下面的两个实验截图来说明这两个命令的区别。

begin开启事务如下:
在这里插入图片描述

上面的第6步中,你可能会感觉到很奇怪:为什么第6步可以看到右侧事务新增加的数据行呢?在RR级别下,左侧的事务启动后,在事务运行期间和结束后,应该看不到右侧事务新增加的行才对?这不就是发生不可重复读的问题了吗?这和我们平时所说的RR级别下,MySQL是支持可重复的结论相违背呀?

其实不违背我们平时说的RR下面MySQL支持可重复的结论。之所以出现上面的这个情况的根本原因是MVCC一致性视图在一个事务当中,创建的时间点是什么时候?是事务开启之后就创建了?还是在事务执行的过程中在某一个动作之后才创建。如果你再上面实验的步骤3之后,不直接去执行步骤4,而是在左侧事务中执行一个和步骤3一样的查询动作,我们暂时称为步骤3.1,如果你再左侧的事务中,3.1步骤,那么久不会发生上面左侧事务读取到右侧事务中插入数据的现象了。因为这个3.1的动作就会触发创建一致性视图的动作,而此时左侧事务创建的MVCC视图中的数据就不会包含右侧事务步骤4插入的数据行。

我们分析一下上面的这个过程:

  • 步骤6是发生在右侧事务开启并结束之后,是步骤1执行完成后,我们就没有在左侧事务中执行任何操作,而是直接在右侧事务中一口气的把右侧的事务执行结束。
  • 此时数据库的表已经有(5,55)这一行数据了。
  • 而在步骤6中,此时是左侧事务在执行完begin命令之后,第一次操作innodb类型的表的SQL语句。此时执行SQL语句的时候才去创建的MVCC一致性视图。
  • 在创建一致性视图的时候,是会获取数据库中最新的已经提交的数据作为MVCC视图中的数据。所以在步骤6这个SQL语句中,会查询出来右侧事务中已经提交的(5,55)这一行数据。

start transaction with consistent snapshot开启的事务如下:
在这里插入图片描述

什么当前读

在一个事务执行的过程中,如果我们这个时候使用了DML语句,也就是我们平时所说的insert、update、delete语句,此时DML会执行当前读,它们会在操作数据库内容之前,去读取数据库中当前时间点以及提交的最新的数据,基于最新的数据的基础上,再去做这个DML语句自己的SQL逻辑。此时的这个读取数据库中最新已提交的数据的这个动作,就是当前读。

我们拿一个事务当中的update语句来说,在修改数据的时候,需要先读到数据,才能基于读到的数据上再去做修改。而这个读取数据的时候,需要基于数据库中最新的已经提交的数据来做,如果此时仍然按照一致性快照读,那么会读取到当前事务开启的时候所能读取到的数据版本,而这个数据版本有可能已经不是最新的了,其他事务可能已经在这个数据版本的基础上进行的修改,如果不去数据库中读取其他事务更改后的数据,那么此时就会覆盖掉其他事务的修改操作。而这是数据库中锁不允许的。所以,在修改的时候,要执行当前读,然后再修改。

在一个事务当中,使用DML语句操作数据库就是属于当前读的范畴。例如使用如下的SQL语句就是属于当前读。

begin;
do something...
insert into t values(11,'zhangsan'); -- 会发生当前读
do something...
update t set name = 'zhang' where id = 11; -- 会发生当前读
do something...
delete from t where id = 11; -- 会发生当前读
do something...
commit;

除了我们平时使用的DML之外,如下两个SQL语句也属于当前读的范畴:

begin;
do something...
select * from t where id = 1 lock in share mode; -- 会发生当前读
do something...
select * from t where id = 2 for update; -- 会发生当前读
do something...
commit;

什么是幻读

幻读是基于插入的操作而言的。更新、删除操作不属于幻读的范畴,属于不可重复读的范畴。

当前事务在运行的过程中,一开始的时候没有读取到其他事务插入的行,但是后来读取到了其他事务插入数据,这才是幻读。读取到其他事务更新、删除的操作内容,不是幻读,而是不可重复读。

MySQL到底有没有解决幻读

我所的困惑点只要有以下几个:

  • MySQL在RR隔离级别下,幻读到底会不会发生?
  • 如果会发生,那么MySQL能避免这种情况的发生吗?
  • 如果可以避免,那它是使用什么方式来避免的呢?

带着以上这3个问题,我们来逐步做实验来验证一下。下面所有的实验都是在MySQL5.7版本的RR事务隔离级别下进行的。

在只有快照读前提下,不会发生幻读

也就是说,如果在事务执行过程中,全部都是使用的一致性快照读,他们读取的数据都是从快照视图中读取的数据,此时的数据就是在事务开始的时候创建好的,在事务执行的过程中,任何时候只要是从快照中去读取,那么数据永远都是一样的,不会发生变化。所以说,在快照读的情况下,不会发生幻读,如下所示:
在这里插入图片描述

注意:在一个事务当中,如果有一次或多次发生了当前读,就有可能会发生幻读。例如先开始的时候是执行的快照读,后来执行了一次因为更新语句而发生的当前读,然后再次执行快照读,就有可能发生幻读。

这里的有可能是有这几个前提:

  1. 一个事务中,快照读和当前读混合使用。
  2. 在执行当前读之前,另外一个事务插入了新的数据。
  3. 在当前这个事务中,执行当前读的时候,查询的范围结果中包含了另外一个事务的插入数据。
  4. 再次执行快照读,幻读就会发生。
begin;
select * from t; -- 快照读
-- 在此时间点,当前事务下面的update语句还没有执行的时候,如果有另外一个事务,向t表中插入的一条数据,然后当前事务再去执行更新全表数据的语句,更新完成后,再次执行快照对,就有可能会发生幻读。
update t set a = a + 1; -- 更新语句,会先执行当前读再去更改数据。
select * from t; -- 此时的快照读有可能发生幻读。
commit;

当前读的时候,可能会发生幻读

  1. 开启左侧事务A
  2. 开启右侧事务B
  3. 是左侧事务A中查询表t
  4. 在右侧事务B中查询表t
  5. 在右侧事务B中,向表t插入一行数据(5,55)
  6. 在右侧事务B中,查询表t,可以查询到自己刚插入的新数据。
  7. 在左侧事务A中,查询表t,看不到事务B中刚插入的数据。
  8. 提交右侧的事务B
  9. 在右侧事务B提交后的窗口中,再次查询表t,可以看到刚插入的数据行。
  10. 在左侧事务A中,查询表t,仍然查询不到右侧事务B所插入并提交的数据。
  11. 在左侧的事务A中,更新表中所有的数据,不使用任何条件。此时发现输入的影响结果的行数不是前面事务A所能查询到的4行数据,而是输出影响了5行数据,多了一行。此时在更新的时候,使用了当前度的功能,把刚才右侧事务B中插入并提交的一行数据(5,55)也给查询出来并其修改掉了。所以在提示结果中,显示影响行数为5行。此时已经发生了幻读,事务A读取到了事务B的插入的数据。
  12. 在右侧事务B结束的窗口中,继续查询表t,发现可以看到5行数据,但是数据内容不是左侧事务A修改的结果,此时正常,因为左侧的事务A还没有提交。
  13. 在左侧事务A中,再次查询表t的数据,发生此时的结果为5行数据。包含了右侧事务B插入且提交的数据行(5,55),并且它被上面的update语句给修改为了(5,5500)。此时发生了幻读。

实验截图如下:
在这里插入图片描述

注意:把上面截图中的第11步更新全表数据的操作,换成如下SQL语句,也会发生幻读。上面的试验操作是把右侧事务新插入的行通过update语句给修改了,下面的两个SQL分别是在左侧事务中尝试再次插入同一行数据,和删除右侧事务新插入的数据。

/*
在左侧事务中,再次尝试插入右侧事务已经插入且提交的数据行的试试,会提示主键冲突的错误,但是查询整个表的数据又发现没有这样的主键值的行,但是就是插入不成功。这也是幻读的一种体现。
*/
insert into t values(5,55);


/*
在左侧事务中,尝试删除右侧事务中插入且提交的数据,此时发现影响行数为1行,我们预期的应该是影响行数为0行,因为我们前面查询的时候,并没有id=5的这样的行存在,但是删除的时候却提示删除成功了。并且提交左侧事务后,在右侧已经结束的事务窗口中再次查询表t,发现之前可以看到的新插入的数据,确实不存在了,被左侧的事务给删除掉了。
*/
delete from t where id = 5;

当前读中发生的幻读是如何解决的

针对前面我们在当前读中锁发生的幻读的现象,MySQL在RR下面,到底能否解决这样的幻读问题呢?答案是肯定的,是通过间隙锁来实现的。

原理就是在我的事务将要操作的表上,除了增加行锁之外,增加间隙锁,让其在这些间隙中,不能插入数据,然后再我的事务后面即便是我执行了当前读也不会发生幻读的现象了。

实验如下:
在这里插入图片描述

注意:上面的第5步中是给表增加了表级别的S锁。这里的加锁方式取决于我们的SQL语句是什么样子的,如果是for update语句,那就是增加X锁,如果是lock in share mode就是S锁,这是指锁的类型。那么锁的粒度或者说是范围是什么样子的呢?

至于锁的粒度范围也是取决于我们的SQL语句和我们的表结构设计的。

  • 如果SQL语句中的where条件,使用到了索引,那么就是增加行锁和间隙锁或临键锁。
  • 如果SQL语句中的where条件,没有使用到了索引,使根据一个普通的列筛选条件,那么将会降级为表锁。把整个表都给锁上了。

这里比较复杂,有各种加锁的方式和规则。后续单独分享一下这里的加锁的逻辑。

总结

所以,到目前为止,我们的疑惑目前应该依据清楚了。

  • MySQL在RR隔离级别下,幻读到底会不会发生?会的。
  • 如果会发生,那么MySQL能避免这种情况的发生吗?能。
  • 如果可以避免,那它是使用什么方式来避免的呢?显示的使用间隙锁。

MySQL在RR隔离级别下,是有可能会发生幻读的。这里的有可能是有以下前提条件的。

  1. 在一个事务当中,快照读和当前读都使用到了。
  2. 与此同时,其他事务刚好有插入数据的操作发生且提交了。
  3. 当前读在当前事务中发生的时间点是在其他事务插入数据且提交之后。
  4. 然后在当前事务中再次执行快照读或当前读的时候,如果查询条件搜索的范围刚好可以包含其他事务插入的数据,则会发生幻读。

严格意义上,MySQL是在一定程度上修复了幻读。为了修复幻读的问题,它提供了间隙锁。在事务中,操作数据之前,我们给我们要操作的数据范围增加上了正确的间隙锁就可以避免幻读的发生。

MySQL提供了修复幻读机制:间隙锁。但是需要业务自己去加锁,如果不加锁,只是简单的SELECT查询,是无法限制并行事务的插入数据的。

而这个加锁的功能,不是MySQL自动就给增加上的,需要结合我们自己的实际业务场景,有选择性的去“开启”这个功能,当我们去开启了这个加锁的功能后,从而可以避免幻读的发生。只是锁数据的粒度、范围是MySQL自己控制的,它会根据我们的表结构、主键、唯一索引、普通索引、普通数据列、SQL语句的where条件等关系,来自动决定锁数据的粒度是使用行锁、还是间隙锁、还是临键锁、还是表锁。

开启这个功能的方式就是在我们的事务中,当前读之前,先去获取锁,然后再去做DML的当前读。

例如下面两个语句,第一个是不能避免大于10的id行在其他事务中别插入的动作,而第二个SQL是可以避免其他事务在执行完该语句后再次向表t中插入id大于10的记录。其实这就是启用的间隙锁的功能,避免了后续出现幻读的可能性。

select * from t where id > 10;
select * from t where id > 10 for update;


微信搜索“coder-home”或扫一扫下面的二维码,关注公众号,第一时间了解更多干货分享,还有各类视频教程资源。扫描它,带走我


<think>好的,我现在需要帮助用户了解MySQL的底层原理。用户提到之前给出的回答引用了三篇关于MySQL底层执行原理、MVCC和索引的文章,但用户现在想更全面地了解MySQL的底层原理。我需要结合之前的引用内容,同时补充其他关键部分,确保回答结构清晰,覆盖主要模块。 首先,我需要整理MySQL的架构层次。根据引用[1],用户可能已经知道binlog归档和存储引擎层的一部分。我应该从连接层、服务层、存储引擎层和日志系统这几个方面展开。连接层涉及线程池和连接管理,服务层包括SQL接口、解析器、优化器和执行器,存储引擎层需要对比InnoDB和MyISAM,日志系统包括redo log、undo log和binlog,这些在引用[1]和[3]中都有提及。 接下来是SQL执行流程。用户可能对查询如何执行感兴趣,需要详细说明解析、优化、执行的过程。比如解析器生成语法树,优化器选择执行计划,执行器调用存储引擎接口。这里可以引用[1]中的binlog归档步骤,说明执行后的日志记录。 然后是索引机制,引用[3]提到主键索引、普通索引和前缀索引,以及回表问题。我需要详细解释B+树的结构,为什么MySQL选择B+树,聚簇索引和非聚簇索引的区别,以及覆盖索引和索引下推技术。这部分需要结合用户提到的索引内容,确保准确。 事务与MVCC部分,引用[2]强调MVCC的实现方式,指出官方文档的正确性。需要解释ACID特性,隔离级别,以及InnoDB如何通过隐藏字段(DB_TRX_ID、DB_ROLL_PTR)和ReadView来实现MVCC,避免快照的说法。这里要纠正常见的误解,比如创建时间和删除时间字段的错误观点,并详细说明ReadView的工作流程。 日志系统部分,引用[1]提到了binlog,需要补充redo log和undo log的作用。解释两阶段提交,如何保证事务的持久性和崩溃恢复,以及binlog的归档使用场景。 锁机制也是一个重点,需要区分行锁、表锁、意向锁和间隙锁,说明InnoDB如何实现不同隔离级别下的锁策略,比如可重复使用间隙锁防止。 最后是存储引擎对比,用户可能想知道InnoDB和MyISAM的区别,适用场景,以及内存临时表的使用。 在回答过程中,需要保持结构清晰,分点说明,使用示例和公式(比如B+树的高度计算),代码块展示日志命令,表格对比存储引擎,以及流程图说明SQL执行步骤。同时,生成的相关问题要引导用户深入思考,比如索引优化、MVCC实现细节、日志系统协同等。 需要检查是否有遗漏的部分,比如缓冲池(Buffer Pool)、change buffer等,但用户可能更关注核心原理。确保引用正确的文献,避免错误,比如MVCC部分要依据官方文档而非《高性能MySQL》的过时观点。最后,语言要口语化,避免使用Markdown格式,保持自然流畅。</think>### MySQL底层原理详解 MySQL的底层实现涉及**存储引擎架构**、**事务处理机制**和**索引优化策略**等核心模块。以下从关键组件出发,结合源码级分析进行阐述: --- #### 一、MySQL架构层次 1. **连接层(Connector Layer)** - 通过线程池管理客户端连接,每个连接对应一个线程。 - 认证协议:基于`mysql_native_password`或`caching_sha2_password`实现身份验证[^1]。 2. **服务层(Server Layer)** - **SQL接口**:接收SQL语句,返回查询结果。 - **解析器(Parser)**:生成语法树(AST),例如: ```sql SELECT * FROM users WHERE id=1; -- 解析为: Query{ command: SELECT, columns: [*], table: users, where: Condition{id=1} } ``` - **优化器(Optimizer)**:基于代价模型选择执行计划(如是否使用索引)。 - 通过`EXPLAIN`可查看优化器决策过程[^3]。 3. **存储引擎层(Storage Engine)** - **InnoDB**:支持事务、行级锁、MVCC(多版本并发控制)。 - **MyISAM**:仅支持表级锁,无事务特性。 4. **日志系统** - **Redo Log**:保证事务持久性(WAL机制)。 - **Undo Log**:实现事务回滚和MVCC。 - **Binlog**:归档日志(主从复制基础)[^1]。 --- #### 二、SQL执行流程(以SELECT为例) ```mermaid graph TD A[客户端] --> B(连接器) B --> C{查询缓存} C -->|命中| D[直接返回结果] C -->|未命中| E[解析器] E --> F[预处理器] F --> G[优化器] G --> H[执行器] H --> I[存储引擎] I --> J[返回数据] ``` - **关键点**: - 查询缓存因高并发场景性能问题,在MySQL 8.0中被移除[^1]。 - 执行器通过调用存储引擎接口(如`handler::read_row()`)逐行获取数据。 --- #### 三、索引机制(InnoDB实现) 1. **B+树索引结构** - **聚簇索引**:叶子节点存储完整数据行(主键索引)。 - **二级索引**:叶子节点存储主键值,需回表查询[^3]。 - 示例: ```sql -- 创建前缀索引 CREATE INDEX idx_name ON users(name(10)); ``` - 前缀索引通过截取字段前N字节构建,减少索引体积[^3]。 2. **索引选择策略** - **覆盖索引**:当查询字段全部包含在索引中时,避免回表。 ```sql SELECT id FROM users WHERE name='Alice'; -- 使用name索引直接返回id ``` - **索引下推(ICP)**:在存储引擎层过滤数据,减少服务层负载。 3. **B+树高度计算** - 假设每页16KB,主键类型为BIGINT(8B),指针6B: $$ \text{单页记录数} = \frac{16384}{8+6} \approx 1170 $$ - 3层B+树可存储约$1170^3=1.6$亿条数据。 --- #### 四、事务与MVCC 1. **ACID实现** - **原子性(A)**:通过Undo Log回滚日志实现。 - **隔离性(I)**:基于锁和MVCC机制。 2. **MVCC核心实现** - **隐藏字段**: - `DB_TRX_ID`:最后修改事务ID(6B)。 - `DB_ROLL_PTR`:回滚指针(7B),指向Undo Log记录[^2]。 - **ReadView**: ```c++ // 源码片段(trx0sys.h) struct ReadView { trx_id_t m_low_limit_id; // 高水位(事务ID>=此值不可见) trx_id_t m_up_limit_id; // 低水位(事务ID<此值可见) ids_t m_ids; // 活跃事务列表 }; ``` - 可见性判断规则: - 如果`DB_TRX_ID < m_up_limit_id`:数据可见。 - 如果`DB_TRX_ID >= m_low_limit_id`:数据不可见。 - 如果`DB_TRX_ID`在`m_ids`中:数据不可见(未提交事务)[^2]。 --- #### 五、日志系统协同 1. **Redo Log与Binlog协同(两阶段提交)** ```mermaid sequenceDiagram participant TC participant InnoDB participant Binlog TC->>InnoDB: Prepare阶段(写Redo Log) TC->>Binlog: Write Binlog TC->>InnoDB: Commit阶段(Redo Log标记提交) ``` - **崩溃恢复**:通过比较Redo Log和Binlog决定事务提交或回滚[^1]。 --- #### 六、锁机制 1. **锁类型** | 锁类型 | 作用范围 | 冲突检测 | |-------------|------------------|--------------------------------| | 行锁(X锁) | 单行记录 | 与其他X锁/S锁冲突 | | 间隙锁 | 索引记录间的间隙 | 防止RR隔离级别特有) | 2. **死锁检测** - InnoDB使用等待图(Wait-for Graph)算法检测死锁,超时时间由`innodb_lock_wait_timeout`控制。 --- #### 七、存储引擎对比 | 特性 | InnoDB | MyISAM | |---------------------|----------------------------|--------------------| | 事务支持 | ✅ ACID兼容 | ❌ | | 锁粒度 | 行级锁 | 表级锁 | | 崩溃恢复 | Redo Log保障 | 仅修复表文件 | | 外键支持 | ✅ | ❌ | | 内存临时表优化 | ❌ | ✅ | --- ### 总结 MySQL底层核心原理围绕**B+树索引优化**(减少磁盘IO)、**MVCC机制**(提升并发性能)、**日志系统协同**(保证数据安全)展开。通过存储引擎层的精细设计,InnoDB在事务处理和高并发场景下展现优势[^1][^3]。 --相关问题-- 1. InnoDB的Change Buffer如何优化写操作性能? 2. 如何通过调整`innodb_flush_log_at_trx_commit`平衡性能与数据安全? 3. MySQL的Hash索引与B+树索引有何本质区别? 4. 在RR隔离级别下,如何通过Next-Key Lock解决问题?
评论 6
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值