PostgreSQL图书队列位置管理存储过程问题修复

问题根源 #

并发添加同一图书的队列时，多个事务会同时读取到相同的最大队列位置，导致插入重复值；若存在队列删除操作，直接插入新位置还会产生间隙，无法保证位置连续。

修正方案1：并发安全的原子插入（适合高并发插入场景） #

通过SELECT ... FOR UPDATE锁定目标图书的队列记录，确保同一时间只有一个事务能获取并更新队列位置，从根源避免重复和间隙：

CREATE OR REPLACE PROCEDURE add_to_queue(p_reader_id INT, p_book_id INT)
LANGUAGE plpgsql
AS $$
DECLARE
    v_next_position INT;
BEGIN
    -- 锁定当前图书的所有队列记录，防止并发事务同时读取到相同的最大位置
    SELECT COALESCE(MAX(position), 0) + 1
    INTO v_next_position
    FROM book_queue
    WHERE book_id = p_book_id
    FOR UPDATE;

    -- 插入新队列条目
    INSERT INTO book_queue(reader_id, book_id, position)
    VALUES (p_reader_id, p_book_id, v_next_position);
END;
$$;

修正方案2：强制维护连续队列位置（含删除场景） #

如果需要在队列有删除操作后，仍保持位置连续，可在插入后重新按加入顺序排序并更新位置：

CREATE OR REPLACE PROCEDURE add_to_queue(p_reader_id INT, p_book_id INT)
LANGUAGE plpgsql
AS $$
BEGIN
    -- 先插入新记录，临时位置可设为任意值
    INSERT INTO book_queue(reader_id, book_id, position, created_at)
    VALUES (p_reader_id, p_book_id, 0, NOW());

    -- 按加入时间重新排序，更新为连续位置
    UPDATE book_queue bq
    SET position = sub.row_num
    FROM (
        SELECT id,
               ROW_NUMBER() OVER (ORDER BY created_at ASC) AS row_num
        FROM book_queue
        WHERE book_id = p_book_id
    ) sub
    WHERE bq.id = sub.id;
END;
$$;

优化建议 #

• 给book_queue表创建索引：CREATE INDEX idx_book_queue_book_id ON book_queue(book_id);，加速锁查询和排序操作
• 若使用方案2，建议添加created_at字段记录队列加入时间，确保排序逻辑准确

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