MySQL · 源码分析 · InnoDB Repeatable Read隔离级别之大不同

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介:

开始介绍之前,先让我们了解一些基本概念。ANSI SQL STANDARD定义了4类隔离级别(READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE),包括了一些具体规则,用来限定事务内外的哪些改变是可见的,哪些是不可见的。低级别的隔离级别一般支持更高的并发处理,并拥有更低的系统开销。

  • Read Uncommitted(读未提交) 在该隔离级别,所有事务都可以看到其他未提交事务的执行结果。本隔离级别很少用于实际应用,因为它的性能也不比其他级别好多少。读取未提交的数据,也被称之为脏读(Dirty Read)。
  • Read Committed(读已提交) 一个事务只能看见已经提交事务所做的改变。这种隔离级别也支持所谓的不可重复读(Nonrepeatable Read),因为同一事务的其他实例在该实例处理其间可能会有新的commit,所以同一select可能返回不同结果。
  • Repeatable Read(可重读) 这是MySQL的默认事务隔离级别,它确保同一事务的多个实例在并发读取数据时,会看到同样的数据行。不过理论上,这会导致另一个棘手的问题:幻读 (Phantom Read)。简单的说,幻读指当用户读取某一范围的数据行时,另一个事务又在该范围内插入了新行,当用户再读取该范围的数据行时,会发现有新的“幻影” 行。InnoDB存储引擎通过多版本并发控制(MVCC,Multiversion Concurrency Control)机制解决了该问题。
  • Serializable(可串行化) 这是最高的隔离级别,它通过强制事务排序,使之不可能相互冲突,从而解决幻读问题。简言之,它是在每个读的数据行上加上共享锁。在这个级别,可能导致大量的超时现象和锁竞争。 这四种隔离级别采取不同的锁类型来实现。并发控制中读取同一个表的数据,可能出现如下问题:

脏读(Drity Read):事务T1修改了一行数据,事务T2在事务T1提交之前读到了该行数据。

不可重复读(Non-repeatable read): 事务T1读取了一行数据。 事务T2接着修改或者删除了改行数据,当T1再次读取同一行数据的时候,读到的数据时修改之后的或者发现已经被删除。

幻读(Phantom Read): 事务T1读取了满足某条件的一个数据集,事务T2插入了一行或者多行数据满足了T1的选择条件,导致事务T1再次使用同样的选择条件读取的时候,得到了比第一次读取更多的数据集。

MySQL/INNODB支持ANSI SQL STANDARD规定的四种隔离级别(READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE).本篇文章重点关注一下MySQL REPEATABLE READ隔离级别与其他数据实现方式上的不同之处。

下面看一下MySQL在REPEATABLE READ 隔离级别下的工作方式:

开启两个session。

rr.png

接下来看一下另外一个开源数据库PostgreSQL在REPEATABLE READ 隔离级别下的工作方式:

rr-pg.png

同样测试了SQL SERVER,得到的结果与PostgreSQL是一致的。

从上面的执行情况我们可以看到MySQL与PostgreSQL两者工作方式上有所不同。MySQL在执行UPDATE语句的时候对于session2的INSERT语句是可以看到的,也就是说发生了幻读。那么MySQL在隔离级别为REPEATABLE READ的情况下,表现出来的幻读现象是否属于一个BUG呢?曾经有人在2013年给官方提过一个关于该现象的BUG,请参考https://bugs.mysql.com/bug.php?id=63870。 从BUG页面的注释可以了解到,该现象是与MySQL对REPATABLE READ隔离级别的实现方式有关。而这种幻读现象对于REPATABLE READ隔离级别也是正确的方式。请看wikipedia上对于REPEATABLE READ的描述:

Repeatable reads
In this isolation level, a lock-based concurrency control DBMS implementation keeps read and write locks (acquired on selected data)
 until the end of the transaction. However, range-locks are not managed, so phantom reads can occur.

另外我们接着看一下ANSI SQL STANDARD对于各种隔离级别发生幻读的规定:

iso-trx.png

我们从wikipedia以及ANSI SQL STANDARD可以看到对于REPEATABLE READ隔离级别下是允许出现幻读现象的。

接下来我们从源码的角度分析一下Innodb对于REPEATABLE READ隔离级别的执行过程(代码只覆盖重要执行部分)。 以上面的例子为依据进行剖析: 对于第一条SELECT语句,InnoDB将调用row_search_for_mysql函数来返回扫描行。函数row_search_for_mysql调用相关代码如下:

