Postgres 更新…限制 1

我有一個 Postgres 數據庫，其中包含有關伺服器集群的詳細資訊，例如伺服器狀態（“活動”、“備用”等）。活動伺服器在任何時候都可能需要故障轉移到備用伺服器，我不關心具體使用哪個備用伺服器。

我想要一個數據庫查詢來更改備用伺服器的狀態 - 只是一個 - 並返回要使用的伺服器 IP。選擇可以是任意的：因為伺服器的狀態會隨著查詢而改變，選擇哪個備用伺服器並不重要。

是否可以將我的查詢限制為一次更新？

這是我到目前為止所擁有的：

UPDATE server_info SET status = 'active' 
WHERE status = 'standby' [[LIMIT 1???]] 
RETURNING server_ip;

Postgres 不喜歡這樣。我能做些什麼不同的事情？

沒有並發寫訪問

在CTE（公用表表達式）中實現選擇並FROM在UPDATE.

WITH cte AS (
  SELECT server_ip          -- pk column or any (set of) unique column(s)
  FROM   server_info
  WHERE  status = 'standby'
  LIMIT  1                  -- arbitrary pick (cheapest)
  )
UPDATE server_info s
SET    status = 'active' 
FROM   cte
WHERE  s.server_ip = cte.server_ip
RETURNING s.server_ip;

我最初在這裡有一個普通的子查詢，但是正如飛克指出的那樣，它可以迴避LIMIT某些查詢計劃：

規劃器可以選擇生成一個在LIMITing子查詢上執行嵌套循環的計劃，從而導致UPDATEs多於LIMIT，例如：

 Update on buganalysis [...] rows=5

  ->  Nested Loop
        ->  Seq Scan on buganalysis
        ->  Subquery Scan on sub [...] loops=11
              ->  Limit [...] rows=2
                    ->  LockRows
                          ->  Sort
                                ->  Seq Scan on buganalysis

重現測試案例

解決上述問題的方法是將LIMIT子查詢包裝在它自己的 CTE 中，因為 CTE 是物化的，它不會在嵌套循環的不同迭代中返回不同的結果。

或者對簡單的情況使用低相關的子查詢LIMIT 1。更簡單，更快：

UPDATE server_info
SET    status = 'active' 
WHERE  server_ip = (
        SELECT server_ip
        FROM   server_info
        WHERE  status = 'standby'
        LIMIT  1
        )
RETURNING server_ip;

具有並發寫訪問權限

假設所有這些的預設隔離級別**READ COMMITTED**。更嚴格的隔離級別 (REPEATABLE READ和SERIALIZABLE) 仍可能導致序列化錯誤。看：

• SELECT … FOR UPDATE SKIP LOCKED 鎖定在 REPEATABLE READ 事務中

在並發寫入負載下，添加FOR UPDATE SKIP LOCKED以鎖定行以避免競爭條件。SKIP LOCKED在 Postgres 9.5中添加，舊版本見下文。手冊：

使用SKIP LOCKED，將跳過任何無法立即鎖定的選定行。跳過鎖定的行提供了不一致的數據視圖，因此這不適用於通用工作，但可用於避免多個消費者訪問類似隊列的表時的鎖爭用。

UPDATE server_info
SET    status = 'active' 
WHERE  server_ip = (
        SELECT server_ip
        FROM   server_info
        WHERE  status = 'standby'
        LIMIT  1
        **FOR UPDATE SKIP LOCKED**
        )
RETURNING server_ip;

如果沒有剩餘的符合條件的未鎖定行，則此查詢中不會發生任何事情（沒有更新任何行）並且您會得到一個空結果。對於不重要的操作，這意味著您已完成。

但是，並發事務可能已鎖定行，但隨後未完成更新（ROLLBACK或其他原因）。為了確保執行最終檢查：

SELECT NOT EXISTS (
  SELECT FROM server_info
  WHERE  status = 'standby'
  );

