MySQL 随笔

数据库设计步骤

一个数据库系统的设计步骤主要可以分为六个步骤，并且是可以据此不断循环往复以满足新需求的：

1. 需求分析

需求分析是数据库系统设计的第一步，也是最基础的、耗时最长的一个阶段。在这个阶段，需要对现实世界中的相关对象（客户）进行详细调查，然后逐步分析，确定客户对系统的数据需求和业务处理需求，进而形成需求分析文档。

2. 概要设计

依赖于需求分析文档，通过对需求分析中涉及到的实体进行综合、归纳和抽象，确定各个实体所必须的属性，以及各个实体之间的关联，最终形成 DBMS 概念模型，也就是 E-R 图。

3. 逻辑结构设计

根据概要设计生成的概念模型 E-R 图，需要将其设计为一个一个的表，确定各个表中每个字段（属性）的简单约束描述，确定各个表的主外键，并应用数据库设计的三大范式进行审核，对其优化。

何为范式呢？可以看一下这篇文章：《[数据库] 理解数据库范式-通俗易懂》，为免文章失效，我简单通俗地总结摘录一下，括号里的内容是理解不到位的地方：

元组：表中的一行记录。
码：可以唯一确认一个元组的属性集合，如果这样的属性集合（集合之间相交不为空集可视为不同属性集合）不只一个，则每一个属性集合都是候选码，从诸多候选码中选出一个，便为主码，若主码的属性集合包括实体的所有属性，那么称其为全码。若一个属性集合它不是该实体的主码，但却是另一个实体的(主)码，那么，这个属性集合被称为此实体的外码。
主属性：所有候选码的并集中的所有元素都是主属性，不属于主属性的属性则为非主属性。

第一范式(1NF)：属性不可分，举个例子，电话可以分为座机和手机，那此时电话就是可分的属性。
第二范式(2NF)：符合 1NF，并且，每个非主属性都依赖于(主)码。通俗点说就是只要说出一个具体的主码值，那么其余所有非主属性的值便都已明了，表示为 码->非主属性。
第三范式(3NF)：符合 2NF，并且，没有传递依赖，亦即主属性不依赖于非主属性。（简单来说，如果码 A 能确定某个属性集合 B，而 B 又能确定另一个属性集合 C，因此 A 可以确定 C，那么就说发生了传递依赖。）
BC范式(BCNF)：符合 3NF，并且，主属性不依赖于主属性。（即不存在任何主码外的主属性依赖于主码的任意真子集，也就是主属性之间也没有传递依赖。）

4. 物理模型设计

根据逻辑结构设计中所得到的各个逻辑表，设计与具体数据库相关联的物理表，明确各个表的表名、每个表的各个字段的字段名、数据类型和其余约束条件，各个表的主外键约束等与具体数据库相关的物理实现。

5. 数据库实施

根据物理模型设计的结果编写相应的 SQL 语句建立数据库、建立各个表，在应用程序中或者直接执行 SQL 语句进行表的各种读写操作。

6. 数据库运行与维护

数据库成功实施后，便可以在不断的运行维护中对原有的表结构进行评价、修改和优化。

MySQL 索引

索引是一个单独的，存储在磁盘上的一个数据库结构，包含着对数据表里所有记录的引用指针。使用索引可以快速找出在某个或多个列中有一特定值的行，MySQL 的所有列类型都可以被索引，对相关列使用索引是增加查询效率的最佳途径。

索引实现原理

对于不同的存储引擎，索引实现方式会有所不同，但使用 B+ 树存储结构实现的索引却是最普遍的，下面是 MyISAM 存储引擎的原理图：

如果熟悉 B+ 树的同学相信一眼就看明白了，叶子节点保存着对数据表所有记录的引用地址，同时每个叶子节点都可以包含多个表记录（一般是一页数据 16 KB），每个叶子节点之间又通过链表的形式相互连接，这个链表总体上是有顺序的。

与 MyISAM 存储引擎不同，InnoDB 本身就是 B+ 树结构的索引文件，为什么这么说呢？因为 InnoDB 要求每个表都必须有主键（如果没有设置主键，就会自动选择一个能够唯一标识一个记录的列作为主键，如果还是找不到，就会创建一个长度为6个字节的长整型的隐藏字段作为主键），而 InnoDB 正是基于这个指定的主键来建立它的数据表，如下图所示：

与 MyISAM 存储引擎相比，叶子节点不再是数据表记录的引用地址，而是直接包含了完整的数据表记录，这个也被称为聚集索引。对于辅助索引来说，MyISAM 的基本存储结构并没有什么太大变化，其叶子节点的值仍然是数据表记录的引用地址，而 InnoDB 的值则不再直接包含数据表记录，而是数据表记录的主键，这意味着如果使用辅助索引，对于 InnoDB 存储引擎的辅助索引查询，它会走两次索引，第一次是走辅助索引获取主键值，第二次是走主键索引，根据第一次获取的主键来找到具体的表记录。

索引类型

1. 普通索引和唯一索引

普通索引是 MySQL索引中的基本索引类型，允许在定义索引的列中插入重复值和空值；唯一索引则要求定义索引的列中无重复值，但也允许空值；而主键索引是一种特殊的唯一索引，它除了不允许重复值，还不允许空值(NULL)。

2. 单列索引和组合索引

单列索引是指索引只包含一个列，一个表可以有多个单列索引；组合索引是指在表的多个字段组合上创建的索引，使用组合索引时遵从最左前缀原则。例如

# 创建了一个组合 book_name 和 age 的组合索引
mysql> CREATE INDEX book_name_age_index ON book(book_name, age);
Query OK, 0 rows affected (0.43 sec)
Records: 0  Duplicates: 0  Warnings: 0

上面新建的索引在索引中就会以 （book_name， age）的形式保存，根据最左前缀原则，索引可以匹配 book_name，（book_name， age）形式，但是不能匹配 age，（age， book_name）。

3. 全文索引

全文索引(FULLTEXT)，是指定义在索引列上的支持值的全文查找，允许重复值和空值的一种类型，全文索引只能用于 InnoDB 或 MyISAM 表，只能为 CHAR、VARCHAR、TEXT 列创建。

4. 空间索引

空间索引是对空间数据类型的列建立索引，在 MySQL 中空间类型有四种，分别是

• Geometry是所有空间集合类型的基类，其他类型如POINT、LINESTRING、POLYGON都是Geometry的子类。
• Point，顾名思义就是点，有一个坐标值。
• LineString，线，由一系列点连接而成。如果线从头至尾没有交叉，那就是简单的（simple）；如果起点和终点重叠，那就是封闭的（closed）。
• Polygon，多边形。可以是一个实心平面形，即没有内部边界，也可以有空洞，类似纽扣。最简单的就是只有一个外边界的情况，例如POLYGON((0 0,10 0,10 10, 0 10))。

空间索引只能在存储引擎为 MyISAM 的表上创建，MySQL 使用 SPATITAL 关键字进行扩展，使得其能够与其余索引一样以同样语法创建。

索引创建

• 任何时候都可以以此种方式新建索引：

CREATE [UNIQUE | FULLTEXT | SPATITAL] [INDEX | KEY] index_name ON table_name(column_name[(length)] [, column_name[(length)] ] [...])：在表中的一列或多列上建立普通索引；如果是BLOB和TEXT类型，必须指定 length，其余情况可以省略。

# book 表结构
mysql> DESC book;
+-----------+-------------+------+-----+---------+----------------+
| Field     | Type        | Null | Key | Default | Extra          |
+-----------+-------------+------+-----+---------+----------------+
| id        | int(11)     | NO   | PRI | NULL    | auto_increment |
| book_name | varchar(30) | YES  |     | NULL    |                |
| age       | int(11)     | YES  | MUL | 1       |                |
+-----------+-------------+------+-----+---------+----------------+

# 下面创建了一个普通索引， 如果添加了 UNIQUE 字段则是创建一个唯一索引
mysql> CREATE INDEX test_index ON book(book_name, age);
Query OK, 0 rows affected (1.21 sec)
Records: 0  Duplicates: 0  Warnings: 0

在上述 book 表结构描述中， Key有 两个值，其中 PRI 表示主键索引 primary key；MUL 表示该列的值可以重复，并且该列是一个非唯一索引的前导列（外键也是一个非唯一索引）；此外， UNI 表示此列是唯一索引，值不可重复但允许空值，

• 以修改表结构方式新建索引:

ALTER TABLE table_name ADD [UNIQUE | FULLTEXT | SPATITAL] [INDEX | KEY] index_name column_name[(length)] [, column_name[(length)] ] [...]

# 在已有表 course 中的 i 列创建了一个名为 unique_index_i 的普通索引
mysql> ALTER TABLE course ADD INDEX unique_index_i(i);
Query OK, 0 rows affected (0.50 sec)
Records: 0  Duplicates: 0  Warnings: 0

• 创建表的时候创建索引：

CREATE TABLE table_name(
# 省略诸多属性字段
...,
[UNIQUE | FULLTEXT | SPATITAL] [INDEX | KEY] index_name(column_name[(length)] [, column_name[(length)] ] [...])
);

# 示例，创建一个临时表，在 MySQL 关闭之时会自动销毁
# testTable 表中在 name 属性列上建立了一个普通索引 name_index
mysql> CREATE TEMPORARY TABLE testTable(
    ->    id int auto_increment primary key,
    ->    name varchar(30),
    ->    INDEX name_index(name)
    -> );
Query OK, 0 rows affected (0.02 sec)

索引分析

