本文为 MySQL 8.0 官方文档 InnoDB Locking and Transaction Model 的翻译，所述的锁和事物模型针对于 InnoDB 存储引擎。

要实现大规模、繁忙或高可靠的数据库应用程序，或调优 MySQL 性能，了解 InnoDB 锁和 InnoDB 事务模型很重要。

InnoDB 和 ACID 模型

ACID 模型是一组数据库设计原则，强调对业务数据和任务关键型应用程序很重要的可靠性方面。MySQL 包括 InnoDB 存储引擎等组件，它们与 ACID 模型密切相关，因此数据不会损坏，结果不会因软件崩溃和硬件故障等异常情况而失真。当您依赖符合 ACID 的特性时，您不需要重新发明一致性检查和崩溃恢复机制的轮子。如果您有额外的软件保护措施、超可靠的硬件、或者可以容忍少量数据丢失或不一致的应用程序，您可以调整 MySQL 设置以换取一些 ACID 可靠性以获得更高的性能或吞吐量。

以下部分讨论 MySQL 特性，特别是 InnoDB 存储引擎，如何与 ACID 模型的类别交互：

  • A：atomicity - 原子性

    事务通常由多个语句组成。原子性保证每个事务都被视为一个“单元”，要么完全成功，要么完全失败：如果构成事务的任何语句未能完成，则整个事务失败，数据库数据保持不变。原子系统必须保证在每一种情况下的原子性，包括电源故障、错误和崩溃。保证原子性可以防止对数据库的更新仅部分发生，这可能会导致比完全拒绝整个系列更大的问题。

  • C：consistency - 一致性/正确性

    一致性确保事务只能将数据库从一种有效状态带到另一种有效状态，维护数据库不变性：根据所有定义的规则，写入数据库的任何数据都必须有效，包括约束、级联、触发器及其任意组合。这可以防止非法事务导致数据库损坏，但不能保证事务是正确的。

  • I：isolation - 隔离型

    事务通常是并发执行的（例如，多个事务同时读取和写入一个表）。隔离确保事务的并发执行使数据库处于与顺序执行事务时获得的状态相同。隔离是并发控制的主要目标；根据所使用的方法，不完整事物的影响甚至可能对其他事物不可见。

  • D：durability - 持久性

    持久性保证一旦事务被提交，即使在系统故障（例如，断电或崩溃）的情况下它也将保持提交。这通常意味着已完成的事物（或它们的影响）被记录在非易失性存储器中。

Atomicity

ACID 模型的原子性方面主要涉及 InnoDB 事务。相关的 MySQL 功能包括：

Consistency

ACID 模型的一致性方面主要涉及内部 InnoDB 处理，以保护数据免于崩溃。相关的 MySQL 功能包括：

Isolation

ACID 模型的隔离方面主要涉及 InnoDB 事务，特别是适用于每个事务的隔离级别。相关的 MySQL 功能包括：

Durability

ACID 模型的持久性方面涉及与您的特定硬件配置交互的 MySQL 软件功能。由于根据您的 CPU、网络和存储设备的能力存在多种可能性，这方面是最复杂的，提供具体的指导方针。 （这些指南可能采取“购买新硬件”的形式。）相关的 MySQL 功能包括：

  • InnoDB 双写缓冲区。请参阅第 15.6.4 节，“双写缓冲区”。
  • innodb_flush_log_at_trx_commit 变量。
  • sync_binlog 变量。
  • innodb_file_per_table 变量。
  • 存储设备中的写入缓冲区，例如磁盘驱动器、SSD 或 RAID 阵列。
  • 存储设备中的电池后备缓存。
  • 用于运行 MySQL 的操作系统，特别是它对 fsync() 系统调用的支持。
  • 不间断电源 (UPS) 保护运行 MySQL 服务器和存储 MySQL 数据的所有计算机服务器和存储设备的电源。
  • 您的备份策略，例如备份的频率和类型以及备份保留期。
  • 对于分布式或托管数据应用程序，MySQL 服务器硬件所在的数据中心的特定特征，以及数据中心之间的网络连接。

InnoDB 多版本控制

InnoDB 是一个多版本的存储引擎。 它保留有关已更改行的旧版本的信息以支持事务功能，例如并发和回滚。 该信息以称为回滚片段的数据结构存储在撤消表空间中。 请参阅第 15.6.3.4 节，“撤消表空间”。 InnoDB 使用回滚片段中的信息来执行事务回滚所需的撤消（undo）操作。 它还使用这些信息来构建行的早期版本以实现一致性读取。

在内部，InnoDB 为存储在数据库中的每一行添加三个字段：

  • 一个 6 字节的 DB_TRX_ID 字段指示插入或更新行的最后一个事务的事务标识符。此外，删除在内部被视为更新，其中设置了行中的特殊位（bit）以将其标记为已删除。
  • 一个 7 字节的 DB_ROLL_PTR 字段称为回滚指针（roll pointer）。回滚指针指向写入回滚片段的撤消日志（undo log）记录。如果该行被更新，撤消日志记录包含在更新前重建该行内容所需的信息。
  • 一个 6 字节的 DB_ROW_ID 字段包含一个行 ID，随着插入新行而单调增加。如果 InnoDB 自动生成聚集索引，则索引包含行 ID 值。否则，DB_ROW_ID 列不会出现在任何索引中。

回滚片段中的撤消日志分为插入和更新撤消日志。插入撤消日志仅在事务回滚时需要，并且可以在事务提交后立即丢弃。更新撤消日志也用于一致性读取，但只有当不存在 InnoDB 为其分配了快照的事务后，它们才能被丢弃，在一致读取中可能需要更新撤消日志中的信息来构建数据库行的早期版本，有关撤消日志（und log）的其他信息，请参阅第 15.6.6 节，“撤消日志”。

建议您定期提交事务，包括仅发出一致性读取的事务。否则，InnoDB 无法丢弃更新撤销日志中的数据，并且回滚段可能会变得太大，填满它所在的撤消表空间。有关管理撤消表空间的信息，请参阅第 15.6.3.4 节，“撤消表空间”。

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

在 InnoDB 多版本控制方案中，当您使用 SQL 语句删除某行时，不会立即从数据库中物理删除该行。InnoDB 仅在丢弃为了删除而写入的更新撤消日志记录时才物理删除相应的行及其索引记录。这种删除操作称为清除（purge），它非常快，通常与执行删除操作的 SQL 语句花费的时间顺序相同。

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

多版本和二级索引

InnoDB 多版本并发控制 (MVCC) 处理二级索引与聚簇索引不同。聚集索引中的记录立即更新，并且它们隐藏的系统列指向可以重建早期版本记录的撤消日志条目。与聚集索引记录不同，二级索引记录不包含隐藏的系统列，也不会立即更新。

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

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

