mysql的多版本并发控制

→_→洋洋

已于 2022-03-20 19:58:18 修改

阅读量1k

点赞数

分类专栏： mysql 事务 MVCC(多版本并发控制) 文章标签： mysql

于 2022-03-20 18:31:03 首次发布

原文链接：https://www.bilibili.com/video/BV1iq4y1u7vj?p=186&spm_id_from=pageDriver

版权

mysql 同时被 3 个专栏收录

5 篇文章 0 订阅

订阅专栏

事务

2 篇文章 0 订阅

订阅专栏

MVCC(多版本并发控制)

1 篇文章 0 订阅

订阅专栏

一、多版本并发控制概念

多版本并发控制（MVCC）是通过对数据行的多个版本管理来实现数据库的并发控制，是一种用来 解决读-写冲突的无锁并发控制。

二、快照读与当前读

1. 快照读

读取的是快照数据，不加锁的select都属于快照读。

2.当前读

读取的是最新数据，加锁的select，或者对数据进行增删改都会进行当前读。

三、MVCC原理的三个核心

1.隐藏字段

2.Undo LOG（回滚日志）版本链

对于使用 InnoDB 存储引擎的表来说，它的聚簇索引记录中都包含两个必要的隐藏列

trx_id：事务id，通过事务id的大小判断事务的时间顺序。
roll_pointer：回滚指针，指向当前行记录的上一个版本，通过这个指针将数据的多个版本连接在一起构成undo log版本链。

3.ReadView

MVCC只针对READ COMMITTED和REPEATEABLE READ隔离级别级别的事务，必须保证读到已经提交了的事务修改过的记录。

（1）ReadView 中的四个重要内容

creator_trx_id ，创建这个 Read View 的事务 ID。
- 只有在对表中的记录做改动时（执行INSERT、DELETE、UPDATE这些语句时）才会为
  事务分配事务id，否则在一个只读事务中的事务id值都默认为0。
trx_ids ，表示在生成ReadView时当前系统中活跃的读写事务的事务id列表。
up_limit_id ，活跃的事务中最小的事务 ID。
low_limit_id ，表示生成ReadView时系统中应该分配给下一个事务的 id 值。low_limit_id 是系
统最大的事务id值+1，这里要注意是系统中的事务id，需要区别于正在活跃的事务ID。
- low_limit_id并不是trx_ids中的最大值，事务id是递增分配的。比如，现在有id为1，
  2，3这三个事务，之后id为3的事务提交了。那么一个新的读事务在生成ReadView时，
  trx_ids就包括1和2，up_limit_id的值就是1，low_limit_id的值就是4。

（2）READ COMMITTED隔离级别下举例说明

READ COMMITTED ：每次读取数据前都生成一个ReadView，即生成新的trx_ids。

案例：

① 现在有两个事务id 分别为 10 、 20 的事务在执行：

# Transaction 10
BEGIN;
UPDATE student SET name="李四" WHERE id=1;
UPDATE student SET name="王五" WHERE id=1;
# Transaction 20
BEGIN;
# 更新了一些别的表的记录
...

② 此时表student中id=1的版本连接入下

③ 假设现在有一个使用 READ COMMITTED 隔离级别的事务开始执行：

# 使用READ COMMITTED隔离级别的事务
BEGIN;
# SELECT1：Transaction 10、20未提交
SELECT * FROM student WHERE id = 1; # 得到的列name的值为'张三'

此时creator_trx_id：

creator_trx_id（事务 ID）	0
trx_ids（事务id列表）	{10,20}
up_limit_id（活跃的事务中最小的事务 ID）	10
low_limit_id（系统最大的事务id值+1）	20+1=21

结合分析②中版本连接分析
- trx_id = 10,10在[10,20]里，代表事务还未提交，所以数据读取不到
- trx_id = 8, 8不在[10,20]里，代表事务已经提交，所以数据可以读取到。
- 所以读取到的是name=“张三”

④ 事务id = 10 的事务提交一下：

# Transaction 10
BEGIN;
UPDATE student SET name="李四" WHERE id=1;
UPDATE student SET name="王五" WHERE id=1;
COMMIT;

⑤ 然后再到事务id 为 20 的事务中更新一下表 student 中 id 为 1 的记录：

# Transaction 20
BEGIN;
# 更新了一些别的表的记录
...
UPDATE student SET name="钱七" WHERE id=1;
UPDATE student SET name="宋八" WHERE id=1;

⑥ 此刻，表student中 id 为 1 的记录的版本链就长这样：

⑦ 然后再到刚才使用 READ COMMITTED 隔离级别的事务中继续查找这个 id 为 1 的记录，如下：

# 使用READ COMMITTED隔离级别的事务
BEGIN;
# SELECT1：Transaction 10、20均未提交
SELECT * FROM student WHERE id = 1; # 得到的列name的值为'张三'
# SELECT2：Transaction 10提交，Transaction 20未提交
SELECT * FROM student WHERE id = 1; # 得到的列name的值为'王五'

