PostgreSQL含SKIP LOCKED的UPDATE无阻塞锁却挂起问题排查

核心原因：大量NULL值导致索引低效+多进程竞争扫描 #

1. 全NULL状态下的索引失效问题 #

你创建的idx_mytable_lock是基于lock字段的B-tree索引。当执行UPDATE mytable set lock = null where lock is not null;后，整张表的lock字段全为NULL：

• PostgreSQL的B-tree索引会将所有NULL值集中存储在索引的同一叶子节点区域，此时查询WHERE LOCK IS NULL需要遍历所有索引条目才能匹配目标行，代价几乎等同于全表扫描。
• 首次执行时，lock从NULL逐步更新为非NULL值，索引中存在大量不同的非NULL条目，查询剩余NULL行时能快速定位；但重置后索引完全被NULL值占据，查询效率直接暴跌。

2. SKIP LOCKED与大LIMIT的竞争放大效应 #

你的UPDATE依赖子查询SELECT id FROM mytable WHERE LOCK IS NULL FOR UPDATE SKIP LOCKED LIMIT 500获取待更新行：

• 当LIMIT值较大（500/1000）时，每个进程需要扫描大量行才能收集到500条未被其他进程锁定的记录。
• 8个进程同时启动后，会互相跳过对方已锁定的行，导致每个进程反复扫描全表/全索引，不断尝试获取可用行。这种“无阻塞但高竞争”的扫描会持续消耗CPU，且随着已锁定行数增加，无效扫描开销越来越大，最终导致进程长时间处于active状态却无法快速推进。
• 当LIMIT=100时，每个进程需要扫描的行数大幅减少，竞争开销被控制在可接受范围内，因此能正常运行。

3. 自动提交模式的额外开销 #

因为使用自动提交，每个UPDATE都是独立事务。每次执行子查询时，数据库都要重新执行扫描、锁定逻辑，无法在一个长事务中持续处理剩余行，进一步加剧了重复扫描的资源消耗。

验证方法 #

重置lock字段后，执行以下语句查看执行计划：

EXPLAIN ANALYZE SELECT id FROM mytable WHERE LOCK IS NULL FOR UPDATE SKIP LOCKED LIMIT 500;

你会看到执行计划大概率是Seq Scan（全表扫描）或者Index Scan using idx_mytable_lock on mytable（低效的全索引扫描），而非高效的索引定位。

可行解决方案 #

• 重建索引：重置lock字段后，执行REINDEX INDEX idx_mytable_lock;，让数据库重新组织索引的NULL值存储，提升查询效率。
• 调整LIMIT值：选择较小的LIMIT（如100-200），平衡单次处理行数和竞争开销。
• 优化查询逻辑：改用基于主键的分批处理方式，比如按UUID范围分段查询，减少多进程的扫描冲突。
• 使用长事务：将多个UPDATE放在同一个长事务中，减少重复扫描次数，但需注意控制事务时长，避免引发其他问题。

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