PostgreSQL 14中多UPDATE CTE能否保证行锁获取顺序？

核心结论 #

在单查询中使用CTE更新不同表的行时，确实无法保证行锁的获取顺序，存在死锁风险。

关键依据与分析 #

1. 官方文档的基础约束 #

PostgreSQL官方明确指出，避免死锁的核心原则是：所有应用必须以一致的固定顺序获取多对象的锁。但CTE的执行特性是：WITH子句中的子语句会与主查询并发执行，数据修改的顺序完全不可预测，这直接导致行锁的获取顺序无法被控制。

2. CTE依赖无法约束锁顺序 #

即使CTE之间存在显式依赖关系，也不能保证锁的获取顺序。因为PostgreSQL的查询规划器会根据优化规则对查询进行改写（例如消除冗余依赖、调整执行计划），不会严格按照你编写的CTE语法顺序来执行子语句，所以依赖关系无法成为锁顺序的可靠保障。

3. 实验现象的深层解读 #

针对你做的实验：

• 创建table_a和table_b，执行带pg_sleep的依赖型UPDATE CTE时，另一连接前5秒能正常更新table_b、之后需等待3秒——这个现象看似table_b的锁在update_a完成后才获取，但pg_locks显示两张表的RowExclusiveLock立即存在。这是因为RowExclusiveLock是表级锁，会在查询启动初期就被获取；而你观察到的等待行为，实际反映的是行级锁的获取顺序不可控。
• 移除CTE依赖后，行为随机，进一步验证了锁的获取顺序没有固定规则。

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