MySQL事务隔离级别以及MVCC机制

SQL标准定义了4类隔离级别，包括了一些具体规则，用来限定事务内外的哪些改变是可见的，哪些是不可见的。低级别的隔离级一般支持更高的并发处理，并拥有更低的系统开销。Read Uncommitted（读取未提交内容）在该隔离级别，所有事务都可以看到其他未提交事务的执行结果。本隔离级别很少用于实际应用，因为它的性能也不比其他级别好多少。读取未提交的数据，也被称之为脏读（Dirt

sofia1217

11115人浏览 · 2016-03-02 12:27:03

sofia1217 · 2016-03-02 12:27:03 发布

SQL标准定义了4类隔离级别，包括了一些具体规则，用来限定事务内外的哪些改变是可见的，哪些是不可见的。低级别的隔离级一般支持更高的并发处理，并拥有更低的系统开销。
Read Uncommitted（读取未提交内容）

在该隔离级别，所有事务都可以看到其他未提交事务的执行结果。本隔离级别很少用于实际应用，因为它的性能也不比其他级别好多少。读取未提交的数据，也被称之为脏读（Dirty Read）。
Read Committed（读取提交内容）

这是大多数数据库系统的默认隔离级别（但不是MySQL默认的）。它满足了隔离的简单定义：一个事务只能看见已经提交事务所做的改变。这种隔离级别也支持所谓的不可重复读（Nonrepeatable Read），因为同一事务的其他实例在该实例处理其间可能会有新的commit，所以同一select可能返回不同结果。
Repeatable Read（可重读）

这是MySQL的默认事务隔离级别，它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行。不过理论上，这会导致另一个棘手的问题：幻读（Phantom Read）。简单的说，幻读指当用户读取某一范围的数据行时，另一个事务又在该范围内插入了新行，当用户再读取该范围的数据行时，会发现有新的“幻影” 行。InnoDB和Falcon存储引擎通过多版本并发控制（MVCC，Multiversion Concurrency Control）机制解决了该问题。

Serializable（可串行化）
这是最高的隔离级别，它通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。简言之，它是在每个读的数据行上加上共享锁。在这个级别，可能导致大量的超时现象和锁竞争。

这四种隔离级别采取不同的锁类型来实现，若读取的是同一个数据的话，就容易发生问题。例如：

脏读(Drity Read)：某个事务已更新一份数据，另一个事务在此时读取了同一份数据，由于某些原因，前一个RollBack了操作，则后一个事务所读取的数据就会是不正确的。

不可重复读(Non-repeatable read):在一个事务的两次查询之中数据不一致，这可能是两次查询过程中间插入了一个事务更新的原有的数据。

幻读(Phantom Read):在一个事务的两次查询中数据笔数不一致，例如有一个事务查询了几列(Row)数据，而另一个事务却在此时插入了新的几列数据，先前的事务在接下来的查询中，就会发现有几列数据是它先前所没有的。

在MySQL中，实现了这四种隔离级别，分别有可能产生问题如下所示：

下面，将利用MySQL的客户端程序，分别测试几种隔离级别。测试数据库为test，表为tx；表结构：

id	int
num	int

两个命令行客户端分别为A，B；不断改变A的隔离级别，在B端修改数据。

（一）、将A的隔离级别设置为read uncommitted(未提交读)

在B未更新数据之前：

B更新数据：

客户端B：

客户端A：

经过上面的实验可以得出结论，事务B更新了一条记录，但是没有提交，此时事务A可以查询出未提交记录。造成脏读现象。未提交读是最低的隔离级别。

（二）、将客户端A的事务隔离级别设置为read committed(已提交读)

在B未更新数据之前：

客户端A：

B更新数据：

客户端B：

客户端A：

经过上面的实验可以得出结论，已提交读隔离级别解决了脏读的问题，但是出现了不可重复读的问题，即事务A在两次查询的数据不一致，因为在两次查询之间事务B更新了一条数据。已提交读只允许读取已提交的记录，但不要求可重复读。

(三)、将A的隔离级别设置为repeatable read(可重复读)

在B未更新数据之前：

客户端A：

B更新数据：

客户端B：

客户端A：

B插入数据：

客户端B：

客户端A：

