“INSERT-like” 语句

所有生成新行的语句，包括 INSERT、INSERT ... SELECT、REPLACE、REPLACE ... SELECT 和 LOAD DATA。包括 “simple-inserts”、“bulk-inserts” 和 “mixed-mode” 插入。

“Simple inserts”

语句，其中可以在处理语句时确定要插入的行数（当语句最初被处理时）。这包括单行和多行 INSERT 和 REPLACE 语句，但不包括 INSERT ... ON DUPLICATE KEY UPDATE。

“Bulk inserts”

语句，其中要插入的行数（和所需的自动递增值数）在处理语句时不知道。这包括 INSERT ... SELECT、REPLACE ... SELECT 和 LOAD DATA 语句，但不包括普通 INSERT。InnoDB 在处理每行时分配新的自动递增值。

“混合模式插入”

这些是 “简单插入” 语句，它们指定了某些（但不是所有）新行的自动递增值。例如，假设 c1 是表 t1 的一个 AUTO_INCREMENT 列：

INSERT INTO t1 (c1,c2) VALUES (1,'a'), (NULL,'b'), (5,'c'), (NULL,'d');

另一种类型的 “混合模式插入” 是 INSERT ... ON DUPLICATE KEY UPDATE，在最坏的情况下，它实际上是一个 INSERT 语句，后跟一个 UPDATE 语句，其中分配的自动递增值可能或不可能在更新阶段使用。

innodb_autoinc_lock_mode = 0 (“传统” 锁模式)

传统锁模式提供了在 innodb_autoinc_lock_mode 变量引入之前存在的行为。传统锁模式选项是为了向后兼容、性能测试和解决“混合模式插入”问题的可能差异。

在这个锁模式下，所有 “INSERT-like” 语句都会获取一个特殊的表级 AUTO-INC 锁，以便插入具有 AUTO_INCREMENT 列的表。这个锁通常保持到语句的结束（而不是事务的结束），以确保自动递增值的分配是可预测的和可重复的，给定一系列 INSERT 语句，并确保自动递增值是连续的。

在基于语句的复制中，这意味着当 SQL 语句在副本服务器上复制时，自动递增列的值与源服务器上的值相同。执行多个 INSERT 语句的结果是确定性的，并且副本服务器上会重现与源服务器相同的数据。如果自动递增值生成的多个 INSERT 语句是交错的，那么两个并发 INSERT 语句的结果将是非确定性的，无法可靠地传播到使用基于语句的复制的副本服务器。

为了明确这一点，考虑一个使用以下表的示例：

CREATE TABLE t1 (
  c1 INT(11) NOT NULL AUTO_INCREMENT,
  c2 VARCHAR(10) DEFAULT NULL,
  PRIMARY KEY (c1)
) ENGINE=InnoDB;

假设有两个事务正在运行，每个事务都将行插入到具有 AUTO_INCREMENT 列的表中。一个事务使用 INSERT ... SELECT 语句插入 1000 行，而另一个事务使用简单的 INSERT 语句插入一行：

Tx1: INSERT INTO t1 (c2) SELECT 1000 rows from another table ...
Tx2: INSERT INTO t1 (c2) VALUES ('xxx');

InnoDB 无法预先知道从 SELECT 语句在 Tx1 中检索的行数，并且它会逐一分配自动递增值，因为语句执行。INSERT 语句在 Tx1 中执行时，表级锁将被持有到语句结束，只有一个 INSERT 语句可以执行，且不同语句生成的自动递增号码不会交叉。Tx1 中的 INSERT ... SELECT 语句生成的自动递增值是连续的，而 Tx2 中的 INSERT 语句使用的自动递增值要么小于 Tx1 中的所有值，要么大于 Tx1 中的所有值，取决于哪个语句首先执行。

只要 SQL 语句在从二进制日志中重放时执行顺序相同（在基于语句的复制或恢复场景中），结果将与 Tx1 和 Tx2 首次运行时相同。因此，表级锁直到语句结束使得 INSERT 语句使用自动递增安全地用于基于语句的复制。然而，这些表级锁限制了多个事务同时执行插入语句时的并发性和可扩展性。

在上面的示例中，如果没有表级锁，Tx2 中的 INSERT 语句使用的自动递增列值取决于语句执行的确切时间。如果 Tx2 中的 INSERT 语句在 Tx1 中的 INSERT 语句执行期间执行（而不是在开始或完成时），则两个 INSERT 语句分配的自动递增值是非确定性的，可能会从运行到运行变化。

