详解MySQL中事务隔离级别的实现原理

前言

说到数据库事务,大家脑子里一定很容易蹦出一堆事务的相关知识,如事务的ACID特性,隔离级别,解决的问题(脏读,不可重复读,幻读)等等,但是可能很少有人真正的清楚事务的这些特性又是怎么实现的,为什么要有四个隔离级别。

今天我们就先来聊聊MySQL中事务的隔离性的实现原理,后续还会继续出文章分析其他特性的实现原理。

当然MySQL博大精深,文章疏漏之处在所难免,欢迎批评指正。

说明

MySQL的事务实现逻辑是位于引擎层的,并且不是所有的引擎都支持事务的,下面的说明都是以InnoDB引擎为基准。

定义

隔离性(isolation)指的是不同事务先后提交并执行后,最终呈现出来的效果是串行的,也就是说,对于事务来说,它在执行过程中,感知到的数据变化应该只有自己操作引起的,不存在其他事务引发的数据变化。

隔离性解决的是并发事务出现的问题。

标准SQL隔离级别

隔离性最简单的实现方式就是各个事务都串行执行了,如果前面的事务还没有执行完毕,后面的事务就都等待。但是这样的实现方式很明显并发效率不高,并不适合在实际环境中使用。

为了解决上述问题,实现不同程度的并发控制,SQL的标准制定者提出了不同的隔离级别:未提交读(read uncommitted)、提交读(read committed)、可重复读(repeatable read)、序列化读(serializable)。其中最高级隔离级别就是序列化读,而在其他隔离级别中,由于事务是并发执行的,所以或多或少允许出现一些问题。见以下的矩阵表:

隔离级别(+:允许出现,-:不允许出现) 脏读 不可重复读 幻读
未提交读                                  +         +               +        
提交读                                    -         +               +        
可重复读                                  -         -               +        
序列化读                                  -         -               -        

注意,MySQL的InnoDB引擎在提交读级别通过MVCC解决了不可重复读的问题,在可重复读级别通过间隙锁解决了幻读问题,具体见下面的分析。

实现原理

标准SQL事务隔离级别实现原理

我们上面遇到的问题其实就是并发事务下的控制问题,解决并发事务的最常见方式就是悲观并发控制了(也就是数据库中的锁)。标准SQL事务隔离级别的实现是依赖锁的,我们来看下具体是怎么实现的:

事务隔离级别    实现方式                                                     
未提交读(RU) 事务对当前被读取的数据不加锁;

事务在更新某数据的瞬间(就是发生更新的瞬间),必须先对其加行级共享锁,直到事务结束才释放。

提交读(RC)    事务对当前被读取的数据加行级共享锁(当读到时才加锁),一旦读完该行,立即释放该行级共享锁;

事务在更新某数据的瞬间(就是发生更新的瞬间),必须先对其加行级排他锁,直到事务结束才释放。

可重复读(RR) 事务在读取某数据的瞬间(就是开始读取的瞬间),必须先对其加行级共享锁,直到事务结束才释放;

事务在更新某数据的瞬间(就是发生更新的瞬间),必须先对其加行级排他锁,直到事务结束才释放。

序列化读(S)   事务在读取数据时,必须先对其加表级共享锁 ,直到事务结束才释放;

事务在更新数据时,必须先对其加表级排他锁 ,直到事务结束才释放。

可以看到,在只使用锁来实现隔离级别的控制的时候,需要频繁的加锁解锁,而且很容易发生读写的冲突(例如在RC级别下,事务A更新了数据行1,事务B则在事务A提交前读取数据行1都要等待事务A提交并释放锁)。

为了不加锁解决读写冲突的问题,MySQL引入了MVCC机制,详细可见我以前的分析文章:一文读懂数据库中的乐观锁和悲观锁和MVCC。

InnoDB事务隔离级别实现原理

在往下分析之前,我们有几个概念需要先了解下:

1、锁定读和一致性非锁定读

锁定读:在一个事务中,主动给读加锁,如SELECT ... LOCK IN SHARE MODE 和 SELECT ... FOR UPDATE。分别加上了行共享锁和行排他锁。锁的分类可见我以前的分析文章:你应该了解的MySQL锁分类)。

https://dev.mysql.com/doc/refman/8.0/en/innodb-locking-reads.html

一致性非锁定读:InnoDB使用MVCC向事务的查询提供某个时间点的数据库快照。查询会看到在该时间点之前提交的事务所做的更改,而不会看到稍后或未提交的事务所做的更改(本事务除外)。也就是说在开始了事务之后,事务看到的数据就都是事务开启那一刻的数据了,其他事务的后续修改不会在本次事务中可见。

