為什麼單個 UPDATE 查詢會出現死鎖？

我有兩個程序並行執行這樣的程式碼：

begin;
update foos set unread=false where owner_id=123 and unread=true;
commit;

這會導致死鎖。

我對導致死鎖的原因的理解就像這個問題中描述的場景一樣，“交織”的 UPDATE 語句以不同的順序更新兩個不同的行。我不明白單個 UPDATE 語句如何導致死鎖。我無法在我的開發環境中使用兩個並行 psql 會話來複製死鎖場景。我對為什麼無法複製它的猜測：

1. 我誤解了創建死鎖錯誤的程式碼，實際上每個事務中有多個 UPDATE 語句
2. “交織”方面正在發生，但在覆蓋多行的 UPDATE 語句“內部”，因此很難複製。

這個單一的 UPDATE 是否有可能造成死鎖？

您的語句修改了幾行。這些行中的每一行在更新時都被鎖定。

並發事務中的語句很可能已經鎖定了這些行之一，從而阻塞了您的UPDATE. 如果並發事務隨後嘗試鎖定您UPDATE已鎖定的行之一，則會出現死鎖。

Laurenz 解釋了可能導致死鎖的機制，您自己已經包含了一個連結，指向 Kevin 的更詳細解釋：

• 執行 UPDATE 時 PostgreSQL 中的死鎖

以下是如何複製死鎖的分步說明 - 使用方式與普通UPDATE方式相同SELECT .. FOR UPDATE：

• 如何在 PostgreSQL 中模擬死鎖？

現在，如何避免這個問題？

如果您要更新大部分或全部表 - 而且您有能力 - 只需對錶進行寫鎖定。通常，這不是要走的路。否則，三種不同的方法：

1. 一致的順序

該手冊在有關死鎖的章節中有此建議：

防止死鎖的最佳方法通常是通過確保所有使用數據庫的應用程序以一致的順序獲取多個對像上的鎖來避免死鎖。

不知道為什麼仍然*沒有ORDER BY*for UPDATE。但這就是我們必須解決的問題。而是將行鎖定SELECT ... FOR UPDATE在同一事務中-就像您已經嘗試過的那樣，正如您之前的問題所表明的那樣。您只是忘記了基本的確定性ORDER BY：

BEGIN;
SELECT FROM foos WHERE owner_id = 123 AND unread
**ORDER BY ???** -- any deterministic order, PK would be an obvious candidate
FOR    UPDATE;

UPDATE foos SET unread = false WHERE owner_id = 123 AND unread;
END;

顯然，所有潛在的競爭事務都必須以相同的順序獲取鎖。

2.跳過鎖定的行

僅處理未鎖定的行：

BEGIN;
SELECT FROM foos WHERE owner_id = 123 AND unread
-- ORDER BY ???  -- optional in this case
FOR    UPDATE **SKIP LOCKED**;

UPDATE foos SET unread = false WHERE owner_id = 123 AND unread;
END;

如果您確定跳過的行已被執行相同操作的競爭事務處理，那麼您就完成了。（你確定嗎？）

否則，為了確保，請跟進檢查：

SELECT EXISTS (SELECT FROM foos WHERE owner_id = 123 AND unread);

寫入器不會阻塞讀取器，讀取器不會阻塞寫入器，所以這會返回TRUE，直到最後一行都成功更新。循環上面的UPDATE塊，然後循環（有適當的延遲），直到你得到FALSE. 然後你就完成了。

ORDER BY對於會增加大量成本的大型套裝來說可能更便宜。ORDER BYOTOH，如果有匹配的索引，添加仍然有意義……

3.一次一個

與上麵類似，只是一次只更新一行。通常更昂貴，但任何死鎖的可能性都會被消除——如果做得好的話。當處理單行已經需要很長時間時，請考慮這一點。

詳解（大多也適用於上述）及說明：

• Postgres 更新…限制 1

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