在 连续 锁模式下，InnoDB 可以避免使用表级 AUTO-INC 锁对于已知行数的“简单插入”语句，并且仍然可以保留确定性的执行和基于语句的复制的安全性。

如果您不使用二进制日志来重放 SQL 语句作为恢复或复制的一部分，可以使用 交叉 锁模式，以消除所有表级 AUTO-INC 锁的使用，提高并发性和性能，但可能会在自动递增号码中留下间隙，并且可能会交叉执行语句分配的号码。

innodb_autoinc_lock_mode = 1 (“连续” 锁模式)

在此模式下，“批量插入” 使用特殊的 AUTO-INC 表级锁，并将其持有到语句结束。这适用于所有 INSERT ... SELECT、REPLACE ... SELECT 和 LOAD DATA 语句。只有一个持有 AUTO-INC 锁的语句可以执行。如果批量插入操作的源表与目标表不同，则在从源表选择第一行后，AUTO-INC 锁将被取在目标表上。如果批量插入操作的源表和目标表相同，则 AUTO-INC 锁将在所有选定的行上取共享锁后被取。

“简单插入” (对于已知提前要插入的行数) 避免表级 AUTO-INC 锁通过在互斥锁的控制下获取所需的自动递增值，该互斥锁仅在分配过程中持有，不到语句完成时。除非另一个事务持有 AUTO-INC 锁，否则不使用表级 AUTO-INC 锁。如果另一个事务持有 AUTO-INC 锁，则 “简单插入” 等待 AUTO-INC 锁，就像它是一个 “批量插入”。

这种锁模式确保，在存在 INSERT 语句的情况下，行数不确定（并且自动递增号码在语句进度中分配），所有自动递增值由任何 “INSERT-like” 语句分配的都是连续的，并且操作对于语句基于复制是安全的。

简言之，这种锁模式大大提高了可扩展性，同时对于语句基于复制是安全的。此外，与 “传统” 锁模式一样，任何语句分配的自动递增号码都是 连续的。与 “传统” 模式相比，对于任何使用自动递增的语句没有语义变化，除了一个重要的例外。

例外是对于 “混合模式插入”，其中用户为自动递增列提供明确值，但不是所有行的多行 “简单插入”。对于这种插入，InnoDB 分配的自动递增值比要插入的行数更多。但是，所有自动分配的值都是连续生成的（因此高于）前一个语句生成的自动递增值。“多余” 号码将丢失。

innodb_autoinc_lock_mode = 2 (“交叉” 锁模式)

在这种锁模式下，没有 “INSERT-like” 语句使用表级 AUTO-INC 锁，多个语句可以同时执行。这是最快和最可扩展的锁模式，但是在使用语句基于复制或恢复场景时不安全，SQL 语句从二进制日志中重放。

在这种锁模式下，自动递增值保证是唯一的和单调递增的，跨所有并发执行的 “INSERT-like” 语句。然而，因为多个语句可以同时生成号码（即号码分配是 交叉 的），因此任何给定语句插入的行的自动递增值可能不是连续的。

如果只有 “简单插入” 语句执行，其中行数是已知的，那么没有号码间隙，除了 “混合模式插入”。然而，当 “批量插入” 执行时，可能会出现自动递增值的间隙。

使用自动递增与复制

如果您使用基于语句的复制，请将 innodb_autoinc_lock_mode 设置为 0 或 1，并在源和副本上使用相同的值。自动递增值不能保证在副本上与源相同，如果您使用 innodb_autoinc_lock_mode = 2 (“交叉”) 或源和副本不使用相同的锁模式的配置。

如果您使用基于行的或混合格式的复制，则所有自动增量锁模式都是安全的，因为基于行的复制不敏感于 SQL 语句的执行顺序（而混合格式使用基于行的复制来处理任何不安全的语句）。

“丢失”的自动增量值和序列差距

在所有锁模式（0、1 和 2）中，如果生成自动增量值的事务回滚，那么这些自动增量值将被“丢失”。一旦生成了自动增量列的值，就不能回滚，无论是否完成了包含事务的 “INSERT-like” 语句，或者包含事务是否回滚。这样的丢失值不会被重用。因此，在表的自动增量列中可能会出现差距。

指定 NULL 或 0 为自动增量列

在所有锁模式（0、1 和 2）中，如果用户在 INSERT 中指定 NULL 或 0 为自动增量列，InnoDB 将该行视为未指定值，并生成一个新值。

将负值赋给自动增量列

在所有锁模式（0、1 和 2）中，如果将负值赋给自动增量列，自动增量机制的行为是未定义的。

如果自动增量值变得大于指定整数类型的最大整数

