贫瘠之地

华北无浪漫，死海扬起帆
多少个夜晚，独自望着天

0%

MySQL 5.7 官方文档 - InnoDB 引擎.md

发表于 分类于 > >

前言

摘自 MySQL 5.7 官方文档 14 章 The InnoDB Storage Engine 下的 InnoDB Locking and Transaction Model

主要内容为 InnoDB 引擎下的锁机制和事务模型

MySQL :: MySQL 5.7 Reference Manual :: 14.7 InnoDB Locking and Transaction Model

InnoDB 的锁机制和事务模型

要实现大规模（large-scale）、繁忙或高可用（highly reliable）的数据库应用程序、从不同的数据库系统移植大量代码，或调整 MySQL 性能，了解 InnoDB 锁定和 InnoDB 事务模型是很重要的。

本节讨论了与 InnoDB 锁机制和您应该熟悉的 InnoDB 事务模型相关的几个主题

  • 14.7.1 - InnoDB 的锁机制
  • 14.7.2 - InnoDB 的事务模型
  • 14.7.3 - InnoDB 中不同 SQL 语句设置的锁
  • 14.7.4 - 幻影行
  • 14.7.5 - InnoDB 中的死锁

InnoDB 的锁机制

共享锁 & 排它锁

InnoDB 实现了标准的行级锁，有两种类型：共享锁 S（shared locks）和排他锁 X（exclusive locks）

  • 共享锁：允许事务读取一行
  • 排他锁：允许事务更新或删除一行

如果事务 T1 对数据行 r 持有 S 锁，则如下处理来自某个不同事务 T2 的对数据行 r 的锁的请求：

  • 立即对 T2 签发 S 锁；此时 T1 和 T2 都持有对 r 的 S 锁
  • 不会立即对 T2 签发 X 锁

如果 T1 持有的是 X 锁，那么 T2 对任意类型的锁都不会被立即签发，相反 T2 只能等待 T1 释放它针对 r 的锁

意向锁

InnoDB 支持多粒度锁（multiple granularity locking），允许行锁和表锁共存；举一个例子，SQL LOCK TABLE ... WRITE 会获取一个排他锁（X 锁）对于该指定的表；为了使多粒度级别的锁定变得实用，InnoDB 使用了意向锁，意向锁是表级锁，用于指示事务稍后需要表中某行使用哪种类型的锁（共享或排他），意向锁有两种类型：

  • 意向共享锁（IS）表示事务意图针对表中的数据行设置共享锁
  • 意向排他锁（IX）表示事务意图针对表中的数据行设置排他锁

例如，SELECT ... LOCK IN SHARE MODE 设置 IS 锁，而 SELECT ... FOR UPDATE 设置排他锁

意向锁定协议如下：

  • 在事务可以获取表中某行的共享锁之前，它必须首先获取表上的 IS 锁或更强的锁
  • 在事务可以获取表中某行的排他锁之前，它必须首先获取表上的 IX 锁

表级锁类型兼容性总结如下表所示

X IX S IS
X Conflict Conflict Conflict Conflict
IX Conflict Compatible Conflict Compatible
S Conflict Conflict Compatible Compatible
IS Conflict Compatible Compatible Compatible

如果请求事务与现有锁兼容，则会将锁授予该事务，但如果它与现有锁冲突，则不会授予该事务；事务将会等待直到导致冲突的锁被释放；如果锁请求与现有锁冲突，并且由于会导致死锁而无法授予，则会发生错误

意向锁不会阻塞除全表请求之外的任何内容（例如，LOCK TABLES…WRITE），意向锁的主要目的是显示有人正在锁定一行，或者要锁定表中的一行（即表达意图）

在 SHOW ENGINE INNODB STATUS 和 InnoDB monitor 输出中，意向锁的事务数据与以下内容类似：

1
TABLE LOCK table `test`.`t` trx id 10080 lock mode IX

记录锁

记录锁用于锁住一条索引记录，举一个例子 SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE; 获取的锁将防止任何其他的事务对 t.c1 = 10 的行记录进行插入、更新、删除

记录锁总是锁定索引记录，即使是没有索引的表，InnoDB 会创建隐式的聚集索引（hidden clustered index）然后使用该索引来进行记录上锁

在 SHOW ENGINE INNODB STATUS 和 InnoDB monitor 输出中，记录锁的事务数据与以下内容类似：

