MySQL中的事务

本文主要摘抄自《MySQL技术内幕（InnoDB存储引擎）第二版》，少部分来自网络博客和自己补充。注意，该书内容是基于MySQL 5.6 及更早的版本，具体的实现方式有可能发生了改变，有些内容我做了注释和补充，但是不保证百分百的完整和正确。

事务（Transaction）是数据库区别于文件系统的重要特性之一。事务会把数据库从一种一致性状态转换为另一种一致状态。在数据库提交工作时，可以确保要么所有修改都已经保存了，要么所有修改都不保存。

InnoDB 存储引擎中的事务完全符合 ACID 的特性。ACID 是以下 4 个词的缩写：

• 原子性（atomicity）
• 一致性（consistency）
• 隔离性（isolation）
• 持久性（durability）

本文主要关注事务的原子性这一概念，并说明怎么正确使用事务及编写正确的事务应用程序，避免在事务方面养成一些不好的习惯。

认识事务

概述

事务可由一条非常简单的 SQL 语句组成，也可以由一组复杂的 SQL 语句组成。事务是访问并更新数据库中各种数据项的一个程序执行单元。在事务操作中，要么都做修改，要么都不做，这就是事务的目的，也是事务模型区别于文件系统的重要特征之一。

理论上说，事务有严格的定义，必须同时满足四个特性，即 ACID 特性。但是数据库厂商出于各种目的，并没有严格去满足事务的 ACID 标准。对于 InnoDB 存储引擎而言，其默认的事务隔离级别为READ REPEATABLE 完全遵循和满足事务的 ACID 特性。这里具体介绍事务的 AICD 特性，并给出相关概念。

A（Atomicity）原子性。在计算机系统中，每个人都将原子性视为理所当然。例如在 C 语言中调用SQRT函数，要么返回正确的平方根值，要么返回错误的代码，而不会在不可预知的情况下改变任何的数据结构和参数。如果SQRT函数被多个程序同时调用，一个程序的返回值也不会是其他程序需要计算的平方根。

然而在数据的事务中实现调用操作的原子性，就没那么简单了，例如一个 ATM 机取款的流程：

1. 登录 ATM 机平台，验证密码。
2. 从远程银行的数据库中，取得账户信息。
3. 用户在 ATM 机上输入欲提取的金额。
4. 从远程银行的数据库中，更新账户信息。
5. ATM 机出款。
6. 用户取钱。

整个取款流程应当视作原子操作，即要么都做，要么不做。不能用户钱未从 ATM 机上取得，但是银行卡上的钱已经被扣除了，相信这是任何人都不能接受的一种情况。而通过事务模型，可以保证操作的原子性。

原子性是指整个数据库事务是不可分割的工作单位。只有使事务中所有的数据库操作都执行成功，才算整个事务成功。任何一个 SQL 语句执行失败，已经执行成功的 SQL 语句也必须撤销，数据库状态应该退回到执行事务前的状态。

C（consistency），一致性。一致性是指事务将数据库从一种状态转变为下一种一致的状态。在事务开始之前和结束之后，数据库的完整性约束没有被破坏。事务是一致性的单位，如果事务中某个动作失败了，系统可以自动撤销事务，返回初始化的状态。

I（isolation），隔离性。隔离性还有其他称呼，如并发控制（consurrency control）、可串行化（serializability）、锁（locking）等。事务的隔离性要求每个读写事务的对象对其他事务的操作对象能相互分离，即该事务提交前对其他事务都不可见，通常这使用锁来实现。当前数据库系统中都提供了一种粒度锁（granular lock）的策略，允许事务仅锁住一个实体对象的子集，以此来提高事务之间的并发度。

D（durability），持久性。事务一旦提交，其结果就是永久性的。即使发生宕机等故障，数据库也能将数据恢复。需要注意的是，只能从事务本身的角度来保证结果的永久性。例如，在事务提交后，所有的变化都是永久的。即使当数据库发生崩溃而需要恢复数据时，也能保证恢复后提交的数据都不会丢失。但若不是数据库本身的问题，那么数据有可能丢失。因此保证事务系统的高可靠性（High Reliability），而不是高可用性（High Availability）。对于高可用性的实现，事务本身并不能保证，需要一些系统共同配合来完成。

分类

从事务理论的角度来说，可以把事务分为以下几种类型：

• 扁平事务（Flat Transactions）
• 带有保存点的扁平事务（Flat Transaction with Savepoints）
• 链事务（Chained Transactions）
• 嵌套事务（Nested Transaction）
• 分布式事务（Distributed Transactions）

扁平事务（Flat Transaction）是事务类型中最简单的一种，但在实际生产环境中，可能是最频繁的事务。在扁平事务中，所有操作都处于同一层次，其由BEGIN WORK开始，由COMMIT WORK或ROLLBACK WORK结束，其间的操作是原子的，要么都执行，要么都回滚。因此扁平事务是应用程序成为原子操作的基本组成模块。

下图显示了扁平事务的三种不同结果：