但是，如果启用了 索引条件下推 (ICP)优化，并且 WHERE 可以仅使用索引中的字段评估部分条件，则 MySQL 服务器会将这部分WHERE条件下推到存储引擎。如果没有找到匹配的记录，则避免聚集索引查找。如果找到匹配的记录，即使是在删除标记的记录中，也会在 InnoDB聚集索引中查找该记录。

InnoDB 锁定

本节介绍 InnoDB 使用的锁类型。

Shared and Exclusive Locks

InnoDB 实现了标准的行级锁定，其中有两种类型的锁，共享 - Share (S) 锁和排它 - Exclusive (X) 锁。

  • 共享 (S) 锁允许持有锁的事务读取一行。
  • 排他 (X) 锁允许持有锁的事务更新或删除行。

如果事务 T1 在行 r 上持有共享 (S) 锁，那么来自某个不同事务 T2 的对行 r 的锁的请求将按如下方式处理：

  • 可以立即授予 T2 对 S 锁的请求。结果，T1 和 T2 都持有 r 的 S 锁。
  • T2 对 X 锁的请求不能立即被授予。

如果事务 T1 在行 r 上持有排他 (X) 锁，则不能立即授予来自某个不同事务 T2 的对 r 上任一类型锁的请求。相反，事务 T2 必须等待事务 T1 释放其对行 r 的锁。

Intention Locks

InnoDB 支持多粒度锁定，允许行锁和表锁共存。例如，诸如 LOCK TABLES ... WRITE 之类的语句在指定的表上使用排他锁（X 锁）。为了使多粒度级别的锁定起作用，InnoDB 使用意向锁（intention locks）。意向锁是表级锁，指示事务稍后需要对表中的行使用哪种类型的锁（共享锁或独占锁）。意向锁有两种类型：

例如，SELECT ... FOR SHARE 设置一个 IS 锁，而 SELECT ... FOR UPDATE 设置一个 IX 锁。

意图锁定协议如下：

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

表级锁类型和行级锁类型兼容性总结在以下矩阵中，由表可以看出意向锁之间不会冲突，但是意向锁在和行锁之间有不同程度的冲突：

XIXSIS
XConflictConflictConflictConflict
IXConflictCompatibleConflictCompatible
SConflictConflictCompatibleCompatible
ISConflictCompatibleCompatibleCompatible

如果请求事务与现有锁兼容，则向请求事务授予锁，但如果与现有锁冲突，则不会授予。事务一直等到冲突的现有锁被释放。如果锁定请求与现有锁定发生冲突并且由于死锁而无法授予，则会发生错误。

除了全表请求（例如，LOCK TABLES ... WRITE）之外，意向锁不会阻塞任何东西。意向锁的主要目的是表明有人正在锁定一行，或者打算锁定表中的一行。

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

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

Record Locks

记录锁是对索引记录的锁。例如，SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE; 阻止任何其他事务插入、更新或删除 t.c1 值为 10 的行。

记录锁总是锁定索引记录，即使一个表没有定义索引。对于这种情况，InnoDB 会创建一个隐藏的聚集索引并使用该索引进行记录锁定。请参阅第 15.6.2.1 节，“聚集索引和二级索引”。

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

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        ;;

Gap Locks

间隙锁是对索引记录之间的间隙的锁，或者是对第一个索引记录之前或最后一个索引记录之后的间隙的锁。例如，SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE; 防止其他事务将 15 的值插入到列 t.c1 中，无论该列中是否已经存在任何此类值，因为该范围内所有现有值之间的间隙已被锁定。

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

间隙锁是性能和并发性之间权衡的一部分，用于某些事务隔离级别。比如可序列化（Serializable）、可重复读（Repeatable Read）隔离级别。

使用唯一索引锁定行以搜索唯一行的语句不需要间隙锁定。 （这不包括搜索条件只包含多列唯一索引的部分列的情况；在这种情况下，确实会发生间隙锁定。）例如，如果 id 列具有唯一索引，则以下语句仅对 id 值为 100 的行使用索引记录锁，其他会话是否在前面的间隙中插入行无关紧要：

SELECT * FROM child WHERE id = 100;

如果 id 未编入索引或具有非唯一索引，则该语句会锁定前面的间隙。

这里还值得注意的是，不同的事务可以在间隙上持有冲突的锁。 例如，事务 A 可以在间隙上持有共享间隙锁（gap S-lock），而事务 B 在同一间隙上持有排他间隙锁（gap X-lock）。 允许冲突间隙锁的原因是：如果要从索引中清除记录，则必须合并不同事务在记录上持有的间隙锁。

间隙锁定在 InnoDB 中是“纯粹的抑制”，这意味着它们的唯一目的是防止其他事务插入间隙。间隙锁可以共存。一个事务采用的间隙锁不会阻止另一个事务在同一间隙上采用间隙锁。共享和排他间隙锁之间没有区别。它们彼此不冲突，并且执行相同的功能。

可以明确禁用间隙锁定。比如将事务隔离级别更改为读已提交 - READ COMMITTED 。在这种情况下，间隙锁定对搜索和索引扫描禁用，仅用于外键约束检查和重复键检查。

使用 READ COMMITTED 隔离级别还有其他影响 。在 MySQL 评估 WHERE 条件后释放不匹配行的记录锁。对于 UPDATE 语句，InnoDB 做一个“半一致 - semi-consistent”读，这样它返回最新提交版本到 MySQL 使 MySQL 能够确定该行是否匹配 UPDATE 的 WHERE 的条件。

Next-Key Locks

next-key 锁是索引记录上的记录锁和索引记录之前的间隙上的间隙锁的组合。

InnoDB 执行行级锁定的方式是，当它搜索或扫描表索引时，它会在遇到的索引记录上设置共享锁或排他锁。因此，行级锁实际上是索引记录锁。索引记录上的 next-key 锁也会影响该索引记录之前的“间隙”。也就是说，**next-key 锁是一个索引记录锁加上一个在索引记录之前的间隙上的间隙锁。**如果一个会话对索引中的记录 R 具有共享或排他锁，则另一个会话不能在索引顺序中紧靠 R 之前的间隙中插入新的索引记录。

假设一个索引包含值 10、11、13 和 20。该索引可能的 next-key 锁涵盖以下间隔，其中圆括号表示排除区间边界值，方括号表示包含边界值：

(negative infinity, 10]
(10, 11]
(11, 13]
(13, 20]
(20, positive infinity)

对于最后一个时间间隔，next-key lock 锁定索引中最大值之上的间隙，并且最后的“supremum”伪记录的值高于索引中的任何实际值。supremum 不是真正的索引记录，因此，实际上，这个 next-key 锁只锁定最大索引值之后的间隙。

