MySQL - 事务 - ACID的原理

主要用于了解学习事务原理, 从而在工作中高效使用事务机制, 当然还有应对面试.

​​​​​​​

文档

https://mp.weixin.qq.com/s/ZXpS9rs3ywFaSrN5ftbgmw // 文章已经被作者删除

高性能 MySQL 实战 | Laravel 学院 // SQL 更新语句的执行流程与日志写入

14丨什么是事务处理，如何使用COMMIT和ROLLBACK进行操作？-极客时间 

15丨初识事务隔离：隔离的级别有哪些，它们都解决了哪些异常问题？-极客时间

31丨为什么大部分RDBMS都会支持MVCC？-极客时间

事务和锁机制是什么关系？ 开启事务就自动加锁了吗？ - 南哥的天下 - 博客园 -- 推荐阅读，但要验证

并发事务存在的问题和 MySQL 事务隔离级别 - Laravel学院 -- 推荐​​​​​​​

通过 MVCC（多版本并发控制）保证数据库事务的一致性 - Laravel 学院

MySQL事务与锁 - 知乎 -- 推荐，并且整理出来思维导图

开始之前，还是有必要说一句，

事务操作「很多数据库机制」都是针对 写操作 而言，如果全是读操作，是没有必要的，

原因想一想就会知道「因为是无害的，无影响的，是否并发访问结果都一样的」

很多服务都是如此，读不用处理，写才是要重点处理的。

引言

照例，我们先来一个场景

面试官: "知道事务的四大特性么?"
你: "懂，ACID嘛，原子性(Atomicity)、一致性(Consistency)、隔离性(Isolation)、持久性(Durability)！"
面试官：“你们是用MySQL 数据库吧，能简单说说Innodb中怎么实现这四大特性的么？”
你: "我只知道隔离性是怎么做的balabala~~"
面试官: "还是回去等通知吧~"

OK，回到正题。

说到事务的四大特性原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability），懂的人很多。

但是稍微涉及细节一点，这四大特性在数据库中的实现原理是怎么样的？那就没有几个人能够答得上来了。

因此，我们这篇文章着重讨论一下四大特性在MySQL 中的实现原理。

正文

我们以从A账户转账50元到B账户为例【经典举例】进行说明一下ACID，四大特性。

原子性

根据定义，原子性是指一个事务是一个不可分割的工作单位，其中的操作要么都做，要么都不做。

即要么转账成功，要么转账失败，是不存在中间的状态！
如果无法保证原子性会怎么样？
OK，就会出现数据不一致的情形，A账户减去50元，而B账户增加50元操作失败。系统将无故丢失50元~

一致性

根据定义，一致性是指事务执行前后，数据处于一种合法的状态，这种状态是语义上的而不是语法上的。
那什么是合法的数据状态呢？
OK，这个状态是满足预定的约束就叫做合法的状态，再通俗一点，这状态是由你自己来定义的。

满足这个状态，数据就是一致的，不满足这个状态，数据就是不一致的！

如果无法保证一致性会怎么样？
例一:

A账户有200元，转账300元出去，此时A账户余额为-100元。你自然就发现了此时数据是不一致的，为什么呢？因为你定义了一个状态，余额这列必须大于0。

例二:

A账户200元，转账50元给B账户，A账户的钱扣了，但是B账户因为各种意外，余额并没有增加。你也知道此时数据是不一致的，为什么呢？因为你定义了一个状态，要求A+B的余额必须不变。

隔离性

根据定义，隔离性是指多个事务并发执行的时候，事务内部的操作与其他事务是隔离的，并发执行的各个事务之间不能互相干扰。

如果无法保证隔离性会怎么样？
OK，假设A账户有200元，B账户0元。A账户往B账户转账两次，金额为50元，分别在两个事务中执行。

如果无法保证隔离性，会出现下面的情形

如图所示，如果不保证隔离性，A扣款两次，而B只加款一次，凭空消失了50元，依然出现了数据不一致的情形！

PS

可能有细心的读者已经发现了，MySQL 中是依靠锁来解决隔离性问题。

嗯，我们后面来说明。

持久性

根据定义，持久性是指事务一旦提交，它对数据库的改变就应该是永久性的。

接下来的其他操作或故障不应该对其有任何影响。

如果无法保证持久性会怎么样？
在MySQL 中，为了解决CPU和磁盘速度不一致问题，MySQL 是将磁盘上的数据加载到内存，对内存进行操作，然后再回写磁盘。好，假设此时宕机了，在内存中修改的数据全部丢失了，持久性就无法保证。

