系列文章：

【Mysql-InnoDB 系列】InnoDB 架构

【Mysql-InnoDB 系列】锁

【Mysql-InnoDB 系列】事务模型

【Mysql-InnoDB 系列】关于一致读

【Mysql-InnoDB 系列】锁定读

【Mysql-InnoDB 系列】事务提交过程

一 幻读

关于幻读，在网上可以搜到很多种解释。这里我们还是先看mysql官方文档中给出的定义：

The so-called phantom problem occurs within a transaction when the same query produces different sets of rows at different times. For example, if a SELECT is executed twice, but returns a row the second time that was not returned the first time, the row is a “phantom” row.

幻读问题，是指在一个事务中，相同的查询在不同的时间得到了不同的（结果）集合。例如，如果一个 SELECT 查询语句执行两次，但第二次查询返回的一条记录不在第一次查询的返回中，这行就是一个“幻”行。

假设有一张 child 表，id 列上建立了索引，你想要查询并锁住所有 id 大于 100 的行，并试图在稍后更新其中的某一列，查询 sql 如下：

SELECT * FROM child WHERE id > 100 FOR UPDATE;

这个查询会从符合 id 大于 100 的第一记录开始扫描索引。假设表包含 id 值为 90 和 102 的行，如果在扫描范围内的索引记录上设置的锁没有锁定在间隙中所做的插入（在本例中是 90 和 102 之间的间隙），则另一个会话可以在表中插入 id 为 101 的新行。如果要在同一事务中执行相同的 SELECT，则在查询返回的结果集中会看到一个 id 为 101 的新行（“幻影”）。如果我们将一组行视为一个数据项，那么新的幻像 child 行将违反事务的隔离原则，即事务在运行时，它读取的数据在事务期间不会更改。

为了避免幻读，InnoDB 使用名为临键（next-key）锁的算法，融合了 index-row 锁 和 间隙（gap）锁。InnoDB 执行行级锁定的方式是，当它搜索或扫描表索引时，它会对遇到的索引记录设置共享或排他锁。因此，行级锁事实上是索引记录所（index-record 锁）。另外，索引记录上的 next-key 锁也会影响该索引记录之前的“间隙”。也就是说，一个 next-key 锁是索引记录锁加上索引记录前面的间隙上的间隙锁。当一个会话 A 有记录 R 索引上的共享锁或排它锁，另一个会话不能在紧靠索引顺序的 R 之前的间隙中插入新的索引记录（需要等会话 A 释放锁）。

当 InnoDB 扫描索引时，可能也会锁住索引中最后一条记录之后的间隙。就发生在前面的例子中：为了防止在 id 大于 100 的表中插入任何内容，InnoDB 设置的锁在 id 值 102 之后的间隙上包含一个锁。

可以使用 next-key 锁在应用程序中实现唯一性检查：如果您在共享模式下读取数据，但没有看到要插入的行的重复项，则可以安全地插入行，并知道在读取期间在行的后续行上设置的 next-key 锁可防止任何人同时插入行的重复项。

间隙锁可以被禁用。但这可能会导致幻读问题，因为其他会话可以在间隙锁被禁用的情况下，把新纪录插入间隙。

二 死锁

2.1 InnoDB 死锁示例（mysql8.0 版本）

下面的示例阐述了，当一个锁请求导致死锁时会发生怎样的错误。示例包括两个客户端，A 和 B。

首先，客户端 A 创建一个质保函一行记录的表，然后开启一个事务。在这个事务内，A 通过共享模式查询，获得这行记录的一个 S 锁（共享锁）。

mysql> CREATE TABLE t (i INT) ENGINE = InnoDB;Query OK, 0 rows affected (1.07 sec)
mysql> INSERT INTO t (i) VALUES(1);Query OK, 1 row affected (0.09 sec)
mysql> START TRANSACTION;Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM t WHERE i = 1 FOR SHARE;+------+| i    |+------+|    1 |+------+

需要特别注意的是，select * from xx for share 是 mysql8 版本的语法，当在 mysql 低版本中执行这条语句时，会报语法错误，例如我在 5.7.28-log：