1
2
3
4
5
6
RECORD LOCKS space id 58 page no 3 n bits 72 index `PRIMARY` of table `test`.`t`
trx id 10078 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 8000000a; asc     ;;
 1: len 6; hex 00000000274f; asc     'O;;
 2: len 7; hex b60000019d0110; asc        ;;

间隙锁

一个间隙锁锁住的是索引记录之间的范围，或者锁定第一个索引记录之前或者最后一个索引记录之后的范围

举个例子，SELECT c1 FROM t1 WHERE c1 BETWEEN 10 and 20 FOR UPDATE;，将会防止其他事务将 value 为 15 的数据插入 t.c1，无论这一列是否已经存在该值，因为该范围中的所有值都被锁定了

间隙可能跨越单个索引值、多个索引值，甚至为空

间隙锁是性能和并发之间权衡的一部分，用于某些事务隔离级别中

间隙锁与唯一索引

间隙锁在对于使用唯一索引查询一行数据的情况下不会加锁（不包括组合唯一索引，只在查询条件中覆盖了部分字段的情况）

举个例子，如果字段 id 是一个唯一索引，以下语句仅对 id 为 100 的行使用索引记录锁，并且其他会话是否在 id = 100 前面的间隙中插入行并不重要：SELECT * FROM child WHERE id = 100;

如果 id 没有索引或者不是唯一索引，则该语句会锁住前面的间隙

间隙锁不互斥

这里同时需要注意，不同的事务可能同时持有互相冲突的间隙锁

举个例子，事务 A 可以持有一个共享间隙锁（gap S-lock），而事务 B 持有一个相同范围的排他间隙锁（gap X-lock），允许间隙锁冲突的原因是如果从索引中清除记录，则必须合并不同事务在记录上持有的间隙锁

InnoDB 中的间隙锁的功能是 “纯粹禁止”（purely inhibitive），这意味着它们的唯一目的是防止其他事务插入到锁定的范围中，所以间隙锁可以共存；一个事务获取的间隙锁定不会阻止另一个事务对同一间隙获取间隙锁定；共享和排他间隙锁之间没有区别，它们彼此不冲突，并且执行相同的功能

禁用间隙锁

间隙锁定可以明确禁用，如果将事务隔离级别更改为 READ COMMITTED 或启用 innodb_locks_unsaf_for_binlog 系统变量（现已废弃）即可；在这种情况下，间隙锁对于查询和索引扫描是禁用的，仅用于外键约束检查和唯一键检查

使用 READ COMMITTED 隔离级别或启用 innodb_locks_unsaf_for_binlog 也会产生其他影响

  • MySQL 评估 WHERE 条件后，将释放不匹配行的记录锁
  • 对于 UPDATE 语句，InnoDB 会进行 semi-consistent 读取，从而将最新提交的版本返回给 MySQL，以便MySQL 可以确定该行是否符合 UPDATE 的 WHERE 条件

临键锁

临键锁是当前索引记录的记录锁和该记录之前范围的间隙锁的组合

InnoDB 的行级锁实现当进行查询或者对索引进行扫描，是这样来进行加锁操作：首先对于扫描的索引行设置共享或者排他锁，因此行级锁实际上是索引记录锁；一个锁住索引行的临键锁同样会影响索引记录之前的区间

如果一个会话对索引中的记录 R 具有共享或排他锁定，则另一个会话将无法在索引顺序中 R 之前的间隙中插入新的索引记录

假设一个索引包含值 10、11、13 和 20，此索引可能的下一个键锁定涵盖以下区间，其中圆括号表示排除区间端点，方括号表示包含端点（开区间闭区间）：

1
2
3
4
5
(negative infinity, 10]
(10, 11]
(11, 13]
(13, 20]
(20, positive infinity)

对于最后一个间隔，临键锁锁定索引中最大值上方的间隙，以及值高于索引中实际任何值的 “上确界” 伪记录（“supremum” pseudo-record），上确界并不是真实的索引记录，所以实际上，临键锁只锁定了最大索引值后面的间隙

InnoDB 默认的隔离级别为 REPEATABLE READ，在这种情况下 InnoDB 会在查询或者扫描索引时使用临键锁来防止出现幻影行

临键锁锁的事务数据在 SHOW ENGINE INNODB STATUS 和 InnoDB monitor 输出中显示如下：

1
2
3
4
5
6
7
8
9
RECORD LOCKS space id 58 page no 3 n bits 72 index `PRIMARY` of table `test`.`t`
trx id 10080 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 8000000a; asc     ;;
 1: len 6; hex 00000000274f; asc     'O;;
 2: len 7; hex b60000019d0110; asc        ;;

