MySQL事务还能这么理解？这回我知道怎么应付面试官了

1、引言

想必参加过后台开发面试的伙伴们都知道，MySQL事务这玩意是各大面试官百问不厌的知识点，但是大家对于事务的了解到什么层面呢，仅仅停留在ACID上么，这篇文章将陪着大家一起深入MySQL中的事务。

2、事务的特性

引言中所提到的ACID正是事务的四个特性：分别是原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability）

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

其中一致性不太好理解，一致性是说无论事务提交还是回滚，不会破坏数据的完整性。比如A给B转100元，如果成功了，A的账户必定会扣100元，而B的账户必定会增加100元；如果失败了，A和B的账户余额不会改变。A和B中的账户金额变动必然是一个完整的过程（不可能是A扣除了50，B增加了50这种情况），整个过程必须是一致的。

2.1 原子性

事务的原子性是指：一个事务中的多个操作都是不可分割的，只能是全部执行成功、或者全部执行失败。
MySQL事务的原子性是通过undo log来实现的。undo log是InnoDB存储引擎特有的。具体的实现机制是：将所有对数据的修改（增、删、改）都写入日志（undo log）。

undo log是逻辑日志，可以理解为：记录和事务操作相反的SQL语句，事务执行insert语句，undo log就记录delete语句。它以追加写的方式记录日志，不会覆盖之前的日志。除此之外undo log还用来实现数据库多版本并发控制（Multiversion Concurrency Control，简称MVCC）。
如果一个事务中的一部分操作已经成功，但另一部分操作，由于断电/系统崩溃/其它的软硬件错误而无法成功执行，则通过回溯日志，将已经执行成功的操作撤销，从而达到全部操作失败的目的。

2.2 持久性

事务的持久性是指：一个事务对数据的所有修改，都会永久地保存在数据库中。
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提供了三种持久化方式:这里有一个参数
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结束后，数据库恢复到一致性状态，可以继续被使用。

2.3 隔离性

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

2.3.1 第一类丢失更新

一个事务在撤销的时候，覆盖了另一个事务已提交的更新数据。 假设现在有两个事务A、B同时操作同一账户的金额，如下图所示：

MySQL事务还能这么理解？这回我知道怎么应付面试官了

显然，事务B在撤销事务的时候，覆盖了事务A在T4阶段已经提交的更新数据。A在T3的时候已经取走了200元，此时的余额应该是800元，但是由于事务B开始的时候，余额是1000元，所以回滚后，余额也会变成1000元。这样一来，用户明明取了钱，但是余额不变，银行亏到姥姥家了。

2.3.2 脏读

一个事务读到了另一个事务未提交的更新数据。
用下图说明：

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

2.3.3 幻读

幻读（虚读）是指：一个事务读到了另一个事务已提交的新增数据。
依然是配图说明：

事务B在同一个事务中执行两次统计操作之间，另一事务insert了一条记录，导致得到的结果不一样，好像发生了幻觉。还有一种情况是事务B更新了表中所有记录的某一字段，之后事务A又插入了一条记录，事务B再去查询发现有一条记录没有被更新，这也是幻读。

2.3.4 不可重复读

不可重复读：一个事务读到了另一个事务已提交的更新数据。
不可重复读，顾名思义，就是在同一个事务中重复读取数据会发生不一致的情况，如下图：

MySQL事务还能这么理解？这回我知道怎么应付面试官了

事务B在T2和T5阶段都执行了查询余额的操作，但是每次得到的结果都不一样，这在开发中是不允许的，同一个事务中同样的多次查询，每次返回不一样的结果，让人不免会对数据库的可靠性产生怀疑。

2.3.5 第二类丢失更新

一个事务在提交的时候，覆盖了另一个事务已提交的更新数据。

MySQL事务还能这么理解？这回我知道怎么应付面试官了

由上图可以看出，当事务A提交之后，账户余额已经发生了变动，然后事务B还是基于原始金额（即1000）的基础上扣除取款金额的，事务B以提交，就是把事务A的提交给完全覆盖了。此为第二类丢失更新。

注意和第一类丢失更新区分，第一类丢失更新重点在事务B最终撤销了事务，第二类是最终提交了事务。

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

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

3、Repeatable Read

Repeatable Read（可重复读）是MySQL默认的隔离级别，也是使用最多的隔离级别，所以单独拿出来深入理解很有必要。Repeatable Read无法解决幻读（虚读）问题。下面来看一个实例。
首先创建一个表并插入一条记录：

CREATE TABLE `student` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  `stu_id` bigint(20) NOT NULL DEFAULT '0' COMMENT '学生学号',
  `stu_name` varchar(100) DEFAULT NULL COMMENT '学生姓名',
  `created_date` datetime NOT NULL COMMENT '创建时间',
  `modified_date` datetime NOT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  `ldelete_flag` tinyint(1) NOT NULL DEFAULT '0' COMMENT '逻辑删除标志，0：未删除，2：已删除',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='学生信息表';

INSERT INTO `student` VALUES (1, 230160340, 'Carson', '2016-08-20 16:37:00', '2016-08-31 16:37:05', 0);

同样的开启两个事务，如下表所示：

按照上述理论，会出现幻读现象。也就是事务A在T4时间段的查询select会看到事务B提交的新增数据。
但让你失望了。

执行结果如下

和预期的结果并不一致，没有出现幻读现象。
实际上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的时候插入一条数据Luffy，然后提交。在T4的时候把Luffy更新成Katakuri，根据上一个例子的经验，此时事务A是看不到Luffy这条数据的，所以更新也不会成功，并且在T5的时候查询，和T1时候一样，只有Carson和Kata两条数据。

但是，又要让你失望了

执行结果如下

但是执行结果却不是预期的那样，事务A不仅看到了Luffy，还把它成功的改成了Katakuri。即使事务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 ...

4、小结

本文主要讲解了MySQL事务的ACID四大特性，undo log和redo log分别实现了原子性和持久性，log持久化的三种方式，数据库并发下的五类问题、四种隔离级别、RR隔离级别下select幻读通过MVCC机制解决、select ... lock in share mode/select ... for
update/insert/update/delete的幻读通过间隙锁来解决。
本文涉及的比较深入，掌握好本文的知识点，让你不仅仅是停留在ACID、隔离级别的层面，在面试中能够化被动为主动，收割大厂offer。

作者：少侠露飞
链接：https://juejin.cn/post/6945713828470620191
来源：掘金