UNIV_INTERN
dberr_t
row_search_for_mysql(
/*=================*/
 byte* buf, /* 用来存放记录的空间地址 */
 ulint mode, /* InnoDB页扫描顺序 */
 row_prebuilt_t* prebuilt, /* InnoDB扫描需要的所有信息都包含在这个结构体,比如表以及Index等信息 */
 ulint match_mode, /* 对于Index的匹配模式,是精确匹配还是前缀索引匹配 */
 ulint direction) /* 指定扫描顺序,正序还是倒叙扫描 */
{
	...
	/* 从这里我们看出开始一个新事务,并非是从执行BEGIN语句位置开始,而是从其后开始执行的第一条语句开始分配事务ID */
	trx_start_if_not_started(trx, ((trx->mysql_thd
 && thd_is_select(trx->mysql_thd)
 ) || srv_read_only_mode) ? FALSE : TRUE); 

	...
	// 如果是SQL语句第一次开始执行,需要考虑对TABLE增加意向所 if (!prebuilt->sql_stat_start) {
	 // 这里标记SQL语句已经开始执行,处理一条SQL语句循环扫描记录的过程 /* No need to set an intention lock or assign a read view */ if (UNIV_UNLIKELY
 (trx->read_view == NULL 
 && prebuilt->select_lock_type == LOCK_NONE)) {
 fputs("InnoDB: Error: MySQL is trying to" " perform a consistent read\n" "InnoDB: but the read view is not assigned!\n", stderr);
 trx_print(stderr, trx, 600);
 fputc('\n', stderr);
 ut_error;
	 }
 } else if (prebuilt->select_lock_type == LOCK_NONE) {
 /* This is a consistent read */ /* Assign a read view for the query */ // 如果是第一次执行SELECT语句,构建READ_VIEW. 该READ_VIEW 用来判断记录的可见性
 trx_assign_read_view(trx);
 prebuilt->sql_stat_start = FALSE;
 } else {
 ...
 }

	...

	 /* We are ready to look at a possible new index entry in the result
 set: the cursor is now placed on a user record */ /* 从这里我们看一下InnoDB如何获取一条新纪录。由于上面例子中SESSION1的第一条语句是SELECT语句,InnoDB在REPEATABLE READ 隔离级别下,不对SELECT 语句加锁,所以这里执行SELECT语句的时候prebuilt->select_lock_type为LOCK_NONE。下面我们直接看一下prebuilt->select_lock_type为LOCK_NONE的情况下,InnoDB如何扫描行? */ if (prebuilt->select_lock_type != LOCK_NONE) {
	... //稍后会对prebuilt->select_lock_type != LOCK_NONE的情况进行分析
	}
 else
	{
		/* This is a non-locking consistent read: if necessary, fetch
 a previous version of the record */ if (trx->isolation_level == TRX_ISO_READ_UNCOMMITTED) {

 /* 对于READ UNCOMMITTED隔离级别,我们什么都不需要,只要让他读取最新的记录版本即可 */

 } else if (index == clust_index) {

 /* Fetch a previous version of the row if the current
 one is not visible in the snapshot; if we have a very
 high force recovery level set, we try to avoid crashes
 by skipping this lookup */ // 如果是全表扫描或主键扫描,这里需要看看当前记录是否对当前事务可见 if (UNIV_LIKELY(srv_force_recovery < 5)
 && !lock_clust_rec_cons_read_sees(
 rec, index, offsets, trx->read_view)) {
		// 如果不可见,这里需要查找历史版本
 rec_t* old_vers;
 /* The following call returns 'offsets'
 associated with 'old_vers' */
 err = row_sel_build_prev_vers_for_mysql(
 trx->read_view, clust_index,
 prebuilt, rec, &offsets, &heap,
 &old_vers, &mtr);
		 if (err != DB_SUCCESS) {

 goto lock_wait_or_error;
 }

 if (old_vers == NULL) {
 /* The row did not exist yet in
 the read view */ // 如果当前记录对当前事务不可见,也没有历史版本,直接查找下一条记录 goto next_rec;
 }

 rec = old_vers;
		 } else {
 /* We are looking into a non-clustered index,
 and to get the right version of the record we
 have to look also into the clustered index: this
 is necessary, because we can only get the undo
 information via the clustered index record. */

 ut_ad(!dict_index_is_clust(index));
	 // 这里处理是Secondary index扫描的情况 if (!lock_sec_rec_cons_read_sees(
 rec, trx->read_view)) {
 /* We should look at the clustered index.
		 However, as this is a non-locking read,
 we can skip the clustered index lookup if
 the condition does not match the secondary
 index entry. */ // 这里InnoDB做了一下优化,如果当前记录不满足ICP,直接查找下一条记录;如果满足ICP则需要继续根据聚集索引寻找历史版本 switch (row_search_idx_cond_check(
 buf, prebuilt, rec, offsets)) {
 case ICP_NO_MATCH:
 goto next_rec;
 case ICP_OUT_OF_RANGE:
 err = DB_RECORD_NOT_FOUND;
 goto idx_cond_failed;
 case ICP_MATCH:
 goto requires_clust_rec;
 }
 ut_error;
 }
 }
 }