由以上的实验可以得出结论，可重复读隔离级别只允许读取已提交记录，而且在一个事务两次读取一个记录期间，其他事务部的更新该记录。但该事务不要求与其他事务可串行化。例如，当一个事务可以找到由一个已提交事务更新的记录，但是可能产生幻读问题(注意是可能，因为数据库对隔离级别的实现有所差别)。像以上的实验，就没有出现数据幻读的问题。

(四)、将A的隔离级别设置为 可串行化 (Serializable)

A端打开事务，B端插入一条记录

事务A端：

事务B端：

因为此时事务A的隔离级别设置为serializable，开始事务后，并没有提交，所以事务B只能等待。

事务A提交事务：

事务A端

事务B端

serializable完全锁定字段，若一个事务来查询同一份数据就必须等待，直到前一个事务完成并解除锁定为止。是完整的隔离级别，会锁定对应的数据表格，因而会有效率的问题。

 Mysql到底是怎么实现MVCC的？这个问题无数人都在问，但google中并无答案，本文尝试从MySQL源码中寻找答案。

在Mysql中MVCC是在Innodb存储引擎中得到支持的，Innodb为每行记录都实现了三个隐藏字段：

6字节的事务ID（DB_TRX_ID ）
7字节的回滚指针（DB_ROLL_PTR）
隐藏的ID

6字节的事物ID用来标识该行所述的事务，7字节的回滚指针需要了解下Innodb的事务模型。

1. Innodb的事务相关概念

为了支持事务，Innbodb引入了下面几个概念：

redo log
redo log就是保存执行的SQL语句到一个指定的Log文件，当Mysql执行recovery时重新执行redo log记录的SQL操作即可。当客户端执行每条SQL（更新语句）时，redo log会被首先写入log buffer；当客户端执行COMMIT命令时，log buffer中的内容会被视情况刷新到磁盘。redo log在磁盘上作为一个独立的文件存在，即Innodb的log文件。
undo log
与redo log相反，undo log是为回滚而用，具体内容就是copy事务前的数据库内容（行）到undo buffer，在适合的时间把undo buffer中的内容刷新到磁盘。undo buffer与redo buffer一样，也是环形缓冲，但当缓冲满的时候，undo buffer中的内容会也会被刷新到磁盘；与redo log不同的是，磁盘上不存在单独的undo log文件，所有的undo log均存放在主ibd数据文件中（表空间），即使客户端设置了每表一个数据文件也是如此。
rollback segment
回滚段这个概念来自Oracle的事物模型，在Innodb中，undo log被划分为多个段，具体某行的undo log就保存在某个段中，称为回滚段。可以认为undo log和回滚段是同一意思。
锁
Innodb提供了基于行的锁，如果行的数量非常大，则在高并发下锁的数量也可能会比较大，据Innodb文档说，Innodb对锁进行了空间有效优化，即使并发量高也不会导致内存耗尽。
对行的锁有分两种：排他锁、共享锁。共享锁针对对，排他锁针对写，完全等同读写锁的概念。如果某个事务在更新某行（排他锁），则其他事物无论是读还是写本行都必须等待；如果某个事物读某行（共享锁），则其他读的事物无需等待，而写事物则需等待。通过共享锁，保证了多读之间的无等待性，但是锁的应用又依赖Mysql的事务隔离级别。
隔离级别
隔离级别用来限制事务直接的交互程度，目前有几个工业标准：
- READ_UNCOMMITTED：脏读
- READ_COMMITTED：读提交
- REPEATABLE_READ：重复读
- SERIALIZABLE：串行化
Innodb对四种类型都支持，脏读和串行化应用场景不多，读提交、重复读用的比较广泛，后面会介绍其实现方式。

2. 行的更新过程

下面演示下事务对某行记录的更新过程：

1. 初始数据行

F1～F6是某行列的名字，1～6是其对应的数据。后面三个隐含字段分别对应该行的事务号和回滚指针，假如这条数据是刚INSERT的，可以认为ID为1，其他两个字段为空。

2.事务1更改该行的各字段的值

当事务1更改该行的值时，会进行如下操作：

用排他锁锁定该行
记录redo log
把该行修改前的值Copy到undo log，即上图中下面的行
修改当前行的值，填写事务编号，使回滚指针指向undo log中的修改前的行