设想一下，系统提示你转账成功。但是你发现金额没有发生任何改变，此时数据出现了不合法的数据状态，我们将这种状态认为是数据不一致的情形。

实战解答

问题一: MySQL 怎么保证原子性的？

OK，是利用Innodb的undo log。
undo log名为回滚日志，是实现原子性的关键，当事务回滚时能够撤销所有已经成功执行的sql语句，他需要记录你要回滚的相应日志信息。

例如

(1) 当你delete一条数据的时候，就需要记录这条数据的信息，回滚的时候，insert这条旧数据
(2) 当你update一条数据的时候，就需要记录之前的旧值，回滚的时候，根据旧值执行update操作
(3) 当年insert一条数据的时候，就需要这条记录的主键，回滚的时候，根据主键执行delete操作

undo log记录了这些回滚需要的信息，当事务执行失败或调用了rollback，导致事务需要回滚，便可以利用undo log中的信息将数据回滚到修改之前的样子。

PS

具体的undo log日志长啥样，这个可以写一篇文章了。

而且写出来，看的人也不多，姑且先这么简单的理解吧。

问题二: MySQL 怎么保证持久性的？

OK，是利用Innodb的redo log。
正如之前说的，MySQL 是先把磁盘上的数据加载到内存中，在内存中对数据进行修改，再刷回磁盘上。如果此时突然宕机，内存中的数据就会丢失。

怎么解决这个问题？
简单啊，事务提交前直接把数据写入磁盘就行啊。

这么做有什么问题？

只修改一个页面里的一个字节，就要将整个页面刷入磁盘，太浪费资源了。毕竟一个页面16KB大小，你只改其中一点点东西，就要将16KB的内容刷入磁盘，听着也不合理。
毕竟一个事务里的SQL可能牵涉到多个数据页的修改，而这些数据页可能不是相邻的，也就是属于随机IO。

显然操作随机IO，速度会比较慢。
于是，决定采用redo log解决上面的问题。

当做数据修改的时候，不仅在内存中操作，还会在redo log中记录这次操作。

当事务提交的时候，会将redo log日志进行刷盘(redo log一部分在内存中，一部分在磁盘上)。「解析： 事务执行提交的时候，会根据redo log 进行刷盘，但如果这时服务宕机，此时便属于事务已提交但尚未刷盘的情况」

当数据库宕机重启的时候，会将redo log中的内容恢复到数据库中，再根据undo log和binlog内容决定回滚数据还是提交数据。

这部分需要结合 3.问题/补充中的「SQL 更新语句的执行流程与日志写入」一起看，思考会比较好～

采用redo log的好处？
其实好处就是将redo log进行刷盘比对数据页刷盘效率高。

具体表现如下：

1. redo log体积小，毕竟只记录了哪一页修改了啥，因此体积小，刷盘快。
2. redo log是一直往末尾进行追加，属于顺序IO。效率显然比随机IO来的快。

PS

不想具体去谈redo log具体长什么样，因为内容太多了。

问题三: MySQL 怎么保证隔离性的？

OK, 利用的是锁和MVCC机制。

还是拿转账例子来说明，有一个账户表如下
表名t_balance

其中id是主键，user_id为账户名，balance为余额。还是以转账两次为例，如下图所示

至于MVCC, 即多版本并发控制(Multi Version Concurrency Control),。

一个行记录数据有多个版本对快照数据，这些快照数据在undo log中。

如果一个事务读取的行正在做 DELETE 或者 UPDATE 操作，读取操作不会等行上的锁释放，而是读取该行的快照版本。

由于MVCC机制在可重复读(Repeateable Read)和读已提交(Read Commited)的MVCC表现形式不同，就不赘述了。 

但是有一点说明一下，在事务隔离级别为读已提交(Read Commited)时，一个事务能够读到另一个事务已经提交的数据，是不满足隔离性的。

但是当事务隔离级别为可重复读(Repeateable Read)中，是满足隔离性的。

问题四：MySQL 怎么保证一致性的？

OK，这个问题分为两个层面来说。
从数据库层面，数据库通过原子性、隔离性、持久性来保证一致性。

也就是说ACID四大特性之中，C(一致性)是目的，A(原子性)、I(隔离性)、D(持久性)是手段，是为了保证一致性，数据库提供的手段。数据库必须要实现AID三大特性，才有可能实现一致性。「这也是为什么将一致性房放在最后提问的原因」