插入意向锁

插入意向锁是在插入行之前由 INSERT 操作设置的一种间隙锁

该锁以这样一种方式发出插入意图的信号，即如果插入到同一索引间隙的多个事务不在间隙内的同一位置插入，则不需要等待彼此；举个例子，存在索引行 value 为 4 和 7，独立的事务打算查询 value 5 和 6，每个事务都会在获得插入行上的排他锁定之前用插入意向锁定来锁定 4 和 7 之间的间隙，但是并不会互相阻塞，因为插入的行是不冲突的

下面的示例演示了一个事务，该事务在获得插入记录的独占锁之前使用插入意向锁，该示例涉及两个客户端，A 和 B

  1. 客户端 A 创建一个包含两个索引记录（90 和 102）的表，然后启动一个事务，对 ID 大于 100 的索引记录进行排他锁定，排他性锁定包括记录 102 之前的间隙锁定： 

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    mysql> CREATE TABLE child (id int(11) NOT NULL, PRIMARY KEY(id)) ENGINE=InnoDB;
    mysql> INSERT INTO child (id) values (90),(102);

    mysql> START TRANSACTION;
    mysql> SELECT * FROM child WHERE id > 100 FOR UPDATE;
    +-----+
    | id  |
    +-----+
    | 102 |
    +-----+

  2. 客户端 B 开启一个事务，将一条记录插入到间隙中，事务在等待获得排他锁的同时获得插入意向锁 

    1
    2
    mysql> START TRANSACTION;
    mysql> INSERT INTO child (id) VALUES (101);

插入意向锁的事务数据在 SHOW ENGINE INNODB STATUS 和 InnoDB monitor 输出中显示如下：

1
2
3
4
5
6
RECORD LOCKS space id 31 page no 3 n bits 72 index `PRIMARY` of table `test`.`child`
trx id 8731 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 80000066; asc    f;;
 1: len 6; hex 000000002215; asc     " ;;
 2: len 7; hex 9000000172011c; asc     r  ;;...

自增锁

自增锁是特殊的表级锁，当事务在表中插入 AUTO_INCREMENT 列时将会获取

在最简单的情况下，如果一个事务正在向表中插入值，那么任何其他事务都必须等待自己向该表中插入，以便第一个事务插入的行接收连续的主键值

innodb_autoinc_lock_mode 变量控制用于自动增量锁定的算法，它允许您选择如何在可预测的自动增量值序列和插入操作的最大并发性之间进行权衡

空间索引的谓词锁

InnoDB 支持以空间数据（spatial data）为列的 SPATIAL 索引

为了处理涉及 SPATIAL 索引的操作的锁定，临键锁不能很好地支持 REPEATABLE READ 或 SERIALIZABLE 事务隔离级别，因为多维数据中没有绝对的排序概念，因此不清楚哪个是 “下一个” 键

为了支持具有 SPATIAL 索引的表的隔离级别，InnoDB 使用了谓词锁（predicate locks）；SPATIAL 索引包含最小边界矩形（MBR）值，因此 InnoDB 通过在用于查询的 MBR 值上设置谓词锁来强制对索引进行一致读取，此时其他事务处理无法插入或修改与查询条件匹配的行

InnoDB 的事务模型

InnoDB 的事务模型旨在将多版本特性（multi-versioning）和传统的两阶段锁定相结合

InnoDB 在行级别执行锁定，并以 Oracle 的风格默认作为非锁定一致读（nonlocking consistent reads）来运行查询

InnoDB 中的锁信息被有效地存储在空间中，因此不需要升级锁，通常允许几个用户锁定 InnoDB 表中的每一行，或行的任何随机子集，并不会导致 InnoDB 内存耗尽

隔离级别

隔离级别是数据库处理的基础，

InnoDB 提供了 SQL:1992 标准 所描述的四种隔离级别：

  • READ UNCOMMITTED
  • READ COMMITTED
  • REPEATABLE READ (DEFAULT)
  • SERIALIZABLE

用户可以使用 SET TRANSACTION 语句来更改单个会话或所有后续连接的隔离级别，在命令行或配置文件使用 --trsaction 配置可以设置服务器的隔离级别