...
	}

}

接下来我们看一下UPDATE的执行过程。对于UPDATE操作执行流程的简单描述如下:

根据WHERE条件扫描一条记录(row_search_for_mysql)

更新当前获取的记录(ha_innobase::update_row)

重新将更新后的记录写入InnoDB存储引擎(row_upd_step)

那么我们按照上面的这个流程看一下源码方面的执行过程:

UNIV_INTERN
dberr_t
row_search_for_mysql(
/*=================*/
 byte* buf, /* 用来存放记录的空间地址 */
 ulint mode, /* InnoDB页扫描顺序 */
 row_prebuilt_t* prebuilt, /* InnoDB扫描需要的所有信息都包含在这个结构体,比如表以及Index等信息 */
 ulint match_mode, /* 对于Index的匹配模式,是精确匹配还是前缀索引匹配 */
 ulint direction) /* 指定扫描顺序,正序还是倒叙扫描 */
{
	...
	/* 从这里我们看出开始一个新事务,并非是从执行BEGIN语句位置开始,而是从其后开始执行的第一条语句开始分配事务ID */
	trx_start_if_not_started(trx, ((trx->mysql_thd
 && thd_is_select(trx->mysql_thd)
 ) || srv_read_only_mode) ? FALSE : TRUE); 

	...
	// 如果是SQL语句第一次开始执行,需要考虑对TABLE增加意向所 if (!prebuilt->sql_stat_start) {
	 // 这里标记SQL语句已经开始执行,处理一条SQL语句循环扫描记录的过程 /* No need to set an intention lock or assign a read view */ if (UNIV_UNLIKELY
 (trx->read_view == NULL 
 && prebuilt->select_lock_type == LOCK_NONE)) {
 ...
	 }
 } else if (prebuilt->select_lock_type == LOCK_NONE) {
	...
 } else {
	// 这里开始非INSERT的DML操作,因为DML会对记录增加记录排他锁。具体需要增加什么类型的锁,可以参考https://dev.mysql.com/doc/refman/5.7/en/innodb-locking.html
 wait_table_again:
	// 这里要对TABLE加意向锁
 err = lock_table(0, index->table,
 prebuilt->select_lock_type == LOCK_S
 ? LOCK_IS : LOCK_IX, thr);

 if (err != DB_SUCCESS) {

 table_lock_waited = TRUE;
 goto lock_table_wait;
 } 
 prebuilt->sql_stat_start = FALSE;
 }

	...
 if (prebuilt->select_lock_type != LOCK_NONE) {
	 ulint lock_type;

 if (!set_also_gap_locks
 || srv_locks_unsafe_for_binlog
 || trx->isolation_level <= TRX_ISO_READ_COMMITTED
 || (unique_search && !rec_get_deleted_flag(rec, comp))) {
	 // 这里对于READ_UNCOMMITTED以及READ_COMMITTED,或者唯一键扫描不需要使用gap锁 goto no_gap_lock;
 } else {
 lock_type = LOCK_ORDINARY;
 }
	
	/* If we are doing a 'greater or equal than a primary key
 value' search from a clustered index, and we find a record
 that has that exact primary key value, then there is no need
 to lock the gap before the record, because no insert in the
 gap can be in our search range. That is, no phantom row can
 appear that way.

 An example: if col1 is the primary key, the search is WHERE
 col1 >= 100, and we find a record where col1 = 100, then no
 need to lock the gap before that record. */ if (index == clust_index
 && mode == PAGE_CUR_GE
 && direction == 0
 && dtuple_get_n_fields_cmp(search_tuple)
 == dict_index_get_n_unique(index)
 && 0 == cmp_dtuple_rec(search_tuple, rec, offsets)) {
no_gap_lock:
 lock_type = LOCK_REC_NOT_GAP;
 }

	 err = sel_set_rec_lock(btr_pcur_get_block(pcur),
 rec, index, offsets,
 prebuilt->select_lock_type,
 lock_type, thr);

 switch (err) {
 const rec_t* old_vers;
 case DB_SUCCESS_LOCKED_REC:
 if (srv_locks_unsafe_for_binlog
 || trx->isolation_level
 <= TRX_ISO_READ_COMMITTED) {
 /* Note that a record of
 prebuilt->index was locked. */
 prebuilt->new_rec_locks = 1;
 }
 err = DB_SUCCESS;
 case DB_SUCCESS:
	 // 加锁成功后就认为记录可见了,并未像SELECT语句一样根据事务开始的READ_VIEW进行可见性判断。所以对于DML来说,所有提交的事务都是可见的。 break;
 case DB_LOCK_WAIT:
	 /* Never unlock rows that were part of a conflict. */ // 如果存在锁冲突,也就是其他事务正在更新同一行
 prebuilt->new_rec_locks = 0;

 if (UNIV_LIKELY(prebuilt->row_read_type
 != ROW_READ_TRY_SEMI_CONSISTENT)
 || unique_search
 || index != clust_index) {

 goto lock_wait_or_error;
 }

 /* The following call returns 'offsets'
 associated with 'old_vers' */ // 这里需要查看是否有别的事务提交了,以便获取最新版本的记录
 row_sel_build_committed_vers_for_mysql(
 clust_index, prebuilt, rec,
 &offsets, &heap, &old_vers, &mtr);

 /* Check whether it was a deadlock or not, if not
 a deadlock and the transaction had to wait then
 release the lock it is waiting on. */
	 err = lock_trx_handle_wait(trx);

 switch (err) {
 case DB_SUCCESS:
 /* The lock was granted while we were
 searching for the last committed version.
 Do a normal locking read. */

 offsets = rec_get_offsets(
 rec, index, offsets, ULINT_UNDEFINED,
 &heap);
 goto locks_ok;
 case DB_DEADLOCK:
 goto lock_wait_or_error;
 case DB_LOCK_WAIT:
 err = DB_SUCCESS;
 break;
 default:
 ut_error;
 }
	 if (old_vers == NULL) {
 /* The row was not yet committed */ goto next_rec;
 }
	 did_semi_consistent_read = TRUE;
 rec = old_vers;
 break;
 default:

 goto lock_wait_or_error;
 }

	}

