对于普通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的时候)，才会物理删除相应的行及其索引记录
- DATA_TRX_ID: 数据行所属事务id, 最近更新该行的事务id
- DATA_ROLL_PTR: 指向undolog的指针
2. InnoDB使用ReadView(读视图)来辅助判断当前事务是否能读取该行数据版本, ReadView主要包含如下属性
- m_ids: 生成ReadView时, 当前活跃所有的事务ID(事务ID自增)
- min_trx_id: 当前活跃的m_ids中最小的事务ID
- max_trx_id: 生成ReadView时，最大的事务ID，并不是m_ids中最大的ID
- 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