扁平事务的主要限制是不能提交或者回滚事务中的某一部分，或者分步骤提交。下面给出了一个扁平事务不足以支持的例子。例如用户在旅行网站上进行自己的旅行度假计划。用户摄像从杭州道意大利的佛罗伦萨，这两个城市之间没有直达的班级，需要用户预定并转乘航班，或者需要搭火车等待。用户旅行度假的事务为：

BEGIN WROK

但是当用户执行到 S3 的时候，发现由于飞机到达米兰的时间台湾，已经没有当天的火车。这时用户希望在米兰当地住一晚，第二天出发去佛罗伦萨。这时如果事务为扁平事务，则需要回滚之前 S1、S2、S3 的三个操作，这个代价就显得有点大。因为当再次进行该事务的时候，S1、S2 的执行计划是不变的。也就是说，如果有支持计划的回滚操作，那么就不需要终止整个事务。因此就出现了带有保存点的扁平事务。

带有保存点的扁平事务（Flat Transaction with Savepoint），除了支持扁平事务支持的操作外，允许在事务执行过程中回滚到同一事务中较早的一个状态。这是因为某霞事务可能在执行过程中出现的错误并不会导致所有的操作都无效，放弃整个事务不合乎要求，开销页太大。保存点（Savepoint）用来通知系统应该记住事务当前的状态，以便当以后发生错误时，事务能回到保存点当时的状态。对于扁平的事务来说，其隐式的设置了一个保存点。而在整个事务中，只有一个保存点，因此，回滚只能回滚到事务开始时的状态。保存点用SAVE WORK函数来建立，通知系统记录当前的处理状态。当出现问题的时候，保存点能用作内部的重启动点，根据应用逻辑，决定是回到最近一个保存点还是其他更早的保存点。下图显示了在事务中使用了保存点：

上图显示了如何在事务中使用保存点。灰色背景部分的操作表示由ROLLBACK WORK而导致的部分回滚，实际并没有执行的操作。当用 BEGIN WORK 开启一个事务时，隐式地包含一个保存点，当事务通过ROLLBACK WORK：2 发出部分回滚命令的时候，事务回滚到保存点 2，接着一次执行，并再次执行到ROLLBACK WORK：7，知道最后的COMMIT WORK操作，这时表示事务结束，除灰色阴影部分的操作外，其他的操作都已经执行，并且提交。

另一点需要注意的是，保存点在事务内部是递增的，这从上图中也可以看出。有人可能会想，返回保存点 2 之后，下一个保存点可以是 3，因为之前的动作都终止了。然而新的保存点编号为 5，这意味着ROLLBACK不影响保存点的计数，并且单调递增的编号能保持事务执行的整个历史过程，包括在执行过程中想法的改变。

此外，当事务通过ROLLBACK WORK：2 命令部分回滚命令时，要记住事务并没有完全被回滚，只是回滚到了保存点 2 而已。这代表当前事务还是活跃的，如果想要完全回滚事务，还需要再执行命令ROLLBACK WORK。

链事务（Chained Transaction）可以视为是保存点模式的一种变种。带有保存点的扁平事务，当事务发生崩溃的时候，所有保存点都将消失，因为其保存点是易失的（volatile），而非持久的（persistent）。这意味着当进行恢复时，事务需要从开始处重新执行，而不能从最近的一个保存点继续执行。

链事务的思想是：在提交一个事务时，释放不需要的数据对象，将必要的数据处理上下文隐式地传给下一个要开始的事务。注意，提交事务操作和开始下一个事务操作将合并为一个原子操作。这意味着下一个事务将看到上一个事务的结果，就好像在一个事务中进行的一样。下图显示了链事务的工作方式：

链事务与带有保存点的扁平事务不同的是，带有保存点的扁平事务能回滚到任意正确的保存点。而链事务中的回滚仅限于当前事务，即只能恢复到最近的一个保存点。对于锁的处理，两者也不相同。链事务在执行COMMIT后即释放了当前事务所持有的锁，

而带有保存点的扁平事务不影响迄今为止所持有的锁。

嵌套事务（Nested Transaction）是一个层次结构框架。由一个顶层事务（top-level transaction）控制着各个层次的事务。顶层事务之下嵌套的事务被称为子事务（subtransaction），其控制着每一个局部的变量。嵌套事务的层次结构如图所示：

下面是 Moss 对嵌套事务的定义：

1. 嵌套事务是由若干事务组成的一颗树，子树既可以是嵌套事务，也可以是扁平事务。
2. 处在叶节点的事务是扁平事务。但是每个子事务从根到叶节点的距离可以是不同的。
3. 处于根节点的事务成为顶层事务，其他事物称为子事务。事务的前驱（predecessor）称为父事务（parent），事务的下一层成为儿子事务（child）。
4. 子事务既可以提交也可以回滚。但是它的提交操作并不会马上生效，除非其父事务已经提交。由此可以推论出，任何子事务都在顶层事务提交后才真正的提交。
5. 树中任何一个事务的回滚都会引起它所有的子事务一同回滚，故子事务仅保留 A、C、I 特性，不具有 D 的特性。