Consistent read是InnoDB在RC和RR隔离级别处理SELECT语句的默认模式。一致性非锁定读不会对其访问的表设置任何锁,因此,在对表执行一致性非锁定读的同时,其它事务可以同时并发的读取或者修改它们。

https://dev.mysql.com/doc/refman/8.0/en/innodb-consistent-read.html

2、当前读和快照读

当前读

读取的是最新版本,像UPDATE、DELETE、INSERT、SELECT ...  LOCK IN SHARE MODE、SELECT ... FOR UPDATE这些操作都是一种当前读,为什么叫当前读?就是它读取的是记录的最新版本,读取时还要保证其他并发事务不能修改当前记录,会对读取的记录进行加锁。

快照读

读取的是快照版本,也就是历史版本,像不加锁的SELECT操作就是快照读,即不加锁的非阻塞读;快照读的前提是隔离级别不是未提交读和序列化读级别,因为未提交读总是读取最新的数据行,而不是符合当前事务版本的数据行,而序列化读则会对表加锁。

3、隐式锁定和显式锁定

隐式锁定

InnoDB在事务执行过程中,使用两阶段锁协议(不主动进行显示锁定的情况):

  • 随时都可以执行锁定,InnoDB会根据隔离级别在需要的时候自动加锁;
  • 锁只有在执行commit或者rollback的时候才会释放,并且所有的锁都是在同一时刻被释放。

显式锁定

  • InnoDB也支持通过特定的语句进行显示锁定(存储引擎层)
select ... lock in share mode //共享锁
select ... for update //排他锁
  • MySQL Server层的显示锁定:
lock table
unlock table

了解完上面的概念后,我们来看下InnoDB的事务具体是怎么实现的(下面的读都指的是非主动加锁的select)

事务隔离级别    实现方式                                                     
未提交读(RU) 事务对当前被读取的数据不加锁,都是当前读;

事务在更新某数据的瞬间(就是发生更新的瞬间),必须先对其加行级共享锁,直到事务结束才释放。

提交读(RC)    事务对当前被读取的数据不加锁,且是快照读;

事务在更新某数据的瞬间(就是发生更新的瞬间),必须先对其加行级排他锁(Record),直到事务结束才释放。

通过快照,在这个级别MySQL就解决了不可重复读的问题

可重复读(RR) 事务对当前被读取的数据不加锁,且是快照读;

事务在更新某数据的瞬间(就是发生更新的瞬间),必须先对其加行级排他锁(Record,GAP,Next-Key),直到事务结束才释放。

通过间隙锁,在这个级别MySQL就解决了幻读的问题

序列化读(S)   事务在读取数据时,必须先对其加表级共享锁 ,直到事务结束才释放,都是当前读;

事务在更新数据时,必须先对其加表级排他锁 ,直到事务结束才释放。

可以看到,InnoDB通过MVCC很好的解决了读写冲突的问题,而且提前一个级别就解决了标准级别下会出现的幻读和不可重复读问题,大大提升了数据库的并发能力。

一些常见误区

幻读到底包不包括了delete的情况?

不可重复读:前后多次读取一行,数据内容不一致,针对其他事务的update和delete操作。为了解决这个问题,使用行共享锁,锁定到事务结束(也就是RR级别,当然MySQL使用MVCC在RC级别就解决了这个问题)

幻读:当同一个查询在不同时间生成不同的行集合时就是出现了幻读,针对的是其他事务的insert操作,为了解决这个问题,锁定整个表到事务结束(也就是S级别,当然MySQL使用间隙锁在RR级别就解决了这个问题)

网上很多文章提到幻读和提交读的时候,有的说幻读包括了delete的情况,有的说delete应该属于提交读的问题,那到底真相如何呢?我们实际来看下MySQL的官方文档(如下)

The so-called phantom problem occurs within a transaction when the same query produces different sets of rows at different times. For example, if a SELECT) is executed twice, but returns a row the second time that was not returned the first time, the row is a “phantom” row.
https://dev.mysql.com/doc/refman/5.7/en/innodb-next-key-locking.html

可以看到,幻读针对的是结果集前后发生变化,所以看起来delete的情况应该归为幻读,但是我们实际分析下上面列出的标准SQL在RR级别的实现原理就知道,标准SQL的RR级别是会对查到的数据行加行共享锁,所以这时候其他事务想删除这些数据行其实是做不到的,所以在RR下,不会出现因delete而出现幻读现象,也就是幻读不包含delete的情况。