3.事务2修改该行的值

与事务1相同，此时undo log，中有有两行记录，并且通过回滚指针连在一起。

因此，如果undo log一直不删除，则会通过当前记录的回滚指针回溯到该行创建时的初始内容，所幸的时在Innodb中存在purge线程，它会查询那些比现在最老的活动事务还早的undo log，并删除它们，从而保证undo log文件不至于无限增长。

4. 事务提交

当事务正常提交时Innbod只需要更改事务状态为COMMIT即可，不需做其他额外的工作，而Rollback则稍微复杂点，需要根据当前回滚指针从undo log中找出事务修改前的版本，并恢复。如果事务影响的行非常多，回滚则可能会变的效率不高，根据经验值没事务行数在1000～10000之间，Innodb效率还是非常高的。很显然，Innodb是一个COMMIT效率比Rollback高的存储引擎。据说，Postgress的实现恰好与此相反。

5. Insert Undo log

上述过程确切地说是描述了UPDATE的事务过程，其实undo log分insert和update undo log，因为insert时，原始的数据并不存在，所以回滚时把insert undo log丢弃即可，而update undo log则必须遵守上述过程。

3. 事务级别

众所周知地是更新（update、insert、delete）是一个事务过程，在Innodb中，查询也是一个事务，只读事务。当读写事务并发访问同一行数据时，能读到什么样的内容则依赖事务级别：

READ_UNCOMMITTED
读未提交时，读事务直接读取主记录，无论更新事务是否完成
READ_COMMITTED
读提交时，读事务每次都读取undo log中最近的版本，因此两次对同一字段的读可能读到不同的数据（幻读），但能保证每次都读到最新的数据。
REPEATABLE_READ
每次都读取指定的版本，这样保证不会产生幻读，但可能读不到最新的数据
SERIALIZABLE
锁表，读写相互阻塞，使用较少

读事务一般有SELECT语句触发，在Innodb中保证其非阻塞，但带FOR UPDATE的SELECT除外，带FOR UPDATE的SELECT会对行加排他锁，等待更新事务完成后读取其最新内容。就整个Innodb的设计目标来说，就是提供高效的、非阻塞的查询操作。

4. MVCC

上述更新前建立undo log，根据各种策略读取时非阻塞就是MVCC，undo log中的行就是MVCC中的多版本，这个可能与我们所理解的MVCC有较大的出入，一般我们认为MVCC有下面几个特点：

每行数据都存在一个版本，每次数据更新时都更新该版本
修改时Copy出当前版本随意修改，个事务之间无干扰
保存时比较版本号，如果成功（commit），则覆盖原记录；失败则放弃copy（rollback）

就是每行都有版本号，保存时根据版本号决定是否成功，听起来含有乐观锁的味道。。。，而Innodb的实现方式是：

事务以排他锁的形式修改原始数据
把修改前的数据存放于undo log，通过回滚指针与主数据关联
修改成功（commit）啥都不做，失败则恢复undo log中的数据（rollback）

二者最本质的区别是，当修改数据时是否要排他锁定，如果锁定了还算不算是MVCC？

Innodb的实现真算不上MVCC，因为并没有实现核心的多版本共存，undo log中的内容只是串行化的结果，记录了多个事务的过程，不属于多版本共存。但理想的MVCC是难以实现的，当事务仅修改一行记录使用理想的MVCC模式是没有问题的，可以通过比较版本号进行回滚；但当事务影响到多行数据时，理想的MVCC据无能为力了。

比如，如果Transaciton1执行理想的MVCC，修改Row1成功，而修改Row2失败，此时需要回滚Row1，但因为Row1没有被锁定，其数据可能又被Transaction2所修改，如果此时回滚Row1的内容，则会破坏Transaction2的修改结果，导致Transaction2违反ACID。

理想MVCC难以实现的根本原因在于企图通过乐观锁代替二段提交。修改两行数据，但为了保证其一致性，与修改两个分布式系统中的数据并无区别，而二提交是目前这种场景保证一致性的唯一手段。二段提交的本质是锁定，乐观锁的本质是消除锁定，二者矛盾，故理想的MVCC难以真正在实际中被应用，Innodb只是借了MVCC这个名字，提供了读的非阻塞而已。