InnoDB 支持不同的隔离事务级别使用不同的锁策略，可以使用默认的 RR 级别强调高度一致性，用于进行对于 ACID 要求较高的关键数据操作；在批量情况下，也可以使用 RC 或者 RU 级别来放宽一致性原则，在这种情况下精确的一致性和重复结果不如最小化上锁开销那么重要；S 级别会比 RR 更加严格，它经常被用于特殊的场景，例如 XA 事务或者解决并发和死锁问题

REAPEATABLE READ

这是 InnoDB 的默认隔离级别；同一事务中都读取由第一次读取建立的快照版本，这意味着如果在同一事务中发出多个非锁定的 SELECT 语句，那么这些 SELECT 操作查询到的数据集彼此是一致的；这被称为 “一致的非锁定读取”（Consistent Nonlocking Reads）

对于锁定读取（SELECT ... FOR UPDATE 或者 LOCK IN SHARE MODE）、UPDATE、DELETE 语句，会根据索引类型和查询范围进行上锁：

  • 对于具有唯一搜索条件的唯一索引，InnoDB 只锁定找到的索引记录，而不锁定之前的间隙
  • 对于其他搜索条件，InnoDB 会锁定扫描的索引范围，使用间隙锁或临键锁来阻止其他会话插入到范围所覆盖的间隙中

READ COMMITTED

即使在同一事务中，每次一致读取都会设置新的快照

对于锁定读取（SELECT ... FOR UPDATE 或者 LOCK IN SHARE MODE）、UPDATE、DELETE 语句，InnoDB 只锁定索引记录，而不锁定它们的间隙，因此允许在锁定记录附近插入新记录；间隙锁定仅用于外键约束检查和重复 key 检查

由于不锁定间隙（间隙锁被禁用），所以可能会出现幻读问题，因为其他会话可以将新行插入间隙中

RC 隔离级别仅支持基于行的二进制日志记录；如果将 RC 与 binlog_format=MIXED 一起使用，则服务器会自动使用基于行的日志记录

使用 RC 隔离级别可能存在的影响：

  • 对于 UPDATE 或 DELETE 语句，InnoDB 只为更新或删除的行保留锁记录，MySQL 会在评估 WHERE 条件后释放不匹配行的记录锁，这大大降低了死锁的概率，但死锁仍然可能发生
  • 对于 UPDATE 语句，如果一行已经被锁定，InnoDB 会执行 “半一致”（semi-consistent） 读取，将最新提交的版本返回给 MySQL，以便 MySQL 可以确定该行是否符合 UPDATE 的 WHERE 条件，如果行匹配（必须更新）MySQL 将再次读取该行，这一次 InnoDB 要么将其锁定，要么等待锁定

READ UNCOMMITTED

SELECT 操作会以无锁方式执行，但是可以使用行的可能的早期版本，所以使用这种隔离级别可能导致读取是不一致的，被称为脏读

SERIALIZABLE

该级别类似 RR，但是如果禁用了自动提交 InnoDB 隐式地将 SELECT 转换为了 SELECT ... LOCK IN SHARE MODE；如果自动提交是启用状态，SELECT 是它自己的事务

因此已知它是只读的，一致（非锁定）读取执行串行化不会阻塞其他的事务（若要在其他事务已修改所选行的情况下进行锁定读取，需要禁用自动提交）

自动提交、提交和回滚

InnoDB 中所有的用户操作都发生在事务中

如果启用了自动提交模式，则每条 SQL 语句都会自己形成一个事务，默认情况下 MySQL 会为每个新连接的 Session 开启自动提交，因此 MySQL 会在每个执行成功的 SQL 语句后自动提交事务；如果执行发生了错误，那么提交或者回滚取决于错误类型

启用了自动提交的会话也可以执行多语句事务，方法是以显式 START transaction 或 BEGIN 语句开始，然后以 COMMIT 或 ROLLBACK 语句结束

如果在 SET autocommit=0 的会话中禁用了自动提交模式，则该会话始终打开一个事务；COMMIT 或 ROLLBACK 语句会结束当前事务，并启动新事务

如果事务禁用了自动提交，并且结束时也没有针对该事务明确的 COMMIT 操作，那么 MySQL 会回滚该事务

一致非锁定读

一致读取意味着 InnoDB 使用多版本控制在某个时间点（timepoint）向查询显示数据库的快照，查询会看到在此时间点之前提交的事务所做的修改，而不会看到以后或未提交的事务进行的修改

您可以通过提交事务，然后使用一致快照执行另一个 SELECT 或开启一个新事务来推进时间点

