AUTO-INC锁和AUTO_INCREMENT在InnoDB中处理方式

2016-12-15 阅读量

AUTO-INC Locks

An AUTO-INC lock is a special table-level lock taken by transactions inserting into tables with AUTO_INCREMENT columns. In the simplest case, if one transaction is inserting values into the table, any other transactions must wait to do their own inserts into that table, so that rows inserted by the first transaction receive consecutive primary key values.

The innodb_autoinc_lock_mode configuration option controls the algorithm used for auto-increment locking. It allows you to choose how to trade off between predictable sequences of auto-increment values and maximum concurrency for insert operations.

AUTO-INC锁是当向使用含有AUTO_INCREMENT列的表中插入数据时需要获取的一种特殊的表级锁
在最简单的情况下，如果一个事务正在向表中插入值，则任何其他事务必须等待对该表执行自己的插入操作，以便第一个事务插入的行的值是连续的。
innodb_autoinc_lock_mode配置选项控制用于自动增量锁定的算法。它允许您选择如何在可预测的自动递增值序列和插入操作的最大并发性之间进行权衡。

AUTO_INCREMENT Handling in InnoDB

InnoDB提供了一个可配置的锁定机制，可以显着提高使用AUTO_INCREMENT列向表中添加行的SQL语句的可伸缩性和性能。要对InnoDB表使用AUTO_INCREMENT机制，必须将AUTO_INCREMENT列定义为索引的一部分，以便可以对表执行相当于索引的SELECT MAX（ai_col）查找以获取最大列值。通常，这是通过使列成为某些表索引的第一列来实现的。

本节介绍AUTO_INCREMENT锁定模式的行为，对不同AUTO_INCREMENT锁定模式设置的使用含义，以及InnoDB如何初始化AUTO_INCREMENT计数器。

InnoDB AUTO_INCREMENT锁定模式
InnoDB AUTO_INCREMENT锁定模式使用含义
InnoDB AUTO_INCREMENT计数器初始化

InnoDB AUTO_INCREMENT锁定模式

本节介绍用于生成自动递增值的AUTO_INCREMENT锁定模式的行为，以及每种锁定模式如何影响复制。自动递增锁定模式在启动时使用innodb_autoinc_lock_mode配置参数进行配置。

以下术语用于描述innodb_autoinc_lock_mode设置:

“INSERT-like” statements(类INSERT语句)
所有可以向表中增加行的语句,包括INSERT, INSERT ... SELECT, REPLACE, REPLACE ... SELECT, and LOAD DATA.包括“simple-inserts”, “bulk-inserts”, and “mixed-mode” inserts.
“Simple inserts”
可以预先确定要插入的行数（当语句被初始处理时）的语句。这包括没有嵌套子查询的单行和多行INSERT和REPLACE语句，但不包括INSERT ... ON DUPLICATE KEY UPDATE。
“Bulk inserts”
事先不知道要插入的行数（和所需自动递增值的数量）的语句。这包括INSERT ... SELECT，REPLACE ... SELECT和LOAD DATA语句，但不包括纯INSERT。 InnoDB在处理每行时一次为AUTO_INCREMENT列分配一个新值。
“Mixed-mode inserts”
这些是“Simple inserts”语句但是指定一些（但不是全部）新行的自动递增值。示例如下，其中c1是表t1的AUTO_INCREMENT列：
INSERT INTO t1 (c1,c2) VALUES (1,'a'), (NULL,'b'), (5,'c'), (NULL,'d');

另一种类型的“Mixed-mode inserts”是INSERT ... ON DUPLICATE KEY UPDATE，其在最坏的情况下实际上是INSERT语句随后又跟了一个UPDATE，其中AUTO_INCREMENT列的分配值不一定会在 UPDATE 阶段使用