默认情况下，InnoDB 的事务隔离级别为可重复读 - REPEATABLE READ。在这种情况下，InnoDB 使用 next-key 锁进行搜索和索引扫描，以防止幻像行（phantom rows），也就是防止幻读（请参阅第 15.7.4 节，“幻像行”）。

SELECT count(1) FROM books WHERE price < 100					/* 时间顺序：1，事务： T1 */
INSERT INTO books(name,price) VALUES ('深入理解Java虚拟机',90)	/* 时间顺序：2，事务： T2 */
SELECT count(1) FROM books WHERE price < 100					/* 时间顺序：3，事务： T1 */

InnoDB 在只读事务中可以完全避免幻读问题，譬如上面例子中事务 T1 只有查询语句，是一个只读事务，所以例子中幻读问题在 MySQL 中并不会出现。但在读写事务中，MySQL 仍然会出现幻读问题，譬如例子中事务 T1 如果在其他事务插入新书后，不是重新查询一次数量，而是要将所有小于 100 元的书改名，那就依然会受到新插入书籍的影响。

next-key 锁的事务数据在 SHOW ENGINE INNODB STATUS 和 InnoDB monitor 输出中显示类似于以下内容：

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 Intention Locks

插入意向锁是一种在行插入之前由 INSERT 操作设置的间隙锁。此锁表示插入意图，如果插入同一索引间隙的多个事务未插入间隙内的相同位置，则它们无需相互等待。假设存在值为 4 和 7 的索引记录。分别尝试插入值 5 和 6 的单独事务，在获得插入行的排他锁之前，每个事务都使用插入意图锁锁定 4 和 7 之间的间隙，但不会相互阻塞，因为行是不冲突的。

以下示例演示了在获取插入记录的排他锁之前采用插入意向锁的事务。该示例涉及两个客户端 A 和 B。

客户端 A 创建一个包含两条索引记录（90 和 102）的表，然后启动一个事务：对 ID 大于 100 的索引记录放置排他锁。

排他锁包括记录 102 之前的间隙锁：

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 |
+-----+

客户端 B 开始一个事务以在间隙中插入一条记录。事务在等待获得排他锁时使用插入意向锁。

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

插入意图锁的事务数据在 SHOW ENGINE INNODB STATUS 和 InnoDB monitor 输出中显示类似于以下内容：

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-INC Locks

AUTO-INC 锁是一种特殊的表级锁，由插入到具有 AUTO_INCREMENT 列的表中的事务获取。在最简单的情况下，如果一个事务正在向表中插入值，任何其他事务对该表的插入操作必须等待，以便从第一个事务插入的行接收连续的主键值。

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

有关更多信息，请参阅第 15.6.1.6 节，“InnoDB 中的 AUTO_INCREMENT 处理”。

Predicate Locks for Spatial Indexes

该锁为空间索引 - R Tree 索引类型的锁，由于我们很少使用 MySQL 存储空间数据以及空间运算，不做过多研究。

InnoDB 支持包含空间数据的列的空间索引（请参阅第 11.4.9 节，“优化空间分析”）。

为了处理涉及 SPATIAL 空间索引的操作的锁定，next-key 锁定不能很好地支持 REPEATABLE READ 或 SERIALIZABLE 事务隔离级别。

多维数据中没有绝对排序的概念，所以不清楚哪个是“下一个”键。

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

InnoDB 事物模型

InnoDB 事务模型旨在将多版本（multi-versioning）数据库的最佳属性与传统的两阶段锁定相结合。 InnoDB 在行级别执行锁定，默认情况下以 Oracle 的风格使用一致性非锁定读取执行查询。InnoDB 中的锁信息以节省空间的方式存储，因此不需要锁升级。通常，允许多个用户锁定 InnoDB 表中的每一行或行的任何随机子集，而不会导致 InnoDB 内存耗尽。

事物隔离级别

事务隔离是数据库处理的基础之一。隔离（Isolation）是缩写 ACID 中的 I；隔离级别是在多个事务同时进行更改和执行查询时微调结果的性能和可靠性、一致性和可再现性之间的平衡的设置。

InnoDB 提供 SQL:1992 标准描述的所有四种事务隔离级别：READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ 和 SERIALIZABLE。 InnoDB 的默认隔离级别是 REPEATABLE READ。

用户可以使用 SET TRANSACTION 语句更改单个会话或所有后续连接的隔离级别。要为所有连接设置服务器的默认隔离级别，在命令行或选项文件中使用 --transaction-isolation 选项。有关隔离级别和级别设置语法的详细信息，请参阅第 13.3.7 节，“SET TRANSACTION 语句”。

InnoDB 使用不同的锁定策略支持这里描述的每个事务隔离级别。对于对 ACID 合规性很重要的关键数据的操作，您可以使用默认的 REPEATABLE READ 级别强制执行高度的一致性。或者你可以通过 READ COMMITTED 甚至 READ UNCOMMITTED 来放松一致性规则，比如在诸如批量报告之类的情况下，精确的一致性和可重复的结果不如最小化锁定开销重要。SERIALIZABLE 执行比 REPEATABLE READ 遵守更严格的规则，主要用于特殊情况，例如 XA 事务以及解决并发和死锁问题。

下面的列表描述了 MySQL 如何支持不同的事务级别。该列表从最常用的级别到最不常用的级别。

  • REPEATABLE READ（可重复读）

    这是 InnoDB 的默认隔离级别。同一事务内的一致性读取使用由第一次读取建立的快照。这意味着如果您在同一个事务中发出多个普通（非锁定）SELECT 语句，这些 SELECT 语句彼此之间也是一致的。请参阅第 15.7.2.3 节，“一致的非锁定读取”。

    对于锁定读取（SELECT FOR UPDATE 或 FOR SHARE）、UPDATE 和 DELETE 语句，锁定取决于语句是使用具有唯一搜索条件的唯一索引还是范围类型搜索条件。

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

  • READ COMMITTED（读已提交）

    每个一致性的读取，即使在同一个事务中，也会设置和读取自己的新快照。

    对于锁定读取（SELECT with FOR UPDATE 或 FOR SHARE）、UPDATE 语句和 DELETE 语句，InnoDB 只锁定索引记录，而不是它们之前的间隙，因此允许在锁定记录旁边自由插入新记录。

    由于间隙锁定被禁用，可能会出现幻像行问题，也就是存在幻读问题，因为其他会话可以将新行插入间隙中。有关幻像行的信息，请参阅第 15.7.4 节，“幻像行”。

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

    使用 READ COMMITTED 有额外的效果：

    • 对于 UPDATE 或 DELETE 语句，只为满足条件的更新或删除数据行设置锁，不满足条件的行记录锁会在 MySQL 评估完 WHERE 条件后释放。
    • 对于 UPDATE 语句，如果一行已经被锁定，InnoDB 执行“半一致性”读取，将最新提交的版本返回给 MySQL，以便 MySQL 可以确定该行是否匹配 UPDATE 的 WHERE 条件。如果该行匹配（必须更新），MySQL 再次读取该行，这次 InnoDB 要么锁定它，要么等待锁定它。

    考虑以下示例：

    CREATE TABLE t (a INT NOT NULL, b INT) ENGINE = InnoDB;
