MySQL8-中文参考-七十-

布客飞龙
56 阅读50分钟

MySQL8 中文参考（七十）

原文：docs.oracle.com/javase/tutorial/reallybigindex.html

17.6.6 撤销日志

原文：dev.mysql.com/doc/refman/8.0/en/innodb-undo-logs.html

撤销日志是与单个读写事务关联的撤销日志记录集合。撤销日志记录包含有关如何撤消事务对聚簇索引记录的最新更改的信息。如果另一个事务需要在一致性读操作的一部分中查看原始数据，则从撤销日志记录中检索未修改的数据。撤销日志存在于撤销日志段中，这些段包含在回滚段中。回滚段驻留在撤销表空间和全局临时表空间中。

驻留在全局临时表空间中的撤销日志用于修改用户定义临时表中的数据的事务。这些撤销日志不会被重做记录，因为它们不需要用于崩溃恢复。它们仅在服务器运行时用于回滚。这种类型的撤销日志通过避免重做日志 I/O 来提高性能。

有关撤销日志的数据静态加密信息，请参阅撤销日志加密。

每个撤销表空间和全局临时表空间分别支持最多 128 个回滚段。innodb_rollback_segments变量定义了回滚段的数量。

回滚段支持的事务数量取决于回滚段中的撤销槽数量以及每个事务所需的撤销日志数量。回滚段中的撤销槽数量根据InnoDB页面大小而异。

InnoDB 页面大小回滚段中的撤销槽数量（InnoDB 页面大小 / 16）
4096 (4KB)256
8192 (8KB)512
16384 (16KB)1024
32768 (32KB)2048
65536 (64KB)4096

每个事务分配最多四个撤销日志，分别用于以下每种操作类型：

  1. 用户定义表上的INSERT操作

  2. 用户定义表上的UPDATE和DELETE操作

  3. 用户定义临时表上的INSERT操作

  4. 用户定义临时表上的UPDATE和DELETE操作

撤销日志根据需要分配。例如，对常规表和临时表执行INSERT、UPDATE和DELETE操作的事务需要完整分配四个撤销日志。只对常规表执行INSERT操作的事务需要一个撤销日志。

在常规表上执行操作的事务从分配的撤销表空间回滚段中分配撤销日志。在临时表上执行操作的事务从分配的全局临时表空间回滚段中分配撤销日志。

一个分配给事务的撤销日志将一直附加到该事务，直到其结束。例如，分配给常规表上的INSERT操作的事务的撤销日志将用于该事务执行的所有常规表上的INSERT操作。

鉴于上述因素，可以使用以下公式来估计InnoDB能够支持的并发读写事务数量。

注意

在达到InnoDB能够支持的并发读写事务数量之前，可能会遇到并发事务限制错误。当分配给事务的回滚段用完撤销槽时会发生这种情况。在这种情况下，请尝试重新运行事务。

当事务在临时表上执行操作时，InnoDB能够支持的并发读写事务数量受限于分配给全局临时表空间的回滚段数量，默认为 128。

  • 如果每个事务执行INSERT 或 UPDATE或DELETE操作，InnoDB能够支持的并发读写事务数量为：

    (innodb_page_size / 16) * innodb_rollback_segments * number of undo tablespaces

  • 如果每个事务执行INSERT 和 UPDATE或DELETE操作，InnoDB能够支持的并发读写事务数量为：

    (innodb_page_size / 16 / 2) * innodb_rollback_segments * number of undo tablespaces

  • 如果每个事务在临时表上执行INSERT操作，InnoDB能够支持的并发读写事务数量为：

    (innodb_page_size / 16) * innodb_rollback_segments

  • 如果每个事务在临时表上执行INSERT 和 UPDATE或DELETE操作，InnoDB能够支持的并发读写事务数量为：

    (innodb_page_size / 16 / 2) * innodb_rollback_segments

17.7 InnoDB 锁定和事务模型

原文：dev.mysql.com/doc/refman/8.0/en/innodb-locking-transaction-model.html

17.7.1 InnoDB 锁定

17.7.2 InnoDB 事务模型

17.7.3 InnoDB 中由不同 SQL 语句设置的锁

17.7.4 幻影行

17.7.5 InnoDB 中的死锁

17.7.6 事务调度

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

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

  • 第 17.7.1 节，“InnoDB 锁定” 描述了InnoDB使用的锁类型。

  • 第 17.7.2 节，“InnoDB 事务模型” 描述了事务隔离级别和每种锁定策略的使用。它还讨论了autocommit的使用，一致的非锁定读取和锁定读取。

  • 第 17.7.3 节，“InnoDB 中由不同 SQL 语句设置的锁” 讨论了在InnoDB中为各种语句设置的特定类型的锁。

  • 第 17.7.4 节，“幻影行” 描述了InnoDB如何使用下一个键锁定来避免幻影行。

  • 第 17.7.5 节，“InnoDB 中的死锁” 提供了一个死锁示例，讨论了死锁检测，并提供了最小化和处理InnoDB中死锁的提示。

17.7.1 InnoDB 锁定

原文：dev.mysql.com/doc/refman/8.0/en/innodb-locking.html

本节描述了 InnoDB 使用的锁类型。

  • 共享锁和独占锁

  • 意向锁

  • 记录锁

  • 间隙锁

  • Next-Key 锁

  • 插入意向锁

  • 自增锁

  • 空间索引的谓词锁

共享锁和独占锁

InnoDB 实现标准的行级锁定，其中有两种类型的锁，共享 (S) 锁 和 独占 (X) 锁。

  • 一个共享 (S) 锁 允许持有锁的事务读取一行。

  • 一个独占 (X) 锁允许持有锁的事务更新或删除一行。

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

  • T2 请求 S 锁可以立即被授予。因此，T1 和 T2 都持有 r 上的 S 锁。

  • T2 对 r 的 X 锁请求无法立即被授予。

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

意向锁

InnoDB 支持多粒度锁定，允许行锁和表锁共存。例如，像LOCK TABLES ... WRITE 这样的语句在指定的表上获取一个独占锁（X 锁）。为了使多粒度级别的锁定实用，InnoDB 使用意向锁。意向锁是表级锁，指示事务后续需要对表中的行请求哪种类型的锁（共享或独占）。有两种类型的意向锁：

  • 一个意向共享锁 (IS) 表示事务打算在表中的各个行上设置共享锁。

  • 意向排他锁（IX）表示一个事务打算在表中的单个行上设置排他锁。

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

意向锁定协议如下：

  • 在事务可以在表中的行上获取共享锁之前，必须首先在表上获取一个IS锁或更强的锁。

  • 在事务可以在表中的行上获取排他锁之前，必须首先在表上获取一个IX锁。

表级锁类型兼容性总结如下矩阵。

XIXSIS
X冲突冲突冲突冲突
IX冲突兼容冲突兼容
S冲突冲突兼容兼容
IS冲突兼容兼容兼容

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

意向锁不会阻塞任何东西，除了完整的表请求（例如，LOCK TABLES ... WRITE）。意向锁的主要目的是显示某人正在锁定一行，或者将要在表中锁定一行。

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

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

记录锁

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

记录锁总是锁定索引记录，即使一个表被定义为没有索引。对于这种情况，InnoDB会创建一个隐藏的聚簇索引，并将此索引用于记录锁定。参见 Section 17.6.2.1, “Clustered and Secondary Indexes”。

记录锁的事务数据在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        ;;

间隙锁

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

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

间隙锁是性能和并发性之间的权衡的一部分，并且在某些事务隔离级别中使用，在其他事务隔离级别中不使用。

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

SELECT * FROM child WHERE id = 100;

如果id没有被索引或具有非唯一索引，则该语句确实锁定了前面的间隙。

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

在InnoDB中，间隙锁是“纯粹抑制性的”，这意味着它们的唯一目的是防止其他事务向间隙中插入数据。间隙锁可以共存。一个事务获取的间隙锁不会阻止另一个事务在同一间隙上获取间隙锁。共享间隙锁和独占间隙锁之间没有区别。它们不会相互冲突，执行相同的功能。

间隙锁定可以被显式禁用。如果将事务隔离级别更改为READ COMMITTED，则会发生这种情况。在这种情况下，间隙锁定对搜索和索引扫描被禁用，仅用于外键约束检查和重复键检查。

使用READ COMMITTED隔离级别还有其他影响。对于不匹配行的记录锁在 MySQL 评估完WHERE条件后被释放。对于UPDATE语句，InnoDB执行“半一致性”读取，以便将最新提交的版本返回给 MySQL，以便 MySQL 可以确定行是否与UPDATE的WHERE条件匹配。

下一个键锁

下一个键锁是在索引记录上设置记录锁和在索引记录之前的间隙上设置间隙锁的组合。

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

假设一个索引包含值 10、11、13 和 20。对于该索引的可能的下一个键锁覆盖以下区间，其中圆括号表示排除区间端点，方括号表示包含端点：

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

对于最后一个区间，下一个键锁锁定了索引中最大值上方的间隙和具有高于索引中任何实际值的“最大值”伪记录。最大值不是真正的索引记录，因此，实际上，这个下一个键锁只锁定了紧随最大索引值后的间隙。

默认情况下，InnoDB 在 REPEATABLE READ 事务隔离级别下运行。在这种情况下，InnoDB 对搜索和索引扫描使用下一个键锁，以防止幻影行（参见 第 17.7.4 节，“幻影行”）。

下一个键锁的事务数据在 SHOW ENGINE INNODB STATUS 和 InnoDB 监视器 输出中类似于以下内容：

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 操作插入行之前设置的一种间隙锁。此锁表示以一种插入的意图插入，即使多个插入到相同索引间隙的事务不需要等待对方，如果它们不是在间隙内的相同位置插入。假设存在值为 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 监视器 输出中类似于以下内容：

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 锁

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

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

更多信息，请参见 第 17.6.1.6 节，“InnoDB 中的 AUTO_INCREMENT 处理”。

空间索引的谓词锁

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

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

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

17.7.2 InnoDB 事务模型

原文：dev.mysql.com/doc/refman/8.0/en/innodb-transaction-model.html

17.7.2.1 事务隔离级别

17.7.2.2 自动提交、提交和回滚

17.7.2.3 一致性非锁定读取

17.7.2.4 锁定读取

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

原文：dev.mysql.com/doc/refman/8.0/en/innodb-transaction-isolation-levels.html

17.7.2.1 事务隔离级别

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

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

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

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

以下列表描述了 MySQL 支持不同事务级别的方式。列表从最常用的级别到最不常用的级别。

  • REPEATABLE READ

    这是InnoDB的默认隔离级别。在同一事务中进行一致性读取时，读取的是第一次读取时建立的快照。这意味着如果您在同一事务中发出几个普通（非锁定）SELECT语句，这些SELECT语句在彼此之间也是一致的。请参阅 Section 17.7.2.3, “Consistent Nonlocking Reads”。

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

    • 对于具有唯一搜索条件的唯一索引，InnoDB仅锁定找到的索引记录，而不是其前面的间隙。

    • 对于其他搜索条件，InnoDB锁定扫描的索引范围，使用间隙锁或下一个键锁来阻止其他会话在范围覆盖的间隙中插入。有关间隙锁和下一个键锁的信息，请参阅 Section 17.7.1, “InnoDB Locking”。

  • READ COMMITTED

    每个一致性读取，即使在同一事务中，也会设置并读取自己的新快照。有关一致性读取的信息，请参阅 Section 17.7.2.3, “Consistent Nonlocking Reads”。

    对于锁定读取（带有FOR UPDATE或FOR SHARE的SELECT）、UPDATE和DELETE语句，InnoDB仅锁定索引记录，而不是它们之前的间隙，因此允许在锁定记录旁边自由插入新记录。间隙锁仅用于外键约束检查和重复键检查。

    由于间隙锁定已禁用，可能会出现幻影行问题，因为其他会话可以在间隙中插入新行。有关幻影行的信息，请参阅 Section 17.7.4, “Phantom Rows”。

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

    使用READ COMMITTED还有额外的影响：

    • 对于UPDATE或DELETE语句，InnoDB仅为更新或删除的行保持锁定。对于不匹配的行，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;

    在这种情况下，表没有索引，因此搜索和索引扫描使用隐藏的聚簇索引进行记录锁定（参见第 17.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，但如果autocommit被禁用，InnoDB会隐式地将所有普通的SELECT语句转换为SELECT ... FOR SHARE。如果autocommit被启用，SELECT就是它自己的事务。因此，它被认为是只读的，如果作为一致的（非锁定的）读取执行，可以进行序列化，不需要为其他事务阻塞。（要强制普通的SELECT在其他事务修改了所选行时阻塞，禁用autocommit。）

    注意

    从 MySQL 8.0.22 开始，从 MySQL 授权表中读取数据的 DML 操作（通过连接列表或子查询）但不修改它们的操作不会获取 MySQL 授权表的读锁，无论隔离级别如何。更多信息，请参阅授权表并发性。

原文：dev.mysql.com/doc/refman/8.0/en/innodb-autocommit-commit-rollback.html

17.7.2.2 自动提交、提交和回滚

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

通过使用显式的START TRANSACTION或BEGIN语句开始，以及使用COMMIT或ROLLBACK语句结束，启用了autocommit的会话可以执行多语句事务。参见第 15.3.1 节，“START TRANSACTION, COMMIT 和 ROLLBACK 语句”。

如果在使用SET autocommit = 0禁用了autocommit模式的会话中，该会话始终保持一个事务处于打开状态。COMMIT或ROLLBACK语句结束当前事务并启动一个新事务。

如果禁用了autocommit的会话在没有显式提交最终事务的情况下结束，MySQL 会回滚该事务。

一些语句会隐式结束一个事务，就好像在执行该语句之前执行了COMMIT。详情请参见第 15.3.3 节，“导致隐式提交的语句”。

一个COMMIT表示当前事务中所做的更改已经永久生效，并对其他会话可见。另一方面，ROLLBACK语句会取消当前事务所做的所有修改。COMMIT和ROLLBACK都会释放在当前事务期间设置的所有InnoDB锁。

使用事务对 DML 操作进行分组

默认情况下，连接到 MySQL 服务器时会启用自动提交模式，这会在执行每个 SQL 语句时自动提交。如果您有其他数据库系统的经验，在那些系统中，通常会发出一系列 DML 语句并一起提交或回滚，这种操作模式可能会让您感到陌生。

要使用多语句事务，请使用 SQL 语句SET autocommit = 0关闭自动提交，并在每个事务结束时使用COMMIT或ROLLBACK。要保持自动提交状态，请在每个事务开始时使用START TRANSACTION，并在结束时使用COMMIT或ROLLBACK。以下示例展示了两个事务。第一个被提交；第二个被回滚。

$> 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 中，您可以像发送其他 SQL 语句（如SELECT或INSERT）一样，将事务控制语句（如COMMIT）作为字符串发送到 MySQL 服务器。一些 API 还提供单独的特殊事务提交和回滚函数或方法。

原文：dev.mysql.com/doc/refman/8.0/en/innodb-consistent-read.html

17.7.2.3 一致性非锁定读取

一致性读取意味着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会阻塞，直到包含最新行的事务结束（参见第 17.7.2.4 节，“锁定读取”）。

一致性读取在某些 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，以避免对从所选表中读取的行设置锁。

原文：dev.mysql.com/doc/refman/8.0/en/innodb-locking-reads.html

17.7.2.4 锁定读取

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

  • SELECT ... FOR SHARE

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

    注意

    SELECT ... FOR SHARE 是 SELECT ... LOCK IN SHARE MODE 的替代品，但 LOCK IN SHARE MODE 仍然可用于向后兼容。这两个语句是等效的。然而，FOR SHARE 支持 OF *table_name*、NOWAIT 和 SKIP LOCKED 选项。请参阅 Locking Read Concurrency with NOWAIT and SKIP LOCKED。

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

    从 MySQL 8.0.22 开始，SELECT ... FOR SHARE 语句不会在 MySQL 授权表上获取读取锁。有关更多信息，请参阅 Grant Table Concurrency。

  • SELECT ... FOR UPDATE

    对于搜索遇到的索引记录，锁定行和任何相关的索引条目，就像您为这些行发出 UPDATE 语句一样。其他事务被阻止更新这些行，执行 SELECT ... FOR SHARE，或在某些事务隔离级别下读取数据。一致性读取会忽略在读取视图中存在的记录上设置的任何锁定。（旧版本的记录不能被锁定；它们通过在记录的内存副本上应用 undo logs 来重建。）

    SELECT ... FOR UPDATE 需要 SELECT 权限和至少一个 DELETE、LOCK TABLES 或 UPDATE 权限。

这些子句主要在处理树形结构或图形结构数据时非常有用，无论是在单个表中还是跨多个表中。您可以从一个地方遍历边缘或树枝到另一个地方，同时保留回来更改任何这些“指针”值的权利。

由FOR SHARE和FOR UPDATE查询设置的所有锁定在事务提交或回滚时释放。

注意

只有在禁用自动提交时（通过使用START TRANSACTION开始事务或将autocommit设置为 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 SHARE不是一个好的解决方案，因为如果两个用户同时读取计数器，其中至少一个在尝试更新计数器时会陷入死锁。

要实现对计数器的读取和递增操作，首先使用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事务必须等待阻塞事务释放行锁。这种行为防止了事务更新或删除被其他事务查询更新的行。然而，如果希望查询在请求的行被锁定时立即返回，或者接受将被锁定的行排除在结果集之外，则无需等待行锁被释放。

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

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

17.7.3 InnoDB 中不同 SQL 语句设置的锁

原文：dev.mysql.com/doc/refman/8.0/en/innodb-locks-set.html

锁定读取、UPDATE 或 DELETE 通常会在处理 SQL 语句时扫描的每个索引记录上设置记录锁。语句中是否有 WHERE 条件排除行并不重要。InnoDB 不记得确切的 WHERE 条件，只知道扫描了哪些索引范围。这些锁通常是下一个键锁，还会阻止插入到记录之前的“间隙”。但是，可以显式禁用间隙锁定，这会导致不使用下一个键锁。有关更多信息，请参见第 17.7.1 节，“InnoDB 锁定”。事务隔离级别也会影响设置的锁；请参见第 17.7.2.1 节，“事务隔离级别”。

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

如果您的语句没有适合的索引，MySQL 必须扫描整个表来处理语句，那么表的每一行都会被锁定，从而阻止其他用户向表中插入数据。重要的是要创建良好的索引，以便您的查询不会扫描比必要更多的行。

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

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

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

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

    • 对于具有唯一搜索条件的唯一索引，InnoDB仅锁定找到的索引记录，而不是其前面的间隙。

    • 对于其他搜索条件和非唯一索引，InnoDB会锁定扫描的索引范围，使用间隙锁或下一个键锁来阻止其他会话在范围覆盖的间隙中插入。有关间隙锁和下一个键锁的信息，请参见第 17.7.1 节，“InnoDB 锁定”。

  • 对于索引记录，搜索遇到的SELECT ... FOR UPDATE会阻止其他会话执行SELECT ... FOR SHARE或在某些事务隔离级别下读取。一致性读取会忽略读取视图中存在的记录上设置的任何锁。

  • UPDATE ... WHERE ...在搜索遇到的每个记录上设置一个独占的下一个键锁。然而，仅对使用唯一索引锁定行以搜索唯一行的语句需要索引记录锁。

  • 当UPDATE修改聚集索引记录时，会对受影响的次要索引记录采取隐式锁。在插入新的次要索引记录之前执行重复检查扫描时，UPDATE操作还会对受影响的次要索引记录采取共享锁，并在插入新的次要索引记录时也会采取共享锁。

  • DELETE FROM ... WHERE ...对搜索遇到的每条记录设置排他的 next-key 锁。然而，对于使用唯一索引锁定行以搜索唯一行的语句，只需要一个索引记录锁。

  • INSERT对插入的行设置排他锁。这个锁是一个索引记录锁，而不是一个 next-key 锁（也就是说，没有间隙锁），不会阻止其他会话在插入行之前插入到间隙中。

    在插入行之前，会设置一种称为插入意向间隙锁的间隙锁类型。这个锁表示插入的意图，以便多个插入相同索引间隙的事务不必等待彼此，如果它们不在间隙内的相同位置插入。假设存在值为 4 和 7 的索引记录。尝试插入值为 5 和 6 的单独事务在获得插入行的排他锁之前，会在 4 和 7 之间的间隙上设置插入意向锁，但不会相互阻塞，因为这些行是不冲突的。

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

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

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

    第 1 节：

    START TRANSACTION;
INSERT INTO t1 VALUES(1);

    第 2 节：

    START TRANSACTION;
INSERT INTO t1 VALUES(1);

    第 3 节：

    START TRANSACTION;
INSERT INTO t1 VALUES(1);

    第 1 节：

    ROLLBACK;

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

    如果表中已经包含具有键值 1 的行，并且三个会话按顺序执行以下操作，则会发生类似的情况：

    第 1 节：

    START TRANSACTION;
DELETE FROM t1 WHERE i = 1;

    第 2 节：

    START TRANSACTION;
INSERT INTO t1 VALUES(1);

    第 3 节：

    START TRANSACTION;
INSERT INTO t1 VALUES(1);

    第 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 会在来自表 s 的行上设置共享的 next-key 锁。

  • 在初始化表上先前指定的 AUTO_INCREMENT 列时，InnoDB 会在与 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。更多信息，请参阅 第 17.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 层不知道行级锁，所以可能在另一个会话当前具有行级锁的表上获取表锁。然而，这并不会危及事务完整性，如第 17.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 ... READ读取或写入的表，它会产生影响。

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

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

17.7.4 幻影行

原文：dev.mysql.com/doc/refman/8.0/en/innodb-next-key-locking.html

所谓的幻影问题发生在事务中，当同一查询在不同时间产生不同的行集时。例如，如果SELECT执行两次，但第二次返回了第一次没有返回的行，则该行是一个“幻影”行。

假设child表的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 锁定使您能够在表中“锁定”某些内容的不存在。

可以按照第 17.7.1 节，“InnoDB 锁定”中讨论的方法禁用间隙锁定。这可能会导致幻影问题，因为在禁用间隙锁定时，其他会话可以在间隙中插入新行。

17.7.5 InnoDB 中的死锁

原文：dev.mysql.com/doc/refman/8.0/en/innodb-deadlocks.html

17.7.5.1 InnoDB 死锁示例

17.7.5.2 死锁检测

17.7.5.3 如何最小化和处理死锁

死锁是指不同事务由于各自持有对方需要的锁而无法继续进行的情况。因为两个事务都在等待资源可用，所以它们都不会释放自己持有的锁。

当事务以相反的顺序锁定多个表中的行（通过诸如UPDATE或SELECT ... FOR UPDATE等语句）时，可能会发生死锁。当这些语句锁定索引记录和间隙的范围时，也可能发生死锁，因为每个事务由于时间问题而获取了一些锁，但没有获取其他锁。有关死锁示例，请参见第 17.7.5.1 节，“InnoDB 死锁示例”。

为减少死锁的可能性，请使用事务而不是LOCK TABLES语句；确保插入或更新数据的事务足够小，不会长时间保持打开状态；当不同事务更新多个表或大范围的行时，在每个事务中使用相同的操作顺序（如SELECT ... FOR UPDATE）；在SELECT ... FOR UPDATE和UPDATE ... WHERE语句中使用的列上创建索引。死锁的可能性不受隔离级别的影响，因为隔离级别改变了读操作的行为，而死锁是由写操作引起的。有关避免和恢复死锁条件的更多信息，请参见第 17.7.5.3 节，“如何最小化和处理死锁”。

当死锁检测被启用（默认情况下），并且确实发生了死锁时，InnoDB会检测到这种情况，并回滚其中一个事务（受害者）。如果使用innodb_deadlock_detect变量禁用了死锁检测，InnoDB会依赖于innodb_lock_wait_timeout设置来在发生死锁时回滚事务。因此，即使您的应用逻辑是正确的，您仍然必须处理事务必须重试的情况。要查看InnoDB用户事务中的最后一个死锁，请使用SHOW ENGINE INNODB STATUS。如果频繁的死锁突显出事务结构或应用程序错误处理的问题，请启用innodb_print_all_deadlocks以将所有死锁的信息打印到mysqld错误日志中。有关死锁如何自动检测和处理的更多信息，请参见 Section 17.7.5.2, “Deadlock Detection”。

原文：dev.mysql.com/doc/refman/8.0/en/innodb-deadlock-example.html

17.7.5.1 InnoDB 死锁示例

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

InnoDB 状态包含最后一个死锁的详细信息。对于频繁发生的死锁，请启用全局变量innodb_print_all_deadlocks。这会将死锁信息添加到错误日志中。

客户端 A 启用innodb_print_all_deadlocks，创建两个表，'Animals' 和 'Birds'，并向每个表插入数据。客户端 A 开始一个事务，并以共享模式选择 Animals 表中的一行：

mysql> SET GLOBAL innodb_print_all_deadlocks = ON;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE Animals (name VARCHAR(10) PRIMARY KEY, value INT) ENGINE = InnoDB;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE Birds (name VARCHAR(10) PRIMARY KEY, value INT) ENGINE = InnoDB;
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO Animals (name,value) VALUES ("Aardvark",10);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO Birds (name,value) VALUES ("Buzzard",20);
Query OK, 1 row affected (0.00 sec)

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

mysql> SELECT value FROM Animals WHERE name='Aardvark' FOR SHARE;
+-------+
| value |
+-------+
|    10 |
+-------+
1 row in set (0.00 sec)

接下来，客户端 B 开始一个事务，并以共享模式选择 Birds 表中的一行：

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

mysql> SELECT value FROM Birds WHERE name='Buzzard' FOR SHARE;
+-------+
| value |
+-------+
|    20 |
+-------+
1 row in set (0.00 sec)

性能模式显示了两个 select 语句后的锁定情况：

mysql> SELECT ENGINE_TRANSACTION_ID as Trx_Id, 
              OBJECT_NAME as `Table`, 
              INDEX_NAME as `Index`, 
              LOCK_DATA as Data, 
              LOCK_MODE as Mode, 
              LOCK_STATUS as Status, 
              LOCK_TYPE as Type 
        FROM performance_schema.data_locks;
+-----------------+---------+---------+------------+---------------+---------+--------+
| Trx_Id          | Table   | Index   | Data       | Mode          | Status  | Type   |
+-----------------+---------+---------+------------+---------------+---------+--------+
| 421291106147544 | Animals | NULL    | NULL       | IS            | GRANTED | TABLE  |
| 421291106147544 | Animals | PRIMARY | 'Aardvark' | S,REC_NOT_GAP | GRANTED | RECORD |
| 421291106148352 | Birds   | NULL    | NULL       | IS            | GRANTED | TABLE  |
| 421291106148352 | Birds   | PRIMARY | 'Buzzard'  | S,REC_NOT_GAP | GRANTED | RECORD |
+-----------------+---------+---------+------------+---------------+---------+--------+
4 rows in set (0.00 sec)

然后客户端 B 更新 Animals 表中的一行：

mysql> UPDATE Animals SET value=30 WHERE name='Aardvark';

客户端 B 必须等待。性能模式显示了等待锁的情况：

mysql> SELECT REQUESTING_ENGINE_LOCK_ID as Req_Lock_Id,
              REQUESTING_ENGINE_TRANSACTION_ID as Req_Trx_Id,
              BLOCKING_ENGINE_LOCK_ID as Blk_Lock_Id, 
              BLOCKING_ENGINE_TRANSACTION_ID as Blk_Trx_Id
        FROM performance_schema.data_lock_waits;
+----------------------------------------+------------+----------------------------------------+-----------------+
| Req_Lock_Id                            | Req_Trx_Id | Blk_Lock_Id                            | Blk_Trx_Id      |
+----------------------------------------+------------+----------------------------------------+-----------------+
| 139816129437696:27:4:2:139816016601240 |      43260 | 139816129436888:27:4:2:139816016594720 | 421291106147544 |
+----------------------------------------+------------+----------------------------------------+-----------------+
1 row in set (0.00 sec)

mysql> SELECT ENGINE_LOCK_ID as Lock_Id, 
              ENGINE_TRANSACTION_ID as Trx_id, 
              OBJECT_NAME as `Table`, 
              INDEX_NAME as `Index`, 
              LOCK_DATA as Data, 
              LOCK_MODE as Mode, 
              LOCK_STATUS as Status, 
              LOCK_TYPE as Type 
        FROM performance_schema.data_locks;
+----------------------------------------+-----------------+---------+---------+------------+---------------+---------+--------+
| Lock_Id                                | Trx_Id          | Table   | Index   | Data       | Mode          | Status  | Type   |
+----------------------------------------+-----------------+---------+---------+------------+---------------+---------+--------+
| 139816129437696:1187:139816016603896   |           43260 | Animals | NULL    | NULL       | IX            | GRANTED | TABLE  |
| 139816129437696:1188:139816016603808   |           43260 | Birds   | NULL    | NULL       | IS            | GRANTED | TABLE  |
| 139816129437696:28:4:2:139816016600896 |           43260 | Birds   | PRIMARY | 'Buzzard'  | S,REC_NOT_GAP | GRANTED | RECORD |
| 139816129437696:27:4:2:139816016601240 |           43260 | Animals | PRIMARY | 'Aardvark' | X,REC_NOT_GAP | WAITING | RECORD |
| 139816129436888:1187:139816016597712   | 421291106147544 | Animals | NULL    | NULL       | IS            | GRANTED | TABLE  |
| 139816129436888:27:4:2:139816016594720 | 421291106147544 | Animals | PRIMARY | 'Aardvark' | S,REC_NOT_GAP | GRANTED | RECORD |
+----------------------------------------+-----------------+---------+---------+------------+---------------+---------+--------+
6 rows in set (0.00 sec)

当事务尝试修改数据库时，InnoDB 仅使用顺序事务 id。因此，之前的只读事务 id 从 421291106148352 变为 43260。

