搜云库技术团队
文章永久连接：https://tech.souyunku.com/?p=5810
前面两章节，我们介绍了 MySQL Innodb AUTO_INCREMENT 锁的三种模式，分别为 「 传统模式 」 、 「 连续模式 」 、「 交错模式 」 ，这三种模式我们可以用同学聚会定餐馆来形象的描述下
1、 「 传统模式 」 – 老板，你家的店我包了，等到我同学聚会完了你再招待其它客人啊….其它客人：坑啊！ 你们快点吃啊，慢吞吞的…
2、 「 连续模式 」 – 老爸，我同学总共 50 个人，先给我来 50 个位置，有些同学可能没来，那就空着。剩下的位置，你可以招待其它的同学。有时候，如果不知道有多少同学 ( 健忘症犯了… )，就只能再回到传统模式了。
3、 「 交错模式 」 – 老板，我也不知道会来几个同学，反正，来了一个就坐一个位置，其它客人来了也可以坐 ( 假设老板家座位足够 )
这三种锁模式的优缺点是啥 ？
1、 「 传统模式 」 能保证所有的自增值是连续的，且不会浪费 ( 也就是会一直 123456789…自增下去，不会断掉 )，但其它的事务或语句要等到当前语句执行完才会继续执行
2、 「 交错模式 」解决了要 「 传统模式 」中要等待的问题，但也会引入新问题，就是可能造成自增值出现断层，比如 12345 缺了 4 这样。同时，在指定插入数据条数不确定的情况下，会回到 「 传统模式 」
3、 「 交错模式 」 解决了自增值断层的问题，但引入了自增值顺序混乱的问题，可能会导致自增值如下 13245687
交错模式在日常的 SELECT 语句中是不会出啥问题的，因为会按照自增值排序，出问题就处恢复数据或主从过程中的二进制日志回放，可能导致从库或者恢复的数据的自增值和源数据不一致。
好了，MySQL Innodb AUTO_INCREMENT 锁的三种模式我们就介绍到此了，接下来的部分我们讨论讨论下这三种模式在 MySQL Innodb 中的使用说明
MySQL Innodb AUTO_INCREMENT 锁的使用说明
主从复制中的 AUTO_INCREMENT
主从复制时，如果你使用的是基于 SQL 语句的复制，需要将 innodb_autoinc_lock_mode 配置项的值设置为 0 或 1 ，而且主服务器和从服务器上的值必须相同。
如果设置 innodb_autoinc_lock_mode = 2，也就是使用 「 交错锁 」 模式，或者主从服务器上的值不相同，则不能确保从服务器上相同行的自增值和主服务器相同
如果你使用的是基于 行 ( row ) 或混合模式的主从复制，那么使用任何一个 AUTO_INCREMENT 锁模式都是安全的，因为基于行的复制对 SQL 语句的执行顺序不敏感。
在 交错锁 模式下，主从复制来说，任何基于语句的复制都是不安全的
注意： 混合模式使用的是基于行的复制方式。
「 丢失 」 ( lost ) 自增值和序列间隙
无论你使用的是哪种锁模式 ( 0 , 1 或 2 )，如果生成自增值的事务回滚，则这些自增量值将 「 丢失 」。
一旦为 AUTO_INCREMENT 列生成一个自增值后，无论 「 INSERT-like 」 语句是否完成，以及包含事务是否回滚，都无法回滚该自增值。
这些丢失的自增值不会被重复使用。因此，表的 AUTO_INCREMENT 列中的值可能存在间隙
为 AUTO_INCREMENT 列指定 NULL 或 0 时候
无论使用哪种 AUTO_INCREMENT 锁模式 ( 0, 1 和 2 )，如果用户在INSERT 语句中为 AUTO_INCREMENT 列指定的值为 NULL 或 0，那么 Innodb 将会忽略这些值并为该列生成一个新的自增值。
也就是说，不管是否为 AUTO_INCREMENT 列指定了值为 NULL ，还是指定了值为 0 ，或是未指定，Innodb 都会自动生成一个自增值作为该列的值。
为 AUTO_INCREMENT 列指定一个负值 ( < 0 )
无论使用哪种 AUTO_INCREMENT 锁模式 ( 0, 1 和 2 )，如果用户在INSERT 语句中为 AUTO_INCREMENT 列指定的值为负数，那么 Innodb 为 AUTO_INCREMENT 生成的值就是不确定的 ( is not defined )
也就是说，如果指定了一个负数，那么当前插入行的 AUTO_INCREMENT 列的值就是不确定的 ( is not defined )
如果 AUTO_INCREMENT 列的值大于指定列数据类型 ( 一般是整数类型 ) 的最大整数
无论使用哪种 AUTO_INCREMENT 锁模式 ( 0, 1 和 2 )，如果 AUTO_INCREMENT 列的值变得大于可以存储在指定整数类型中的最大整数，则最终的结果也是不确定的 ( is not defined )
「 批量插入 」 中的自增值的间隙
在 innodb_autoinc_lock_mode 配置项的值设置为 0 ( 传统锁模式 ) 或 1 ( 连续锁模式 ) 时，任何给定语句生成的自增值都是连续的，没有间隙。
因为在这两个模式下，「 批量插入 」 会使用表级别的 AUTO-INC 锁，且会一直持有直到所有语句运行结束。在表级别的 AUTO-INC 锁控制下，一次只能执行一个这样的语句
在 innodb_autoinc_lock_mode 配置项的值设置为 2 ( 交错锁模式 ） 时，「 批量插入 」 生成的自增值可能存在间隙，仅有的出现前提是同时执行 「 INSERT-like 」 语句
对于锁模式 1 和 2 ，连续的语句间也可能出现间隙，因为批量插入，可能并不知道每个语句所需的确切数量的自增值，可能会存在高估，如果一旦高估了，那么高估的自增值将会被抛弃且永远也不会使用到。
「 混合模式插入 」 中的自增值
「 混合模式插入 」 中，那些 「 简单插入 」中的某些语句 ( 但不是全部 ) 可能显式的为 AUTO_INCREMENT 列指定了值。
如果真的发生了这种情况，三种锁模式下的结果是各不相同的。
例如，假设 c1 列是表 t1 的 AUTO_INCREMENT 列，且假设最新自动生成的序列号为 100
CREATE TABLE t1 (
c1 INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
c2 CHAR(1)
) ENGINE = INNODB;
现在，我们来看看下面这条混合模式插入语句
INSERT INTO t1 (c1,c2) VALUES (1,'a'), (NULL,'b'), (5,'c'), (NULL,'d');
在 innodb_autoinc_lock_mode 设置为 0 ( 传统锁模式下 )，刚刚插入的 4 条数据如下
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 ，也就是 「 连续锁模式 」，那么刚刚插入的 4 条数据如下
mysql> SELECT c1, c2 FROM t1 ORDER BY c2;
+-----+------+
| c1 | c2 |
+-----+------+
| 1 | a |
| 101 | b |
| 5 | c |
| 102 | d |
+-----+------+
虽然最终数据看起来一样。但是，行为结果还是不同的。
在这种情况下，下一个可用的自增值是 105，而不是 103，因为在处理语句时分配了四个自增值，但只使用了两个，也就是 103 和 104 永远的被丢失了。这时候无论是否同时并发的执行其它 「 INSERT-like 」 语句 ( 任何类型 )，此结果都是正确的
但如果设置 innodb_autoinc_lock_mode 的值为 2 ，也就是 「 交错锁模式 」，那么刚刚插入的 4 条数据如下
mysql> SELECT c1, c2 FROM t1 ORDER BY c2;
+-----+------+
| c1 | c2 |
+-----+------+
| 1 | a |
| x | b |
| 5 | c |
| y | d |
+-----+------+
x 和 y 的值是唯一的，并且比任何先前生成的行的都大。但是，x 和 y 的具体值取决于同时执行语句生成的自增值的数量。如果没有其它 「 INSERT-like 」语句并发执行，结果和 「 传统锁 」 模式是一样的。
最后，我们来看看下面这个 SQL 语句，假设最新自动生成的序列号为 100
INSERT INTO t1 (c1,c2) VALUES (1,'a'), (NULL,'b'), (101,'c'), (NULL,'d');
无论配置项 innodb_autoinc_lock_mode 的值设置为何种锁模式，该语句会抛出生成重复键错误 23000 ( Can’t write; duplicate key in table ) 。因为自增值 101 已经分配给了 (NULL,'b') ，这时候 (101,'c') 再使用 101 就会失败，当然了，前提是 AUTO_INCREMENT 列设置为主键或唯一索引
在一序列 INSERT 语句的中间修改 AUTO_INCREMENT 列值
在 MySQL 5.7 及更早版本中，在一序列 INSERT 语句的中间修改 AUTO_INCREMENT 列值可能会导致 「 重复条目 」 ( Duplicate entry ) 错误。例如，如果执行了将 AUTO_INCREMENT 列值更改为大于当前最大自增值的 UPDATE 操作，那些后续的未指定也未使用自增值的 INSERT 操作可能会遇到 「 重复条目 」 错误
在MySQL 8.0 及更高版本中，如果将 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 |
+----+
结束语
知识量好多有没有 ？ 这次的文字简单明了，我就不做过多解释了…看完了你有啥感觉？
干货推荐
附录：MySQL InnoDB 面试题：系列文章
- 一、MySQL 面试题 InnoDB 那些事 – 开篇
- 二、MySQL 面试题 InnoDB AUTO_INCREMENT ( 上 )
- 三、MySQL 面试题 InnoDB AUTO_INCREMENT ( 中 )
- 【当前读到】四、MySQL 面试题 InnoDB AUTO_INCREMENT ( 下 )
- 五、MySQL 面试题 InnoDB AUTO_INCREMENT ( 末 )
- 六、MySQL 面试题 InnoDB 聚簇索引和非聚簇索引
- 七、MySQL 面试题 InnoDB 索引的物理保存结构
- 八、MySQL 面试题 InnoDB 排序索引构建
- 九、MySQL 面试题 InnoDB 中的全文检索索引 ( 上 )
- 十、MySQL 面试题 InnoDB 中的全文检索索引 ( 中 )
- 十一、MySQL 面试题 InnoDB 中的全文索引 ( 下 )
- 十二、MySQL 面试题 InnoDB 创建 InnoDB 表