例如，原子性无法保证，显然一致性也无法保证。

但是，如果你在事务里故意写出违反约束的代码，一致性还是无法保证的。例如，你在转账的例子中，你的代码里故意不给B账户加钱，那一致性还是无法保证。

因此，还必须从应用层角度考虑。

从应用层面，通过代码判断数据库数据是否有效，然后决定回滚还是提交数据！

总结

本文讲了Mysql中事务ACID四大特性的实现原理，希望大家有所收获。

其实，还是要尝试去理解，结合自己已有的知识节点，串联起来，形成自己的知识体系~

学习一定要注意思考，举一反三，不能有一学一，而应该学一个就应该学一类, 个人做的也不好，只是在朝着这个方向努力～～

事务隔离的级别有哪些？

脏读、不可重复读和幻读这三种异常情况，是在 SQL-92 标准中定义的，同时 SQL-92 标准还定义了 4 种隔离级别来解决这些异常情况。解决异常数量从少到多的顺序（比如读未提交可能存在 3 种异常，可串行化则不会存在这些异常）决定了隔离级别的高低，这四种隔离级别从低到高分别是：读未提交（READ UNCOMMITTED ）、读已提交（READ COMMITTED）、可重复读（REPEATABLE READ）和可串行化（SERIALIZABLE）。这些隔离级别能解决的异常情况如下表所示

你能看到可串行化能避免所有的异常情况，而读未提交则允许异常情况发生。

关于这四种级别，我来简单讲解下。

读未提交，也就是允许读到未提交的数据，这种情况下查询是不会使用锁的，可能会产生脏读、不可重复读、幻读等情况。

读已提交就是只能读到已经提交的内容，可以避免脏读的产生，属于 RDBMS 中常见的默认隔离级别（比如说 Oracle 和 SQL Server），但如果想要避免不可重复读或者幻读，就需要我们在 SQL 查询的时候编写带加锁的 SQL 语句（我会在进阶篇里讲加锁）。

可重复读，保证一个事务在相同查询条件下两次查询得到的数据结果是一致的，可以避免不可重复读和脏读，但无法避免幻读。

MySQL 默认的隔离级别就是可重复读。

可串行化，将事务进行串行化，也就是在一个队列中按照顺序执行，可串行化是最高级别的隔离等级，可以解决事务读取中所有可能出现的异常情况，但是它牺牲了系统的并发性。

1. 关于SQL 更新语句的执行流程与日志写入

执行流程

SQL 更新语句包括插入、修改和删除三种操作，我们还是基于上篇教程中的 MySQL 服务端架构图进行介绍，这样会更加直观一些：

和 SQL 查询语句一样，MySQL 客户端提交 SQL 更新语句（表示一个更新请求）前，先要通过连接器建立与服务端的连接，然后就可以执行更新操作了（假设在 test 数据库中已经存在一个 post 数据表，如果没有的话，可以手动创建）：

我们在介绍查询缓存的时候提到过，当一张数据表有更新操作时，对应的查询缓存数据会清空，所以上述插入语句会清空 post 表的所有缓存（修改、删除语句也是一样）。

接下来，分析器会通过词法和语法解析知道这是一条 SQL 插入语句，优化器为其生成对应的执行计划，最后，执行器负责具体执行，插入数据（具体操作交由存储引擎去做）。

以上插入语句，如果是想下面这样的修改语句：

update post set title = 'test title 2' where id = 1;

连接器和查询缓存这里和插入语句都是一样的，在分析器中会解析出这是一条 SQL 修改语句，由于带有 WHERE 查询条件，因此在优化器生成的执行计划会判定是否使用索引（我们可以通过explain 语句预览执行计划，这里可以看到会使用 id 这个主键索引）：

最后，执行器负责具体执行，找到这一行记录，然后进行更新。

与查询流程不一样的是，更新流程还涉及两个重要的日志写入，分别是 redo log（重做日志）和 binlog（归档日志）。

日志写入

我们知道，MySQL 数据库数据是会持久化到磁盘的（在文件系统中有对应的数据目录，关于这一块后面会专门介绍），如果每一次的更新操作都要写入磁盘，整个过程的 IO 成本很高（如果包含查询的话，还有额外的查询成本）。

为了解决这个问题，MySQL 的设计者引入了 WAL 技术（Write-Ahead Logging），即先写日志，再写磁盘。