innodb_autoinc_lock_mode = 0 (“traditional” lock mode)
传统的锁定模式提供了在MySQL 5.1中引入innodb_autoinc_lock_mode配置参数之前存在的相同行为。传统的锁定模式选项用于向后兼容性，性能测试以及解决“Mixed-mode inserts”的问题，因为语义上可能存在差异。

在此锁定模式下，所有“INSERT-like”语句获得一个特殊的表级AUTO-INC锁，用于插入具有AUTO_INCREMENT列的表。此锁定通常保持到语句结束（不是事务结束），以确保为给定的INSERT语句序列以可预测和可重复的顺序分配自动递增值，并确保自动递增由任何给定语句分配的值是连续的。

SESSION_A>DROP TABLE IF EXISTS t;
Query OK, 0 rows affected (0.00 sec)

SESSION_A>CREATE TABLE t (a bigint unsigned auto_increment primary key) ENGINE=InnoDB;
Query OK, 0 rows affected (0.01 sec)

SESSION_A>insert into t values(1),(3),(4),(5),(6),(7);
Query OK, 6 rows affected (0.01 sec)
Records: 6  Duplicates: 0  Warnings: 0

SESSION_A>select * from t;
+---+
| a |
+---+
| 1 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
+---+
6 rows in set (0.00 sec)

SESSION_A>select @@innodb_autoinc_lock_mode;
+----------------------------+
| @@innodb_autoinc_lock_mode |
+----------------------------+
|                          0 |
+----------------------------+
1 row in set (0.00 sec)

A B C 三个会话事务隔离级别都是 RR
SESSION_A>select @@global.tx_isolation,@@session.tx_isolation;
+-----------------------+------------------------+
| @@global.tx_isolation | @@session.tx_isolation |
+-----------------------+------------------------+
| REPEATABLE-READ       | REPEATABLE-READ        |
+-----------------------+------------------------+

SESSION_A>begin;
Query OK, 0 rows affected (0.00 sec)

SESSION_A>delete from t where a>4;
Query OK, 3 rows affected (0.00 sec)

B会话被锁,这是由于会话 A 产生的 gap lock
SESSION_B>begin;
Query OK, 0 rows affected (0.00 sec)

SESSION_B>insert into t values(null); --注意这里因为是 null, 锁需要在内存中分配 AUTO-INCREMENT 值

C 会话被阻塞
SESSION_C>begin;
Query OK, 0 rows affected (0.00 sec)

SESSION_C>insert into t values(2);  --这里插入2,没有 gap lock 也被锁了

(mysql@localhost) [fandb]> (mysql@localhost) [fandb]> select trx_id,trx_state,trx_requested_lock_id,trx_weight,trx_mysql_thread_id,trx_query, trx_operation_state from information_schema.INNODB_TRX;
+--------+-----------+-----------------------+------------+---------------------+----------------------------+-----------------------+
| trx_id | trx_state | trx_requested_lock_id | trx_weight | trx_mysql_thread_id | trx_query                  | trx_operation_state   |
+--------+-----------+-----------------------+------------+---------------------+----------------------------+-----------------------+
| 321912 | LOCK WAIT | 321912:701            |          3 |                   7 | insert into t values(2)    | setting auto-inc lock |
| 321911 | LOCK WAIT | 321911:690:3:1        |          3 |                   2 | insert into t values(null) | inserting             |
| 321906 | RUNNING   | NULL                  |          5 |                   1 | NULL                       | NULL                  |
+--------+-----------+-----------------------+------------+---------------------+----------------------------+-----------------------+
3 rows in set (0.00 sec)
可以看到,SESSION_C是等待自增锁，一直处于setting auto-inc lock状态

