如果我使用 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 隔离级别的行为。