以 InnoDB 引擎为例，当有一条记录需要更新的时候，InnoDB 引擎就会先把记录写到 redo log（重做日志）里面，并更新内存，这个时候更新就算完成了，然后，InnoDB 引擎会在系统空闲的时候，将这个操作记录更新到磁盘里面。

之所以叫做重做日志，是因为如果 MySQL 数据库服务端发生异常崩溃，重启时可以根据这个日志记录的步骤完成事务已提交但未持久化到磁盘的数据更新操作，从而保证事务的持久性。

那为什么又有 binlog 呢？

实际上，redo log 是 InnoDB 引擎提供的日志系统，在 InnoDB 引擎出现之前，MySQL 默认的存储引擎是 MyISAM，那个时候为了实现数据备份和恢复，使用的是 binlog，不过 binlog 是一个归档日志，不具备数据库崩溃重启后的数据恢复功能，不过 MyISAM 也不支持事务，而 InnoDB 支持事务，因此，InnoDB 专门开发了一套 redo log 日志系统。

作为归档日志，binlog 是增量写（可以一直追加写入），负责数据库全量数据的备份和恢复，比如数据库集群中的主从同步就是基于 binlog 实现的。

作为重做日志，redo log 负责已提交事务未持久化到磁盘部分更新数据的备份和恢复（重新做一遍），是 InnoDB 引擎事务机制下数据恢复的重要补充，也是事务持久性的保障，如果 redo log 中的数据已经持久化到磁盘，这部分重做日志就可以被覆盖了，所以 redo log 是循环写（后面的记录会覆盖前面的）。

注：binlog 是属于 MySQL Server 层的日志系统，因此所有的存储引擎都可以共用它。

下面我们来看看在 InnoDB 引擎中，这两个日志是如何写入的：

1. 执行器通过 API 接口将更新数据传递给存储引擎执行更新操作；
2. 存储引擎在拿到更新数据后，先将其更新到内存，同时将这个更新操作记录到 redo log，此时 redo log 处于 prepare 状态，然后告知执行器执行完成了，随时可以提交事务；
3. 执行器生成这个操作的 binlog，并把 binlog 写入磁盘（写入时机可以配置，对于事务操作而言，都是在事务提交时才会持久化写入的，相关细节我们后面讲数据库数据一致性的时候会详细介绍）；
4. 执行器调用引擎的提交事务接口，引擎把刚刚写入的 redo log 改成 commit 状态，更新完成。

在上述步骤中，将 redo log 的写入拆成了两个步骤：prepare 和 commit，这就是「两阶段提交」。

如果不使用两阶段提交，会导致两份日志恢复的数据不一致：

比如先写 redo log，binlog 还没有写入，数据库崩溃重启；

或者先写 binlog，redo 还没有写入数据库崩溃重启，都将造成恢复数据的不一致。

而使用两阶段提交后，就可以保证两份日志恢复的数据一致：

只有 binlog 写入成功的情况下，才会提交 redo log，否则 redo log 处于 prepare 状态，事务会回滚，这样一来，就保证了数据的一致性。

补充：

学院君，你好， 请问一个问题，文中提到【不过 binlog 是一个归档日志，不具备数据库崩溃重启后的数据恢复功能】，binlog也能用于数据库崩溃后的数据恢复吧，应该是 【不具备事务处理时数据库崩溃重启后的数据恢复功能】吧？ 谢谢。

学院君回复：很严谨，不过换个角度说，对于目前主流的支持事务的 InnoDB 引擎而言，默认所有操作都是基于事务的，所以说 binlog 不能用于实现数据库崩溃后数据恢复也没毛病，而对于不支持事务的引擎，数据库崩溃就崩溃了吧，也没啥要恢复的，反正也不存在事务已提交尚未持久化的概念，就好比未提交事务崩溃重启后也不用管的是一样的道理。这里面其实是一个事务持久化机制的保证，已提交事务即便数据库崩溃，重启后也要恢复。

2. 问题：redo log仅仅是记录一个事务的操作过程吗？还是一个事务对应一个redo log？

TBD

认真阅读并思考之后，就知道： redo log记录很多事务的操作过程，单从一个事务修改的数据量大小有可能小于16KB就知道。

