MySQL InnoDB MVCC机制

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
日志服务 SLS,月写入数据量 50GB 1个月
简介: MySQL InnoDB MVCC机制

对于普通select来说, InnoDB使用MVCC保证了事务隔离. 同一事务的两次相同查询语句都是同样结果, 其他事务修改记录不影响当前事务, 特殊情况是会看到同一事务中先前语句所做的更新, 所以对于普通select(快照读)来说, MVCC是解决了脏读/不可重复读/幻行的; 而对于当前读(锁定读)来说, InnoDB提供了GAP/Next-Key/Index-Record等锁算法保证隔离性, 这个后续再说.

当前MySQL8.0.28

那么InnoDB是如何实现MVCC的

1. InnoDB默认以B+Tree结构组织索引记录, 主键是聚集索引, 叶子节点存储真正的索引记录, 而索引记录会多出两列与MVCC有关的隐藏列, 当使用 SQL 删除行时,不会立即从数据库中物理删除它. 对于更新操作, 更新前的记录同样会被保留, 只是标记删除. InnoDB只有在清除undolog时(当系统里没有比这个回滚日志更早的ReadView的时候),才会物理删除相应的行及其索引记录

  1. DATA_TRX_ID: 数据行所属事务id, 最近更新该行的事务id
  2. DATA_ROLL_PTR: 指向undolog的指针

2. InnoDB使用ReadView(读视图)来辅助判断当前事务是否能读取该行数据版本, ReadView主要包含如下属性

  1. m_ids: 生成ReadView时, 当前活跃所有的事务ID(事务ID自增)
  2. min_trx_id: 当前活跃的m_ids中最小的事务ID
  3. max_trx_id: 生成ReadView时,最大的事务ID,并不是m_ids中最大的ID
  4. creator_trx_id: 该ReadView在哪个事务创建的

1.如果被访问版本(当前最新记录或undolog中的记录)的 data_trx_id 小于min_trx_id,说明生成该版本的事务在 ReadView 生成前就已经提交了,那么该版本可以被当前事务访问 2.如果被访问版本的 data_trx_id大于max_trx_id,说明生成该版本数据的事务在生成 ReadView后才生成,那么该版本不可以被当前事务访问 3.如果被访问版本的 data_trx_id属性值在 max_trx_id和min_trx_id之间(包含),那就需要判断一下 trx_id 的值是不是在 m_ids 列表中。如果在,说明创建 ReadView 时生成该版本所属事务还是活跃的,因此该版本不可以被访问;如果不在,说明创建 ReadView 时生成该版本的事务已经被提交,该版本可以被访问 4.被访问版本的事务id等于当前事务id

mysql8.0.28源码

[[nodiscard]] bool changes_visible(trx_id_t id,                                      const table_name_t &name) const {     ut_ad(id > 0);     if (id < m_up_limit_id || id == m_creator_trx_id) {       return (true);     }     check_trx_id_sanity(id, name);     if (id >= m_low_limit_id) {       return (false);     } else if (m_ids.empty()) {       return (true);     }     const ids_t::value_type *p = m_ids.data();     return (!std::binary_search(p, p + m_ids.size(), id));   }

3. 在MySQL中, 实际上每条记录在更新的时候都会同时记录一条回滚操作到undolog(undolog默认在mysql的data文件夹中)中. 记录上的最新值, 通过回滚操作, 都可以得到前一个状态的值.

比如id=880这行记录

id

name

age

value

uni

DATA_TRX_ID

DATA_ROLL_PTR

880

Barb Dwyer

10

42

52

100

执行update user set age = 12 where id = 880;后, undolog中存储update执行前的记录

更新后的记录

id

name

age

value

uni

DATA_TRX_ID

DATA_ROLL_PTR

880

Barb Dwyer

12

42

52

200

0xAA

undolog记录

id

name

age

value

uni

DATA_TRX_ID

DATA_ROLL_PTR

880

Barb Dwyer

10

42

52

100

指向下一个undolog的地址

若后续事务多次修改该行, 则undolog中会有多条记录

再执行update user set age = 14 where id = 880;

undolog记录

addr

id

name

age

value

uni

DATA_TRX_ID

DATA_ROLL_PTR

0xBB

880

Barb Dwyer

12

42

52

200

0xAA

0xAA

880

Barb Dwyer

10

42

52

100

当系统里没有比这个回滚日志更早的ReadView的时候会删除回滚日志, 即该undolog不再被需要, 但insert的undolog日志在事务结束后可以立即删除, 因为如果某个事务ID=100新增了一条记录,那么在这个事务版本之前这个记录是不存在的,也就是这条数据要么就是事务100提交,然后就存在这条数据了,事务100没有提交,这条数据就是null, 也就不需要多版本的冗余, 所以事务提交就可以直接删除insert的undo log.

4. 对于二级索引(非聚簇索引), MVCC对二级索引的处理方式与对聚集索引的处理方式不同. 聚集索引中的记录立即更新(内存中的记录),它们的隐藏列指向undolog记录位置,可以从中重建早期版本的记录。与聚集索引记录不同,二级索引记录不包含隐藏的系统列,也不会立即更新. 当二级索引列被更新时,旧二级索引记录被删除标记,新记录被插入,并且被删除标记的记录最终被清除(当该记录不再被需要时), 当二级索引记录被标记删除或二级索引页面被更新时,则在聚集索引中查找数据库记录. 但是,如果启用了 索引条件下推 (ICP)优化,并且WHERE条件可以仅使用索引中的字段来过滤数据,则 MySQL 服务器仍会将这部分WHERE条件下推到存储引擎. 如果没有找到匹配的记录,则无需在聚集索引中查找。如果找到匹配的记录,即使记录被标记删除,也会在聚集索引中查找记录

5. RR与RC的区别就在于, RC每次查询都生成一个最新的ReadView, 而RR只生成一个

以下是一些较特殊的情况

Step

Session A

Session B

1

begin;

2

begin; update user set age = 12 where id = 880; commit;

3

select * from user where id = 880; +-----+------------+-----+-------+-----+ | id | name | age | value | uni | +-----+------------+-----+-------+-----+ | 880 | Barb Dwyer | 12 | 42 | 52 |

4

commit;

RR隔离级别下的一致性读,不是以begin开始的时间点作为快照建立时间点,而是以第一条select语句的时间点作为快照建立的时间点.

Step

Session A

Session B

1

begin;

2

select * from user where name = "update"; Empty set

3

begin; update user set name = "update" where value = 42; commit;

4

select * from user where name = "update"; Empty set

5

select * from user where name = "update" for update +-----+------------+-----+-------+-----+ |id |name |age |value |uni | +-----+------------+-----+-------+-----+ |880 |update|12 |42 |52 | +-----+------------+-----+-------+-----+

6

commit;

select ... for update使用当前读, 会读取最新版本的数据

Step

Session A

Session B

Session C

1

begin; select * from user where name = 'tom'; +-----+------------+-----+-------+-----+ |id |name |age |value |uni | +-----+------------+-----+-------+-----+ |990 | tom | 10 | 42 | 52 |

2

begin; select * from user where name = "update"; Empty set

3

begin; update user set name = "update" where id = 990; commit;

4

select * from user where name = "update"; Empty set

5

update user set age = 99 where name = "update";

6

select * from user where name = "update" +-----+------------+-----+-------+-----+ |id | name |age |value |uni | +-----+------------+-----+-------+-----+ |990| update | 99 |42 |52 | +-----+------------+-----+-------+-----+

select * from user where name = 'tom'; +-----+------------+-----+-------+-----+ |id |name |age |value |uni | +-----+------------+-----+-------+-----+ |990 |tom |10 |42 |52 |

7

commit;

commit;

若当前事务修改了其他事务修改过的行, 则该事务后续使用普通select也能看到其他事务更新的数据.

会话A一开始查询不到name=update的记录,

接着会话B在第三步修改了将id=990这行记录的name修改为update, 生成了一条undolog记录, 同时也将990这行的事务id和undolog指针记录更新了. 根据ReadView的定义, 会话B的事务id明显比会话A创建时最大的事务id还要大,

所以会话A第四步再次查询, 仍然查询不到最新的修改.

但会话A第五步, 使用了update语句修改990这行的age字段, update使用当前读, 所以能够查询到name=update的记录, 事务A把字段age更新为99, 也将990这行的事务id和undolog指针记录更新为当前事务id和当前事务产生的undolog位置

会话A第六步再调用select查询, 查询到了990这行的name符合条件, 同时该行的事务id也符合ReadView可见性的定义, 事务列的数据与当前事务一致, 所以就可以查询到记录

同是第六步, 会话C再次调用查询, name和age仍为事务开始时所查询到的. 且因为name这行只是被标记删除, 所以name这个条件仍可以用于查询. 只是InnoDB发现当前行的事务id已经被更新过, 所以再去查询undolog中的版本记录, 最终根据会话C开启事务时创建的ReadView返回会话B修改后生成的数据版本

ref: https://www.cnblogs.com/rongdi/p/13378892.html

