第 21 章 一条记录的多幅面孔——事务的隔离级别与 MVCC

于 2024-10-02 22:25:35 发布
阅读量369 收藏 2
点赞数 17
分类专栏： MySQL是怎样运行的 文章标签： mysql 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_45740473/article/details/142686122
版权

21.1 事前准备

CREATE TABLE hero ( 
    number INT, 
    NAME VARCHAR ( 100 ), 
    country VARCHAR ( 100 ), 
    PRIMARY KEY ( number ) 
) ENGINE = INNODB CHARSET = utf8;

INSERT INTO hero VALUES ( 1, '刘备', '蜀' );

21.2 事务隔离级别

在保证事务隔离性的前提下，使用不同的隔离级别，来尽量提高多个事务访问同一数据的性能。

21.2.1 事务并发执行遇到的问题
  1. 脏写（Dirty Write）：一个事务修改了另一个未提交事务修改过的数据。
  2. 脏读（Dirty Read）：一个事务读到了另一个未提交事务修改过的数据。
  3. 不可重复读（Non-Repeatable Read）：一个事务只能读到另一个已经提交的事务修改过的数据，并且其他事务每对该数据进行一次修改并提交，该事务都能查询得到最新值。
  4. 幻读（Phantom）：一个事务先根据某些条件查询出一些记录，之后另一个事务又向表中插入了符合这些条件的记录，原先的事务再次查询时，能把另一个事务插入的记录也读出来。
21.2.2 SQL 标准中的四种隔离级别

按严重性：

脏写 > 脏读 > 不可重复读 > 幻读

隔离级别中文名脏读不可重复读幻读
READ UNCOMMITTED读未提交PossiblePossiblePossible
READ COMMITTED读已提交Not PossiblePossiblePossible
REPAEATABLE READ可重复读Not PossibleNot PossiblePossible
SERIALIZABLE串行化Not PossibleNot PossibleNot Possible

不论哪种隔离级别，都不允许脏写的情况发生。

21.2.3 MySQL 中支持的四种隔离级别

MySQL 的默认隔离级别为 REPEATABLE READ ，我们可以手动修改一下事务的隔离级别。

# 如何设置事务的隔离级别
# SET [GLOBAL|SESSION] TRANSACTION ISOLATION LEVEL level;
SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE;

#  查看当前会话默认的隔离级别
SHOW VARIABLES LIKE 'transaction_isolation';

21.3 MVCC 原理

21.3.1 版本链

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

  1. trx_id：每次一个事务对某条聚簇索引记录进行改动时，都会把该事务的事务id赋值给 trx_id 隐藏列。
  2. roll_pointer：每次对某条聚簇索引记录进行改动时，都会把旧的版本写入到 undo log 中，然后这个隐藏列就相当于一个指针，可以通过它来找到该记录修改前的信息。

在这里插入图片描述

Tips：实际上 insert undo 只在事务回滚时起作用，当事务提交后，该类型的 undo log 就没用了。

假设之后有两个事务 id 分别为100和200的事务对这条记录进行 UPDATE 操作：

在这里插入图片描述

对该记录每次更新后，都会将旧值放到一条 undo log 中，就算是该记录的一个旧版本，随着更新次数的增多，所有的版本都会被 roll_pointer 属性连接成一个链表，我们把这个链表称为 版本链，它的头节点就是当前记录最新的值，另外，每个版本还包含生成该版本时对应的事务 id。

在这里插入图片描述

21.3.2 ReadView
  1. 对于 READ UNCOMMITTED 级别的事务来说，直接读取记录的最新版本；
  2. 对于 SERIALIZABLE 级别的事务来说，使用加锁的方式来访问；
  3. 对于 READ COMMITED 和 REPAEATABLE READ 级别的事务来说，必须保证讲到已经提交了的事务修改过的记录，而不能直接读取最新版本版本的记录。那么怎么判断版本链中哪个版本是当前事务可见的？于是便有了 ReadView 的概念。

