浅析Mysql的隔离级别及MVCC

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,高可用系列 2核4GB
简介: 浅析Mysql的隔离级别及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;


结果如下:


image.png


B用户操作如下:


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


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


image.png


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


1.2 READ COMMITTED(提交读)


即RC,大多数数据库系统的默认隔离级别都是READ COMMTTED(但MySQL不是,Mysql的默认隔离级别是REPEATABLE READ)。


READ COMMITTED满足前面提到的隔离性的简单定义:一个事务开始时,只能”看见”已经提交的事务所做的修改。换句话说,一个事务从开始直到提交之前,所做的任何修改对其他事务都是不可见的。


这个级别有时候叫做不可重复读(nonrepeatble read),因为两次执行同样的查询,可能会得到不一样的结果。以例子说明:


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


set session transaction isolation level read committed;


在A所在的会话中执行


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


在B用户的会话中查询:


select * from account;


结果如下:




image.png


发现数据没有变,还是1000,说明可以避免脏读了。

接着A用户会话中将事务提交:


commit;

再次在B中查询,结果如下:


image.png


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


1.3 REPEATABLE READ(可重复读)


REPEATABLE READ解决了脏读的问题。该隔离级别保证了在同一个事务中多次读取同样记录结果是一致的。但是理论上,可重复读隔离级别还是无法解决另外一个幻读(Phantom Read)的问题。


所谓幻读,指的是当某个事务在读取某个范围内的记录时,另一个事务又在该范围内插入了新的记录,当之前的事务再次读取该范围的记录时,会产生幻行(Phantom Row)。


Mysql的RR是由“行排它锁+MVCC”一起实现的。


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


set session transaction isolation level repeatable read;
start transaction;


接着在B中查询数据:




image.png


两条。

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


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


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



image.png


成功,有三条数据。

回到用户B会话中,再次查询:


image.png


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


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


会报错:


image.png


说是主键重复了,可是B用户刚刚查询并没有id=3的记录。这就是幻读现象。

我的理解是:不可重复读指的是update操作,而幻读指的是insert或delete操作。


1.4 SERIALIZABLE(串行化)


SERIALIZABLE是最高的隔离级别。它通过强制事务串行执行,避免了前面说的幻读的问题。简单来说,SERIALIZABLE会在读取每一行数据都加锁,所以可能导致大量的超时和锁争用问题。


实际应用中也很少用到这个隔离级别,只有在非常需要确保数据的一致性而且可以接受没有并发的情况下,才考虑采用该级别。


二、MVCC


首先介绍一下几个概念:

读锁:也叫共享锁、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>>


image.png


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



image.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只查找版本早于当前事务版本的数据行,即,行的系统版本号小于或等于事务的系统版本号,这样可以确保事务读取的行,要么是在事务开始前已经存在的,要么是事务自身插入或者修改过的。
  • 行的删除版本,要么未定义,要么大于当前事务版本号。这样可以确保事务读取到的行,在事务开始之前未被删除。

只有符合上述两个条件的纪录,才能作为查询结果返回。


NSERT


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


DELETE


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


    UPDATE


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


    优点:

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


    缺点:

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


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


    image.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事务都是看不到的。如下图所示:


    image.png


    结束。rm -rf / 跑



    参考资料: 《唐成-2016PG大会-数据库多版本实现内幕.pdf》



    相关实践学习
    如何快速连接云数据库RDS MySQL
    本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL,然后进行数据表的CRUD操作。
    全面了解阿里云能为你做什么
    阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
    目录
    相关文章
    |
    1月前
    |
    存储 关系型数据库 MySQL
    MySQL MVCC全面解读:掌握并发控制的核心机制
    【10月更文挑战第15天】 在数据库管理系统中,MySQL的InnoDB存储引擎采用了一种称为MVCC(Multi-Version Concurrency Control,多版本并发控制)的技术来处理事务的并发访问。MVCC不仅提高了数据库的并发性能,还保证了事务的隔离性。本文将深入探讨MySQL中的MVCC机制,为你在面试中遇到的相关问题提供全面的解答。
    211 2
    |
    6天前
    |
    SQL 关系型数据库 MySQL
    MySQL进阶突击系列(04)事务隔离级别、AICD、CAP、BASE原则一直搞不懂? | 看这篇就够了
    本文详细介绍了数据库事务的四大特性(AICD原则),包括原子性、隔离性、一致性和持久性,并深入探讨了事务并发问题与隔离级别。同时,文章还讲解了分布式系统中的CAP理论及其不可能三角关系,以及BASE原则在分布式系统设计中的应用。通过具体案例和图解,帮助读者理解事务处理的核心概念和最佳实践,为应对相关技术面试提供了全面的知识准备。
    |
    2月前
    |
    存储 SQL 关系型数据库
    MySQL的事务隔离级别
    【10月更文挑战第17天】MySQL的事务隔离级别
    127 43
    |
    27天前
    |
    关系型数据库 MySQL 数据库
    MySQL事务隔离级别及默认隔离级别的设置
    在数据库系统中,事务隔离级别是一个关键的概念,它决定了事务在并发执行时如何相互隔离。MySQL提供了四种事务隔离级别,每种级别都解决了不同的并发问题。本文将详细介绍这些隔离级别以及MySQL的默认隔离级别。
    |
    1月前
    |
    存储 关系型数据库 MySQL
    MySQL MVCC深度解析:掌握并发控制的艺术
    【10月更文挑战第23天】 在数据库领域,MVCC(Multi-Version Concurrency Control,多版本并发控制)是一种重要的并发控制机制,它允许多个事务并发执行而不产生冲突。MySQL作为广泛使用的数据库系统,其InnoDB存储引擎就采用了MVCC来处理事务。本文将深入探讨MySQL中的MVCC机制,帮助你在面试中自信应对相关问题。
    160 3
    |
    2月前
    |
    存储 关系型数据库 MySQL
    RR隔离级别在MySQL中的实现与幻读问题探讨
    【10月更文挑战第3天】在数据库管理系统中,事务隔离级别是确保数据一致性和并发性能的关键要素。MySQL作为广泛使用的关系型数据库管理系统,支持多种事务隔离级别,其中可重复读(Repeatable Read,简称RR)是其默认隔离级别。本文将深入探讨RR隔离级别在MySQL中的实现原理,以及RR隔离级别下幻读问题的产生与解决方案。
    126 2
    |
    3月前
    |
    关系型数据库 MySQL 数据库
    MySQL高级篇——MVCC多版本并发控制
    什么是MVCC、快照读与当前读、隐藏字段、Undo Log版本链、ReadView、举例说明、InnoDB 解决幻读问题
    |
    3月前
    |
    关系型数据库 MySQL 数据库
    深入理解MySQL数据库隔离级别
    深入理解MySQL数据库隔离级别
    146 1
    |
    4月前
    |
    人工智能 小程序 关系型数据库
    【MySQL】黑悟空都掌握的技能,数据库隔离级别全攻略
    本文以热门游戏《黑神话:悟空》为契机,深入浅出地解析了数据库事务的四种隔离级别:读未提交、读已提交、可重复读和串行化。通过具体示例,展示了不同隔离级别下的事务行为差异及可能遇到的问题,如脏读、不可重复读和幻读等。此外,还介绍了在MySQL中设置隔离级别的方法,包括全局和会话级别的调整,并通过实操演示了各隔离级别下的具体效果。本文旨在帮助开发者更好地理解和运用事务隔离级别,以提升数据库应用的一致性和性能。
    151 2
    【MySQL】黑悟空都掌握的技能,数据库隔离级别全攻略
    |
    3月前
    |
    SQL Oracle 关系型数据库
    详解 MySQL 的事务以及隔离级别
    详解 MySQL 的事务以及隔离级别
    57 0