MySQL 8 事务性批量选取code_id为0的行并分配不同值的并发实现

核心实现逻辑 #

要满足「并发线程不会抢占同一未分配行、按顺序为选中行匹配传入的编码值」的要求，核心是在同一个事务内，通过带排他行锁的查询先锁定待操作行，再完成批量更新，全程不能出现无锁的读操作。

前置必做优化 #

当前表结构没有针对code_id查询条件的索引，直接按code_id=0筛选会触发全表扫描，加锁范围过大极易出现死锁、并发性能暴跌的问题，先补充适配查询场景的覆盖索引：

ALTER TABLE test_codes ADD INDEX idx_code_segment (code_id, segment_id);

事务内执行流程 #

整个分配逻辑必须包裹在一个数据库事务中执行，拆分两步操作：

1. 锁定指定数量的可用行
   利用MySQL 8原生支持的FOR UPDATE SKIP LOCKED语法查询未分配的行，该语法的特性可以完全适配需求：

   • 以当前读模式查询，对匹配到的行加行级排他锁，其他并发事务对这些行的读、写操作都会被阻塞，完全无法抢占
   • 自动跳过已经被其他事务加锁的行，避免当前事务陷入长时间锁等待
   • 按固定规则排序，保证选出来的行顺序稳定，和传入的编码数组一一对应

   示例SQL（假设需要选4行，和编码数组[1,200,10,18]的长度匹配）：

   START TRANSACTION;
   
   -- 按id升序锁定4条code_id=0的可用行，自动跳过其他事务锁占的行
   SELECT id
   FROM test_codes
   WHERE code_id = 0
     -- 如果业务是按segment维度独立分配编码，可在此处补充 segment_id = 目标段ID 的过滤条件
   ORDER BY id ASC
   LIMIT 4
   FOR UPDATE SKIP LOCKED;
   

   执行这一步后，如果返回的id数量小于需要的编码长度（比如需要4行但只查到3行），直接执行ROLLBACK回滚，终止本次分配，不要强行凑数更新。将拿到的id列表按返回顺序记为[id1, id2, id3, id4]，和传入的编码数组一一对应。

2. 批量顺序更新行的code_id
   拿到有序的id列表后，用CASE WHEN结构批量更新，保证每个id对应固定的编码值，顺序完全匹配：

   UPDATE test_codes
   SET code_id = CASE id
     WHEN id1 THEN 1
     WHEN id2 THEN 200
     WHEN id3 THEN 10
     WHEN id4 THEN 18
   END
   WHERE id IN (id1, id2, id3, id4);
   
   -- 确认更新行数和选中的行数一致后，提交事务
   COMMIT;
   

   事务提交后，之前加的行锁会自动释放，其他事务可以正常操作这些已经分配完成的行。

避坑提醒 #

• 禁止用不带锁的普通SELECT先查id再更新：普通SELECT是快照读，不会加锁，并发场景下两个事务会查到完全相同的行，导致重复分配同一行的问题
• 整个选行、更新的流程不能拆到多个事务中执行，否则锁会在中途被释放，失去并发保护能力
• 不要用“标记待处理状态”的冗余方案：不需要额外增加字段、处理超时回滚逻辑，依赖数据库自带的行锁机制就可以完成并发隔离，维护成本最低

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