【MySQL面试专题二】 MVCC是什么？如何解决一致性问题？

问题一：MySQL 的事务隔离级别有哪些？分别用于解决什么问题？ 

脏读：一个事务读取到另一个事务还未提交的数据。

不可重复读：在一个事务中多次读取同一个数据时，结果出现不一致。

幻读：在一个事务中使用相同的 SQL 两次读取，第二次读取到了其他事务新插入的行。

不可重复读注重于数据的修改，而幻读注重于数据的插入。

问题二、MySQL 为什么默认隔离级别采用可重复读？如何实现的？ 

 采用可重复读，可以避免脏读和不客重复的读。使用 MVCC 实现的，即 Mutil-Version Concurrency Control，多版本并发控制。关于 MVCC介绍之前大家看下

一、什么是MVCC？

MVCC，Multi-Version Concurrency Control，多版本并发控制。这项技术使得InnoDB的事务隔离级别下执行一致性读操作有了保证，换言之，就是为了查询一些正在被另一个事务更新的行，并且可以看到它们被更新之前的值。这是一个可以用来增强并发性的强大技术，查询不用等待另一个事务释放锁。这项技术广泛应用于数据库，例如Oracle，PostgreSQL。当然也有一些数据库产品以及mysql的其它存储引擎不支持它。

看一看MVCC机制的示意图，图下边会给出文字解释 

图中底部横轴是时间，纵向的箭头用来标记增、删、改、查发生的时刻。尤其注意时间轴上方两条色块，代表数据的两个版本V1、V2。为了醒目，我把V1、V2用红色方框圈了起来（多版本的体现）。从左向右解读这张图

1、T1事务插入数据a=3，然后提交，生成了数据对应的V1版本

2、T2事务开始读取a数据，读取会持续一段时间，由于开始读取的时刻，只有V1版本，所以最终T2读到a=3

3、T2读取过程中，T3对数据a进行修改，a=4，生成a数据的V2版本，但此时并未提交，因此生效的是V1版本数据。

4、T3修改提交之前，T4读取a数据，由于此时V1版本数据生效，因此，T4读到a=3

5、T3提交a=4的修改，V1版本数据失效，V2生效。a的值变为4

6、T5读取a的值，读到V2版本，a=4

至此，MVCC的大致概念就搞明白了

二、那么MySQL是怎么实现的呢？

1、实际在InnoDB引擎中多版本的实现，是靠三个隐藏字段

在内部，InnoDB向数据库中存储的每一行数据添加三个字段。

（1）DB_TRX_ID字段，6字节。表示插入或更新行的最后一个事务的事务标识符。此外，删除在内部被视为更新，其中行中的特殊位被设置为将其标记为已删除。

（2）DB_ROLL_PTR字段，7字节，叫做回滚指针（roll pointer）。回滚指针指向写入回滚段的撤消日志（Undo Log）。如果行已更新，则撤消日志包含重建更新前该行内容所需的信息。

（3）DB_ROW_ID字段，6字节。包含一个随着新行插入而单调增加的行ID，如果innodb自动生成聚集索引，则该索引包含行ID值。否则，DB_ROW_ID列不会出现在任何索引中。

2、多版本产生过程

以新增一条记录并对该记录进行2次修改来说明具体实现 

这条记录有3个隐含字段（前面已经介绍），分别应对行的ID、事务号和回滚指针。

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

这个过程做了以下几件事

• 用排他锁锁定该行
• 把该行修改前的值拷贝到Undo Log中
• 修改当前行的值，填写事务编号，使回滚指针指向Undo Log中的修改前的行
• 记录Redo Log，包括Undo Log中的变化 

多次更新后，回滚指针会把不同版本的记录串在一起。在InnoDB中存在purge线程，它会查询那些比现在最老的活动事务还早的Undo Log，并删除它们，从而保证Undo Log文件不至于无限增长。

3、提交与回滚

当事务正常提交时，InnoDB只需要更改事务状态为commit即可，不需要做其他额外的工作

回滚（rollback）需要根据当前回滚指针从Undo Log中找出事务修改前的版本，并恢复。如果事务影响的行非常多，回滚则可能会很慢，根据经验值没提交的事务行数在1000~10000之间，InnoDB效率还是非常高的（唐成-数据库多版本实现内幕）。

commit效率高，rollback代价大

4、可见性

事务隔离是数据库处理的基础之一，隔离是缩写ACID中的I。隔离级别是当多个事务同时进行更改和执行查询时，微调性能、可靠性、一致性和结果再现性之间的平衡的设置。

