自增主键不连续？了解下Mysql的innodb_autoinc_lock_mode

        Mysql是如何保证自增主键的单调递增属性的呢，那就要学习下 innodb_autoinc_lock_mode（自增锁模式）了

自增锁模式

Mysql中的配置项 innodb_autoinc_lock_mode

查看 innodb_autoinc_lock_mode 配置

-- 查看自增锁模式配置
show variables like 'innodb_autoinc_lock_mode';

设置innodb_autoinc_lock_mode配置

-- 设置配置
set global variable innodb_autoinc_lock_mode= 0\1\2;

在解释自增锁模式前要先明确四个基本术语 

定义四种术语

• “INSERT-like” statements
  • 所有在表内生成新行的语句，包括 insert、insert.....select, replace,  replace......select，LOAD_DATA，包括以下三类
• Simple inserts
  • 可以预先确定插入行数的，包括单行、多行 并且没有嵌套子查询的 INSERT 和 REPLACE 语句，但不包括 INSERT..... ON DUPLICATE KEY UPDATE
• Bulk inserts
  • 不可以预期插入的行数（需要申请的自增值的数量），包括 insert.....select, replace......select ，LOAD_DATA 语句，但是不包含普通INSERT，InnoDB会在每一行的执行中分配新的值给自增列
• Mixed-mode inserts

  • 指的是一些 Simple insert 语句给一些新行指定了自增值的。例如 c1 是 t1表中的自增列

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

    另一类 Mixed-mode insert 是 INSERT ..... ON DUPLICATE KEY UPDATE,  最坏的情况是 insert 后跟 update，在修改中可能会或不会使用自增列分配的值

三种自增锁

Mysql 8.0开始默认是 2，以前是1

1. innodb_autoinc_lock_mode = 0 (“traditional” lock mode)​​​​​​​ - 传统锁模式​​​​​​​​​​​​​​

   1. 此锁模式是Mysql在自增锁模式变量出现前就具备的行为，为了解决备份的一致性、性能测试、Mixed-mode问题（即自增值丢失的问题）
   2. 在插入自增列时所有的 insert-like 会获得一个表级锁。这个锁将被持有到语句结束（不是事务结束），以确保自增值被分配给指定的insert语句以可预测的、可重复的顺序，并且确保分配的自增值是连续的
   3. 在基于语句的复制情况下，副本服务通过语句复制，自增列会拥有和源服务器相同的值，多个insert执行结果是确定的，并且副本复制了原服务器相同数据

   4. CREATE TABLE t1 (
        c1 INT(11) NOT NULL AUTO_INCREMENT,
        c2 VARCHAR(10) DEFAULT NULL,
        PRIMARY KEY (c1)
      ) ENGINE=InnoDB;
      
      
      -- Tx1: 
      INSERT INTO t1 (c2) SELECT 1000 rows from another table ...
      -- Tx2: 
      INSERT INTO t1 (c2) VALUES ('xxx');

      • 基于上边的表，运行两个事务，分别插入自增列

      •  InnoDB无法预知 TX1 的select检索了多少行随着语句的进行，一次分配一个自增值，持有表锁直到语句结束。

      • 同一时刻只有一条insert 语句可以引用 t1表，因此不同语句自增值的生成顺序是不会被打乱的，所以TX1语句生成的自增值是连续的

      • TX2语句生成的自增值要么比TX1小要么大，取决于哪个语句先执行

      • 如果没有表锁，上边的例子中TX2的自增值依赖执行时间，如果正好在TX1运行中执行，则两个insert语句分配的自增值是不确定的

      • 只要SQL通过日志以相同的顺序执行（当用于基于语句的复制、系统恢复），结果就会与TX1、TX2首次执行的相同。

      • 因此，表级锁使得含有自增字段的语句在基于语句的复制场景下安全可靠，然而限制了多个事物执行insert的并发和伸缩性。​​​​​​​

2. ​​​​​​​innodb_autoinc_lock_mode = 1 (“consecutive” lock mode) - 连续锁模式

   1. ​​​​​​​此模式下， bulk-inserts使用表锁直到语句结束，同时只有一个语句持有表锁。如果源表和目标表不一致，则先在源表selected上加共享锁后对目标表加自增锁，如果是同一张表，择对所有selected加共享锁后加自增锁

   2. simple inserts（可预知行数的insert）通过持有所需要数量的自增值的互斥锁（轻量锁）来避免使用表锁，这个锁仅在分配过程中持有，不会持续到语句结束。
   3. 锁定模式确保insert在无法预期行数（在随着语句执行分配递增值的语句）所有递增值分配到insert like 语句是连续的，在基于SQL语句的备份操作下是安全的
   4. 当备份策略为语句时，此模式显著的提高了扩展性
   5. 例外情况，mix-mode inserts 的语句中，指定了自增列值的情况下的多行simple insert。此类语句，InnoDB会分配比实际需要多的自增值。会自动分配给最近执行的语句连续生成自增值，过量的会被丢弃

3. innodb_autoinc_lock_mode = 2 (“interleaved” lock mode) - 交叉锁模式

   1. ​​​​​​​​​​​​此模式下，insert-like 语句不会使用表锁，并且多条语句可以同时执行。这是最快和最具扩展性的模式，但是基于语句的备份模式和依赖sql语句的恢复是不安全的

   2. 保证所有正在执行的 insert-like 语句的自增值唯一、单调递增，因为允许多个语句同时生成自增值（分配自增值和语句是交叉的）为任一行语句生成的值可能是不连续的
   3. 仅有 simple-inserts 在已知影响行数的情况下，则为单个语句生成的值没有间隙，除了 mix-mode inserts
   4. bulk-inserts 语句生成的主键会存在间隙

接下来我们用一些例子来说明自增锁模式对自增值的影响

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

-- 假设当前 t1表的自增序列为 100
 
INSERT INTO t1 (c1,c2) VALUES (1,'a'), (NULL,'b'), (5,'c'), (NULL,'d');

-- mode = 0，且下一个自增序列是103
 SELECT c1, c2 FROM t1 ORDER BY c2;
+-----+------+
| c1  | c2   |
+-----+------+
|   1 | a    |
| 101 | b    |
|   5 | c    |
| 102 | d    |
+-----+------+

-- mode = 1，且下一个自增序列是105
SELECT c1, c2 FROM t1 ORDER BY c2;
+-----+------+
| c1  | c2   |
+-----+------+
|   1 | a    |
| 101 | b    |
|   5 | c    |
| 102 | d    |
+-----+------+


-- mode = 2，且下一个自增序列是103
SELECT c1, c2 FROM t1 ORDER BY c2;
+-----+------+
| c1  | c2   |
+-----+------+
|   1 | a    |
|   x | b    |
|   5 | c    |
|   y | d    |
+-----+------+


-- 典型
-- 对于任何 innodb_autoinc_lock_mode 设置，此语句都会生成重复键错误 23000 ( Can't write; duplicate key in table)，因为为该行分配了 101 并且该行的 (NULL, 'b')插入 (101, 'c')失败。
INSERT INTO t1 (c1,c2) VALUES (1,'a'), (NULL,'b'), (101,'c'), (NULL,'d');