MySQL的事务隔离和MVCC

0.前言:为什么要写这篇文章?

事务隔离和mvcc的重要性

不同于很多MySQL的原理,只需要DBA掌握,事务对于研发人员也是必须掌握的知识点和原理。并发程度越高,数据库里的锁和事务越明显,越重要。所以:数据库事务和mvcc是研发和DBA都要熟练掌握的

另一方面的原因是现有的资料对mvcc写得不够直观

现有的对mvcc原理的讲解停留在画图阶段,我觉得光画图还不够,要实打实的一个字节一个字节的看MySQL真实的数据文件是怎么实现的。利用自研的MySQL数据文件分析工具( 参考:innodb存储格式 )。可以很直观的把mvcc实现的底层逻辑给展示出来。

以下两篇文章,可以协助你更好的理解本章节的内容

环境准备

建一张表dboopuser并insert几条数据

drop table dboopuser;
create table dboopuser(
userid int unsigned  not null primary key ,
age smallint unsigned not null  default 0,
username varchar(20) not null default '',
userimg varchar(255) not null default ''
) ENGINE=InnoDB  COMMENT='测试user表--用于mvcc测试20220727' ;


insert into dboopuser(userid,age,username,userimg) values(9527,25,'cccccccccc','http://www.dboop.com/img/user/2002_innodbtrx_527.jpg');
insert into dboopuser(userid,age,username,userimg) values(9528,15,'dddddddddddddd','http://www.dboop.com/img/user/2002_innodbtrx_528.jpg');
insert into dboopuser(userid,age,username,userimg) values(9529,25,'eeeeeeeeeeeeeeeee','http://www.dboop.com/img/user/2002_innodbtrx_529.jpg');
insert into dboopuser(userid,age,username,userimg) values(9530,26,'zhangsan01','http://www.dboop.com/img/user/2002_innodbtrx_530.jpg');
insert into dboopuser(userid,age,username,userimg) values(9531,27,'zhangsan02','http://www.dboop.com/img/user/2002_innodbtrx_531.jpg');
insert into dboopuser(userid,age,username,userimg) values(9532,28,'zhangsan03','http://www.dboop.com/img/user/2002_innodbtrx_532.jpg');
insert into dboopuser(userid,age,username,userimg) values(9533,29,'zhangsan04','http://www.dboop.com/img/user/2002_innodbtrx_533.jpg');
insert into dboopuser(userid,age,username,userimg) values(9534,29,'zhangsan05','http://www.dboop.com/img/user/2002_innodbtrx_534.jpg');
insert into dboopuser(userid,age,username,userimg) values(9535,29,'zhangsan06','http://www.dboop.com/img/user/2002_innodbtrx_535.jpg');


初始状态

文件信息

我们在文件系统中看到data目录多了一个dboopuser.ibd文件

ll /data/mysql3406/data/dboop_test
total 80
-rw-r----- 1 mysql mysql 114688 Jul 28 20:19 dboopuser.ibd

注意这里的文件大小:114688 用它除以16384(16k) 刚好等于 7

因为MySQL是用页来组织文件的,所以这里用了7页,每页16k。

图解:用可视化工具查看这个文件

innodb_mvcc01

工具的介绍和使用说明,参看以前录的视频:innodb存储格式 通过可视化工具,我们可以直观的看到这7个页的类型。这里我们重点要关注的是第4页

图解:查看Bree 页详细内容

点开page 4 这个Btree页查看16k字节的详细内容

innodb_mvcc03

上图的图形化展示,我们可以看到我们insert的9条数据,在ibd文件的第4个page中,具体的存放方式。

分析具体的一条record记录

上面的图上,我们找到了这个想要的9条记录,现在来分析它