使用索引最重要的就是加快查询速度，那么如何知道索引是否生效，以及哪些查询速度较慢，也就是慢查询有哪些呢？我们可以利用 MySQL 的慢查询日志得知有哪些慢查询。

慢查询优化

1. 开启慢查询日志
在 MySQL 8 中如何开启慢查询日志呢？

• 在配置文件 my.ini 或 my.cnf 中找到或增加下面的配置项

    # 慢查询日志开关，0 为关闭，1 为开启，MySQL 8 默认开启
    slow-query-log=1
  
    # 存放慢查询日志的文件
    slow_query_log_file="XTZJ-20220209AS-slow.log"
  
    # 如何定义慢查询，这里默认查询时间超过 10 秒的就是慢查询，单位秒
    long_query_time=10
  

  然后重新启动服务器即可。windows 配置文件一般在 datadir 里：

    # 数据文件所在目录
    mysql> SELECT @@datadir;
    +---------------------------------------------+
    | @@datadir                                   |
    +---------------------------------------------+
    | C:\ProgramData\MySQL\MySQL Server 8.0\Data\ |
    +---------------------------------------------+
    1 row in set (0.00 sec)
  
    # MySQL bin 所在目录
    mysql> SELECT @@basedir;
    +------------------------------------------+
    | @@basedir                                |
    +------------------------------------------+
    | C:\Program Files\MySQL\MySQL Server 8.0\ |
    +------------------------------------------+
    1 row in set (0.00 sec)
  

• 命令行启动 MySQL 时带上 --slow-queries-log 选项：

    C:\Program Files\MySQL\MySQL Server 8.0\bin>net start mysql80 --slow-queries-log
    MySQL80 服务正在启动 ...
    MySQL80 服务已经启动成功。
  

2. 分析慢查询日志

直接打开慢查询日志文件，找到对应的 SQL 查询语句，然后利用 EXPLAIN 或者 DESCRIBE(DESC) 关键字来模拟优化器执行 SQL 查询语句，进而可以得到为何这个查询会如此慢。下面简单介绍一下这两个关键字：

[DESC | EXPLAIN] SELECT 语句：使用 DESCRIBE (简写为 DESC) 或者 EXPLAIN 命令可以对查询语句进行分析

mysql> DESCRIBE SELECT * FROM book;
+----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key        | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | book  | NULL       | index | NULL          | test_index | 128     | NULL |    9 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+-------------+

可以看到这条命令执行完后返回了上面这张表，其中各个字段的简单含义如下所示：

• id: select 识别符，这是 select 语句的查询序列号，id 越大的越先执行，id 相等的从上到下执行
• select_type：表示 select 语句的查询类型
• table： 表示操作的是哪张表
• type：表示表的连接类型
• possible_keys：表示 MySQL 在搜索数据记录时可选用的各个索引
• key：表示 MySQL 真正选用的索引
• key_len：给出索引按字节记录的长度，数值越小，表示查询越快
• ref：给出了关联关系中另一个数据表的列名
• rows：表示 MySQL 在执行这个查询时预计会扫描的行数
• Extra：表示与本查询相关的信息

记录一下有疑问的地方及学习文章：
《什么是覆盖索引?》
《MySQL执行计划extra中的using index 和 using where using index 的区别》
《EXPLAIN 命令详解》
《explain详解》

SQL 优化

• 由于索引的存在，会使得当数据插入、删除和更新时都要顺手维护一下索引，所以，为了更快，我们需要在数据被修改之前关闭索引，修改完成后打开索引：

ALTER TABLE table_name DISABLE KEYS : 关闭表中的索引
ALTER TABLE table_name ENABLE KEYS：打开表中的索引
此方法适用于 MyISAM 和 InnoDB。

• 除了索引，表中有些列还要求一些诸如唯一性检查、外键检查等，所以在插入数据时需要禁用这些检查以提升速度：

SET UNIQUE_CHECKS = 0 :0 表示关闭唯一性检查，1 表示打开
SET FOREIGN_KEY_CHECKS = 0 :0 表示关闭外键检查，1 表示打开
对于唯一性检查，适用于 MyISAM 和 InnoDB，而 MyISAM 由于无需主键，故而不用外键，因此外键检查只适用于 InnoDB。

• 插入数据时，使用批量插入比单个插入更高效：

INSERT INTO table_name(column_1,column_2,...) VALUES(column_1,column_2,...), (column_1,column_2,...), ...
还可以使用 LOAD DATA INFILE 进行批量导入 当需要批量导入数据时，使用 LOAD DATA INFILE 语句导入数据的速度比INSERT语句快。
据说这主要是针对 MyISAM 常见的优化手段，而对于 InnoDB，则是说在插入数据之前，关闭事务的自动提交：
SET AUTOCOMMIT = 0 ：0 表示关闭自动提交，1 表示打开

事务

事务是指一系列指令的集合，可以是一条指令，也可以是多条指令，这些指令要么全部成功执行，要么全部失败，只要有一条指令执行失败，那么之前所执行的指令就会全部失效，也就是回滚到事务开始之前。在 MySQL 事务中，一条 SQL 语句就是一个事务，但它是隐式的，命令执行成功就会自动提交。如果我们想要显式开始一个事务，可以使用 BEGIN 和 COMMIT, ROLLBACK，执行成功则是 COMMIT（提交） ，失败则是 ROLLBACK（回滚）：

# 建一个表来测试
mysql> CREATE TABLE book(
    ->  id int AUTO_INCREMENT PRIMARY KEY,
    ->  book_name VARCHAR(30),
    ->  author VARCHAR(30),
    ->  count INT
    -> );
Query OK, 0 rows affected (1.12 sec)

# 开启一个事务
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

# 事务中的第一个操作
mysql> INSERT INTO book(book_name, author, count) value('高等数学', '同济大学数学系', 10);
Query OK, 1 row affected (0.13 sec)

# 事务中的第二个操作
mysql> SELECT * FROM book;
+----+-----------+----------------+-------+
| id | book_name | author         | count |
+----+-----------+----------------+-------+
|  1 | 高等数学   | 同济大学数学系  |    10 |
+----+-----------+----------------+-------+
1 row in set (0.05 sec)

# 提交一个事务
mysql> COMMIT;
Query OK, 0 rows affected (0.06 sec)

事务的四大特性 ACID

• 原子性（Atomicity）：事务中的操作是一个不可分割的整体，要么全部成功完成，要么全部失败，一个操作（指令）失败就算全部失败，应该立即回滚到事务执行之前。比如，当你上厕所的时候，正准备一泻千里，幸好发现自己没有带手纸，于是你赶紧整理好仪容，恢复到进厕所之前的样子，然后拿到纸后，再重新来一遍。
• 一致性（Consistency）：事务的成功执行使得数据的状态发生了变化，但是不管状态如何变化，都应该满足数据的完整性约束。比如，我原来是个帅哥，不会因为放了屁后就不是帅哥了。
• 隔离性（Isolation）：不同事务之间是相互隔离的，即如果对同一数据进行操作，不同事务对这个数据的修改是其余事务所不可见的。通俗点说就是在一个事务看来，在它对数据的操作中，是没有其余事务在修改这些数据的，就是你忙你的，我忙我的，我不想看到我的东西不是我原来看到的样子。
• 持久性（Durability）：当事务成功完成后，对数据的更改是永久性的。也就是说，该做的事情我都做完了，不管你发生什么意外，都必须要给我这个结果。

事务的类型

1. 扁平事务
扁平事务是事务类型中最简单的一种，也就是平时我们所用到的事务。在扁平事务中，所有的操作都处于同一层次（何谓同一层次呢？就是说大家的级别是一样的，你在第一层，我也在第一层），其由 BEGIN 开始，由 COMMIT 或者 ROLLBACK 结束，没错，我们上面演示的就是一个扁平事务，处于开始和结束之间的操作集合是满足原子性的。

2. 带有保存点的扁平事务
带有保存点的扁平事务支持事务回滚到保存点的位置，而不一定要是回滚整个事务，因为有时一个操作失败，并不一定会对全面所有的操作产生影响，比如在一个事务中，给两个不同的人转账，第一个人转账很顺利，没有出现任何问题，但是在给第二个人转账时，还没有完成机器就出故障了，那么此时事务要进行回滚，但是第一个人已经转账成功了，第二个人转账失败并不会对他造成影响，但是在没有保存点之前，他也一样会被回滚。这样既不符合逻辑，开销也大。

# 删除 id 不为 1 的所有数据
mysql> DELETE FROM book WHERE id != 1;
Query OK, 3 rows affected (0.19 sec)

# 更新表的自增从 2 开始
mysql> ALTER TABLE book AUTO_INCREMENT = 2;
Query OK, 0 rows affected (0.46 sec)
Records: 0  Duplicates: 0  Warnings: 0

# 获取表中上次自增的 id 是几，并不会因为我重置了 AUTO_INCREMENT 而变更，而是由上一次插入成功的 id 决定
mysql> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                2 |
+------------------+
1 row in set (0.00 sec)

现在开始演示一下带有保存点的扁平事务：

# 开始一个事务，也可以使用 SET AUTOCOMMIT = 0,在提交时则是  SET AUTOCOMMIT = 1
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO book(book_name, author, count) VALUE('Computer', 'Computer', 10);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM book;
+----+-----------+----------------+-------+
| id | book_name | author         | count |
+----+-----------+----------------+-------+
|  1 | 高等数学   | 同济大学数学系  |    10 |
|  2 | Computer  | Computer       |    10 |
+----+-----------+----------------+-------+
2 rows in set (0.00 sec)