InnoDB提供SQL1992标准定义的四个隔离级别，READ UNCOMMITTED（未提交读）, READ COMMITTED（已提交读）, REPEATABLE READ（可重复读）, and SERIALIZABLE（可串行化）。默认的是REPEATABLE READ

每种隔离级别具体的意义可以百度查到，实现原理深入进去比较复杂。注意到每条数据隐藏的事务ID字段DB_TRX_ID有时序性，理论上可以根据一些策略，借助这个字段来实现与隔离级别相关的功能。事实上InnoDB也是这么做的。当然这个功能还涉及很多锁的问题，这里不再展开。

MySQL官方文档在“锁和事务模型”这一章节开始就介绍了InnoDB的锁，感兴趣可以去读一下。

问题三、说说你对binlog、redo log、undo log了解或者实用场景。

binlog使用场景

在实际应用中， binlog 的主要使用场景有两个，分别是 主从复制 和 数据恢复 。

1. 主从复制 ：在 Master 端开启 binlog ，然后将 binlog发送到各个 Slave 端， Slave 端重放 binlog 从而达到主从数据一致。

2. 数据恢复 ：通过使用 mysqlbinlog 工具来恢复数据。

binlog刷盘时机

对于 InnoDB 存储引擎而言，只有在事务提交时才会记录biglog ，此时记录还在内存中，那么 biglog是什么时候刷到磁盘中的呢？

mysql 通过 sync_binlog 参数控制 biglog 的刷盘时机，取值范围是 0-N：

• 0：不去强制要求，由系统自行判断何时写入磁盘；

• 1：每次 commit 的时候都要将 binlog 写入磁盘；

• N：每N个事务，才会将 binlog 写入磁盘。

从上面可以看出， sync_binlog 最安全的是设置是 1 ，这也是MySQL 5.7.7之后版本的默认值。但是设置一个大一些的值可以提升数据库性能，因此实际情况下也可以将值适当调大，牺牲一定的一致性来获取更好的性能。

binlog日志格式

binlog 日志有三种格式，分别为 STATMENT 、 ROW 和 MIXED。

在 MySQL 5.7.7 之前，默认的格式是 STATEMENT ， MySQL 5.7.7 之后，默认值是 ROW。日志格式通过 binlog-format 指定。

• STATMENT：基于SQL 语句的复制( statement-based replication, SBR )，每一条会修改数据的sql语句会记录到binlog 中  。

• • 优点：不需要记录每一行的变化，减少了 binlog 日志量，节约了  IO  , 从而提高了性能；

  • 缺点：在某些情况下会导致主从数据不一致，比如执行sysdate() 、  slepp()  等 。

• ROW：基于行的复制(row-based replication, RBR )，不记录每条sql语句的上下文信息，仅需记录哪条数据被修改了 。

• • 优点：不会出现某些特定情况下的存储过程、或function、或trigger的调用和触发无法被正确复制的问题 ；

  • 缺点：会产生大量的日志，尤其是` alter table ` 的时候会让日志暴涨

• MIXED：基于STATMENT 和 ROW 两种模式的混合复制(mixed-based replication, MBR )，一般的复制使用STATEMENT 模式保存 binlog ，对于 STATEMENT 模式无法复制的操作使用 ROW 模式保存 binlog

redo log

为什么需要redo log

我们都知道，事务的四大特性里面有一个是 持久性 ，具体来说就是只要事务提交成功，那么对数据库做的修改就被永久保存下来了，不可能因为任何原因再回到原来的状态 。

那么 mysql是如何保证一致性的呢？

最简单的做法是在每次事务提交的时候，将该事务涉及修改的数据页全部刷新到磁盘中。但是这么做会有严重的性能问题，主要体现在两个方面：

1. 因为 Innodb 是以 页 为单位进行磁盘交互的，而一个事务很可能只修改一个数据页里面的几个字节，这个时候将完整的数据页刷到磁盘的话，太浪费资源了！

2. 一个事务可能涉及修改多个数据页，并且这些数据页在物理上并不连续，使用随机IO写入性能太差！

因此 mysql 设计了 redo log ， 具体来说就是只记录事务对数据页做了哪些修改，这样就能完美地解决性能问题了(相对而言文件更小并且是顺序IO)。

redo log基本概念

redo log 包括两部分：一个是内存中的日志缓冲( redo log buffer )，另一个是磁盘上的日志文件( redo logfile)。

mysql 每执行一条 DML 语句，先将记录写入 redo log buffer，后续某个时间点再一次性将多个操作记录写到 redo log file。这种 先写日志，再写磁盘 的技术就是 MySQL
里经常说到的 WAL(Write-Ahead Logging) 技术。