Row:007f,delete_flag:0,min_rec:0,row_id:9527,trx_id:2387,roll_pointer:1,2,225,272,data:(25,cccccccccc..),next_offset:0x58-->0xd7
Row:00d7,delete_flag:0,min_rec:0,row_id:9528,trx_id:2389,roll_pointer:1,1,226,272,data:(15,dddddddddd..),next_offset:0x5c-->0x133
Row:0133,delete_flag:0,min_rec:0,row_id:9529,trx_id:2396,roll_pointer:1,1,230,272,data:(25,eeeeeeeeee..),next_offset:0x5f-->0x192
Row:0192,delete_flag:0,min_rec:0,row_id:9530,trx_id:2398,roll_pointer:1,2,231,272,data:(26,zhangsan01..),next_offset:0x58-->0x1ea
Row:01ea,delete_flag:0,min_rec:0,row_id:9531,trx_id:2404,roll_pointer:1,1,234,272,data:(27,zhangsan02..),next_offset:0x58-->0x242
Row:0242,delete_flag:0,min_rec:0,row_id:9532,trx_id:2406,roll_pointer:1,2,235,272,data:(28,zhangsan03..),next_offset:0x58-->0x29a
Row:029a,delete_flag:0,min_rec:0,row_id:9533,trx_id:2408,roll_pointer:1,1,235,272,data:(29,zhangsan04..),next_offset:0x58-->0x2f2
Row:02f2,delete_flag:0,min_rec:0,row_id:9534,trx_id:2410,roll_pointer:1,2,236,272,data:(29,zhangsan05..),next_offset:0x58-->0x34a
Row:034a,delete_flag:0,min_rec:0,row_id:9535,trx_id:2412,roll_pointer:1,1,236,272,data:(29,zhangsan06..),next_offset:-0x2da-->0x0

需要记住以下几个关键的信息

1.事务隔离级别

以下4个隔离级别,从上往下,要求越来越严重,通常我们只会用到中间两个

重点要理解的是

可重复读 (RR级别)

已提交读 (RC级别)

2.ReadView

什么是mvcc

MVCC (Multi-Version Concurrency Control ,多版本并发控制)指的就是在使用READ COMMITTD、REPEATABLE READ这两种隔离级别的事务在执行普通的SEELCT操作时访问记录的版本链的过程。

** 注意:ReadView是理解MVCC的关键,是理解MySQL事务隔离级别的关键,是线上偶然发生的~莫名期妙的事务问题~定位的关键 **

什么是ReadView

官方文档的解释:

An internal snapshot used by the MVCC mechanism of InnoDB. Certain transactions, depending on their isolation level, see the data values as they were at the time the transaction (or in some cases, the statement) started. Isolation levels that use a read view are REPEATABLE READ, READ COMMITTED, and READ UNCOMMITTED.

翻译过来:

ReadView存放的内容

ReadView是个内存里的结构,里面存放了4块内容

id:表示生成该readview的事务的事务id;

当前事务,假定当前的事务是00000100

m_ids:生成readview时,当前系统中活跃的读写事务id列表;

例如:创建事务00000100创建RV时,系统正在活跃的事务有0000095,0x000096,0000097那么0000095,0000096,0000097都会加入到00000100的m_ids中.

m_low_limit_id:又叫min_trx_id,生成readview时,当前系统中活跃的读写事务中最小的事务id

也就是m_ids中最小的值;本例中的 0000095

m_up_limit_id:又叫max_trx_id,生成readview时,系统中应该分配给下一个事务的id值;

本例中,下一个事务是00000101

ReadView如何控制访问行为

判断规则

有了ReadView,在访问某条记录时,按照以下步骤判断记录的某个版本是否可见:

实例演示(RR级别)

初始状态

我们来看真实的dboopuser这张表的情况

mysql> select * from dboopuser;
+--------+-----+-------------------+------------------------------------------------------+
| userid | age | username          | userimg                                              |
+--------+-----+-------------------+------------------------------------------------------+
|   9527 |  25 | cccccccccc        | http://www.dboop.com/img/user/2002_innodbtrx_527.jpg |
|   9528 |  15 | dddddddddddddd    | http://www.dboop.com/img/user/2002_innodbtrx_528.jpg |
|   9529 |  25 | eeeeeeeeeeeeeeeee | http://www.dboop.com/img/user/2002_innodbtrx_529.jpg |
|   9530 |  26 | zhangsan01        | http://www.dboop.com/img/user/2002_innodbtrx_530.jpg |
|   9531 |  27 | zhangsan02        | http://www.dboop.com/img/user/2002_innodbtrx_531.jpg |
|   9532 |  28 | zhangsan03        | http://www.dboop.com/img/user/2002_innodbtrx_532.jpg |
|   9533 |  29 | zhangsan04        | http://www.dboop.com/img/user/2002_innodbtrx_533.jpg |
|   9534 |  29 | zhangsan05        | http://www.dboop.com/img/user/2002_innodbtrx_534.jpg |
|   9535 |  29 | zhangsan06        | http://www.dboop.com/img/user/2002_innodbtrx_535.jpg |
+--------+-----+-------------------+------------------------------------------------------+
9 rows in set (0.00 sec)