不同隔离级别的快照策略：

  • REPEATABLE READ：第一次读取设置快照；提交事务后的读取则会使用最新数据
  • READ COMMITTED：事务中的每个一致读取都会设置并读取自己的新快照

举一个例子说明一致性读

事务 A 只有在 B 提交了事务，并且 A 也提交了事务时才看到由 B 插入的行

事务 A 事务 B
开启事务 开启事务
SELECT * FROM t;（empty set）
INSERT INTO tVALUES (1, 2);
SELECT * FROM t;（empty set）
COMMIT;
SELECT * FROM t;（empty set）
COMMIT;
SELECT * FROM t;（1,2）

如果您想查看数据库的“最新”状态，请使用 READ COMMITTED 隔离级别或使用锁定读

需要注意 DML 对数据可见的影响

数据库状态的快照应用于事务中的 SELECT 语句，而不一定应用于 DML 语句

如果插入或修改某些行，然后提交该事务，则从另一个并发的 REPEATABLE READ 事务发出的 DELETE 或 UPDATE 语句可能会影响那些刚刚提交的行，即使会话无法查询到这些数据（因为快照）

如果事务确实更新或删除了由其他事务提交的行，则这些更改对当前事务可见

1
2
3
4
5
6
7
8
9
10
11
SELECT COUNT(c1) FROM t1 WHERE c1 = 'xyz';
-- 0 行
DELETE FROM t1 WHERE c1 = 'xyz';
-- 可能会删除最近由其他事务提交的匹配到的行

SELECT COUNT(c2) FROM t1 WHERE c2 = 'abc';
-- 0 行
UPDATE t1 SET c2 = 'cba' WHERE c2 = 'abc';
-- 如果另一个事务刚刚提交了 abc 值的 10 行
SELECT COUNT(c2) FROM t1 WHERE c2 = 'cba';
-- 10 行；这个事务现在可以看到它刚刚更新的行

锁定读

如果查询数据后希望在同一事务中插入或更新相关数据，则常规 SELECT 语句无法提供足够的保护，其他事务可以更新或删除您刚才查询的同一行

InnoDB 引擎提供了两种锁定读的方式来提供额外的安全保证：

  • SELECT ... LOCK IN SHARE MODE 在读取到的所有行上设置共享锁（shared mode lock）其他事务可以进行读取，但是在事务提交之前都不能进行修改，如果查询过程中这些行中的任意行被另一个尚未提交的事务修改，则查询将等待该事务结束，然后使用更新后的值
  • SELECT ... FOR UPDATE 对于查询到的索引记录，锁定行和任何关联的索引条目，就像为这些行进行 UPDATE 语句一样 对其他事务被阻止更新这些行、进行查询、在共享模式下锁定或读取特定事务隔离级别的数据 一致读（快照读）将忽略在读取视图中存在的记录上设置的任何锁（记录的旧版本无法锁定，因为它们是通过在记录的内存副本上应用 undo log 来重建的）

这些子句在处理树结构或图结构数据时非常有用，无论是在单个表中还是在多个表中拆分；可以从一个位置遍历边或树枝到另一个位置，同时保留返回并更改任何这些“指针”值的权利

当事务被提交或回滚时，由 LOCK IN SHARE MODE 和 FOR UPDATE 查询设置的所有锁都会被释放

FOR UPDATE 不会传递

外部语句中的锁定读取子句不会锁定嵌套子查询中表的行，除非在子查询中也指定了锁定读取子句

例如，以下语句不会锁定表 t2 中的行

SELECT * FROM t1 WHERE c1 = (SELECT c1 FROM t2) FOR UPDATE;

要锁定表 t2 中的行，请向子查询添加一个锁定读取子句

SELECT * FROM t1 WHERE c1 = (SELECT c1 FROM t2 FOR UPDATE) FOR UPDATE;

locking read

A SELECT statement that also performs a locking operation on an InnoDB table. Either SELECT ... FOR UPDATE or SELECT ... LOCK IN SHARE MODE. It has the potential to produce a deadlock, depending on the isolation level of the transaction. The opposite of a non-locking read*. Not allowed for global tables in a read-only transaction.

SELECT ... FOR SHARE replaces SELECT ... LOCK IN SHARE MODE in MySQL 8.0.1, but LOCK IN SHARE MODE remains available for backward compatibility.

See Section 14.7.2.4, “Locking Reads”.

See Also deadlock, isolation level, locking, non-locking read, read-only transaction.