mysql> SELECT * FROM t WHERE i = 1 FOR SHARE;ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SHARE' at line 1
mysql> select @@version;+------------+| @@version  |+------------+| 5.7.28-log |+------------+

如果要继续这个示例，需要改为：

SELECT * FROM t WHERE i = 1 lock in share mode;

接下来，客户端 B 开启一个事务，并尝试从表中删除这行记录。

mysql> START TRANSACTION;Query OK, 0 rows affected (0.00 sec)
mysql> DELETE FROM t WHERE i = 1;

删除操作需要一个 X 锁（排它锁）。这个锁不会被授予，因为它和客户端 A 持有的排它锁不兼容，所以请求进入行和客户机 B 块的锁请求队列。

最后，客户端 A 还尝试从表中删除行：

mysql> DELETE FROM t WHERE i = 1;

在这里由于客户端 A 需要一个 X 锁来删除这行记录，所以发生了死锁。但是无法授予该锁请求，因为客户机 B 已经请求了 X 锁，并且正在等待客户机 a 释放其 S 锁；也不能因为 B 事先请求 X 锁而将 A 持有的基础锁升级为 X 锁。结果，InnoDB 为其中一个客户机生成一个错误并释放其锁。客户端返回此错误：

ERROR 1213 (40001): Deadlock found when trying to get lock;try restarting transaction

此时，可以授予另一个客户端的锁请求，并从表中删除该行。

注：另外还有一种情况，上述示例中客户端 A 一直持有 S 锁，且不执行删除动作也不提交；客户端 B 会一直等待 X 锁，但不会无限制的等下去。当超过超时时间阈值时，会返回如下错误：

mysql>  delete from t where i=1;ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

默认情况是 50 秒，这是 InnoDB 事务等待行锁的时长限制，在参数 innodb_lock_wait_timeout 中：

mysql> show variables like 'innodb_lock_wait_timeout';+--------------------------+-------+| Variable_name            | Value |+--------------------------+-------+| innodb_lock_wait_timeout | 50    |+--------------------------+-------+

2.2 死锁检测

当开启死锁检测默认开启，InnoDB 会自动检查事务中的死锁，并回滚一个或多个事务来打破死锁。InnoDB 尝试选择“小”事务来进行回滚，事务的大小由插入，更新，或删除的行数来决定。

如果 innoDB_table_locks=1（默认值）和 autocommit=0，那么 InnoDB 感知表锁，并且上面的 MySQL 层感知行级锁。否则，InnoDB 无法检测死锁，其中涉及由 MySQL lock TABLES 语句设置的表锁或由 InnoDB 以外的存储引擎设置的锁。通过设置 innodb_lock_wait_timeout 这个系统变量可以解决这些问题。

如果 InnoDB Monitor 输出的LATEST DETECTED DEADLOCK部分包含一条消息，说明 TOO DEEP OR LONG SEARCH IN THE LOCK TABLE WAITS-FOR GRAPH, WE WILL ROLL BACK FOLLOWING TRANSACTION（在锁表 WAITS-FOR 图中搜索太深或太长，我们将回滚下面的事务），这表明 wait-for 列表上的事务数已经达到了 200 的限制。超过 200 个事务的等待列表将被视为死锁，并尝试检查等待列表的事务将回滚。如果锁定线程必须查看等待列表上事务拥有的超过 1000000 个锁，也可能发生相同的错误。

2.2.1 禁用死锁检测

在高并发系统中，当多个线程等待同一把锁时，死锁检测可能导致减速。有时，禁用死锁检测并在死锁发生时依赖 innodb_lock_wait_timeout 设置事务回滚可能更有效。死锁检测可以使用 innodb_deadlock_detect 配置项禁用。

mysql> show variables like 'innodb_deadlock_detect';+------------------------+-------+| Variable_name          | Value |+------------------------+-------+| innodb_deadlock_detect | ON    |+------------------------+-------+1 row in set (0.01 sec)

2.3 如何最小化和处理死锁

前面描述了死锁场景和检测工具，本章以上一章的死锁检测内容为基础，将描述怎样最小化和处理死锁。