# 设置一个保存点 one ，因为下面操作的成败对上面的操作不会造成影响
# 如果要释放这个保存点可以 RELEASE SAVEPOINT one;
mysql> SAVEPOINT one;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO book(book_name, author, count) VALUE('Computer', 'Computer', 10);
Query OK, 1 row affected (0.00 sec)

# 事务第二个操作插入后的结果
mysql> SELECT * FROM book;
+----+-----------+----------------+-------+
| id | book_name | author         | count |
+----+-----------+----------------+-------+
|  1 | 高等数学   | 同济大学数学系  |    10 |
|  2 | Computer  | Computer       |    10 |
|  3 | Computer  | Computer       |    10 |
+----+-----------+----------------+-------+
3 rows in set (0.00 sec)

# 此时第二个操作期间模拟出现错误回滚，回滚到保存点 one 的位置
mysql> ROLLBACK TO SAVEPOINT one;
Query OK, 0 rows affected (0.00 sec)

# 再次查看，发现第二个操作插入的数据已经回滚了，而第一个插入操作的数据还保存着
mysql> SELECT * FROM book;
+----+-----------+----------------+-------+
| id | book_name | author         | count |
+----+-----------+----------------+-------+
|  1 | 高等数学   | 同济大学数学系  |    10 |
|  2 | Computer  | Computer       |    10 |
+----+-----------+----------------+-------+
2 rows in set (0.00 sec)

# 再次尝试重复第二个操作
mysql> INSERT INTO book(book_name, author, count) VALUE('Computer', 'Computer', 10);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM book;
+----+-----------+----------------+-------+
| id | book_name | author         | count |
+----+-----------+----------------+-------+
|  1 | 高等数学   | 同济大学数学系  |    10 |
|  2 | Computer  | Computer       |    10 |
|  4 | Computer  | Computer       |    10 |
+----+-----------+----------------+-------+
3 rows in set (0.00 sec)

# 又发生意外要回滚了，这侧面也说明即使已经回滚过了，但是这个事务还并未结束
mysql> ROLLBACK TO SAVEPOINT one;
Query OK, 0 rows affected (0.00 sec)

# 回滚到第一个操作的结果了
mysql> SELECT * FROM book;
+----+-----------+----------------+-------+
| id | book_name | author         | count |
+----+-----------+----------------+-------+
|  1 | 高等数学   | 同济大学数学系  |    10 |
|  2 | Computer  | Computer       |    10 |
+----+-----------+----------------+-------+
2 rows in set (0.00 sec)

# 再次重复第二次操作
mysql> INSERT INTO book(book_name, author, count) VALUE('Computer', 'Computer', 10);
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM book;
+----+-----------+----------------+-------+
| id | book_name | author         | count |
+----+-----------+----------------+-------+
|  1 | 高等数学   | 同济大学数学系  |    10 |
|  2 | Computer  | Computer       |    10 |
|  5 | Computer  | Computer       |    10 |
+----+-----------+----------------+-------+
3 rows in set (0.00 sec)

# 只有 ROLLBACK 或者 COMMIT 之后才表示一个事务结束了
# 此时不再回滚到保存点，而是全部回滚，如果是 COMMIT 的话，那就是保存这些成功的修改
mysql> ROLLBACK;
Query OK, 0 rows affected (0.08 sec)

# 可以看到回到了事务执行前的状态
mysql> SELECT * FROM book;
+----+-----------+----------------+-------+
| id | book_name | author         | count |
+----+-----------+----------------+-------+
|  1 | 高等数学   | 同济大学数学系  |    10 |
+----+-----------+----------------+-------+
1 row in set (0.00 sec)

3. 链事务
链事务可以说是带有保存点事务的一个变种，但它又不需要显式指定 SAVEPOINT ,它是由许多个子事务链接而成的，一个子事务的结束就代表着另一个子事务的开始，也就是说上一个子事务的提交操作和下一个子事务的开始操作被合并为了一个原子操作，这个子事务与子事务之间的提价开始点就是链事务的一个保存点，每当子事务发生错误回滚时就回到这个保存点，而不会回滚前面的所有子事务。由于各个子事务之间依靠这些保存点链接在一起，但它们又属于一个事务，因此就称为链事务。它有以下一些特征：

a 链事务（Chained Transaction）是指一个事务由多个子事务链式组成
b 前一个子事务的提交操作和下一个子事务的开始操作合并成一个原子操作，这意味着下一个事务将看到上一个事务的结果，就好像在一个事务中进行的一样
c 在提交子事务时就可以释放不需要的数据对象，而不必等到整个事务完成后才释放

它与带有保存点的扁平事务之间存在以下一些区别：

a 链事务中的回滚仅限于当前事务，相当于只能恢复到最近的一个保存点
b 带保存节点的扁平事务能回滚到任意正确的保存点
c 带保存节点的扁平事务中的保存点事易失的，当发生系统崩溃时，所有的保存点都将消失，这意味着当进行恢复时，事务需要从开始处重新执行
d 链事务在执行 COMMIT 后释放了当前事务所持有的锁，而前者不影响迄今为止所持有的锁（未定之言）

4. 嵌套事务
嵌套事务通俗点来说就是在一个 BEGIN 与 ROLLBACK/COMMIT 之间，也就是一个事务之内，又有一个事务，事务之内又有事务，这样层层嵌套就使得最外层事务成为了一个嵌套事务，先简单地介绍一下嵌套事务的性质：

a 嵌套事务（Nested Transaction）是一个层次结构框架
b 有一个顶层事务（top-level transaction）控制各个层次的事务
c 顶层事务之下嵌套的事务成为子事务（subtransaction）其控制着每一个局部的操作，子事务本身也可以是前台事务
d 嵌套事务的层次结构可以看成是一棵树，其叶子节点是一个扁平事务
e 子事务（被嵌套在一个事务中的事务）既可以提交也可以回滚（但它的提交操作并不马上生效，除非其父事务已经提交）
f 树中的任意一个事务的回滚会引起它的所有子事务一同回滚（子事务仅保留 A、C、I 特性，不具备 D）

简单举个嵌套事务的例子：

# 开始一个事务，第一层，也叫顶层事务
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

# 嵌套一个事务，第二层，是第一层事务的子事务
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM book;
+----+-----------+----------------+-------+
| id | book_name | author         | count |
+----+-----------+----------------+-------+
|  1 | 高等数学   | 同济大学数学系  |    10 |
|  6 | Computer  | Computer       |    10 |
+----+-----------+----------------+-------+
2 rows in set (0.00 sec)

# 嵌套事务中再嵌套一个事务，第三层
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

# 第三层事务插入一条记录
mysql> INSERT INTO book(book_name, author, count) VALUE('Java', 'Java', 10);
Query OK, 1 row affected (0.08 sec)

mysql> SELECT * FROM book;
+----+-----------+----------------+-------+
| id | book_name | author         | count |
+----+-----------+----------------+-------+
|  1 | 高等数学   | 同济大学数学系  |    10 |
|  6 | Computer  | Computer       |    10 |
|  7 | Java      | Java           |    10 |
+----+-----------+----------------+-------+
3 rows in set (0.03 sec)

# 第三层事务进行回滚，不要插入这条记录，第三层事务结束
mysql> ROLLBACK;
Query OK, 0 rows affected (0.08 sec)

mysql> SELECT * FROM book;
+----+-----------+----------------+-------+
| id | book_name | author         | count |
+----+-----------+----------------+-------+
|  1 | 高等数学   | 同济大学数学系  |    10 |
|  6 | Computer  | Computer       |    10 |
+----+-----------+----------------+-------+
2 rows in set (0.00 sec)

# 第二层事务插入一条记录
mysql> INSERT INTO book(book_name, author, count) VALUE('Java', 'Java', 10);
Query OK, 1 row affected (0.11 sec)

mysql> SELECT * FROM book;
+----+-----------+----------------+-------+
| id | book_name | author         | count |
+----+-----------+----------------+-------+
|  1 | 高等数学   | 同济大学数学系  |    10 |
|  6 | Computer  | Computer       |    10 |
|  8 | Java      | Java           |    10 |
+----+-----------+----------------+-------+
3 rows in set (0.00 sec)

# 第二层事务提交，插入的记录保存了下来，第二层事务结束
mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM book;
+----+-----------+----------------+-------+
| id | book_name | author         | count |
+----+-----------+----------------+-------+
|  1 | 高等数学   | 同济大学数学系  |    10 |
|  6 | Computer  | Computer       |    10 |
|  8 | Java      | Java           |    10 |
+----+-----------+----------------+-------+
3 rows in set (0.00 sec)

# 第一层事务插入一条同样的记录
mysql> INSERT INTO book(book_name, author, count) VALUE('Java', 'Java', 10);
Query OK, 1 row affected (0.16 sec)

mysql> SELECT * FROM book;
+----+-----------+----------------+-------+
| id | book_name | author         | count |
+----+-----------+----------------+-------+
|  1 | 高等数学   | 同济大学数学系  |    10 |
|  6 | Computer  | Computer       |    10 |
|  8 | Java      | Java           |    10 |
|  9 | Java      | Java           |    10 |
+----+-----------+----------------+-------+
4 rows in set (0.00 sec)

# 第一层事务进行回滚，即所有操作都不要，第一层事务结束，整个事务结束
mysql> ROLLBACK;
Query OK, 0 rows affected (0.00 sec)