INSERT INTO t VALUES (1,2),(2,3),(3,2),(4,3),(5,2);
COMMIT;

    在这种情况下，表没有索引字段，因此搜索和索引扫描使用隐藏的聚集索引进行记录锁定（请参阅第 15.6.2.1 节，“聚集索引和二级索引”）而不是索引列。

    假设一个会话使用以下语句执行 UPDATE：

    # Session A
START TRANSACTION;
UPDATE t SET b = 5 WHERE b = 3;

    还假设第二个会话通过在第一个会话之后执行这些语句来执行 UPDATE：

    # Session B
UPDATE t SET b = 4 WHERE b = 2;

    InnoDB 在执行每个 UPDATE 时，首先为每一行获取一个排他锁，然后再决定是否修改它。如果 InnoDB 不修改该行，它会释放锁。否则，InnoDB 会保留锁直到事务结束。这会影响事务处理如下。

    当使用默认的 REPEATABLE READ 隔离级别时，第一个 UPDATE 在它读取的每一行上获取一个 x 锁，并且不会释放任何行：

    x-lock(1,2); retain x-lock
x-lock(2,3); update(2,3) to (2,5); retain x-lock
x-lock(3,2); retain x-lock
x-lock(4,3); update(4,3) to (4,5); retain x-lock
x-lock(5,2); retain x-lock

    第二个 UPDATE 在尝试获取任何锁时立即阻塞（因为第一个更新已保留所有行上的锁），并且在第一个 UPDATE 提交或回滚之前不会继续：

    x-lock(1,2); block and wait for first UPDATE to commit or roll back

    如果改为使用 READ COMMITTED，则第一个 UPDATE 在它读取的每一行上获取一个 x 锁，并为它不修改的行释放那些锁：

    x-lock(1,2); unlock(1,2)
x-lock(2,3); update(2,3) to (2,5); retain x-lock
x-lock(3,2); unlock(3,2)
x-lock(4,3); update(4,3) to (4,5); retain x-lock
x-lock(5,2); unlock(5,2)

    对于第二次 UPDATE，InnoDB 执行“半一致性”读取，将读取的每一行的最新提交版本返回给 MySQL，以便 MySQL 可以确定该行是否与 UPDATE 的 WHERE 条件匹配：

    x-lock(1,2); update(1,2) to (1,4); retain x-lock
x-lock(2,3); unlock(2,3)
x-lock(3,2); update(3,2) to (3,4); retain x-lock
x-lock(4,3); unlock(4,3)
x-lock(5,2); update(5,2) to (5,4); retain x-lock

    但是，如果 WHERE 条件包含索引列，并且 InnoDB 使用该索引，则在获取和保留记录锁时仅考虑索引列。

    在以下示例中，第一个 UPDATE 在 b = 2 的每一行上获取并保留一个 x 锁。第二个 UPDATE 在尝试获取相同记录上的 x 锁时阻塞，因为它也使用在 b 列上定义的索引。

    CREATE TABLE t (a INT NOT NULL, b INT, c INT, INDEX (b)) ENGINE = InnoDB;
INSERT INTO t VALUES (1,2,3),(2,2,4);
COMMIT;

# Session A
START TRANSACTION;
UPDATE t SET b = 3 WHERE b = 2 AND c = 3;

# Session B
UPDATE t SET b = 4 WHERE b = 2 AND c = 4;

    READ COMMITTED 隔离级别可以在启动时设置或在运行时更改。在运行时，它可以为所有会话全局设置，也可以为每个会话单独设置。

  • READ UNCOMMITTED（读未提交）

    **SELECT 语句以非锁定方式执行，但可能会使用行的早期版本。**因此，使用此隔离级别，将产生读取不一致。这也称为脏读。除了读取，此隔离级别的工作方式类似于 READ COMMITTED。

  • SERIALIZABLE（序列化读）

    此级别类似于 REPEATABLE READ，但 InnoDB 在禁用自动提交情况下将所有普通 SELECT 语句隐式转换为 SELECT ... FOR SHARE。如果启用了自动提交，则 SELECT 语句为它自己的事务。 因此，它是只读的，如果作为一致（非锁定）读取执行并且不需要阻塞其他事务，则可以序列化。（如果其他事务修改了所选行，要强制普通 SELECT 阻塞，请禁用自动提交。）

自动提交, 手动提交, 和回滚

在 InnoDB 中，所有用户活动都发生在一个事务中。如果启用了自动提交（autocommit）模式，则每个 SQL 语句都会单独形成一个事务。默认情况下，MySQL 为每个启用自动提交的新连接启动会话，因此，如果该语句没有返回错误，则 MySQL 在每个 SQL 语句之后执行一次提交。如果语句返回错误，则提交或回滚行为取决于错误。请参阅第 15.21.5 节，“InnoDB 错误处理”。

**启用了自动提交的会话可以通过显式的 START TRANSACTION 或 BEGIN 语句启动它并以 COMMIT 或 ROLLBACK 语句结束它来执行多语句事务。**请参阅第 13.3.1 节，“START TRANSACTION、COMMIT 和 ROLLBACK 语句”。

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

如果禁用自动提交的会话在没有明确提交最终事务的情况下结束，MySQL 将回滚该事务。

有些语句隐式地结束了一个事务，就好像您在执行该语句之前已经完成了 COMMIT 一样。有关详细信息，请参阅第 13.3.3 节，“导致隐式提交的语句”。

COMMIT 意味着在当前事务中所做的更改是永久的，并且对其他会话可见。另一方面，ROLLBACK 语句取消当前事务所做的所有修改。 COMMIT 和 ROLLBACK 都释放在当前事务期间设置的所有 InnoDB 锁。

将 DML 操作与事务分组

默认情况下，与 MySQL 服务器的连接以启用自动提交模式开始，它会在您执行每个 SQL 语句时自动提交它。如果您有其他数据库系统的经验，这种操作模式可能会比较陌生，标准做法是发出一系列 DML 语句并将它们提交或全部回滚。