InnoDB 中不同语句设置的锁

如果在查询中使用了二级索引，并且要设置的索引记录锁（record locks）是排他的，那么 InnoDB 还会检索相应的聚集索引记录并对其设置锁

如果你的语句没有合适的索引，MySQL 必须扫描整个表，该表的每一行都会被锁住，将会阻止其他用户对该表的所有插入，所以创建良好的索引非常重要，这样查询操作就不会扫描超出需求范围的记录行

InnoDB 设置特定类型的锁，如下所示：

  • SELECT ... FROM 一致性读，读取数据库的快照不会上锁，除非隔离级别为 SERIALIZABLE；对于 SERIALIZABLE 级别，该查询将会在遇到的索引记录上设置共享的临键锁；但是对于使用唯一索引锁定行来查询唯一行的语句，只需要索引记录锁

  • SELECT ... FOR UPDATE 或 SELECT ... LOCK IN SHARE MODE 为扫描的行获取锁，并可能为不符合结果集中包含条件的行释放锁（例如，如果它们不符合 WHERE 子句中给定的条件 但是在某些情况下可能不会立即解锁，因为结果行与其原始源之间的关系在查询执行过程中丢失，例如在 UNION 中，在评估表中扫描（和锁定）的行是否符合结果集之前，可能会将这些行插入到临时表中；在这种情况下，临时表中的行与原始表中的列之间的关系将丢失，而后的行直到查询执行结束才被解锁

  • SELECT ... LOCK IN SHARE MODE 在查询遇到的所有索引记录上设置共享的临键锁，但是对于使用唯一索引锁定行来查询唯一行的语句，只需要索引记录锁

  • SELECT ... FOR UPDATE 在查询遇到的所有索引记录上设置排他的临键锁，但是对于使用唯一索引锁定行来查询唯一行的语句，只需要索引记录锁

  • UPDATE ... WHERE ... 在遇到的每一行索引记录上设置排他的临键锁；但是对于使用唯一索引锁定行来查询唯一行的语句，只需要索引记录锁

  • UPDATE 修改聚集索引记录时，会对受影响的辅助索引记录使用隐式锁，在查询新的辅助索引记录之前执行重复检测时、或者在插入新的辅助索引记录时，更新操作还会对受影响的辅助索引记录获取共享锁

  • DELETE FROM ... WHERE ... 对遇到的索引记录设置排他临键锁；但是对于使用唯一索引锁定行来查询唯一行的语句，只需要索引记录锁

  • INSERT 对插入的行设置排他记录锁，而不是临键锁（即没有间隙锁），并且不会阻止其他会话插入插入行之前的间隙中

    在插入行之前，设置类型为间隙锁的插入意向锁；该锁相当于发出插入意向的信号，即如果插入到同一索引间隙中的多个事务并不在间隙内的同一位置插入，则它们不需要彼此等待；假设存在值为 4 和 7 的索引记录，当尝试插入值为 5 和 6 的单独事务，在获得排他记录锁之前，每个事务都使用插入意向锁来锁定 4 和 7 之间的间隙，但不会互相阻止，因为插入行不冲突

    如果出现重复键错误，则会对重复索引记录设置共享锁，如果有多个会话试图插入同一行，而另一个会话已经具有排他锁，则这种共享锁的使用可能会导致死锁，如果另一个会话删除该行，则可能会发生如下情况；假设一个 InnoDB 表 t1 具有以下结构：

    1
    CREATE TABLE t1 (i INT, PRIMARY KEY (i)) ENGINE = InnoDB;

    现在假设三个事务按顺序执行以下操作：

    1. 事务 1 

      1
      2
      START TRANSACTION;
      INSERT INTO t1 VALUES(1);

    2. 事务 2 

      1
      2
      START TRANSACTION;
      INSERT INTO t1 VALUES(1);

    3. 事务 3 

      1
      2
      START TRANSACTION;
      INSERT INTO t1 VALUES(1);

    4. 事务 1 

      1
      ROLLBACK;

    流程分析：事务 1 的第一个操作会获取 1 记录行的排他锁，而后事务 2 和 事务 3 也会插入 1 导致重复键错误，各自获取针对 1 的共享锁，事务 1 进行回滚，此时事务 2 和事务 3 出现死锁：由于另一方持有共享锁，因此两方都无法获取该行的排他锁

    如果表中已经包含键值为 1 的行，并且三个事务按顺序执行以下操作，则会出现类似的情况：

    1. 事务 1 

      1
      2
      START TRANSACTION;
      DELETE FROM t1 WHERE i = 1;

    2. 事务 2 

      1
      2
      START TRANSACTION;
      INSERT INTO t1 VALUES(1);

    3. 事务 3 

      1
      2
      START TRANSACTION;
      INSERT INTO t1 VALUES(1);

    4. 事务 1 

      1
      COMMIT;

    流程分析：事务 1 的第一个操作会获取 1 记录行的排他锁，而后事务 2 和 事务 3 也会插入 1 导致重复键错误，各自获取针对 1 的共享锁，当事务 1 提交时，它会释放其在行上的排他锁，并授予事务 2 和 3 的排队共享锁请求；此事务 2和事务 3 出现死锁：由于另一方持有共享锁，因此两方都无法获取该行的独占锁

  • INSERT ... ON DUPLICATE KEY UPDATE 不同于简单的 INSERT 操作，当发生重复键错误时，会在需要更新的行上设置排他锁，而不是共享锁；对重复的主键值使用排他记录锁，对于重复的唯一索引值，将使用排他临键锁

  • REPLACE 如果唯一键没有冲突则和一个 INSERT 操作一致，否则会在需要 replace 的行上加排他临键锁

  • INSERT INTO T SELECT ... FROM S WHERE ...