# 但是？不是回滚了吗，怎么这些插入的记录还在呢？那是因为 MySQL 并不支持嵌套事务
mysql> SELECT * FROM book;
+----+-----------+----------------+-------+
| id | book_name | author         | count |
+----+-----------+----------------+-------+
|  1 | 高等数学   | 同济大学数学系  |    10 |
|  6 | Computer  | Computer       |    10 |
|  8 | Java      | Java           |    10 |
|  9 | Java      | Java           |    10 |
+----+-----------+----------------+-------+

5. 分布式事务

通常是一个在分布式环境下运行的扁平事务，需要根据数据所在位置访问不同的网络节点，也就是说，在一个扁平事务中，所查询或者操作的数据并不在同一个服务器中，可能保存在另一个服务器中的数据库中。尽管如此，分布式事务也要保证事务的四个特性。

事务的隔离级别

在事务的并发执行下，由于事务并不会始终占据 CPU，因此每个事务都有可能是断断续续地执行完毕的，这和线程并发的情况是一致的，因为事务往往依赖于一个线程，在一个线程内执行。因此可能会存在多种问题，下面一一进行介绍：

1. 脏读

所谓脏读，就是指一个事务 A 读取到了另一个事务 B 已修改但尚未提交的数据，一旦另一个事务 B 发生错误进行了回滚，那么这个数据就将不复存在，但是这个数据已经被事务 A 所读取，此时称事务 A 所读取的数据为脏数据，而这种读取事务未提交的数据的方式称为脏读。我在这篇《快速理解脏读、不可重复读、幻读和MVCC》盗了张图，形象地演示了脏读发生的情形：

2. 不可重复读

不可重复读就是说事务 A 同样的指令两次读取到数据却不一样，为什么会这样呢？首先可能是发生了脏读，第一次读取的是另一个事务未提交的数据，而在第二次读取之前，另一个事务回滚了，所以第二次再读取时，就与第一次读取到数据不一致。但一定是脏读吗？假设事务 A 在事务 B 开始之前读取了一个记录，在要第二次读取这个记录之前，事务 B 抢先执行，修改了这个记录，并且成功提交这个修改，这样当事务 A 进行第二次读取时，所读取到的数据就是这个由事务 B 修改后的最新数据，也就意味着第一次和第二次所读取的数据不一样，因而发生了脏读。下面又从那里盗图让大家看一下非脏读引起的不可重复案例：

3. 幻读

幻读是指事务 A 两次以同样的命令查询记录总数时却发现记录总数不一致，少了或者多了，注意这是与不可重复读最大的区别，严格来说这种情况应该也属于不可重复读，但是它仍然被分为了两种类型，区别它们的关键是不可重复读数据记录总数前后没有变化，只是行记录发生了变化（UPDATE 操作），而幻读则是数据记录总数前后发生了变化（INSERT / DELETE 操作）。这种情况可能是由脏读导致的，比方说一个事务 B 新增或者删除了一条数据但它尚未提交，此时事务 A 如果第一次读取的是事务 B 未提交的数据记录总数，但后面事务 B 回滚了，那么此时事务 A 执行第二次读取，那么一定就会导致记录数少了或者多了一条。也有可能是与不可重复读的情形类似的情况下发生的，但此时事务 B 所执行的指令就不是修改数据，而是新增或者删除数据，再盗张图来：

4. 第一类丢失更新

第一类更新丢失，指的是事务 A 的回滚将另一个事务所作的修改覆盖了，看张来自数据库第一类第二类丢失更新的图来理解一下：

这是完全没有事务隔离级别造成的，所以在支持事务的 MySQL InnoDB 存储引擎中，不会发生第一类丢失更新，先简单知道是因为读写锁机制的存在。有了读写锁只能限制一个时刻只有一个事务在修改数据，但并不拒绝多个事务并发读取数据，因而仍然会造成脏读、不可重复读和幻读的情况。

5. 第二类更新丢失

第二类更新丢失，指的是事务 A 的提交将另一个事务所作的修改给覆盖了，导致另一个事务的操作丢失了，继续来看张图理解一下：

可以发现第二类丢失更新事实上是特殊的不可重复读，如果事务 A 在汇入 100 元之前，查询了一下账户余额就会发现账户余额不再是之前的所查询的 1000 元。

为了解决由事务并发带来的这些问题，SQL 标准定义了四种事务隔离级别：

• 读未提交（READ UNCOMMITTED）
• 读已提交 （READ COMMITTED）
• 可重复读 （REPEATABLE READ）
• 串行化 （SERIALIZABLE）

这四种隔离级别理论上对应可解决的问题如下图所示：

	第一类丢失更新	脏读	第二类丢失更新	不可重复读	幻读
读未提交	不可能	可能	可能	可能	可能
读已提交	不可能	不可能	可能	可能	可能
可重复读	不可能	不可能	不可能	不可能	可能
串行化	不可能	不可能	不可能	不可能	不可能

下面我们将以 MySQL 的 InnoDB 为例对这四个隔离级别进行简单的说明分析。在正式开始之前，我们要先了解《MySQL 中的WAL机制》，下面我将基于此文进行简单地总结分析。

WAL（Write-Ahead Logging， 预写日志系统）机制

WAL 机制，指的是 MySQL 的写操作并不马上写入磁盘，而是先记录在日志中，等到合适的时机再写入磁盘。

undo log

为什么要有 undo log ？是为了实现 MVCC （多版本并发控制）来实现读已提交和可重复读级别的事务隔离级别。下面先来了解 undo log 是如何形成和 undo log 的作用。

每当有数据记录被修改或者删除前，就将原始数据拷贝一份放到 undo log 中，然后再对数据记录进行删除或者删除。在数据表中每条记录后面都有两个隐式字段，分别是 trx_id 和 roll_pointer，分别表示上次修改这个记录的事务 ID 和在上次修改之前的这个数据记录的拷贝存放在 undo log 中的指针。

假设现在在数据表中的记录是这样的（后面两个字段是我加上去的，请不要误会）：

+----+-----------+----------------+-------+---------+--------------+
| id | book_name | author         | count | trx_id  | roll_pointer |
+----+-----------+----------------+-------+---------+--------------+
|  1 | 高等数学   | 同济大学数学系  |    10 |      1  | null         |
+----+-----------+----------------+-------+---------+--------------+

上文中 null 也是我编的，表示上次事务 1 修改了这条记录，但是在此之前并没有这条记录，可能是事务 1 新插入的数据。此时事务 2 要来修改这条数据了，它将 count 修改为 50，于是要先将旧的数据记录写入 undo log，再修改两个隐藏字段：

// 表示这是一个 undo log 的文件，保存拷贝记录，当然也可以是用以暂存的内存缓存区
// 1. 模拟拷贝数据的指针
undo log file:
1.    1   高等数学   同济大学数学系    10     1     null

于是，在修改前在 undo log 中记录下这个数据记录的拷贝后，就开始修改数据，并同时修改 trx_id 为当前作出修改的事务的 ID ，也就是事务 2 的 ID —— 2, roll_point 则为之前拷贝在 undo log 中的地址指针，也就是 1 ：

+----+-----------+----------------+-------+---------+--------------+
| id | book_name | author         | count | trx_id  | roll_pointer |
+----+-----------+----------------+-------+---------+--------------+
|  1 | 高等数学   | 同济大学数学系  |    50 |      2  |            1 |
+----+-----------+----------------+-------+---------+--------------+

每当有修改或者删除操作时，就进行这样的操作，这样，保存在数据表中的数据记录就是时刻都是最新的，而每条记录都可以依靠 roll_pointer 指针值往上追溯自己每个不同版本的数据记录状态，以上例为例，id = 1 的数据记录保存在磁盘（或缓冲区）中的数据总是最新的（事务 2 修改的 count = 50），而根据 roll_pointer = 1 可以在 undo log 中找到 count = 10 的那个版本的数据记录，也就是说数据表中的每条记录都有它自己的一个版本链，如果 undo log 始终存在的话，那么每个记录就有一个从创建到最新时刻的完整版本链，每个版本都包含这个版本是谁做的，上一个版本在哪的信息。

也就是说依靠 undo log 可以形成每条数据记录的版本链，而 MVCC 的核心正是依靠这个版本链的 Read View 机制。

redo log

InnoDB 存储引擎的数据是保存在磁盘中的，这意味着每次读写数据都要进行磁盘 IO ，效率会很低，于是就引入了缓存池(Buffer Pool)，缓存池包含了磁盘中部分数据页的映射，作为访问数据库的缓冲。对于缓存层有如下性质：

• 当从数据库中读取数据时，应该首先读取缓存池，如果缓存池没有需要的数据，再从磁盘中读取并放入缓存池，而后返回。
• 当数据写入数据库时，应该先写入缓存层，而后定期将缓存层中的数据刷新磁盘数据库中（这一过程称为刷脏，为什么呢？当缓存层数据页与磁盘数据页一致时，称为干净页，否则称为脏页，而刷脏就是将缓存层中新的数据页写入旧的磁盘数据页，从而使之称为干净页，因此叫刷脏）。

那为何需要 redo log 呢？那是为了防止缓存层有些数据页还未来得及刷脏，如果 MySQL 就宕机了，那么这些数据就丢失了。在了解 redo log 之前，找张图来看看一条 DML 语句是怎样执行的：

来自《一条 sql 的执行过程详解》

TA 以 update 语句为例演示了 DML (insert / update / delete) 语句的执行流程，从图中（7 和 8）可以看到所有的数据记录操作都是在内存（缓存池）中执行的，不管它之前是否在缓存池中，在缓存池中执行完 DML 命令后，这个数据页就变成了脏页，因此后续便是再对其进行刷脏。

