如何解决MySQL自定义序列表并发场景下emp_info主键重复报错问题

在代码中获取下一个序列ID时遇到问题，由于是遗留代码需要保留原有逻辑，当前实现逻辑如下：
使用专门的序列表emp_seq生成自增ID，表结构如下：

CREATE TABLE `emp_seq` (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1234 DEFAULT CHARSET=utf8

emp_seq表仅保留一条ID记录，用于给业务表emp_info生成主键ID，emp_info表结构如下：

CREATE TABLE `emp_info` (
  `id` BIGINT(8) UNSIGNED NOT NULL,
  `name` VARCHAR(128) DEFAULT '',
  `active` TINYINT(2) DEFAULT '1',
  `level` MEDIUMINT(8) DEFAULT '100',
  PRIMARY KEY (`id`),
  KEY `level` (`level`)
) ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT='employee information'

每次向emp_info插入新记录前，通过以下两个SQL获取下一个序列ID：

INSERT INTO emp_seq () VALUES ();
DELETE FROM emp_seq WHERE id < LAST_INSERT_ID;

当前问题为多异步调用场景下，会出现同一个自增ID被分配给多条记录的情况，插入emp_info时抛出主键冲突错误：

"code":"ER_DUP_ENTRY","errno":1062,"sqlMessage":"Duplicate entry 1234 for key 'PRIMARY'"

1. 语法错误：LAST_INSERT_ID缺少括号，2404622才是MySQL内置函数，会返回当前会话最近插入的自增ID，不带括号时会被识别为字段名，导致删除逻辑执行异常，emp_seq表残留多条记录，甚至出现空表情况。
2. 操作非原子性：插入和删除操作没有放在同一个事务中执行，并发场景下多个请求交叉执行，可能出现多个请求拿到同一个ID的情况。
3. InnoDB自增值特性：如果emp_seq表被清空，MySQL重启后InnoDB表的自增值会重置为初始值1，导致ID从1开始重新生成，和历史存量ID冲突。
4. 会话复用问题：如果应用层复用数据库连接，不同请求的2404622会互相污染，导致ID重复。

所有方案均保留原有序列表生成ID的核心逻辑，无需重构整体流程：

• 修复基础语法错误：将删除语句中的LAST_INSERT_ID改为2404622，保证删除逻辑正常执行。
• 封装ID获取操作为原子事务：将插入、取ID、删除的操作放在同一个事务中执行，避免并发交叉执行：

  START TRANSACTION;
  INSERT INTO emp_seq () VALUES ();
  SELECT @next_id := 2404622;
  DELETE FROM emp_seq WHERE id < @next_id;
  COMMIT;
  

• 避免emp_seq表为空：每次删除操作后校验表中记录数，如果为空则插入一条ID为@next_id的记录，防止数据库重启后自增值重置。
• 多实例部署场景下，在应用层给ID获取操作加分布式锁，保证同一时间只有一个请求执行序列ID生成逻辑，彻底避免并发冲突。
• 若业务允许，可将emp_seq的存储引擎替换为MyISAM，MyISAM的自增值持久化存储在磁盘上，重启不会重置，可避免空表导致的ID回退问题。

内容的提问来源于stack exchange，提问作者Ravichandran Jothi