MySQL InnoDB AUTO_INCREMENT ( 下 )

前面两章节,我们介绍了 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 配置项的值设置为 01 ,而且主服务器和从服务器上的值必须相同。

如果设置 innodb_autoinc_lock_mode = 2,也就是使用 「 交错锁 」 模式,或者主从服务器上的值不相同,则不能确保从服务器上相同行的自增值和主服务器相同

如果你使用的是基于 ( row ) 或混合模式的主从复制,那么使用任何一个 AUTO_INCREMENT 锁模式都是安全的,因为基于行的复制对 SQL 语句的执行顺序不敏感。

交错锁 模式下,主从复制来说,任何基于语句的复制都是不安全的

注意: 混合模式使用的是基于行的复制方式。

「 丢失 」 ( lost ) 自增值和序列间隙

无论你使用的是哪种锁模式 ( 0 , 1 或 2 ),如果生成自增值的事务回滚,则这些自增量值将 「 丢失 」。

一旦为 AUTO_INCREMENT 列生成一个自增值后,无论 「 INSERT-like 」 语句是否完成,以及包含事务是否回滚,都无法回滚该自增值。

这些丢失的自增值不会被重复使用。因此,表的 AUTO_INCREMENT 列中的值可能存在间隙

AUTO_INCREMENT 列指定 NULL0 时候

无论使用哪种 AUTO_INCREMENT 锁模式 ( 0, 1 和 2 ),如果用户在INSERT 语句中为 AUTO_INCREMENT 列指定的值为 NULL0,那么 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 列是表 t1AUTO_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    |
+-----+------+

xy 的值是唯一的,并且比任何先前生成的行的都大。但是,xy 的具体值取决于同时执行语句生成的自增值的数量。如果没有其它 「 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 |
+----+

结束语

知识量好多有没有 ? 这次的文字简单明了,我就不做过多解释了...看完了你有啥感觉?

关于   |   FAQ   |   我们的愿景   |   广告投放   |  博客

  简单教程,简单编程 - IT 入门首选站

Copyright © 2013-2022 简单教程 twle.cn All Rights Reserved.