要使用多语句事务，请使用 SQL 语句 SET autocommit = 0 关闭自动提交，并根据需要使用 COMMIT 或 ROLLBACK 结束每个事务。或者在保持自动提交的情况下，显式的以 START TRANSACTION 开始每个事务，并以 COMMIT 或 ROLLBACK 结束。

以下示例显示了两个事务。第一个是提交成功的；第二个回滚。

shell> mysql test

mysql> CREATE TABLE customer (a INT, b CHAR (20), INDEX (a));
Query OK, 0 rows affected (0.00 sec)
mysql> -- Do a transaction with autocommit turned on.
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO customer VALUES (10, 'Heikki');
Query OK, 1 row affected (0.00 sec)
mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)
mysql> -- Do another transaction with autocommit turned off.
mysql> SET autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO customer VALUES (15, 'John');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO customer VALUES (20, 'Paul');
Query OK, 1 row affected (0.00 sec)
mysql> DELETE FROM customer WHERE b = 'Heikki';
Query OK, 1 row affected (0.00 sec)
mysql> -- Now we undo those last 2 inserts and the delete.
mysql> ROLLBACK;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM customer;
+------+--------+
| a    | b      |
+------+--------+
|   10 | Heikki |
+------+--------+
1 row in set (0.00 sec)
mysql>

客户端语言的事务

在 PHP、Perl DBI、JDBC、ODBC 或 MySQL 的标准 C 调用接口等 API 中，您可以像 SELECT 或 INSERT 等任何其他 SQL 语句一样将 COMMIT 等事务控制语句作为字符串发送到 MySQL 服务器。一些 API 还提供单独的特殊事务提交和回滚功能或方法。

一致性非阻塞读取

一致性读取意味着 InnoDB 使用多版本控制在某个时间点向查询呈现数据库的快照。查询会看到在该时间点之前提交的事务所做的更改，而不会看到稍后或未提交的事务所做的更改。此规则的一个例外是查询会看到同一事务中较早的语句所做的更改。此异常会导致以下异常情况：如果更新表中的某些行，SELECT 会看到更新行的最新版本，但它也可能会看到任何行的旧版本。如果其他会话同时更新同一个表，则异常意味着您可能会看到该表处于数据库中从未存在过的状态。

如果事务隔离级别是 REPEATABLE READ（默认级别），则同一事务中的所有一致性读取都会使用第一次读取建立的快照。你可以通过提交当前事务然后发出新查询来为您的查询获取更新的快照。

使用 READ COMMITTED 隔离级别，事务中的每个一致性读取设置并读取自己的新快照。

一致读取是 InnoDB 在 READ COMMITTED 和 REPEATABLE READ 隔离级别处理 SELECT 语句的默认模式。一致读不会在它访问的表上设置任何锁，因此，其他会话可以在对表执行一致读取的同时自由修改这些表。

假设您在默认的 REPEATABLE READ 隔离级别下运行。当您发出一致性读取（即普通的 SELECT 语句）时，InnoDB 为您的事务提供一个时间点，您的查询根据该时间点查看数据库。如果另一个事务在分配您的时间点后删除一行并提交，您不会看到该行已被删除。插入和更新的处理方式类似。

数据库状态的快照适用于事务中的 SELECT 语句，不一定适用于 DML 语句。如果您插入或修改一些行然后提交该事务，从另一个并发 REPEATABLE READ 事务发出的 DELETE 或 UPDATE 语句可能会影响那些刚刚提交的行，即使会话无法查询它们。如果一个事务确实更新或删除了由不同事务提交的行，则这些更改对当前事务是可见的。例如，您可能会遇到如下情况：

SELECT COUNT(c1) FROM t1 WHERE c1 = 'xyz';
-- Returns 0: no rows match.
DELETE FROM t1 WHERE c1 = 'xyz';
-- Deletes several rows recently committed by other transaction.

SELECT COUNT(c2) FROM t1 WHERE c2 = 'abc';
-- Returns 0: no rows match.
UPDATE t1 SET c2 = 'cba' WHERE c2 = 'abc';
-- Affects 10 rows: another txn just committed 10 rows with 'abc' values.
SELECT COUNT(c2) FROM t1 WHERE c2 = 'cba';
-- Returns 10: this txn can now see the rows it just updated.

您可以通过提交您的事物，然后使用另一个 SELECT 或 START TRANSACTION WITH CONSISTENT SNAPSHOT 来提前您的时间点。

这称为多版本并发控制。

在以下示例中，会话 A 仅在 B 已提交插入且 A 也已提交时才能看到 B 插入的行，因此时间点会提前到 B 的提交之后。

             Session A              Session B

           SET autocommit=0;      SET autocommit=0;
time
|          SELECT * FROM t;
|          empty set
|                                 INSERT INTO t VALUES (1, 2);
|
v          SELECT * FROM t;
           empty set
                                  COMMIT;

           SELECT * FROM t;
           empty set

           COMMIT;

           SELECT * FROM t;
           ---------------------
           |    1    |    2    |
           ---------------------

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

SELECT * FROM t FOR SHARE;

使用 READ COMMITTED 隔离级别，事务中的每个一致性读取设置并读取自己的新快照。使用 FOR SHARE 时，会发生锁定读取：SELECT 会阻塞，直到包含最新行的事务结束。

一致性读取不适用于某些 DDL 语句：

  • 一致性读取不适用于 DROP TABLE，因为 MySQL 无法使用已删除的表，并且 InnoDB 会破坏该表。
  • 一致性读取不适用于 ALTER TABLE 操作，这些操作创建原始表的临时副本并在构建临时副本时删除原始表。当您在事务中重新发出一致性读取时，新表中的行不可见，因为在获取事务快照时这些行不存在。在这种情况下，事务返回错误：ER_TABLE_DEF_CHANGED，“表定义已更改，请重试事务”。

读取类型因 INSERT INTO ... SELECT、UPDATE ... (SELECT) 和 CREATE TABLE ... SELECT 等子句中的选择而异，这些子句未指定 FOR UPDATE 或 FOR SHARE：

  • 默认情况下，InnoDB 对这些语句使用更强的锁，SELECT 部分的作用类似于 READ COMMITTED，其中每个一致性的读取，即使在同一个事务中，也会设置和读取自己的新快照。
  • 要在这种情况下执行非锁定读取，请将事务的隔离级别设置为 READ UNCOMMITTED 或 READ COMMITTED 以避免对从所选表读取的行设置锁定。

锁定读取