如果客户端 A 同时尝试更新 Birds 表中的一行，这将导致死锁：

mysql> UPDATE Birds SET value=40 WHERE name='Buzzard';
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

InnoDB 回滚导致死锁的事务。现在，来自客户端 B 的第一个更新可以继续进行。

信息模式包含死锁数量：

mysql> SELECT `count` FROM INFORMATION_SCHEMA.INNODB_METRICS
          WHERE NAME="lock_deadlocks";
+-------+
| count |
+-------+
|     1 |
+-------+
1 row in set (0.00 sec)

InnoDB 状态包含有关死锁和事务的以下信息。它还显示只读事务 id 421291106147544 变为顺序事务 id 43261。

mysql> SHOW ENGINE INNODB STATUS;
------------------------
LATEST DETECTED DEADLOCK
------------------------
2022-11-25 15:58:22 139815661168384
*** (1) TRANSACTION:
TRANSACTION 43260, ACTIVE 186 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1128, 2 row lock(s)
MySQL thread id 19, OS thread handle 139815619204864, query id 143 localhost u2 updating
UPDATE Animals SET value=30 WHERE name='Aardvark'

*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 28 page no 4 n bits 72 index PRIMARY of table `test`.`Birds` trx id 43260 lock mode S locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 7; hex 42757a7a617264; asc Buzzard;;
 1: len 6; hex 00000000a8fb; asc       ;;
 2: len 7; hex 82000000e40110; asc        ;;
 3: len 4; hex 80000014; asc     ;;

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 27 page no 4 n bits 72 index PRIMARY of table `test`.`Animals` trx id 43260 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 8; hex 416172647661726b; asc Aardvark;;
 1: len 6; hex 00000000a8f9; asc       ;;
 2: len 7; hex 82000000e20110; asc        ;;
 3: len 4; hex 8000000a; asc     ;;

*** (2) TRANSACTION:
TRANSACTION 43261, ACTIVE 209 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1128, 2 row lock(s)
MySQL thread id 18, OS thread handle 139815618148096, query id 146 localhost u1 updating
UPDATE Birds SET value=40 WHERE name='Buzzard'

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 27 page no 4 n bits 72 index PRIMARY of table `test`.`Animals` trx id 43261 lock mode S locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 8; hex 416172647661726b; asc Aardvark;;
 1: len 6; hex 00000000a8f9; asc       ;;
 2: len 7; hex 82000000e20110; asc        ;;
 3: len 4; hex 8000000a; asc     ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 28 page no 4 n bits 72 index PRIMARY of table `test`.`Birds` trx id 43261 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 7; hex 42757a7a617264; asc Buzzard;;
 1: len 6; hex 00000000a8fb; asc       ;;
 2: len 7; hex 82000000e40110; asc        ;;
 3: len 4; hex 80000014; asc     ;;

*** WE ROLL BACK TRANSACTION (2)
------------
TRANSACTIONS
------------
Trx id counter 43262
Purge done for trx's n:o < 43256 undo n:o < 0 state: running but idle
History list length 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421291106147544, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421291106146736, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421291106145928, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 43260, ACTIVE 219 sec
4 lock struct(s), heap size 1128, 2 row lock(s), undo log entries 1
MySQL thread id 19, OS thread handle 139815619204864, query id 143 localhost u2

错误日志包含有关事务和锁的信息：

mysql> SELECT @@log_error;
+---------------------+
| @@log_error         |
+---------------------+
| /var/log/mysqld.log |
+---------------------+
1 row in set (0.00 sec)

TRANSACTION 43260, ACTIVE 186 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1128, 2 row lock(s)
MySQL thread id 19, OS thread handle 139815619204864, query id 143 localhost u2 updating
UPDATE Animals SET value=30 WHERE name='Aardvark'
RECORD LOCKS space id 28 page no 4 n bits 72 index PRIMARY of table `test`.`Birds` trx id 43260 lock mode S locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 7; hex 42757a7a617264; asc Buzzard;;
 1: len 6; hex 00000000a8fb; asc       ;;
 2: len 7; hex 82000000e40110; asc        ;;
 3: len 4; hex 80000014; asc     ;;

RECORD LOCKS space id 27 page no 4 n bits 72 index PRIMARY of table `test`.`Animals` trx id 43260 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 8; hex 416172647661726b; asc Aardvark;;
 1: len 6; hex 00000000a8f9; asc       ;;
 2: len 7; hex 82000000e20110; asc        ;;
 3: len 4; hex 8000000a; asc     ;;

TRANSACTION 43261, ACTIVE 209 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1128, 2 row lock(s)
MySQL thread id 18, OS thread handle 139815618148096, query id 146 localhost u1 updating
UPDATE Birds SET value=40 WHERE name='Buzzard'
RECORD LOCKS space id 27 page no 4 n bits 72 index PRIMARY of table `test`.`Animals` trx id 43261 lock mode S locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 8; hex 416172647661726b; asc Aardvark;;
 1: len 6; hex 00000000a8f9; asc       ;;
 2: len 7; hex 82000000e20110; asc        ;;
 3: len 4; hex 8000000a; asc     ;;

RECORD LOCKS space id 28 page no 4 n bits 72 index PRIMARY of table `test`.`Birds` trx id 43261 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 7; hex 42757a7a617264; asc Buzzard;;
 1: len 6; hex 00000000a8fb; asc       ;;
 2: len 7; hex 82000000e40110; asc        ;;
 3: len 4; hex 80000014; asc     ;;

原文：dev.mysql.com/doc/refman/8.0/en/innodb-deadlock-detection.html

17.7.5.2 死锁检测

当启用死锁检测（默认情况下），InnoDB会自动检测事务死锁并回滚一个或多个事务以打破死锁。InnoDB尝试选择要回滚的小事务，事务的大小由插入、更新或删除的行数确定。

当innodb_table_locks = 1（默认）且autocommit = 0时，InnoDB会意识到表锁，并且 MySQL 层知道行级锁。否则，InnoDB无法检测到由 MySQL LOCK TABLES语句设置的表锁或由InnoDB之外的存储引擎设置的锁所涉及的死锁。通过设置innodb_lock_wait_timeout系统变量的值来解决这些情况。

如果InnoDB监视器输出的LATEST DETECTED DEADLOCK部分包含一条消息，指出在锁表等待图中搜索过深或过长，我们将回滚以下事务，则表示等待列表上的事务数量已达到 200 的限制。超过 200 个事务的等待列表被视为死锁，并且试图检查等待列表的事务将被回滚。如果锁定线程必须查看等待列表上的超过 1,000,000 个事务拥有的锁，则也可能发生相同的错误。

有关组织数据库操作以避免死锁的技术，请参阅第 17.7.5 节，“InnoDB 中的死锁”。

禁用死锁检测

在高并发系统中，死锁检测可能会导致大量线程等待同一锁时出现减速。有时，禁用死锁检测并依赖于innodb_lock_wait_timeout设置在死锁发生时进行事务回滚可能更有效。可以使用innodb_deadlock_detect变量禁用死锁检测。

原文：dev.mysql.com/doc/refman/8.0/en/innodb-deadlocks-handling.html

17.7.5.3 如何最小化和处理死锁

本节建立在关于死锁的概念信息之上，详见第 17.7.5.2 节，“死锁检测”。它解释了如何组织数据库操作以最小化死锁，并说明了应用程序中所需的后续错误处理。

死锁是事务性数据库中的经典问题，但除非它们频繁到您根本无法运行某些事务，否则它们并不危险。通常，您必须编写应用程序，以便它们始终准备好在由于死锁而回滚事务时重新发出该事务。

InnoDB使用自动的行级锁定。即使是仅插入或删除单行的事务，也可能发生死锁。这是因为这些操作并不真正“原子化”；它们会自动在插入或删除的行的（可能是多个）索引记录上设置锁。

您可以通过以下技术来应对死锁并减少其发生的可能性：

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

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

  • 如果由于死锁而事务失败，请随时准备重新发出该事务。死锁并不危险。只需再试一次。

  • 保持事务小且持续时间短，以减少碰撞的可能性。

  • 在进行一组相关更改后立即提交事务，以减少碰撞的可能性。特别是，不要将一个交互式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的方法是以SET 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 表级锁，必须使用超时方法来解决死锁。

17.7.6 事务调度

原文：dev.mysql.com/doc/refman/8.0/en/innodb-transaction-scheduling.html

InnoDB使用 Contenion-Aware Transaction Scheduling (CATS)算法来优先处理等待锁的事务。当多个事务等待同一对象上的锁时，CATS 算法确定哪个事务首先获得锁。

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

注意

在 MySQL 8.0.20 之前，InnoDB还使用先进先出（FIFO）算法来调度事务，并且 CATS 算法仅在锁争用严重时使用。MySQL 8.0.20 中的 CATS 算法增强使 FIFO 算法变得多余，允许其移除。MySQL 8.0.20 之后，由 FIFO 算法执行的事务调度由 CATS 算法执行。在某些情况下，此更改可能会影响事务获得锁的顺序。

您可以通过查询信息模式INNODB_TRX表中的TRX_SCHEDULE_WEIGHT列来查看事务调度权重。权重仅针对等待事务计算。等待事务是指处于LOCK WAIT事务执行状态的事务，如TRX_STATE列所报告的那样。不等待锁的事务报告 NULL 的TRX_SCHEDULE_WEIGHT值。

INNODB_METRICS提供了用于监视代码级事务调度事件的计数器。有关使用INNODB_METRICS计数器的信息，请参见第 17.15.6 节，“InnoDB INFORMATION_SCHEMA Metrics Table”。

  • lock_rec_release_attempts

    释放记录锁的尝试次数。一次尝试可能导致释放零个或多个记录锁，因为单个结构中可能存在零个或多个记录锁。

  • lock_rec_grant_attempts

    尝试授予记录锁的次数。一次尝试可能导致授予零个或多个记录锁。

  • lock_schedule_refreshes

    分析等待图以更新调度事务权重的次数。

17.8 InnoDB 配置

原文：dev.mysql.com/doc/refman/8.0/en/innodb-configuration.html

