面试官：MySQL事务是怎么实现的「终于解决」

前言

用过MySQL的同学都知道，它的InnoDB存储引擎，是通过事务来保证数据的一致性的。

数据库事务通常包含了一个序列的对数据库的读/写操作。包含有以下两个目的：
为数据库操作序列提供了一个从失败中恢复到正常状态的方法，同时提供了数据库即使在异常状态下仍能保持一致性的方法。
当多个应用程序在并发访问数据库时，可以在这些应用程序之间提供一个隔离方法，以防止彼此的操作互相干扰。

特性

说到事务就不得不说它的四个特性：ACID。分别是原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability）

• 原子性（Atomicity）：事务作为一个整体被执行，包含在其中的对数据库的操作要么全部被执行，要么都不执行。
• 一致性（Consistency）：事务应确保数据库的状态从一个一致状态转变为另一个一致状态。一致状态的含义是数据库中的数据应满足完整性约束。
• 隔离性（Isolation）：多个事务并发执行时，一个事务的执行不应影响其他事务的执行。
• 持久性（Durability）：已被提交的事务对数据库的修改应该永久保存在数据库中。

其中一致性不太好理解，一致性是说无论事务提交还是回滚，不会破坏数据的完整性。比如A给B转100元，如果成功了，A的账户必定会扣100元，而B的账户必定会增加100元；如果失败了，A和B的账户余额不会改变。不会存在其他的情况，这样就是符合一致性的。

事实上，原子性、持久性和隔离性都是为了保证一致性。

原子性

事务的原子性是指：一个事务中的多个操作都是不可分割的，只能是全部执行成功、或者全部执行失败。
MySQL事务的原子性是通过undo log来实现的。undo log是InnoDB存储引擎特有的。具体的实现方式是：将所有对数据的修改（增、删、改）都写入日志（undo log）。如果一个事务中的一部分操作已经成功，但另一部分操作，由于断电/系统崩溃/其它的软硬件错误而无法成功执行，则通过回溯日志，将已经执行成功的操作撤销，从而达到全部操作失败的目的。

undo log是逻辑日志，可以理解为：记录和事务操作相反的SQL语句，事务执行insert语句，undo log就记录delete语句。它以追加写的方式记录日志，不会覆盖之前的日志。除此之外undo log还用来实现数据库多版本并发控制（Multiversion Concurrency Control，简称MVCC）

持久性

事务的持久性是指：一个事务对数据的所有修改，都会永久的保存在数据库中。
MySQL事务的持久性是通过redo log来实现的。redo log也是InnoDB存储引擎特有的。具体实现方式是：当发生数据修改（增、删、改）的时候，InnoDB引擎会先将记录写到redo log中，并更新内存，此时更新就算完成了。同时InnoDB引擎会在合适的时机将记录刷到磁盘中。
redo log是物理日志，记录的是在某个数据页做了什么修改，而不是SQL语句的形式。它有固定大小，是循环写的方式记录日志，空间用完后会覆盖之前的日志。

undo log和redo log并不是直接写到磁盘上的，而是先写入log buffer。再等待合适的时机同步到OS buffer，再由操作系统决定何时刷到磁盘，具体过程如下：

既然undo log和redo log都是从log buffer 到 OS buffer，再到磁盘。所以中途还是有可能因为断电/硬件故障等原因导致日志丢失。为此MySQL提供了三种持久化方式，之前的博客：MySQL优化：参数 中提到过一个参数innodb_flush_log_at_trx_commit，这个参数主要控制InnoDB将log buffer中的数据写入OS buffer，并刷到磁盘的时间点，取值分别为0，1，2，默认是1。这三个值的意思分别如下

首先查看MySQL默认设置的方式1，也就是每次提交后直接写入OS buffer，并且调用系统函数fsync()把日志写到磁盘上。就保证数据一致性的角度来说，这种方式无疑是最安全的。但是我们都知道，安全大多数时候意味着效率偏低。每次提交都直接写入OS buffer并且写到磁盘，无疑会导致单位时间内IO的次数过多而效率低下。除此之外，还有方式0和方式2。基本上都是每秒写入磁盘一次，所以效率都比方式1更高。但是方式0是把数据先写入log buffer再写入OS buffer再写入磁盘，而方式2是直接写入OS buffer，再写入磁盘，少了一次数据拷贝的过程（从log buffer到OS buffer），所以方式2比方式0更加高效。