MVCC能解决了幻读问题?

网上很多文章会说MVCC或者MVCC+间隙锁解决了幻读问题,实际上MVCC并不能解决幻读问题。如以下的例子:

begin;

#假设users表为空,下面查出来的数据为空

select * from users; #没有加锁

#此时另一个事务提交了,且插入了一条id=1的数据

select * from users; #读快照,查出来的数据为空

update users set name='mysql' where id=1;#update是当前读,所以更新成功,并生成一个更新的快照

select * from users; #读快照,查出来id为1的一条记录,因为MVCC可以查到当前事务生成的快照

commit;

可以看到前后查出来的数据行不一致,发生了幻读。所以说只有MVCC是不能解决幻读问题的,解决幻读问题靠的是间隙锁。如下:

begin;

#假设users表为空,下面查出来的数据为空

select * from users lock in share mode; #加上共享锁

#此时另一个事务B想提交且插入了一条id=1的数据,由于有间隙锁,所以要等待

select * from users; #读快照,查出来的数据为空

update users set name='mysql' where id=1;#update是当前读,由于不存在数据,不进行更新

select * from users; #读快照,查出来的数据为空

commit;

#事务B提交成功并插入数据

注意,RR级别下想解决幻读问题,需要我们显式加锁,不然查询的时候还是不会加锁的

以上就是详解MySQL中事务隔离级别的实现原理的详细内容,更多关于MySQL 事务隔离级别的资料请关注我们其它相关文章!

时间: 2021-01-27

MySQL 事务autocommit自动提交操作

MySQL默认操作模式就是autocommit自动提交模式.这就表示除非显式地开始一个事务,否则每个查询都被当做一个单独的事务自动执行.我们可以通过设置autocommit的值改变是否是自动提交autocommit模式. 通过以下命令可以查看当前autocommit模式 mysql> show variables like 'autocommit'; +---------------+-------+ | Variable_name | Value | +---------------+----

MySQL 查看事务和锁情况的常用语句分享

一些查看数据库中事务和锁情况的常用语句 查看事务等待状况: SELECT r.trx_id waiting_trx_id, r.trx_mysql_thread_id waiting_thread, r.trx_query waiting_query, b.trx_id blocking_trx_id, b.trx_mysql_thread_id blocking_thread, b.trx_query blocking_query FROM information_schema.innodb_

深入理解PHP+Mysql分布式事务与解决方案

事务(Transaction)是访问并可能更新数据库中各种数据项的一个程序执行单元: 事务的ACID特性 事务应该具有4个属性:原子性.一致性.隔离性.持续性 原子性(atomicity).一个事务是一个不可分割的工作单位,事务中包括的诸操作要么都做,要么都不做. 一致性(consistency).事务必须是使数据库从一个一致性状态变到另一个一致性状态.一致性与原子性是密切相关的. 隔离性(isolation).一个事务的执行不能被其他事务干扰.即一个事务内部的操作及使用的数据对并发的其他事务是

MySQL如何实现事务的ACID

前言 最近在面试,有被问到,MySQL的InnoDB引擎是如何实现事务的,又或者说是如何实现ACID这几个特性的,当时没有答好,所以自己总结出来,记录一下. 事务的四大特性ACID 事务的四大特性ACID分别是,A-原子性(Atomicity),C-一致性(Consistency),I-隔离性(Isolation),D-持久性(Durability).一致性是最终目的,原子性.隔离性.持久性是为了保证一致性所做的措施.所以我写的顺序并不是按照ACID来写的,将一致性放到了最后,顺序就变成了,AD

MySQL执行事务的语法与流程详解

摘要:MySQL 提供了多种存储引擎来支持事务. MySQL 提供了多种存储引擎来支持事务.支持事务的存储引擎有 InnoDB 和 BDB,其中,InnoDB 存储引擎事务主要通过 UNDO 日志和 REDO 日志实现,MyISAM 存储引擎不支持事务. 拓展:任何一种数据库,都会拥有各种各样的日志,用来记录数据库的运行情况.日常操作.错误信息等,MySQL 也不例外.例如,当用户 root 登录到 MySQL 服务器,就会在日志文件里记录该用户的登录时间.执行操作等. 为了维护 MySQL 服

Mysql事务中Update是否会锁表?

