多版本并发控制(Multi-Version Concurrency Control, MVCC)是 MySQL 的 InnoDB 存储引擎实现隔离级别的一种具体方式,用于实现提交读和可重复读这两种隔离级别。而未提交读隔离级别总是读取最新的数据行,无需使用 MVCC。可串行化隔离级别需要对所有读取的行都加锁,单纯使用 MVCC 无法实现。
MVCC提供并发访问数据库时,对事务内读取的到的内存做处理,用来避免写操作堵塞读操作的并发问题。MVCC可以在大多数情况下代替行级锁,使用MVCC,能降低其系统开销。
一、创建表结构
数据库表创建时,内部的隐藏列ROW_ID(行号)、DB_TRX_ID(事务id)、DB_ROLL_PTR(回滚指针);行号,模拟数据的存在的地址,事务ID,存放事务ID,回滚指针,上次提交数据事务的ID,方便回滚,类似链表的指针,指向上一条数据。
create table mvcc (
-- INNODB 隐藏列
ROW_ID bigint not null auto_increment primary key, -- 行号,模拟指针地址
MY_DB_TRX_ID int not null, -- 事务id
MY_DB_ROLL_PTR bigint, -- 回滚指针
DELETED bit, -- 删除标识
-- 真实字段
id bigint, -- 主键id
name varchar(32) -- 名称
) charset 'utf8', engine 'innodb';
二、初始化数据
insert into mvcc (MY_DB_TRX_ID, MY_DB_ROLL_PTR, DELETED, id, name) values (1, null, null, 1, '用来修改'), (2, null, null, 2, '用来删除'), (3, null, null, 3, 'test');
行号ID模拟,事务ID,处理该条记录的事务的ID,该条数据的事务ID为累加,不可缩减。回滚指针为空,是因为该条数据没有上一次事务,因此回滚指针为空。id、name为用户能够看到的数据。
三、模拟查询数据
举例,当累计查询,修改后,事务ID到目前为11;即MY_DB_TRX_ID = 11;
目标:查询ID = 1的数据
要执行的sql:select * from mvcc where id = 1;
1.实际内部查询的逻辑,查看比当前事务ID小的最近的一条ID = 1的数据,翻译成sql为:
select * from mvcc where id = 1 and MY_DB_TRX_ID <= 11 order by MY_DB_TRX_ID desc limit 1;
查询结果:
四、事务ID = 12修改数据
当前事务ID = 12 ,修改ID = 1的数据
要执行的sql: update mvcc set name = '修改后的数据' where id = 1;
1.查询到满足条件的行
select ROW_ID from mvcc where id = 1 and MY_DB_TRX_ID <= 12 order by MY_DB_TRX_ID desc limit 1
2.复制目标数据
insert into mvcc (MY_DB_TRX_ID, MY_DB_ROLL_PTR, DELETED, id, name) select MY_DB_TRX_ID, MY_DB_ROLL_PTR, DELETED, id, name from mvcc where ROW_ID = 1;
执行结果:
3.修改数据,并将DB_TRX_ID改为当前事务id,将当前行DB_ROLL_PTR指向复制的行
update mvcc set name = '修改后的数据', MY_DB_TRX_ID = 12, MY_DB_ROLL_PTR = 4 where ROW_ID = 1;
执行结果:
4.当事务12查询数据时
执行的思路,翻译为sql为:
select * from mvcc where id = 1 and MY_DB_TRX_ID <= 12 order by MY_DB_TRX_ID desc limit 1;
查到的数据是正常的更新后的数据。id = 1对应的内容是“修改后的数据”;
5.当事务11查询时
select * from mvcc where id = 1 and MY_DB_TRX_ID <= 11 order by MY_DB_TRX_ID desc limit 1;
查询到的是原本的数据id = 1对应的内容为:“用来修改”。保证了可重复读。
五、事务12删除数据
要执行的sql:delete from mvcc where id = 2;
1.1、查询到满足条件的行并复制一份
select ROW_ID from mvcc where id = 2 and MY_DB_TRX_ID <= 12 and DELETED is null order by MY_DB_TRX_ID desc limit 1;
insert into mvcc (MY_DB_TRX_ID, MY_DB_ROLL_PTR, DELETED, id, name)
select MY_DB_TRX_ID, MY_DB_ROLL_PTR, DELETED, id, name
from mvcc where ROW_ID = 2; -- 5
执行结果:
2.当前行打上删除标记,并将DB_TRX_ID改为当前事务id,将当前行DB_ROLL_PTR指向复制的行
update mvcc set DELETED = true, MY_DB_TRX_ID = 12, MY_DB_ROLL_PTR = 5 where ROW_ID = 2;
执行结果:
3.事务12查询
select * from mvcc where id = 2 and MY_DB_TRX_ID <= 12 order by MY_DB_TRX_ID desc limit 1;
执行结果:已删除数据
4.事务11查询
select * from mvcc where id = 2 and MY_DB_TRX_ID <= 11 order by MY_DB_TRX_ID desc limit 1;
执行结果:正常查看
保证了数据可重复读的特性。
六.事务12新增数据
要执行的sql:insert into mvcc(name) values ('新增的数据');
1.获取自增id(4) 并插入数据
insert into mvcc(my_db_trx_id, my_db_roll_ptr, deleted, id, name) values (12, null, null, 4, '新增的数据');
执行结果:
2.事务12查询数据
select * from mvcc where id = 4 and MY_DB_TRX_ID <= 12 order by MY_DB_TRX_ID desc limit 1;
事务12本身查询结果:
3.事务11查询数据
select * from mvcc where id = 4 and MY_DB_TRX_ID <= 11 order by MY_DB_TRX_ID desc limit 1; 事务11查询结果:
查询不到新增的数据。
疑问:事务12启动后添加了一条数据,事务13启动,为什么事务13看不到事务12新增的数据?
具体当前事务或被该事务id使用快照,记录起来,在查询时,会根据自身当前事务ID,对比快照中,该数据的最大,最小事务ID,判断是否在事务进程中,如果事务进程中,会根据回滚ID,查询上次修改记录,直至查询快照中不含该事务ID,从而返回已提交事务的ID。