在 Postgres 中優化並發更新

我正在執行這樣的並發 Postgres 查詢：

UPDATE foo SET bar = bar + 1 WHERE baz = 1234

每個查詢都會影響固定的 K 行數，並且我找不到強制執行更新行的順序的方法，最終導致死鎖。目前我通過手動執行訂單來解決問題，但這意味著我必須執行比通常更多的查詢，同時還將搜尋複雜度從 O(log N + K) 提高到 O(K log N)。

有沒有辦法提高性能而不至於容易陷入死鎖？我懷疑用(baz)索引替換(baz, id)索引可能會起作用，前提是 Postgres 以與掃描它們相同的順序更新行，這是一種值得追求的方法嗎？

命令ORDER BY中沒有。SQL UPDATEPostgres 以任意順序更新行：

• 用 ORDER BY 更新

為了絕對避免死鎖，您可以在可序列化事務隔離中執行您的語句。但這更昂貴，您需要準備在序列化失敗時重複命令。

您最好的做法可能是SELECT ... ORDER BY ... FOR UPDATE在子查詢中顯式鎖定或SELECT在事務中獨立鎖定 - 在預設的“讀取送出”隔離級別。在 pgsql-general 上引用 Tom Lane 的話：

應該沒問題 — FOR UPDATE 鎖定始終是 SELECT 管道中的最後一步。

這應該做的工作：

BEGIN;

SELECT 1
FROM   foo 
WHERE  baz = 1234
ORDER  BY bar
FOR    UPDATE;

UPDATE foo
SET    bar = bar + 1
WHERE  baz = 1234;

COMMIT;

多列索引(baz, bar)可能非常適合性能。但是由於bar顯然更新了很多，單列索引(baz)可能會更好。取決於幾個因素。每行多少行baz？沒有多列索引是否可以進行HOT 更新？…

如果 baz同時更新，則仍然不太可能發生衝突（根據文件）：

SELECT在READ COMMITTED 事務隔離級別執行並使用鎖定子句的命令可能ORDER BY會亂序返回行。…

此外，如果您應該有一個涉及 的唯一約束bar，請考慮一個DEFERRABLE約束以避免在同一命令中出現唯一違規。相關答案：

• 約束定義 DEFERRABLE INITIALLY IMMEDIATE 仍然是 DEFERRED？

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