根据 Moss 的理论，实际的工作是交由叶子节点来完成的，即只有叶子节点的事务才能访问数据库、发送消息、获取其他类型的资源。而高层的事务仅负责逻辑控制，决定何时调用相关的子事务。即使一个系统不支持嵌套事务，用户也可以通过保存点技术来模拟嵌套事务，如图所示：

从图中也可以发现，在恢复时采用保存点技术比嵌套查询有更大的灵活性。例如在完成 Tk3 这个事务的时候，可以回滚到保存点 S2 的状态。而在嵌套查询的层次结构中，这是不被允许的。

但是用保存点技术来模拟嵌套事务在锁的持有方面还是与嵌套查询有些区别。当通过保存点技术来模拟嵌套事务时，用户无法选择哪些锁需要被子事务继承，哪些需要被父事务保留。就是说：无论有多少个保存点，所有被锁住的对象都可以被得到和访问。而在查询嵌套中，不同的子事务在数据库对象上持有的锁是不同的。 例如有一个父事务 P1，其持有对象 X 和 Y 的排它锁，现在要开始调用子事务 p11，那么父事务 P1 可以不传递锁，也可以传递所有的锁，也可以只传递一个排他锁。如果子事务 P11 中还有持有对象 X、Y、Z 的排他锁。如果这时又再次调用了一个子事务 P12，那么它可以传递那里已经持有的锁。

然而，如果系统支持在嵌套事务中并行地执行各个事务，在这种情况下，采用保存点的扁平事务来模拟嵌套事务就不切实际了。者从另一个方面反映出，想要实现事务键的并行性，需要真正支持的嵌套事务。

分布式事务（Distributed Transaction）通常是在一个分布式环境下运行的扁平事务，因此需要根据数据所在位置访问网络中的不同节点。

假设一个用户在 ATM 机上进行银行的转账操作，例如持卡人从招商银行的储蓄卡转账 10000 元到工商银行的储蓄卡。在这汇总情况下，可以将 ATM 机视为节点 A，招商银行的后台数据库视为节点 B，工商银行的后台数据库视为 C，这个转账操作可以分解为以下的步骤：

1. 节点 A 发出转账命令。
2. 节点 B 执行储蓄卡中的余额值减去 10000。
3. 节点 C 执行储蓄卡中的余额值加上 10000。
4. 节点 A 通知用户完成或者节点 A 通知用户操作失败。

这里需要使用分布式事务，因为节点 A 不能通过调用一台数据库就完成任务，其需要访问网络中两个节点的数据库，而在每个节点中的数据库执行的事务操作都又是扁平的。对于分布式事务，其同样需要满足 ACID 特性，要么都发生，要么都失效。对于上述例子，如果 2、3 步中任何一个操作失败，都会导致整个分布式事务回滚。

对于 InnoDB 存储引擎来说，支持扁平事务、带有保存点的事务、链事务、分布式事务。对于嵌套事务，其并不原生支持，因此，对于有并行事务需求的用户来说，MySQL 数据库或 InnoDB 存储引擎就显得无能为力了。然而用户仍可以通过带有保存点的事务来模拟串行的嵌套事务。

事务的实现

事务的隔离性由锁来实现。原子性、一致性、持久性通过数据库的 redo log 或 undo log 来完成。redo log 又称为重做日志，用来保证事务的原子性和持久性。undo log 用来保证事务的一致性。

有人可能会认为 undo 是 redo 的逆过程，其实不然。redo 和 undo 的作用都可以视为是一种恢复操作，redo 恢复提交事务修改的页操作，而 undo 回滚行记录到某个特定版本。因此两者记录的内容不同，redo 通常是物理日志，记录的是页的屋里操作改动，undo 是逻辑日志，根据每行记录进行记录。

redo

重做日志用来实现事务的持久性，即事务 ACID 中的 D。其由两部分组成：一是内存中的重做日志缓冲（redo log buffer），其是易失的；二是重做日志文件（redo log file），其是持久的。

InnoDB 是事务的存储引擎，通过Force Log at Commit机制实现事务的持久性，即当事务提交（COMMIT）的时候，必须先把事务的所有日志写入到重做日志文件进行持久化，等事务的提交操作完成之后才算完成。这里的日志是指重做日志，在 InnoDB 存储引擎中，由两部分组成，即 redo log 和 undo log。redo log 用来保证事务的持久性，undo log 用来帮助事务回滚以及 MVCC 的功能。redo log 基本上都是顺序写的，在数据库运行时不需要对 redo log 的文件进行读取操作。而 undo log 是需要进行随机读写的。

为了确保每次日志都写入重做日志文件，在每次日志缓冲写入重做日志文件之后，InnoDB 存储引擎都需要调用一次fsync操作。由于重做日志文件打开并没有使用O_DIRECT选项，因此重做日志缓冲先写入文件系统缓存。由于fsync的效率取决于磁盘性能，因此磁盘的性能决定了事务提交的性能，也就是数据库的性能。