刷脏小知识：MySQL 在刷脏时会采用“连坐”机制，即在刷脏时，如果发现旁边的数据页也是脏页就会一起刷，每个数据页刷脏的时候都会这样做，即使是因为连坐牵连的脏页也会如此。在 Innodb中可以通过 innodb_flush_neighbors 来控制刷脏时是否“连坐”。

    # 默认是 0 表示关闭（MySQL 8.0）
    mysql> show variables like 'innodb_flush_neighbors';
    +------------------------+-------+
    | Variable_name          | Value |
    +------------------------+-------+
    | innodb_flush_neighbors | 0     |
    +------------------------+-------+
    1 row in set, 1 warning (0.01 sec)

    # 设置为 1 表示开启，GLOBAL 表示全局变量，SESSION 表示会话级别，也就是一个数据库连接
    mysql> SET GLOBAL innodb_flush_neighbors = 1;
    Query OK, 0 rows affected (0.02 sec)

来自 MySQL的WAL（Write-Ahead Logging）机制

我们再看 DML 执行流程，会发现该操作在写入缓存池中的数据页后会写入 redo log 中，同时根据步骤 7 的引导，在数据并不在缓存池中时，如果不是唯一索引，那么这个操作就保存在一个叫 Change Buffer （写缓存）的缓存中，注意这个并不是缓存池(Buffer Pool)，它们是两个不同的缓存，缓存池保存的是磁盘中的数据页，而写缓存保存的是对在缓存层中未命中数据记录的修改，它的作用是可以延缓写操作写入磁盘的时机，减少 IO 操作，当需要读取这个数据记录时，就将其从磁盘读出（一般读取是以页为单位，即读取出的是包含此纪录的整个数据页），再将这页数据保存到缓存池，然后读取写缓存(Change Buffer)将对此数据记录的修改应用（Merge）到这页缓存中，那么此时这页就变成了脏页，后续在合适的时机便会被刷脏以更新数据到磁盘。简单来说就是如果更新的信息不包括唯一索引，而这条数据又不在缓存池中，那么就会将修改写入写缓存，当需要读取这条数据时再从磁盘读入缓存池，然后将写缓存中的修改应用到缓存池中，使这条数据保持最新状态。

redo log 无论修改是否写入写缓存，只要有数据修改，那么就会写入 redo log。什么是 redo log ？通俗点来说，它保存的信息是“在某个数据页上作了什么修改”，所以说它是物理日志，而且是 InnoDB 存储引擎独有的，它的信息也并不是直接写入磁盘，而是先写入 redo log buffer ，但最终是会写入磁盘的，它的结构就像这张图一样：

1、修改信息先写入 redo log buffer 中，这部分是 MySQL 中的内存，是全局公用的。
2、在事务编写完成后，就可以执行 write 操作，写到文件系统的 page cache 中，属于操作系统的内存，如果 MySQL 崩溃不会影响，但如果机器断电则会丢失数据。
3、执行 fsync（持久化）操作，将 page cache 中的数据正式写入磁盘上的 redo log 中，也就是图中的 hard disk（这个过程叫刷盘）。

接下来我们来了解一下，redo log buffer 是什么样的，包括它的存储结构和存储方式，以及 redo log buffer 中缓存的信息是如何刷盘的。

刷盘小知识之 redo log buffer ：redo log buffer 是一片连续的空间，类似于数组，其大小由 innodb_log_buffer_size 来指定，默认是 16 MB，其内每个元素都是一个 512 B 的 redo log block ，这个块（block）包括块头（块信息）、块体和块尾（块校验信息），redo log 主要保存在块体中，一个块体存满了 redo log 那就继续存储到下一块的块体中，buf_free 这个全局参数指明了 redo log 写到了哪个块中。
来自《Innodb引擎 · 基础模块篇(三) · 详解redo log存储结构》

刷盘小知识之 redo file ：redo log buffer 中的信息最终是要写到磁盘的，那它是怎么写入的呢？首先，我们要先了解 redo log 存放在哪里，在 MySQL 的数据目录下，有两个文件，分别是 ib_logfile0 和 ib_logfile1，这两个文件就是 redo log 在磁盘中的保存位置。而下图表示的是 redo log 在磁盘中文件不止一个，可能是多个，称为日志文件组，组内有多少个文件由 innodb_log_files_in_group 决定，组内文件之间形成一个循环队列，一个 ib_log_file 文件写满了就会往下一个 ib_log_file 文件中覆盖写：

为了防止将 redo log 中的未刷盘记录覆盖掉，在这个循环队列中，有两个指针，分别是write pos、checkpoint

• write pos 是当前记录的位置，一边写一边后移
• checkpoint 是当前要擦除的位置，也是往后推移

就像这样：

每当有 redo log buffer 中的记录刷盘到这个日志文件组时，write pos 就会往后移动，那 redo log buffer 中的记录什么条件下会刷盘到日志文件组呢？

InnoDB存储引擎为redo log的刷盘策略提供了 innodb_flush_log_at_trx_commit 参数，它支持三种策略

• 设置为0的时候，表示每次事务提交时不进行刷盘操作
• 设置为1的时候，表示每次事务提交时都将进行刷盘操作（默认值）
• 设置为2的时候，表示每次事务提交时都只把 redo log buffer 内容写入 page cache

另外 InnoDB 存储引擎有一个后台线程，每隔1秒，就会把 redo log buffer 中的内容写到文件系统缓存（page cache），然后调用 fsync 刷盘。
除了后台线程每秒1次的轮询操作，还有一种情况，当redo log buffer占用的空间即将达到 innodb_log_buffer_size 一半的时候，后台线程会主动刷盘。
来自《聊聊redo log是什么？》

那 checkpoint 何时往后移呢？首先日志文件组中的 redo log 记录要可以被覆盖写必须保证它对应的脏页已被刷脏，若是则 checkpoint 可以往后移动，那在什么条件下触发 checkpoint 后移呢？MySQL 提供了两种方式：

• Sharp Checkpoint ： 发生在数据库关闭时将所有的脏页都刷新回磁盘，这是默认的工作方式，参数 innodb_fast_shutdown = 1
• Fuzzy Checkpoint ： InnoDB存储引擎内部使用这种模式,只刷新一部分脏页，而不是刷新所有的脏页回磁盘（和第一个相比这个应该也是会工作的）

FuzzyCheckpoint发生的情况：

• Master Thread Checkpoint：差不多以每秒或每十秒的速度从缓存池的脏页列表中以异步非阻塞方式刷新一定比例的页回磁盘。
• FLUSH_LRU_LIST Checkpoint：因为LRU列表要保证一定数量的空闲页可被使用，所以如果不够会从尾部移除页，如果移除的页有脏页，就会进行此Checkpoint。5.6版本后，这个Checkpoint放在了一个单独的Page Cleaner线程中进行，并且用户可以通过参数innodb_lru_scan_depth控制LRU列表中可用页的数量，该值默认为1024
• Async/Sync Flush Checkpoint：指的是redo log文件不可用的情况(当 write pos = checkpoint 时)，这时需要强制将一些页刷新回磁盘，而此时脏页是从脏页列表中选取的，5.6版本后不会阻塞用户查询
• Dirty Page too much Checkpoint：即脏页的数量太多，导致InnoDB存储引擎强制进行Checkpoint。其目的总的来说还是为了保证缓冲池中有足够可用的页。其可由参数 innodb_max_dirty_pages_pct 控制,比如该值为75，表示当缓冲池中脏页占据75%时，强制进行CheckPoint

来自《了解InnoDB的Checkpoint技术》

在了解了 checkpoint 会往后移动的情况时，我们可以顺便将刷脏时机，也就是什么情况下会将缓存池中的脏页写入磁盘中，为什么放在要这里说呢？因为 checkpoint 会移动多半是因为要刷脏，因此它发生的时机其实也正好是刷脏发生的时机：

InnoDB 刷脏页的时机：

• （Async/Sync Flush Checkpoint）内存中的redo log 写满了，这时系统就会停止所有更新操作，把 checkoutpoint 往前推，redo log 留出空间可以继续写。
• （FLUSH_LRU_LIST Checkpoint）系统中内存不足时，当这个时候需要新的数据页到内存中，就要淘汰掉一些数据页，如果淘汰的是“脏页”，就要先将“脏页”写到磁盘。
• （Master Thread Checkpoint）数据库空闲的时候刷脏页。
• （Sharp Checkpoint）数据库正常关闭的时候，也要把内存中所有的脏页全都flush 到磁盘上。

来自《MySQL的WAL（Write-Ahead Logging）机制》

在简单了解了 redo log 的用处以及产生过程和处理流程后，我们最终会在磁盘中得到多个 redo log 文件，这主要取决于日志文件组的数量。那这些文件按照设计初衷，它是为了在 MySQL 非正常关闭的情况下进行数据恢复的，那具体怎么恢复呢？

在利用 redo log 进行恢复操作时，先把确定需要恢复的起点和终点位置，然后利用 Hash 算法将同一页的修改放在同一个位置，如此便可在对一页修改时可以将此页的数据一起写入磁盘，尽可能少的减少磁盘 IO 次数。
redo log 崩溃恢复详情可见 《Mysql工作原理——redo日志文件和恢复操作》