5.总结

也不是说MVCC就无处可用，对一些一致性要求不高的场景和对单一数据的操作的场景还是可以发挥作用的，比如多个事务同时更改用户在线数，如果某个事务更新失败则重新计算后重试，直至成功。这样使用MVCC会极大地提高并发数，并消除线程锁。

6. 参考资料

Mysql官网
http://blog.chinaunix.net/link.php?url=http://forge.mysql.com%2Fwiki%2FMySQL_Internals
Understanding MySQL Internals

MYSQL MVCC实现及其机制

多版本并发控制

　　Multiversion Concurrency Control

　　大部分的MySQL的存储引擎，比如InnoDB，Falcon，以及PBXT并不是简简单单的使用行锁机制。它们都使用了行锁结合一种提高并发的技术，被称为MVCC（多版本并发控制）。MVCC并不单单应用在MySQL中，其他的数据库如Oracle,PostgreSQL,以及其他数据库也使用这个技术。

　　MVCC避免了许多需要加锁的情形以及降低消耗。这取决于它实现的方式，它允许非阻塞读取，在写的操作的时候阻塞必要的记录。

　　MVCC保存了某一时刻数据的一个快照。意思就是无论事物运行了多久，它们都能看到一致的数据。也就是说在相同的时间下，不同的事物看相同表的数据是不同的。如果你从来没有这方面的经验，可能说这些有点令人困惑。但是在以后这个会很容易理解和熟悉的。

　　每个存储引擎实现MVCC方式都是不同的。有许多种包含了乐观（optimistic）和悲观（pessimistic）的并发控制。我们用简单的InnoDb的行为来举例说明MVCC工作方式。

　　InnoDB实现MVCC的方法是，它存储了每一行的两个额外的隐藏字段，这两个隐藏字段分别记录了行的创建的时间和删除的时间。在每个事件发生的时候，每行存储版本号，而不是存储事件实际发生的时间。每次事物的开始这个版本号都会增加。自记录时间开始，每个事物都会保存记录的系统版本号。依照事物的版本来检查每行的版本号。在事物隔离级别为可重复读的情况下，来看看怎样应用它。

　　SELECT

　　InnoDB检查每行，要确定它符合两个标准。

　　InnoDB必须知道行的版本号，这个行的版本号至少要和事物版本号一样的老。（也就是是说它的版本号可能少于或者和事物版本号相同）。这个既能确定事物开始之前行是存在的，也能确定事物创建或修改了这行。

　　行的删除操作的版本一定是未定义的或者大于事物的版本号。确定了事物开始之前，行没有被删除。

　　符合了以上两点。会返回查询结果。

　　INSERT

　　InnoDB记录了当前新增行的系统版本号。

　　DELETE

　　InnoDB记录的删除行的系统版本号作为行的删除ID。

　　UPDATE

　　InnoDB复制了一行。这个新行的版本号使用了系统版本号。它也把系统版本号作为了删除行的版本。

　　所有其他记录的结果保存是，从未获得锁的查询。这样它们查询的数据就会尽可能的快。要确定查询行要遵循这些标准。缺点是存储引擎要为每一行存储更多的数据，检查行的时候要做更多的处理以及其他内部的一些操作。

　　MVCC只能在可重复读和可提交读的隔离级别下生效。不可提交读不能使用它的原因是不能读取符合事物版本的行版本。它们总是读取最新的行版本。可序列化不能使用MVCC的原因是，它总是要锁定行。

　　下面的表说明了在MySQL中不同锁的模式以及并发级别。

锁的策略	并发性	开销	引擎
表	最低	最低	MyISAM,Merge,Memory
行	高	高	NDB Cluster
行和MVCC	最高	最高	InnoDB,Falcon,PBXT,solidD

也即使说，对某行的写操作会阻塞所有对该行的读取操作，对某行的读操作会阻塞所有对该行的写操作，在系统存在读、写并发时，不论系统IO能力有多高，会受限于锁而导致性能低下。

MVCC用于解决这个问题来提高系统性能，MVCC并没有统一的标准，各个数据库实现均采用不同方式来实现MVCC，InnoDB的实现方式如下：

准备工作：

（1）对每行记录增加行标志和删除标志两个字段；

