如果我使用 CTE 进行如下查询:
WITH cte_a AS (
SELECT a.id, a.something
FROM a
WHERE a.something IS NOT NULL
),
-- [...] some other CTEs
cte_d AS (
SELECT
cte_a.id,
cte_a.something
FROM cte_a
JOIN
-- something
JOIN
-- something
WHERE
-- something
ORDER BY cte_a.id ASC
FOR UPDATE -- Here, will the `FOR UPDATE` locks `a` rows?
),
-- rest of the query, which will update `a.something`.
更新锁会应用于表的行a
吗?还是会应用于生成的物化表cte_a
?
如果锁适用于物化表,以下方法可以解决问题吗?
WITH cte_a AS NOT MATERIALIZED (
-- Rest of the query
我正在使用 PostgreSQL v14。
如果它改变了任何东西,我最感兴趣的是默认 READ COMMITTED 隔离级别的行为。
我的答案的基础在手册中提供:
所以:
若要在子句中仅锁定特定表的行
FROM
,请在锁定子句中列出它们的名称。否则,列表中所有表的所有合格行都FROM
将被锁定。如果锁定子句中没有表限制,则
FROM
子句中所有表的所有选定行都将被锁定。主查询中的锁定子句不会传播到 CTE。
然而,这并不能完全阐明您的复杂情况:
SELECT
a
来自中的表cte_a
。cte_d
有,加上一些其他表,加上更多的过滤器。cte_a
FROM
FOR UPDATE
锁定子句,而无需命名特定的表。UPDATE a
在主要查询中。根据经验和一些额外的快速表面测试:
在任何情况下,锁定子句都不适用于列表
WITH
中的查询。不仅在主查询中,在其他 CTE 中也是如此。FROM
此外,添加锁定子句可防止“内联”相同的 CTE(自动优化,或
NOT MATERIALIZED
Postgres 12+ 中明确要求)。因此,符合此特定 CTE 的所有行都将被锁定,其他(“后续”)CTE 或主查询中的附加过滤器不会缩小集合范围。结论
要锁定表的行
a
,您必须在查询中添加锁定子句,其中该表在子句中列出FROM
。您的查询可能如下所示:但是,这可能会锁定比必要更多的行。尝试在锁定子句之前应用所有过滤器。(在相同的
SELECT
计数中。)我很可能重写整个查询。
如何测试?
最小设置:
然后在一个会话中启动一个事务并保持其打开:
然后在另一个会话中尝试(单独的连接/单独的查询窗口):
这将通过,或者被行锁阻止(在这种情况下您将中止)。
在每个会话中运行
ROLLBACK;
下一个测试之前。观察各个星座的不同效果。