一共有9条数据,用分析工具,读取其物理结构
Row:007f,delete_flag:0,min_rec:0,row_id:9527,trx_id:2387,roll_pointer:1,2,225,272,data:(25,cccccccccc..),next_offset:0x58-->0xd7
Row:00d7,delete_flag:0,min_rec:0,row_id:9528,trx_id:2389,roll_pointer:1,1,226,272,data:(15,dddddddddd..),next_offset:0x5c-->0x133
Row:0133,delete_flag:0,min_rec:0,row_id:9529,trx_id:2396,roll_pointer:1,1,230,272,data:(25,eeeeeeeeee..),next_offset:0x5f-->0x192
Row:0192,delete_flag:0,min_rec:0,row_id:9530,trx_id:2398,roll_pointer:1,2,231,272,data:(26,zhangsan01..),next_offset:0x58-->0x1ea
Row:01ea,delete_flag:0,min_rec:0,row_id:9531,trx_id:2404,roll_pointer:1,1,234,272,data:(27,zhangsan02..),next_offset:0x58-->0x242
Row:0242,delete_flag:0,min_rec:0,row_id:9532,trx_id:2406,roll_pointer:1,2,235,272,data:(28,zhangsan03..),next_offset:0x58-->0x29a
Row:029a,delete_flag:0,min_rec:0,row_id:9533,trx_id:2408,roll_pointer:1,1,235,272,data:(29,zhangsan04..),next_offset:0x58-->0x2f2
Row:02f2,delete_flag:0,min_rec:0,row_id:9534,trx_id:2410,roll_pointer:1,2,236,272,data:(29,zhangsan05..),next_offset:0x58-->0x34a
Row:034a,delete_flag:0,min_rec:0,row_id:9535,trx_id:2412,roll_pointer:1,1,236,272,data:(29,zhangsan06..),next_offset:-0x2da-->0x0

测试步骤1

线程1:begin;select * from dboopuser;

返回结果:

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from dboopuser;
+--------+-----+-------------------+------------------------------------------------------+
| userid | age | username          | userimg                                              |
+--------+-----+-------------------+------------------------------------------------------+
|   9527 |  25 | cccccccccc        | http://www.dboop.com/img/user/2002_innodbtrx_527.jpg |
|   9528 |  15 | dddddddddddddd    | http://www.dboop.com/img/user/2002_innodbtrx_528.jpg |
|   9529 |  25 | eeeeeeeeeeeeeeeee | http://www.dboop.com/img/user/2002_innodbtrx_529.jpg |
|   9530 |  26 | zhangsan01        | http://www.dboop.com/img/user/2002_innodbtrx_530.jpg |
|   9531 |  27 | zhangsan02        | http://www.dboop.com/img/user/2002_innodbtrx_531.jpg |
|   9532 |  28 | zhangsan03        | http://www.dboop.com/img/user/2002_innodbtrx_532.jpg |
|   9533 |  29 | zhangsan04        | http://www.dboop.com/img/user/2002_innodbtrx_533.jpg |
|   9534 |  29 | zhangsan05        | http://www.dboop.com/img/user/2002_innodbtrx_534.jpg |
|   9535 |  29 | zhangsan06        | http://www.dboop.com/img/user/2002_innodbtrx_535.jpg |
+--------+-----+-------------------+------------------------------------------------------+
9 rows in set (0.00 sec)

mysql> select TRX_ID from INFORMATION_SCHEMA.INNODB_TRX where TRX_MYSQL_THREAD_ID = CONNECTION_ID();
+-----------------+
| TRX_ID          |
+-----------------+
| 421658589187480 |
+-----------------+
1 row in set (0.00 sec)

show engine innodb status \G
*************************** 1. row ***************************
  Type: InnoDB
....
------------
TRANSACTIONS
------------
Trx id counter 2431

线程2:begin;update dboopuser set age=35 where userid=9528;commit;

返回结果:

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update dboopuser set age=35 where userid=9528;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select TRX_ID from INFORMATION_SCHEMA.INNODB_TRX where TRX_MYSQL_THREAD_ID = CONNECTION_ID();
+--------+
| TRX_ID |
+--------+
|   2432 |
+--------+
1 row in set (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.02 sec)

线程3:依次执行 begin;select/update/delete/select,但不commit