此时creator_trx_id：

creator_trx_id（事务 ID）	0
trx_ids（事务id列表）	{20}
up_limit_id（活跃的事务中最小的事务 ID）	20
low_limit_id（系统最大的事务id值+1）	20+1=21

结合分析⑥中版本连接分析
- trx_id = 20,20在[20]里，代表事务还未提交，所以数据读取不到
- trx_id = 10, 10不在[20]里，代表事务已经提交，所以数据可以读取到。
- 所以读取到的是name=“王五”

（3）REPEATABLE READ隔离级别下

只会在第一次执行查询语句时生成一个 ReadView ，之后的查询就不会重复生成了，即trx_ids不变。

案例：

① 现在有两个事务id 分别为 10 、 20 的事务在执行：

# Transaction 10
BEGIN;
UPDATE student SET name="李四" WHERE id=1;
UPDATE student SET name="王五" WHERE id=1;
# Transaction 20
BEGIN;
# 更新了一些别的表的记录
...

② 此时表student中id=1的版本连接入下

③ 假设现在有一个使用 READ COMMITTED 隔离级别的事务开始执行：

# 使用READ COMMITTED隔离级别的事务
BEGIN;
# SELECT1：Transaction 10、20未提交
SELECT * FROM student WHERE id = 1; # 得到的列name的值为'张三'

此时creator_trx_id：

creator_trx_id（事务 ID）	0
trx_ids（事务id列表）	{10,20}
up_limit_id（活跃的事务中最小的事务 ID）	10
low_limit_id（系统最大的事务id值+1）	20+1=21

结合分析②中版本连接分析
- trx_id = 10,10在[10,20]里，代表事务还未提交，所以数据读取不到
- trx_id = 8, 8不在[10,20]里，代表事务已经提交，所以数据可以读取到。
- 所以读取到的是name=“张三”

④ 事务id = 10 的事务提交一下：

# Transaction 10
BEGIN;
UPDATE student SET name="李四" WHERE id=1;
UPDATE student SET name="王五" WHERE id=1;
COMMIT;

⑤ 然后再到事务id 为 20 的事务中更新一下表 student 中 id 为 1 的记录：

# Transaction 20
BEGIN;
# 更新了一些别的表的记录
...
UPDATE student SET name="钱七" WHERE id=1;
UPDATE student SET name="宋八" WHERE id=1;

⑥ 此刻，表student中 id 为 1 的记录的版本链就长这样：

⑦ 然后再到刚才使用 READ COMMITTED 隔离级别的事务中继续查找这个 id 为 1 的记录，如下：

# 使用READ COMMITTED隔离级别的事务
BEGIN;
# SELECT1：Transaction 10、20均未提交
SELECT * FROM student WHERE id = 1; # 得到的列name的值为'张三'
# SELECT2：Transaction 10提交，Transaction 20未提交
SELECT * FROM student WHERE id = 1; # 得到的列name的值为'王五'

此时creator_trx_id：

creator_trx_id（事务 ID）	0
trx_ids（事务id列表）	{10，20}
up_limit_id（活跃的事务中最小的事务 ID）	10
low_limit_id（系统最大的事务id值+1）	20+1=21

结合分析⑥中版本连接分析
- trx_id = 20,20在[10,20]里，代表事务还未提交，所以数据读取不到
- trx_id = 10,10在[10,20]里，代表事务还未提交，所以数据读取不到
- trx_id = 8, 8不在[10,20]里，代表事务已经提交，所以数据可以读取到。
- 所以读取到的是name=“张三”

（4）InnDB解决幻读---REPEATABLE READ隔离级别下

① 假设现在表 student 中只有一条数据，数据内容中，主键 id=1，隐藏的 trx_id=10，它的 undo log 如下图所示。

② 假设现在有事务 A 和事务 B 并发执行，事务 A 的事务 id 为 20 ，事务 B 的事务 id 为 30
③

所以此时③中的查询结果

⑤ 接着事务 B(trx_id=30)，往表 student 中新插入两条数据，并提交事务。

insert into student(id,name) values(2,'李四');
insert into student(id,name) values(3,'王五');

⑥ 此时表student 中就有三条数据了，对应的 undo 如下图所示：

⑦ 事务 A 开始第二次查询数据，查询的 SQL 语句如下。

select * from student where id >= 1;

⑧ 此时ReadView 的内容如下：

creator_trx_id（事务 ID）	0
trx_ids（事务id列表）	{20，30}
up_limit_id（活跃的事务中最小的事务 ID）	20
low_limit_id（系统最大的事务id值+1）	30+1=31

所以此时⑦中的查询结果

从而避免了mysql中的幻读问题。

尚硅谷视频学习连接：

MySQL数据库教程天花板，mysql安装到mysql高级，强！硬！_哔哩哔哩_bilibili