幻影行

之所以称呼为 “幻影读问题” 是因为一个事务在不同的时间进行相同的查询请求返回的结果集不同；举个例子，如果一个 SELECT 操作执行了两次，但是第一次查询返回了 1 行数据，第二次查询没有返回数据，那么这行数据就被称为 “幻影行”（phantom row）

假设子表的 id 列上有一个索引，并且希望读取并锁定表中标识符值大于 100 的所有行，以便稍后更新所选行中的某些列：

1
SELECT * FROM child WHERE id > 100 FOR UPDATE;

该查询会从 id 大于 100 的第一条记录开始扫描索引；假设这个表存在一些数据，id 的值为 90 和 102，如果在扫描范围内的索引记录上设置的锁不锁定在间隙（在这种情况下是 90 和 102 之间的间隙）中进行的插入，那么另一个事务就可以查询一个新行例如 id 为 101；如果随后同一个事务内执行了相同的 SELECT 操作，将会看到新插入的数据 101（幻影）也在返回结果中，这样其实违反了事务的隔离原则

为了防止幻影，InnoDB 使用了一种被称为 next-key locking 的算法，即结合了记录锁和间隙锁的临键锁；当查询进行扫描索引时，InnoDB 提供了这样的行级锁，将会对需要的行设置共享或者排他锁

因此行级锁实际上是索引记录锁，此外索引记录上的下一个键锁定也会影响索引记录之前的间隔，也就是说，下一个键锁是索引记录锁加上索引记录之前间隙上的间隙，如果一个会话对索引中的记录 R 持有共享或独占锁，则另一个会话无法在索引顺序中 R 之前的间隙中插入新的索引记录

当 InnoDB 扫描索引时，它还可以锁定索引中最后一条记录之后的间隙。正如前面的例子所发生的那样：为了防止在 id 大于 100 的表中插入任何内容，InnoDB 设置的锁包括 id 值 102 后面的间隙上的锁

可以使用临键锁在应用程序中实现唯一性检查：如果在共享模式下读取数据，但没有看到要插入的行的重复项，那么您可以安全地插入行，并知道在读取期间在行的后续行上设置的临键锁可以防止其他事务同时插入重复项，临键锁可以锁住表中不存在的数据

间隙锁可以被禁用，但这可能会导致幻读问题的出现

InnoDB 中的死锁

死锁示例

以下示例说明了当锁定请求导致死锁时，错误是如何发生的，该示例涉及两个客户端，A 和 B

  1. 首先，客户端 A 创建一个包含一行的表，然后开始一个事务，在事务中 A 通过在共享模式下选择行来获得该行的 S 锁： 

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    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 LOCK IN SHARE MODE;
    +------+
    | i    |
    +------+
    |    1 |
    +------+

  2. 下一步，客户端 B 开启事务，尝试从表中删除该行 

    1
    2
    3
    4
    mysql> START TRANSACTION;
    Query OK, 0 rows affected (0.00 sec)

    mysql> DELETE FROM t WHERE i = 1;

  3. 删除操作需要 X 锁，现状是无法授予该锁，因为它与客户端 A 持有的 S 锁互斥，因此该请求会出现在行和客户端 B 块的锁请求队列中

  4. 最后客户端 A 也试图删除该行数据 

    1
    mysql> DELETE FROM t WHERE i = 1;