了解了undo log和redo log的作用和实现过程之后，再来看一下这两个日志具体是怎么让数据库从异常的状态恢复到正常状态的。
数据库系统崩溃后重启，此时数据库处于不一致的状态，必须先执行一个crash recovery的过程：首先读取redo log，把成功提交但是还没来得及写入磁盘的数据重新写入磁盘，保证了持久性。再读取undo log将还没有成功提交的事务进行回滚，保证了原子性。crash recovery结束后，数据库恢复到一致性状态，可以继续被使用。

隔离性

数据库事务的隔离性是指：多个事务并发执行时，一个事务的执行不应影响其他事务的执行。正常情况下，肯定是多个事务同时操作同一个数据库，所以事务之间的隔离就显得必不可少。先看一下，如果没有隔离性，会发生哪些问题。

第一类丢失更新

第一类丢失更新是指：一个事务在撤销的时候，覆盖了另一个事务已提交的更新数据
假设事务A和事务B操作同一个账户的金：

事务B在撤销事务的时候，覆盖了事务A在T4的时候已经提交的更新数据。A在T3的时候已经取走了100元，此时的余额应该是400元，但是由于事务B开始的时候，余额是500元，所以回滚后，余额也会变成500元。

脏读

脏读：一个事务读到了另一个事务未提交的更新数据

事务A在T3的时候取走了400元，但是未提交。事务B在T4时查询余额就能看到事务A未提交的更新。

幻读

幻读（虚读）是指：一个事务读到了另一个事务已提交的新增数据

事务B在同一个事务中执行两次统计操作，得到的结果不一样

不可重复读

不可重复读：一个事务读到了另一个事务已提交的更新数据

事务B在同一个事务中，两次读取余额，得到的结果却不一样。

第二类丢失更新

第二类丢失更新是指：一个事务在提交的时候，覆盖了另一个事务已提交的更新数据

事务A和事务B分别取了100元，所以余额应该为300元。但是事务B在提交的时候，覆盖了事务A已提交的更新数据，所以导致结果出错。

为了解决这五类问题，MySQL提供了四种隔离级别：

• Serializable（串行化）：事务之间以一种串行的方式执行，安全性非常高，效率低
• Repeatable Read（可重复读）：是MySQL默认的隔离级别，同一个事务中相同的查询会看到同样的数据行，安全性较高，效率较好
• Read Commited（读已提交）：一个事务可以读到另一个事务已经提交的数据，安全性较低，效率较高
• Read Uncommited（读未提交）：一个事务可以读到另一个事务未提交的数据，安全性低，效率高

隔离级别与并发性能的关系图如下

需要注意的是，除了Serializable能完完全全的解决这五类问题之外，其余的三种隔离级别都不能完全解决这五类问题。各种隔离级别能解决的问题对应如下

Repeatable Read

Repeatable Read（可重复读）是MySQL默认的隔离级别，也是使用最多的隔离级别，所以单独拿出来深入理解很有必要。Repeatable Read无法解决虚读（幻读）问题。下面来看一个实例
SQL脚本

# 建表
CREATE TABLE student  (
  id int(4) primary key auto_increment,
  name varchar(10)
) ENGINE = InnoDB;

# 插入一条数据
insert into student(name) values('zhangsan');

开启两个操作窗口，均关闭自动提交set autocommit = 0，其余的操作的时间线如下：

按照上述理论，会出现幻读现象。也就是事务A的第二次select会看到事务B提交的新增数据。
执行结果如下

mysql> select * from student;
+----+----------+
| id | name     |
+----+----------+
|  1 | zhangsan |
+----+----------+
1 row in set (0.00 sec)

和预期的结果并不一致，没有出现幻读现象。
实际上MySQL在Repeatable Read隔离级别下，用MVCC（Multiversion Concurrency Control，多版本并发控制）解决了select普通查询的幻读现象。
具体的实现方式就是事务开始时，第一条select语句查询结果集会生成一个快照（snapshot），并且这个事务结束前，同样的select语句返回的都是这个快照的结果，而不是最新的查询结果，这就是MySQL在Repeatable Read隔离级别对普通select语句使用的快照读（snapshot read）。

快照读和MVCC是什么关系呢？

MVCC是多版本并发控制，快照就是其中的一个版本。所以可以说MVCC实现了快照读，具体的实现方式涉及到MySQL的隐藏列。MySQL会给每个表自动创建三个隐藏列

• DB_TRX_ID：事务ID，记录操作（增、删、改）该数据事务的事务ID
• DB_ROLL_PTR：回滚指针，记录上一个版本的数据在undo log中的位置
• DB_ROW_ID：隐藏ID ，创建表没有合适的索引作为聚簇索引时，会用该隐藏ID创建聚簇索引