(mysql@localhost) [fandb]> select * from information_schema.INNODB_LOCKS;
+----------------+-------------+-----------+-----------+-------------+------------+------------+-----------+----------+------------------------+
| lock_id        | lock_trx_id | lock_mode | lock_type | lock_table  | lock_index | lock_space | lock_page | lock_rec | lock_data              |
+----------------+-------------+-----------+-----------+-------------+------------+------------+-----------+----------+------------------------+
| 321912:701     | 321912      | AUTO_INC  | TABLE     | `fandb`.`t` | NULL       |       NULL |      NULL |     NULL | NULL                   |
| 321911:701     | 321911      | AUTO_INC  | TABLE     | `fandb`.`t` | NULL       |       NULL |      NULL |     NULL | NULL                   |
| 321911:690:3:1 | 321911      | X         | RECORD    | `fandb`.`t` | PRIMARY    |        690 |         3 |        1 | supremum pseudo-record |
| 321906:690:3:1 | 321906      | X         | RECORD    | `fandb`.`t` | PRIMARY    |        690 |         3 |        1 | supremum pseudo-record |
+----------------+-------------+-----------+-----------+-------------+------------+------------+-----------+----------+------------------------+
4 rows in set (0.00 sec)

在statement-based replication的情况下，这意味着当在从服务器上复制SQL语句时，自动增量列使用与主服务器上相同的值。多个INSERT语句的执行结果是确定性的，SLAVE再现与MASTER相同的数据。如果由多个INSERT语句生成的自动递增值交错，则两个并发INSERT语句的结果将是不确定的，并且不能使用基于语句的复制可靠地传播到从属服务器。

为了解释清楚,查看下面的例子:

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

假设有两个事务正在运行，每个事务都将行插入到具有AUTO_INCREMENT列的表中。一个事务正在使用插入1000行的INSERT … SELECT语句，另一个事务正在使用插入一行的“Simple inserts”语句:

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

InnoDB不能预先得知有多少行会从TX1的select部分获取到,所以在事务进行过程中,InnoDB一次只会为AUTO_INCREMENT列分配一个值.
通过一个表级锁的控制,保证了在同一时刻只有一个引用表t1的INSERT语句可以执行,直到整个INSERT语句结束,并且由不同语句生成自动递增数不会交错
由Tx1 INSERT ... SELECT语句生成的自动递增值将是连续的，并且Tx2中的INSERT语句使用的（单个）自动递增值将小于或大于用于Tx1的所有那些值，具体取决于那个语句先执行。

只要SQL语句在从二进制日志（当使用基于语句的复制或在恢复方案中）重放时以相同的顺序执行，结果将与Tx1和Tx2首次运行时的结果相同。因此，持续至语句结束的表级锁定( table-level locks)保证了在statement-based replication中对auto-increment列的插入数据的安全性. 但是，当多个事务同时执行insert语句时，这些表级锁定会限制并发性和可伸缩性。

在前面的示例中，如果没有表级锁，则Tx2中用于INSERT的自动递增列的值取决于语句执行的确切时间。如果Tx2的INSERT在Tx1的INSERT正在运行时（而不是在它开始之前或完成之后）执行，则由两个INSERT语句分配的特定自动递增值将是不确定的，并且可能每次运行都会得到不同的值

在连续锁定模式下，InnoDB可以避免为“Simple inserts”语句使用表级AUTO-INC锁，其中行数是预先已知的，并且仍然保留基于语句的复制的确定性执行和安全性。

如果不使用二进制日志作为恢复或复制的一部分来重放SQL语句，则可以使用interleaved lock模式来消除所有使用表级AUTO-INC锁，以实现更大的并发性和性能,其代价是由于并发的语句交错执行,同一语句生成的AUTO-INCREMENT值可能会产生GAP

innodb_autoinc_lock_mode = 1 (“consecutive” lock mode)
这是默认的锁定模式.在这个模式下,“bulk inserts”仍然使用AUTO-INC表级锁,并保持到语句结束.这适用于所有INSERT ... SELECT，REPLACE ... SELECT和LOAD DATA语句。同一时刻只有一个语句可以持有AUTO-INC锁.