如果您查询数据然后在同一事务中插入或更新相关数据，则常规 SELECT 语句不会提供足够的保护。其他事务可以更新或删除您刚刚查询的相同行。InnoDB 支持两种类型的锁定读取，提供额外的安全性：

  • SELECT ... FOR SHARE

    在读取的任何行上设置共享模式锁。其他会话可以读取这些行，但在您的事务提交之前无法修改它们。如果这些行中的任何一行被另一个尚未提交的事务更改，您的查询将等待该事务结束，然后使用最新值。

    在 MySQL 8.0.22 之前，SELECT ... FOR SHARE 需要 SELECT 权限和至少 DELETE、LOCK TABLES 或 UPDATE 权限之一。从 MySQL 8.0.22 开始，只需要 SELECT 权限。

  • SELECT ... FOR UPDATE

    对于搜索遇到的索引记录，将会锁定该行和任何关联的索引条目，就像您为这些行发出 UPDATE 语句一样。其他事务被阻止更新这些行、执行 SELECT ... FOR SHARE 或读取某些事务隔离级别的数据。

    一致读取忽略对读取视图中存在的记录设置的任何锁定。 （旧版本的记录不能被锁定；它们是通过在记录的内存副本上应用撤消日志（undo logs）来重建的。）

    SELECT ... FOR UPDATE 需要 SELECT 权限和需要执行的 DELETE、LOCK TABLES 或 UPDATE 权限之一。

这些语句主要在处理树结构或图结构数据时有用，无论是在单个表中还是拆分到多个表中。你遍历边界或将树从一个地方到分支另一个地方，同时保留返回并更改任何这些“指针”值。

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

只有在禁用自动提交时才可能锁定读取（通过使用 START TRANSACTION 开始事务或将自动提交设置为 0。)

除非在子查询中还指定了锁定读取子句，否则外部语句中的锁定读取子句不会锁定嵌套子查询中表的行。例如，以下语句不会锁定表 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;

锁定读取示例

假设您要在表 child 中插入新行，并确保子行在表 parent 中也存在。您的应用程序代码需要确保整个序列操作中的数据正确引用。

首先，使用一致性读取来查询表 PARENT 并验证父行是否存在。您可以安全地将子行插入到表 CHILD 中吗？不，因为其他一些会话可能会在您的 SELECT 和您的 INSERT 之间删除父行，而您没有意识到这一点。

为避免此潜在问题，请使用 FOR SHARE 执行 SELECT：

SELECT * FROM parent WHERE NAME = 'Jones' FOR SHARE;

在 FOR SHARE 查询返回父“Jones”后，您可以安全地将子记录添加到 CHILD 表并提交事务。

任何试图在 PARENT 表中的这些行上获取排他锁的事务都将等待您完成，即直到所有表中的数据都处于一致状态。

再举一个例子，考虑表 CHILD_CODES 中的整数计数器字段，用于为添加到表 CHILD 的每个子项分配唯一标识符。不要使用一致读取或共享模式读取来读取计数器的当前值，因为数据库的两个用户可能会看到计数器的相同值，如果两个事务尝试将具有相同标识符的行添加到 CHILD 表，则会发生重复键错误。

要实现计数器的读取和递增，首先使用 FOR UPDATE 对计数器进行锁定读取，然后递增计数器。例如：

SELECT counter_field FROM child_codes FOR UPDATE;
UPDATE child_codes SET counter_field = counter_field + 1;

SELECT ... FOR UPDATE 读取最新的可用数据，在它读取的每一行上设置排他锁。因此，它设置了与搜索 SQL UPDATE 将在行上设置的相同的锁。

前面的描述仅仅是 SELECT ... FOR UPDATE 如何工作的一个例子。在 MySQL 中，生成唯一标识符的具体任务实际上可以通过对表的单次访问来完成：

UPDATE child_codes SET counter_field = LAST_INSERT_ID(counter_field + 1);
SELECT LAST_INSERT_ID();

SELECT 语句仅检索标识符信息（特定于当前连接）。它不访问任何表。

使用 NOWAIT 和 SKIP LOCKED 锁定读取并发

如果行被事务锁定，则请求同一锁定行的 SELECT ... FOR UPDATE 或 SELECT ... FOR SHARE 事务必须等到阻塞事务释放行锁。此行为可防止事务更新或删除其他事务查询更新的行。但是，如果您希望在请求的行被锁定时立即返回查询，或者如果从结果集中排除锁定的行是可以接受的，则不必等待行锁被释放。

为了避免等待其他事务释放行锁，可以将 NOWAIT 和 SKIP LOCKED 选项与 SELECT ... FOR UPDATE 或 SELECT ... FOR SHARE 锁定读取语句一起使用。

  • NOWAIT

    使用 NOWAIT 的锁定读取从不等待获取行锁。查询立即执行，如果请求的行被锁定，则会失败并显示错误。

  • SKIP LOCKED

    使用 SKIP LOCKED 的锁定读取从不等待获取行锁。查询立即执行，从结果集中删除锁定的行。

    跳过锁定行的查询会返回不一致的数据视图。因此，SKIP LOCKED 不适用于一般事务性工作。但是，当多个会话访问同一个类似队列的表时，它可以用来避免锁争用。

NOWAIT 和 SKIP LOCKED 仅适用于行级锁。

使用 NOWAIT 或 SKIP LOCKED 的语句对于基于语句的复制是不安全的。

下面的示例演示了 NOWAIT 和 SKIP LOCKED。会话 1 启动一个对单个记录进行行锁定的事务。会话 2 尝试使用 NOWAIT 选项对同一记录进行锁定读取。由于请求的行被会话 1 锁定，锁定读取会立即返回并显示错误。在会话 3 中，使用 SKIP LOCKED 的锁定读取返回请求的行，但会话 1 锁定的行除外。

# Session 1:

mysql> CREATE TABLE t (i INT, PRIMARY KEY (i)) ENGINE = InnoDB;

mysql> INSERT INTO t (i) VALUES(1),(2),(3);

mysql> START TRANSACTION;

mysql> SELECT * FROM t WHERE i = 2 FOR UPDATE;
+---+
| i |
+---+
| 2 |
+---+

# Session 2:

mysql> START TRANSACTION;

mysql> SELECT * FROM t WHERE i = 2 FOR UPDATE NOWAIT;
ERROR 3572 (HY000): Do not wait for lock.

# Session 3:

mysql> START TRANSACTION;

mysql> SELECT * FROM t FOR UPDATE SKIP LOCKED;
+---+
| i |
+---+
| 1 |
| 3 |
+---+

InnoDB 中不同 SQL 语句设置的锁

锁定读取、更新或删除通常会在处理 SQL 语句时在扫描的每个索引记录上设置记录锁定。**语句中是否存在排除该行的 WHERE 条件并不重要。**InnoDB 不记得确切的 WHERE 条件，但只知道扫描了哪些索引范围。锁通常是 next-key 锁，它也阻止插入记录之前的“间隙”。但是，可以显式禁用间隙锁定，这会导致不使用 next-key 锁定。事务隔离级别也会影响设置了哪些锁；