InnoDB 存储引擎允许用户手动设置非持久性的情况发生，因此提高数据库的性能。当事务提交的时候，日志不写入重做日志文件，而是等待一个时间周期后再执行fsync操作。由于并非强制在事务提交的时候进行一次fync操作，显然可以提高数据库性能。但是在数据库发生宕机的时候，由于部分日志未发刷新到磁盘，因此会丢失最后一段时间的事务。

参数inndob_flush_log_at_trx_commit用来控制重做日志刷新到磁盘的策略，默认值是 1，表示每次都会调用fsync操作。这个参数还可以设置为 0 或者 2。0 表示事务提交时不进行写入重做日志操作，这个操作仅在 master thread 中完成，而在 master thread 中每一秒会进行一次fsync操作。2 表示事务提交时将重做日志写入重做日志文件，但仅写入文件系统的缓存中，不进行 fsync 操作。在这个设置下，当 MySQL 数据库发生宕机而操作系统不发生宕机时，并不会导致事务的丢失。但是当操作系统宕机时，重启数据库后会丢失未从文件系统缓存刷新到重做日志文件那部分事务。

下图是模拟插入 50 万行数据，参数为 1 是每插入一条就显式执行一次 COMMIT 操作，跟其他参数设置之间的数据库效率对比：

虽然用户可以通过设置参数innodb_flush_log_at_trx_commit为 0 或 2 来提高事务提交的性能，但是需要牢记的事，这种设置方法丧失了事务的 ACID 特性。而针对上述存储过程，为了提高事务的提交性能，应该在将 50 万行数据插入表后进行一次 COMMIT 操作，而不是在每插入一条记录之后进行一次 COMMIT 操作。

在 MySQL 数据库中还有一种二进制日志（binlog），其用来进行 POINT-TIME（PIT）的恢复及主从复制（Replication）环境的建立。从表面上看其和重做日志非常相似，都是记录了对于数据库操作的日志。然而，从本质上来看，二者有着非常大的不同。

首先，重做日志是在 InnoDB 存储引擎层产生，而二进制日志是在 MySQL 数据库的上层产生的，并且二进制日志不仅仅对于 InnoDB 引擎，MySQL 数据库中的任何存储引擎对于数据库的更改都会产生二进制日志。

其次，两种日志记录的内容形式不同。MySQL 数据库上层的二进制日志是一种逻辑日志，其记录的是对应的 SQL 语句（注，这里不严谨，新版本的 MySQL 不完全是对应的 SQL）。而 InnoDB 存储引擎的重做日志在事务进行中不断地被写入，这表现为日志并不是随事务提交的顺序进行写入的。

undo

重做日志记录的事务的行为，可以很好地通过其对页进行“重做”操作。但是事务有时候还会进行回滚操作，这时就需要 undo。因此在对数据库在进行修改的时候，InnoDB 存储引擎不但会产生 redo，还会产生一定量的 undo。这样如果用户执行的事务或语句由于某种原因失败了，又或者用户用一条ROLLBACK语句请求回滚，就可以利用这些 undo 信息将数据回滚到修改之前的样子。

redo 存放在重做日志文件中，与 redo 不同，undo 存放在数据库内部的一个特殊段（segment）中，这个段称为 undo 段（undo segment）。undo 段位于共享表空间内。可以通过py_innodb_page_info.py工具来查看当前共享表空间中 undo 的数量。

用户通常对 undo 有这样的误解：undo 用于将数据库物理的恢复到执行语句或事务之前的样子——但事实并非如此。undo 是逻辑日志，因此只将数据库逻辑的恢复到原来的样子。所有修改都被逻辑地取消掉了，但是数据结构和页本身在回滚之后可能大不相同。这是因为在多用户并发系统中，可能会有数十、数百甚至数千个并发事务。数据库的主要任务就是协调堆数据记录的并发访问。比如，一个事务在修改当前一个页中某几条记录，同时还有别的事务在对同一个页中另几条记录进行修改。因此，不能将一个页回滚到事务刚开始的样子，因为这样会影响其他事务正在进行的工作。

例如，用户执行了一个 INSERT 10 万条记录的事务，这个事务会导致分配一个新的段，即表空间会增大。在用户执行ROLLBACK时，会将插入的事务进行回滚，但是表空间的大小不会因此收缩。因此，当 InnoDB 存储引擎回滚时，它实际上做的是与先前相反的工作。对于每个 INSERT ，InnoDB 存储引擎会完成一个 DELETE；对于每个 DELETE，InnoDB 存储引擎会执行一个 INSERT；对于每个 UPDATE，InnoDB 引擎会执行一个相反的 UPDATE，将修改前的行放进去。

除了回滚操作，undo 的另一个作用是 MVCC，即在 InnoDB 存储引擎中 MVCC 的实现是通过 undo 来完成。当用户读取一行记录时，若该记录已经被其他事务占用，当前事务可以通过 undo 读取之前的行版本信息，以此实现非锁定读取。

最后也是最重要的一点是，undo log 会产生 redo log，也就是 undo log 的产生会伴随着 redo log 的产生，这是因为 undo log 也需要持久性的保护。

pruge

