MySQL重大Bug!自增主键竟然不是连续递增

更改表的存储引擎时，不适用于新存储引擎的表选项会保留在表定义，以便在必要时将具有先前定义选项的表恢复到原始存储引擎。例如，将存储引擎从 InnoDB 更改为 MyISAM 时，将保留 InnoDB 特定的选项，例如 ROW_FORMAT=COMPACT。

mysql> CREATE TABLE t1 (c1 INT PRIMARY KEY) ROW_FORMAT=COMPACT ENGINE=InnoDB;

mysql> ALTER TABLE t1 ENGINE=MyISAM;

mysql> SHOW CREATE TABLE t1\G

*************************** 1. row ***************************

Table: t1

Create Table: CREATE TABLE t1 (

c1 int(11) NOT NULL,

PRIMARY KEY (c1)

) ENGINE=MyISAM DEFAULT CHARSET=latin1 ROW_FORMAT=COMPACT

创建禁用严格模式的表时，若不支持指定的行格式，则使用存储引擎的默认行格式。表的实际行格式在 Row_format 列中报告，以响应

SHOW TABLE STATUS。 SHOW CREATE TABLE 显示在 CREATE TABLE 语句中指定的行格式。

AUTO_INCREMENT=2，表示下一次插入数据时，若需要自动生成自增值，会生成id=2。

这个输出结果容易引起误解：自增值是保存在表结构定义里的。实际上，表的结构定义存在.frm文件，但不会保存自增值。

自增值的保存策略

---

MyISAM

自增值保存在数据文件中。

InnoDB

自增值保存在内存，MySQL 8.0后，才有了“自增值持久化”能力，即才实现了“若重启，表的自增值可以恢复为MySQL重启前的值”，具体情况是：

≤5.7，自增值保存在内存，无持久化。每次重启后，第一次打开表时，都会去找自增值的最大值max(id)，然后将max(id)+1作为这个表当前的自增值。

若一个表当前数据行里最大的id是10，AUTO_INCREMENT=11。这时，我们删除id=10的行，AUTO_INCREMENT还是11。但若马上重启实例，重启后，该表的AUTO_INCREMENT就会变成10。

即MySQL重启可能会修改一个表的AUTO_INCREMENT值。

MySQL 8.0将自增值的变更记录在redo log，重启时依靠redo log恢复重启之前的值。

理解了MySQL对自增值的保存策略以后，我们再看看自增值修改机制。

自增值的修改策略

---

若字段id被定义为AUTO_INCREMENT，在插入一行数据时，自增值的行为如下：

1. 若插入数据时id字段指定为0、null 或未指定值，则把该表当前AUTO_INCREMENT值填到自增字段

2. 若插入数据时id字段指定了具体值，则使用语句里指定值

根据要插入的值和当前自增值大小关系，假设要插入值X，而当前自增值Y，若：

• X<Y，则该表的自增值不变

• X≥Y，把当前自增值修改为新自增值

自增值生成算法

从

• auto_increment_offset（自增的初始值）开始

• 以auto_increment_increment（步长）持续叠加

直到找到第一个大于X的值，作为新的自增值。

两个系统参数默认值都是1。

某些场景使用的就不全是默认值。比如，双M架构要求双写时，可能设置成auto_increment_increment=2，让一个库的自增id都是奇数，另一个库的自增id都是偶数，避免两个库生成的主键发生冲突。

所以，默认情况下，若准备插入的值≥当前自增值：

• 新自增值就是“准备插入的值+1”

• 否则，自增值不变

自增值的修改时机

=======================================================================

• 表t里面已有如下记录

再执行一条插入数据命令

该唯一键冲突的语句执行流程：

1. 执行器调用InnoDB引擎接口写入一行，传入的这一行的值是(0,1,1)

2. InnoDB发现用户没有指定自增id的值，获取表t当前的自增值2

3. 将传入的行的值改成(2,1,1)

4. 将表的自增值改成3

5. 继续执行插入数据(2,1,1)，由于已存在c=1，所以报Duplicate key error

6. 语句返回

该表的自增值已经改成3，是在真正执行插入数据之前。而该语句真正执行时，因唯一键冲突，所以id=2这行插入失败，但却没有将自增值改回去。

• 此后再成功插入新数据，拿到自增id就是3了

如你所见，自增主键不连续了！所以唯一键冲突是导致自增主键id不连续的一大原因。

事务回滚是二大原因。

为何现唯一键冲突或回滚时，MySQL不把自增值回退？

这么设计是为了提升性能。

假设有俩并行执行的事务，在申请自增值时，为避免两个事务申请到相同自增id，肯定要加锁，然后顺序申请。

假设事务 B 稍后于 A

| 事务A | 事务B |

| — | — |

| 申请到id=2 | |

| | 申请到id=3 |

| 此时表t的自增值4 | 此时表t的自增值4 |

| | 正确提交了 |

| 唯一键冲突 | |