死锁在事务型数据库中是一个经典问题，但它们并不危险，除非它们非常频繁，以至于根本无法运行某些事务。通常，必须编写应用程序，以便在事务由于死锁而回滚时，它们随时准备重新发布事务。

InnoDB 使用自动行级锁，即使事务只是插入或删除一行，也可能会出现死锁。这是因为这些操作并不是真的“自动”；它们会自动对插入或删除的行的索引记录（可能有多个）设置锁。

我们可以使用以下技术处理死锁并降低死锁发生的可能性：

• 任意时间，都可以通过 show_engine_innodb_status 命令来确认最近一次死锁的原因。这可以帮你调整应用来避免死锁。

• 如果频繁死锁报警引起关注，可以通过开启 innodb_print_all_deadlocks 配置选项来收集更多调试信息。不只是最近的一个，每条关于死锁的信息都被记录到了 MySQL 的 error log 中。在调试完成后关闭这个选项。

• 如果事务因死锁而失败，请随时准备重新发出事务。死锁并不危险，只要重试就好。

• 使事务保持小而短的持续时间，以使它们不易发生冲突。

• 在进行一组相关更改后立即提交事务，以使它们不易发生冲突。特别是，不要让交互式 mysql 会话在未提交事务的情况下长时间处于打开状态。

• 如果使用锁定读(SELECT ... FOR UPDATE 或 SELECT ... FOR SHARE)，尝试使用较低的隔离级别，例如 READ COMMITTED。

• 当修改一个事务中的多个表或同一个表中的不同行集时，每次都以一致的顺序执行这些操作。那么事务会形成定义良好的队列，不会死锁。例如，将数据库操作组织到应用程序中的函数中，或者调用存储过程，而不是在不同的位置编码多个类似的 INSERT、UPDATE 和 DELETE 语句序列

• 为表添加精心选择的索引。这样查询需要扫描更少的索引记录，从而设置更少的锁。使用 EXPLAIN SELECT 来确定 MySQL 服务器认为哪些索引最适合你的查询。

• 少使用锁。如果能够接受通过 SELECT 来返回旧的快照数据，就不要增加 FOR UPDATE 或 FOR SHARE 子句。在这里，使用 READ COMMITTED 隔离级别比较适合，因为同一事务中的每个一致读取都从自己的新快照中读取。

• 如果没有其他帮助，请用表级锁串行化事务。在事务表（例如 InnoDB 表）中使用 LOCK TABLES 的正确方式，是通过设置 autocommit=0（而不是 START TRANSACTION）开启事务，然后使用 LOCK TABLES，并且不要在你完全提交事务之前调用 UNLOCK TABLES。例如，如果你需要写表 t1 并且读表 t2，你可以像下面方式：

SET autocommit=0;LOCK TABLES t1 WRITE, t2 READ, ...;... do something with tables t1 and t2 here ...COMMIT;UNLOCK TABLES;

• 串行化事务的其他方法，是创建一个辅助的“信号量”表，它只包含一行。让每个事务在访问其他表之前更新该行。这样，所有事务都以串行方式发生。注意，InnoDB 即时死锁检测算法也适用于这种情况，因为序列化锁是行级锁。对于 MySQL 表级锁，必须使用 timeout 方法来解决死锁。

三 事务调度

InnoDB 使用 竞争感知事务调度（CATS）算法来对等待锁定的事务进行优先级排序。当多个事务等待同一对象上的锁时，CATS 算法确定哪个事务首先接收锁。

CATS 算法通过分配调度权重来确定等待事务的优先级，调度权重是根据事务阻塞的事务数计算的。例如，如果两个事务正在等待同一对象上的锁，则阻塞最多事务的事务将被分配更大的调度权重。如果权重相等，则优先处理等待时间最长的事务。

注意：

在 MySQL 8.0.20 之前，InnoDB 也使用先入先出（FIFO）算法来调度事务，CATS 算法只用在重锁竞争的场景。MySQL 8.0.20 中的 CATS 算法增强使 FIFO 算法变得多余，允许删除它。从 MySQL 8.0.20 开始，以前由 FIFO 算法执行的事务调度由 CATS 算法执行。在某些情况下，此更改可能会影响事务被授予锁的顺序。