Postgresql

Postgres 更新…限制 1

  • April 16, 2021

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

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

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

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

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

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

沒有並發寫訪問

CTE(公用表表達式)中實現選擇並FROMUPDATE.

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 READSERIALIZABLE) 仍可能導致序列化錯誤。看:

在並發寫入負載下,添加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.

有關的:

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則重新評估條件,如果不再存在TRUEstatus已更改),則 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

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

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

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

或者(對於順序掃描更便宜)將條件嵌套在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(). 手冊:

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

有關的:

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