“Simple inserts”（要插入的行数事先已知）通过在mutex（轻量锁）的控制下获得所需数量的自动递增值来避免表级AUTO-INC锁，它只在分配过程的持续时间内保持，而不是直到语句完成。不使用表级AUTO-INC锁，除非AUTO-INC锁由另一个事务保持。如果另一个事务保持AUTO-INC锁，则“简单插入”等待AUTO-INC锁，如同它是一个“批量插入”。

SESSION_A>DROP TABLE IF EXISTS t;
Query OK, 0 rows affected (0.01 sec)

SESSION_A>CREATE TABLE t (a bigint unsigned auto_increment primary key) ENGINE=InnoDB;
Query OK, 0 rows affected (0.01 sec)

SESSION_A>insert into t values(1),(3),(4),(5),(6),(7);
Query OK, 6 rows affected (0.01 sec)
Records: 6  Duplicates: 0  Warnings: 0

SESSION_A>select @@innodb_autoinc_lock_mode;
+----------------------------+
| @@innodb_autoinc_lock_mode |
+----------------------------+
|                          1 |
+----------------------------+
1 row in set (0.00 sec)

SESSION_A>select * from t;
+---+
| a |
+---+
| 1 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
+---+
6 rows in set (0.00 sec)

SESSION_A>begin;
Query OK, 0 rows affected (0.00 sec)

SESSION_A>delete from t where a>4;
Query OK, 3 rows affected (0.00 sec)

会话 B, 被 GAP LOCK 阻塞
SESSION_B>begin;
Query OK, 0 rows affected (0.00 sec)

SESSION_B>insert into t values(null); --由于是`simple-insert`且`innodb_autoinc_lock_mode=1`,所以并不需要AUTO-INC表级锁

会话 C 成功插入没有阻塞
SESSION_C>begin;
Query OK, 0 rows affected (0.00 sec)

SESSION_C>insert into t values(2); --由于它也是`simple-insert`且`innodb_autoinc_lock_mode=1`所以不需要获取AUTO-INC表级锁,没有阻塞成功插入
Query OK, 1 row affected (0.00 sec)

C会话rollback,B会话改为使用“Bulk inserts”
SESSION_C>rollback;
Query OK, 0 rows affected (0.00 sec)

SESSION_B>insert into t select null;

此时 C 会话又被阻塞了
SESSION_C>begin;
Query OK, 0 rows affected (0.00 sec)

SESSION_C>insert into t values(2); --这验证了官方文档中的说法`If another transaction holds an AUTO-INC lock, a “simple insert” waits for the AUTO-INC lock, as if it were a “bulk insert”.`
Query OK, 1 row affected (41.17 sec)

此锁定模式确保,当行数不预先知道的INSERT存在时(并且自动递增值在语句过程执行中分配)由任何“类INSERT”语句分配的所有自动递增值是连续的，并且对于基于语句的复制(statement-based replication)操作是安全的。

这种锁定模式显著地提高了可扩展性,并且保证了对于基于语句的复制(statement-based replication)的安全性.此外，与“传统”锁定模式一样，由任何给定语句分配的自动递增数字是连续的。与使用自动递增的任何语句的“传统”模式相比，语义没有变化.
但有一个特例:

The exception is for “mixed-mode inserts”, where the user provides explicit values for an AUTO_INCREMENT column for some, but not all, rows in a multiple-row “simple insert”. For such inserts, InnoDB allocates more auto-increment values than the number of rows to be inserted. However, all values automatically assigned are consecutively generated (and thus higher than) the auto-increment value generated by the most recently executed previous statement. “Excess” numbers are lost.

innodb_autoinc_lock_mode = 2 (“interleaved” lock mode)
在这种锁定模式下,所有类INSERT(“INSERT-like” )语句都不会使用表级AUTO-INC lock,并且可以同时执行多个语句。这是最快和最可扩展的锁定模式，但是当使用基于语句的复制或恢复方案时，从二进制日志重播SQL语句时，这是不安全的。