两种情况: 1.带索引 2.不带索引 前提介绍: 方式:采用命令行的方式来模拟 1.mysq由于默认是开启自动提交事务,所以首先得查看自己当前的数据库是否开启了自动提交事务. 命令:select @@autocommit; 结果如下: +--------------+ | @@autocommit | +--------------+ | 0 | +--------------+ 如果是1,那么运行命令:set autocommit = 0;设置为不开启自动提交 2.当前的数据库表格式如下 tb

MySQL 主从同步,事务回滚的实现原理

BinLog BinLog是记录所有数据库表结构变更(例如create.alter table)以及表数据修改(insert.update.delete)的二进制日志,主从数据库同步用到的都是BinLog文件.BinLog日志文件有三种模式. STATEMENT 模式 内容:binlog 只会记录引起数据变更的 sql 语句 优势:该模式下,因为没有记录实际的数据,所以日志量和 IO 都消耗很低,性能是最优的 劣势:但有些操作并不是确定的,比如 uuid() 函数会随机产生唯一标识,当依赖 bi

mysql、oracle默认事务隔离级别的说明

1.事务的特性(ACID) (1)原子性(Atomicity).事务中所涉及的程序对数据库的修改操作要么全部成功,要么全部失败. (2)一致性(Consistency).事务执行前和执行后来源和去向保持平衡. (3)隔离性(Isolation).并发时每个事务是隔离的,相互不影响. (4)持久性(Durubility).一旦事务成功提交,应该保证数据的完整存在. 2.事务隔离级别 (1)read uncommitted 未提交读 所有事务都可以看到没有提交事务的数据. (2)read commi

MySQL数据库事务与锁深入分析

一.基本概念 事务是指满足ACID特性的的一组操作,可以通过Commit提交事务,也可以也可以通过Rollback进行回滚.会存在中间态和一致性状态(也是真正在数据库表中存在的状态) 二.ACID Atomicity[原子性]:事务被视为不可分割的最小单元,事务的所有操作要么全部提交成功,要么全部失败回滚.回滚可以用回滚日志(undo Log)来实现,回滚日志记录着事务所执行的修改操作,在回滚时反向执行这些修改操作即可 undoLog:为了满足事务的原子性,在操作任何数据之前,首先将数据备份到U

详解MySQL中事务的持久性实现原理

前言 说到数据库事务,大家脑子里一定很容易蹦出一堆事务的相关知识,如事务的ACID特性,隔离级别,解决的问题(脏读,不可重复读,幻读)等等,但是可能很少有人真正的清楚事务的这些特性又是怎么实现的,为什么要有四个隔离级别. 在之前的文章我们已经了解了MySQL中事务的隔离性的实现原理,今天就继续来聊一聊MySQL持久性的实现原理. 当然MySQL博大精深,文章疏漏之处在所难免,欢迎批评指正. 说明 MySQL的事务实现逻辑是位于引擎层的,并且不是所有的引擎都支持事务的,下面的说明都是以InnoDB

MySQL为什么要避免大事务以及大事务解决的方法

什么是大事务 运行时间比较长,长时间未提交的事务就可以称为大事务 大事务产生的原因 操作的数据比较多 大量的锁竞争 事务中有其他非DB的耗时操作 ... 大事务造成的影响 并发情况下,数据库连接池容易被撑爆 锁定太多的数据,造成大量的阻塞和锁超时 执行时间长,容易造成主从延迟 回滚所需要的时间比较长 undo log膨胀 ... 如何查询大事务 **注**:本文的sql的操作都是基于mysql5.7版本 以查询执行时间超过10秒的事务为例: select \* from information\

Oracle和MySQL的数据导入为何差别这么大

经常会有一些朋友咨询我一些数据库的问题,我注意到一个很有意思的现象,凡是数据导入的问题,基本上都是Oracle类的,MySQL类的问题脑子里想了下竟然一次都没有. 我禁不住开始思考这个未曾注意的问题: 为什么Oracle导入数据会碰到很多的问题? 我们来梳理一下这个问题,分别从导出导入的方式来聊聊. 首先Oracle导出的文件格式就没打算让你拿来即用,导出文件叫做dump,换句话说可以理解这是一个二进制文件.当然实际上这个文件还是有很多的方式去抓取一些关键的信息,比如dump头部的信息可以通过s

PHP使用Redis实现防止大并发下二次写入的方法