如果在搜索中使用二级索引并且要设置的索引记录锁是排他的，则 InnoDB 还会检索相应的聚集索引记录并对其设置锁。

如果您没有适合您的语句的索引，并且 MySQL 必须扫描整个表来处理该语句，则该表的每一行都会被锁定，从而阻止其他用户对该表的所有插入。创建好的索引很重要，这样您的查询就不会扫描过多的行。

InnoDB 设置特定类型的锁如下。

  • SELECT ... FROM 是一致性读取，读取数据库的快照并且不设置锁，除非事务隔离级别设置为 SERIALIZABLE。对于 SERIALIZABLE 级别，搜索在它遇到的索引记录上设置共享的 next-key 锁。但是，对于使用唯一索引锁定行以搜索唯一行的语句，只需要索引记录锁。

  • SELECT ... FOR UPDATE 和 SELECT ... FOR SHARE 语句使用唯一索引获取扫描行的锁，并释放不符合包含在结果集中的行的锁（例如，如果它们不符合 WHERE 子句中给出的条件）。但是，在某些情况下，行可能不会立即解锁，因为在查询执行期间结果行与其原始数据之间的关系丢失。例如，在 UNION 中，表中的扫描（和锁定）行可能会在评估它们是否符合结果集的条件之前插入到临时表中。在这种情况下，临时表中的行与原始表中的行的关系丢失，并且后面的行直到查询执行结束才解锁。

  • 对于锁定读取（SELECT with FOR UPDATE 或 FOR SHARE）、UPDATE 和 DELETE 语句，采用的锁定取决于该语句是使用具有唯一搜索条件的唯一索引还是范围类型搜索条件。

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

  • 对于搜索遇到的索引记录，SELECT ... FOR UPDATE 阻止其他会话执行 SELECT ... FOR SHARE 或读取某些事务隔离级别。

  • UPDATE ... WHERE ... 在搜索遇到的每条记录上设置一个独占的 next-key 锁。但是，对于使用唯一索引锁定行以搜索唯一行的语句，只需要索引记录锁。

  • 当 UPDATE 修改聚集索引记录时，会对受影响的二级索引记录进行隐式锁定。在插入新的二级索引记录之前执行重复检查扫描时，以及在插入新的二级索引记录时，UPDATE 操作还会对受影响的二级索引记录使用共享锁。

  • DELETE FROM ... WHERE ... 在搜索遇到的每条记录上设置一个独占的 next-key 锁。但是，对于使用唯一索引锁定行以搜索唯一行的语句，只需要索引记录锁。

  • INSERT 在插入的行上设置排他锁。这个锁是索引记录锁，不是 next-key 锁（即没有间隙锁），并且不会阻止其他会话在插入行之前插入间隙。

    在插入行之前，设置了一种称为插入意向间隙锁（Insert intention gap lock）的间隙锁。此锁表示插入意图，如果插入同一索引间隙的多个事务未插入间隙内的相同位置，则它们无需相互等待。假设存在值为 4 和 7 的索引记录。 尝试插入值 5 和 6 的单独事务在获得插入行的排他锁之前，每个事务都使用插入意向锁锁定了 4 和 7 之间的间隙，但不会相互阻塞，因为行不冲突。

    如果发生重复键错误，则在重复索引记录上设置共享锁。如果另一个会话已经拥有排它锁，那么如果有多个会话尝试插入同一行，则使用共享锁可能会导致死锁。如果另一个会话删除该行，就会发生这种情况。假设 InnoDB 表 t1 具有以下结构：

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

    现在假设三个会话依次执行以下操作：

    Session 1:

    START TRANSACTION;
INSERT INTO t1 VALUES(1);

    Session 2:

    START TRANSACTION;
INSERT INTO t1 VALUES(1);

    Session 3:

    START TRANSACTION;
INSERT INTO t1 VALUES(1);

    Session 1:

    ROLLBACK;

    会话 1 的第一个操作获取该行的排它锁。会话 2 和 3 的操作都会导致重复键错误，并且它们都为该行请求共享锁。当会话 1 回滚时，它会释放它对该行的排它锁，并且会话 2 和 3 的排队共享锁请求被授予。此时，会话 2 和会话 3 陷入僵局：此时，会话 2 和 3 死锁：由于对方都持有共享锁，因此都无法获取该行的排他锁。

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

    Session 1:

    START TRANSACTION;
DELETE FROM t1 WHERE i = 1;

    Session 2:

    START TRANSACTION;
INSERT INTO t1 VALUES(1);

    Session 3:

    START TRANSACTION;
