首页 > 数据库 >MySQL读取的记录和我想象的不一致

MySQL读取的记录和我想象的不一致

时间：2023-06-13 11:44:09浏览次数：63  
标签：事务 读取 记录 ReadView trx 版本 MySQL 想象 id

摘要：并发的事务在运行过程中会出现一些可能引发一致性问题的现象，本篇将详细分析一下。

本文分享自华为云社区《MySQL读取的记录和我想象的不一致——事物隔离级别和MVCC》，作者：砖业洋__。

事务的特性简介

1.1 原子性（Atomicity）

要么全做，要么全不做，一系列操作都是不可分割的，如果在执行操作的过程发生了错误，那么就把已经执行的操作恢复成没执行之前的样子。比如转账不能只有一方扣钱另一方不增加余额。

1.2 隔离性（Isolation）

任何其他状态操作不能影响本次状态操作转换，比如A几乎同时向B转2次账，不同的事务读取出的卡上余额都是12元，在第一个事务A-5元后，第二个事务A-5(那这里是12-5还是7-5呢？)，所以MySQL需要一些措施保证这些操作的隔离。

1.3 一致性（Consistency）

如果数据库的数据全部符合现实世界的约束，则这些数据就是一致性的，或者说符合一致性的。

比如余额不能小于0，有一些业务id不能为空。数据库本身能为我们解决一部分一致性需求，比如NOT NULL来拒绝NULL值的插入，但是更多的是需要靠写业务代码的程序员自己保证，比如在Spring Boot里面，入参就可以@NotNull或者@NotBlank之类的来进行入参校验。

数据库检查一致性是一个耗费性能的工作，比如为表建立一个触发器，每当插入或更新记录的时候就会校验是否满足条件，如果涉及到某一些列的计算，就会严重影响插入或更新的速度。

尽量不要把校验参数的判断条件(一致性检查)写在MySQL语句中，不仅影响插入更新的速度，而且数据库连接也是很耗时的。能在业务层面解决就在业务层面判断。

提示：建表时的CHECK子句对于一致性检查没什么用，在MySQL中也不会去检查CHECK子句中的约束是否成立。比如：

create table test (
 id unsigned int not null auto_increment comment ‘主键id’,
 name varchar(100) comment ‘姓名’,
 balance int comment ‘余额’,
 primary key (id),
 check (balance >= 0)
);

1.4 持久性 （Durability）

数据库修改的数据都应该在磁盘中保留下来，无论发生什么事故，本次操作的影响都不应该丢失。比如转账成功后不可以又恢复到没转账之前的样子，那样钱就没了。

我们把这四种特性的首字母提出来加以排序就是一个英文单词：ACID(英文中“酸”的意思)，方便记忆

2. 建表

CREATE TABLE hero (
    number INT,
    name VARCHAR(100),
    country varchar(100),
 PRIMARY KEY (number),
 KEY idx_hero_name (name)
) Engine=InnoDB CHARSET=utf8;

这里把hero表的主键命名为number是为了与后面的事务id进行区分，为了简单，就不写约束条件和注释了。

然后向这个表里插入一条数据：

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

现在表里的数据就是这样的：

3. 事务隔离级别

MySQL是一个客户端／服务器架构的软件，对于同一个服务器来说，可以有若干个客户端与之连接，每个客户端与服务器连接后，就形成了一个会话（Session）。每个客户端都可以在自己的会话中向服务器发出请求语句，一个请求语句可能是某个事务的一部分。服务器可以同时处理来自多个客户端的多个事务。

3.1 事务并发执行时遇到的一致性问题

在不同隔离级别中，对数据库的操作可能会出现几种现象。如下：

3.1.1 脏写（Dirty Write）(用于熟悉和理解ACID特性，实际中不可能存在脏写)

如果一个事务修改了另一个未提交事务修改过的数据，那就意味着发生了脏写。如下：

假设两个会话各开启了一个事务TA和TB，

  • 原有x=0， y=0，TA先修改了x=3，TB修改了x=1，y=1，然后TB提交，最后TA回滚。
    如果TA回滚导致x=0，那么对于TB来说破坏了原子性，因为x被回滚，y还是正常修改。
    如果TA回滚导致TB所有的修改都回滚，那么对于TB来说破坏了持久性，明明TB都提交了，怎么能让一个未提交的TA将TB的持久性破坏掉呢？

无论哪种隔离级别，都不允许脏写的存在，所以脏写也可以作为介绍事务特性的一个序言，了解即可。

3.1.2 脏读（Dirty Read）

如果一个事务读到了另一个未提交事务修改过的数据，那就意味着发生了脏读，示意图如下：