ReadView 主要包括以下4个重点：

  1. m_ids：表示在生成 ReadView 时当前系统中活跃的读写事务的**事务 id **列表
  2. min_trx_id：表示在生成 ReadView 时当前系统中活跃的读写事务中最小的**事务 id **，也就是 m_ids 中的最小值。
  3. max_trx_id：表示生成 ReadView 时系统中应该分配给下一个事务的 id 值。
  4. creator_trx_id：表示生成该 ReadView 的事务的 事务 id。

TIPS：只有在对表中的记录做改动时才会为事务分配事务 id，否则在一个只读事务中的事务 id 都默认为 0。

通过 ReadView 就可以判断访问某条记录时，某个版本是否可见：

  1. 如果被访问版本的 trx_id 与 ReadView 中的 creator_trx_id 相同，意味着当前事务在访问它自己修改过的记录，所以该版本可以被当前事务访问。
  2. 如果被访问版本的 trx_id 小于 ReadView 中的 min_trx_id，表明生成该版本的事务在当前事务生成 ReadView 前已经提交，所以该版本可以被当前事务访问。
  3. 如果被访问版本的 trx_id 大于 ReadView 中的 max_trx_id，表明生成该版本的事务在当前事务生成 ReadView 后才开启，所以该版本不可以被当前事务访问。
  4. 如果被访问版本的 trx_id 属性值在 ReadView 的 min_trx_id 和 max_trx_id 之间，那就需要判断一下 trx_id 是不是在 m_ids 列表中，如果在，说明创建 ReadView 时生成该版本的事务还是活跃的，该版本不可以被访问；如果不在，说明创建 ReadView 时生成该版本的事务已经被提交，该版本可以被访问。
public boolean isShow(long trx_id, long creator_trx_id, long min_trx_id, long max_trx_id, List<Long> m_ids) {
        if (trx_id == creator_trx_id) {
            return true;
        }
        if (trx_id < min_trx_id) {
            return true;
        }
        if (trx_id > max_trx_id) {
            return false;
        }
        return !m_ids.contains(trx_id);
    }

如果某个版本的数据对当前事务不可见的话，那就顺着版本链找到下一个版本，直到最后一个版本。

READ COMMITTED 和 REPEATABLE READ 生成 ReadView 的时机不同。

21.3.2.1 READ COMMITTED——每次读取数据前都生成一个ReadView
# Transaction 100
BEGIN;
UPDATE hero SET name = '关羽' WHERE number = 1;
UPDATE hero SET name = '张飞' WHERE number = 1;

# Transaction 200
BEGIN;

# 更新了一些别的表的记录
...

此时 number=1 的记录版本链：

在这里插入图片描述

使用 READ COMMITED 隔离级别的事务开始执行查询：

# 使用READ COMMITTED隔离级别的事务
BEGIN;

# SELECT1：Transaction 100、200未提交
SELECT * FROM hero WHERE number = 1; # 得到的列name的值为'刘备'

SELECT1 的执行过程如下：

  1. 在执行 SELECT 语句时会先生成一个 ReadView：

    {
	m_ids:[100, 200],
    min_trx_id: 100,
    max_trx_id: 201,
    creator_trx_id: 0
}

  2. 然后从版本链中挑选可见的记录，从图中可以看出，最新版本的列 name = ‘张飞’ ，trx_id = 100 ，在 m_ids 列表内，所以不符合可见性要求，根据 roll_pointer 跳到下一个版本。

  3. 下一个版本的列 name = ‘关羽’ ，trx_id = 100 ，也在 m_ids 列表内，所以也不符合要求，继续跳到下一个版本。

  4. 下一个版本的列 name = ‘刘备’ ，trx_id = 80 ，小于 ReadView 中的 min_trx_id 值100 ，所以这个版本是符合要求的，最后返回给用户的版本就是这条列 name 为 ‘刘备’ 的记录。

之后，把事务id=100的事务提交，并使用事务id=200的事务继续更新

# Transaction 100
BEGIN;
UPDATE hero SET name = '关羽' WHERE number = 1;
UPDATE hero SET name = '张飞' WHERE number = 1;
COMMIT;

# Transaction 200
BEGIN;

# 更新了一些别的表的记录
...

UPDATE hero SET name = '赵云' WHERE number = 1;
UPDATE hero SET name = '诸葛亮' WHERE number = 1;

