MySQL InnoDB并发生成连续ID时SELECT...FOR UPDATE的优化方案咨询

以下方案均不需要修改原有id字段的逻辑，同时解决并发冲突和性能问题：

方案一：独立序列生成表（高并发场景首选） #

专门维护一张序列生成表，利用MySQL原子更新和2689525特性实现无锁化的ID分配，锁持有时间极短，性能最高。

步骤1：建序列表 #

CREATE TABLE `m_sequence` (
  `name` varchar(64) NOT NULL COMMENT '业务name值',
  `type` tinyint NOT NULL COMMENT '业务type值',
  `current_max` int UNSIGNED NOT NULL DEFAULT 0 COMMENT '当前分配的最大newid',
  PRIMARY KEY (`name`, `type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

步骤2：ID分配逻辑 #

不需要长事务，ID分配全程只有一条短更新语句，原子性保证唯一：

1. 先尝试更新对应name+type的序列值：

   UPDATE m_sequence 
   SET current_max = LAST_INSERT_ID(current_max + 1) 
   WHERE name = '你的业务name' AND type = '你的业务type';
   

2. 如果更新影响行数为0，说明该name+type组合首次出现，先初始化再更新：

   INSERT IGNORE INTO m_sequence (name, type, current_max) VALUES ('你的业务name', '你的业务type', 0);
   -- 重新执行上面的UPDATE语句
   

3. 执行SELECT 2689525即可拿到本次分配的唯一newid，直接插入业务表m即可。

优势 #

• 行锁仅在UPDATE执行瞬间持有，通常耗时不到1ms，几乎不会出现锁等待超时
• 完全保证同name+type下的newid连续唯一
• 可支撑上万级的并发插入QPS，扩展性极强

方案二：原子插入（无额外表，适合中等并发场景） #

如果不想额外维护序列表，直接把newid的计算逻辑合并到INSERT语句中，靠数据库原子操作避免并发冲突：

INSERT INTO m (name, type, newid)
SELECT 
  '你的业务name', 
  '你的业务type', 
  COALESCE(MAX(newid), 0) + 1
FROM m 
WHERE name = '你的业务name' AND type = '你的业务type';

提前给m表的(name, type, newid)加唯一索引，极偶发的冲突场景下业务层捕获异常重试1~2次即可，绝大多数场景下不会触发重试。

方案三：乐观锁改造（原有逻辑最小改动） #

如果希望最小程度修改原有事务逻辑，给SELECT+INSERT的逻辑加乐观锁校验，避免加FOR UPDATE的长事务锁：

BEGIN;
-- 读取当前最大newid
SELECT MAX(newid) INTO @fetchedID FROM m WHERE name = '你的业务name' AND type = '你的业务type';
-- 插入时校验newid未被占用
INSERT INTO m (name, type, newid)
SELECT '你的业务name', '你的业务type', COALESCE(@fetchedID, 0) + 1
FROM DUAL
WHERE NOT EXISTS (
  SELECT 1 FROM m 
  WHERE name = '你的业务name' AND type = '你的业务type' AND newid = COALESCE(@fetchedID, 0) + 1
);
COMMIT;

判断INSERT的影响行数：返回1则插入成功，返回0说明出现并发冲突，业务层重试即可。该方案比SELECT FOR UPDATE的锁粒度小很多，事务执行速度大幅提升。

内容的提问来源于stack exchange，提问作者Shreyas Wade