在所有锁模式（0、1 和 2）中，如果自动增量值变得大于指定整数类型的最大整数，自动增量机制的行为是未定义的。

批量插入的自动增量值差距

使用 innodb_autoinc_lock_mode 设置为 0 (“传统”) 或 1 (“连续”)，生成的自动增量值是连续的，没有差距，因为表级 AUTO-INC 锁直到语句结束，并且只有一个语句可以执行。

使用 innodb_autoinc_lock_mode 设置为 2 (“交叉”)，可能会在批量插入中出现自动增量值差距，但只有在并发执行 “INSERT-like” 语句时。

对于锁模式 1 或 2，可能会在连续语句之间出现差距，因为批量插入的确切自动增量值需求可能不知道，并且可能会过高估计。

混合模式插入分配的自动增量值

考虑一个 “混合模式插入,”其中一个 “简单插入” 指定了一些（但不是所有）结果行的自动增量值。这样的语句在锁模式 0、1 和 2 中表现不同。例如，假设 c1 是表 t1 的自动增量列，并且最近生成的自动增量序列号是 100。

mysql> CREATE TABLE t1 (
    -> c1 INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, 
    -> c2 CHAR(1)
    -> ) ENGINE = INNODB;

现在，考虑以下 “混合模式插入” 语句：

mysql> INSERT INTO t1 (c1,c2) VALUES (1,'a'), (NULL,'b'), (5,'c'), (NULL,'d');

使用 innodb_autoinc_lock_mode 设置为 0 (“传统”)，四个新行是：

mysql> SELECT c1, c2 FROM t1 ORDER BY c2;
+-----+------+
| c1  | c2   |
+-----+------+
|   1 | a    |
| 101 | b    |
|   5 | c    |
| 102 | d    |
+-----+------+

下一个可用的自动增量值是 103，因为自动增量值是一次分配的，而不是在语句执行开始时一次分配的。这结果是正确的，无论是否有并发执行 “INSERT-like” 语句（任何类型）。

使用 innodb_autoinc_lock_mode 设置为 1 (“连续”)，四个新行也是：

mysql> SELECT c1, c2 FROM t1 ORDER BY c2;
+-----+------+
| c1  | c2   |
+-----+------+
|   1 | a    |
| 101 | b    |
|   5 | c    |
| 102 | d    |
+-----+------+

然而，在这种情况下，下一个可用的自动递增值是 105，而不是 103，因为四个自动递增值是在语句处理时分配的，但只有两个被使用。这结果是正确的，无论是否有并发执行INSERT-like 语句（任何类型）.

使用innodb_autoinc_lock_mode设置为 2（““交错””），四个新行是：

mysql> SELECT c1, c2 FROM t1 ORDER BY c2;
+-----+------+
| c1  | c2   |
+-----+------+
|   1 | a    |
|   x | b    |
|   5 | c    |
|   y | d    |
+-----+------+

值 x 和 y 是唯一的且大于以前生成的行。然而，x 和 y 的具体值取决于并发执行语句生成的自动递增值的数量。

最后，考虑以下语句，在最近生成的序列号为 100 时发出：

mysql> INSERT INTO t1 (c1,c2) VALUES (1,'a'), (NULL,'b'), (101,'c'), (NULL,'d');

无论innodb_autoinc_lock_mode设置如何，这个语句都会生成重复键错误 23000 (无法写入；表中的重复键），因为 101 被分配给行 (NULL, 'b')，并且插入行 (101, 'c') 失败。

修改 AUTO_INCREMENT 列值在一系列 INSERT 语句中

如果您修改 AUTO_INCREMENT 列值为当前最大自动递增值以上的值，新的值将被持久化，并且后续 INSERT 操作将从新的、较大的值开始分配自动递增值。这个行为在以下示例中被演示：

mysql> CREATE TABLE t1 (
    -> c1 INT NOT NULL AUTO_INCREMENT,
    -> PRIMARY KEY (c1)
    ->  ) ENGINE = InnoDB;

mysql> INSERT INTO t1 VALUES(0), (0), (3);

mysql> SELECT c1 FROM t1;
+----+
| c1 |
+----+
|  1 |
|  2 |
|  3 |
+----+

mysql> UPDATE t1 SET c1 = 4 WHERE c1 = 1;

mysql> SELECT c1 FROM t1;
+----+
| c1 |
+----+
|  2 |
|  3 |
|  4 |
+----+

mysql> INSERT INTO t1 VALUES(0);

mysql> SELECT c1 FROM t1;
+----+
| c1 |
+----+
|  2 |
|  3 |
|  4 |
|  5 |
+----+