若允许A把自增id回退，即把t的当前自增值改回2，则：表里已有id=3，而当前自增id值是2。

接下来，继续执行其它事务就会申请到id=2，然后再申请到id=3：报错“主键冲突”。

要解决该主键冲突，怎么办？

1. 每次申请id前，先判断表里是否已存该id。若存在，就跳过该id。但这样操作成本很高。因为申请id本来很快的，现在竟然还要人家再去主键索引树判断id是否存在

2. 把自增id的锁范围扩大，必须等到一个事务执行完成并提交，下一个事务才能再申请自增id。但这样锁的粒度太大，系统度大大下降！

低级的工程师想到的这些方案都会导致性能问题。之所以走进如此的怪圈，就因为“允许自增id回退”这个前提的存在。

所以InnoDB放弃这样的设计，语句即使执行失败了，也不回退自增id！

所以自增id只保证是递增的，但不保证是连续的！

自增锁的养成计划

=======================================================================

所以自增id的锁并非事务锁，而是每次申请完就马上释放，其它事务可以再申请。其实，在MySQL 5.1版本之前，并不是这样的。

MySQL 5.0时，自增锁的范围是语句级别：若一个语句申请了一个表自增锁，该锁会等语句执行结束以后才释放。显然，这样影响并发度

MySQL 5.1.22版本引入了一个新策略，新增参数innodb_autoinc_lock_mode，默认值1。该参数的值为0时，表示采用5.0的策略，设置为1时：

• 普通insert语句

申请后，马上释放；

• 类似insert … select 这样的批量插入语句

等语句结束后，才释放

设置为2时，所有的申请自增主键的动作都是申请后就释放锁。

为什么默认设置下的insert … select 偏偏要使用语句级锁？为什么该参数默认值不是2？

为了数据的一致性。

看个案例：批量插入数据的自增锁

| session1 | session2 |

| — | — |

| insert into t values (null, 2, 2);

insert into t values (null, 3, 3);

insert into t values (null, 4, 4);

| |

| | create table t2 like t; |

| insert into t values (null, 5, 5); | insert into t2(c,d) select c,d from t; |

若session2申请了自增值后，马上释放自增锁，则可能发生：

• session2先插入了两个记录，(1,1,1)、(2,2,2)

• 然后，session1来申请自增id得到id=3，插入（3,5,5)

• session2继续执行，插入两条记录(4,3,3)、 (5,4,4)

这好像也没关系吧，毕竟session 2语义本身就没有要求t2的所有行数据都和session1相同。

从数据逻辑角度看是对的。但若此时binlog_format=statement，binlog会怎么记录呢？

先看看 MySQL 此时的告警：

mysql> insert into t2(c,d) select c,d from t;

Query OK, 4 rows affected, 1 warning (0.01 sec)

Records: 4 Duplicates: 0 Warnings: 1

mysql> show warnings;

±------±-----±---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| Level | Code | Message |
自我介绍一下，小编13年上海交大毕业，曾经在小公司待过，也去过华为、OPPO等大厂，18年进入阿里一直到现在。

深知大多数Java工程师，想要提升技能，往往是自己摸索成长或者是报班学习，但对于培训机构动则几千的学费，着实压力不小。自己不成体系的自学效果低效又漫长，而且极易碰到天花板技术停滞不前！

因此收集整理了一份《2024年Java开发全套学习资料》，初衷也很简单，就是希望能够帮助到想自学提升又不知道该从何学起的朋友，同时减轻大家的负担。

既有适合小白学习的零基础资料，也有适合3年以上经验的小伙伴深入学习提升的进阶课程，基本涵盖了95%以上Java开发知识点，真正体系化！

由于文件比较大，这里只是将部分目录截图出来，每个节点里面都包含大厂面经、学习笔记、源码讲义、实战项目、讲解视频，并且会持续更新！

如果你觉得这些内容对你有帮助，可以扫码获取！！（备注Java获取）

Docker步步实践

目录文档：

①Docker简介

②基本概念

③安装Docker

④使用镜像：

⑤操作容器：

⑥访问仓库：

⑦数据管理：

⑧使用网络：

⑨高级网络配置：

⑩安全：

⑪底层实现：

⑫其他项目：

《互联网大厂面试真题解析、进阶开发核心学习笔记、全套讲解视频、实战项目源码讲义》点击传送门即可获取！

⑧使用网络：

[外链图片转存中…(img-pY6kTy2v-1713340338839)]

⑨高级网络配置：

[外链图片转存中…(img-sRpHnc21-1713340338839)]

⑩安全：

[外链图片转存中…(img-AP3H3QkK-1713340338839)]

⑪底层实现：

[外链图片转存中…(img-O3zIRJOH-1713340338840)]

⑫其他项目：

[外链图片转存中…(img-YoFYG5uQ-1713340338840)]

《互联网大厂面试真题解析、进阶开发核心学习笔记、全套讲解视频、实战项目源码讲义》点击传送门即可获取！