本文实例讲述了PHP使用Redis实现防止大并发下二次写入的方法.分享给大家供大家参考,具体如下: PHP调用redis进行读写操作,大并发下会出现:读取key1,没有内容则写入内容,但是大并发下会出现同时多个php进程写入的情况,这个时候需要加一个锁,即获取锁的php进程有权限写. $lock_key = 'LOCK_PREFIX' . $redis_key; $is_lock = $redis->setnx($lock_key, 1); // 加锁 if($is_lock == true){

Mysql事务操作失败如何解决

Mysql事务操作失败如何解决 事务的原子性 :事务是数据库的逻辑工作单位,事务中包含的各操作要么都做,要么都不做 . 要实现事务的原子性,单单靠一条commit或是rollback命令还是不行的,因为例如commit命令它只是将一个事务中执行成功的DML语句提交给数据库里.如果要实现事务的原子性,则就需要commit和rollback命令配合上程序上的一个业务逻辑才能可以,具体业务逻辑代码如下示例代码: 1.现象 程序中打开了事务进行插入,但是没有commit,表中的数据已经存在,就是回滚也不

linux 查找大目录和大文件的方法(推荐)

今天,在机器上执行命令的时候,发现tab键无法补全了,原因竟然是磁盘空间满了,使用df命令看了一下,确实如此,每个分区的使用率都得到100%了,因此想找到系统中的大目录和大文件,删除一部分. 主要涉及到两个命令 du和find du命令(查找系统中的大目录): -h已易读的格式显示指定目录或文件的大小 -s选项指定对于目录不详细显示每个子目录或文件的大小 -m或–megabytes 以1MB为单位 –max-depth=1:其中,数字"1"是指查询结果中最多显示的目录层数,这里指最多显

win2008 iis7 上传大文件限制的真正解决办法

iis7 上传大文件限制的真正解决办法 修改IIS_schema.xml这个文件要先获得这个文件的控制权; 进入目录C:\Windows\System32\inetsrv\config\schema,修改文件IIS_schema.xml 权限: 进入IIS_schema.xml文件权限修改,选择"高级" 选择"所有者" 选中 administrators 确定 再进入权限编辑,修改administrators 完全控制. 再去掉IIS_schema.xml的只读属性

asp.net(C#)中上传大文件的几中常见应用方法

几种常见的方法,本文主要内容包括: 第一部分:首先我们来说一下如何解决ASP.net中的文件上传大小限制的问题,我们知道在默认情况下ASP.NET的文件上传大小限制为2M,一般情况下,我们可以采用更改Web.Config文件来自定义最大文件大小,如下: 这样上传文件的最大值就变成了4M,但这样并不能让我们无限的扩大 MaxRequestLength的值,因为ASP.NET会将全部文件载入内存后,再加以处理.解决的方法是利用隐含的 HttpWorkerRequest,用它的GetPreloaded

PHP读取大文件末尾N行的高效方法推荐

小文件几兆以内大小,都可以通过file()函数,将文件按行读入数组,在用array_pop取得最后一行,就可以了. 但是对于很大的文本文件来说,机器内存不够大,或者php本身memory_limit有限制,这个办法就不适用了,即使强行不限制,效率也是非常低的. 没有办法了吗?当然有,不过没有现成的函数了,需要自己动手了. 这里需要用到文件指针,学过C的应该知道指针式个嘛玩意,通俗的讲吧,PHP中通过fopen打开一个文件,这时候还没有读取文件,这时候指向的是文件开头,指针位置也就是0,当你通过f

C++解决大数组栈内存不够问题的方法分析

本文实例讲述了C++解决大数组栈内存不够问题的方法.分享给大家供大家参考,具体如下: 在c++中,我们可以直接通过下面的方式创建一个数组: const int N = 6; const int Nx = 100; const int Ny = 100; double phi[N][Nx][Ny]; double phi_b[N][Nx][Ny]; 但是,如果上述的Nx和Ny比较小还好说,一旦Nx和Ny很大时,就会报错,导致编译失败. 为解决这一问题,我们可以采用下面的几种方法来解决此问题: 1.

PHP大文件分片上传的实现方法

一.前言 在网站开发中,经常会有上传文件的需求,有的文件size太大直接上传,经常会导致上传过程中耗时太久,大量占用带宽资源,因此有了分片上传. 分片上传主要是前端将一个较大的文件分成等分的几片,标识当前分片是第几片和总共几片,待所有的分片均上传成功的时候,在后台进行合成文件即可. 二.开发过程中遇到的问题 分片的时候每片该分多大size?太大会出现"413 request entity too large" 分片上传的时候并不是严格按照分片的序号顺序上传,如何判断所有的分片均上传成功