MySQL 问题案例：DDL 报错主键冲突

樱木...

已于 2024-02-28 16:53:37 修改

阅读量1.3k

点赞数

分类专栏： MySQL 文章标签： mysql 数据库 database linux

于 2022-10-30 11:15:46 首次发布

本文链接：https://blog.csdn.net/Tony_stark_L/article/details/127596879

版权

MySQL 专栏收录该内容

17 篇文章 0 订阅

订阅专栏

由于个人能力有限，文中可能存在错误，并且很多细节没有深入分析，欢迎批评指正。

文章目录

问题描述
问题分析
问题复现
测试结论
如何解决
补充

问题描述

在对 MySQL 数据库中的表执行 DDL 添加或删除字段时，遇到如下报错：

ERROR 1062 (23000): Duplicate entry ‘47071930’ for key ‘PRIMARY’

从错误信息来看是主键冲突，但查询对应报错的主键值 “47071930” 时，却发现并没有该条数据。

官网文档中也提到了这个问题，大概意思是在线执行 DDL 时，同时也有其他的线程在并发执行 DML 增量修改，这些 DML 操作会记录到 online log （row_log 对象，记录数据变更的增量）中，在 commit 阶段，再重放应用这些 DML 操作，期间可能存在重复数据，并引发唯一键值冲突。

When running an online DDL operation, the thread that runs the ALTER TABLE statement applies an online log of DML operations that were run concurrently on the same table from other connection threads. When the DML operations are applied, it is possible to encounter a duplicate key entry error (ERROR 1062 (23000): Duplicate entry), even if the duplicate entry is only temporary and would be reverted by a later entry in the online log. This is similar to the idea of a foreign key constraint check in InnoDB in which constraints must hold during a transaction.
– 引用自官方文档《Online DDL Limitations》，详情请见：https://dev.mysql.com/doc/refman/5.7/en/innodb-online-ddl-limitations.html

问题分析

该问题的发生需满足数据表中有唯一键索引，包括主键、唯一索引；
一般情况下，主键用的是跟业务无关的字段，如 id int(11) ，并带自增属性 AUTO_INCREMENT，在业务程序运行中就不需要再单独维护主键，正常写入表其他字段数据即可；
根据业务需求，部分字段会要求其唯一性，所以也会对某些字段添加唯一索引；
DDL 的同时存在并发执行的 DML 对相同的表进行增量修改操作。

问题复现

测试环境：
MySQL 版本：5.7.19

# 创建测试表 t_ddl_test
mysql> CREATE TABLE t_ddl_test (id int(11) NOT NULL AUTO_INCREMENT,c1 int(11) NOT NULL,c2 int(11) NOT NULL,c3 int(11) NOT NULL,PRIMARY KEY (id),UNIQUE KEY uk_c1 (c1));

# 创建存储过程 idata ，并往 t_ddl_test 表中插入1千万条数据
delimiter ;;
create procedure idata()
begin
  declare i int;
  set i=1;
  while (i<=10000000) do
    insert into t_ddl_test (c1,c2,c3) values (i, i, i);
    set i=i+1;
  end while;
end;;
delimiter ;

mysql> call idata();

# 查看数据
mysql> show create table t_ddl_test;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t_ddl_test | CREATE TABLE `t_ddl_test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `c1` int(11) NOT NULL,
  `c2` int(11) NOT NULL,
  `c3` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_c1` (`c1`)
) ENGINE=InnoDB AUTO_INCREMENT=10000001 DEFAULT CHARSET=utf8mb4 |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select count(*) from t_ddl_test;
+----------+
| count(*) |
+----------+
| 10000000 |
+----------+
1 row in set (2.37 sec)

mysql> select * from t_ddl_test order by id desc limit 1;
+----------+----------+----------+----------+
| id       | c1       | c2       | c3       |
+----------+----------+----------+----------+
| 10000000 | 10000000 | 10000000 | 10000000 |
+----------+----------+----------+----------+
1 row in set (0.00 sec)

备注：这里写入的数据较多，测试的时候可以考虑设置 sync_binlog、innodb_flush_log_at_trx_commit 参数值为 0。

接下来，创建两个连接，分别执行 DDL 和 DML，如下所示：

session 1	session 2
mysql> alter table t_ddl_test add column d1 int(11) not null default 0;
	mysql> insert into t_ddl_test (c1,c2,c3) values (33,22,44);
	ERROR 1062 (23000): Duplicate entry ‘33’ for key ‘uk_c1’