Session A和Session B各开启了一个事务，Session B中的事务先将number列为1的记录的name列更新为’关羽’，然后Session A中的事务再去查询这条number为1的记录，如果读到列name的值为’关羽’，而Session B中的事务稍后进行了回滚，那么Session A中的事务相当于读到了一个不存在的数据，这种现象就称之为脏读。

这里例子中Session B中的事务是rollback，即使是commit了，虽然最终数据库的状态是一致的，但是在Session A中的事务读取number=1这条记录的时候，这个事务却得到了不一致的状态。数据库不一致的状态是不应该暴露给用户的。

严格一点的解释：假设事务T1、T2并发执行，它们都要访问数据项X，T1先修改了X的值，然后T2又读取了未提交事务T1修改后的X值，之后T1中止而T2提交。这就意味着T2读到了一个根本不存在的值，这也是脏读的严格解释。

3.1.3 不可重复读（Non-Repeatable Read）

如果一个事务修改了另一个未提交事务读取的数据，就意味着发生了不可重复读现象，或者叫模糊读(Fuzzy Read)现象。

读的’刘备’，被修改为’关羽’ ，读的’关羽’ 又被修改为了’张飞’ 。

严格一点的解释：假设事务T1、T2并发执行，它们都要访问数据项X，T1先读取了X的值，然后T2又修改了未提交事务T1读取的X的值，之后T2提交，然后T1再次读取数据项X的值时会得到与第一次读取时不同的值。

3.1.4 幻读（Phantom）

如果一个事务先根据某些条件查询出一些记录，之后另一个事务又向表中插入了符合这些条件的记录，原先的事务再次按照该条件查询时，能把另一个事务插入的记录也读出来，那就意味着发生了幻读，示意图如下：

严格一点的解释：假设事务T1、T2并发执行，T1先读取符合搜索条件P的记录，然后T2写入了符合搜索条件P的记录。之后T1再读取符合搜索条件P的记录时，会发现两次读取的记录时不一样的。

如果Session B中是删除了一些符合number > 0的记录而不是插入新记录，那Session A中之后再根据number > 0的条件读取的记录变少了，这种现象算不算幻读呢？明确说明下，这种现象不属于幻读，幻读强调的是一个事务按照某个相同条件多次读取记录时，后读取时读到了之前没有读到的记录。

我们这里只考虑SQL标准中提到的，不考虑其他论文的描述，对于MySQL来说，幻读强调的是“一个事务在按照某个相同的搜索条件多次读取记录时，在后续读取到了之前没读到的记录”，可能是别的事务insert操作引起的。那对于先前已经读到的记录，之后又读取不到这种情况算啥呢？我们把这种现象认为是结果集中的每一条记录分别发生了不可重复读的现象。

比如：第一次读到abc三条记录，第二次读到abd，既多了d记录，又少了c记录，这怎么分析?
对于记录c来说，发生了不可重复读，对于记录d来说，发生了幻读。一致性问题针对每条记录分析即可。

是否有可能发生一致性问题的判断依据是，在准备读取的那一刻，想查询的数据库某些列的值与实际查询出来的可能会有出入，则认为可能会发生一致性问题。

综上：脏读、不可重复读、幻读都可能会发生一致性问题。

既然会出现这些问题，那么SQL也有一些标准来处理这些问题，接着看吧

3.2 SQL标准中的四种隔离级别

我们给可能导致一致性问题的严重性给这些现象排一下序：

脏读 > 不可重复读 > 幻读

舍弃一部分隔离性来换取一部分性能在这里就体现在：设立一些隔离级别，隔离级别越低，越可能发生严重的问题。有一帮人（并不是设计MySQL的大叔）制定了一个所谓的SQL标准，在标准中设立了4个隔离级别：

  • READ UNCOMMITTED：未提交读。
  • READ COMMITTED：已提交读 （又简称为RC） 。
  • REPEATABLE READ：可重复读 （又简称为RR）。
  • SERIALIZABLE：可串行化。

SQL标准中规定（是SQL标准中规定，不是MySQL中规定），针对不同的隔离级别，并发事务可以发生不同的现象，具体情况如下：

SQL92标准中并没有指出脏写的现象，脏写这个现象对于一致性影响太严重了，无论哪种隔离级别都不允许脏写的发生，这里就不多提。

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

不同的数据库厂商对SQL标准中规定的4种隔离级别支持不一样，比方说Oracle就只支持READ COMMITTED（Oracle的默认隔离级别）和SERIALIZABLE隔离级别。这里所讨论的MySQL虽然支持4种隔离级别，但与SQL标准中所规定的各级隔离级别允许发生的问题却有些出入，MySQL在REPEATABLE READ隔离级别下，是可以很大程度上禁止幻读问题的发生的（关于如何禁止之后会详细说明的）。