由于undo log中记录了各个版本的数据，并且通过DB_ROLL_PTR可以找到各个历史版本，并且由DB_TRX_ID决定使用哪个版本（快照）。所以相当于undo log实现了MVCC，MVCC实现了快照读。

如此看来，MySQL的Repeatable Read隔离级别利用快照读，已经解决了幻读的问题。但是事实并非如此，接下来再看一个例子
继续上面的操作（注意：开始前，先提交上一次操作的事务）

事务A在T1的时候生成快照，事务B在T2的时候插入一条数据wangwu，然后提交。在T4的时候把wangwu更新成zhaoliu，根据上一个例子的经验，此时事务A是看不到wangwu这条数据的，所以更新也不会成功，并且在T5的时候查询，和T1时候一样，只有zhangsan和lisi两条数据。
执行结果如下

mysql> select * from student;
+----+----------+
| id | name     |
+----+----------+
|  1 | zhangsan |
|  2 | lisi     |
|  3 | zhaoliu  |
+----+----------+
3 rows in set (0.00 sec)

但是执行结果却不是预期的那样，事务A不仅看到了wangwu，还把它成功的改成了zhaoliu。即使事务A成功commit之后，再次查询还是这样。

这其实是MySQL对insert、update和delete语句所使用的当前读（current read）。因为涉及到数据的修改，所以MySQL必须拿到最新的数据才能修改，所以涉及到数据的修改肯定不能使用快照读（snapshot read）。由于事务A读到了事务B已提交的新增数据，所以就产生了前文所说的幻读。

那么在Repeatable Read隔离级别是怎么解决幻读的呢？

是通过间隙锁（Gap Lock）来解决的。我们都知道InnoDB支持行锁，并且行锁是锁住索引。而间隙锁用来锁定索引记录间隙，确保索引记录的间隙不变。间隙锁是针对事务隔离级别为Repeatable Read或以上级别而已的，间隙锁和行锁一起组成了Next-Key Lock。当InnoDB扫描索引记录的时候，会首先对索引记录加上行锁，再对索引记录两边的间隙加上间隙锁（Gap Lock）。加上间隙锁之后，其他事务就不能在这个间隙插入记录。这样就有效的防止了幻读的发生。

默认情况下，InnoDB工作在Repeatable Read的隔离级别下，并且以Next-Key Lock的方式对索引行进行加锁。当查询的索引具有唯一性（主键、唯一索引）时，Innodb存储引擎会对Next-Key Lock进行优化，将其降为行锁，仅仅锁住索引本身，而不是范围（除非锁定不存在的值）。若是普通索引，则会使用Next-Key Lock将记录和间隙一起锁定。

• 使用快照读的语句

  select * from ...
  

• 使用当前读的语句

  select * from ... lock in share mode
  select * from ... for update
  insert into table...
  update table set ...
  delete table where ...
  

更新

感谢@mo_qy在评论区的提醒

前一节Repeatable Read中部分内容描述有误，第二个实验

事务A在T1时刻和T5时刻，两条相同的查询语句在同一事务中看到了不同的结果集，这是符合幻读的定义的，MySQL官方定如下

The so-called phantom problem occurs within a transaction when the same query produces different sets of rows at different times.
—— https://dev.mysql.com/doc/refman/5.6/en/innodb-next-key-locking.html

这个实验中出现幻读的原因是事务A中没有使用到Next-Key Lock。仅仅是select子句只会产生快照读，不会加锁。要想使用Next-Key Lock需要改成select ... lock in share mode或者select ... for update。现在改造下上面的实验，首先看下表中数据

mysql> select * from student;
+----+----------+
| id | name     |
+----+----------+
|  2 | lisi     |
|  1 | zhangsan |
+----+----------+
2 rows in set (0.00 sec)

实验步骤如下（记得关闭事务自动提交）

事务A在T1时刻执行的语句会给id > 1的索引和间隙加上Next-Key Lock，此时事务B便不能在记录2的后面插入新的数据，所以事务A在T1是可和T4时刻会查询到相同的结果集，这样就解决了幻读问题。

总结

本文主要讲解了MySQL事务的ACID四大特性、undo log和redo log分别实现了原子性和持久性、log持久化的三种方式、数据库并发下的五类问题、四种隔离级别、RR隔离级别下select幻读通过MVCC机制解决、select ... lock in share mode/select ... for update/insert/update/delete的幻读通过间隙锁来解决。

参考

• https://dev.mysql.com/doc/refman/5.6/en/innodb-transaction-isolation-levels.html
• https://www.cnblogs.com/zhoujinyi/p/3435982.html
• https://dev.mysql.com/doc/refman/5.6/en/innodb-next-key-locking.html