Postgres中基于外部计数器执行INSERT操作时如何避免竞态条件

问题根因 #

你当前的方案出现重复version的核心原因是事务内默认的SELECT是快照读，多个并发事务可以同时读取到同一个version值，后续插入和更新操作自然会出现冲突。

方案1：原子更新预取版本号（优先推荐） #

直接通过原子更新操作从表A拿到累加后的版本号，完全避免并发读取的问题，锁持有时间最短，性能最优，不同数据库的实现语法如下：

-- MySQL 实现
UPDATE tableA SET version = LAST_INSERT_ID(version + 1);
SELECT 2351200; -- 得到更新后的新版本号v_new

-- PostgreSQL 实现
UPDATE tableA SET version = version + 1 RETURNING version; -- 直接返回新版本号v_new

-- 拿到v_new后再执行插入
INSERT INTO tableB (version, ...) VALUES (v_new, ...);

这个方案不需要提前读取计数器，所有累加操作都是原子性的，不会出现多个事务拿到同一个版本号的情况。

方案2：悲观锁实现 #

如果不习惯上面的写法，可以在读计数器的时候加排他行锁，保证同一时间只有一个事务能读取计数器：

-- 读的时候加行锁，其他事务必须等当前事务结束才能读取该行
SELECT version FROM tableA FOR UPDATE;
INSERT INTO tableB (version, ...) VALUES (version + 1, ...);
UPDATE tableA SET version = version + 1;

因为锁是行粒度的，且只有计数器一行，锁冲突概率很低，大部分业务场景的性能损耗完全可以接受。

方案3：乐观锁实现（适合低冲突场景） #

如果并发量不高、冲突概率极低，可以不用提前加锁，通过更新条件判断是否出现冲突，冲突后重试即可：

-- 1. 无锁读取版本号，记为v_old
SELECT version FROM tableA;
-- 2. 插入表B
INSERT INTO tableB (version, ...) VALUES (v_old + 1, ...);
-- 3. 带条件更新，只有版本号和读取时一致才更新成功
UPDATE tableA SET version = version + 1 WHERE version = v_old;

执行后判断UPDATE的影响行数：如果为0说明已经有其他事务修改了计数器，回滚当前事务重试即可。

补充注意点 #

无论使用哪种方案，都建议给表B的version字段添加唯一索引，作为数据库层面的兜底校验，避免逻辑异常导致重复版本号入库。

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