在计算机操作系统中，用户空间( user space )下的缓冲区数据一般情况下是无法直接写入磁盘的，中间必须经过操作系统内核空间( kernel space )缓冲区( OS Buffer )。

因此， redo log buffer 写入 redo logfile 实际上是先写入 OS Buffer ，然后再通过系统调用 fsync() 将其刷到 redo log file
中，过程如下：

mysql 支持三种将 redo log buffer 写入 redo log file 的时机，可以通过 innodb_flush_log_at_trx_commit 参数配置，各参数值含义如下：

redo log记录形式

前面说过， redo log 实际上记录数据页的变更，而这种变更记录是没必要全部保存，因此 redo log实现上采用了大小固定，循环写入的方式，当写到结尾时，会回到开头循环写日志。如下图：

同时我们很容易得知， 在innodb中，既有redo log 需要刷盘，还有 数据页 也需要刷盘， redo log存在的意义主要就是降低对 数据页 刷盘的要求 ** 。

在上图中， write pos 表示 redo log 当前记录的 LSN (逻辑序列号)位置， check point 表示 数据页更改记录 刷盘后对应 redo log 所处的 LSN(逻辑序列号)位置。

write pos 到 check point 之间的部分是 redo log 空着的部分，用于记录新的记录；check point 到 write pos 之间是 redo log 待落盘的数据页更改记录。当 write pos追上check point 时，会先推动 check point 向前移动，空出位置再记录新的日志。

启动 innodb 的时候，不管上次是正常关闭还是异常关闭，总是会进行恢复操作。因为 redo log记录的是数据页的物理变化，因此恢复的时候速度比逻辑日志(如 binlog )要快很多。

重启innodb 时，首先会检查磁盘中数据页的 LSN ，如果数据页的LSN 小于日志中的 LSN ，则会从 checkpoint 开始恢复。

还有一种情况，在宕机前正处于checkpoint 的刷盘过程，且数据页的刷盘进度超过了日志页的刷盘进度，此时会出现数据页中记录的 LSN 大于日志中的 LSN，这时超出日志进度的部分将不会重做，因为这本身就表示已经做过的事情，无需再重做。

redo log与binlog区别

由 binlog 和 redo log 的区别可知：binlog 日志只用于归档，只依靠 binlog 是没有 crash-safe 能力的。

但只有 redo log 也不行，因为 redo log 是 InnoDB特有的，且日志上的记录落盘后会被覆盖掉。因此需要 binlog和 redo log二者同时记录，才能保证当数据库发生宕机重启时，数据不会丢失。

undo log

数据库事务四大特性中有一个是 原子性 ，具体来说就是 原子性是指对数据库的一系列操作，要么全部成功，要么全部失败，不可能出现部分成功的情况。

实际上， 原子性 底层就是通过 undo log 实现的。undo log主要记录了数据的逻辑变化，比如一条 INSERT 语句，对应一条DELETE 的 undo log ，对于每个 UPDATE 语句，对应一条相反的 UPDATE 的 undo log ，这样在发生错误时，就能回滚到事务之前的数据状态。

同时， undo log 也是 MVCC(多版本并发控制)实现的关键。

问题四、MySQL中是如何利用binlog和redo log解决一致性问题的？

是先写binlog还是先写redo log的呢？

• 写binlog和redo log的顺序对于数据库系统的持久性和主从复制会不会产生影响？
• 如果有影响，MySQL又是怎么做到binlog和redo log的一致性的呢？
• 带着这些问题，我深入地研究了MySQL中binlog和redo log的一致性问题。

针对这个疑问，我们可以做出两个假设。

假设一：先写redo log再写binlog
想象一下，如果数据库系统在写完一个事务的redo log时发生crash，而此时这个事务的binlog还没有持久化。在数据库恢复后，主库会根据redo log中去完成此事务的重做，主库中就有可这个事务的数据。但是，由于此事务并没有产生binlog，即使主库恢复后，关于此事务的数据修改也不会同步到从库上，这样就产生了主从不一致的错误。

假设二：先写binlog再写redo log
想象一下，如果数据库系统在写完一个事务的binlog时发生crash，而此时这个事务的redo log还没有持久化，或者说此事务的redo log还没记录完（至少没有记录commit log）。在数据库恢复后，从库会根据主库中记录的binlog去回放此事务的数据修改。但是，由于此事务并没有产生完整提交的redo log，主库在恢复后会回滚该事务，这样也会产生主从不一致的错误。

通过上面的假设和分析，我们可以看出，不管是先写redo log还是先写binlog，都有可能会产生主从不一致的错误，那么MySQL又是怎么做到binlog和redo log的一致性的呢？