（2）维护一个全局的系统版本号，每开始一个事务（注意select也是事务，读事务），将该系统版本号加1并作为事务的版本号

插入记录的行标志设置为本事务版本号，删除标志为空；

删除记录的删除标志设置为本事务版本号；

修改的处理过程：将原记录的删除版本号修改为本事务版本号；新插入一条记录，包含原记录数据及本次修改，行记录标志设置为本事务版本号，删除标志为空；

读取的处理过程：

仅读取同时满足以下条件的记录行：

（1）行标志小于或等于本事务版本号（等于用于保证能够读取到本事务内提交的增加）；

（2）删除标志为空或者大于本事务版本号（不包括等于以保证不会读取到本事务删除的记录）；

相当于在读事务开始的时刻点，建立了一个系统的快照，该事务读取的所有数据，均是从快照中读取的，因此满足可重复读的条件，并且可解决幻读的问题，并且也不会读到产生“同样查询条件，事务中第一次读到的记录数大于第二次读到的记录数的问题“（由并发删除引起）

从上可知，使用MVCC后，大部分读都不再需要加读锁，因此读不再阻塞写，写也不再阻塞读。读操作只再受限于系统IO能力。

MVCC多版本并发控制

原理：

mvcc提供基于某个时间的快照,使得对于事务看来,总是可以提供与事务开始时刻相一致的数据,而不管这个事

务执行的时间有多长,故在不同事务看来,同一时刻看到的相同的行数据可能是不一样的,即:每一行数据会有

多个版本数据(副本)

InnoDB中每行隐含2个字段:更新或修改版本号和删除版本号(可以为空),每一个事务开始也有自己的版本号且是递增(

类似于SCN)

以select,delete,insert update语句来说明:

1)select 同时满足2个条件的行,才能被返回:

*行的被修改版本号<=该版本号

*行的被删除版本号要么没有被定义,要么大于事务的版本号:行的删除版本号如没被定义,说明行没有被删除过;如删除

版本号>当前事务的版本号,说明该行的是被该事务的后面启动事务删除过(接着看下去..)

2)insert

对新插入的行,行的更新版本被修改为该事务的版本号

3)delete

对于删除,innodb直接把该行的被删除版本号设置为当前事务版本号,相当于标记删除,不是实际删除

4)update

在更新行的时候,innodb会把原来的行复制一份到回滚端的表空间中,若成功,并把当前事务的版本号作为该行

的更新版本号,否则rollback;

mvcc优缺点:

在读取数据时,innodb几乎不用获取任何锁,在每个查询通过版本检查,只获取需要的数据版本,提高系统并发度

缺点:为了实现多版本,innodb必须对每行增加相应字段来存储版本信息,同时需要维护每一行的版本信息,而且

在检索行的时候,需要进行版本的比较,因而减低了查询效率;innodb还需要定期清理不再需要的行版本,及时回收

空间,这也增加开销;

innodb支持事务隔离级别:

1)read uncommitted: (读没有提交的数据),无法避免脏读;

2)read committed: (只能读提交的数据),其他事务对数据库的修改,只能已提交,其修改的结果可以看见,与这2个事务

开始的先后顺序无关,这个级别避免脏读,无法实现可重复读，可能会产生幻读

不可重复读:t1:读取一行 t2:再读取这行时,可能被修改了，看不到啦

幻读:t1:读取有一行,t2:再读取相同数据时,比t1时间多了数据

3)repeatable read:(可重复读),只能读取在它开始之前提交事务对数据库的修改,在它开始之后,所有其他事务对数据库

的修改对它来说均不可见.

一门面向 Data 和 AI 的低代码、云原生的开源编程语言

无需安装部署，在线快速体验 Byzer

更多推荐

byzer plugin install log

Byzer 白泽

函数实现越通用越好？来看看 Byzer-LLM 的 Function Implementation 带来的编程思想大变化...

前言Function Calling 是 OpenAI 首先提出来的一个非常有用的功能，实现了大模型对函数的调用能力。Byzer-LLM 给开源模型也带来了 Function Calling 实现。在这个基础上，我们还拓展了 Respond With Class 功能，允许大模型输出标准的Python对象，进步控制了大模型的输出能力。这两个能力参看：给开源大模型带来Function Callin.