3. 关于“以 InnoDB 引擎为例，当有一条记录需要更新的时候，InnoDB 引擎就会先把记录写到 redo log（重做日志）里面，并更新内存，这个时候更新就算完成了，然后，InnoDB 引擎会在系统空闲的时候，将这个操作记录更新到磁盘里面。”的描述。

更新内存与更新redo log的顺序？

通过问题/补充中1的阅读以及其中重要片段，可以知道：

执行器通过 API 接口将更新数据传递给存储引擎执行更新操作；

存储引擎在拿到更新数据后，先将其更新到内存，同时将这个更新操作记录到 redo log

可以看出，应该是同时操作，当然内存操作应该更快完成，其实思考后，这个顺序是不重要的

补充

InnoDB 引擎就会先把记录先写 redo log（重做日志）还是先写内存，或者同时写入，如果写入redo log一半时候系统崩溃，事务的上下文就会丢失，这时候这部分提交的事务就会回滚吗？

学院君：

后面有详细介绍：https://xueyuanjun.com/post/22082 // 通过 redo 日志保证数据库事务的持久性

为什么还要更新在内存中？

TBD

判断可知，因为内存修改速度远快于磁盘操作，而且是频繁操作，自然放在内存中是最佳方案，也是DB设计者选择放在内存中操作的初衷，而且通常情况下，这里redo log是用不上的，只有系统崩溃的时候才会被用到。

更新时机为什么又说系统空闲的时候？此时“将这个操作记录更新到此盘中” 这个操作记录是指的是内存的操作记录还是redo log中的？

TBD

可以知道，这个时候的系统是空闲的，正常运行的，自然是将内存中的操作记录。

只有当系统崩溃时，才会是读取redo log文件中的操作记录，毕竟内存中的数据早已不复存在。

4. redolog 是 innodb 独有的，而 innodb SQL操作都是包含事务的，如果没有显式声明，则每条 SQL 语句会隐式使用事务。

这里的 innodb SQL操作也包括查询操作吗？

思考过后，就知道，这个问题有是不该问的，查询读取操作是无害的，不会对数据本身有任何影响，自然是没必要使用事务的。

5. redo log除了内存不够还有什么机制才会往磁盘同步呢，同步完成，会不会删除状态commit的日志

详细介绍：https://laravelacademy.org/post/22082#toc-4 // 通过 redo 日志保证数据库事务的持久性

6. 学院君，你好， 请问一个问题，文中提到【不过 binlog 是一个归档日志，不具备数据库崩溃重启后的数据恢复功能】，binlog也能用于数据库崩溃后的数据恢复吧，应该是 【不具备事务处理时数据库崩溃重启后的数据恢复功能】吧？ 谢谢。

学院君子：

很严谨，不过换个角度说，对于目前主流的支持事务的 InnoDB 引擎而言，默认所有操作都是基于事务的，所以说 binlog 不能用于实现数据库崩溃后数据恢复也没毛病，而对于不支持事务的引擎，数据库崩溃就崩溃了吧，也没啥要恢复的，反正也不存在事务已提交尚未持久化的概念，就好比未提交事务崩溃重启后也不用管的是一样的道理。这里面其实是一个事务持久化机制的保证，已提交事务即便数据库崩溃，重启后也要恢复。

突然才明白过来，对于不支持事务的存储引擎，也没啥恢复的，也就是说binlog其实通常情况下，并不会用到，比如服务宕机， 重启就行了。 除非是被恶意删除数据库数据，然后通过binlog恢复数据，当然还会基于binlog 进行主从同步操作～ // 有理解不对的地方，请指教！

7.  本人还是没搞明白，锁与事务之间的关系？而且平时在开发项目时，代码中基本没有使用锁相关操作，以及事务操作？

TBD

这里先简单说下：

事务中的隔离性特性，就是要靠数据库锁来实现 【当然只靠锁也不行，还要依靠MVCC】

锁是手段，实现方式。

平时在开发项目时，代码中基本没有使用锁相关操作，以及事务操作？

首先，通常我们数据表采用Innodb存储引擎，而且通常在，涉及到

支付等操作，为了达到事务的特性，会启用事务，成功提交，失败回滚。

开启事务就自动加锁。

一个事务执行的任何过程中都可以获得锁，但是只有事务提交或回滚的时候才释放这些锁。这些都是隐式锁定，也可以显式锁定，InnoDB支持显式锁定，例如：

SELECT .... LOCK IN SHARE MODE (加共享锁)

SELECT .....FOR UPDATE(加排他锁)

...

参见文档阅读列表