MySQL的默认隔离级别为REPEATABLE READ，我自己手上的项目在生产环境的隔离级别是READ COMMITTED，而且相关的一些接口可能同时操作同一张表的某一个账号，并发性较高，我的操作是：每次进入事务之前都会用Redis分布式锁去锁住这个账号再进入事务，操作同一个账号同一时间只能有一个成功，这样就不会出现多个事务并发去操作这个账号相关性的数据，也就不会有这条记录出现不可重复读和幻读的机会。

3.3.1 如何设置事务的隔离级别

我们可以通过下边的语句修改事务的隔离级别（实际开发中是不会让开发人员随意有这种操作的，可以在自己电脑尝试）：

SET [GLOBAL|SESSION] TRANSACTION ISOLATION LEVEL level;

其中的level可选值有4个：

level: {
 REPEATABLE READ
 | READ COMMITTED
 | READ UNCOMMITTED
 | SERIALIZABLE
}

设置事务的隔离级别的语句中，在SET关键字后可以放置GLOBAL关键字、SESSION关键字或者什么都不放，这样会对不同范围的事务产生不同的影响，具体如下：

  • 使用GLOBAL关键字（在全局范围产生影响）：

比如下面这样：

SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE;

则：

  1. 只对执行完该语句之后新产生的会话起作用。
  2. 当前已经存在的会话无效。

所谓新产生的会话，如果你是navicat操作，得关闭连接之后再打开连接才算新的会话，如果仅仅是新建查询还算同一个会话，是看不到设置前后隔离级别的变化的。

  • 使用SESSION关键字（在会话范围影响）：

比方说这样：

SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

则：

  1. 对当前会话的所有后续的事务有效
  2. 该语句可以在已经开启的事务中间执行，但不会影响当前正在执行的事务。
  3. 如果在事务之间执行，则对后续的事务有效。
  • 上述两个关键字都不用（只对执行这个SET语句后的下一个事务产生影响）：

比如下面这样：

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

则：

  1. 只对当前会话中下一个即将开启的事务有效。
  2. 下一个事务执行完后，后续事务将恢复到之前的隔离级别。
  3. 该语句不能在已经开启的事务中间执行，否则会报错。

如果我们在服务器启动时想改变事务的默认隔离级别，可以修改启动参数transaction-isolation的值，比方说我们在启动服务器时指定了--transaction-isolation=SERIALIZABLE，那么事务的默认隔离级别就从原来的REPEATABLE READ变成了SERIALIZABLE。

可以通过查看系统变量transaction_isolation的值来确定当前会话默认的隔离级别：

SHOW VARIABLES LIKE 'transaction_isolation';

注意：transaction_isolation是在MySQL 5.7.20的版本中引入来替换tx_isolation的，如果你使用的是之前版本的MySQL，请将上述用到系统变量transaction_isolation的地方替换为tx_isolation。

或者使用更简便的写法：

SELECT @@transaction_isolation;

我们之前使用SET TRANSACTION语法来设置事务的隔离级别时，其实就是在间接设置系统变量transaction_isolation的值，我们也可以直接修改系统变量transaction_isolation来设置事务的隔离级别。系统变量一般系统变量只有GLOBAL和SESSION两个作用范围，而这个transaction_isolation却有3个（GLOBAL、SESSION、仅作用于下一个事务），设置语法上有些特殊，更多详情可以参见文档：transaction_isolation。
这里总结下：

4. MVCC原理

4.1 版本链

在前文底层揭秘MySQL行格式记录头信息说过，对于使用InnoDB存储引擎的表来说，它的聚簇索引记录中都包含两个必要的隐藏列（row_id并不是必要的，我们创建的表中有主键或者有NOT NULL限制的UNIQUE键时都不会包含row_id列）：

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

比方说我们的表hero现在只包含一条记录：

假设插入该记录的事务id为80，那么此刻该条记录的示意图如下所示：

假设之后两个事务id分别为100、200的事务对这条记录进行UPDATE操作，操作流程如下：

是否可以在两个事务中交叉更新同一条记录呢？不可以！这不就是一个事务修改了另一个未提交事务修改过的数据，沦为了脏写了么？InnoDB使用锁来保证不会有脏写情况的发生，也就是在第一个事务更新某条记录前，就会给这条记录加锁，另一个事务再次更新该记录时，就需要等待第一个事务提交，把锁释放之后才可以继续更新。所以这里trx 200在③④⑤步的时候因为锁的原因是被阻塞的，关于锁，后续文章再介绍。