此时 number=1 的记录版本链：

在这里插入图片描述

再次使用 READ COMMITED 隔离级别的事务开始执行查询：

# 使用READ COMMITTED隔离级别的事务
BEGIN;

# SELECT1：Transaction 100、200均未提交
SELECT * FROM hero WHERE number = 1; # 得到的列name的值为'刘备'

# SELECT2：Transaction 100提交，Transaction 200未提交
SELECT * FROM hero WHERE number = 1; # 得到的列name的值为'张飞

SELECT2 的执行过程如下：

  1. 在执行 SELECT 语句时会先生成一个 ReadView：

    {
	m_ids:[200],
    min_trx_id: 200,
    max_trx_id: 201,
    creator_trx_id: 0
}

  2. 然后从版本链中挑选可见的记录，从图中可以看出，最新版本的列 name = ‘诸葛亮’ ，trx_id = 200 ，在 m_ids 列表内，所以不符合可见性要求，根据 roll_pointer 跳到下一个版本。

  3. 下一个版本的列 name = ‘赵云’ ，trx_id = 200 ，也在 m_ids 列表内，所以也不符合要求，继续跳到下一个版本。

  4. 下一个版本的列 name = ‘张飞’ ，trx_id = 100 ，小于 ReadView 中的 min_trx_id 值200 ，所以这个版本是符合要求的，最后返回给用户的版本就是这条列 name 为 ‘张飞’ 的记录

每次 SELECT 查询都可以读到最新已提交的记录，这就是读已提交

21.3.2.2 REPAEATABLE READ——在第一次读取数据时生成一个ReadView
# Transaction 100
BEGIN;
UPDATE hero SET name = '关羽' WHERE number = 1;
UPDATE hero SET name = '张飞' WHERE number = 1;

# Transaction 200
BEGIN;

# 更新了一些别的表的记录
...

此时 number=1 的记录版本链：

在这里插入图片描述

使用 REPAEATABLE READ 隔离级别的事务开始执行查询：

# 使用REPEATABLE READ隔离级别的事务
BEGIN;

# SELECT1：Transaction 100、200未提交
SELECT * FROM hero WHERE number = 1; # 得到的列name的值为'刘备

SELECT1 的执行过程如下：

  1. 在执行 SELECT 语句时会先生成一个 ReadView：

    {
	m_ids:[100, 200],
    min_trx_id: 100,
    max_trx_id: 201,
    creator_trx_id: 0
}

  2. 然后从版本链中挑选可见的记录，从图中可以看出，最新版本的列 name = ‘张飞’ ，trx_id = 100 ，在 m_ids 列表内，所以不符合可见性要求，根据 roll_pointer 跳到下一个版本。

  3. 下一个版本的列 name = ‘关羽’ ，该版本的 trx_id = 100 ，也在 m_ids 列表内，所以也不符合要求，继续跳到下一个版本。

  4. 下一个版本的列 name = ‘刘备’ ，该版本的 trx_id = 80 ，小于 ReadView 中的 min_trx_id 值100 ，所以这个版本是符合要求的，最后返回给用户的版本就是这条列 name 为 ‘刘备’ 的记录。

之后，把事务id=100的事务提交，并使用事务id=200的事务继续更新

# Transaction 100
BEGIN;
UPDATE hero SET name = '关羽' WHERE number = 1;
UPDATE hero SET name = '张飞' WHERE number = 1;
COMMIT;

# Transaction 200
BEGIN;

# 更新了一些别的表的记录
...

UPDATE hero SET name = '赵云' WHERE number = 1;
UPDATE hero SET name = '诸葛亮' WHERE number = 1;

此时 number=1 的记录版本链：

在这里插入图片描述

再次使用 REPAEATABLE READ 隔离级别的事务开始执行查询：

# 使用 REPEATABLE READ 隔离级别的事务
BEGIN;

# SELECT1：Transaction 100、200均未提交
SELECT * FROM hero WHERE number = 1; # 得到的列name的值为'刘备'

