InnoDB多行锁下MySQL 5.7.16自定义表序列生成器实现问题

1. 序列表结构定义 #

首先创建存储多序列的表，用sequence_name作为主键来区分不同业务的序列：

CREATE TABLE sequence_table (
    sequence_name VARCHAR(64) NOT NULL COMMENT '序列唯一标识',
    next_val BIGINT NOT NULL DEFAULT 1 COMMENT '下一个待分配的序列值',
    PRIMARY KEY (sequence_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

2. 序列分配核心逻辑 #

我采用“查询-计算-条件更新”的策略，确保并发场景下序列值的唯一性：

• 第一步：查询目标序列的当前值

  SELECT next_val FROM sequence_table WHERE sequence_name = ?;
  

• 第二步：根据业务需求的分配步长（比如单次分配1个值，或者批量分配N个），计算新的序列值：new_val = current_val + step_size
• 第三步：执行条件更新，仅当表中当前值和查询到的current_val完全匹配时才更新，避免并发场景下的覆盖问题

  UPDATE sequence_table 
  SET next_val = ? 
  WHERE sequence_name = ? AND next_val = ?;
  

• 第四步：检查更新影响的行数，如果返回0，说明并发过程中已有其他线程修改了该序列，需要重新执行上述步骤（建议加入有限次数的重试机制）

3. 解决InnoDB中UPDATE ... WHERE ...的多行锁问题 #

InnoDB的行锁依赖索引生效，这里我们可以通过几个关键细节避免锁范围扩大：

• 强制使用主键/唯一索引作为查询条件：sequence_name是主键，属于唯一索引，这能确保每次UPDATE只会锁定目标行，不会触发全表扫描导致的表锁
• 避免范围类查询条件：不要用LIKE、>/<这类范围条件匹配sequence_name，否则会锁定所有符合范围的行，影响其他序列的正常使用
• 用短事务减少锁持有时间：把序列查询和更新逻辑封装在一个短事务中，还可以用SELECT ... FOR UPDATE提前锁定目标行，减少“查询-更新”间隙的并发冲突，示例如下：

  BEGIN;
  SELECT next_val FROM sequence_table WHERE sequence_name = 'order_seq' FOR UPDATE;
  -- 计算new_val的逻辑
  UPDATE sequence_table SET next_val = new_val WHERE sequence_name = 'order_seq';
  COMMIT;
  

如果业务需要批量分配序列（比如一次获取10个连续值），直接把step_size设为10即可，一次更新就能完成批量分配，既减少数据库交互次数，也能降低锁竞争概率。

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