每次对记录进行改动，都会记录一条undo日志，每条undo日志也都有一个roll_pointer属性（INSERT操作对应的undo日志没有该属性，因为该记录并没有更早的版本），可以将这些undo日志都连起来，串成一个链表，所以现在的情况就像下图一样：

在每次更新该记录后，都会将旧值放到一条undo日志中（就算是该记录的一个旧版本），随着更新次数的增多，所有的版本都会被roll_pointer属性连接成一个链表，我们把这个链表称之为版本链，版本链的头节点就是当前记录最新的值。另外，每个版本中还包含生成该版本时对应的事务id（这很重要），我们之后会利用这个记录的版本链来控制并发事务访问相同记录的行为，我们把这种机制称之为多版本并发控制（Multi-Version Concurrency Control，MVCC）

从上图可以看到，聚集索引记录和undo日志中的roll_pointer属性可以串连成一个记录的版本链。同一条记录在系统中可以存在多个版本，就是数据库的多版本并发控制（MVCC）

在UPDATE操作产生的undo日志中，只会记录一些索引列以及被更新的列的信息，并不会记录所有列的信息，上一张图展示的undo日志中，之所以将一条记录的全部列的信息都画出来是为了方便理解（因为这样很直观的显示了该版本中各个列的值是什么）。比如对于trx_id为80的那条undo日志来说，本身是没有记录country列的信息的，那怎么知道该版本中country列的值是多少呢？没有更新该列则说明该列和上一个版本中的值相同。如果上一个版本的undo日志也没有记录该列的值，那么就和上上个版本中该列的值相同。如果各个版本的undo日志都没有记录该列的值，说明该列从未被更新过，那么trx_id为80的那个版本的country列的值就和数据页中的聚集索引记录的country列的值相同。

4.2 ReadView

一条记录竟然更新了那么多版本？版本链中的哪个版本的记录是当前事务可见的？这在不同隔离级别中可见性是不相同的

  • 对于使用READ UNCOMMITTED隔离级别的事务来说，由于可以读到未提交事务修改过的记录，所以直接读取记录的最新版本就好了。（不生成ReadView）
  • 对于使用SERIALIZABLE隔离级别的事务来说，设计InnoDB的大叔规定使用加锁的方式来访问记录。（不生成ReadView）
  • 对于使用READ COMMITTED和REPEATABLE READ隔离级别的事务来说，都必须保证读到已经提交的事务修改过的记录，也就是说假如另一个事务已经修改了记录但是尚未提交，则不能直接读取最新版本的记录。（只有RC、RR这2个隔离级别在读取数据的时候生成ReadView）

一定要注意，没有事务就没有ReadView，ReadView是事务产生的，而且是基于整个数据库的。

对此，设计InnoDB的大叔提出了一个ReadView（有的翻译为“一致性视图”）的概念

注意！在MySQL里有两个“视图”的概念：

一个是view。它是一个用查询语句定义的虚拟表，在调用的时候执行查询语句并生成结果。创建视图的语法是create view ...，而它的查询方法与表一样。 另一个是InnoDB在实现MVCC时用到的一致性读视图，即consistent read view，用于支持RC和RR隔离级别的实现。 ReadView它没有物理结构，作用是事务执行期间用来定义“我能看到什么数据”。

这个ReadView中主要包含4个比较重要的内容：

  1. m_ids：表示在生成ReadView时当前系统中活跃的读写事务的事务id列表。“活跃”指的就是，启动了但还没提交。
  2. min_trx_id：表示在生成ReadView时当前系统中活跃的读写事务中最小的事务id，也就是m_ids中的最小值。
  3. max_trx_id：表示生成ReadView时系统中应该分配给下一个事务的事务id值。
    注意max_trx_id并不是m_ids中的最大值，事务id是递增分配的。比方说现在有事务id为1，2，3这三个事务，之后事务id为3的事务提交了。那么一个新的读事务在生成ReadView时，m_ids就包括1和2，min_trx_id的值就是1，max_trx_id的值就是4。
  4. creator_trx_id：表示生成该ReadView的事务的事务id。

只有在对表中的记录做改动时（执行INSERT、DELETE、UPDATE这些语句时）才会为事务分配trx_id，否则在一个只读事务中的事务id的值trx_id都默认为0，未分配trx_id前，creator_trx_id的值为0，分配trx_id后，creator_trx_id就变化成了对应的事务的trx_id。

在MySQL中，READ COMMITTED和REPEATABLE READ隔离级别的的一个非常大的区别就是它们生成ReadView的时机不同。我们还是以表hero为例来，假设现在表hero中只有一条由事务id为80的事务插入的一条记录：

