主页 / dba / 问题 / 308688
Accepted
cis
Asked: 2022-03-14 22:18:40 +0800 CST

在 UPDATE 中的 CTE 中是否需要显式 FOR UPDATE 锁？

  • 772

在 Postgres 13 中，我有一个经常更新的表。但是，更新查询相当复杂，并且多次使用相同的值。因此，使用 CTE 似乎是一件合乎逻辑的事情。

一个简化的示例如下所示：

WITH my_cte AS (
    SELECT
          my_id,
          CASE WHEN my_value1 > 100 THEN 50 ELSE 10 END AS my_addition     
    FROM my_table      
    WHERE my_id = $1
)
UPDATE my_table
        SET my_value1 = my_table.my_value1 + my_cte.my_addition,
            my_value2 = my_table.my_value2 + my_cte.my_addition
FROM my_cte
WHERE my_table.my_id = my_cte.my_id

现在我想知道：如果在SELECTCTE 和之间UPDATE，表被另一个查询更新，my_value1因此发生变化，计算会my_addition在发生时变得过时和错误UPDATE。会不会出现这样的情况？还是 Postgres 自动设置隐式锁？

如果 Postgres 在这里没有魔法，我需要自己处理它：在 CTE 中做就足够了FOR UPDATE吗SELECT？

抱歉，如果我没有在这里说清楚：这不是我想“看到”那些并发修改，我想阻止它们，即一旦计算SELECT完成，在完成之前没有其他查询可能修改该行UPDATE。

在现实生活中，我在这里嘲笑的CASE WHEN my_value1 > 100 THEN 50 ELSE 10 END是大约 20 行长，我在UPDATE. 由于我是“不要重复自己”的忠实粉丝，我认为 CTE 是要走的路。或者有没有更好的方法来避免在UPDATE没有 CTE 的情况下复制和粘贴？

postgresql update

3 个回答

  1. Best Answer

    Postgres 使用多版本模型（多版本并发控制，MVCC）。

    在默认READ COMMITTED隔离级别下，每个单独的查询在查询开始运行的那一刻有效地看到数据库的快照。如果在两者之间提交并发事务，则后续查询（即使在同一事务中）也可以看到不同的快照。（加上迄今为止在同一事务中所做的事情。）

    但是，就CTE而言，所有子语句WITH都与外部语句同时执行，它们实际上看到了数据库的相同快照。为此，所有这些都被视为单个查询。

    所以，不，您不需要显式锁定来保持一致。

    出于多种原因，将逻辑封装在函数中可能很方便，但这对并发性没有任何影响。另外：具有volatile函数的 CTE 永远不会内联。看：

    ASELECT不锁定查询的行。Postgres 允许并发UPDATES. 但UPDATE锁定目标行。尝试写入的并发事务也必须等到锁定事务完成。

    如果您想禁止写入仅在您UPDATE的进程中被选中的行（列），您可能仍然需要锁定（或使用更严格的隔离级别）。可能FOR UPDATE是锁，或者可能是更弱的锁。这取决于您在问题中明确隐瞒/不提供的细节和要求。

    此外（尽管您没有要求这样做），如果多个并发事务可能正在写入重叠行（一次多个），请务必遵守相同、一致的行顺序以避免死锁。

    • 6

  2. 如果要防止并发语句在 CTE 选择的行更新之前对其进行修改，则需要SELECT ... FOR NO KEY UPDATE在 CTE 中使用。

    • 1

  3. 基于a_horse_with_no_name所说的：

    我会将这样的条件放入（SQL）函数中。锁定的另一种替代方法（如果您希望这种情况很少发生）是使用serializable隔离级别并在发生错误时重新运行 UPDATE。

    将加法逻辑放入一个函数中，然后每次设置新值时调用该函数。这将从两个方面帮助您。

    1. 这允许您避免在每次使用时重复添加逻辑。
    2. 这使得一个非常简单的更新语句可以快速进入，锁定几行，然后退出。

    像这样的东西应该工作。

    CREATE FUNCTION fn_my_addition(my_value int)
RETURNS INT
LANGUAGE SQL
AS
$$
  select CASE my_value1 > 100 THEN 50 ELSE 10 END;
$$;

UPDATE my_table
SET my_value1 = my_value1 + fn_my_addition(my_value1),
    my_value2 = my_value2 + fn_my_addition(my_value2)
WHERE my_id = $1;
    • 0

相关问题