返回结果:

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from dboopuser;
+--------+-----+-------------------+------------------------------------------------------+
| userid | age | username          | userimg                                              |
+--------+-----+-------------------+------------------------------------------------------+
|   9527 |  25 | cccccccccc        | http://www.dboop.com/img/user/2002_innodbtrx_527.jpg |
|   9528 |  35 | dddddddddddddd    | http://www.dboop.com/img/user/2002_innodbtrx_528.jpg |
|   9529 |  25 | eeeeeeeeeeeeeeeee | http://www.dboop.com/img/user/2002_innodbtrx_529.jpg |
|   9530 |  26 | zhangsan01        | http://www.dboop.com/img/user/2002_innodbtrx_530.jpg |
|   9531 |  27 | zhangsan02        | http://www.dboop.com/img/user/2002_innodbtrx_531.jpg |
|   9532 |  28 | zhangsan03        | http://www.dboop.com/img/user/2002_innodbtrx_532.jpg |
|   9533 |  29 | zhangsan04        | http://www.dboop.com/img/user/2002_innodbtrx_533.jpg |
|   9534 |  29 | zhangsan05        | http://www.dboop.com/img/user/2002_innodbtrx_534.jpg |
|   9535 |  29 | zhangsan06        | http://www.dboop.com/img/user/2002_innodbtrx_535.jpg |
+--------+-----+-------------------+------------------------------------------------------+
9 rows in set (0.00 sec)

mysql> update dboopuser set username='aaaaaa' where userid=9529;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> delete from dboopuser where userid=9532;
Query OK, 1 row affected (0.00 sec)

mysql> select * from dboopuser;
+--------+-----+----------------+------------------------------------------------------+
| userid | age | username       | userimg                                              |
+--------+-----+----------------+------------------------------------------------------+
|   9527 |  25 | cccccccccc     | http://www.dboop.com/img/user/2002_innodbtrx_527.jpg |
|   9528 |  35 | dddddddddddddd | http://www.dboop.com/img/user/2002_innodbtrx_528.jpg |
|   9529 |  25 | aaaaaa         | http://www.dboop.com/img/user/2002_innodbtrx_529.jpg |
|   9530 |  26 | zhangsan01     | http://www.dboop.com/img/user/2002_innodbtrx_530.jpg |
|   9531 |  27 | zhangsan02     | http://www.dboop.com/img/user/2002_innodbtrx_531.jpg |
|   9533 |  29 | zhangsan04     | http://www.dboop.com/img/user/2002_innodbtrx_533.jpg |
|   9534 |  29 | zhangsan05     | http://www.dboop.com/img/user/2002_innodbtrx_534.jpg |
|   9535 |  29 | zhangsan06     | http://www.dboop.com/img/user/2002_innodbtrx_535.jpg |
+--------+-----+----------------+------------------------------------------------------+
8 rows in set (0.00 sec)

线程1:再次执行select * from dboopuser;

返回结果:


mysql> select * from dboopuser;
+--------+-----+-------------------+------------------------------------------------------+
| userid | age | username          | userimg                                              |
+--------+-----+-------------------+------------------------------------------------------+
|   9527 |  25 | cccccccccc        | http://www.dboop.com/img/user/2002_innodbtrx_527.jpg |
|   9528 |  15 | dddddddddddddd    | http://www.dboop.com/img/user/2002_innodbtrx_528.jpg |
|   9529 |  25 | eeeeeeeeeeeeeeeee | http://www.dboop.com/img/user/2002_innodbtrx_529.jpg |
|   9530 |  26 | zhangsan01        | http://www.dboop.com/img/user/2002_innodbtrx_530.jpg |
|   9531 |  27 | zhangsan02        | http://www.dboop.com/img/user/2002_innodbtrx_531.jpg |
|   9532 |  28 | zhangsan03        | http://www.dboop.com/img/user/2002_innodbtrx_532.jpg |
|   9533 |  29 | zhangsan04        | http://www.dboop.com/img/user/2002_innodbtrx_533.jpg |
|   9534 |  29 | zhangsan05        | http://www.dboop.com/img/user/2002_innodbtrx_534.jpg |
|   9535 |  29 | zhangsan06        | http://www.dboop.com/img/user/2002_innodbtrx_535.jpg |
+--------+-----+-------------------+------------------------------------------------------+
9 rows in set (0.00 sec)

真实存储分析

图解:

innodb_mvcc05

数据变化

Mvcc原理实现逻辑

为什么事务1,可以一直读取到第一次读到的原始表?

为什么事务3,可以读取到事务2的数据?

如果事务3,最后rollback了,会怎样?

如图所示,事备3回滚以后,数据又回到事务3开始前的状态(一模一样),这也是undolog的一个重要做用

innodb_mvcc06

>> Home

51ak

2022/07/28

Categories: mysql undolog 事务 Tags: 原创 精品

《数据库工作笔记》公众号
扫描上面的二维码,关注我的《数据库工作笔记》公众号