17.8.1 InnoDB 启动配置

17.8.2 配置 InnoDB 为只读操作

17.8.3 InnoDB 缓冲池配置

17.8.4 配置 InnoDB 的线程并发性

17.8.5 配置后台 InnoDB I/O 线程数量

17.8.6 在 Linux 上使用异步 I/O

17.8.7 配置 InnoDB 的 I/O 容量

17.8.8 配置自旋锁轮询

17.8.9 清理配置

17.8.10 配置 InnoDB 的优化器统计信息

17.8.11 配置索引页的合并阈值

17.8.12 启用专用 MySQL 服务器的自动配置

本节提供了有关InnoDB初始化、启动以及各种组件和特性的配置信息和流程。有关优化InnoDB表的数据库操作的信息，请参阅 Section 10.5, “Optimizing for InnoDB Tables”。

17.8.1 InnoDB 启动配置

原文：dev.mysql.com/doc/refman/8.0/en/innodb-init-startup-configuration.html

关于 InnoDB 配置的首要决策涉及数据文件、日志文件、页面大小和内存缓冲区的配置，这些应在初始化 InnoDB 之前配置。在初始化 InnoDB 后修改配置可能涉及非平凡的程序。

本节提供了有关在配置文件中指定 InnoDB 设置、查看 InnoDB 初始化信息和重要存储考虑事项的信息。

  • 在 MySQL 选项文件中指定选项

  • 查看 InnoDB 初始化信息

  • 重要的存储考虑

  • 系统表空间数据文件配置

  • InnoDB 双写缓冲文件配置

  • 重做日志配置

  • 撤销表空间配置

  • 全局临时表空间配置

  • 会话临时表空间配置

  • 页面大小配置

  • 内存配置

在 MySQL 选项文件中指定选项

因为 MySQL 使用数据文件、日志文件和页面大小设置来初始化 InnoDB，建议您在 MySQL 在启动时读取的选项文件中定义这些设置，以便在初始化 InnoDB 之前。通常情况下，当 MySQL 服务器首次启动时会初始化 InnoDB。

您可以将 InnoDB 选项放在服务器启动时读取的任何选项文件的 [mysqld] 组中。MySQL 选项文件的位置在 Section 6.2.2.2, “Using Option Files” 中有描述。

为了确保mysqld仅从特定文件（和mysqld-auto.cnf）中读取选项，请在启动服务器时将--defaults-file选项作为命令行中的第一个选项：

mysqld --defaults-file=*path_to_option_file*

查看 InnoDB 初始化信息

要查看启动期间的InnoDB初始化信息，请从命令提示符启动mysqld，这会将初始化信息打印到控制台。

例如，在 Windows 上，如果mysqld位于C:\Program Files\MySQL\MySQL Server 8.0\bin，可以这样启动 MySQL 服务器：

C:\> "C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqld" --console

在类 Unix 系统上，mysqld位于 MySQL 安装目录的bin目录中：

$> bin/mysqld --user=mysql &

如果您没有将服务器输出发送到控制台，请在启动后检查错误日志，查看在启动过程中打印的InnoDB初始化信息。

有关使用其他方法启动 MySQL 的信息，请参见 Section 2.9.5, “Starting and Stopping MySQL Automatically”。

注意

InnoDB在启动时不会打开所有用户表和相关数据文件。但是，InnoDB会检查数据字典中引用的表空间文件是否存在。如果找不到表空间文件，InnoDB会记录错误并继续启动序列。重做日志中引用的表空间文件可能在崩溃恢复期间打开以进行重做应用。

重要的存储考虑事项

在继续进行启动配置之前，请查看以下与存储相关的考虑事项。

  • 在某些情况下，通过将数据和日志文件放在不同的物理磁盘上，可以提高数据库性能。您还可以为InnoDB数据文件使用原始磁盘分区（原始设备），这可能加快 I/O 速度。请参阅 Using Raw Disk Partitions for the System Tablespace。

  • InnoDB是一个支持事务的（符合 ACID 标准）存储引擎，具有提交、回滚和崩溃恢复功能，以保护用户数据。但是，如果底层操作系统或硬件不按照宣传的方式工作，它就无法做到。许多操作系统或磁盘子系统可能会延迟或重新排序写操作以提高性能。在某些操作系统上，应该等待直到文件的所有未写入数据都已刷新的fsync()系统调用实际上可能会在数据刷新到稳定存储之前返回。由于这个原因，操作系统崩溃或停电可能会破坏最近提交的数据，或者在最坏的情况下，甚至损坏数据库，因为写操作已经被重新排序。如果数据完整性对您很重要，请在生产环境中使用任何内容之前执行“拔插头”测试。在 macOS 上，InnoDB使用一种特殊的fcntl()文件刷新方法。在 Linux 下，建议禁用写回缓存。

    在 ATA/SATA 硬盘驱动器上，像hdparm -W0 /dev/hda这样的命令可能可以禁用写回缓存。请注意，一些驱动器或磁盘控制器可能无法禁用写回缓存。

  • 关于保护用户数据的InnoDB恢复能力，InnoDB使用一种涉及名为双写缓冲区的结构的文件刷新技术，默认情况下启用（innodb_doublewrite=ON）。双写缓冲区在意外退出或停电后的恢复过程中增加了安全性，并通过减少大多数 Unix 系统上fsync()操作的需求来提高性能。如果您关心数据完整性或可能的故障，请保持innodb_doublewrite选项启用。有关双写缓冲区的信息，请参阅第 17.11.1 节，“InnoDB 磁盘 I/O”。

  • 在使用InnoDB与 NFS 之前，请查看使用 NFS 与 MySQL 中概述的潜在问题。

系统表空间数据文件配置

innodb_data_file_path选项定义了InnoDB系统表空间数据文件的名称、大小和属性。如果在初始化 MySQL 服务器之前未配置此选项，则默认行为是创建一个稍大于 12MB 的单个自动扩展数据文件，名称为ibdata1：

mysql> SHOW VARIABLES LIKE 'innodb_data_file_path';
+-----------------------+------------------------+
| Variable_name         | Value                  |
+-----------------------+------------------------+
| innodb_data_file_path | ibdata1:12M:autoextend |
+-----------------------+------------------------+

完整的数据文件规范语法包括文件名、文件大小、autoextend属性和max属性：

*file_name*:*file_size*[:autoextend[:max:*max_file_size*]]

文件大小以 KB、MB 或 GB 为单位指定，通过在大小值后附加K、M或G来实现。如果以 KB 为单位指定数据文件大小，请以 1024 的倍数进行。否则，KB 值将四舍五入到最近的兆字节（MB）边界。文件大小之和必须至少略大于 12MB。

您可以使用分号分隔的列表指定多个数据文件。例如：

[mysqld]
innodb_data_file_path=ibdata1:50M;ibdata2:50M:autoextend

autoextend和max属性只能用于指定的最后一个数据文件。

当指定autoextend属性时，数据文件会根据需要自动增加 64MB 的增量。innodb_autoextend_increment变量控制增量大小。

要为自动扩展数据文件指定最大大小，请在autoextend属性后使用max属性。只有在约束磁盘使用量至关重要的情况下才使用max属性。以下配置允许ibdata1增长到 500MB 的限制：

[mysqld]
innodb_data_file_path=ibdata1:12M:autoextend:max:500M

为第一个系统表空间数据文件强制执行最小文件大小，以确保有足够的空间用于双写缓冲区页。以下表显示了每个InnoDB页大小的最小文件大小。默认的InnoDB页大小为 16384（16KB）。

页大小（innodb_page_size）最小文件大小
16384 (16KB) 或更少3MB
32768 (32KB)6MB
65536 (64KB)12MB

如果您的磁盘已满，可以在另一个磁盘上添加数据文件。有关说明，请参阅调整系统表空间大小。

个别文件的大小限制由您的操作系统确定。在支持大文件的操作系统上，您可以将文件大小设置为超过 4GB。您还可以将原始磁盘分区用作数据文件。请参阅使用原始磁盘分区作为系统表空间。

InnoDB 不了解文件系统的最大文件大小，因此在最大文件大小为 2GB 等较小值的文件系统上要小心。

默认情况下，系统表空间文件在数据目录中创建（datadir）。要指定替代位置，请使用innodb_data_home_dir选项。例如，要在名为myibdata的目录中创建系统表空间数据文件，请使用以下配置：

[mysqld]
innodb_data_home_dir = /myibdata/
innodb_data_file_path=ibdata1:50M:autoextend

在为innodb_data_home_dir指定值时，需要添加尾随斜杠。InnoDB不会创建目录，因此请确保在启动服务器之前指定的目录存在。还要确保 MySQL 服务器具有在目录中创建文件的适当访问权限。

InnoDB通过将innodb_data_home_dir的值与数据文件名文本连接来形成每个数据文件的目录路径。如果未定义innodb_data_home_dir，默认值为“./”，即数据目录。（当 MySQL 服务器开始执行时，它会将当前工作目录更改为数据目录。）

或者，您可以为系统表空间数据文件指定绝对路径。以下配置与前述配置等效：

[mysqld]
innodb_data_file_path=/myibdata/ibdata1:50M:autoextend

当您为innodb_data_file_path指定绝对路径时，设置不会与innodb_data_home_dir设置连接。系统表空间文件将在指定的绝对路径中创建。在启动服务器之前，指定的目录必须存在。

InnoDB 双写缓冲区文件配置

自 MySQL 8.0.20 起，双写缓冲区存储区域位于双写文件中，这提供了关于双写页存储位置的灵活性。在先前的版本中，双写缓冲区存储区域位于系统表空间中。innodb_doublewrite_dir变量定义了InnoDB在启动时创建双写文件的目录。如果未指定目录，则双写文件将在innodb_data_home_dir目录中创建，如果未指定，则默认为数据目录。

若要在innodb_data_home_dir目录之外的位置创建双写文件，请配置innodb_doublewrite_dir变量。例如：

innodb_doublewrite_dir=*/path/to/doublewrite_directory*

其他双写缓冲区变量允许定义双写文件数量、每个线程的页面数量以及双写批量大小。有关双写缓冲区配置的更多信息，请参阅第 17.6.4 节，“双写缓冲区”。