INSERT INTO t1 VALUES(1);

    Session 1:

    COMMIT;

    会话 1 的第一个操作获取该行的排它锁。会话 2 和 3 的操作都会导致重复键错误，并且它们都为该行请求共享锁。当会话 1 提交时，它会释放其在该行上的排它锁，并授予会话 2 和 3 的排队共享锁请求。此时，会话 2 和会话 3 陷入僵局：由于对方都持有共享锁，双方都无法获得该行的排他锁。

  • INSERT ... ON DUPLICATE KEY UPDATE 与简单的 INSERT 的不同之处在于，当发生重复键错误时，将在要更新的行上放置排他锁而不是共享锁。对重复的主键值采用独占索引记录锁。对重复的唯一键值采用独占的 next-key 锁。

  • REPLACE 就像 INSERT 一样，如果唯一键没有冲突。否则，将在要替换的行上放置一个独占的 next-key 锁。

  • INSERT INTO T SELECT ... FROM S WHERE ... 在插入到 T 的每一行上设置排他索引记录锁（没有间隙锁）。如果事务隔离级别为READ COMMITTED，InnoDB则将搜索S作为一致读（无锁）进行。否则，InnoDB对来自 S 的行设置共享 next-key 锁。 InnoDB在后一种情况下必须设置锁：在使用基于语句的二进制日志的前滚恢复期间，每个 SQL 语句都必须以与最初执行的方式完全相同的方式执行。

    CREATE TABLE ... SELECT ... 使用共享的 next-key 锁或作为一致读取执行 SELECT，如 INSERT ... SELECT。

    当在结构 REPLACE INTO t SELECT ... FROM s WHERE ... 或 UPDATE t ... WHERE col IN (SELECT ... FROM s ...) 中使用 SELECT 时，InnoDB 将共享 next-key 锁设置为表 s 中的行。

  • InnoDB 在初始化表上先前指定的 AUTO_INCREMENT 列时，在与 AUTO_INCREMENT 列关联的索引的末尾设置排他锁。

    当 innodb_autoinc_lock_mode=0 时，InnoDB 使用特殊的 AUTO-INC 表锁模式，在访问自动递增计数器时，获取锁并将其保持到当前 SQL 语句的末尾（而不是整个事务的末尾）。持有 AUTO-INC 表锁时，其他客户端无法插入表中。对于 innodb_autoinc_lock_mode=1 的“批量插入”也会发生相同的行为。表级 AUTO-INC 锁不与 innodb_autoinc_lock_mode=2 一起使用。有关更多信息，请参阅第 15.6.1.6 节，“InnoDB 中的 AUTO_INCREMENT 处理”。

    InnoDB 在不设置任何锁的情况下获取先前初始化的 AUTO_INCREMENT 列的值。

  • 如果在表上定义了 FOREIGN KEY 约束，则任何需要检查约束条件的插入、更新或删除都会在它查看以检查约束的记录上设置共享记录级锁。InnoDB 也会在约束失败的情况下设置这些锁。

  • LOCK TABLES 设置表锁，但设置这些锁的是 InnoDB 层之上的更高 MySQL 层。如果 innodb_table_locks = 1（默认值）和 autocommit = 0，InnoDB 知道表锁，并且 InnoDB 之上的 MySQL 层知道行级锁。

    否则，InnoDB 的自动死锁检测无法检测到涉及此类表锁的死锁。因为在这种情况下，更高的 MySQL 层不知道行级锁，可以在另一个会话当前具有行级锁的表上获得表锁。但是，这不会危及事务完整性，如第 15.7.5.2 节“死锁检测”中所述。

  • 如果 innodb_table_locks=1（默认值），LOCK TABLES 会在每个表上获取两个锁。除了 MySQL 层的表锁外，它还获取了一个InnoDB 表锁。为了避免获取 InnoDB 表锁，设置 innodb_table_locks=0。如果没有获取 InnoDB 表锁，即使表的某些记录被其他事务锁定，LOCK TABLES 也会完成。

    在 MySQL 8.0 中，innodb_table_locks=0 对使用 LOCK TABLES ... WRITE 显式锁定的表没有影响。但是对通过 LOCK TABLES ... WRITE 隐式（例如，通过触发器）锁定以进行读取或写入的表有影响。

  • 当事务提交或中止时，事务持有的所有 InnoDB 锁都会被释放。因此，在 autocommit=1 模式下在 InnoDB 表上调用 LOCK TABLES 没有多大意义，因为获取的 InnoDB 表锁会立即释放。

  • 你不能在事务中间锁定其他表，因为 LOCK TABLES 执行隐式 COMMIT 和 UNLOCK TABLES。

幻读

当一个事物中同一个查询在不同时间产生不同的行集时，就认为发生了幻读问题。例如，如果 SELECT 执行两次，但第二次返回第一次未返回的行，则该行被称为“幻影”行。

假设 child 表的 id 列上有一个索引，并且您想要读取并锁定表中 id 值大于 100 的所有行，以便稍后更新所选行中的某些列：

SELECT * FROM child WHERE id > 100 FOR UPDATE;

查询从 id 大于 100 的第一条记录开始扫描索引。该表包含 id 值为 90 和 102 的行。如果在扫描范围内的索引记录上设置的锁没有锁定在间隙（在本例中为 90 和 102 之间的间隙）中进行的插入，另一个会话就可以在表的该间隙中插入一个新行，id 为 101。如果你在同一个事务中执行相同的 SELECT，就会在查询返回的结果集中看到一个 id 为 101（“幻影”）的新行。如果我们把一组行看作一个数据项整体，新的幻影行就会违反事务的隔离原则：隔离确保事务的并发执行使数据库处于与顺序执行事务时获得的状态相同。不完整的事物的影响甚至可能对其他事物不可见。

为了防止幻读，InnoDB 使用了一种称为 next-key 锁定的算法，该算法将索引行锁定与间隙锁定相结合。InnoDB 执行行级锁定的方式是，当它搜索或扫描表索引时，它会在遇到的索引记录上设置共享锁或排他锁。因此，行级锁实际上是索引记录锁。此外，索引记录上的 next-key 锁也会影响索引记录之前的“间隙”。也就是说，next-key 锁是一个索引记录锁加上一个在索引记录之前的间隙上的间隙锁。如果一个会话在索引中的记录 R 上有共享锁或排他锁，另一个会话就不能在索引顺序中紧靠 R 之前的间隙中插入新的索引记录。

InnoDB 扫描索引时，也可以锁定索引中最后一条记录后的间隙。在前面的例子中就是这样：为了防止在 id 大于 100 的表中进行任何插入，InnoDB 设置的锁包括对 id 值 102 之后的间隙的锁。

你可以使用 next-key 锁定在你的应用程序中实现唯一性检查：如果您在共享模式下读取数据并且没有看到您要插入的行的重复项，那么您可以安全地插入您的行并知道在读取期间在您的行的后继者上设置的 next-key 锁可防止任何人同时为您的行插入重复项。因此，next-key 锁定使您能够“锁定”表中不存在的内容。

可以禁用间隙锁定，如 第 15.7.1 节，“InnoDB 锁定”中所述。这可能会导致幻读问题，因为当禁用间隙锁定时，其他会话可以将新行插入间隙中。

总结

在默认的事物隔离级别可重复读 REPEATABLE READ 在同一事物中会使用基于 MVCC 的多版本控制系统来实现无锁的一致性读，多次读取会使用第一次建立的快照。

对于 UPDATE、DELETE 等操作使用锁定读取，锁定读取会为扫描到的每条记录设置锁定，不管有没有匹配 WHERE 条件，如果查询是搜索唯一行的唯一索引，则只锁定记录锁，否则对于非唯一索引、其他搜索条件，会使用 next-key 独占锁锁定记录和之间的间隙锁。

对于 INSERT 语句，会先使用插入意向锁这种间隙锁来表明自己要在此间隙插入的意图，在获取到要插入位置的意向锁后会使用排他记录锁锁定该行。

由于可重复读隔离级别使用一次建立快照的一致性读和 next-key 锁，有效避免了同一事物在间隙中插入数据而导致两次查询返回不同的数据的幻读问题。

对于读已提交 READ COMMITTED隔离级别，也是使用一致性读，但是统一事物中每次一致性读都会建立新的快照，而且对于 UPDATE、DELETE 等操作的锁定读取变为只锁定记录，不锁定间隙，且只锁定满足条件的记录。INSERT 和可重复读保持一致。

由于少了间隙锁和使用每次建立新快照的一致性读，导致同一事物中可能在两次范围查询中间存在该范围的插入数据，导致后面一次查询出现幻影行，导致幻读。