经过上面的简单理解，我们可以发现 redo log buffer 中的记录仍然是要经常写入磁盘的，而引入 redo log 就是为了减少磁盘 IO 时间，现在倒好，你也时常访问磁盘 IO，那为何不直接对 Buffer Pool 进行刷脏而不使用 redo log 呢？主要是因为：

• 刷脏是随机 IO，因为每个数据页在磁盘中的位置都不尽相同，而 redo log 是在 redo log file 中追加写入，属于顺序 IO。
• 刷脏是以数据页为单位进行的，意味着即使一个数据页中只有一条记录被修改，也要整页写入，而 redo log 只需要在文件尾追加一条记录的修改即可，无效 IO 大大减小。

而为了保证事务的持久性，持久化到磁盘又是事务成功所必须的操作，要么选择在每次数据修改后直接刷脏，要么选择将少量的修改信息持久化到 redo log file 中，也就是说这个持久化的操作与事务是同步执行的，如果选择直接刷脏，事务执行时间将会受到极大影响。但并不是说有了 redo log 就不用刷脏了，当条件满足时，MySQL 依然会进行刷脏操作。

bin log

redo log 因为大小固定，所以不能存储过多的数据，它只能用于未更新的数据落盘（刷盘），而数据操作的备份恢复、以及主从复制是靠 bin log（如果数据库误删需要还原，那么需要某个时间点的数据备份以及bin log）。5.7默认记录的是操作语句涉及的每一行修改前后的行记录。

在更新到数据页缓存或者 Change Buffer 后，首先进行 redo log 的编写，编写完成后将 redo log 设为 prepare 状态，随后再进行 binlog 的编写，等到 binlog 也编写完成后再将 redo log 设置为 commit 状态（这个又叫三步提交）。这是为了防止数据库宕机导致 binlog 没有将修改记录写入，后面数据恢复、主从复制时数据不一致。在断电重启后先检查 redo log 记录的事务操作是否为 commit 状态：

1、如果是 commit 状态说明没有数据丢失，判断下一个。

2、如果是 prepare 状态，检查 binlog 记录的对应事务操作（redo log 与 binlog 记录的事务操作有一个共同字段 XID，redo log 就是通过这个字段找到 binlog 中对应的事务的）是否完整（每种格式（看下面）记录的事务结尾都有特定的标识），如果完整就将 redo log 设为 commit 状态，然后结束；不完整就回滚 redo log 的事务，结束。

三种格式：
1、Row（5.7默认）。记录操作语句对具体行的操作以及操作前的整行信息。缺点是占空间大。优点是能保证数据安全，不会发生遗漏。内容可以通过 " mysqlbinlog + 文件名 " 来查看，一个事务的结尾会有 " Xid" 标记。
2、Statement。记录修改的 sql。缺点是在 mysql 集群时可能会导致操作不一致从而使得数据不一致（比如在操作中加入了Now()函数，主从数据库操作的时间不同结果也不同）。优点是占空间小，执行快。可以使用 "show binlog events in '文件名'" 来查看 statement 格式的日志内容（通用），一个事务的结尾会有 " COMMIT " 标志。
3、Mixed。会针对于操作的 sql 选择使用Row 还是 Statement。缺点是还是可能发生主从不一致的情况。

binlog 的写入机制比较简单：事务执行的过程中，先把日志写到 binlog cache，事务提交的时候，再把 binlog cache 写到 binlog 文件中。

系统给 binlog cache 分配了一片内存，每个线程一个，参数 binglog_cache_size 用于控制单个线程内 binlog cache 的内存大小，超过就要暂存在磁盘。

事务提交的时候，执行器把 binlog cache 里完整事务写入到 binlog 中，并清空 binlog cache。

write 指的是把日志写入到文件系统的 page cache，并没有把数据持久化到磁盘，所以速度比较快。
fsync 是持久化到磁盘的操作，一般情况下， fsync 才会占磁盘的 IOPS（Input/Output Operations Per Second）。

write 和 fsync 的时机，是由参数 sync_binlog 控制的：

sync_binlog=0 的时候，表示每次提交事务都只 write，不 fsync；
sync_binlog=1 的时候，表示每次提交事务都会执行 fsync；
sync_binlog=N(N>1) 的时候，表示每次提交事务都 write，但累积 N 个事务后才 fsync。
此小节内容摘自《一条 sql 的执行过程详解》 和 《MySQL的WAL（Write-Ahead Logging）机制》

三个日志文件的区别

1、undo log是用于事务的回滚、保证事务隔离级别读已提交、可重复读实现的。redo log是用于对暂不更新到磁盘上的操作进行记录，使得其可以延迟落盘，保证程序的效率。bin log是对数据操作进行备份恢复（并不能依靠 bin log 直接完成数据恢复）。

2、undo log 与 redo log 是存储引擎层的日志，只能在 InnoDB 下使用；而bin log 是 Server 层的日志，可以在任何引擎下使用。

3、redo log 大小有限，超过后会循环写；另外两个大小不会。

4、undo log 记录的是行记录变化前的数据；redo log 记录的是 sql 的数据页修改逻辑以及 change buffer 的变更；bin log记录操作语句对具体行的操作以及操作前的整行信息（5.7默认）或者sql语句。

5、单独的 binlog 没有 crash-safe 能力，也就是在异常断电后，之前已经提交但未更新的事务操作到磁盘的操作会丢失，也就是主从复制的一致性无法保障，而 redo log 有 crash-safe 能力，通过与 redo log 的配合实现 "三步提交"，就可以让主从库的数据也能保证一致性。

6、redo log 是物理日志，它记录的是数据页修改逻辑以及 change buffer 的变更，只能在当前存储引擎下使用，而 binlog 是逻辑日志，它记录的是操作语句涉及的每一行修改前后的值，在任何存储引擎下都可以使用。

此小节内容摘自《MySQL 中的WAL机制》

事务四大隔离级别实现原理

有了上面对三种日志的简单了解后，我们就可以来探讨 MySQL 是如何实现事务的各个隔离级别了。

读已提交和可重复读

实现这两个级别主要靠的是 MVCC（多版本并发控制），在 undo log 一小节中，我们有简单提到过 MVCC 的核心就是依靠版本链的 Read View 机制，其核心有二，一是版本链，二是 Read View 机制，版本链我们已经知道它是什么样的了，所以接下来重点就是来看看 Read View 机制的原理：

Read View 内部主要有四个部分组成，第一个是创建当前 Read View 的事务 id creator_trx_id，第二个是创建 Read View 时还未提交的事务 id 集合 trx_ids，第三个是未提交事务 id 集合中的最大值 up_limit_id，第四个是未提交事务 id 集合中的最小值 low_limit_id。

当执行查询操作时（当操作为非查询时，为版本链的职责）会先找磁盘上的数据，然后根据 Read View 里的各个值进行判断，

1）如果该数据的 trx_id 等于 creator_trx_id，那么就说明这条数据是创建 Read View的事务修改的，那么就直接返回；

2）如果 trx_id 大于等于 up_limit_id，说明是新事务修改的，那么会根据 roll_pointer 找到上一个版本的数据重新比较；

3）如果 trx_id 小于 low_limit_id，那么说明是之前的事务修改的数据，那么就直接返回；

4）如果 trx_id 是在 [low_limit_id, up_limit_id) 区间范围内，那么需要去 trx_ids 中对各个元素逐个判断，如果存在相同值的元素，就根据 roll_pointer 找到上一个版本的数据，然后再重复判断；如果不存在就说明该数据是创建当前 Read View 时就已经修改好的了，可以返回。

而读已提交和可重复读之所以不同就是它们 Read View 生成机制不同，读已提交是每次 select 都会重新生成一次，而可重复读是一次事务只会创建一次且在第一次查询时创建 Read View。事务启动命令 begin/start transaction 不会创建 Read View，但是通过 start transaction with consistent snapshot 开启事务就会在开始时就创建一次 Read View。

其实上述步骤的核心就是 Read View 会记录当前时刻，正在活跃的所有事务（包括创建者自己）trx_ids，也就是还未提交的事务有哪些，然后循着版本链依次寻找，只有那些事务 ID 比正在活跃的最小事务 ID 还小的或者在 [low_limit_id, up_limit_id] 区间范围内的但是又不是正在活跃的事务 ID 的版本（也就是在我创建 Read View 前就已经提交的事务所修改的版本），才是创建这个 Read View 的事务想要找的版本，也就是说我要的版本不可能是当前正在活跃的这些事务或者以后的事务修改过的。

这样说来，便可以很容易地理解为什么每次 select 时创建一个 Read View 会导致读已提交，因为在事务创建一个新的 Read View 前，如果有另一个事务提交了数据，那么它就是这个事务所要寻找的那个版本。而只在第一次查询时创建 Read View，则不会因为后面有新的事务哪怕是 trx_ids 中的某个事务提交而变更需要的版本，因为后面新提交的事务 ID trx_id > up_limit_id，因此按照算法一定会找到上一个版本，而如果是 trx_ids 中的某个事务提交，按照算法第四步，trx_id 在 trx_ids 集合中，也同样会找到上一个版本，这样我们可以说，相同的 Read View 一定能保证每次所读的版本都一样。

假设在事务 3 第一次 select 的时候创建 Read View 的 trx_ids = {2, 3}， trx_id = 1 ，那么 up_limit_id = 3, low_limit_id = 2，根据算法读取到的版本是 trx_id = 1；