重做日志配置

从 MySQL 8.0.30 开始，重做日志文件占用的磁盘空间量由innodb_redo_log_capacity变量控制，该变量可以在启动时或运行时设置；例如，要在选项文件中将变量设置为 8GB，请添加以下条目：

[mysqld]
innodb_redo_log_capacity = 8589934592

有关在运行时配置重做日志容量的信息，请参阅配置重做日志容量（MySQL 8.0.30 或更高版本）。

innodb_redo_log_capacity变量取代了已弃用的innodb_log_file_size和innodb_log_files_in_group变量。当定义了innodb_redo_log_capacity设置时，将忽略innodb_log_file_size和innodb_log_files_in_group设置；否则，这些设置用于计算innodb_redo_log_capacity设置（innodb_log_files_in_group * innodb_log_file_size = innodb_redo_log_capacity）。如果没有设置这些变量中的任何一个，innodb_redo_log_capacity将设置为默认值，即 104857600 字节（100MB）。最大设置为 128GB。

从 MySQL 8.0.30 开始，InnoDB尝试维护 32 个重做日志文件，每个文件大小为 1/32 * innodb_redo_log_capacity。重做日志文件位于数据目录中的#innodb_redo目录中，除非通过innodb_log_group_home_dir变量指定了不同的目录。如果定义了innodb_log_group_home_dir，则重做日志文件位于该目录中的#innodb_redo目录中。有关更多信息，请参见第 17.6.5 节，“重做日志”。

在 MySQL 8.0.30 之前，InnoDB默认在数据目录中创建两个 5MB 的重做日志文件，分别命名为ib_logfile0和ib_logfile1。您可以在初始化 MySQL 服务器实例时通过配置innodb_log_files_in_group和innodb_log_file_size变量来定义不同数量和大小的重做日志文件。

  • innodb_log_files_in_group定义了日志组中日志文件的数量。默认值和推荐值为 2。

  • innodb_log_file_size定义了日志组中每个日志文件的大小（以字节为单位）。组合日志文件大小（innodb_log_file_size * innodb_log_files_in_group）不能超过最大值，该值略小于 512GB。例如，一对 255GB 的日志文件接近极限但不超过。默认的日志文件大小为 48MB。通常，日志文件的组合大小应足够大，以使服务器能够平滑处理工作负载活动中的高峰和低谷，这通常意味着有足够的重做日志空间来处理超过一个小时的写入活动。较大的日志文件大小意味着在缓冲池中减少检查点刷新活动，从而减少磁盘 I/O。有关更多信息，请参见第 10.5.4 节，“优化 InnoDB 重做日志记录”。

innodb_log_group_home_dir定义了InnoDB日志文件的目录路径。您可以使用此选项将InnoDB重做日志文件放置在与InnoDB数据文件不同的物理存储位置，以避免潜在的 I/O 资源冲突；例如：

[mysqld]
innodb_log_group_home_dir = /dr3/iblogs

注意

InnoDB不会创建目录，因此在启动服务器之前确保日志目录存在。使用 Unix 或 DOS 的mkdir命令创建任何必要的目录。

确保 MySQL 服务器具有在日志目录中创建文件的适当访问权限。更一般地，服务器必须在需要创建文件的任何目录中具有访问权限。

撤销表空间配置

默认情况下，撤销日志驻留在 MySQL 实例初始化时创建的两个撤销表空间中。

innodb_undo_directory变量定义了InnoDB创建默认撤销表空间的路径。如果该变量未定义，则默认的撤销表空间将在数据目录中创建。innodb_undo_directory变量不是动态的。配置它需要重新启动服务器。

撤销日志的 I/O 模式使撤销表空间成为 SSD 存储的良好选择。

有关配置额外撤销表空间的信息，请参见第 17.6.3.4 节，“撤销表空间”。

全局临时表空间配置

全局临时表空间存储对用户创建的临时表所做更改的回滚段。

默认情况下，在innodb_data_home_dir目录中有一个名为ibtmp1的单个自动扩展全局临时表空间数据文件。初始文件大小略大于 12MB。

innodb_temp_data_file_path 选项指定了全局临时表空间数据文件的路径、文件名和文件大小。文件大小通过在大小值后附加 K、M 或 G 来指定为 KB、MB 或 GB。文件大小或组合文件大小必须略大于 12MB。

要指定全局临时表空间数据文件的替代位置，请在启动时配置 innodb_temp_data_file_path 选项。

会话临时表空间配置

在 MySQL 8.0.15 及更早版本中，会话临时表空间存储用户创建的临时表和由优化器创建的内部临时表，当 InnoDB 被配置为内部临时表的磁盘存储引擎时（internal_tmp_disk_storage_engine=InnoDB）。从 MySQL 8.0.16 开始，InnoDB 总是被用作内部临时表的磁盘存储引擎。

innodb_temp_tablespaces_dir 变量定义了 InnoDB 创建会话临时表空间的位置。默认位置是数据目录中的 #innodb_temp 目录。

要指定会话临时表空间的替代位置，请在启动时配置 innodb_temp_tablespaces_dir 变量。允许使用完全限定路径或相对于数据目录的路径。

页面大小配置

innodb_page_size 选项指定了 MySQL 实例中所有 InnoDB 表空间的页面大小。此值在实例创建时设置，并在此后保持不变。有效值为 64KB、32KB、16KB（默认值）、8KB 和 4KB。另外，您也可以按字节指定页面大小（65536、32768、16384、8192、4096）。

默认的 16KB 页面大小适用于各种工作负载，特别是涉及表扫描和涉及大量更新的 DML 操作的查询。对于涉及许多小写入的 OLTP 工作负载，较小的页面大小可能更有效，当单个页面包含许多行时，争用可能是一个问题。较小的页面对于通常使用小块大小的 SSD 存储设备也可能更有效。保持 InnoDB 页面大小接近存储设备块大小可以最小化被重写到磁盘的未更改数据量。

重要提示

innodb_page_size 只能在初始化数据目录时设置。有关此变量的更多信息，请参阅其描述。

内存配置

MySQL 为各种缓存和缓冲区分配内存以提高数据库操作的性能。在为InnoDB分配内存时，始终考虑操作系统所需的内存、分配给其他应用程序的内存以及为其他 MySQL 缓冲区和缓存分配的内存。例如，如果您使用MyISAM表，请考虑为键缓冲区（key_buffer_size）分配的内存量。有关 MySQL 缓冲区和缓存的概述，请参阅第 10.12.3.1 节，“MySQL 如何使用内存”。

InnoDB特定的缓冲区使用以下参数进行配置：

  • innodb_buffer_pool_size 定义了缓冲池的大小，这是一个内存区域，用于保存InnoDB表、索引和其他辅助缓冲区的缓存数据。缓冲池的大小对系统性能很重要，通常建议将innodb_buffer_pool_size 配置为系统内存的 50%到 75%。默认的缓冲池大小为 128MB。有关更多指导，请参阅第 10.12.3.1 节，“MySQL 如何使用内存”。有关如何配置InnoDB缓冲池大小的信息，请参阅第 17.8.3.1 节，“配置 InnoDB 缓冲池大小”。缓冲池大小可以在启动时或动态配置。

    在具有大量内存的系统上，可以通过将缓冲池划分为多个缓冲池实例来提高并发性。缓冲池实例的数量由innodb_buffer_pool_instances 选项控制。默认情况下，InnoDB创建一个缓冲池实例。缓冲池实例的数量可以在启动时配置。有关更多信息，请参阅第 17.8.3.2 节，“配置多个缓冲池实例”。

  • innodb_log_buffer_size 定义了InnoDB用于向磁盘上的日志文件写入的缓冲区大小。默认大小为 16MB。较大的日志缓冲区使得大型事务在提交之前可以运行而不必将日志写入磁盘。如果您有更新、插入或删除许多行的事务，可以考虑增加日志缓冲区的大小以节省磁盘 I/O。innodb_log_buffer_size 可以在启动时配置。有关相关信息，请参阅第 10.5.4 节，“优化 InnoDB 重做日志记录”。

警告

在 32 位 GNU/Linux x86 上，如果内存使用量设置过高，glibc可能允许进程堆增长超过线程堆栈，导致服务器失败。如果为mysqld进程分配的全局和每个线程的缓冲区和缓存接近或超过 2GB，这是一个风险。

可以使用类似于以下计算 MySQL 全局和每个线程内存分配的公式来估算 MySQL 内存使用量。您可能需要修改公式以考虑您的 MySQL 版本和配置中的缓冲区和缓存。有关 MySQL 缓冲区和缓存的概述，请参见第 10.12.3.1 节，“MySQL 如何使用内存”。

innodb_buffer_pool_size
+ key_buffer_size
+ max_connections*(sort_buffer_size+read_buffer_size+binlog_cache_size)
+ max_connections*2MB

每个线程使用一个堆栈（通常为 2MB，但由 Oracle Corporation 提供的 MySQL 二进制文件中仅为 256KB），在最坏的情况下还会使用sort_buffer_size + read_buffer_size额外的内存。

在 Linux 上，如果内核启用了大页支持，InnoDB可以使用大页来为其缓冲池分配内存。参见第 10.12.3.3 节，“启用大页支持”。

17.8.2 配置 InnoDB 为只读操作

原文：dev.mysql.com/doc/refman/8.0/en/innodb-read-only-instance.html

通过在服务器启动时启用--innodb-read-only配置选项，可以查询InnoDB表，其中 MySQL 数据目录位于只读介质上。

如何启用

为了准备一个实例进行只读操作，在将其存储在只读介质上之前，确保所有必要的信息都被刷新到数据文件中。运行服务器时禁用更改缓冲（innodb_change_buffering=0），并进行慢关闭。

