PostgreSQL使用UPDATE行锁查询时排序结果异常问题咨询

技术栈 #

• Node.js - v16.13.0
• PostgreSQL - postgres (PostgreSQL) 14.0 (Debian 14.0-1.pgdg110+1)
• Sequelize - ^5.12.3
• SQL事务隔离级别 - READ COMMITTED

问题根因 #

该现象由PostgreSQL的SELECT ... FOR UPDATE执行逻辑与READ COMMITTED隔离级别的快照规则共同导致：

1. 执行SELECT ... ORDER BY 列名 DESC FOR UPDATE语句时，PostgreSQL默认执行顺序为：先基于查询启动瞬间的快照生成排序结果，再按排序后的顺序逐行申请行锁。
2. 第二个事务的查询启动时，第一个事务尚未提交，因此它拿到的快照是修改前的全量数据，基于这份快照生成的排序就是旧的余额顺序。随后第二个事务按这个排序尝试给第一行（余额最大的行）加锁时，被持有该行锁的第一个事务阻塞。
3. 第一个事务提交释放锁后，第二个事务只会重新读取被加锁行的最新提交版本，不会重新执行全量排序逻辑，剩下的行仍沿用之前快照的顺序，最终返回结果就出现了旧排序+最新行值的异常情况。
4. 子查询写法生效的原因是：子查询先完成所有行的加锁操作，外层查询再基于加锁后拿到的最新全量数据做排序，结果自然符合预期。

最优处理方案 #

方案1：使用SKIP LOCKED（推荐，适用于仅需要取余额最大的单条/少量数据的场景） #

如果业务逻辑仅需要获取当前可用的余额最高的钱包，不需要处理全表数据，直接修改查询语句即可，既避免排序异常，还能大幅提升并发性能：

SELECT * FROM "Wallets" 
ORDER BY CAST("balance" AS NUMERIC) DESC 
FOR UPDATE SKIP LOCKED
LIMIT 1;

该写法会直接跳过已经被其他事务加锁的行，无需等待锁释放，也不会出现排序错误。

方案2：统一使用先锁后排序的子查询（适用于需要操作全表所有行的场景） #

就是你已经验证过的写法，逻辑清晰无额外兼容问题：

SELECT * 
FROM (SELECT * FROM "Wallets" FOR UPDATE) w1
ORDER BY CAST("balance" AS NUMERIC) DESC;

不推荐方案：提升隔离级别到REPEATABLE READ #

调整隔离级别后，第二个事务遇到锁等待且前序事务提交时，会直接抛出序列化失败错误，需要业务侧额外加重试逻辑，复杂度高且性能下降明显，不建议采用。

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