# SELECT2：Transaction 100提交，Transaction 200未提交
SELECT * FROM hero WHERE number = 1; # 得到的列name的值仍为'刘备

SELECT2 的执行过程如下：

  1. 使用 REPEATABLE READ 隔离级别时，因为之前执行 SELECT1 时已经生成过 ReadView 了，所以此时不再重新生成，而是直接复用之前的 ReadView

    {
	m_ids:[100, 200],
    min_trx_id: 100,
    max_trx_id: 201,
    creator_trx_id: 0
}

  2. 然后从版本链中挑选可见的记录，从图中可以看出，最新版本的列 name = ‘诸葛亮’ ，trx_id = 200 ，在 m_ids 列表内，所以不符合可见性要求，根据 roll_pointer 跳到下一个版本。

  3. 下一个版本 name = ‘赵云’ ，trx_id = 200 ，也在 m_ids 列表内，所以也不符合要求，继续跳到下一个版本。

  4. 下一个版本的列 name = ‘张飞’ ，trx_id = 100 ，也在 m_ids 列表内，所以也不符合要求，同理下一个列 name 的内容是 ‘关羽’ 的版本也不符合要求。继续跳到下一个版本。

  5. 下一个版本的列 name = ‘刘备’ ，trx_id = 80 ，小于 ReadView 中的 min_trx_id 值
    100 ，所以这个版本是符合要求的，最后返回给用户的版本就是这条列 c 为 ‘刘备’ 的记录。

两次 SELECT 查询得到的结果都是相同的，这就是所谓的可重复读。

21.3.3 二级索引与MVCC

前面使用的 trx_id 和 roll_pointer 隐藏列都只存在于聚簇索引，当使用二级索引查询时，该如何判断可见性？

BEGIN;

SELECT name FROM hero WHERE name = '刘备';
  1. 二级索引页面的 Page Header 部分有一个名为 PAGE_MAX_TRX_ID 的属性，每次有事务增删改本页面中的记录时，如果该事务的 id 大于 PAGE_MAX_TRX_ID ，就把这个 id 赋值给 PAGE_MAX_TRX_ID。这也就意味 PAGE_MAX_TRX_ID 是修改该二级索引页面的最大事务 id。
  2. 当 SELECT 语句访问某个二级索引记录时，如果对应的 ReadView 的 min_trx_id 大于该页面的 PAGE_MAX_TRX_ID，说明该页面的所有记录都对该 ReadView 可见，否则执行步骤 3。
  3. 利用二级索引进行回表，得到对应的聚簇索引记录后按照前面说的方式判断是否可见。
21.3.4 MVCC 小结
  1. 所谓 MVCC（Multi-Version Concurrency Control，多版本并发控制）指的就是在使用 READ COMMITED、REPEATABLE READ 这两种隔离级别的事务在执行普通的 SELECT 操作时访问记录的版本链的过程，这样子可以使不同事务的读-写、写-读操作并发执行，从而提升系统性能。
  2. READ COMMITED、REPEATABLE READ 这两种隔离级别主要不同在于生成 ReadView 的时机。
  3. 只有在进行普通 SELECT 查询时，MVCC 才生效。

21.4 关于 purge

在合适的时候把 update undo 日志以及仅仅被标记为删除的记录彻底删除掉，这个删除操作就称为 purge。

21.5 总结

  1. 并发的事务在运行过程中会出现一些可能的引发一致性问题
  2. SQL 标准中有4种隔离级别
  3. 版本链
  4. ReadView
