Mysql的四个隔离级别 与mvcc（2）

2024-09-05 22:48
文章标签 mysql 级别 mvcc 隔离 database 四个

本文主要是介绍Mysql的四个隔离级别 与mvcc（2），希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

特殊颜色字体为自己总结，关于mvcc直接看最后面的。

一、Mysql的四个隔离级别

预备工作：

  • 先创建一个test数据库及account表，

create database test;
use test;
create table account(
id int not null,
balance float not null,PRIMARY KEY ( id)
)
  • 向account中插入两条测试数据

INSERT INTO table(id,balance)
VALUES (1,1000);
INSERT INTO table(id,balance)
VALUES (2,1000);

开启两个控制台窗口，当做两个用户（A和B）

1.1 READ UNCOMMITTED（未提交读）

也即RU，在READ UNCOMMITTED级别，事务中的修改，即使没有提交，对其他事务也都是可见的。事务可以读取未提交的数据，这也被称为脏读（Dirty Read）。这个级别会导致很多问题，从性能上来说，READ UNCOMMITTED不会比其他的级别好太多，但却缺乏其他级别的很多好处，除非真的有非常必要的理由，在实际应用中一般很少使用。

A用户操作如下：

set session transaction isolation level read uncommitted;
start transaction;
select * from account;

结果如下：

001.png

B用户操作如下：

set session transaction isolation level read uncommitted;
start transaction;
update account set balance=balance+200 where id = 1;

随后在A用户终端中查询数据，结果如下：

002.png

可以看到B用户并未提交事务，但是A用户却能读到未提交的数据，这就是脏读。

1.2 READ COMMITTED（提交读）

即RC，大多数数据库系统的默认隔离级别都是READ COMMTTED（但MySQL不是，Mysql的默认隔离级别是REPEATABLE READ）。READ COMMITTED满足前面提到的隔离性的简单定义：一个事务开始时，只能”看见”已经提交的事务所做的修改。换句话说，一个事务从开始直到提交之前，所做的任何修改对其他事务都是不可见的。这个级别有时候叫做不可重复读（nonrepeatble read），因为两次执行同样的查询，可能会得到不一样的结果（因为每次select 都会创建一个快照都readview）。以例子说明：

我们将用户B所在的会话当前事务隔离级别设置为read commited。

set session transaction isolation level read committed;

在A所在的会话中执行

update account set balance=balance-200 where id = 1;

在B用户的会话中查询：

select * from account;

结果如下：

003.png

发现数据没有变，还是1000，说明可以避免脏读了。
接着A用户会话中将事务提交：

commit;

再次在B中查询，结果如下：

004.png

可以看到，B用户读取到了A用户提交的数据。这么做有什么问题么？那就是我们在会话B同一个事务中，读取到两次不同的结果。这就造成了不可重复读，就是两次读取的结果不同。

因为每次select 都会创建一个快照读readview，所以即使在一个事务中，前后不同的select语句的readview 中存储的未提交的事务id也不同。

1.3 REPEATABLE READ(可重复读)

REPEATABLE READ解决了脏读的问题。该隔离级别保证了在同一个事务中多次读取同样记录结果是一致的。但是理论上，可重复读隔离级别还是无法解决另外一个幻读（Phantom Read）的问题。所谓幻读，指的是当某个事务在读取某个范围内的记录时，另一个事务又在该范围内插入了新的记录，当之前的事务再次读取该范围的记录时，会产生幻行（Phantom Row）。Mysql的RR是

在事务start时生成一个readview，同一个事务中后面每次select 都会使用同一个readview。

以下为幻读的示例：

我们将用户B所在的会话当前事务隔离级别设置为repeatable read。

set session transaction isolation level repeatable read;
start transaction;

接着在B中查询数据：

005.png

两条。
然后我们到A用户会话中插入一条数据：

insert into account(id,balance) value(3,1000);

在A中查看是否添加成功：

006.png

成功，有三条数据。
回到用户B会话中，再次查询：

007.png


发现没有变还是两条。这时，用户B想插入一条id=3，balance=1000的数据：

insert into account(id,balance) value(3,1000);

会报错：

008.png


说是主键重复了，可是B用户刚刚查询并没有id=3的记录。这就是幻读现象。
主要是因为 select 是快照读（rr级别只会读取当前事务开始前已提交的事务，rc 级别会读取当前执行的select 语句前已提交的事务），而insert update delete是当前读（即会读取其他事务未提交的）。

幻读的解决办法可以在select时加间隙锁、把select 语句页改为当前读。如select for update 就可以对查询到的数据枷锁，并且使用当前读而不是快照读readview.

