在 UPDATE 中的 CTE 中是否需要顯式 FOR UPDATE 鎖？

在 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 的情況下複製和粘貼？

Postgres 使用多版本模型（多版本並發控制，MVCC）。

在預設READ COMMITTED隔離級別下，每個單獨的查詢在查詢開始執行的那一刻有效地看到數據庫的快照。如果在兩者之間送出並發事務，則後續查詢（即使在同一事務中）也可以看到不同的快照。（加上迄今為止在同一事務中所做的事情。）

但是，就CTE而言，所有子語句WITH都與外部語句同時執行，它們實際上看到了數據庫的相同快照。為此，所有這些都被視為單個查詢。

所以，不，您不需要顯式鎖定來保持一致。

出於多種原因，將邏輯封裝在函式中可能很方便，但這對並發性沒有任何影響。另外：具有volatile函式的 CTE 永遠不會內聯。看：

• 在查詢中返回列是否已被目前查詢更改

ASELECT不鎖定查詢的行。Postgres 允許並發UPDATES. 但UPDATE鎖定目標行。嘗試寫入的並發事務也必須等到鎖定事務完成。

如果您想禁止寫入僅在您UPDATE的程序中被選中的行（列），您可能仍需要鎖定（或使用更嚴格的隔離級別）。可能FOR UPDATE是鎖，或者可能是更弱的鎖。這取決於您在問題中明確隱瞞/不提供的細節和要求。

此外（儘管您沒有要求這樣做），如果多個並發事務可能正在寫入重疊行（一次多個），請務必遵守相同、一致的行順序以避免死鎖。

引用自：https://dba.stackexchange.com/questions/308688