MySQL的innodb表批量插入数据时，自增主键不连续原因和解决办法_多线程并发插入数据到一张自增id的mysql表,id不连续-优快云博客

本文链接：https://blog.youkuaiyun.com/wsx9172/article/details/144172515

问题说明

在mysql中造测试数据时，出现了主键不连续的情况

CREATE TABLE test  ( 
	id int NOT NULL AUTO_INCREMENT COMMENT 'ID', 
	name varchar(255) , 
	PRIMARY KEY (id) 
);

insert into test (name) VALUES ('A');

insert into test (name) select name from test; 
insert into test (name) select name from test; 
insert into test (name) select name from test; 
insert into test (name) select name from test;

select * from test;

结果：

`1  A`
`2  A`
`3  A`
`4  A`
`6  A`
`7  A`
`8  A`
`9  A`
`13 A`
`14 A`
`15 A`
`16 A`
`17 A`
`18 A`
`19 A`
`20 A`

5,10,11,12 丢失了

原因分析

经查询发现是innodb存储引擎有三种自增模式

innodb_autoinc_lock_mode 变量有三种可能的设置：0、1 或 2，分别对应“传统”（traditional）、“连续”（consecutive）和“交错”（interleaved）锁模式。在 MySQL 8.4 中，默认设置为交错锁模式（innodb_autoinc_lock_mode=2）。

SHOW VARIABLES LIKE 'innodb_autoinc_lock_mode';

Variable_name	Value
innodb_autoinc_lock_mode	2

交错锁模式是默认设置。

innodb_autoinc_lock_mode = 0（“传统”锁模式）

在此锁定模式下，所有类型的insert语句都会获得一个特殊的表级AUTO-INC锁，用于插入具有AUTO_INCREMENT列的表。即每当执行insert的时候，都会得到一个表级锁(AUTO-INC锁)，使得语句中生成的auto_increment为顺序，且在binlog中重放的时候，可以保证master与slave中数据的auto_increment是相同的。因为是表级锁，当在同一时间多个事务中执行insert的时候，对于AUTO-INC锁的争夺会限制并发能力。

innodb_autoinc_lock_mode = 1（“连续”锁模式）

在MySQL8.0之前，连续锁定模式是默认的。在这个模式下，“bulkinserts”仍然使用AUTO-INC表级锁，并保持到语句结束。这适用于所有INSERT…SELECT，REPLACE…SELECT和LOADDATA语句。同一时刻只有一个语句可以持有AUTO-INC锁。对于“Simpleinserts”（要插入的行数事先已知），则通过在mutex（轻量锁）的控制下获得所需数量的自动递增值来避免表级AUTO-INC锁，它只在分配过程的持续时间内保持，而不是直到语句完成。不使用表级AUTO-INC锁，除非AUTO-INC锁由另一个事务保持。如果另一个事务保持AUTO-INC锁，则“Simpleinserts”等待AUTO-INC锁，如同它是一个“bulkinserts”。在一个时间点，只能有一个语句持有 AUTO-INC 锁并执行。如果批量插入操作的源表和目标表不同，目标表上的 AUTO-INC 锁会在对源表的第一行加共享锁后获取。如果源表和目标表相同，AUTO-INC 锁会在对所有选定行加共享锁后获取。此锁模式确保，即使执行了插入语句，其中的自动增长值也是连续的，并且对于基于语句的复制是安全的。