在此锁定模式下，自动递增值保证在所有并发执行的“类INSERT”语句中是唯一且单调递增的。但是，由于多个语句可以同时生成数字（即，跨语句交叉编号），为任何给定语句插入的行生成的值可能不是连续的。

如果执行的语句是“simple inserts”，其中要插入的行数已提前知道，则除了“混合模式插入”之外，为单个语句生成的数字不会有间隙。然而，当执行“批量插入”时，在由任何给定语句分配的自动递增值中可能存在间隙。

InnoDB AUTO_INCREMENT锁定模式使用含义

在复制环节中使用自增列
如果你在使用基于语句的复制(statement-based replication)请将innodb_autoinc_lock_mode设置为0或1，并在主从上使用相同的值。如果使用innodb_autoinc_lock_mode = 2（“interleaved”）或主从不使用相同的锁定模式的配置，自动递增值不能保证在从机上与主机上相同。

如果使用基于行的或混合模式的复制，则所有自动增量锁定模式都是安全的，因为基于行的复制对SQL语句的执行顺序不敏感（混合模式会在遇到不安全的语句是使用基于行的复制模式）。

“Lost” auto-increment values and sequence gaps
在所有锁定模式（0,1和2）中，如果生成自动递增值的事务回滚，那些自动递增值将“丢失”。一旦为自动增量列生成了值，无论是否完成“类似INSERT”语句以及包含事务是否回滚，都不能回滚。这种丢失的值不被重用。因此，存储在表的AUTO_INCREMENT列中的值可能存在间隙。
Specifying NULL or 0 for the AUTO_INCREMENT column
在所有锁定模式（0,1和2）中，如果用户在INSERT中为AUTO_INCREMENT列指定NULL或0，InnoDB会将该行视为未指定值，并为其生成新值。
为AUTO_INCREMENT列分配一个负值
在所有锁定模式（0,1和2）中，如果您为AUTO_INCREMENT列分配了一个负值，则不会定义自动增量机制的行为。
如果AUTO_INCREMENT值大于指定整数类型的最大整数
在所有锁定模式（0,1和2）中，如果值大于可以存储在指定整数类型中的最大整数，则不定义自动递增机制的行为。
Gaps in auto-increment values for “bulk inserts”
当innodb_autoinc_lock_mode设置为0（“traditional”）或1（“consecutive”）时,任何给定语句生成的自动递增值是连续的，没有间隙，因为表级AUTO-INC锁会持续到语句结束,并且一次只能执行一个这样的语句。

当innodb_autoinc_lock_mode设置为2（“interleaved”）时，在“bulk inserts”生成的自动递增值中可能存在间隙，但只有在并发执行“INSERT-Like”语句时才会产生这种情况。

对于锁定模式1或2，在连续语句之间可能出现间隙，因为对于批量插入，每个语句所需的自动递增值的确切数目可能不为人所知，并且可能进行过度估计。

select @@innodb_autoinc_lock_mode;
+----------------------------+
| @@innodb_autoinc_lock_mode |
+----------------------------+
|                          0 |
+----------------------------+

DROP TABLE IF EXISTS t;
CREATE TABLE t (a bigint unsigned auto_increment primary key) ENGINE=InnoDB SELECT NULL AS a;
/* #1 */ INSERT INTO t SELECT NULL FROM t;
/* #2 */ INSERT INTO t SELECT NULL FROM t;
/* #3 */ INSERT INTO t SELECT NULL FROM t;
/* #4 */ INSERT INTO t SELECT NULL FROM t;

SELECT * FROM t;
+----+
| a  |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |
|  6 |
|  7 |
|  8 |
|  9 |
| 10 |
| 11 |
| 12 |
| 13 |
| 14 |
| 15 |
| 16 |
+----+
当innodb_autoinc_lock_mode=0 类INSERT语句产生的自动递增值都是连续的

select @@innodb_autoinc_lock_mode;
+----------------------------+
| @@innodb_autoinc_lock_mode |
+----------------------------+
|                          1 |
+----------------------------+
1 row in set (0.00 sec)