要为整个 MySQL 实例启用只读模式，请在服务器启动时指定以下配置选项：

  • --innodb-read-only=1

  • 如果实例在只读介质上，如 DVD 或 CD，或者/var目录不可被所有用户写入：--pid-file=*path_on_writeable_media* 和 --event-scheduler=disabled

  • --innodb-temp-data-file-path。此选项指定了InnoDB临时表空间数据文件的路径、文件名和文件大小。默认设置为ibtmp1:12M:autoextend，这将在数据目录中创建ibtmp1临时表空间数据文件。为了准备一个实例进行只读操作，将innodb_temp_data_file_path设置为数据目录之外的位置。路径必须相对于数据目录。例如：

    --innodb-temp-data-file-path=../../../tmp/ibtmp1:12M:autoextend

从 MySQL 8.0 开始，启用innodb_read_only会阻止所有存储引擎的表创建和删除操作。这些操作修改了mysql系统数据库中的数据字典表，但这些表使用了InnoDB存储引擎，当启用innodb_read_only时无法修改。相同的限制也适用于任何修改数据字典表的操作，如ANALYZE TABLE 和 ALTER TABLE *tbl_name* ENGINE=*engine_name*。

此外，MySQL 8.0 中的mysql系统数据库中的其他表使用InnoDB存储引擎。将这些表设置为只读会导致对修改它们的操作的限制。例如，在只读模式下不允许CREATE USER、GRANT、REVOKE和INSTALL PLUGIN操作。

使用场景

这种操作模式适用于以下情况：

  • 在只读存储介质（如 DVD 或 CD）上分发 MySQL 应用程序或一组 MySQL 数据。

  • 多个同时查询相同数据目录的 MySQL 实例，通常在数据仓库配置中。您可以使用这种技术来避免在负载较重的 MySQL 实例中可能出现的瓶颈，或者您可以为各个实例使用不同的配置选项，为每个实例调整特定类型的查询。

  • 查询已被放入只读状态以确保安全性或数据完整性的数据，例如已存档的备份数据。

注意

此功能主要用于在分发和部署方面提供灵活性，而不是基于只读方面的原始性能。请参阅第 10.5.3 节，“优化 InnoDB 只读事务”以了解如何调整只读查询的性能，这不需要使整个服务器变为只读。

工作原理

当服务器通过--innodb-read-only选项以只读模式运行时，某些InnoDB功能和组件会减少或完全关闭：

  • 不进行更改缓冲，特别是不进行来自更改缓冲区的合并。为确保在准备实例进行只读操作时更改缓冲区为空，请禁用更改缓冲（innodb_change_buffering=0）并首先进行慢关闭。

  • 启动时没有崩溃恢复阶段。实例必须在被置于只读状态之前执行慢关闭。

  • 因为重做日志在只读操作中不被使用，您可以在将实例设置为只读之前将innodb_log_file_size设置为可能的最小大小（1 MB）。

  • 大多数后台线程被关闭。I/O 读线程保持开启，以及 I/O 写线程和用于写入临时文件的页面刷新协调器线程，这在只读模式下是允许的。一个缓冲池调整线程也保持活动状态，以便在线调整缓冲池大小。

  • 关于死锁、监视器输出等信息不会写入临时文件。因此，SHOW ENGINE INNODB STATUS不会产生任何输出。

  • 当服务器处于只读模式时，通常会更改写操作行为的配置选项设置更改不会产生任何效果。

  • 用于执行隔离级别的 MVCC 处理被关闭。所有查询读取记录的最新版本，因为更新和删除是不可能的。

  • 撤销日志未被使用。禁用innodb_undo_tablespaces和innodb_undo_directory配置选项的任何设置。

17.8.3 InnoDB 缓冲池配置

原文：dev.mysql.com/doc/refman/8.0/en/innodb-performance-buffer-pool.html

17.8.3.1 配置 InnoDB 缓冲池大小

17.8.3.2 配置多个缓冲池实例

17.8.3.3 使缓冲池具有扫描抵抗性

17.8.3.4 配置 InnoDB 缓冲池预取（预读）

17.8.3.5 配置缓冲池刷新

17.8.3.6 保存和恢复缓冲池状态

17.8.3.7 排除核心文件中的缓冲池页面

本节提供了InnoDB缓冲池的配置和调优信息。

原文：dev.mysql.com/doc/refman/8.0/en/innodb-buffer-pool-resize.html

17.8.3.1 配置 InnoDB 缓冲池大小

您可以在离线或服务器运行时配置InnoDB缓冲池大小。本节描述的行为适用于两种方法。有关在线配置缓冲池大小的更多信息，请参见在线配置 InnoDB 缓冲池大小。

当增加或减少innodb_buffer_pool_size时，操作是以块为单位进行的。块大小由innodb_buffer_pool_chunk_size配置选项定义，默认值为128M。有关更多信息，请参见配置 InnoDB 缓冲池块大小。

缓冲池大小必须始终等于或是innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances的倍数。如果您将innodb_buffer_pool_size配置为不等于或不是innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances的倍数的值，缓冲池大小将自动调整为等于或是innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances的倍数的值。

在以下示例中，innodb_buffer_pool_size设置为8G，innodb_buffer_pool_instances设置为16。innodb_buffer_pool_chunk_size为128M，这是默认值。

8G是一个有效的innodb_buffer_pool_size值，因为8G是innodb_buffer_pool_instances=16 * innodb_buffer_pool_chunk_size=128M的倍数，即2G。

$> mysqld --innodb-buffer-pool-size=8G --innodb-buffer-pool-instances=16

mysql> SELECT @@innodb_buffer_pool_size/1024/1024/1024;
+------------------------------------------+
| @@innodb_buffer_pool_size/1024/1024/1024 |
+------------------------------------------+
|                           8.000000000000 |
+------------------------------------------+

在这个例子中，innodb_buffer_pool_size 设置为 9G，而 innodb_buffer_pool_instances 设置为 16。innodb_buffer_pool_chunk_size 是 128M，这是默认值。在这种情况下，9G 不是 innodb_buffer_pool_instances=16 * innodb_buffer_pool_chunk_size=128M 的倍数，因此 innodb_buffer_pool_size 被调整为 10G，这是 innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances 的倍数。

$> mysqld --innodb-buffer-pool-size=9G --innodb-buffer-pool-instances=16

mysql> SELECT @@innodb_buffer_pool_size/1024/1024/1024;
+------------------------------------------+
| @@innodb_buffer_pool_size/1024/1024/1024 |
+------------------------------------------+
|                          10.000000000000 |
+------------------------------------------+
配置 InnoDB 缓冲池块大小

innodb_buffer_pool_chunk_size 可以以 1MB（1048576 字节）为单位增加或减少，但只能在启动时修改，在命令行字符串或 MySQL 配置文件中。

命令行：

$> mysqld --innodb-buffer-pool-chunk-size=134217728

配置文件：

[mysqld]
innodb_buffer_pool_chunk_size=134217728

当修改 innodb_buffer_pool_chunk_size 时，以下条件适用：

  • 如果新的 innodb_buffer_pool_chunk_size 值 * innodb_buffer_pool_instances 大于当前缓冲池大小在缓冲池初始化时，innodb_buffer_pool_chunk_size 被截断为 innodb_buffer_pool_size / innodb_buffer_pool_instances。

    例如，如果缓冲池初始化大小为 2GB（2147483648 字节），有 4 个缓冲池实例，以及 1GB（1073741824 字节）的块大小，块大小被截断为等于 innodb_buffer_pool_size / innodb_buffer_pool_instances 的值，如下所示：

    $> mysqld --innodb-buffer-pool-size=2147483648 --innodb-buffer-pool-instances=4
--innodb-buffer-pool-chunk-size=1073741824;

    mysql> SELECT @@innodb_buffer_pool_size;
+---------------------------+
| @@innodb_buffer_pool_size |
+---------------------------+
|                2147483648 |
+---------------------------+

mysql> SELECT @@innodb_buffer_pool_instances;
+--------------------------------+
| @@innodb_buffer_pool_instances |
+--------------------------------+
|                              4 |
+--------------------------------+

# Chunk size was set to 1GB (1073741824 bytes) on startup but was
# truncated to innodb_buffer_pool_size / innodb_buffer_pool_instances

mysql> SELECT @@innodb_buffer_pool_chunk_size;
+---------------------------------+
| @@innodb_buffer_pool_chunk_size |
+---------------------------------+
|                       536870912 |
+---------------------------------+

  • 缓冲池大小必须始终等于或是innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances的倍数。如果您更改innodb_buffer_pool_chunk_size，innodb_buffer_pool_size会自动调整为等于或是innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances的值。此调整发生在缓冲池初始化时。以下示例演示了这种行为：

    # The buffer pool has a default size of 128MB (134217728 bytes)

mysql> SELECT @@innodb_buffer_pool_size;
+---------------------------+
| @@innodb_buffer_pool_size |
+---------------------------+
|                 134217728 |
+---------------------------+

# The chunk size is also 128MB (134217728 bytes)

mysql> SELECT @@innodb_buffer_pool_chunk_size;
+---------------------------------+
| @@innodb_buffer_pool_chunk_size |
+---------------------------------+
|                       134217728 |
+---------------------------------+

# There is a single buffer pool instance

mysql> SELECT @@innodb_buffer_pool_instances;
+--------------------------------+
| @@innodb_buffer_pool_instances |
+--------------------------------+
|                              1 |
+--------------------------------+

# Chunk size is decreased by 1MB (1048576 bytes) at startup
# (134217728 - 1048576 = 133169152):

$> mysqld --innodb-buffer-pool-chunk-size=133169152

mysql> SELECT @@innodb_buffer_pool_chunk_size;
+---------------------------------+
| @@innodb_buffer_pool_chunk_size |
+---------------------------------+
|                       133169152 |
+---------------------------------+

# Buffer pool size increases from 134217728 to 266338304
# Buffer pool size is automatically adjusted to a value that is equal to
# or a multiple of innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances

mysql> SELECT @@innodb_buffer_pool_size;
+---------------------------+
| @@innodb_buffer_pool_size |
+---------------------------+
|                 266338304 |
+---------------------------+

    该示例演示了具有多个缓冲池实例的相同行为：

    # The buffer pool has a default size of 2GB (2147483648 bytes)

mysql> SELECT @@innodb_buffer_pool_size;
+---------------------------+
| @@innodb_buffer_pool_size |
+---------------------------+
|                2147483648 |
+---------------------------+