死锁之所以会出现是因为客户端 A 需要 X 锁来删除该行，不过该锁不会被签发因为客户端 B 的一个请求已经已经获取了 X 锁并且在等待客户端 A 释放它的 S 锁，也无法将 A 持有的 S 锁升级为 X 锁，因为此时 B 已经持有了 X 锁，所以 InnoDB 为其中一个客户端返回错误并释放其锁

客户端返回此错误：

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

此时就可以将锁签发给另一个客户端，让它从表中删除该行

死锁检测

当启用死锁检测（默认启用）时，InnoDB 会自动检测事务死锁，并回滚一个或多个事务以打破死锁；InnoDB 试图选择要回滚的小事务，其中事务的大小由插入、更新或删除的行数决定

如果 InnoDB_table_locks=1（默认值）和 autocommit=0，则 InnoDB 可以感知表锁，并且引擎上层的 MySQL 层可以感知行级锁；否则当涉及 MySQL lock TABLES 语句设置的表锁或 InnoDB 以外的存储引擎设置的锁时，InnoDB 无法检测到死锁；通过设置 innodb_lock_wait_timeout 系统变量的值来解决这些情况

在高并发系统上，当多个线程等待同一个锁时，死锁检测可能会导致速度减慢；有时当死锁发生时，禁用死锁检测并依靠 innodb_lock_wait_timeout 设置进行事务回滚可能会更有效；可以使用 innodb_Deadlock_detect 变量禁用死锁检测

如何减少和解决死锁

死锁是事务数据库中的一个经典问题，但除非它们非常频繁以至于根本无法成功执行某些事务，否则它们并不危险

通常必须编写逻辑用于在事务因死锁而回滚时，可以重新执行事务

InnoDB 使用自动行级锁定（automatic row-level locking），所以即使在只插入或删除一行的事务的情况下，也可能出现死锁；这是因为这些操作并不是真正的“原子”操作；它们会自动对插入或删除的行的（可能有几个）索引记录设置锁

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

  • 随时使用 SHOW ENGINE INNODB STATUS 指令以确定最近死锁的原因；这可以帮助调整应用程序以避免死锁

  • 如果频繁的死锁警告引起问题，请通过启用 innodb_print_all_deadlocks 变量来收集更广泛的调试信息；MySQL 错误日志中记录了每个死锁的信息，而不仅仅是最新的死锁；调试完成后禁用此选项

  • 如果事务由于死锁而失败，请始终做好重新执行事务的准备；死锁并不危险，只需要再试一次

  • 保持事务的规模小且持续时间短，以减少它们发生冲突的可能性；即避免大事务

  • 在进行一组相关更改后立即提交事务，以减少它们发生冲突的可能性；特别是，不要让交互式 MySQL 会话与未提交的事务长时间开启

  • 如果使用锁定读（SELECT…FOR UPDATE 或 SELECT…LOCK IN SHARE MODE），请尝试使用较低的隔离级别，例如 READ COMMITTED

  • 当修改事务中的多个表或同一表中的不同行集时，每次都要以一致的顺序执行这些操作，可以让事务的执行形成定义良好的队列，从而避免死锁；即应用中的按照表顺序、字段值顺序排序

  • 精心设置索引，避免过多索引导致的死锁；使用 EXPLAIN SELECT 来确定 MySQL 服务器认为哪些索引最适合查询

  • 不使用锁；如果业务可以允许查询从旧快照返回数据，不要使用 FOR UPDATE 或 LOCK IN SHARE MODE 子句，并且推荐使用 READ COMMITTED 隔离级别，因为同一事务中的每个一致读都从自己的新快照中读取

  • 如果上述建议没有帮助，可以使用表级锁序列化事务，将 LOCK TABLES 与事务性表（如 InnoDB 表）一起使用的正确方法是以 SET autocommit=0（而不是 START transaction）开头，后跟 LOCK TABLES，然后在显式提交事务之前不调用 UNLOCK TABLES 例如，如果您需要写入表 t1 并从表 t2 读取，则可以执行以下操作：

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

    表级锁防止对表进行并发更新，从而避免死锁，而代价是并发响应能力较低

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

参考

MySQL :: MySQL 5.7 Reference Manual :: 14.7 InnoDB Locking and Transaction Model