1.4 SERIALIZABLE（串行化）

SERIALIZABLE是最高的隔离级别。它通过强制事务串行执行，避免了前面说的幻读的问题。简单来说，SERIALIZABLE会在读取每一行数据都加锁，所以可能导致大量的超时和锁争用问题。实际应用中也很少用到这个隔离级别，只有在非常需要确保数据的一致性而且可以接受没有并发的情况下，才考虑采用该级别。

二、MVCC

mvcc 并不是为了解决幻读，而是用于版本控制（如inndoe的读已提交，可重复读 两种隔离级别 都是基于mvcc实现的）

mvcc也解决不了幻读，幻读的原因是select的快照读与insert、update、delete的当前读 造成的问题。

首先介绍一下几个概念：
读锁：也叫共享锁、S锁，若事务T对数据对象A加上S锁，则事务T可以读A但不能修改A，其他事务只能再对A加S锁，而不能加X锁，直到T释放A上的S 锁。这保证了其他事务可以读A，但在T释放A上的S锁之前不能对A做任何修改。
写锁：又称排他锁、X锁。若事务T对数据对象A加上X锁，事务T可以读A也可以修改A，其他事务不能再对A加任何锁，直到T释放A上的锁。这保证了其他事务在T释放A上的锁之前不能再读取和修改A。
表锁：操作对象是数据表。Mysql大多数锁策略都支持，是系统开销最低但并发性最低的一个锁策略。事务t对整个表加读锁，则其他事务可读不可写，若加写锁，则其他事务增删改都不行。
行级锁：操作对象是数据表中的一行。是MVCC技术用的比较多的。行级锁对系统开销较大，但处理高并发较好。

MVCC使得大部分支持行锁的事务引擎，不再单纯的使用行锁来进行数据库的并发控制，取而代之的是把数据库的行锁与行的多个版本结合起来，只需要很小的开销,就可以实现非锁定读，从而大大提高数据库系统的并发性能。

2.1 重要字段

Mysql Innodb中行记录的存储格式，除了最基本的行信息外，还会有一些额外的字段，这里主要介绍和MVCC有关的字段：DATA_TRX_ID和DATA_ROLL_PTR。

DATA_TRX_ID：用来标识最近一次对本行记录做修改(insert|update)的事务的标识符, 即最后一次修改(insert|update)本行记录的事务id。

DATA_ROLL_PTR：指写入回滚段(rollback segment)的 undo log record (撤销日志记录记录)。如果一行记录被更新, 则 undo log record 包含 '重建该行记录被更新之前内容' 所必须的信息。

借图举例：出自<<唐成－2016PG大会-数据库多版本实现内幕.pdf>>

009.png

当插入的是一条新数据时，记录上对应的回滚段指针为NULL

010.png

DB_TRX_ID记录了行的创建的时间,删除的时间在每个事件发生的时候，每行存储版本号，而不是存储事件实际发生的时间。每次事物的开始这个版本号都会增加。自记录时间开始，每个事物都会保存记录的系统版本号。依照事物的版本来检查每行的版本号。

  • 在insert操作时， “创建时间”=DB_TRX_ID，这时，“删除时间”是未定义的；
  • 在update操作时，复制新增行的“创建时间”=DB_TRX_ID，删除时间未定义，旧数据行“创建时间”不变，删除时间=该事务DB_TRX_ID；
  • 在delete操作时，相应数据行的“创建时间”不变，删除时间=该事务的DB_ROW_ID；
  • 在select操作时，对两者都不修改，只读相应的数据。

2.2 原理

InnoDB的MVCC，是通过在每行纪录后面保存两个隐藏的列来实现的。这两个列，一个保存了行的创建时间，一个保存了行的过期时间（或删除时间），当然存储的并不是实际的时间值，而是系统版本号。每开始一个新的事务，系统版本号都会自动递增。事务开始时刻的系统版本号会作为事务的版本号，用来和查询到的每行纪录的版本号进行比较。在REPEATABLE READ隔离级别下，MVCC具体的操作如下：

SELECT
InnoDB会根据以下两个条件检查每行纪录：

  • InnoDB只查找版本早于当前事务版本的数据行，即，行的系统版本号小于或等于事务的系统版本号，这样可以确保事务读取的行，要么是在事务开始前已经存在的，要么是事务自身插入或者修改过的。
  • 行的删除版本，要么未定义，要么大于当前事务版本号。这样可以确保事务读取到的行，在事务开始之前未被删除。
    只有符合上述两个条件的纪录，才能作为查询结果返回。

INSERT

  • InnoDB为插入的每一行保存当前系统版本号作为行版本号。