然后假设在事务 3 进行第二次 select 操作前，事务 2 提交了修改，那么 trx_id = 2，在事务 3 第二次 select 操作时，对于读已提交，又会创建一个新的 Read View ，其 creator_trx_id = 3，trx_ids = {1, 5}，那么 up_limit_id = 5, low_limit_id = 1，根据算法，事务 3 读取的结果是事务 2 所提交的修改(2 < 5 且 2 > 1 但 2 不属于 {1, 5}，故直接返回)。

如果是可重复读，那么事务 3 第二次 select 时，Read View 不会重新创建，而仍然是 trx_ids = {2, 3}， trx_id = 1 ，up_limit_id = 3, low_limit_id = 2，那么即使此时 trx_id = 2，但是由于 2 在 trx_ids 中，所以会去找上一个版本，而上一个版本是 trx_id = 1，这样最终读取的数据仍然是 trx_id = 1。

我们知道 MySQL 的可重复读级别下幻读也是不可能的，那具体是怎么防止幻读的呢？首先要了解一下：

• 快照读：读取的是数据的可见版本，就是说可能不是最新版本
• 当前读：读取的是最新版本，也就是数据表中的版本，而不是位于 undo log 的旧版本。

在 Mysql 的隔离级别中，除了可串行化级别的读外，其他隔离级别中事务的读都是快照读。
首先是可串行化中事务的读操作是当前读，而四种隔离级别中的所有修改（insert、update、delete）操作都属于当前读。可能你觉得读操作和修改操作没有关系，但是事实是这些修改操作是先 “读” 找到数据具体的位置才能进行 “修改”。
来自《MySQL中的事务原理和锁机制》

我们知道幻读产生是因为新增或者删除了一条记录，那 MVCC 是如何解决幻读的呢？我们以例子来说明：

“快照读”幻读

时刻	事务 A	事务 B
1
2
3
4
5

在上表中，
1 时刻：事务 A (假设事务 A Id = 1)和事务 B (假设事务 B Id = 2)同时开始；

2 时刻：事务 A 首先执行 select 命令，这会生成一个 Read View(creator_trx_id = 1, trx_ids = {1, 2}, up_limit_id = 2, low_limit_id = 1)。

3 时刻：事务 B 删除了一条 id = 9 的记录，并作了一次查询， id = 9 的记录确实已经删除了，在这次查询时，事务 B 也一样会生成一个 Read View(creator_trx_id = 2, trx_ids = {1, 2}, up_limit_id = 2, low_limit_id = 1)。在这里我们要简单扩展一下以阐明对于 delete 操作 undo log 是如何处理的：

数据记录被删除时，是分为两个阶段的，第一个阶段就是给被删除记录打上一个删除标识，此时这个记录仍然在数据表中，只是记录中打上了删除标识，而且 trx_id 指向了删除它的事务 ID ，roll_point 指向 undo log 中删除前的版本。第二个阶段，是当删除此纪录的事务提交后且与此记录相关的事务都结束后，也就是再无任何事务共享此记录时，会启动一个 purge 后台线程来对这个记录进行实际的删除，同时将相关的 undo log 版本链信息清除。
参考《Mysql工作原理——增删改类型undo日志格式介绍》、《mysql purge 触发条件_什么是purge操作》

3 时刻：事务 B 基于这样的原理，id = 9 的数据记录 trx_id = creator_trx_id = 2，所以事务 B 能够看到这条数据的最新版本，然后再一看，此纪录已被打上删除标识，所以就不再返回，于是 select 结果集中就没有这条记录了。

4 时刻：事务 A 再次执行 select 操作，由于此时事务隔离级别是可重复读 ：

mysql> SHOW VARIABLES LIKE '%isolation%';
+-----------------------+-----------------+
| Variable_name         | Value           |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
+-----------------------+-----------------+
1 row in set, 1 warning (0.01 sec)

所以这次的 select 操作并不会生成新的 Read View ，而是使用第一次生成的 Read View 进行判断，我们可以发现 id = 9 的记录仍然能够被事务 A 查询出来，为什么呢？因为由于还有事务 A 共享此记录，且事务 B 尚未提交，所以 id = 9 的记录仍然在数据表中，只是被打上了删除标识，且 trx_id = 2,根据 Read View 算法， 2 在 trx_ids 集合中，因此必然会去找上一个版本，而上一版本就是还未删除（未被打上删除标记的），所以事务 A 可以读出来，而这正好说明已经避免了幻读。

4 时刻：除了 select 操作，事务 A 还执行了 insert 操作新增了一条数据，对于新增的数据，和以我粗浅的理解力在讲 undo log 的时候一样，我简单地置为 trx_id = 1, roll_point = null ，这样，当事务 A 自己执行 select 的时候，发现 trx_id = creator_trx_id = 1，于是可以事务 A 可以看到这条记录，以下面那条 select 为证。

5 时刻：事务 B 也执行 select 操作，这主要是想验证事务 A 新增的数据记录，事务 B 能否看见，此时事务 B 的 Read View(creator_trx_id = 2, trx_ids = {1, 2}, up_limit_id = 2, low_limit_id = 1)，而此纪录的 trx_id = 1 在 trx_ids 集合中，于是去找上一版本，可是发现没有上一个版本(roll_point = null)，说明这条记录没有对事务 B 是可见的版本，于是就没有查出这条记录，这也就避免了幻读。

一个事务只能有一个 Read View 存在，以最新创建的为准

“当前读”幻读

同样举个例子：

时刻	事务 A	事务 B
1
2
3
4
5

上表中：
1 时刻： 开始了事务 A 和事务 B，假设它们的事务 Id 分别为 3，5；
2 时刻： 事务 A 执行查询，生成 Read view (creator_trx_id = 3, trx_ids = {3, 5}, up_limit_id = 5, low_limit_id = 3) ；
3 时刻： 事务 B 新增一条 id = 20 的数据，并提交了事务；
4 时刻： 事务 A 也想新增一条 id = 20 的数据，但是失败了，提示主键 20 重复，为什么呢，事务 A 之前明明没有增加这条数据。因为虽然这条由事务 B 新增的数据对事务 A 不可见，但它却是实实在在地写在数据表中，而新增一条数据时，也是要实实在在地写入数据表，表中又有主键唯一性约束，自然就会冲突。
5 时刻： 事务 A 不信邪，查查看之前哪里有插入这条 id = 20 的数据，一看果然没有（因为这条记录 trx_id = 5，在 trx_ids 集合中，所以不会返回），那就让它原形毕露，去修改 id = 20 这条数据记录，然后再查，果然原形毕露，请问是怎么回事？因为 id = 20 这条数据记录虽然对事务 A 暂不可见，但是却是真实存在的，于是当它修改 id = 20 这条数据时，并不会涉及 Read View 机制（只有读的时候会触发），而是触发了 undo log 机制，将 id = 20 的 trx_id 由事务 B 的 ID 5 改为了 3，于是再次读取时，trx_id = creator_trx_id = 3 ，于是返回这条记录。

这个例子说明对于“当前读”幻读问题仍旧是存在的。那该如何是好呢？别急，MySQL 还有它自己的锁机制，用来防止这种当前读产生的问题。

读未提交和可串行化实现

这两个实现比较简单。读未提交就是每次事务执行的修改都更新到对应的数据上，然后读取直接读取这个数据就可以了。而可串行化则是使用了读锁和写锁以及间隙锁来实现的，对会造成“幻读”、“脏读”、“不可重复读” 的操作会进行阻塞，也正因为这样，极易任意造成阻塞，所以不建议使用可串行化级别。

MySQL 的锁机制

对于存储引擎 MyISAM ，只支持表级锁，对于 InnoDB 来说，既支持表级锁、也支持行级锁。所以 InnoDB 可以用于高并发的场景下而 MyISAM 不行。

分类

• 根据颗粒度划分：行级锁，表级锁，全局锁，页级锁
• 根据种类划分：共享锁（读锁）、排他锁（写锁）
• 其他种类：记录锁(Record Lock)、间隙锁(Gap Lock)、Next-Key Lock（记录锁 + 间隙锁）、MDL（MetaData Lock）、自增锁

记录锁与间隙锁

首先我们要知道记录锁和间隙锁，锁的是什么，要知道这一点，我们需要有必要回顾一下 MySQL 的 InnoDB 存储引擎的底层原理，我们知道，数据底层是一个 B+ 树索引表，它的数据记录也是直接存放在 B+ 树的叶子节点中的，每个叶子节点又以链表的形式相互连接，可以简化为这样：

图片及参考来自于《什么是间隙锁》

记录锁锁的就是一条真实存在的索引树叶子，而间隙锁锁的是叶子节点指向下一个叶子节点的 next 指针。 Next-Key Lock 锁的就是叶子节点本身和 next 指针，所以说它是记录锁 + 间隙锁。说穿了锁住的索引树，所以对于辅助索引，也一样可以产生锁。

加锁的原则：

原则1：加锁的基本单位是 next-key lock。需要注意的是，next-key lock 都是前开后闭区间。
原则2：查找过程中访问到的对象才会加锁（只要加锁就要遵循原则 1）。
原则3：非唯一索引会向排序方向一直匹配（升序向右，降序向左），直到不满足条件为止。唯一索引不需要。
优化1：索引上的等值查询，给唯一索引加锁的时候，next-key lock 会退化成行锁。
优化2：索引上的等值查询，向一个方向遍历（取决于降序还是升序）直到有一个值不符合查询条件，且这个值不满足等值条件的时候，next-key lock 会退化成间隙锁。
一个 bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止。（MySQL 8.0 已被修复）
参考自《MySQL中的事务原理和锁机制》

举几个简单的例子：