从上面的代码我们可以看到,对于UPDATE操作更新的记录包含幻读读取到的已提交事务的最新记录。那么接下来看为什么UPDATE之后的SELECT语句对于UPDATE之后的所有语句都可见了? 原因是前面的UPDATE语句执行之后,会将当前记录上存储的事务信息更新为当前的事务,而当前事务所做的任何更新,对本事务所有SELECT查询都变的可见,因此最后输出的结果是UPDATE执行后更新的所有记录。

当前各种数据库对于隔离级别的支持不尽相同,比如ORACLE,它只实现了READ COMMITTED和SERIALIZABLE两种ANSI SQL STANDARD规定的隔离级别(这里ORACLE还实现了一种自定义的READ ONLY隔离级别,具体请参考https://docs.oracle.com/cd/B28359_01/server.111/b28318/consist.htm#CNCPT621) , 而没有实现REPEATABLE READ。对于相同的隔离级别,不同的数据库有着自己不同的实现方式。所以我们在理解隔离级别的时候需要针对具体的数据库。综上所述,我们看到了MySQL InnoDB引擎对于REPEATABLE READ隔离级别有着不同于其他数据库的实现方式。而该实现方式符合ANSI SQL STANDARD,并非属于实现上的BUG。

相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL,然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
2天前
|
SQL 关系型数据库 MySQL
MySQL底层概述—10.InnoDB锁机制
本文介绍了:锁概述、锁分类、全局锁实战、表级锁(偏读)实战、行级锁升级表级锁实战、间隙锁实战、临键锁实战、幻读演示和解决、行级锁(偏写)优化建议、乐观锁实战、行锁原理分析、死锁与解决方案
MySQL底层概述—10.InnoDB锁机制
|
4天前
|
存储 缓存 关系型数据库
MySQL底层概述—5.InnoDB参数优化
本文介绍了MySQL数据库中与内存、日志和IO线程相关的参数优化,旨在提升数据库性能。主要内容包括: 1. 内存相关参数优化:缓冲池内存大小配置、配置多个Buffer Pool实例、Chunk大小配置、InnoDB缓存性能评估、Page管理相关参数、Change Buffer相关参数优化。 2. 日志相关参数优化:日志缓冲区配置、日志文件参数优化。 3. IO线程相关参数优化: 查询缓存参数、脏页刷盘参数、LRU链表参数、脏页刷盘相关参数。
MySQL底层概述—5.InnoDB参数优化
|
4天前
|
存储 SQL 关系型数据库
MySQL底层概述—4.InnoDB数据文件
本文介绍了InnoDB表空间文件结构及其组成部分,包括表空间、段、区、页和行。表空间是最高逻辑层,包含多个段;段由若干个区组成,每个区包含64个连续的页,页用于存储多条行记录。文章还详细解析了Page结构,分为通用部分(文件头与文件尾)、数据记录部分和页目录部分。此外,文中探讨了行记录格式,包括四种行格式(Redundant、Compact、Dynamic和Compressed),重点介绍了Compact行记录格式及其溢出机制。最后,文章解释了不同行格式的特点及应用场景,帮助理解InnoDB存储引擎的工作原理。
MySQL底层概述—4.InnoDB数据文件
|
4天前
|
存储 缓存 关系型数据库
MySQL底层概述—3.InnoDB线程模型
InnoDB存储引擎采用多线程模型,包含多个后台线程以处理不同任务。主要线程包括:IO Thread负责读写数据页和日志;Purge Thread回收已提交事务的undo日志;Page Cleaner Thread刷新脏页并清理redo日志;Master Thread调度其他线程,定时刷新脏页、回收undo日志、写入redo日志和合并写缓冲。各线程协同工作,确保数据一致性和高效性能。
MySQL底层概述—3.InnoDB线程模型
|
5天前
|
存储 SQL 关系型数据库
MySQL底层概述—2.InnoDB磁盘结构
InnoDB磁盘结构主要包括表空间(Tablespaces)、数据字典(Data Dictionary)、双写缓冲区(Double Write Buffer)、重做日志(redo log)和撤销日志(undo log)。其中,表空间分为系统、独立、通用、Undo及临时表空间,分别用于存储不同类型的数据。数据字典从MySQL 8.0起不再依赖.frm文件,转而使用InnoDB引擎存储,支持事务原子性DDL操作。
157 100
MySQL底层概述—2.InnoDB磁盘结构
|
5天前
|
缓存 算法 关系型数据库
MySQL底层概述—1.InnoDB内存结构
本文介绍了InnoDB引擎的关键组件和机制,包括引擎架构、Buffer Pool、Page管理机制、Change Buffer、Log Buffer及Adaptive Hash Index。
151 97
MySQL底层概述—1.InnoDB内存结构
|
10天前
|
存储 SQL 缓存
MySQL原理简介—2.InnoDB架构原理和执行流程
本文介绍了MySQL中更新语句的执行流程及其背后的机制,主要包括: 1. **更新语句的执行流程**:从SQL解析到执行器调用InnoDB存储引擎接口。 2. **Buffer Pool缓冲池**:缓存磁盘数据,减少磁盘I/O。 3. **Undo日志**:记录更新前的数据,支持事务回滚。 4. **Redo日志**:确保事务持久性,防止宕机导致的数据丢失。 5. **Binlog日志**:记录逻辑操作,用于数据恢复和主从复制。 6. **事务提交机制**:包括redo日志和binlog日志的刷盘策略,确保数据一致性。 7. **后台IO线程**:将内存中的脏数据异步刷入磁盘。
|
2月前
|
存储 缓存 关系型数据库
【MySQL进阶篇】存储引擎(MySQL体系结构、InnoDB、MyISAM、Memory区别及特点、存储引擎的选择方案)
MySQL的存储引擎是其核心组件之一,负责数据的存储、索引和检索。不同的存储引擎具有不同的功能和特性,可以根据业务需求 选择合适的引擎。本文详细介绍了MySQL体系结构、InnoDB、MyISAM、Memory区别及特点、存储引擎的选择方案。
【MySQL进阶篇】存储引擎(MySQL体系结构、InnoDB、MyISAM、Memory区别及特点、存储引擎的选择方案)
|
2月前
|
存储 关系型数据库 MySQL
MySQL存储引擎详述:InnoDB为何胜出?
MySQL 是最流行的开源关系型数据库之一,其存储引擎设计是其高效灵活的关键。InnoDB 作为默认存储引擎,支持事务、行级锁和外键约束,适用于高并发读写和数据完整性要求高的场景;而 MyISAM 不支持事务,适合读密集且对事务要求不高的应用。根据不同需求选择合适的存储引擎至关重要,官方推荐大多数场景使用 InnoDB。
82 7
|
3月前
|
存储 Oracle 关系型数据库
【赵渝强老师】MySQL InnoDB的数据文件与重做日志文件
本文介绍了MySQL InnoDB存储引擎中的数据文件和重做日志文件。数据文件包括`.ibd`和`ibdata`文件,用于存放InnoDB数据和索引。重做日志文件(redo log)确保数据的可靠性和事务的持久性,其大小和路径可由相关参数配置。文章还提供了视频讲解和示例代码。
200 11
【赵渝强老师】MySQL InnoDB的数据文件与重做日志文件