DELETE

  • InnoDB为删除的每一行保存当前系统版本号作为行删除标识。

UPDATE

  • InnoDB为插入一行新纪录，保存当前系统版本号作为行版本号，同时，保存当前系统版本号到原来的行作为行删除标识。

优点：
保存这两个额外系统版本号，使大多数读操作都可以不用加锁。这样设计使得读数据操作很简单，性能很好。

缺点：
每行纪录都需要额外的存储空间，需要做更多的行检查工作，以及一些额外的维护工作。

读到这里，也许会有一个疑问，考虑如下执行序列：

011.png

按照之前的Select规则，会话B 的事务是在 会话A的后面开启的，那么B的事务版本号大于A的事务版本号。这样在A中插入的数据在未提交的情况下，B可以读到A修改的数据，这不就自相矛盾了么？其实不然，InnoDB每个事务在开始的时候，会将当前系统中的活跃事务列表（trx_sys->trx_list）创建一个副本（read view），然后一致性读去比较记录的tx id的时候，并不是根据当前事务的tx id，而是根据read view最早一个事务的tx id（read view->up_limit_id）来做比较的，这样就能确保在事务B之前没有提交的所有事务的变更，B事务都是看不到的。如下图所示：

当我们执行一个select操作时，执行引擎会查询到 insert、update、delete操作后未提交或者已提交的数据。但是为了实现select的 读已提交、可重复读。就需要用到mvcc版本控制，产生一个快照读readview列表、列表中存储着当前活跃的也就是未提交的事务id。通过对比查询到的数据行的事务id 和当前readview中的，来判断是否返回此行数据。

其实判断主要就两步：

设该行的当前事务id为trx_id_0，read view中最早的事务id为trx_id_1, 最迟的事务id为trx_id_2

判断trx_id_0是否不在 reqdview （select时的活跃事务列表）中，如果不在说明已经提交了，如果在说明还没提交。

若上一步判断trx_id_0为已经提交了的事务，继续判断是否是在创建 reqdview前提交的，当trx_id_0<=trx_id_2 是表名是在select前就存在的事务。

也就是满足 trx_id_0<=trx_id_2，且trx_id_0 不在reqdview 中 两个条件，即能返回此记录。

注意（个人理解）：trx_id_2 应为select 前的活跃的最大事务id，而不是当前select的事务id，不然就没办法解释 select 能查询到同一个事务中insert的数据了，因为同一事物中select 和insert的事务id一样。

更详细判断步骤如下：

  1. 设该行的当前事务id为trx_id_0，read view中最早的事务id为trx_id_1, 最迟的事务id为trx_id_2。
  2. 如果trx_id_0< trx_id_1的话，那么表明该行记录所在的事务已经在本次新事务创建之前就提交了，所以该行记录的当前值是可见的。跳到步骤6.
  3. 如果trx_id_0>trx_id_2的话，那么表明该行记录所在的事务在本次新事务创建之后才开启，所以该行记录的当前值不可见.跳到步骤5。
  4. 如果trx_id_1<=trx_id_0<=trx_id_2, 那么表明该行记录所在事务在本次新事务创建的时候处于活动状态，从trx_id_1到trx_id_2进行遍历，如果trx_id_0等于他们之中的某个事务id的话，那么不可见。跳到步骤5。如果trx_id_0 在read view中不存在，那说明已经提交了事务，跳到步骤6.
  5. 从该行记录的DB_ROLL_PTR指针所指向的回滚段中取出最新的undo-log的版本号，将它赋值该trx_id_0，然后跳到步骤2.
  6. 将该可见行的值返回。


————————————————
版权声明：本文为CSDN博主「toforu」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/u013378306/article/details/107637410

 

读已提交：即使在一个事务中，每次select 语句都会生成一个最新的readview（注意若对select加for update 就变成了当前读）。

可重复读：只在事务开始时，生成一个readview，在一个事务中，所有的select 都使用事务开始时生成的readview（注意若对select加for update 就变成了当前读）。

幻读解决方案：对select 加for update 等使其变为当前读。

作者：小北觅
链接：https://www.jianshu.com/p/db334404d909
来源：简书
著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。

这篇关于Mysql的四个隔离级别 与mvcc（2）的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！


http://www.chinasem.cn/article/1140279。

相关文章

SQL中的外键约束

SQL中的外键约束

外键约束用于表示两张表中的指标连接关系。外键约束的作用主要有以下三点： 1.确保子表中的某个字段（外键）只能引用父表中的有效记录2.主表中的列被删除时，子表中的关联列也会被删除3.主表中的列更新时，子表中的关联元素也会被更新 子表中的元素指向主表 以下是一个外键约束的实例展示
阅读更多...
基于MySQL Binlog的Elasticsearch数据同步实践

