多应用实例下无间隙生成带序列段的设备编号方案咨询

一、替代悲观锁的无间隙编号生成方案 #

1. 单条记录维护序列状态（乐观锁+版本号） #

建一张专门的序列状态表，比如series_sequence，结构如下：

CREATE TABLE series_sequence (
    series VARCHAR(10) PRIMARY KEY,
    max_num INT NOT NULL,
    current_num INT NOT NULL DEFAULT 0,
    version INT NOT NULL DEFAULT 0
);

生成编号的流程：

• 先查询对应SERIES的当前状态（必须带版本号）
• 计算下一个编号：如果current_num +1 <= max_num，就用current_num +1；如果已达上限，就切换到下一个预定义的SERIES（比如按字典序查找），并从1开始
• 用乐观锁更新：UPDATE series_sequence SET current_num = ?, version = version +1 WHERE series = ? AND version = ?
• 若更新失败（说明有其他线程抢先修改），则重试整个流程

这种方式靠版本号处理冲突，无需长期持有锁，适合并发量不算极高的场景。

2. 数据库存储过程实现原子生成 #

把查询、判断、更新的逻辑全部放在PostgreSQL存储过程中，利用数据库事务的原子性保证无间隙：

CREATE OR REPLACE FUNCTION generate_device_id(p_series VARCHAR)
RETURNS VARCHAR AS $$
DECLARE
    v_max_num INT;
    v_current_num INT;
    v_next_num INT;
    v_next_series VARCHAR;
BEGIN
    -- 锁定对应SERIES的状态行，防止并发修改
    SELECT max_num, current_num INTO v_max_num, v_current_num
    FROM series_sequence
    WHERE series = p_series
    FOR UPDATE;

    IF v_current_num + 1 <= v_max_num THEN
        v_next_num := v_current_num + 1;
        -- 更新当前SERIES的编号
        UPDATE series_sequence
        SET current_num = v_next_num
        WHERE series = p_series;
        RETURN p_series || '-' || v_next_num;
    ELSE
        -- 切换到下一个SERIES（假设存在预定义的series_definition表存储所有SERIES及对应max_num）
        SELECT series INTO v_next_series
        FROM series_definition
        WHERE series > p_series
        ORDER BY series ASC
        LIMIT 1;
        
        -- 初始化新SERIES的状态（不存在则插入，存在则重置为1）
        INSERT INTO series_sequence (series, max_num, current_num)
        SELECT v_next_series, max_num, 1
        FROM series_definition
        WHERE series = v_next_series
        ON CONFLICT (series) DO UPDATE SET current_num = 1;
        
        RETURN v_next_series || '-1';
    END IF;
END;
$$ LANGUAGE plpgsql;

调用时直接执行SELECT generate_device_id('AA');即可，所有逻辑在数据库端完成，避免应用层的冲突处理麻烦。

3. 分段预生成（高并发场景适用） #

如果并发量特别大，可以提前为每个SERIES预生成一段编号（比如一次预生成10个），存储在应用层缓存中，缓存耗尽后再去数据库申请下一段。但要注意：

• 预生成的段必须在数据库标记为已分配，防止重复
• 若应用实例崩溃，未使用的预生成编号需要回收，保证无间隙（可通过定时任务清理）

这种方式实现复杂度较高，仅适合高并发场景。

二、PostgreSQL锁机制的具体行为分析 #

假设你的findMax()是直接查询对应SERIES的最大行并加悲观锁（比如JPA的PESSIMISTIC_WRITE），对应的SQL会带上FOR UPDATE锁，锁定AA|4|3这一行。

此时其他线程执行SELECT max(number) FROM series_record WHERE series = 'AA';：

• 不会等待锁释放，直接返回3。因为PostgreSQL默认的READ COMMITTED隔离级别下，普通查询是快照读，语句开始时的快照已包含已提交的AA|4|3记录，聚合查询能正常读取到最大值，不会被行锁阻塞。

只有当其他线程尝试修改AA|4|3这一行，或者执行带FOR UPDATE/FOR NO KEY UPDATE这类锁的查询时，才会等待锁释放。

另外需要注意：如果findMax()是先查max(number)，再根据该值查询行并加锁，中间可能出现间隙（比如查完max为3后，另一个线程插入了4，此时锁定的3就不是最新的）。正确的做法是直接在查询最大行时加锁，比如：

SELECT * FROM series_record WHERE series = 'AA' ORDER BY number DESC LIMIT 1 FOR UPDATE;

这样能确保锁定的是当前最新的最大行。

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