注意：当一个ReadView生成了，m_ids、min_trx_id、max_trx_id、creator_trx_id等变量的值都是固定的，比如此时有事务提交，m_ids活跃事务列表的值也不会变。ReadView就像快照一样，生成了就不再变，除非生成新的。

接下来看一下READ COMMITTED和REPEATABLE READ所谓的生成ReadView的时机不同到底不同在哪里。

4.2.1 READ COMMITTED —— 一个事务中每次读取数据前都生成一个ReadView

比如，现在系统里有两个事务id分别为100、200的事务在执行：

再次强调，事务执行过程中，只有在第一次真正修改记录时（比如使用INSERT、DELETE、UPDATE语句），才会被分配一个唯一的事务id，这个事务id是递增的。所以我们才在Transaction 200中更新一些别的表的记录，目的是让它分配事务id。

此刻，表hero中number为1的记录得到的版本链表如下所示：

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

# 使用READ COMMITTED隔离级别的事务
BEGIN;
# SELECT1：Transaction 100、200未提交
SELECT * FROM hero WHERE number = 1; # 得到的列name的值为'刘备'

这个SELECT1的执行过程如下：

  1. 在执行SELECT语句时会先生成一个ReadView，ReadView的m_ids列表的内容就是[100, 200]，min_trx_id为100，max_trx_id为201，creator_trx_id为0。
  2. 然后从版本链中挑选可见的记录。从图中可以看出，最新版本的列name的内容是’张飞’，该版本的trx_id值为100，在m_ids列表内，说明trx_id为100的事务还没提交，所以不符合可见性要求，根据roll_pointer跳到下一个版本。
  3. 下一个版本的列name的内容是’关羽’，该版本的trx_id值也为100，也在m_ids列表内，所以也不符合要求，继续跳到下一个版本。
  4. 下一个版本的列name的内容是’刘备’，该版本的trx_id值为80，小于ReadView中的min_trx_id值100，说明trx_id为80的事务已经提交了，所以这个版本是符合要求的，最后返回给用户的版本就是这条列name为’刘备’的记录。

之后，我们把事务id为100的事务提交一下，如下：

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

然后再到事务id为200的事务中更新一下表hero中number为1的记录：

# Transaction 200
BEGIN;
# 更新了一些别的表的记录
...
UPDATE hero SET name = '赵云' WHERE number = 1;
UPDATE hero SET name = '诸葛亮' WHERE number = 1;

此刻，表hero中number为1的记录的版本链就长这样：

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

# 使用READ COMMITTED隔离级别的事务
BEGIN;
# SELECT1：Transaction 100、200均未提交（第一次查询两个事务均未提交）
SELECT * FROM hero WHERE number = 1; # 得到的列name的值为'刘备'
# SELECT2：Transaction 100提交，Transaction 200未提交（第二次查询事务id为100的事务提交了）
SELECT * FROM hero WHERE number = 1; # 得到的列name的值为'张飞'

分析一下SELECT2的执行过程

  1. 在执行SELECT语句时会又会单独生成一个ReadView，该ReadView的m_ids列表的内容就是[200]（事务id为100的那个事务已经提交了，所以再次生成ReadView时就没有它了），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为’张飞’的记录。

以此类推，如果之后事务id为200的记录也提交了，再次在使用READ COMMITTED隔离级别的事务中查询表hero中number值为1的记录时，得到的结果就是’诸葛亮’了。总结一下就是：使用READ COMMITTED隔离级别的事务在每次查询开始时都会生成一个独立的ReadView。

注意：RC下，在一个事务中，一条查询语句执行完，事务生成的ReadView就没用了，下次查询得重新生成ReadView。

4.2.2 REPEATABLE READ —— 一个事务中第一次读取数据时生成一个ReadView

按照可重复读的定义，一个事务启动的时候，能够看到所有已经提交的事务结果。但是之后这个事务执行期间，其他事务的更新对它不可见。

对于使用REPEATABLE READ隔离级别的事务来说，只会在第一次执行查询语句时生成一个ReadView，之后的查询就不会重复生成了。来用和之前相同的例子再次分析。

比如，现在系统里有两个事务id分别为100、200的事务在执行：

此刻，表hero中number为1的记录得到的版本链表如下所示：

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

# 使用REPEATABLE READ隔离级别的事务
BEGIN;
# SELECT1：Transaction 100、200未提交
SELECT * FROM hero WHERE number = 1; # 得到的列name的值为'刘备'

这里和4.2.1节的READ COMMITTED隔离级别的SELECT1分析过程一模一样，不赘述。查询出来是name为’刘备’ 的记录。

我们把事务id为100的事务提交一下，如下：

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

然后再到事务id为200的事务中更新一下表hero中number为1的记录：

# Transaction 200
BEGIN;
# 更新了一些别的表的记录
...
UPDATE hero SET name = '赵云' WHERE number = 1;
UPDATE hero SET name = '诸葛亮' WHERE number = 1;

此刻，表hero中number为1的记录的版本链就长这样：

一直到这里，例子分析和4.2.1节的READ COMMITTED隔离级别的分析过程一样。接下来，不一样的来了。

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

# 使用REPEATABLE READ隔离级别的事务300
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。 之前的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列表中是包含值为100的事务id的，所以该版本也不符合要求，同理下一个列name的内容是’关羽’的版本也不符合要求。继续跳到下一个版本。
  5. 下一个版本的列name的内容是’刘备’，该版本的trx_id值为80，小于ReadView中的min_trx_id值100，所以这个版本是符合要求的，最后返回给用户的版本就是这条列name为’刘备’的记录。

也就是说在REPEATABLE READ隔离级别下，事务的两次查询得到的结果是一样的。记录的name列值都是’刘备’，这就是为什么在RR下，不会出现不可重复读的理由。如果我们之后再把事务id为200的记录提交了，然后再到刚才使用REPEATABLE READ隔离级别的事务中继续查找这个number为1的记录，得到的结果还是’刘备’。

要想读到最新name值为’诸葛亮’该如何操作呢？

前提: 把事务id为100、200的事务提交。

  1. 此时把事务id为300的事务提交，ReadView就没用了，下次开启新的事务查询的时候会生成新的ReadView，m_ids列表中没有100、200，就可以查询到name为’诸葛亮’了。
  2. 如果新的查询没有事务，那就没有ReadView这一说了，直接select查询就可以查到name为’诸葛亮’的记录，因为事务100、200已提交。

注意对比：

RR下，一个事务提交时，它生成的ReadView就没用了。

RC下，在一个事务中，一条查询语句执行完，事务生成的ReadView就没用了，下次查询得重新生成ReadView。

小提示：

在RR下，如果使用START TRANSACTION WITH CONSISTENT SNAPSHOT语句开启事务，会在执行该语句后立即生成一个ReadView，而不是在执行第一条SELECT语句时才生成。

使用START TRANSACTION WITH CONSISTENT SNAPSHOT这个语句开始，创建一个持续整个事务的ReadView。所以在RC隔离级别下（每次读都创建ReadView），这个用法就没意义了，等效于普通的start transaction。

4.2.3 ReadView的可见性规则总结

在访问某条记录时，只需要按照下边的步骤判断记录的某个版本是否可见：

  1. trx_id = creator_trx_id时，意味着当前事务在访问它自己修改过的记录，所以该版本可以被当前事务访问。
  2. trx_id < min_trx_id时，表明生成该版本的事务在当前事务生成ReadView前已经提交，所以该版本可以被当前事务访问。
  3. trx_id ≥ max_trx_id时，表明生成该版本的事务在当前事务生成ReadView后才开启，所以该版本不可以被当前事务访问。
  4. min_trx_id ≤ trx_id ≤ max_trx_id之间，那就需要判断一下trx_id属性值是不是在m_ids列表中，如果在，说明创建ReadView时生成该版本的事务还是活跃的，该版本不可以被访问；如果不在，说明创建ReadView时生成该版本的事务已经被提交，该版本可以被访问。

如果某个版本的数据对当前事务不可见的话，那就顺着版本链找到下一个版本的数据，继续按照上边的步骤判断可见性，依此类推，直到版本链中的最后一个版本。如果最后一个版本也不可见的话，那么就意味着该条记录对该事务完全不可见，查询结果就不包含该记录。

上面说了，ReadView是基于整库的。如果一个库有100G，那么我启动一个事务，MySQL就要拷贝100G的数据出来吗，这样多慢啊，可是，我们平时的事务执行起来很快啊。

实际上，我们并不需要拷贝出这100G的数据。InnoDB利用版本链和活跃的事务id列表，可以实现“秒级创建ReadView”。

思考题：

RR隔离级别下事务T1和T2并发执行，T1先根据某个搜索条件读取到3条记录，然后事务T2插入一条符合相应搜索条件的记录并提交，然后事务T1再根据相同搜索条件执行查询，结果如何？

分析：根据版本链和ReadView分析，T1第一次搜索3条记录的时候生成了ReadView，此时T1、T2都在m_ids列表，都是活跃的，那么T2中插入的版本记录T1是不可见的，所以事务T1第二次搜索仍然是3条记录。此时在RR下避免了幻读的产生。