基于MySQL Binlog的Elasticsearch数据同步实践

一、为什么要做 随着马蜂窝的逐渐发展，我们的业务数据越来越多，单纯使用 MySQL 已经不能满足我们的数据查询需求，例如对于商品、订单等数据的多维度检索。 使用 Elasticsearch 存储业务数据可以很好的解决我们业务中的搜索需求。而数据进行异构存储后，随之而来的就是数据同步的问题。 二、现有方法及问题 对于数据同步，我们目前的解决方案是建立数据中间表。把需要检索的业务数据，统一放到一张M
阅读更多...
如何去写一手好SQL

如何去写一手好SQL

MySQL性能 最大数据量 抛开数据量和并发数，谈性能都是耍流氓。MySQL没有限制单表最大记录数，它取决于操作系统对文件大小的限制。 《阿里巴巴Java开发手册》提出单表行数超过500万行或者单表容量超过2GB，才推荐分库分表。性能由综合因素决定，抛开业务复杂度，影响程度依次是硬件配置、MySQL配置、数据表设计、索引优化。500万这个值仅供参考，并非铁律。 博主曾经操作过超过4亿行数据
阅读更多...
性能分析之MySQL索引实战案例

性能分析之MySQL索引实战案例

文章目录 一、前言二、准备三、MySQL索引优化四、MySQL 索引知识回顾五、总结 一、前言 在上一讲性能工具之 JProfiler 简单登录案例分析实战中已经发现SQL没有建立索引问题，本文将一起从代码层去分析为什么没有建立索引？ 开源ERP项目地址：https://gitee.com/jishenghua/JSH_ERP 二、准备 打开IDEA找到登录请求资源路径位置
阅读更多...
MySQL数据库宕机，启动不起来，教你一招搞定！

MySQL数据库宕机，启动不起来，教你一招搞定！

作者介绍：老苏，10余年DBA工作运维经验，擅长Oracle、MySQL、PG、Mongodb数据库运维（如安装迁移，性能优化、故障应急处理等）公众号：老苏畅谈运维欢迎关注本人公众号，更多精彩与您分享。 MySQL数据库宕机，数据页损坏问题，启动不起来，该如何排查和解决，本文将为你说明具体的排查过程。 查看MySQL error日志 查看 MySQL error日志，排查哪个表（表空间
阅读更多...
MySQL高性能优化规范

MySQL高性能优化规范

前言：      笔者最近上班途中突然想丰富下自己的数据库优化技能。于是在查阅了多篇文章后，总结出了这篇！ 数据库命令规范 所有数据库对象名称必须使用小写字母并用下划线分割 所有数据库对象名称禁止使用mysql保留关键字（如果表名中包含关键字查询时，需要将其用单引号括起来） 数据库对象的命名要能做到见名识意，并且最后不要超过32个字符 临时库表必须以tmp_为前缀并以日期为后缀，备份
阅读更多...
[MySQL表的增删改查-进阶]

[MySQL表的增删改查-进阶]

🌈个人主页：努力学编程’ ⛅个人推荐： c语言从初阶到进阶 JavaEE详解 数据结构 ⚡学好数据结构，刷题刻不容缓：点击一起刷题 🌙心灵鸡汤：总有人要赢，为什么不能是我呢 💻💻💻数据库约束 🔭🔭🔭约束类型 not null: 指示某列不能存储 NULL 值unique: 保证某列的每行必须有唯一的值default: 规定没有给列赋值时的默认值.primary key:
阅读更多...
MySQL-CRUD入门1

MySQL-CRUD入门1

文章目录 认识配置文件client节点mysql节点mysqld节点 数据的添加(Create)添加一行数据添加多行数据两种添加数据的效率对比 数据的查询(Retrieve)全列查询指定列查询查询中带有表达式关于字面量关于as重命名 临时表引入distinct去重order by 排序关于NULL 认识配置文件 在我们的MySQL服务安装好了之后, 会有一个配置文件, 也就
阅读更多...
Java 连接Sql sever 2008

Java 连接Sql sever 2008

Java 连接Sql sever 2008 /Sql sever 2008 R2 import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.Statement; public class TestJDBC
阅读更多...
Mysql BLOB类型介绍

Mysql BLOB类型介绍

BLOB类型的字段用于存储二进制数据 在MySQL中，BLOB类型，包括：TinyBlob、Blob、MediumBlob、LongBlob，这几个类型之间的唯一区别是在存储的大小不同。 TinyBlob 最大 255 Blob 最大 65K MediumBlob 最大 16M LongBlob 最大 4G
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2