沉登c
关注
专栏目录
事务隔离级别和MVCC
super_dmz的博客
10-14 317
前言 本博客主要是对《MySQL是怎样运行的》一书的内容进行整理 第21章 事务隔离级别和MVCC 我们希望让不同的事务“隔离”地执行，互不干涉。这也就是事务的隔离性。 串行执行过于严格，会严重降低系统吞吐量和资源利用率，增加事务的等待时间，因此需要采用其他机制来保证并发执行的事务的执行结果与串行执行的结果一样。 两个并发的事务在执行过程中访问相同数据的情况有四种：读——读，读——写，写——读，——写——写。只有在至少一个事务对数据进行写操作时，才可能带来一致性问题。 这一致性通常通过加锁来
MySQL事务隔离级别及MVCC详解
Counter-Strike大牛
03-19 1091
C事务读id为1的数据，由于是可重复读隔离级别，所以不再生成新的ReadView，还是用之前的，m_ids=[100, 200]，min_trx_id=100, max_trx_id=201，creator_trx_id=0。C事务读id为1的数据，再次生成ReadView，m_ids=[200]，min_trx_id=200，max_trx_id=201，creator_trx_id=0。ReadView中有m_ids、min_trx_id、max_trx_id、creator_trx_id。
MySQL读取的记录和我想象的不一致——事物隔离级别和MVCC
卓越无关环境，保持空杯心态——靡不有初，鲜克有终
11-25 1840
并发的事务在运行过程中会出现一些可能引发一致性问题的现象，本篇将详细分析一下。
「mysql是怎样运行的」第24章 一条记录的多幅面孔---事务的隔离级别与MVCC
frozenpenguin的博客
03-01 1191
我们知道 MySQL 是一个 客户端/服务器 架构的软件，对于同一个服务器来说，可以有若干个客户端与之连接， 每个客户端与服务器连接上之后，就可以称之为一个会话( Session )。
事务隔离级别与MVCC（看这一篇就够了）
qq_45726143的博客
03-12 2339
一、事务简介 我们在说起事务的时候，经常拿银行转账的例子，因为这个例子非常经典，A 向 B 转账5元，先获取 A 判断如果账户大于5元，则从 A 账户上减去 5 元，在获取 B 账户加上 5 元。并且保证该过程永远不能出错不会出错。 1.1 事务四大特性（ACID） 原子性（Atomicity）：数据库把“要么全做，要么全部做”的这种规则称为原子性 隔离性（Isolation）：事务之间相互隔离，不受影响，这与事务的隔离级别密切相关 一致性（Consistency）：事务执行前后的状态要一致，可理解为数据
MySQL事务隔离级别&MVCC原理
记录各种Bug解决案例及日常学习内容
04-13 1227
MySQL事务隔离级别&MVCC原理 目录MySQL事务隔离级别&MVCC原理一、MySQL事务隔离级别1. 脏读2. 可重复读3.不可重复度4. 幻读5. 事务隔离级别案例分析二、什么是MVCC三、什么是多版本控制四、什么事当前读和快照读1. 当前读2. 快照读五、MVCC的实现原理六、UndoLog实现原理1. insert undo log2. update undo log3. UndoLog版本链3.1 插入一条数据3.2 第一次修改数据3.3 第二次修改数据3.4 第三次修改数据
Mysql-事务（隔离级别，事务底层原理，MVCC）
qq_43259860的博客
01-29 1036
事务：事务指的是逻辑上的一组操作，组成这组操作的各个单元要么全都成功，要么全都失败。原子性（Atomicity）： 原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。一致性（Consistency）：事务前后数据的完整性必须保持一致隔离性（Isolation）：多个用户并发访问数据库时，一个用户的事务不能被其它用户的事务所干扰，多个并发事务之间数据要相互隔离。隔离性由隔离级别保障。
MySQL是怎样运行的：从根儿上理解MySQL | 一条记录的多幅面孔-事务的隔离级别与 MVCC
光看不点赞的博客
03-28 1377
文章目录前言：本博文是对MySQL是怎样运行的：从根儿上理解MySQL这本书的归纳和总结第24章 一条记录的多幅面孔-事务的隔离级别与 MVCC1.事前准备2.事务隔离级别2.1 事务并发执行遇到的问题2.2 SQL标准中的四种隔离级别2.3 MySQL中支持的四种事务隔离级别2.3.1 如何设置事务的隔离级别3. MVCC原理3.1 版本链3.1.1 回顾两个必要隐藏列（`trx_id、roll_pointer`）3.1.2 通过更新操作引入版本链3.2 ReadView3.2.1 判断记录某个版本是否可
事务隔离级别和MVCC的关系
李长渊的博客
11-19 1145
事务隔离级别和MVCC的关系
MySQL学习笔记（一）数据库事务隔离级别与多版本并发控制（MVCC）
飞！！！的博客
03-05 1240
多版本并发控制英文全称是 Multiversion Concurrency Control，简称 MVCC。MVCC是通过保存数据行的历史版本，组成版本链，对比事务ID与版本号来确定当前事务应该使用哪个历史版本数据，而无需加锁就可以保证事务的隔离效果，可以认为是一种不加锁的行锁，可以提高数据库的性能。MySQL的大多数事务型存储引擎实现的都不是简单的行级锁。基于提升并发性能的考虑，它们一般都同时实现了多版本并发控制（MVCC）。
事务的隔离级别与MVCC.doc
07-08
事务的隔离级别与MVCC 事务是数据库中的一种逻辑工作单元，它由多个数据库操作组成，保证了数据库的一致性和可靠性。事务的隔离级别是指数据库系统中多个事务并发执行时，如何避免相互干扰和保证数据的一致性。 ...
关于Mysql隔离级别、锁与MVCC介绍
09-09
MySQL数据库在处理并发事务时，采用了多种机制来确保数据的一致性和隔离性，其中关键的概念包括隔离级别、锁以及多版本并发控制（MVCC）。理解这些机制有助于优化数据库性能和解决并发冲突。 首先，隔离级别是...
【mysql相关总结】
Charles D
09-30 227
数据库小的表,全表扫描效率更高，不用建索引。索引的类型1.普通索引：基本的索引，没有任何约束限制2.唯一索引：类似普通索引,有唯一约束性3.主键索引：特殊的唯一索引,不允许有空值4.组合索引：多个列组合创建索引5.外键索引：只有innodb可以使用6.全文索引：只有myisam可以使用，且只能对英文进行全文检索注意事项1.复合索引遵循前缀原则（最左匹配原则）2.like查询,%号不能在前面，否则索引失效3.如果or前面的条件有索引,也会失效。
docker容器mysql数据备份 mysql容器无法启动备份数据
zhangxu1998lq的博客
09-30 305
mysql是旧容器名 因为mysql的配置文件my.cnf的路径没有映射到服务器，我修改了配置文件，配置文件有错误导致，容器启动不了，一直restarting。nmysql是新容器名 mysql是旧容器名 mysql:5.7是我的镜像名。新容器正常启动，旧容器的数据都还在。
滚雪球学MySQL[1.3讲]：MySQL客户端工具：详解与实践
最新发布
**My Coding Family**
10-01 1153
在上期文章中，我们深入探讨了MySQL的安装与配置，这为我们顺利使用MySQL打下了基础。具体来说，我们介绍了如何正确安装MySQL、配置环境变量、调整字符集，并确保数据库可以正常运行。这些基础配置是后续操作的前提，理解并掌握这些技能能够帮助你在未来的开发工作中减少不必要的麻烦。然而，安装与配置仅仅是迈出了使用MySQL的第一步。真正的工作是如何高效地与数据库交互，并对数据进行增删改查。这时候就需要借助强大的MySQL客户端工具。
MySQL 基础语法详解
weixin_64178283的博客
09-25 1350
DDL 用于定义数据库和表结构，是数据库操作的基础。1. CREATE: 创建数据库或数据表CREATE DATABASE 数据库名;CREATE TABLE 表名 (列名1 数据类型 约束,列名2 数据类型 约束,...USE school;-- 选择数据库id INT PRIMARY KEY AUTO_INCREMENT, -- 学生ID，主键，自增name VARCHAR(255) NOT NULL, -- 学生姓名，非空约束age INT, -- 学生年龄。
基于SpringBoot+Vue+MySQL的考勤管理系统
程序员大金的博客
09-30 689
基于SpringBoot+Vue+MySQL的考勤管理系统，附源码。
hive/impala/mysql几种数据库的sql常用写法和函数说明
biqidaoer的专栏
09-27 371
做大数据开发的时候，会在几种库中来回跳，同一个需求，不同库函数和写法会有出入，在此做汇总沉淀。
Python对数据库（MySQL，redis、MongoDB）的操作
wu73guang5jian的博客
09-27 1464
python对MySQL、Redis，MongoDB的操作
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值