如何在PostgreSQL中实现指定优先级的事务执行策略？

表结构 #

当前事务操作 #

各事务作用 #

• 事务1：更新表A状态为非运行，同时获取该表关联的所有运行中表B记录
• 事务2：仅当关联表A处于运行状态时，向表B插入新记录
• 事务3：更新表B中指定记录的运行状态

核心需求：事务优先级3 > 1 > 2 #

• 事务2若检测到事务1已提交，必须直接回滚
• 事务1需等待事务3提交完成，确保查询到的表B记录是最新状态

现有尝试问题 #

曾将事务2中的SELECT改为SELECT ... FOR UPDATE来实现序列化控制，但无法满足“事务1提交后强制回滚事务2”的要求。

解决方案 #

1. 实现事务1等待事务3提交：行级锁强制顺序

事务1在执行核心逻辑前，先对事务3操作的表B记录（id=2）加共享锁，确保必须等待事务3释放锁后才能继续：

-- 事务1新增前置步骤
SELECT * FROM table_b WHERE id=2 FOR SHARE;
-- 原事务1逻辑
UPDATE table_a SET is_running = false WHERE id=1;
SELECT id FROM table_b WHERE a_id=1 AND is_running=true;

事务3执行UPDATE时会对id=2的记录加排他锁，事务1的FOR SHARE会等待排他锁释放（即事务3提交/回滚），从而保证后续查询到最新的表B数据。

2. 实现事务2在事务1提交后回滚：乐观锁+版本校验

在表A中新增version字段（整数类型，每次更新自增），调整事务2的执行流程：

-- 事务2步骤1：获取当前表A状态和版本号
SELECT is_running, version INTO @is_running, @version FROM table_a WHERE id=1;
IF @is_running != true THEN
    ROLLBACK;
END IF;

-- 步骤2：校验版本号，确认表A未被事务1修改
UPDATE table_a SET version = version + 1 WHERE id=1 AND is_running=true AND version=@version;
IF ROW_COUNT() = 0 THEN
    -- 版本号变化或is_running已被修改（事务1已提交），直接回滚
    ROLLBACK;
END IF;

-- 步骤3：执行插入
INSERT INTO table_b values (3, 1, true);
COMMIT;

通过版本号校验，确保事务2插入数据前，表A的状态未被事务1修改；若事务1已提交修改表A，UPDATE语句会返回0行影响，触发事务2回滚。

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