MySQL的内部XA（两阶段提交）
XA-2PC (two phase commit, 两阶段提交 )
XA是由X/Open组织提出的分布式事务的规范。XA规范主要定义了(全局)事务管理器(TM: Transaction Manager)和(局部)资源管理器(RM: Resource Manager)之间的接口。XA为了实现分布式事务，将事务的提交分成了两个阶段：也就是2PC (tow phase commit)，XA协议就是通过将事务的提交分为两个阶段来实现分布式事务。

• prepare 阶段：第一阶段，事务管理器向所有涉及到的数据库服务器发出prepare"准备提交"请求，数据库收到请求后执行数据修改和日志记录等处理，处理完成后只是把事务的状态改成"可以提交",然后把结果返回给事务管理器.
• commit 阶段：事务管理器收到回应后进入第二阶段，如果在第一阶段内有任何一个数据库的操作发生了错误，或者事务管理器收不到某个数据库的回应，则认为事务失败，回撤所有数据库的事务。数据库服务器收不到第二阶段的确认提交请求，也会把"可以提交"的事务回撤。如果第一阶段中所有数据库都提交成功，那么事务管理器向数据库服务器发出"确认提交"请求，数据库服务器把事务的"可以提交"状态改为"提交完成"状态，然后返回应答。

MySQL中的XA实现分为：外部XA和内部XA。前者是指我们通常意义上的分布式事务实现；后者是指单台MySQL服务器中，Server层作为TM(事务协调者)，而服务器中的多个数据库实例作为RM，而进行的一种分布式事务，也就是MySQL跨库事务；也就是一个事务涉及到同一条MySQL服务器中的两个innodb数据库(因为其它引擎不支持XA)。

内部XA的额外功能

在MySQL内部，在事务提交时利用两阶段提交(内部XA的两阶段提交)很好地解决了上面提到的binlog和redo log的一致性问题：

• 第一阶段： InnoDB Prepare阶段。此时SQL已经成功执行，并生成事务ID(xid)信息及redo和undo的内存日志。此阶段InnoDB会写事务的redo log，但要注意的是，此时redo log只是记录了事务的所有操作日志，并没有记录提交（commit）日志，因此事务此时的状态为Prepare。此阶段对binlog不会有任何操作。
• 第二阶段：commit 阶段，这个阶段又分成两个步骤。第一步写binlog（先调用write()将binlog内存日志数据写入文件系统缓存，再调用fsync()将binlog文件系统缓存日志数据永久写入磁盘）；第二步完成事务的提交（commit），此时在redo log中记录此事务的提交日志（增加commit 标签）。

可以看出，此过程中是先写redo log再写binlog的。但需要注意的是，在第一阶段并没有记录完整的redo log（不包含事务的commit标签），而是在第二阶段记录完binlog后再写入redo log的commit 标签。还要注意的是，在这个过程中是以第二阶段中binlog的写入与否作为事务是否成功提交的标志。

通过上述MySQL内部XA的两阶段提交就可以解决binlog和redo log的一致性问题。数据库在上述任何阶段crash，主从库都不会产生不一致的错误。

此时的崩溃恢复过程如下：

• 如果数据库在记录此事务的binlog之前和过程中发生crash。数据库在恢复后认为此事务并没有成功提交，则会回滚此事务的操作。与此同时，因为在binlog中也没有此事务的记录，所以从库也不会有此事务的数据修改。
• 如果数据库在记录此事务的binlog之后发生crash。此时，即使是redo log中还没有记录此事务的commit 标签，数据库在恢复后也会认为此事务提交成功（因为在上述两阶段过程中，binlog写入成功就认为事务成功提交了）。它会扫描最后一个binlog文件，并提取其中的事务ID（xid），InnoDB会将那些状态为Prepare的事务（redo log没有记录commit 标签）的xid和Binlog中提取的xid做比较，如果在Binlog中存在，则提交该事务，否则回滚该事务。这也就是说，binlog中记录的事务，在恢复时都会被认为是已提交事务，会在redo log中重新写入commit标志，并完成此事务的重做（主库中有此事务的数据修改）。与此同时，因为在binlog中已经有了此事务的记录，所有从库也会有此事务的数据修改。

总结
上述利用两阶段提交解决了事务提交时binlog和redo log的一致性问题，此过程的实现是在MySQL 5.6 之前。但是此过程存在严重缺陷：此过程中为了保证MySQL Server层binlog的写入顺序和InnoDB层的事务提交顺序是一致的，MySQL数据库内部使用了prepare_commit_mutex这个锁。但是在启用了这个锁之后，并不能并发写入binlog，从而导致了group commit失效。这个问题在MySQL 5.6中的Binary Log Group Commit（BLGC）得到解决。