PostgreSQL如何原子更新users与jobs表并避免并发重复扣款问题

当前实现的并发风险 #

你目前的事务写法存在重复扣除用户余额的风险，核心问题在于事务内的执行顺序不合理：你先执行用户余额扣减，再修改job状态。两个相同job ID的并发事务同时启动时，会同时读到jobs.state = 'pending'的状态，先后/同时触发用户余额扣减，等第一个事务修改完job状态时，第二个事务可能已经完成了余额扣减操作，导致重复扣费。

事务本身不止提供「执行失败回滚所有变更」的原子性能力，配合数据库的行级锁、隔离级别，完全可以实现你要的并发安全控制，不需要调整表结构也能实现。

推荐的安全实现方案 #

调整事务内的执行顺序，先锁定job行并判断状态，再执行余额扣减，利用UPDATE操作自带的行排他锁特性避免并发竞争：

BEGIN;
-- 第一步：先更新job状态，仅当状态为pending时更新成功，同时对该行加排他锁
-- 执行后通过影响行数判断是否拿到执行权限：返回1行说明成功，返回0行说明job已被处理，直接结束事务即可
UPDATE jobs 
SET state = 'success' 
WHERE id = ? AND state = 'pending'
RETURNING cost, user_id;

-- 只有上一步返回了有效数据时才执行余额扣减
UPDATE users 
SET credit = credit - $1 
WHERE id = $2;
COMMIT;

为什么这个方案是安全的？ #

• 数据库执行UPDATE操作时，会对所有匹配WHERE条件的行加排他锁，直到事务结束才释放
• 并发的同job ID请求执行第一步UPDATE时，会排队等待已持有锁的事务提交/回滚：
  • 如果前序事务提交成功，job状态已变为success，后续请求的UPDATE匹配不到符合条件的行，不会执行后续的余额扣减
  • 如果前序事务回滚，后续请求拿到锁后会再次判断job状态，符合条件才会继续执行

可选优化（无需调整也可使用） #

如果希望用乐观锁方案降低锁开销，可以给jobs表增加版本号字段：

ALTER TABLE jobs ADD COLUMN version INT NOT NULL DEFAULT 0;

更新时带上版本号判断：

UPDATE jobs 
SET state = 'success', version = version + 1
WHERE id = ? AND state = 'pending' AND version = ?
RETURNING cost, user_id;

这个方案适合并发量非常高的场景，普通场景下上面的悲观锁方案已经足够稳定。

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