# The chunk size is .5 GB (536870912 bytes)

mysql> SELECT @@innodb_buffer_pool_chunk_size;
+---------------------------------+
| @@innodb_buffer_pool_chunk_size |
+---------------------------------+
|                       536870912 |
+---------------------------------+

# There are 4 buffer pool instances

mysql> SELECT @@innodb_buffer_pool_instances;
+--------------------------------+
| @@innodb_buffer_pool_instances |
+--------------------------------+
|                              4 |
+--------------------------------+

# Chunk size is decreased by 1MB (1048576 bytes) at startup
# (536870912 - 1048576 = 535822336):

$> mysqld --innodb-buffer-pool-chunk-size=535822336

mysql> SELECT @@innodb_buffer_pool_chunk_size;
+---------------------------------+
| @@innodb_buffer_pool_chunk_size |
+---------------------------------+
|                       535822336 |
+---------------------------------+

# Buffer pool size increases from 2147483648 to 4286578688
# Buffer pool size is automatically adjusted to a value that is equal to
# or a multiple of innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances

mysql> SELECT @@innodb_buffer_pool_size;
+---------------------------+
| @@innodb_buffer_pool_size |
+---------------------------+
|                4286578688 |
+---------------------------+

    当更改innodb_buffer_pool_chunk_size时需要小心，因为更改此值可能会增加缓冲池的大小，如上面的示例所示。在更改innodb_buffer_pool_chunk_size之前，请计算对innodb_buffer_pool_size的影响，以确保最终的缓冲池大小是可接受的。

注意

为避免潜在的性能问题，块数（innodb_buffer_pool_size / innodb_buffer_pool_chunk_size）不应超过 1000。

在线配置 InnoDB 缓冲池大小

innodb_buffer_pool_size配置选项可以使用SET语句动态设置，允许您调整缓冲池的大小而无需重新启动服务器。例如：

mysql> SET GLOBAL innodb_buffer_pool_size=402653184;

注意

缓冲池大小必须等于或是innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances的倍数。更改这些变量设置需要重新启动服务器。

在调整缓冲池大小之前，应完成通过 InnoDB API 执行的活动事务和操作。在启动调整操作时，操作直到所有活动事务完成后才开始。一旦调整操作正在进行中，需要访问缓冲池的新事务和操作必须等待调整操作完成。唯一的例外是，在减小缓冲池大小时，允许并发访问缓冲池，当缓冲池碎片整理并在减小缓冲池大小时撤回页面时。允许并发访问的缺点是，在页面被撤回时可能导致可用页面的暂时短缺。

注意

如果在缓冲池调整操作开始后启动嵌套事务，可能会失败。

监视在线缓冲池调整进度

Innodb_buffer_pool_resize_status 变量报告一个字符串值，指示缓冲池调整的进度；例如：

mysql> SHOW STATUS WHERE Variable_name='InnoDB_buffer_pool_resize_status';
+----------------------------------+----------------------------------+
| Variable_name                    | Value                            |
+----------------------------------+----------------------------------+
| Innodb_buffer_pool_resize_status | Resizing also other hash tables. |
+----------------------------------+----------------------------------+

从 MyQL 8.0.31 开始，您还可以使用 Innodb_buffer_pool_resize_status_code 和 Innodb_buffer_pool_resize_status_progress 状态变量监视在线缓冲池调整操作，这些变量报告数值，适合程序化监控。

Innodb_buffer_pool_resize_status_code 状态变量报告一个状态码，指示在线缓冲池调整操作的阶段。状态码包括：

  • 0: 没有正在进行的调整操作

  • 1: 开始调整大小

  • 2: 禁用 AHI（自适应哈希索引）

  • 3: 撤回块

  • 4: 获取全局锁

  • 5: 调整池

  • 6: 调整哈希

  • 7: 调整失败

Innodb_buffer_pool_resize_status_progress 状态变量报告一个百分比值，指示每个阶段的进度。百分比值在处理每个缓冲池实例后更新。当状态（由 Innodb_buffer_pool_resize_status_code 报告）从一个状态变为另一个状态时，百分比值将重置为 0。

以下查询返回一个字符串值，指示缓冲池调整的进度，一个代码，指示操作的当前阶段，以及该阶段的当前进度，表示为百分比值：

SELECT variable_name, variable_value 
 FROM performance_schema.global_status 
 WHERE LOWER(variable_name) LIKE "innodb_buffer_pool_resize%";

缓冲池调整进度也可以在服务器错误日志中看到。此示例显示了在增加缓冲池大小时记录的注释：

[Note] InnoDB: Resizing buffer pool from 134217728 to 4294967296. (unit=134217728)
[Note] InnoDB: disabled adaptive hash index.
[Note] InnoDB: buffer pool 0 : 31 chunks (253952 blocks) was added.
[Note] InnoDB: buffer pool 0 : hash tables were resized.
[Note] InnoDB: Resized hash tables at lock_sys, adaptive hash index, dictionary.
[Note] InnoDB: completed to resize buffer pool from 134217728 to 4294967296.
[Note] InnoDB: re-enabled adaptive hash index.

此示例显示了在减小缓冲池大小时记录的注释：

[Note] InnoDB: Resizing buffer pool from 4294967296 to 134217728. (unit=134217728)
[Note] InnoDB: disabled adaptive hash index.
[Note] InnoDB: buffer pool 0 : start to withdraw the last 253952 blocks.
[Note] InnoDB: buffer pool 0 : withdrew 253952 blocks from free list. tried to relocate 
0 pages. (253952/253952)
[Note] InnoDB: buffer pool 0 : withdrawn target 253952 blocks.
[Note] InnoDB: buffer pool 0 : 31 chunks (253952 blocks) was freed.
[Note] InnoDB: buffer pool 0 : hash tables were resized.
[Note] InnoDB: Resized hash tables at lock_sys, adaptive hash index, dictionary.
[Note] InnoDB: completed to resize buffer pool from 4294967296 to 134217728.
[Note] InnoDB: re-enabled adaptive hash index.

从 MySQL 8.0.31 开始，使用--log-error-verbosity=3启动服务器时，在在线缓冲池调整操作期间向错误日志记录额外信息。额外信息包括由Innodb_buffer_pool_resize_status_code报告的状态代码和由Innodb_buffer_pool_resize_status_progress报告的百分比进度值。

[Note] [MY-012398] [InnoDB] Requested to resize buffer pool. (new size: 1073741824 bytes)
[Note] [MY-013954] [InnoDB] Status code 1: Resizing buffer pool from 134217728 to 1073741824
(unit=134217728).
[Note] [MY-013953] [InnoDB] Status code 1: 100% complete
[Note] [MY-013952] [InnoDB] Status code 1: Completed
[Note] [MY-013954] [InnoDB] Status code 2: Disabling adaptive hash index.
[Note] [MY-011885] [InnoDB] disabled adaptive hash index.
[Note] [MY-013953] [InnoDB] Status code 2: 100% complete
[Note] [MY-013952] [InnoDB] Status code 2: Completed
[Note] [MY-013954] [InnoDB] Status code 3: Withdrawing blocks to be shrunken.
[Note] [MY-013953] [InnoDB] Status code 3: 100% complete
[Note] [MY-013952] [InnoDB] Status code 3: Completed
[Note] [MY-013954] [InnoDB] Status code 4: Latching whole of buffer pool.
[Note] [MY-013953] [InnoDB] Status code 4: 14% complete
[Note] [MY-013953] [InnoDB] Status code 4: 28% complete
[Note] [MY-013953] [InnoDB] Status code 4: 42% complete
[Note] [MY-013953] [InnoDB] Status code 4: 57% complete
[Note] [MY-013953] [InnoDB] Status code 4: 71% complete
[Note] [MY-013953] [InnoDB] Status code 4: 85% complete
[Note] [MY-013953] [InnoDB] Status code 4: 100% complete
[Note] [MY-013952] [InnoDB] Status code 4: Completed
[Note] [MY-013954] [InnoDB] Status code 5: Starting pool resize
[Note] [MY-013954] [InnoDB] Status code 5: buffer pool 0 : resizing with chunks 1 to 8.
[Note] [MY-011891] [InnoDB] buffer pool 0 : 7 chunks (57339 blocks) were added.
[Note] [MY-013953] [InnoDB] Status code 5: 100% complete
[Note] [MY-013952] [InnoDB] Status code 5: Completed
[Note] [MY-013954] [InnoDB] Status code 6: Resizing hash tables.
[Note] [MY-011892] [InnoDB] buffer pool 0 : hash tables were resized.
[Note] [MY-013953] [InnoDB] Status code 6: 100% complete
[Note] [MY-013954] [InnoDB] Status code 6: Resizing also other hash tables.
[Note] [MY-011893] [InnoDB] Resized hash tables at lock_sys, adaptive hash index, dictionary.
[Note] [MY-011894] [InnoDB] Completed to resize buffer pool from 134217728 to 1073741824.
[Note] [MY-011895] [InnoDB] Re-enabled adaptive hash index.
[Note] [MY-013952] [InnoDB] Status code 6: Completed
[Note] [MY-013954] [InnoDB] Status code 0: Completed resizing buffer pool at 220826  6:25:46.
[Note] [MY-013953] [InnoDB] Status code 0: 100% complete
在线缓冲池调整内部机制

调整操作由后台线程执行。当增加缓冲池大小时，调整操作：

  • 按块（块大小由innodb_buffer_pool_chunk_size定义）添加页面

  • 将哈希表、列表和指针转换为在内存中使用新地址

  • 将新页面添加到空闲列表中

在进行这些操作时，其他线程被阻止访问缓冲池。

当缩小缓冲池大小时，调整操作：

  • 对缓冲池进行碎片整理并撤回（释放）页面

  • 按块（块大小由innodb_buffer_pool_chunk_size定义）移除页面

  • 将哈希表、列表和指针转换为在内存中使用新地址

在这些操作中，只有对缓冲池进行碎片整理和撤回页面允许其他线程同时访问缓冲池。

avatar
文章
阅读
粉丝