ERROR 1062 (23000): Duplicate entry ‘10000001’ for key ‘PRIMARY’

至此，成功复现该问题。同时，查询 “id=10000001”，发现并无该条数据，且表的自增值 AUTO_INCREMENT 已经改为 10000002 。

mysql> select * from t_ddl_test where id=10000001;
Empty set (0.00 sec)

mysql> show create table t_ddl_test;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t_ddl_test | CREATE TABLE `t_ddl_test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `c1` int(11) NOT NULL,
  `c2` int(11) NOT NULL,
  `c3` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_c1` (`c1`)
) ENGINE=InnoDB AUTO_INCREMENT=10000002 DEFAULT CHARSET=utf8mb4 |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

为什么自增值被修改了？其实这跟 MySQL 自增主键的设计有关。

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

如果插入数据时 id 字段指定为 0、null 或未指定值，那么就把这个表当前的 AUTO_INCREMENT 值填到自增字段；
如果插入数据时 id 字段指定了具体的值，就直接使用语句里指定的值。

当 session 2 执行写入操作时，表 t_ddl_test 前面已经通过存储过程写入了 (33,33,33,33) 这条数据。

# session 2 写入数据
mysql> insert into t_ddl_test (c1,c2,c3) values (33,22,44);
ERROR 1062 (23000): Duplicate entry '33' for key 'uk_c1'

对应 session 2 的执行流程就是：

执行器调用 InnoDB 引擎接口写入一行，传入的这一行的值是 (null,33,22,44);
InnoDB 发现用户没有指定自增 id 的值，获取表 t 当前的自增值 10000001；
将传入的行的值改成 (10000001,33,22,44);
将表的自增值改成 10000002；
继续执行插入数据操作，由于已经存在 c1=33 的记录，所以报 Duplicate key error，语句返回。

但需要注意的是，在出现唯一键冲突或者回滚的时候，MySQL 并不会把表的自增值改回去。

其实，MySQL 这么设计是为了提升性能。假设有两个并行执行的事务，在申请自增值的时候，为了避免两个事务申请到相同的自增 id，肯定要加锁，然后顺序申请。
1.假设事务 A 申请到了 id=2，事务 B 申请到 id=3，那么这时候表 t 的自增值是 4，之后继续执行。
2. 事务 B 正确提交了，但事务 A 出现了唯一键冲突。
3. 如果允许事务 A 把自增 id 回退，也就是把表 t 的当前自增值改回 2，那么就会出现这样的情况：表里面已经有 id=3 的行，而当前的自增 id 值是 2。
4. 接下来，继续执行的其他事务就会申请到 id=2，然后再申请到 id=3。这时，就会出现插入语句报错“主键冲突”。
而为了解决这个主键冲突，有两种方法：
1.每次申请 id 之前，先判断表里面是否已经存在这个 id。如果存在，就跳过这个 id。但是，这个方法的成本很高。因为，本来申请 id 是一个很快的操作，现在还要再去主键索引树上判断 id 是否存在。
2.把自增 id 的锁范围扩大，必须等到一个事务执行完成并提交，下一个事务才能再申请自增 id。这个方法的问题，就是锁的粒度太大，系统并发能力大大下降。
可见，这两个方法都会导致性能问题。造成这些麻烦的罪魁祸首，就是我们假设的这个“允许自增 id 回退”的前提导致的。
因此，InnoDB 放弃了这个设计，语句执行失败也不回退自增 id。也正是因为这样，所以才只保证了自增 id 是递增的，但不保证是连续的。
– 引用自林晓斌（丁奇）《MySQL 实战 45 讲》

测试结论

当执行 MySQL DDL 时，期间如有其他并发的 DML 对相同的表进行增量修改，比如 update、insert、insert into … on duplicate key、replace into 等，并且增量修改的数据违背唯一约束，那么 DDL 最后都会执行失败，报错主键冲突。

如何解决

重试 DDL；
修改 DDL 执行方式，add/drop column 等 DDL 语句默认采用 INPLACE 算法，可以在 DDL 语句中指定 ALGORITHM=COPY，但该过程中只允许查询，不允许写入。或者指定 LOCK=SHARED/EXCLUSIVE（共享锁/排他锁），前者只允许查询，后者不允许任何读写操作;
```
ALTER TABLE tb_name ADD column c1 int, ALGORITHM=COPY;
ALTER TABLE tb_name ADD column c1 int, LOCK=SHARED/EXCLUSIVE;
```
使用第三方工具 pt-osc 或者 gh-ost 执行在线 DDL。