delete 和 update 操作可能不会直接删除原有的数据。假设有如下表 t：

CREATE TABLE t(

对其执行 如下 SQL 语句：

DELETE FROM t WHERE a=1;

表 t 上列 a 有聚集索引，列 b 上有辅助索引。对于上述 delete 操作，仅仅是将主键列等于 1 的记录delete flag设置为 1，记录并没有被删除，即记录还存在于 B+ 树中。其次，对辅助索引上 a 等于 1，b等于 1 的记录同样没有做任何处理，甚至没有产生 undo log。而真正删除这样记录的操作其实被“延时”了，最终在 purge 操作中完成。

purge 用于最终完成 delete 和 update 操作。这样设计是因为 InnoDB 存储引擎支持 MVCC，所以记录不能在事务提交时立即进行处理。这时其他事务可能正在引用这行，故 InnoDB 存储引擎需要保存记录之前的版本。而是否可以删除改条记录通过 purge 来进行判断。若改行记录已经不被其他任何事务引用，那么就可以进行真正的 delete 操作。可见，purge 操作是清理之前的 delete 和 update 操作，故上述操作“最终”完成。而实际执行的操作为 delete 操作，清理之前行记录的版本。

group commit

若事务为非只读事务，则每次事务提交时需要进行一次fsync操作，以保证重做日志都已经写入磁盘。当数据库发生宕机时，可以通过重做日志进行恢复。虽然固态硬盘的出现提高了磁盘的性能，然而磁盘的fsync性能是有限的。为了提高磁盘fsync的效率，当前数据库都提供了group commit的功能，即一次fsync可以刷新确保多个事务日志被写入文件。对于InnoDB 存储引擎来说，事务提交时会进行两个阶段的操作：

1. 修改内存中事务对应的信息，并且将日志写入重做日志缓冲。
2. 调用fsync将确保日志都从重做日志缓冲个写入磁盘。

步骤 2 相对于步骤 1 是一个较慢的过程，这是因为存储引擎需要与磁盘打交道。但当有事务进行这个过程时，其他事务可以进行步骤 1 的操作，正在提交的事务完成提交操作后，再次执行步骤 2 时，就可以将多个事务的重做日志通过一次fsync刷新到磁盘，这样就大大减少了磁盘的压力，从而提高了数据库的整体性能。对于写入或更新较为频繁的操作，group commit的效果尤为明显。

事务控制语句

在 MySQL 命令行的默认设置下，事务都是自动提交（auto commit）的，即执行 SQL 语句之后就会马上执行 COMMIT 操作。因此要显式的开启一个事务需要使用命令BEGIN、START TRANSACTION，或者执行命令SET AUTOCOMMIT=0，禁用当前会话的自动提交。每个数据库厂商自动提交的设置都不相同，每个 DBA 或开发人员需要非常明白这一点，这对之后的 SQL 编程会有非凡的意义，因此用户不能以之前的经验来判断 MySQL 数据库的运行方式。在具体介绍其含义之前，先看看有哪些事务控制语句：

• START TRANSACTION|BEGIN：显式地开启一个事务。
• COMMIT：提交事务，等价于COMMIT WORK。
• ROLLBACK：回滚会结束用户的事务，并撤销正在进行的所有未提交的修改。等价于ROLLBACK WORK
• SAVEPOINT identifier：SAVEPOINT允许在事务中创建一个保存点，一个事务中可以有多个保存点。
• ROLLBACK TO[SAVEPOINT] identifier：这个语句与SAVEPOINT命令一起使用。可以把事务回滚到标记点，而不回滚在此标记点之前的任何工作。例如可以发出两条UPDATE语句，后面跟一个SAVEPOINT，然后又是两条DELETE语句。如果执行DELETE语句期间出现了某种异常情况，并且捕获到了这个异常，同时发出了ROLLBACK TO SAVEPOINT命令，事务就会回滚到指定的SAVEPOINT，撤销DELETE完成的所有工作，而UPDATE语句完成的工作不受影响。
• SET TRANSACTION：这个语句用来设置事物的隔离级别。InnoDB 存储引擎提供的事务隔离级别有：READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ、SERIALISZABLE。

START TRANSACTION、BEGIN语句都可以在 MySQL 命令行下显式地开启一个事务。但是在存储过程中，MySQL数据库的分析器会自动将BEGIN识别为BEGIN END，因此在存储过程中只能使用START TRANSACTION语句来开启一个事务。

COMMIT和COMMIT WORK语句基本是一致的，都是用来提交事务。不同之处在于COMMIT WORK用来控制事务结束后的行为是CHAIN还是RELEASE的。如果是CHAIN方式，那么事务就变成了链事务。

InnoDB 存储引擎中的事务都是原子的，这说明下述两种情况：构成事务的每条语句都会提交（成为永久），或者所有语句都回滚。这种保护还延伸到单个的语句。一套语句要么完全成功，要么完全回滚（注意，这里说的是语句回滚）。因此一条语句失败并抛出异常时，并不会导致先前已经执行的语句自动回滚。所有的执行都会得到保留，必须由用户自己来决定是否对其进行提交或回滚的操作。

另一个容易犯的错误是ROLLBACK TO SAVEPOINT，虽然有ROLLBACK，但其实并不真正的结束一个事务，因此即使执行了ROLLBACK TO SAVEPOINT，之后也需要显式地运行COMMIT或ROLLBACK命令。

隐式提交的 SQL 语句

以下 SQL 语句会产生一个隐式的提交操作，即执行完这些语句后，会有一个隐式的COMMIT操作。

• DDL 语句：ALTER DATABASE...UPGRADE DATA DIRECTORY NAME，ALTER EVENT，ALTER PROCEDURE，ALTER TABLE，ALTER VIEW，CREATE DATABASE，CREATE ENENT，CREATE INDEX，CREATE PROCEDURE，CREATE TABLE，CREATE TRIGGER，CREATE VIEW，DROP DATABASE，DROP EVENT，DROP INDEX，DROP PROCEDURE，DROP TABLE，DROP TRIGGER，DROP VIEW，RENAME TABLE，TRAUNCATE TABLE
• 用来隐式地修改 MySQL 架构的操作：CREATE USER、DROP USER、GRANT、RENAME USER、REVOKE、SET PASSWORD
• 管理语句：ANALYZE TABLE、CACHE INDEX、CHECK TABLE、LOAD INDEX INTO CACHE、OPTIMEIZE TABLE、REPAIR TABLE、

另外需要注意的是，TRUNCATE TABLE语句是 DDL，因此虽然和对整张表执行DELETE的结果是一样的，但它不能回滚的。

对于事务操作的统计

对于 InnoDB 存储引擎是支持事务的，因此 InnoDB 存储引擎的应用需要在考虑每秒请求数（Question Per Second，QPS）的同时，应该关注每秒事务处理的能力（Transaction Per Second，TPS）。

计算 TPS 的方法是（com_commit+com_rollback）/time。但是利用这种方法进行计算的前提是：所有的事务必须都是显式的提交，如果存在隐式地提交和回滚（默认 autocommit=1），不会计算到com_commit和com_rollback中。

事务的隔离级别

SQL 标准定义的四个隔离级别：

• READ UNCOMMITTED
• READ COMMITTED
• REPEATABLE READ
• SERIALIZABLE

InnoDB 存储引擎默认支持的隔离级别是 REPEATABLE READ，但与标准 SQL 不同的是，InnoDB 存储引擎在 REPEATABLE READ 事务隔离级别下，使用 Next-Key Lock 锁的算法，因此避免幻读的产生。所以说，InnoDB 存储引擎在默认的 REPEATABLE READ的事务隔离级别下，已经能完全保证事务的隔离性要求，即达到 SQL 标准的 SERIALIZABLE 隔离级别。

隔离级别越低，事务请求的锁越少或保持锁的时间就越短，这也是为什么大多数数据库的默认隔离级别是 READ COMMITTED。

大部分用户质疑 SERIALIZABLE 隔离级别带来的性能问题，但是两者的开销几乎是一样的，甚至 SERIALIZABLE 可能更优！因此在 InnoDB 存储引擎中选择 REPEATABLE READ 的事务隔离级别并不会有任何性能的损失。同样的，即使使用 READ COMMITTED 的隔离级别，用户也不会得到性能的大幅提升。

在 InnoDB 存储引擎中，可以使用以下命令来设置当前会话或全局的事务隔离级别：

SET [GLOBAL|SESSION] TRANSACTION ISOLATION LEVEL{

如果想在 MySQL 数据库启动时就设置事务的默认隔离级别，那就需要修改 MySQL 的配置文件，在[mysqld]中添加如下行：

[mysqld]

查看当前会话的事务隔离级别，可以使用：

SELECT @@transaction_isolation\G;

查看全局的事务隔离级别，可以使用：

SELECT @@global.transaction_isolation\G;

注意，MySQL 8.0 之前查询当前事务隔离级别的语句是select @@tx_isolation，之后是上面用的select @@transaction_isolation

在 SERIALIABLE 的隔离级别下，InnoDB 引擎会对每个 SELECT 语句后自动加上一个LOCK IN SHARE MODE，即为每个读取操作加一个共享锁。因此在这个事务隔离级别下，读占用了锁，对一致性的非锁定读不再予以支持。这时，事务隔离级别 SERIALIZABLE 符合数据库理论上的要求，即事务是 well-formed 的，并且是 two-phrased 的。

分布式事务

MySQL 数据库分布式事务

InnoDB 存储引擎提供了对 XA 事务的支持，并通过 XA 事务来支持分布式事务的实现。分布式事务指的是允许多个独立的事务资源（transaction resources）参与到一个全局事务中。事务资源通常是关系型数据库系统，但也可以是其他类型的资源。全局事务要求在其中的所有参与的事务要么都提交，要么都会滚，这对事务原有的 ACID 要求又有了提高。另外，在使用分布式事务时，InnoDB 存储引擎的事务隔离级别必须设置为 SERIALIZABLE。

XA 事务允许你不同数据库之间的分布式事务，如一台数据库是 MySQL 数据库的，另一台是 Oracle 的，有可能还有一台是 SQL SERVER 数据库的，只要参与到全局事务中的每个节点都支持 XA 事务。

XA 事务由一个或多个资源管理器（Resource MAanagers）、一个事务管理器（Transaction Manager）以及一个应用程序（Application Program）组成。

• 资源管理器：提供访问事务资源的方法。通常一个数据库就是一个资源管理器。
• 事务管理器：协调参与全局事务中的各个事务。需要和参与全局事务的所有资源管理器进行通信。
• 应用程序：定义事务的边界，指定全局事务中的操作。

在 MySQL 数据库的分布式事务中，资源管理器就是 MySQL 数据库，事务管理器为连接 MySQL 服务器的客户端。下图显示了一个分布式事务的模型：

分布式事务使用两段式提交（two-phase commit）的方式。在第一阶段，所有参与全局事务的节点都开始准备（PREPARE），告诉事务管理器他们准备号提交了。在第二阶段，事务管理器告诉资源管理器执行ROLLBACK还是COMMIT。如果任何一个节点显示不能提交，则所有的节点都被告知需要回滚。可见与本地事务不同的是，分布式事务需要多一次 PREPARE 的操作，待收到所有节点的同意信息后，再进行COMMIT或是ROLLBACK操作。

内部 XA 事务

之前讨论的分布式事务是外部事务，即资源管理器是 MySQL 本身。在 MySQL 数据库中还存在另一种分布式事务，其在存储引擎与插件之间，又或者在存储引擎与存储引擎之间，称之为内部 XA 事务。】

最常见的内部 XA 事务存在于 binlog 与 InnoDB 存储引擎之间。由于复制的需要，因此目前绝大多数的数据库都开启了 binlog 功能。在事务提交时，先写二进制日志，再写 InnoDB 存储引擎的重做日志。对上述两个操作的要求也是原子的。即二进制日志和重做日志必须同时写入。若 二进制日志先写了，而在写入 InnoDB 存储引擎时发生了宕机，那么 slave 可能会接收到 master 传过去的二进制日志并执行，最终导致了主从不一致的情况。

为了解决这个问题，MySQL 数据库在 binlog 与 InnoDB 存储引擎之间采用 XA 事务。当事务提交的时候，InnoDB 存储引擎回西安做一个 PREPARE 操作，将事务的 xid 写入，接着二进制日志写入，入股偶在 InnoDB 存储引擎提交前，MySQL 数据库宕机了，那么 MySQL 数据库在重启后会先检查准备的 UXID 事务是否已经提交，若没有，则在存储引擎层再进行一次提交操作。如下图所示：

不好的事务习惯

在循环中提交

有的开发人员习惯再循环中进行事务的提交，比如可能常写的一个存储过程：

这个里面的COMMIT命令其实并不关键。因为 InnoDB 存储引擎默认自动提交，这也是经常被开发人员忽视的问题：

其实无论上面哪个存储过程都存在一个问题，当发生错误的时候，数据库会停留在一个位置的位置。例如，用户需要插入 10000 条记录，但是当插入 5000 条时，发生了错误，这时前 5000 条记录都已经存放在数据库中，那应该怎么处理呐？另外一个问题性能问题，上面两个存储过程都不会比下面的存储过程更快，因为下面的存储过程将所有的INSERT都放在了一个事务中：

第三种方法要快的多！这是因为每次提交都要写一次重做日志，存储过程 load1 和 load2 实际上写了 10000 次重做日志文件，而对存储过程 load3 来说，实际上只写了 1 次。

所以无论从何种角度出发，都不应该在一个循环中反复进行提交操作，无论是显式还是隐式的提交。

使用自动提交

使用自动提交不是一个好习惯，因为这会使初级 DBA 容易犯错，另外还可能是一些开发人员产生错误的理解。在编写程序开发时，最好把事务的控制权限交给开发人员，即在程序端进行事务的开始和结束。同时，开发人员必须了解是自动提交可能带来的问题。

使用自动回滚

InnoDB 存储引擎支持通过定义一个 HANDLER 来进行自动事务的回滚操作，如在一个存储过程中发生了错误会自动对其进行回滚操作。因此很多开发人员喜欢在应用程序的存储过程中使用自动回滚操作。

在存储过程中使用自动回滚很容易丢失错误信息，所以应该在程序中控制而不是数据库中。在程序中控制事务的好处是用户可以得知发生错误的原因，然后根据发生的原因进一步调试程序。

长事务

长事务（Loing-Lived Transaction），顾名思义，就是执行时间较长的事务，比如，对于银行系统中的数据库，每过一个阶段可能需要更新对应账户的利息。如果对应账户的数量非常大，例如对有 1 亿用户的表进行更新操作，可能需要非常长的时间来完成，可能需要一个小时，也可能需要 4、5 个小时，这取决于数据库的硬件配置。然而，由于事务的 ACID 特性，这个操作被封装在一个事务中完成。这就产生了一个问题，在执行过程中，当数据库、操作系统或者硬件发生问题的时候，重新开始事务的代价变得不可接受。数据库需要回滚所有已经发生的变化，而这个过程可能比产生这些变化的时间还要长。因此，对于长事务的问题，有时候可以通过转化为小批量（mini batch）的事务来进行处理。当事务发生错误时，只需要回滚一部分数据，然后接着上次已完成的事务继续进行。

由一个小地方还需要注意，要保证事务在处理工程中，没有其他的事务来更新表中的数据，需要人工加一个共享锁。

MVCC

关于 MVCC，感觉博客中好多解释是冲突的，干脆去看官网，以下内容直接粘贴的官网文档：

InnoDB多版本

InnoDB是一个 多版本的存储引擎：它保留有关已更改行的旧版本的信息，以支持诸如并发和回滚之类的事务功能 。此信息存储在表空间中的数据结构中，该数据结构称为 回滚段（出现在 Oracle 中类似的数据结构之后）。InnoDB 使用回滚段中的信息来执行事务回滚中所需的撤消操作。它还使用该信息来构建行的早期版本，以实现一致的读取。

在内部的实现中，InnoDB向数据库中存储的每一行添加了三个字段。包括：

• 一个 6 个字节的DB_TRX_ID字段，表示插入或更新该行的最后一个事务的事务标识符。同样，在内部用更新来实现删除，在更新操作的时候，行中的软删除标记位被标记为删除。
• 还包含一个 7 字节的 DB_ROLL_PTR字段，称为滚动指针。回滚指针指向写入回滚段的撤消日志（undo log）记录。如果行已更新，则撤消日志记录将保存了更新之前的全部信息，方便进行重建。
• 一个 6 字节的DB_ROW_ID字段包含一个行 ID，该行 ID 随着插入新行而单调增加。如果 InnoDB自动生成聚集索引，该索引包含行 ID 值。否则，该 DB_ROW_ID列不会出现在任何索引中。

回滚段中的撤消日志分为插入撤销和更新撤消日志。插入撤消日志仅在事务回滚时才需要，并且在事务提交后可以立即将其丢弃。更新撤消日志也用于一致的读取中，但是只有在其没有了分配了快照的事务后，才可以被删除。（因为一致性读取可能需要使用更新撤消日志中的信息来构建数据库行的早期版本。）

定期提交您的事务，包括仅用于一致读取的事务。否则， InnoDB将无法丢弃更新撤消日志中的数据，并且回滚段可能会变得太大而填满表空间。

回滚段中的撤消日志记录的物理大小通常小于相应的插入或更新的行。您可以使用此信息来计算回滚段所需的空间。

在InnoDB多版本方案中，当您使用 SQL 语句删除行时，并不会立即将其从数据库中物理删除。InnoDB仅在丢弃那些删除操作的撤销记录时，才物理删除相应的行及其索引记录。此删除操作称为purge，它非常快，通常花费与执行删除操作的SQL语句相同的时间顺序。

如果您以大致相同的速率在表中以较小的批次插入和删除行，则由于所有“ 死 ”行，清除线程可能开始滞后并且表可能会变得越来越大 ，从而使所有内容都受磁盘约束而且非常慢。在这种情况下，请限制新行的操作，并通过调整innodb_max_purge_lag系统变量来向清除线程分配更多资源 。有关更多信息，请参见第15.14节“ InnoDB启动选项和系统变量”。

多版本索引和二级索引

InnoDB多版本并发控制（MVCC）对二级索引的处理方式不同于聚簇索引。聚簇索引中的记录将就地更新，其隐藏的系统列指向撤消日志条目，可以从中重建记录的早期版本。与聚簇索引记录不同，辅助索引记录不包含隐藏的系统列，也不会就地更新。

更新二级索引列时，将对旧的二级索引记录进行删除标记，将新记录插入，并最终清除带有删除标记的记录。当二级索引记录被删除标记或二级索引页被较新的事务更新时，InnoDB在聚集索引中查找数据库记录。在聚集索引中，DB_TRX_ID检查记录的记录，如果在启动读取事务后修改了记录，则从撤消日志中检索记录的正确版本。

如果二级索引记录被标记为删除，或二级索引页被更新的事务更新， 则不使用覆盖索引技术。InnoDB在聚集索引中查找记录，而不是从索引结构中返回值。

但是，如果启用了 索引条件下推（ICP）优化，并且WHERE只能使用索引中的字段来评估部分条件，则 MySQL 服务器仍会将WHERE条件的这一部分下推到存储引擎，并使用索引。如果找不到匹配的记录，则避免聚集索引查找。如果找到了匹配的记录，即使在删除标记的记录中，也要在 InnoDB聚簇索引中查找记录。

小结

我们了解了 InnoDB 存储引擎管理事务的许多方面。了解了事务如何工作以及如何使用。

事务必须遵循 ACID 特性，即 Atomicity（原子性）、Consistency（一致性）、Isolation（隔离性）和 Durability（持久性）。隔离性通过锁来完成；原子性、一致性、隔离性通过 redo 和 undo 来完成。