SELECT也看到鎖定的行。即使不返回true，一或多行仍未完成，事務仍可能回滾。（或者同時添加了新行。）等一下，然後循環兩個步驟：（UPDATE直到你沒有得到任何行回來；SELECT…）直到你得到true.

有關的：

• Postgres 中的原子更新 .. SELECT

SKIP LOCKED在 PostgreSQL 9.4 或更早版本中沒有

UPDATE server_info
SET    status = 'active' 
WHERE  server_ip = (
        SELECT server_ip
        FROM   server_info
        WHERE  status = 'standby'
        LIMIT  1
        **FOR UPDATE**
        )
RETURNING server_ip;

試圖鎖定同一行的並發事務被阻塞，直到第一個釋放它的鎖。

如果第一個被回滾，則下一個事務獲取鎖並正常進行；隊列中的其他人繼續等待。

如果第一次送出，WHERE則重新評估條件，如果不再存在TRUE（status已更改），則 CTE（有點令人驚訝）不返回任何行。什麼都沒發生。當所有事務都想更新同一**行時****，**這是期望的行為。

但不是當每個事務都想更新下一行時。而且由於我們只想更新任意（或隨機）行**，因此根本沒有必要等待。

我們可以在諮詢鎖的幫助下解除阻塞：

UPDATE server_info
SET    status = 'active' 
WHERE  server_ip = (
        SELECT server_ip
        FROM   server_info
        WHERE  status = 'standby'
        **AND pg_try_advisory_xact_lock(id)**
        LIMIT  1
        FOR    UPDATE
        )
RETURNING server_ip;

這樣，尚未鎖定的下一行將被更新。每個事務都有一個新的行來處理。我從捷克 Postgres Wiki那裡得到了這個技巧的幫助。

id是任何唯一的bigint列（或任何具有隱式轉換的類型，如int4or int2）。

如果建議鎖同時用於數據庫中的多個表，請消除歧義pg_try_advisory_xact_lock(tableoid::int, id)-id在此處是唯一的integer。

既然tableoid是bigint量，理論上可以溢出integer。如果您足夠偏執，請(tableoid::bigint % 2147483648)::int改用-為真正的偏執狂留下理論上的“雜湊衝突”…

此外，Postgres 可以自由地WHERE以任何順序測試條件。它可以**在之前測試 pg_try_advisory_xact_lock()並獲取一個鎖，這可能會導致在不相關的行上產生額外的諮詢鎖，但事實並非如此。關於 SO 的相關問題： status = 'standby'``status = 'standby'

• Postgres pg_try_advisory_lock 阻塞所有記錄

通常，您可以忽略這一點。為了保證只有符合條件的行被鎖定，您可以將謂詞嵌套在像上面這樣的 CTE 或帶有OFFSET 0hack (prevents inlining)的子查詢中。例子：

• 將 pg_try_advisory_xact_lock() 放在嵌套子查詢中？

或者（對於順序掃描更便宜）將條件嵌套在CASE如下語句中：

WHERE  CASE WHEN status = 'standby' THEN pg_try_advisory_xact_lock(id) END

然而，這個CASE技巧也會阻止 Postgres 在status. 如果這樣的索引可用，您就不需要額外的嵌套：只有符合條件的行將被鎖定在索引掃描中。

由於您無法確定每次呼叫都使用索引，因此您可以：

WHERE  status = 'standby'
AND    CASE WHEN status = 'standby' THEN pg_try_advisory_xact_lock(id) END

這CASE在邏輯上是多餘的，但它服務於討論的目的。

如果命令是長事務的一部分，請考慮可以（並且必須）手動釋放的會話級鎖。因此，您可以在完成鎖定行後立即解鎖：pg_try_advisory_lock()和pg_advisory_unlock(). 手冊：

一旦在會話級別獲得，建議鎖定將一直保持，直到顯式釋放或會話結束。

有關的：

• 在 Postgres 中優化並發更新

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