如何基于其他列存储行索引编号？解决并发插入重复问题

需求概述 #

需要实现：存储行的写入顺序，且每个Base列的唯一值对应独立的索引序列。示例数据如下：

核心要求：index字段需基于Base列生成唯一的递增序列，同一Base下的index不能重复。

尝试的方案及问题 #

之前尝试通过自定义函数next_index生成索引，插入语句如下：

INSERT INTO mytable ("Column A", "Base", "index") VALUES ($1, $2, next_index($2))

next_index函数代码：

DECLARE
    new_index bigint;
BEGIN
    SELECT MAX(index) + 1
    INTO new_index
    FROM mytable
    WHERE Base = $1;
    RETURN new_index;
END;

问题：并发插入时，多个请求会同时读取同一Base的MAX(index)值，导致计算出相同的new_index，最终插入重复的index值，违反唯一性要求。

解决方案 #

方案1：序列管理表+行级锁 #

创建专门的序列管理表，通过行级锁避免并发冲突：

1. 创建序列管理表：

CREATE TABLE base_sequence (
    base_value INT PRIMARY KEY,
    current_index BIGINT DEFAULT 1
);

2. 修改next_index函数，加入行级锁逻辑：

CREATE OR REPLACE FUNCTION next_index(p_base INT)
RETURNS BIGINT AS $$
DECLARE
    new_index BIGINT;
BEGIN
    -- 自动新增Base对应的序列记录（不存在则插入）
    INSERT INTO base_sequence (base_value)
    VALUES (p_base)
    ON CONFLICT (base_value) DO NOTHING;

    -- 锁定该行，阻止并发修改
    SELECT current_index + 1
    INTO new_index
    FROM base_sequence
    WHERE base_value = p_base
    FOR UPDATE;

    -- 更新当前序列值
    UPDATE base_sequence
    SET current_index = new_index
    WHERE base_value = p_base;

    RETURN new_index;
END;
$$ LANGUAGE plpgsql;

3. 插入语句保持不变：

INSERT INTO mytable ("Column A", "Base", "index") VALUES ($1, $2, next_index($2))

方案2：独立序列（PostgreSQL专属） #

为每个Base值创建独立的数据库序列，利用序列的原子性避免冲突：

1. 为已存在的Base值创建序列：

-- 示例：为Base=1创建序列
CREATE SEQUENCE seq_base_1 START 1;
-- 为Base=2创建序列
CREATE SEQUENCE seq_base_2 START 1;

2. 插入时动态调用对应序列：

INSERT INTO mytable ("Column A", "Base", "index")
VALUES ($1, $2, nextval('seq_base_' || $2::TEXT));

注意：若Base值是动态新增的，需额外逻辑自动创建对应序列。

方案3：触发器自动生成索引 #

通过触发器在插入前自动计算index，结合锁机制保证唯一性：

1. 创建触发器函数：

CREATE OR REPLACE FUNCTION set_base_index()
RETURNS TRIGGER AS $$
DECLARE
    new_index BIGINT;
BEGIN
    -- 锁定查询范围，阻止并发读取旧值
    SELECT COALESCE(MAX(index), 0) + 1
    INTO new_index
    FROM mytable
    WHERE Base = NEW.Base
    FOR UPDATE;

    NEW.index = new_index;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

2. 创建触发器：

CREATE TRIGGER trigger_set_base_index
BEFORE INSERT ON mytable
FOR EACH ROW
EXECUTE FUNCTION set_base_index();

3. 插入时无需手动指定index：

INSERT INTO mytable ("Column A", "Base") VALUES ($1, $2);

关键原理 #

以上方案核心都是通过行级锁或原子性序列规避并发竞态：

• 方案1通过序列管理表的行锁，确保同一Base下仅一个请求能修改序列值；
• 方案2利用数据库原生序列的原子自增特性，每个Base独立序列天然无冲突；
• 方案3通过FOR UPDATE锁定查询范围，阻止其他请求读取旧的MAX(index)值。

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