未被后续使用的CTE中SELECT...FOR UPDATE为何失效？

问题背景 #

表结构与初始数据 #

表结构：

Table "public.test"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 id     | integer |           | not null | 
 name   | text    |           |          | 
Indexes:
    "ss_pkey" PRIMARY KEY, btree (id)

初始数据：

id |  name   
----+---------
  0 | Matthew
  1 | Thomas
(2 rows)

并发事务设置 #

两个事务故意使用相反的插入顺序，尝试用CTE中的SELECT...FOR UPDATE提前锁行避免死锁：

事务1：

BEGIN;

WITH locked_rows AS(
    SELECT *
    FROM TEST
    FOR UPDATE
)
INSERT INTO TEST
VALUES 
(1, 'Chris'),
(0, 'John') 
ON CONFLICT(id) DO
UPDATE
SET name = excluded.name
WHERE test.name <> excluded.name

COMMIT;

事务2：

BEGIN;

WITH locked_rows AS(
    SELECT *
    FROM TEST
    FOR UPDATE
)
INSERT INTO TEST
VALUES 
(0, 'Bryan'),
(1, 'Steven') 
ON CONFLICT(id) DO
UPDATE
SET name = excluded.name
WHERE test.name <> excluded.name

COMMIT;

现象 #

并发执行时必然触发死锁，错误信息：

ERROR:  deadlock detected
DETAIL:  Process 29588 waits for ShareLock on transaction 255002; blocked by process 29010.
Process 29010 waits for ShareLock on transaction 255003; blocked by process 29588.
HINT:  See server log for query details.
CONTEXT:  while inserting index tuple (0,27) in relation "test"
SQL state: 40P01

但将事务拆分为两个独立命令后，死锁消失，第二个事务会等待第一个释放行锁：

BEGIN;

SELECT * FROM TEST FOR UPDATE;

INSERT INTO TEST
VALUES 
(1, 'Chris'),
(0, 'John') 
ON CONFLICT(id) DO
UPDATE
SET name = excluded.name
WHERE test.name <> excluded.name

COMMIT;

核心原因分析 #

未引用的SELECT型CTE会被优化器消除 #

你编写的locked_rows是SELECT型CTE，但后续的INSERT语句完全没有引用这个CTE的结果。PostgreSQL的优化器会判定这个CTE是无意义的冗余代码，直接跳过执行——等于那个SELECT...FOR UPDATE根本没跑，自然不会提前锁住表中的行。

而拆分后的两个独立命令中，SELECT...FOR UPDATE是单独执行的SQL语句，优化器无法消除它，因此会正常锁住所有行，后续INSERT时锁顺序一致，避免了死锁。

为什么DML型CTE不会被消除？ #

你提到的DELETE/UPDATE等命令在未被引用的CTE中能正常工作，是因为这类CTE属于数据修改语句（DML），它们会产生数据变更的副作用。PostgreSQL优化器不会消除有副作用的CTE，无论主查询是否引用，都会执行其中的修改逻辑。

关于文档内容的补充说明 #

你看到的文档片段，核心是说明锁子句（如FOR UPDATE）的作用范围，和你的问题场景无关。你的问题本质是CTE被优化消除，而非锁子句的作用范围问题。

验证方式 #

可以通过EXPLAIN查看执行计划，会发现带未引用CTE的INSERT语句中，完全没有SELECT...FOR UPDATE的执行步骤，证明该CTE已被优化器移除。

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