SELECT … ORDER BY xxx LIMIT 1 FOR UPDATE 將鎖定多少行？

正如在stackoverflow上被問到的那樣，但對於 MySQL，我想知道這在 PostgreSQL 中是如何工作的。當我結合“LIMIT”、“ORDER BY”和一些“WHERE”執行“FOR UPDATE”時，有多少行被鎖定。他們更新返回的行。我希望“FOR UPDATE”-查詢只會鎖定一行，但也許我會錯過一些實現問題。（我打算寫一些壓力測試來更可靠地重現這個）

這些查詢導致我的系統死鎖：

(session1) select field1, field2, ... from documents where transaction_path='some/path' for update
(session2) select field1, field2, ... from documents where (transaction_path is null) and queue_id=2 and next_pickup_ts<now() order by next_pickup_ts limit 1 for update
(session3) select field1, field2, ... from documents where (transaction_path is null) and queue_id=2 and next_pickup_ts<now() order by next_pickup_ts limit 1 for update

但我不明白為什麼。（transaction_path 上有一個唯一索引。）

如果你使用EXPLAIN你會看到排序。對於一個隨機計劃，我用一些一次性數據和類似的查詢 ( ORDER BY ... LIMIT 1 FOR UPDATE) 我得到了這個計劃：

Limit  (cost=33.09..33.10 rows=1 width=26)
  ->  LockRows  (cost=33.09..39.88 rows=543 width=26)
        ->  Sort  (cost=33.09..34.45 rows=543 width=26)
              Sort Key: t_id
              ->  Seq Scan on m  (cost=0.00..30.38 rows=543 width=26)
                    Filter: (b_id <= 33)
(6 rows)

在這裡，您可以看到行在應用限制之前可能被鎖定。

現在，在實踐中，匹配的第一行通常會被鎖定並返回，導致LockRows後面的行被跳過……但不能保證這一點。所以你不應該依賴它。

一般來說，混合行鎖定LIMIT是一個壞主意，通常應該避免。

如果你必須這樣做，你會想要做類似的事情：

SELECT *
FROM my_table
WHERE id = (SELECT id FROM my_table 
           WHERE mywhereclause 
           ORDER BY ... LIMIT 1)
 AND mywhereclause
FOR UPDATE;

在哪裡強制找到目標行，然後才鎖定。

您應該在外部查詢中重複 WHERE 子句，以確保在發生任何鎖定等待後該行仍然與謂詞匹配。否則可能發生的情況是內部查詢找到該行，返回 ID，然後您嘗試使用該 ID 鎖定該行。PostgreSQL 看到其他人對該行有一個鎖，因此它等待該鎖被釋放。然後它重新檢查WHERE外部查詢上的子句以確保它仍然匹配（如果該行被刪除，或者是UPDATEd）。因為內部查詢是一個不相關的子查詢，所以它不會重新計算它，所以內部WHERE子句不會重新執行，WHERE如果你不重複，你可以獲得不再匹配內部子句的行它在外部WHERE子句中。

混合行限制和行鎖定很難。建構實際上比單個工人表現更好的正確排隊系統更加困難。使用現成的排隊系統，為自己省去很多麻煩。

（PostgreSQL 9.5 將會有SKIP LOCKED，這使得這種方式更容易，但從現在起一年多內不會有穩定的版本，所以不要屏住呼吸。）

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