由于MySQL的具体实现问题，RR隔离级别下并不能完全避免幻读（只能很大程度避免），只有加锁才可以完全避免。

4.3 为什么不推荐使用长事务？

前面讲版本链的时候说过，每条记录在更新的时候都会同时记录一条回滚的 undo日志 （也称为回滚段）。通过回滚操作，都可以得到前一个状态的值。

当前number为1的记录name为是'诸葛亮'，但是在查询这条记录的时候，不同时刻启动的事务会有不同的ReadView。如图，要得到name为'刘备'的记录，就必须将当前值依次执行图中所有的回滚操作得到。

  • 回滚段非常占用内存，那回滚段什么时候删除呢？

从上图可以看到回滚段里都是之前事务修改过的记录，事务提交后该记录的旧版本就不需要了，所以只有当开启回滚段以来的所有事务都提交的时候，回滚段就可以删除。

  • 为什么不推荐使用长事务？

长事务意味着系统里面会存在很老的记录，事务不提交，记录的旧版本会一直存在。由于这些事务随时可能访问数据库里面的任何数据，所以这个事务提交之前，数据库里面它可能用到的回滚记录都必须保留，这就会导致大量占用存储空间。

在MySQL 5.5及以前的版本，回滚日志是跟数据字典一起放在ibdata文件里的，即使长事务最终提交，回滚段被清理，文件也不会变小。有时候数据只有20GB，而回滚段有200GB的库。最终只好为了清理回滚段，重建整个库。

除了对回滚段的影响，长事务还占用锁资源，也可能拖垮整个库。

  • 如何查询长事务？

在information_schema库的innodb_trx这个表中查询长事务，比如下面这个语句，用于查找持续时间超过60s的事务。

select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>60

4.4 非聚集索引与MVCC

前面说过，只有聚集索引记录才有trx_id和roll_pointer隐藏列，如果某个查询语句是使用二级索引来执行查询时，该如何判断可见性呢？

begin；
select * from hero where name = '刘备';

这里判断条件是name了，这是一个普通的非聚集索引，没有trx_id和roll_pointer该怎么根据版本链和ReadView去判断可见性呢？

注：trx_id是记录存放该事务的事务id的地方，没有这一列只能说明非聚集索引记录没存，并不代表执行事务时没有事务id了。

过程如下：

步骤1：非聚集索引页面的Page Header部分有一个名为PAGE_MAX_TRX_ID的属性，每当对该页面中的记录执行增删改操作的时候，如下：

// 这里用伪代码说明更便捷
if(如果执行该事务的事务id > PAGE_MAX_TRX_ID) {
PAGE_MAX_TRX_ID = 如果执行该事务的事务id;
}

所以PAGE_MAX_TRX_ID属性值代表修改该非聚集索引页面的最大的事务id。

当SELECT语句根据条件找到某个非聚集索引记录时，如下：

if (对应ReadView的min_trx_id > PAGE_MAX_TRX_ID) {
说明该页面中的所有记录都对该ReadView可见
} else {
执行步骤2
}

步骤2： 根据主键回表后，得到满足搜索条件的聚集索引记录后，根据版本链找到该ReadView可见的第一个版本，然后判断该版本中相应的非聚集索引列的值是否与利用该非聚集索引查询时的值相同。本例子就是判断可见版本的name是不是’刘备’。如果是，就把这条记录发送给客户端（如果where子句中还有其他搜索条件的话还需要继续判断筛选后再返回），否则就跳过该记录。

4.5 MVCC小结

所谓的MVCC（Multi-Version Concurrency Control ，多版本并发控制）指的就是在使用READ COMMITTD、REPEATABLE READ这两种隔离级别的事务执行普通的SELECT操作时，访问记录的版本链的过程。这样可以使不同事务的读-写、写-读操作并发执行，从而提升系统性能。READ COMMITTD、REPEATABLE READ这两个隔离级别的一个很大不同，就是生成ReadView的时机不同，READ COMMITTD在一个事务中每一次进行普通SELECT操作前都会生成一个ReadView，而REPEATABLE READ在一个事务中只在第一次进行普通SELECT操作前生成一个ReadView，之后的查询操作都重复使用这个ReadView。