DROP TABLE IF EXISTS t;
CREATE TABLE t (a bigint unsigned auto_increment primary key) ENGINE=InnoDB SELECT NULL AS a;
/* #1 */ INSERT INTO t SELECT NULL FROM t;
/* #2 */ INSERT INTO t SELECT NULL FROM t;
/* #3 */ INSERT INTO t SELECT NULL FROM t;
/* #4 */ INSERT INTO t SELECT NULL FROM t;

SELECT * FROM t;
+----+
| a  |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  6 |
|  7 |
|  8 |
|  9 |
| 13 |
| 14 |
| 15 |
| 16 |
| 17 |
| 18 |
| 19 |
| 20 |
+----+
出现了间隙gap, 5和10-12都没了,下面来解释产生这种情况的原因:
/* #1 */ 这是第一次INSERT,此时表中只有一行(创建表时的那一行),但是MySQL不知道有多少行.
然后MySQL Grab a chunk of auto_increment values 在chunk中有多少？ 一 只有一个,即'2',将其插入表中.
没有更多的行插入，所以一切完成。

/* #2 */ 这是第二次INSERT,此时表中有两行(1,2),但是MySQL不知道有多少行.
MySQL Grab a chunk of auto_increment values 在chunk中有多少？ 一 只有一个,即'3',将其插入表中.
还有需要插入的行,所以Grab another chunk,这次是前一次的两倍大小 在chunk中有多少？ 一 两个,'4'和'5'. 插入'4'.
没有更多的行插入，所以一切完成,'5'被舍弃,但是此时 AUTO_INCREMENT的下一个值是6了

/* #3 */这是第三次INSERT,此时表中有四行(1,2,3,4),但是MySQL不知道有多少行.
- Grab a chunk of auto_increment values. How many in the chunk? One - the value '6'. Insert it (one row inserted).
- Still more rows to insert. Grab another chunk, twice as big as before - two values, '7' and '8'. Insert them (three rows inserted).
- Still more rows to insert. Grab another chunk, twice as big as before - four values, '9', '10', '11', '12'. Insert the '9' (four rows inserted).
- No more rows to insert. Discard the left over '10', '11', and '12'.

#4: Insert as many rows as there are in the table (it's eight rows, but MySQL doesn't know that.)
- Grab a chunk of auto_increment values. How many in the chunk? One - the value '13'. Insert it (one row inserted).
- Still more rows to insert. Grab another chunk, twice as big as before - two values, '14' and '15'. Insert them (three rows inserted).
- Still more rows to insert. Grab another chunk, twice as big as before - four values, '16', '17', '18', '19'. Insert them (seven rows inserted).
- Still more rows to insert. Grab another chunk, twice as big as before - eight values, '20', '21', '22', ..., '27'. Insert the '20' (eight rows inserted).
- No more rows to insert. Discard the left over '21', '22', etc.

所以这就是 gap 产生的原因

由“mixed-mode inserts”分配的自动递增值
考虑一下场景,在“mixed-mode insert”中,其中一个“simple insert”语句指定了一些（但不是全部）行的AUTO-INCREMENT值。这样的语句在锁模式0,1和2中表现不同。例如，假设c1是表t1的AUTO_INCREMENT列，并且最近自动生成的序列号是100。
1
2
3
4
5
mysql> CREATE TABLE t1 (
-> c1 INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
-> c2 CHAR(1)
-> ) ENGINE = INNODB;
Now, consider the following “mixed-mode insert” statement:
1
mysql> INSERT INTO t1 (c1,c2) VALUES (1,'a'), (NULL,'b'), (5,'c'), (NULL,'d');
当innodb_autoinc_lock_mode=0时:
1
2
3
4
5
6
7
8
9
mysql> SELECT c1, c2 FROM t1 ORDER BY c2;
+-----+------+
| c1 | c2 |
+-----+------+
| 1 | a |
| 101 | b |
| 5 | c |
| 102 | d |
+-----+------+
下一个可用的auto-increment值103.因为innodb_autoinc_lock_mode=0时,auto-increment值一次只分配一个,而不是在开始时全部分配.不论是否有并发的其他类INSERT语句同时执行,都会是这样的结果

