postgresql 为什么SELECT.FORUPDATE在CTE中执行时不起作用，而CTE在事务的后面没有使用？

nmpmafwu 于 2023-06-29 发布在 PostgreSQL

关注(0)|答案(2)|浏览(221)

我有一个简单的表格，如下所示：

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)

现在我有两个事务并发运行：

Transaction 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;

Transaction 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;

要插入的值有意地以相反的顺序插入事务之间，因为我想触发死锁，看看是否可以通过使用SELECT...FOR UPDATE;来避免。现在我明白了，改变顺序可以避免僵局，但我还是想知道这里发生了什么。
当这两个事务并发执行时，它们总是死锁，并出现错误：

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

但是如果我移除CTE并简单地将每个事务分解成两个单独的命令，例如：

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;

另一个事务也是如此，那么死锁消失，第二个事务只需等待第一个事务释放行上的锁，这也是我所期望的。
我希望在使用CTE时也会发生同样的情况，因为SELECT...FOR UPDATE仍然在事务中执行，但事实并非如此。我还尝试声明CTE AS MATERIALIZED，看看这是否会有所不同，但没有任何变化。从Postgres的文档中，我看到了这样的内容：
如果在锁定子句中指定了特定的表，则只锁定来自这些表的行; SELECT中使用的任何其他表都可以像往常一样简单地读取。没有表列表的锁定子句会影响语句中使用的所有表。如果将锁定子句应用于视图或子查询，则它会影响视图或子查询中使用的所有表。但是，这些子句不适用于主查询引用的WITH查询。如果希望在WITH查询中发生行锁定，请在WITH查询中指定锁定子句。
但我不太明白它的意思，我想这是指FOR UPDATE中的[ OF table_name [, ...] ]可选参数。
postgres是否完全忽略CTE，因为它不会在后续查询中使用？如果是这样的话，那么为什么其他命令（如DELETE或UPDATE）在未使用的CTE中正常工作？还是在幕后发生了什么？
注意：我使用的是PostgreSQL 15.2

postgresql

来源：https://stackoverflow.com/questions/76459310/why-is-select-for-update-not-working-if-executed-inside-a-cte-that-is-not-used

2条答案

按热度按时间

hs1ihplo1#

由于CTE永远不会执行，因此您会出现死锁。
CTE仅在包含DML语句或在main语句中使用时才会执行。参见the documentation：
WITH中的数据修改语句只执行一次，并且始终执行到完成，与主查询是否读取所有（或实际上任何）它们的输出无关。请注意，这与WITH中SELECT的规则不同：如前一节所述，SELECT的执行仅在主查询要求其输出的情况下进行。
使用SELECT ... FOR UPDATE避免死锁的两个注意事项：
1.除非计划删除该行或修改键，否则应使用FOR NO KEY UPDATE以避免过度锁定

运行SELECT ... FROM tab FOR NO KEY UPDATE的两个会话仍可能彼此死锁
由于同步的顺序扫描（参见synchronize_seqscans参数）
如果表上存在并发更新，并且两个SELECT的快照以不同的顺序查看行

赞(0）回复(0）举报 2023-06-29

ubby3x7f2#

两个事务都试图同时获取同一行上的exclusive锁。
在第一个事务中，您使用CTE从测试表中选择所有行，并使用FOR UPDATE.锁定它们。这意味着第一个事务将获取对测试表中所有行的exclusive锁。
在第二个事务中，您还使用CTE从测试表中选择所有行。但是，此CTE不使用FOR UPDATE子句。这意味着第二个事务将只获取测试表中的shared lock on the rows。
当第二个事务尝试更新已被第一个事务锁定的行时，将发生deadlock。
要避免此死锁，您可以：

在两个CTE中使用FOR UPDATE子句
使用单个事务同时执行选择和更新

在CTEs中使用FOR UPDATE子句

BEGIN;

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

COMMIT;

执行选择和更新的单个事务：

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;

第二个CTE也使用FOR UPDATE子句。但是，deadlock仍然发生，因为第二个事务正在尝试获取第一个事务已经锁定的行上的exclusive锁。
第二个事务能够在测试表中的行上获取shared loc k的原因是因为第一个事务已经在这些行上获取了独占锁。这是因为FOR UPDATE子句是blocking lock.这意味着第二个事务将等待直到第一个事务释放行上的排他锁，然后才能获取这些行上的共享锁。
但是，当第二个事务尝试更新已经被第一个事务锁定的行时，会发生死锁，因为第二个事务现在正试图获取该行的exclusive锁。这将导致第一个事务阻塞，第二个事务阻塞。这将产生无法解决的deadlock情况。
为了避免这种死锁，可以在两个CTE中使用FOR UPDATE子句，也可以使用单个事务来执行选择和更新。

赞(0）回复(0）举报 2023-06-29

我来回答

postgresql 为什么SELECT.FORUPDATE在CTE中执行时不起作用，而CTE在事务的后面没有使用？

2条答案

相关问题

热门标签

最新问答