相关实践学习
每个IT人都想学的“Web应用上云经典架构”实战
本实验从Web应用上云这个最基本的、最普遍的需求出发,帮助IT从业者们通过“阿里云Web应用上云解决方案”,了解一个企业级Web应用上云的常见架构,了解如何构建一个高可用、可扩展的企业级应用架构。
MySQL数据库入门学习
本课程通过最流行的开源数据库MySQL带你了解数据库的世界。 &nbsp; 相关的阿里云产品:云数据库RDS MySQL 版 阿里云关系型数据库RDS(Relational Database Service)是一种稳定可靠、可弹性伸缩的在线数据库服务,提供容灾、备份、恢复、迁移等方面的全套解决方案,彻底解决数据库运维的烦恼。 了解产品详情:&nbsp;https://www.aliyun.com/product/rds/mysql&nbsp;
目录
相关文章
|
23天前
|
存储 关系型数据库 MySQL
介绍MySQL的InnoDB引擎特性
总结而言 , Inno DB 引搞 是 MySQL 中 高 性 能 , 高 可靠 的 存 储选项 , 宽泛 应用于要求强 复杂交易处理场景 。
63 15
|
6月前
|
存储 网络协议 关系型数据库
MySQL8.4创建keyring给InnoDB表进行静态数据加密
MySQL8.4创建keyring给InnoDB表进行静态数据加密
150 1
|
10月前
|
存储 缓存 关系型数据库
【MySQL进阶篇】存储引擎(MySQL体系结构、InnoDB、MyISAM、Memory区别及特点、存储引擎的选择方案)
MySQL的存储引擎是其核心组件之一,负责数据的存储、索引和检索。不同的存储引擎具有不同的功能和特性,可以根据业务需求 选择合适的引擎。本文详细介绍了MySQL体系结构、InnoDB、MyISAM、Memory区别及特点、存储引擎的选择方案。
1677 57
【MySQL进阶篇】存储引擎(MySQL体系结构、InnoDB、MyISAM、Memory区别及特点、存储引擎的选择方案)
|
6月前
|
SQL 缓存 关系型数据库
使用温InnoDB缓冲池启动MySQL测试
使用温InnoDB缓冲池启动MySQL测试
105 0
|
1月前
|
安全 关系型数据库 MySQL
MySQL安全最佳实践:保护你的数据库
本文深入探讨了MySQL数据库的安全防护体系,涵盖认证安全、访问控制、网络安全、数据加密、审计监控、备份恢复、操作系统安全、应急响应等多个方面。通过具体配置示例,为企业提供了一套全面的安全实践方案,帮助强化数据库安全,防止数据泄露和未授权访问,保障企业数据资产安全。
|
17天前
|
缓存 关系型数据库 BI
使用MYSQL Report分析数据库性能(下)
使用MYSQL Report分析数据库性能
54 3
|
23天前
|
关系型数据库 MySQL 数据库
自建数据库如何迁移至RDS MySQL实例
数据库迁移是一项复杂且耗时的工程,需考虑数据安全、完整性及业务中断影响。使用阿里云数据传输服务DTS,可快速、平滑完成迁移任务,将应用停机时间降至分钟级。您还可通过全量备份自建数据库并恢复至RDS MySQL实例,实现间接迁移上云。
|
2月前
|
存储 运维 关系型数据库
从MySQL到云数据库,数据库迁移真的有必要吗?
本文探讨了企业在业务增长背景下,是否应从 MySQL 迁移至云数据库的决策问题。分析了 MySQL 的优势与瓶颈,对比了云数据库在存储计算分离、自动化运维、多负载支持等方面的优势,并提出判断迁移必要性的五个关键问题及实施路径,帮助企业理性决策并落地迁移方案。
|
10天前
|
关系型数据库 MySQL 分布式数据库
阿里云PolarDB云原生数据库收费价格:MySQL和PostgreSQL详细介绍
阿里云PolarDB兼容MySQL、PostgreSQL及Oracle语法,支持集中式与分布式架构。标准版2核4G年费1116元起,企业版最高性能达4核16G,支持HTAP与多级高可用,广泛应用于金融、政务、互联网等领域,TCO成本降低50%。
|
11天前
|
关系型数据库 MySQL 数据库
阿里云数据库RDS费用价格:MySQL、SQL Server、PostgreSQL和MariaDB引擎收费标准
阿里云RDS数据库支持MySQL、SQL Server、PostgreSQL、MariaDB,多种引擎优惠上线!MySQL倚天版88元/年,SQL Server 2核4G仅299元/年,PostgreSQL 227元/年起。高可用、可弹性伸缩,安全稳定。详情见官网活动页。

推荐镜像

更多