当innodb_autoinc_lock_mode=1时:

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

不同于innodb_autoinc_lock_mode=0时的情况,此时下一个可用的auto-increment值105,因为auto-increment值在语句一开始就分配了,分配了四个,但是只用了俩.不论是否有并发的其他类INSERT语句同时执行,都会是这样的结果

当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的具体值取决于通过并发执行语句生成的自动增量值的数量。

最后考虑下面的情况,当最近的 AUTO-INCREMENT 值为4时,执行下面的语句:

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

无论innodb_autoinc_lock_mode如何设置,都会报错duplicate-key error 23000 (Can’t write; duplicate key in table)
因为5已经分配给了(NULL, ‘b’),所以导致插入(5, ‘C’)时报错

在INSERT语句序列的中间修改AUTO_INCREMENT列值
在所有锁定模式（0,1和2）中，在INSERT语句序列中间修改AUTO_INCREMENT列值可能会导致duplicate key错误。

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

mysql> INSERT INTO t1 VALUES(0), (0), (3); -- 0 0分配两个值1,2. 手动指定3,则此时AUTO_INCREMENT为3,下一个值为4

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); --由于分配值为4,所以报错duplicate key
ERROR 1062 (23000): Duplicate entry '4' for key 'PRIMARY'

InnoDB AUTO_INCREMENT计数器初始化

本章节讨论 InnoDB如何初始化AUTO_INCREMENT计数器
如果你为一个Innodb表创建了一个AUTO_INCREMENT列,则InnoDB数据字典中的表句柄包含一个称为自动递增计数器的特殊计数器，用于为列分配新值。此计数器仅存在于内存中，而不存储在磁盘上。

要在服务器重新启动后初始化自动递增计数器，InnoDB将在首次插入行到包含AUTO_INCREMENT列的表时执行以下语句的等效语句。

1	SELECT MAX(ai_col) FROM table_name FOR UPDATE;

InnoDB增加语句检索的值，并将其分配给表和表的自动递增计数器。默认情况下，值增加1.此默认值可以由auto_increment_increment配置设置覆盖。

如果表为空，InnoDB使用值1.此默认值可以由auto_increment_offset配置设置覆盖。

如果在自动递增计数器初始化前使用SHOW TABLE STATUS语句查看表, InnoDB将初始化计数器值,但不会递增该值.这个值会储存起来以备之后的插入语句使用.这个初始化过程使用了一个普通的排它锁来读取表中自增列的最大值. InnoDB遵循相同的过程来初始化新创建的表的自动递增计数器。

在自动递增计数器初始化之后，如果您未明确指定AUTO_INCREMENT列的值，InnoDB会递增计数器并将新值分配给该列。如果插入显式指定列值的行，并且该值大于当前计数器值，则将计数器设置为指定的列值。

只要服务器运行，InnoDB就使用内存中自动递增计数器。当服务器停止并重新启动时，InnoDB会重新初始化每个表的计数器，以便对表进行第一次INSERT，如前所述。

服务器重新启动还会取消CREATE TABLE和ALTER TABLE语句中的AUTO_INCREMENT = N表选项的效果

AUTO-INC Locks

AUTO_INCREMENT Handling in InnoDB

InnoDB AUTO_INCREMENT锁定模式

innodb_autoinc_lock_mode = 0 (“traditional” lock mode)

innodb_autoinc_lock_mode = 1 (“consecutive” lock mode)

innodb_autoinc_lock_mode = 2 (“interleaved” lock mode)

InnoDB AUTO_INCREMENT锁定模式使用含义

InnoDB AUTO_INCREMENT计数器初始化