iis服务器助手广告广告

扫码关注官方微信

扫码下载APP
返回顶部
首页 > 资讯 > 数据库 >一文带你了解MySQL之事务隔离级别和MVCC
  • 613

0
分享到

一文带你了解MySQL之事务隔离级别和MVCC

mysql数据库数据库开发大数据数据库架构 2023-08-16 18:08:29 613人浏览 八月长安
摘要

目录 一、数据准备二、事务隔离级别2.1 事务并发执行遇到的问题2.2 SQL标准中的四种隔离级别2.3 MySQL中支持的四种隔离级别 三、MVCC3.1 版本链3.2 ReadView3.2.1 READ COMMITTED

目录

一、数据准备

为了我们学习的顺利进行，我们这边创建一张 hero表

CREATE TABLE hero(number INT PRIMARY KEY,name VARCHAR(4),country VARCHAR(2));

这里需要注意的是，我们的 hero表的主键是number，而不是id，主要是后边要用到的事务id做一下区别，然后我们给这个表里插入一条数据

mysql> INSERT INTO hero VALUES(1,'张角','东汉');Query OK, 1 row affected (0.01 sec

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

mysql> SELECT * FROM hero;+--------+--------+---------+| number | name   | country |+--------+--------+---------+|      1 | 张角   | 东汉    |+--------+--------+---------+1 row in set (0.00 sec)

二、事务隔离级别

我们知道Mysql是CS架构的软件，若干个客户端与服务器连接上之后，就可以称之为一个会话（Session）。每个客户端都可以在自己的会话中向服务器发出请求语句，一个请求语句可能是某个事务的一部分，也就是对于服务器来说可能同时处理多个事务。在事务简介的章节中我们说过事务有一个称之为隔离性的特性，理论上在某个事务对某个数据进行访问时，其他事务应该进行排队，当该事务提交之后，其他事务才可以继续访问这个数据。但是这样子的话对性能影响太大，我们既想保持事务的隔离性，又想让服务器在处理访问同一数据的多个事务时性能尽量高些，鱼和熊掌不可得兼，舍一部分隔离性而取性能者也。

2.1 事务并发执行遇到的问题

怎么个舍弃法呢？我们先得看一下访问相同数据的事务在不保证串行执行（也就是执行完一个再执行另一个）的情况下可能会出现哪些问题：

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

Session ASession B
1BEGIN;
2BEGIN;
3UPDATE hero SET name = ‘赵云’ WHERE number = 1;
4UPDATE hero SET name = ‘法正’ WHERE number = 1;
5COMMIT;
6ROLLBACK;

如上表，Session A和Session B各开启了一个事务，Session B中的事务先将number列为1的记录的name列更新为'赵云'，然后Session A中的事务接着又把这条number列为1的记录的name列更新为法正。如果之后Session B中的事务进行了回滚，那么Session A中的更新也将不复存在，这种现象就称之为脏写。这时Session A中的事务就很懵逼，我明明把数据更新了，最后也提交事务了，怎么到最后说自己啥也没干呢？

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

Session ASession B
1BEGIN;
2BEGIN;
3UPDATE hero SET name = ‘赵云’ WHERE number = 1;
4SELECT * FROM hero WHERE number = 1;(此时读到的name列的值为‘赵云’，味的发生了脏读)
5COMMIT;
6ROLLBACK;

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

不可重复读（Non-Repeatable Read）： 如果一个事务只能读到另一个已经提交的事务修改过的数据，并且其他事务每对该数据进行一次修改并提交后，该事务都能查询得到最新值，那就意味着发生了不可重复读，示意图如下：

Session ASession B
1BEGIN;
2SELECT * FROM hero WHERE number = 1;(此时读到的name列的值为‘张角’)
3UPDATE hero SET name = ‘赵云’ WHERE number = 1;
4SELECT * FROM hero WHERE number = 1;(此时读到的name列的值为‘赵云’，意味的发生了不可重复读)
5UPDATE hero SET name = ‘法正’ WHERE number = 1;
6SELECT * FROM hero WHERE number = 1;(此时读到的name列的值为‘法正’，意味的发生了不可重复读)

如上表，我们在Session B中提交了几个隐式事务（意味着语句结束事务就提交了），这些事务都修改了number列为1的记录的列name的值，每次事务提交之后，如果Session A中的事务都可以查看到最新的值，这种现象也被称之为不可重复读。

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

Session ASession B
1BEGIN;
2SELECT * FROM hero WHERE number = 1;(此时读到的name列的值为‘张角’)
3INSERT INTO hero VALUES(1,‘许诸’,‘魏国’);
4SELECT * FROM hero WHERE number = 1;(此时读到的name列的值为‘张角’、‘许诸’，意味的发生了幻读)

如上表，Session A中的事务先根据条件number > 0这个条件查询表hero，得到了name列值为'张角'的记录；之后Session B中提交了一个隐式事务，该事务向表hero中插入了一条新记录；之后Session A中的事务再根据相同的条件number > 0查询表hero，得到的结果集中包含Session B中的事务新插入的那条记录，这种现象也被称之为幻读。

Q1: 如果Session B中是删除了一些符合number > 0的记录而不是插入新记录，那Session A中之后再根据number > 0的条件读取的记录变少了，这种现象算不算幻读呢？

W1: 明确说一下，这种现象不属于幻读，幻读强调的是一个事务按照某个相同条件多次读取记录时，后读取时读到了之前没有读到的记录。

小提士：
那对于先前已经读到的记录，之后又读取不到这种情况，算啥呢？其实这相当于对每一条记录都发生了不可重复读的现象。幻读只是重点强调了读取到了之前读取没有获取到的记录。

2.2 sql标准中的四种隔离级别

我们上边介绍了几种并发事务执行过程中可能遇到的一些问题，这些问题也有轻重缓急之分，我们给这些问题按照严重性来排一下序：

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

我们上边所说的舍弃一部分隔离性来换取一部分性能在这里就体现在：设立一些隔离级别，隔离级别越低，越严重的问题就越可能发生。于是制定了一个所谓的SQL标准，在标准中设立了4个隔离级别：

  • READ UNCOMMITTED：未提交读
  • READ COMMITTED：已提交读
  • REPEATABLE READ：可重复读
  • SERIALIZABLE：可串行化

SQL标准中规定，针对不同的隔离级别，并发事务可以发生不同严重程度的问题，具体情况如下：

隔离级别脏读不可重复读幻读
READ UNCOMMITTEDPossiblePossiblePossible
READ COMMITTEDNot PossiblePossiblePossible
REPEATABLE READNot PossibleNot PossiblePossible
SERIALIZABLENot PossibleNot PossibleNot Possible

也就是说：

  • READ UNCOMMITTED隔离级别下，可能发生脏读、不可重复读和幻读问题。
  • READ COMMITTED隔离级别下，可能发生不可重复读和幻读问题，但是不可以发生脏读问题。
  • REPEATABLE READ隔离级别下，可能发生幻读问题，但是不可以发生脏读和不可重复读的问题。
  • SERIALIZABLE隔离级别下，各种问题都不可以发生。

Q2: 脏写是怎么回事儿？怎么里边都没写呢？
W2: 这是因为脏写这个问题太严重了，不论是哪种隔离级别，都不允许脏写的情况发生。

2.3 mysql中支持的四种隔离级别

不同的数据库厂商对SQL标准中规定的四种隔离级别支持不一样，比方说oracle就只支持READ COMMITTED和SERIALIZABLE隔离级别。MySQL虽然支持4种隔离级别，但与SQL标准中所规定的各级隔离级别允许发生的问题却有些出入，MySQL在REPEATABLE READ隔离级别下，是可以禁止幻读问题的发生的。

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

如何设置事务的隔离级别

我们可以通过下边的语句修改事务的隔离级别：

SET [GLOBAL|SESSION|PERSIST] TRANSACTION ISOLATION LEVEL level;

其中的level可选值有4个：

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

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

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

    比如：
    SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE;

    则：

    • 只对执行完该语句之后产生的会话起作用。
    • 当前已经存在的会话无效
    • 重启之后会恢复到默认值

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

    比如：
    SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

    则：

    • 对当前会话的所有后续的事务有效
    • 该语句可以在已经开启的事务中间执行，但不会影响当前正在执行的事务
    • 如果在事务之间执行，则对后续的事务有效
    • 重启之后会恢复到默认值

  • 使用PERSIST关键字（使全局系统变量变为永久性）：

    比如：
    SET PERSIST TRANSACTION ISOLATION LEVEL SERIALIZABLE;
    则：

    • 只对执行完该语句之后产生的会话起作用
    • 当前已经存在的会话无效
    • 重启之后不会恢复到默认值

    小提示：
    在5.7版本以前在修改全局变量时候，并不会影响到配置文件，重启之后就会恢复到默认值，MySQL8可以使用PERSIST使全局系统变量变为永久性

  • 上述三个关键字都不用（只对执行语句后的下一个事务产生影响）：

    比如：SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

    则：

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

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

mysql> SHOW VARIABLES LIKE 'transaction_isolation';+-----------------------+--------------+| Variable_name         | Value        |+-----------------------+--------------+| transaction_isolation | SERIALIZABLE |+-----------------------+--------------+1 row in set (0.01 sec)

或者使用更简便的写法：

mysql> SELECT @@transaction_isolation;+-------------------------+| @@transaction_isolation |+-------------------------+| SERIALIZABLE            |+-------------------------+1 row in set (0.00 sec)

三、mvcC

3.1 版本链

我们前边说过，对于使用InnoDB存储引擎的表来说，它的聚簇索引记录中都包含两个必要的隐藏列（row_id并不是必要的，我们创建的表中有主键或者非NULL的UNIQUE键时都不会包含row_id列）：

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

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

mysql> SELECT * FROM hero;+--------+--------+---------+| number | name   | country |+--------+--------+---------+|      1 | 张角   | 东汉    |+--------+--------+---------+1 row in set (0.00 sec)

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

在这里插入图片描述

小提示：
实际上insert undo只在事务回滚时起作用，当事务提交后，该类型的undo日志就没用了，它占用的Undo Log Segment也会被系统回收（也就是该undo日志占用的Undo页面链表要么被重用，要么被释放）。虽然真正的insert undo日志占用的存储空间被释放了，但是roll_pointer的值并不会被清除，roll_pointer属性占用7个字节，第一个比特位就标记着它指向的undo日志的类型，如果该比特位的值为1时，就代表着它指向的undo日志类型为insert undo。所以我们之后在画图时都会把insert undo给去掉，大家留意一下就好了。

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

trx 100trx 200
1BEGIN;
2BEGIN;
3UPDATE hero SET name = ‘赵云’ WHERE number = 1;
4UPDATE hero SET name = ‘法正’ WHERE number = 1;
5COMMIT;
6UPDATE hero SET name = ‘孙尚香’ WHERE number = 1;
7UPDATE hero SET name = ‘妲己’ WHERE number = 1;
8COMMIT;

小提示：
能不能在两个事务中交叉更新同一条记录呢？哈哈，这不就是一个事务修改了另一个未提交事务修改过的数据，沦为了脏写了么？InnoDB使用锁来保证不会有脏写情况的发生，也就是在第一个事务更新了某条记录后，就会给这条记录加锁，另一个事务再次更新时就需要等待第一个事务提交了，把锁释放之后才可以继续更新。关于锁的更多细节我们后续的文章中再进行学习～

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

在这里插入图片描述
对该记录每次更新后，都会将旧值放到一条undo日志中，就算是该记录的一个旧版本，随着更新次数的增多，所有的版本都会被roll_pointer属性连接成一个链表，我们把这个链表称之为版本链，版本链的头节点就是当前记录最新的值。另外，每个版本中还包含生成该版本时对应的事务id，这个信息很重要，我们稍后就会用到。

3.2 ReadView

对于使用READ UNCOMMITTED隔离级别的事务来说，由于可以读到未提交事务修改过的记录，所以直接读取记录的最新版本就好了；对于使用SERIALIZABLE隔离级别的事务来说，InnoDB的规定使用加锁的方式来访问记录（加锁是啥我们后续文章中说哈）；对于使用READ COMMITTED和REPEATABLE READ隔离级别的事务来说，都必须保证读到已经提交了的事务修改过的记录，也就是说假如另一个事务已经修改了记录但是尚未提交，是不能直接读取最新版本的记录的，核心问题就是：需要判断一下版本链中的哪个版本是当前事务可见的。为此，InnoDB提出了一个ReadView的概念，这个ReadView中主要包含4个比较重要的内容：

  • m_ids：表示在生成ReadView时当前系统中活跃的读写事务的事务id列表。

  • min_trx_id：表示在生成ReadView时当前系统中活跃的读写事务中最小的事务id，也就是m_ids中的最小值。

  • 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。

  • creator_trx_id：表示生成该ReadView的事务的事务id。

    小提示：
    我们前边说过，只有在对表中的记录做改动时（执行INSERT、DELETE、UPDATE这些语句时）才会为事务分配事务id，否则在一个只读事务中的事务id值都默认为0。

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

  • 如果被访问版本的trx_id属性值与ReadView中的creator_trx_id值相同，意味着当前事务在访问它自己修改过的记录，所以该版本可以被当前事务访问。

  • 如果被访问版本的trx_id属性值小于ReadView中的min_trx_id值，表明生成该版本的事务在当前事务生成ReadView前已经提交，所以该版本可以被当前事务访问。

  • 如果被访问版本的trx_id属性值大于或等于ReadView中的max_trx_id值，表明生成该版本的事务在当前事务生成ReadView后才开启，所以该版本不可以被当前事务访问。

  • 如果被访问版本的trx_id属性值在ReadView的min_trx_id和max_trx_id之间，那就需要判断一下trx_id属性值是不是在m_ids列表中，如果在，说明创建ReadView时生成该版本的事务还是活跃的，该版本不可以被访问；如果不在，说明创建ReadView时生成该版本的事务已经被提交，该版本可以被访问。

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

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

mysql> SELECT * FROM hero;+--------+--------+---------+| number | name   | country |+--------+--------+---------+|      1 | 张角   | 东汉    |+--------+--------+---------+1 row in set (0.00 sec)

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

3.2.1 READ COMMITTED

对于使用REPEATABLE COMMITTED隔离级别的事务来说，每次读取数据前都生成一个ReadView。比方说现在系统里有两个事务id分别为100、200的事务在执行：

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

小提示：
再次强调一遍，事务执行过程中，只有在第一次真正修改记录时（比如使用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的执行过程如下：

  • 在执行SELECT语句时会先生成一个ReadView，ReadView的m_ids列表的内容就是[100, 200]，min_trx_id为100，max_trx_id为201，creator_trx_id为0。

  • 然后从版本链中挑选可见的记录，从图中可以看出，最新版本的列name的内容是'法正'，该版本的trx_id值为100，在m_ids列表内，所以不符合可见性要求，根据roll_pointer跳到下一个版本。

  • 下一个版本的列name的内容是'赵云'，该版本的trx_id值也为100，也在m_ids列表内，所以也不符合要求，继续跳到下一个版本。

    下一个版本的列name的内容是'张角'，该版本的trx_id值为80，小于ReadView中的min_trx_id值100，所以这个版本是符合要求的，最后返回给用户的版本就是这条列name为'张角'的记录。

# Transaction 100BEGIN;UPDATE hero SET name = '赵云' WHERE number = 1;UPDATE hero SET name = '法正' WHERE number = 1;COMMIT;

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

# Transaction 200BEGIN;# 更新了一些别的表的记录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未提交SELECT * FROM hero WHERE number = 1; # 得到的列name的值为'法正'

这个SELECT2的执行过程如下：

  • 在执行SELECT语句时会又会单独生成一个ReadView，该ReadView的m_ids列表的内容就是[200]（事务id为100的那个事务已经提交了，所以再次生成快照时就没有它了），min_trx_id为200，max_trx_id为201，creator_trx_id为0。

  • 然后从版本链中挑选可见的记录，从图中可以看出，最新版本的列name的内容是'妲己'，该版本的trx_id值为200，在m_ids列表内，所以不符合可见性要求，根据roll_pointer跳到下一个版本。

  • 下一个版本的列name的内容是'孙尚香'，该版本的trx_id值为200，也在m_ids列表内，所以也不符合要求，继续跳到下一个版本。

  • 下一个版本的列name的内容是'法正'，该版本的trx_id值为100，小于ReadView中的min_trx_id值200，所以这个版本是符合要求的，最后返回给用户的版本就是这条列name为'法正'的记录。

以此类推，如果之后事务id为200的记录也提交了，再次在使用READ COMMITTED隔离级别的事务中查询表hero中number值为1的记录时，得到的结果就是'妲己'了，具体流程我们就不分析了。总结一下就是：使用READ COMMITTED隔离级别的事务在每次查询开始时都会生成一个独立的ReadView。

3.2.2 REPEATABLE READ

对于使用REPEATABLE READ隔离级别的事务来说，只会在第一次执行查询语句时生成一个ReadView，之后的查询就不会重复生成了。比方说现在系统里有两个事务id分别为100、200的事务在执行：

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

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

在这里插入图片描述
假设现在有一个使用REPEATABLE READ隔离级别的事务开始执行：

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

这个SELECT1的执行过程如下：

  • 在执行SELECT语句时会先生成一个ReadView，ReadView的m_ids列表的内容就是[100, 200]，min_trx_id为100，max_trx_id为201，creator_trx_id为0。

  • 然后从版本链中挑选可见的记录，从图中可以看出，最新版本的列name的内容是'法正'，该版本的trx_id值为100，在m_ids列表内，所以不符合可见性要求，根据roll_pointer跳到下一个版本。

  • 下一个版本的列name的内容是'赵云'，该版本的trx_id值也为100，也在m_ids列表内，所以也不符合要求，继续跳到下一个版本。

  • 下一个版本的列name的内容是'张角'，该版本的trx_id值为80，小于ReadView中的min_trx_id值100，所以这个版本是符合要求的，最后返回给用户的版本就是这条列name为'张角'的记录。

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

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

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

# Transaction 200BEGIN;# 更新了一些别的表的记录...UPDATE hero SET name = '孙尚香' WHERE number = 1;UPDATE hero SET name = '妲己' WHERE number = 1;

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

在这里插入图片描述

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

# 使用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的执行过程如下：

  • 因为当前事务的隔离级别为REPEATABLE READ，而之前在执行SELECT1时已经生成过ReadView了，所以此时直接复用之前的ReadView，之前的ReadView的m_ids列表的内容就是[100, 200]，min_trx_id为100，max_trx_id为201，creator_trx_id为0

  • 然后从版本链中挑选可见的记录，从图中可以看出，最新版本的列name的内容是'妲己'，该版本的trx_id值为200，在m_ids列表内，所以不符合可见性要求，根据roll_pointer跳到下一个版本。

  • 下一个版本的列name的内容是'孙尚香'，该版本的trx_id值为200，也在m_ids列表内，所以也不符合要求，继续跳到下一个版本。

    下一个版本的列name的内容是'法正'，该版本的trx_id值为100，而m_ids列表中是包含值为100的事务id的，所以该版本也不符合要求，同理下一个列name的内容是'赵云'的版本也不符合要求。继续跳到下一个版本。

  • 下一个版本的列name的内容是'张角'，该版本的trx_id值为80，小于ReadView中的min_trx_id值100，所以这个版本是符合要求的，最后返回给用户的版本就是这条列‘name’为'张角'的记录。

也就是说两次SELECT查询得到的结果是重复的，记录的列name值都是'张角'，这就是可重复读的含义。如果我们之后再把事务id为200的记录提交了，然后再到刚才使用REPEATABLE READ隔离级别的事务中继续查找这个number为1的记录，得到的结果还是'张角'，具体执行过程大家可以自己分析一下。

3.3 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就好了。

小提示：
我们之前说执行DELETE语句或者更新主键的UPDATE语句并不会立即把对应的记录完全从页面中删除，而是执行一个所谓的delete mark操作，相当于只是对记录打上了一个删除标志位，这主要就是为MVCC服务的，大家可以对比上边举的例子自己试想一下怎么使用。
另外，所谓的MVCC只是在我们进行普通的SEELCT查询时才生效，截止到目前我们所见的所有SELECT语句都算是普通的查询，至于啥是个不普通的查询，我们稍后再说哈～

四、关于purge

大家有没有发现两件事儿：

  • 我们说insert undo在事务提交之后就可以被释放掉了，而update undo由于还需要支持MVCC，不能立即删除掉。

    我们在学习undo日志的时候，一个事务写的一组undo日志中都有一个Undo Log Header部分，这个Undo Log Header中有一个名为TRX_UNDO_HISTORY_node的属性，表示名为History链表的节点。当一个事务提交之后，就会把这个事务的执行过程产生的这一组Update undo日志插入到History链表的头部

    我们还说过，每个回滚段都对应一个名为Rollbcak Segment Header的页面，这个页面中有两个属性：

    • TRX_RSEG_HISTORY：表示Histroy链表的基节点

    • TRX_RSEG_HISTORY_SIZE：表示Histroy链表占用的页面数量

    也就是说每个回滚段都有一个History链表，一个事务在某个回滚段中写入的一组update undo日志在该事物提交之后就会加入这个回滚段的History链表。系统中存在很多回滚段，这也意味着可能存在很多个History链表。不过加入到History链表的update undo日志所占用的存储空间页没有释放掉，他们总不能一直存在吧，那得用多大的存储空间存放这些undo日志。

  • 为了支持MVCC，对于delete mark操作来说，仅仅是在记录上打一个删除标记，并没有真正将它删除掉。

    大家应该还记得，在一组undo日志中的Undo Log Header部分有个名为TRX_UNDO_DEL_MARKS的属性，用来标记本组的undo日志中是否包含因delete mark操作二产生的日志，这些打了删除标记的记录也不能一直存在吧？

为了节约空间，我们应该在合适的时候把update undo日志以及仅仅被标记为删除的记录彻底删除，这个删除操作就是purge。不过于问题的关健在于这个合适的时候到底是什么时候？

update undo日志和被标记删除的记录都是为了支持MVCC而存在的，只要系统中最早产生的ReadView不在访问它们，它们的使命就此结束了，就可以丢进历史的垃圾堆里了。一个ReadView在什么时候才肯定不会访问某个事物执行过程中产生的undo日志呢？其实，只要我们能保证生成ReadView时，某个事物已经提交，那么该ReadView肯定就不需要访问事物运行过程中产生的undo日志了（因为该事物所改动的最新版均对该ReadView可见）。

InnoDB为此做了两件事：

  • 在一个事务提交时，会为这个事务生成一个名为事务no的值，该值用来表示事务提交的顺序。先提交的事务的事务no值小，后提交的事务的事务no值大。

    别忘看在一组undo日志中对应的Undo Log Header部分有一个名为TRX_UNDO_TRX_NO的属性。当事务提交时，就把该事物对应的事务no值填到这个属性中，因为事务no代表着各个事务提交的顺序，而History链表又是按照事务提交的顺序来排列各种undo日志，所以History链表中各组的undo日志也是按照对应的事务no来排序的。

  • 一个ReadView结构除了包含前面学习的几个属性之外，还会包含一个事务no的属性。在生成一个ReadView时，会把比当前系统中最大的事务no的值还大1的值赋予这个属性。

InnoDB还把当前系统中所有的ReadView按照创建时间连成了一个链表。当执行purge操作时（这个purge操作是在专门的后线程中执行的），就把系统中最早生成的ReadView给取出来。如果当前系统中不存在ReadView,那就现场创建一个（新创建的这个ReadView的事务no值肯定比当前已经提交的事务的事务no值大）。然后从各个回滚段的History链表中取出事务no值较小的各组undo日志。如果第一组undo日志的事务no小于当前系统最早生成的ReadView的事务no，就意味着该组undo日志没有用了，就会从History链表中移除，并且释放掉它们占用的存储空间。如果该组undo日志包含因delete mark操作而产生的undo日志（TRX_UNDO_DEL_MARKS的属性值为1），那么也需要将对应的标记为删除的记录给彻底删除。

这里我们需要注意的一点，当前系统中系统最早生成的ReadView决定了purge操作中可以清理哪些update undo日志以及打了删除标记的记录。如果某个事物使用的REPEATABLE READ隔离级别，那么该事物一直复用最初产生的ReadView。假如这个事务运行了很久，一直没有提交，那么最早产生的ReadView会一直不释放。系统中updtae undo日志和打了删除标记的记录就会越来越多，表空间对应的文件也会越来越大，一条记录的版本链将越来越长，从而影响系统的性能。

五、总结

  • 并发的事务在运行过程中会出现一些可能引发一致性问题的现象，具体如下:

    • 脏写：一个事务修改了另一个未提交事务修改过的数据
    • 脏读：一个事务读到了另一个未提交事务修改过的数据
    • 不可重复读：一个事务修改了另一个未提交事务读取的数据
    • 幻读：一个事务先根据某些搜索条件查询出一些记录，在该事务未提交时，另一个事务写入了一些符合那些搜索条件的记录

  • SOL标准中的4种隔离级别：

    • READ UNCOMMITTED：可能发生脏读、不可重复读和幻读现象

    • READCOMMITTED：可能发生不可重复读和幻读现象，但是不可能发生脏读现象。

    • REPEATABLE READ：可能发生幻读现象，但是不可能发生脏读和不可重复读的现象

    • SERIALIZABLE：各种现象都不可以发生。

    实际上，MySOL在REPEATABLE READ隔离级别下是可以在很大程度上禁止出现幻读现象的。

  • 下面的语句用来设置事务的隔离级别:
    SET [GLOBALI|SESSION|PERSIST] TRANSACTION ISOLATION LEVEL Level;

  • 聚簇索引记录和undo日志中的roll pointer属性可以串连成一个记录的版本链

  • 通过生成ReadView来判断记录的某个版本的可见性，其中READ COMMITTD在每一次进行普通SELECT操作前都会生成一个ReadView，而REPEATABLE READ只在第一次进行普通SELECT操作前生成一个ReadView，之后的查询操作都重复使用这个ReadView

  • 当前系统中，如果最早生成的 ReadView 不再访问undo日志以及打了删除标记的记录则可以通过purge操作将它们清除。

至此今天的学习就到此结束了，愿您成为坚不可摧的自己~~~
​

You can’t connect the dots looking forward; you can only connect them looking backwards. So you have to trust that the dots will somehow connect in your future.You have to trust in something - your gut, destiny, life, karma, whatever. This approach has never let me down, and it has made all the difference in my life

如果我的内容对你有帮助，请 点赞、评论、收藏，创作不易，大家的支持就是我坚持下去的动力！

本文章参考：小孩子《MySQL是怎样运行的》
在这里插入图片描述

来源地址：https://blog.csdn.net/liang921119/article/details/131008641

您可能感兴趣的文档:
点击免费下载>>软考高级考试备考技巧/历年真题/备考精华资料

--结束END--

本文标题: 一文带你了解MySQL之事务隔离级别和MVCC

本文链接: https://www.lsjlt.com/news/371857.html(转载时请注明来源链接)

有问题或投稿请发送至: 邮箱/279061341@qq.com    QQ/279061341

本篇文章演示代码以及资料文档资料下载

下载Word文档到电脑，方便收藏和打印～

下载Word文档
去做题
猜你喜欢
  • 一文带你了解MySQL之事务隔离级别和MVCC
    目录 一、数据准备二、事务隔离级别2.1 事务并发执行遇到的问题2.2 SQL标准中的四种隔离级别2.3 MySQL中支持的四种隔离级别 三、MVCC3.1 版本链3.2 ReadView3.2.1 READ COMMITTED...
    99+
    2023-08-16
    mysql 数据库 数据库开发 大数据 数据库架构
  • MYSQL事务的隔离级别与MVCC
    目录前言1. 事务（transaction）的起源1.1. 事务的定义1.2. 哪些存储引擎支持事务2. MySQL的事务语法2.1. 自动提交2.2. 手动操作事务2.2.1. 开...
    99+
    2024-04-02
  • MySQL事务和MVCC怎么实现隔离级别
    这篇文章主要讲解了“MySQL事务和MVCC怎么实现隔离级别”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习“MySQL事务和MVCC怎么实现隔离级别”吧！数据...
    99+
    2024-04-02
  • MySQL事务、隔离级别及MVCC是什么
    这篇文章主要介绍了MySQL事务、隔离级别及MVCC是什么，具有一定借鉴价值，需要的朋友可以参考下。希望大家阅读完这篇文章后大有收获。下面让小编带着大家一起了解一下。mysql教程栏目介绍MySQL相关的事...
    99+
    2022-11-30
    mysql vcc
  • MySQL事务与MVCC怎么实现隔离级别
    这篇文章给大家介绍MySQL事务与MVCC怎么实现隔离级别，内容非常详细，感兴趣的小伙伴们可以参考借鉴，希望对大家能有所帮助。数据库事务介绍事务的四大特性(ACID)原子性(atomicity)： 事务的最...
    99+
    2024-04-02
  • Mysql之事务提交和隔离级别
    Mysql之事务提交和隔离级别一、事务是什么事务简言之就是一组SQL执行要么全部成功，要么全部失败。MYSQL的事务在存储引擎层实现。1、事务都有ACID特性：原子性（Atomicity）：一个事务必须被视...
    99+
    2024-04-02
  • PostgreSQL DBA(23) - MVCC#3(事务快照和隔离级别)
    如前所述，PostgreSQL使...
    99+
    2024-04-02
  • MySQL事务隔离级别详解
    一、什么是事务？ 事务是逻辑上的一组操作，要么全执行，要么全不执行。 事务最经典栗子也经常被拿出来的栗子就是银行转账了。比如小明要给小红转账1000元，这个转账会涉及到两个关键操作：将小明的余额减1000元，将小红的余额减1000元。万一这...
    99+
    2023-08-30
    mysql
  • mysql 5.5 innodb事务隔离级别之开篇
    小结:     1,事务隔离级别隶属于rdbms的acid之i,即隔离性     2,不同的事务隔离级别，导致一致性读的行为有所差异     ...
    99+
    2024-04-02
  • MySql学习笔记之事务隔离级别详解
    背景 说的事务，大家应该都不陌生，开发用到 MySql 数据库的时候，通常会用到事务。其中比较经典的例子就是转账，比如你要给小明转 50 块钱，而此时你的银行卡也就只有 50 块钱...
    99+
    2024-04-02
  • 如何理解MySQL中的事务隔离级别
    这篇文章给大家介绍如何理解MySQL中的事务隔离级别，内容非常详细，感兴趣的小伙伴们可以参考借鉴，希望对大家能有所帮助。很多小伙伴对 MySQL 的隔离级别一直心存疑惑，其实这个问题一点都不难，关键看怎么讲...
    99+
    2024-04-02
  • 一文带你了解MySQL中的事务
    目录一.什么是事务二.事务操作演示小结三.事务的特性四.事务的隔离级别概述四种隔离级别脏读、不可重复读、幻读操作一.什么是事务 在mysql中的事务(Transaction）是由存储引擎实现的，在MySQL中，只有Inn...
    99+
    2023-02-17
    MySQL事务使用 MySQL事务
  • mysql事务和隔离级别底层原理浅析
    目录前言一、事务底层原理浅析原子性：持久性隔离性：一致性：二、隔离级别底层原理浅析三、总结前言 首先回顾一下什么是事务，事务是数据库操作的最小工作单元，是作为单个逻辑工作单元执行的一...
    99+
    2024-04-02
  • 一文带你了解MySQL之锁
    目录 一、解决并发事务带来问题的两种基本方式1.1 一致性读（Consistent Reads）1.2 锁定读（Locking Reads）1.2.1 共享锁和独占锁1.2.2 锁定读的语句 1.3 写操作 二、多粒度锁三...
    99+
    2023-08-16
    mysql 数据库 大数据 数据库架构 数据库开发
  • 深入理解MySQL事务的4种隔离级别
    目录1 简介2 什么是数据库事务？2.1 事务的四大特性（ACID）3 并发事务会导致的问题3.1 本文会使用到的 SQL 语句3.1.1 示例表结构3.1.2 查询事务的默认隔离级...
    99+
    2024-04-02
  • Mysql事务隔离级别之读提交的示例分析
    这篇文章主要为大家展示了“Mysql事务隔离级别之读提交的示例分析”，内容简而易懂，条理清晰，希望能够帮助大家解决疑惑，下面让小编带领大家一起研究并学习一下“Mysql事务隔离级别之读提交的示例分析”这篇文...
    99+
    2024-04-02
  • 一文带你了解MySQL之约束
    在SQL标准中，一共规定了6种不同的约束，包括非空约束，唯一约束和检查约束等，而在MySQL中是不支持检查约束的，所以这篇文章先对其余5种约束做一个详解和练习。 文章目录 1. 约束的概念2. 约束的分类3. 非空约束4. 唯一...
    99+
    2023-08-17
    mysql 数据库 sql
  • MySQL核心技术原理之：MySQL事务隔离级别与原理
    作者：禅与计算机程序设计艺术 1.简介 随着互联网的飞速发展，网站的并发访问量越来越大，数据库的事务处理能力要求也越来越高。在这种情况下，如果应用服务器直接对数据库进行操作，可能会出现资源竞争、数据...
    99+
    2023-10-20
    大数据 人工智能 语言模型 Java Python 架构设计
  • 怎么在MySQL中查看和修改事务隔离级别
    今天就跟大家聊聊有关怎么在MySQL中查看和修改事务隔离级别，可能很多人都不太了解，为了让大家更加了解，小编给大家总结了以下内容，希望大家根据这篇文章可以有所收获。查看事务隔离级别在 MySQL 中，可以通...
    99+
    2024-04-02
  • 【MySQL】一文带你彻底了解事务机制
    文章目录 何谓事务？事务的特性：ACID事务的操作隔离性引发的并发问题不可重复读和幻读有什么区别 事务的隔离级别MySQL 的隔离级别是基于锁实现的吗？默认隔离级别解决幻读的方法总结 我们设想一个场景，这个场景中我们需...
    99+
    2023-08-17
    mysql 数据库 服务器 隔离级别 幻读
软考高级职称资格查询
iis服务器助手广告
软考职称历年真题下载
软考职称资料下载
热门wiki
近期文章
推荐阅读
热门问答
热门标签
编程网，编程工程师的家园，是目前国内优秀的开源技术社区之一，形成了由开源软件库、代码分享、资讯、协作翻译、讨论区和博客等几大频道内容，为IT开发者提供了一个发现、使用、并交流开源技术的平台。

  • 官方手机版

  • 微信公众号

  • 商务合作