5. 全篇的反思与小结，你需要弄懂这几个问题

  1. 事务的概念是什么?
  2. MySQL的事务隔离级别读未提交, 读已提交, 可重复读, 串行读各是什么意思?
  3. 读已提交, 可重复读是怎么通过视图构建实现的?
  4. 事务隔离是怎么通过ReadView(读视图)实现的?
  5. 并发版本控制(MVCC)的概念是什么, 是怎么实现的?
  6. 使用长事务的弊病? 为什么使用长事务可能拖垮整个库?
  7. 怎么查询各个表中的长事务?
  8. 如何避免长事务的出现?

 

点击关注，第一时间了解华为云新鲜技术~

标签：事务,读取,记录,ReadView,trx,版本,MySQL,想象,id
From： https://www.cnblogs.com/huaweiyun/p/17477122.html

相关文章

  • 压缩包版MySQL8安装
    1、下载解压链接：https://pan.baidu.com/s/11OxndQSx9GVP30chaGf7uA?pwd=1mfh提取码：1mfh2、新建ini文件在MySQL根目录新建（具有bin文件夹那一级）[mysqld]<!--设置3306端口-->port=3306<!--设置mysql的安装目录-->basedir=D:\File\Mysql\mysql-8.0.22-winx64<!--设置mysql......
  • 【MySQL的CheckPoint技术】
    MySQL的CheckPoint（CheckPoint）技术，又称"redolog"（Redolog)，是MySQL数据库的核心技术之一，其目的是在发生故障或故障时，保证数据库中存储的数据可以得到有效的修复。对数据库进行的修改，包括写入，更新，删除等，都要进行相应的文档化，以确保数据在任何时候都是一致的。如何使用MySQL检查......
  • 小灰灰深度学习day9——多线程读取小批量数据(这里运行的时候报错了,目前还不会解决,
    在这里先把代码放上来importtorchimporttimeimportnumpyasnpimporttorchvisionfromtorch.utilsimportdatafromtorchvisionimporttransformsfromd2limporttorchasd2ld2l.use_svg_display()#利用svg显示图片importosos.environ["KMP_DUPLICATE_LIB_OK......
  • centos7 虚拟机安装 MySQL数据库
    检查是否安装过MySQL以及版本信息rpm-qa|grep-imysql卸载之前的版本,参数-e表示删除，-nodeps表示不校验依赖关系rpm-emysql-5.1.47-4.el6.x86_64–nodeps二进制包格式安装的mysql卸载如果我们的mysql是通过二级制包格式安装的，则rpm-qa|grep-imysql是......
  • Ubuntu-20.04.6-server安装MySQL实现远程连接
    Ubuntu-20.04.6-server安装MySQL，修改密码安装MySQL一、查看是否安装数据库mysql--version二、更新系统中的所有软件包和存储库sudoaptupdate三、安装Mysql最新版(我这里已经安装好了)sudoaptinstallmysql-server四、安装成功后，mysql--version查看版本五、查......
  • MySQL-MHA搭建
    1、测试环境目前MHA主要支持一主多从的架构，要搭建MHA,要求一个复制集群中必须最少有三台数据库服务器，一主二从，即一台充当master，一台充当备用master，另外一台充当从库.一主两从的环境，通过GTID方式配置主从复制同步环境用途IP地址安装软件MySQL版本主库db01192.168.3......
  • 熟悉常用的mysql增删改查命令
    增删改查，掌握基本的数据库基础操作命令1.创建数据库createdatabasepems2;这个是连接数据库的工具查询已有的数据库，也可以在服务器进入数据库命令行查询mysql>showdatabases;+--------------------+|Database|+--------------------+|information_schema||mysql......
  • Springboot读取不到yml文件的问题(转载)
    Springboot工程分为两个大文件夹，/src/main和/src/test，main和test下面分别由java和resource，如图所示： 规则：main的java里面的代码，只能读取main的resource的配置文件；test的java里面的代码，既可以读取main的resource的配置文件，也可以读取test的resource的配置文件。test......
  • Docker 安装 MySQL8 数据库
    创建数据卷mkdir-p/usr/mysql/conf/usr/mysql/datachmod-R755/usr/mysql/创建配置文件vim/usr/mysql/conf/my.cnf:[client]#socket=/usr/mysql/mysqld.sockdefault-character-set=utf8mb4[mysqld]#pid-file=/var/run/mysqld/mysqld.pid#sock......
  • Qt打包程序移动到新环境时提示 QMYSQL driver not loaded
    Qt版本是：Qt6.3.2MySQL版本是：mysql8.0.33运行时日志提示如下：Warning:File:()Line:(0)QSqlDatabase:QMYSQLdrivernotloaded(2023-06-1217:16:56)Warning:File:()Line:(0)QSqlDatabase:availabledrivers:QSQLITEQMARIADBQMYSQLQODBCQPSQL(2023-06-1217:16:56)......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99