# InnoDB 存储引擎新建表
CREATE TABLE `test1` (
  `id` int(1) NOT NULL AUTO_INCREMENT,
  `number` int(1) NOT NULL COMMENT '数字',
  PRIMARY KEY (`id`),
  KEY `number` (`number`)
)；

# 插入数据
INSERT INTO `test1` VALUES (1, 1);
INSERT INTO `test1` VALUES (5, 3);
INSERT INTO `test1` VALUES (7, 8);
INSERT INTO `test1` VALUES (11, 12);

# 事务 A
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM test1 WHERE id = 5 FOR UPDATE;
+----+--------+
| id | number |
+----+--------+
|  5 |      3 |
+----+--------+
1 row in set (0.00 sec)

我们先根据这条查询语句分析一下，它锁住了什么，首先，这个过程是通过索引树的搜索过程得到的，在引擎内部，其实是要找到 id=5 的这个值，最终也找到了（这个过程就是等值查询，即：通过索引树搜索的就是等值查询），所以根据原则 2，访问的数据要加锁，再根据原则 1 ，就锁住了 (1, 5] ，然后由于是主键，根据优化 1 ，给唯一索引加锁，退化为行锁，于是只锁住了 5 这条记录，根据原则 3，唯一索引不必继续寻找不符合查询条件的下一个值，所以这个查询只锁住了 5 这条记录，这就是一个记录锁。

# 事务 B 验证
mysql> BEGIN;
Query OK, 0 rows affected (0.09 sec)

# 阻塞，我中断了它
mysql> UPDATE test1 SET number = 10 WHERE id = 5;
^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted

# 其余不阻塞
mysql> INSERT INTO test1(id, number) value(4, 3);
Query OK, 1 row affected (0.03 sec)

mysql> INSERT INTO test1(id, number) value(6, 3);
Query OK, 1 row affected (0.00 sec)

mysql> ROLLBACK;
Query OK, 0 rows affected (0.14 sec)

同样的数据，演示一下间隙锁：

# 在未结束的事务 A 中继续
mysql> SELECT * FROM test1 WHERE id = 3 FOR UPDATE;
Empty set (0.00 sec)

同样分析一波，这个过程是通过索引树的搜索过程得到的，在引擎内部，其实是要找到 id=3 的这个值，只是最终没找到，但找到了 (1,5) 这个间隙。（这里也是等值查询，即：通过索引树搜索的就是等值查询）。于是根据原则 3 由于 id 是主键索引，也就是特殊的唯一索引，所以不用再往后找到一个不符合条件的值为止，因此结束，故锁住的范围是 (1, 5) 这个区间。

同样，事务 B 验证：

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

# (1, 5) 区间都锁住了
mysql> INSERT INTO test1(id, number) value(2, 1);
^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted

mysql> INSERT INTO test1(id, number) value(4, 1);
^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted

# 1 没锁住
mysql> UPDATE test1 SET number = 100 WHERE id = 1;
Query OK, 1 row affected (0.06 sec)
Rows matched: 1  Changed: 1  Warnings: 0

# 5 被锁住了是因为事务 A 第一条查询语句锁住了 id = 5 的记录，锁是在事务结束后释放的
mysql> UPDATE test1 SET number = 100 WHERE id = 5;
^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted

# > 5 的没锁住
mysql> INSERT INTO test1(id, number) value(6, 1);
Query OK, 1 row affected (0.01 sec)

mysql> ROLLBACK;
Query OK, 0 rows affected (0.06 sec)

接下来，说说范围查询：

# 事务 A 提交后重新开始
mysql> ROLLBACK;
Query OK, 0 rows affected (0.00 sec)

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM test1 WHERE id > 3 AND id < 9 FOR UPDATE;
+----+--------+
| id | number |
+----+--------+
|  5 |      3 |
+----+--------+
1 row in set (0.07 sec)

这个查询，首先它查找的是 id = 3 的索引记录（这里通过索引树查，所以也是等值查询），但是没找到，找到的是间隙 (1, 5)，然后沿着链表向右找，找到的第一个值 5，于是根据原则 2，加锁 (1, 5]，此时优化 1 和 2 不符合条件，因为它是沿着链表查的，不是等值查询，所以不退化为记录锁，继续沿着链表向右找，找到一个 7 ，满足查询条件，于是加锁 (5, 7]，同理不满足优化 1 和 2，继续找到下一个 11 ，不满足查询条件，但被访问了，根据原则 2，加锁 (7, 11]，但根据优化 2 不是等值条件，于是退化为间隙锁 (7, 11)，所以最终这个查询锁住的范围是 (1, 5],(5, 7], (7, 11)。

事务 B 重新开始验证：

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

# (1, 11) 区间都锁住了
mysql> INSERT INTO test1(id, number) value(2, 1);
^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted

mysql> INSERT INTO test1(id, number) value(4, 1);
^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted

mysql> INSERT INTO test1(id, number) value(6, 1);
^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted

mysql> INSERT INTO test1(id, number) value(8, 1);
^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted

mysql> UPDATE test1 SET number = 100 WHERE id = 5;
^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted

mysql> UPDATE test1 SET number = 100 WHERE id = 7;
^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted

# 1 没锁住
mysql> UPDATE test1 SET number = 100 WHERE id = 1;
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0

# 11 没锁住
mysql> UPDATE test1 SET number = 100 WHERE id = 11;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

# > 11 没锁住
mysql> INSERT INTO test1(id, number) value(12, 1);
Query OK, 1 row affected (0.01 sec)

mysql> ROLLBACK;
Query OK, 0 rows affected (0.10 sec)

死锁

由于一个事务本质上其实是由一个线程来执行的，这也就意味着事务造成的死锁与多线程的死锁的原因是一样的，即都是在互相等待对方先前持有的锁而导致的，举个例子：

# 先简单看看 book 表中有哪些数据
mysql> SELECT * FROM book;
+----+---------------+----------------+-------+
| id | book_name     | author         | count |
+----+---------------+----------------+-------+
|  1 | 高等数学       | 同济大学数学系  |    10 |
|  6 | Computer      | Computer       |    10 |
|  8 | Java          | Java           |    10 |
| 15 | Test          | lizhpn         |   100 |
| 18 | C             | C              |   100 |
| 20 | Test And Test | lizhpn         |    10 |
| 23 | Test And Test | lizhpn         |   100 |
+----+---------------+----------------+-------+
7 rows in set (0.10 sec)

然后开启一个连接作为事务 A ，先执行：

mysql> BEGIN;
Query OK, 0 rows affected (0.02 sec)

mysql> SELECT * FROM book WHERE id = 3 FOR UPDATE;
Empty set (0.00 sec)

事务 A 首先根据 SELECT 语句会锁住的范围为 (1, 6) 区间，然后重新打开一个连接作为事务 B，执行以下命令：

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM book WHERE id > 8 AND id <= 15 FOR UPDATE;
+----+-----------+--------+-------+
| id | book_name | author | count |
+----+-----------+--------+-------+
| 15 | Test      | lizhpn |   100 |
+----+-----------+--------+-------+
1 row in set (0.10 sec)

# 这里会要求一个范围为 (1, 6) 的锁，而它被事务 A 持有，因此会阻塞
mysql> INSERT INTO book(id, book_name, author, count) VALUE(2, "Java", "LZP", 100);

事务 B 执行完这个 SELECT 命令后锁住的范围是 (8, 15], 那为什么没有 (15, 18)，因为 id 列是唯一索引，根据原则 3 唯一索引不需要一直找到不符合条件的值，但对于符合条件的都需要访问，因此只访问了 15 而没有再访问 18，因此没有 (15, 18) 这个范围。然后回到事务 A ，执行：

# 这里会要求一个范围为 (8, 15) 的锁，而它被事务 B 所持有，因此按理也会阻塞，这样
# 事务 A 无法释放锁 (1, 6)，事务 B 也就无法释放锁 (8, 15]，就这样形成死锁
mysql> INSERT INTO book(id, book_name, author, count) VALUE(11, "Java", "LZP", 100);
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

死锁解除

在上面事务 A 最后一个 INSERT SQL 语句中，并不是如我们所期望的那样阻塞了，而是告诉了我们产生了死锁，并回滚了事务 A，释放了事务 A 持有的所有锁，因此事务 B 得以继续执行。那为什么它没有阻塞呢？这里就要来聊聊如果产生了死锁怎么办？

超时

当一个事务执行超时，事务回滚，这样即便发生了死锁，也会因为其中一个事务超时回滚而得到解除。

Wait-for graph

译为等待图，是一种主动的死锁检测机制，相比于超时，它更倾向于预防式，即任何时候都不会发生死锁，一旦某个事务将会引发死锁，那么 undo log 最少的那个事务将被回滚，从而避免了死锁的产生。那它是怎样工作的呢？

简单来说，它维护着两个信息，一个是事务等待链表，另一个是锁的信息链表。

• 事务等待链表：记录当前时刻正在活跃的事务集合
• 锁的信息链表：标识哪个事务在申请什么锁（排他锁或共享锁），以队列的形式存在，每个列都有这样的一个队列

根据这两个表构建出一张图，图中的节点是事务等待链表中的活跃事务，边的信息是由锁的信息链表所决定的，一旦某个事务要等待某个事务释放锁时，就添加一条有向边指向它，等某时刻的等待图构建好后，判断图中是否存在回路，如果有回路，那么说明发生了死锁。

具体流程可以看这里：

后面是说不用递归了，而采用非递归方式实现

来自《当前数据库普遍使用wait-for graph等待图来进行死锁检测》