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
) 仍可能導致序列化錯誤。看:在並發寫入負載下,添加
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
則重新評估條件,如果不再存在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
列(或任何具有隱式轉換的類型,如int4
orint2
)。如果建議鎖同時用於數據庫中的多個表,請消除歧義
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'
通常,您可以忽略這一點。為了保證只有符合條件的行被鎖定,您可以將謂詞嵌套在像上面這樣的 CTE 或帶有
OFFSET 0
hack (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()
. 手冊:一旦在會話級別獲得,建議鎖定將一直保持,直到顯式釋放或會話結束。
有關的: