鎖的設計#

拜快照隔離之賜，heap 元組不必為了讀取而上鎖。然而兩筆寫入交易絕不能被允許同時修改同一列——此時列必須被鎖住。

但重量鎖不是好選擇：

• 每個重量鎖都佔用伺服器共享記憶體（數百位元組，還不算所有支援用的基礎設施）
• PostgreSQL 的內部機制並非為處理巨量並行重量鎖而設計

有些資料庫系統以鎖升級解決這個問題：列層級鎖太多時，就換成單一較粗粒度的鎖（例如頁面層級或表格層級）。這簡化了實作，卻可能大幅限制系統吞吐量。

列層級鎖實質上是 heap 頁面裡的屬性，而非真正的鎖，而且它們完全不反映在 RAM 中。

列通常在被更新或刪除時上鎖。兩種情況下，該列的目前版本都被標記為已刪除——用來標記的屬性就是寫在 xmax 欄位的目前交易 ID，而正是同一個 ID（搭配額外的提示位元）表示該列被鎖住。

若某交易想修改一列，卻在其目前版本的 xmax 欄位看到一個活躍的交易 ID，它就必須等該交易完成。交易一結束，所有鎖被釋放，等待中的交易即可繼續。

缺點：由於 RAM 中沒有這類鎖的資訊，其他行程無法形成佇列。因此仍然需要重量鎖：等待某列被釋放的行程，會請求「目前佔用該列的那筆交易的 ID」上的鎖。

結果是：重量鎖的數量正比於並行行程數，而非被修改的列數。

列層級鎖的四種模式#

列層級鎖支援四種模式：兩種為排他鎖（同時只能由一筆交易取得），另兩種為共享鎖（可同時由多筆交易持有）。

排他模式#

• Update：允許修改任何元組欄位，甚至刪除整個元組
• No Key Update：只允許「不涉及唯一索引相關欄位」的變更（換言之，外鍵不得受影響）

實驗：更新第一個帳戶的餘額（鍵不變）與第二個帳戶的 id（鍵被更新）：

=> UPDATE accounts SET amount = amount + 100.00 WHERE id = 1;
=> UPDATE accounts SET id = 20 WHERE id = 2;

=> SELECT * FROM row_locks('accounts',0) LIMIT 2;
 ctid | xmax | lock_only | is_multi | keys_upd | keyshr | shr
−−−−−−−+−−−−−−−−+−−−−−−−−−−−+−−−−−−−−−−+−−−−−−−−−−+−−−−−−−−+−−−−−
 (0,1) | 122858 |           |          |          |        |
 (0,2) | 122858 |           |          | t        |        |
(2 rows)

鎖定模式由 keys_updated 提示位元決定。

SELECT FOR ... 指令使用同一個 xmax 欄位作為鎖定屬性，但此時還必須設定 xmax_lock_only 提示位元——它表示該元組被鎖住但未被刪除，意即它仍是目前版本：

=> SELECT * FROM accounts WHERE id = 1 FOR NO KEY UPDATE;
=> SELECT * FROM accounts WHERE id = 2 FOR UPDATE;
=> SELECT * FROM row_locks('accounts',0) LIMIT 2;
 ctid | xmax | lock_only | is_multi | keys_upd | keyshr | shr
−−−−−−−+−−−−−−−−+−−−−−−−−−−−+−−−−−−−−−−+−−−−−−−−−−+−−−−−−−−+−−−−−
 (0,1) | 122859 | t         |          |          |        |
 (0,2) | 122859 | t         |          | t        |        |
(2 rows)

=> CREATE FUNCTION row_locks(relname text, pageno integer)
RETURNS TABLE(
  ctid tid, xmax text, lock_only text, is_multi text,
  keys_upd text, keyshr text, shr text
)
AS $$
SELECT (pageno,lp)::text::tid,
  t_xmax,
  CASE WHEN t_infomask & 128    = 128   THEN 't' END,
  CASE WHEN t_infomask & 4096 = 4096 THEN 't' END,
  CASE WHEN t_infomask2 & 8192 = 8192 THEN 't' END,
  CASE WHEN t_infomask & 16     = 16    THEN 't' END,
  CASE WHEN t_infomask & 16+64 = 16+64 THEN 't' END
FROM heap_page_items(get_raw_page(relname,pageno))
ORDER BY lp;
$$ LANGUAGE sql;

共享模式#

• Share：需要讀取某列、但必須禁止其他交易修改它時使用
• Key Share：允許更新除鍵屬性之外的任何元組欄位

所有共享模式中，PostgreSQL 核心只使用 Key Share，用於檢查外鍵。由於它與 No Key Update 排他模式相容，外鍵檢查不會干擾非鍵屬性的並行更新。應用程式則可自由使用任何共享模式。

再強調一次：單純的 SELECT 指令從不使用列層級鎖。

多重交易#

鎖定屬性由 xmax 欄位表示，設為取得該鎖的交易 ID。那麼，多筆交易同時持有的共享鎖，這個屬性該怎麼設？

處理共享鎖時，PostgreSQL 採用所謂的多重交易（multitransaction，multixact）：這是一群被指派了獨立 ID 的交易。

• 成員與其鎖定模式的詳細資訊存放在 PGDATA/pg_multixact 目錄下的檔案中
• 為加速存取，被鎖頁面快取在伺服器共享記憶體中
• 所有變更都被記錄到 WAL 以確保容錯

Multixact ID 與一般交易 ID 同為 32 位元，但獨立配發。這表示交易與多重交易可能有相同的 ID；為區分兩者，PostgreSQL 使用額外的提示位元 xmax_is_multi。

實驗：在既有的 Key Share 之上再加一個另一筆交易取得的 No Key Update（兩者相容）：

=> SELECT * FROM row_locks('accounts',0) LIMIT 2;
 ctid | xmax | lock_only | is_multi | keys_upd | keyshr | shr
−−−−−−−+−−−−−−−−+−−−−−−−−−−−+−−−−−−−−−−+−−−−−−−−−−+−−−−−−−−+−−−−−
 (0,1) | 1      |           | t        |          |        |
 (0,2) | 122860 | t         |          |          | t      | t
(2 rows)

xmax_is_multi 位元顯示第一列使用的是 multixact ID 而非一般交易 ID。

用 pgrowlocks 擴充可顯示所有可能的列層級鎖資訊：

=> CREATE EXTENSION pgrowlocks;
=> SELECT * FROM pgrowlocks('accounts') \gx
−[ RECORD 1 ]−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
locked_row | (0,1)
locker     | 1
multi      | t
xids       | {122860,122861}
modes      | {"Key Share","No Key Update"}
pids       | {30423,30723}

這看起來很像查詢 pg_locks 視圖，但 pgrowlocks 函式必須存取 heap 頁面——因為 RAM 中根本沒有列層級鎖的資訊。

多重交易的凍結#

由於 multixact ID 是 32 位元，它們也會因計數器上限而迴繞，就像一般交易 ID 一樣。因此 PostgreSQL 必須以類似凍結的方式處理它們：舊的 multixact ID 被換成新的（或者，若屆時只剩一筆交易持有鎖，就換成一般交易 ID）。

多重交易的凍結可透過與一般凍結相當類似的組態參數管理：vacuum_multixact_freeze_min_age、vacuum_multixact_freeze_table_age、autovacuum_multixact_freeze_max_age，以及 PostgreSQL 14 新增的 vacuum_multixact_failsafe_age。

等待佇列#

排他模式的四步流程#

由於列層級鎖只是一個屬性，佇列的安排方式並不單純。交易要修改某列時必須遵循以下步驟：

1. 若 xmax 欄位與提示位元顯示該列已被以不相容模式鎖住，就對正被修改的元組取得一個排他重量鎖（tuple lock）
2. 必要時，藉由請求 xmax 交易 ID 上的鎖（若 xmax 含 multixact ID 則是多筆交易），等待所有不相容的鎖被釋放
3. 把自己的 ID 寫進元組標頭的 xmax，並設定所需的提示位元
4. 若第一步取得了元組鎖，釋放它

看似步驟 1 與 4 是多餘的、只要等所有鎖定交易結束就好。但若多筆交易試圖更新同一列，它們全都會在那筆正在處理該列的交易上等待。該交易一完成，它們就陷入爭奪鎖定權的競爭狀態，某些「倒楣」的交易可能要等上無限久——這種情況稱為資源飢餓（resource starvation）。

元組鎖識別出佇列中的第一筆交易，保證它會是下一個取得鎖的。

實驗：四筆交易更新同一列#

T1（pid 30723）更新，走完四個步驟，持有表格鎖與自身交易 ID 的鎖：

 30723 | relation      | accounts | RowExclusiveLock | t
 30723 | transactionid | 122863   | ExclusiveLock    | t

T2（pid 30794）更新同一列 → 掛在第 2 步。除了表格與自身 ID 的鎖，還多兩個鎖：第 1 步取得的元組鎖，以及第 2 步請求的 T1 交易 ID 上的鎖：

 30794 | relation      | accounts      | RowExclusiveLock | t
 30794 | transactionid | 122863        | ShareLock        | f
 30794 | transactionid | 122864        | ExclusiveLock    | t
 30794 | tuple         | accounts(0,1) | ExclusiveLock    | t

T3（pid 30865）卡在第 1 步——它試圖取得元組鎖就停住了：

 30865 | tuple         | accounts(0,1) | ExclusiveLock    | f

T4 以及所有後續交易在這點上與 T3 無異：全都在同一個元組鎖上等待。

=> SELECT pid, wait_event_type, wait_event, pg_blocking_pids(pid)
FROM pg_stat_activity WHERE pid IN (30723,30794,30865,30936);
  pid | wait_event_type | wait_event     | pg_blocking_pids
−−−−−−−+−−−−−−−−−−−−−−−−−+−−−−−−−−−−−−−−−+−−−−−−−−−−−−−−−−−−
 30723 | Client          | ClientRead    | {}
 30794 | Lock            | transactionid | {30723}
 30865 | Lock            | tuple         | {30794}
 30936 | Lock            | tuple         | {30794,30865}
(4 rows)

佇列如何瓦解#

若 T1 被中止，一切如預期運作：所有後續交易依序前進一步，沒有人插隊。

但更可能的是 T1 被提交：

• 在 Repeatable Read 或 Serializable 層級，這會造成序列化失敗，T2 必須被中止（佇列中所有後續交易也會被中止）
• 在 Read Committed 層級，被修改的列會被重讀，更新重試

T1 提交後，T2 醒來並成功完成第 3、4 步。T2 一釋放元組鎖，T3 也醒來——但它發現新元組的 xmax 欄位已經含有不同的 ID。此時上述流程結束；在 Read Committed 層級會再進行一次鎖定嘗試，但不再遵循前述步驟：

 30865 | transactionid | 122864   | ShareLock        | f
 30865 | transactionid | 122865   | ExclusiveLock    | t

T4 也是如此。

現在 T3 與 T4 都在等 T2 完成，冒著陷入競爭狀態的風險——佇列實質上已經瓦解了。若還有其他交易在佇列尚存時加入，它們全都會被捲進這場競爭。

結論：在多個並行行程中更新同一列不是好主意。 高負載下，這個熱點會迅速變成造成效能問題的瓶頸。

共享模式會插隊#

PostgreSQL 只為參照完整性檢查取得共享鎖。在高負載應用中使用它們可能導致資源飢餓，而兩層鎖定模型無法防止這種結果。

回顧四步流程：前兩步意味著若鎖定模式相容，交易就會插隊。

實驗：

1. T1 以 FOR SHARE 鎖住某列
2. T2 試圖 UPDATE 同一列——不被允許（Share 與 No Key Update 不相容），於是持有元組鎖等待
3. T3 以 FOR SHARE 鎖住該列——這與已取得的鎖相容，於是它直接插隊：

=> SELECT * FROM pgrowlocks('accounts') \gx
locked_row | (0,1)
locker     | 2
multi      | t
xids       | {122869,122871}
modes      | {Share,Share}
pids       | {30723,30865}

此時 T1 若完成，T2 醒來卻發現該列仍被鎖住，只好回到佇列——但這次它排在 T3 後面：

 30794 | transactionid | 122870        | ExclusiveLock    | t
 30794 | transactionid | 122871        | ShareLock        | f
 30794 | tuple         | accounts(0,1) | ExclusiveLock    | t

只有等 T3 也完成，T2 才能執行更新——除非這段時間內又冒出其他共享鎖。

外鍵檢查不太可能造成問題，因為鍵屬性通常維持不變，且 Key Share 可與 No Key Update 並用。

但多數情況下，應用程式應避免使用共享列層級鎖。

無等待鎖#

SQL 指令通常會等待所請求的資源被釋放。但有時「鎖無法立即取得就取消操作」更合理，為此 SELECT、LOCK、ALTER 等指令提供 NOWAIT 子句。

=> SELECT * FROM accounts
FOR UPDATE NOWAIT;
ERROR:   could not obtain lock on row in relation "accounts"

這類錯誤可由應用程式碼捕捉並處理。

SKIP LOCKED#

少數情況下，跳過已被鎖住的列、直接開始處理可用的列會很方便，這正是 SELECT FOR UPDATE SKIP LOCKED 的作用：

=> SELECT * FROM accounts
ORDER BY id
FOR UPDATE SKIP LOCKED
LIMIT 1;
 id | client | amount
−−−−+−−−−−−−−+−−−−−−−−
  2 | bob    | 200.00
(1 row)

第一列（已被鎖住）被跳過，查詢鎖住並回傳了第二列。

這種做法讓我們能批次處理列或平行處理事件佇列。但別為這個指令發明其他用途——多數任務用簡單得多的方法就能解決。

逾時#

最後，設定逾時也能避免長時間等待：

=> SET lock_timeout = '1s';
=> ALTER TABLE accounts DROP COLUMN amount;
ERROR:    canceling statement due to lock timeout

指令因一秒內未能取得鎖而以錯誤結束。逾時不只能在 session 層級設定，也能設在更低層級（例如特定交易）。

這方法能防止「在負載下執行需要排他鎖的指令」時發生長時間等待。發生錯誤後，該指令可稍後重試。

注意區別：statement_timeout 限制運算子執行的總時間，而 lock_timeout 定義的是等待鎖所能花費的最長時間。

死結#

交易有時會需要另一筆交易正在使用的資源，而後者又可能在等第三筆交易鎖住的資源，依此類推——這些交易透過重量鎖排隊。

但偶爾某筆已在佇列中的交易又需要另一個資源，於是它得再次加入同一個佇列等待。死結（deadlock）就此發生：佇列出現了無法自行化解的循環依賴。

為便於視覺化，可畫出等待圖（wait-for graph）：節點代表活躍行程，箭頭從「等待鎖的行程」指向「持有這些鎖的行程」。若圖中存在環（某節點沿箭頭能回到自己），就代表發生了死結。

圖示中通常畫的是交易而非行程。這種替換通常可接受，因為一筆交易由一個行程執行，且鎖只能在交易內取得。但一般而言講「行程」更正確——某些鎖在交易完成時未必立刻釋放。

自動偵測機制#

若發生死結且無人設定逾時，交易將永遠互相等待。因此鎖管理器會執行自動死結偵測。

然而這項檢查需要一些成本，不該在每次請求鎖時都浪費（畢竟死結不常發生）。因此：

• 行程若取鎖失敗、加入佇列後睡眠，PostgreSQL 會自動設定一個由 deadlock_timeout（預設 1 秒）定義的逾時
• 若資源更早可用——很好，就省下了檢查的額外成本
• 但若等待超過 deadlock_timeout，等待中的行程就醒來並發起檢查

檢查的實質內容是建立等待圖並搜尋其中的環。為了「凍結」圖的當下狀態，PostgreSQL 在整個檢查期間停止一切重量鎖的處理。

• 未偵測到死結 → 行程再次睡眠，遲早會輪到它
• 偵測到死結 → 其中一筆交易被強制終止，釋放其鎖，讓其他交易得以繼續

多數情況下被中斷的是發起檢查的那筆交易。但若環中包含一個 autovacuum 行程、且它當下並非正在為防止迴繞而凍結元組，伺服器會終止 autovacuum——因為它優先權較低。

• 伺服器日誌中的對應訊息
• pg_stat_database 表格中不斷增加的 deadlocks 值

因列更新順序不同而死結#

儘管死結最終由重量鎖造成，多數是因為列層級鎖被以不同順序取得。

轉帳情境：

-- T1：從帳戶 1 扣 100
=> UPDATE accounts SET amount = amount - 100.00 WHERE id = 1;

    -- T2：從帳戶 2 扣 10
    => UPDATE accounts SET amount = amount - 10.00 WHERE id = 2;

-- T1：想加到帳戶 2 → 被鎖住
=> UPDATE accounts SET amount = amount + 100.00 WHERE id = 2;

    -- T2：想加到帳戶 1 → 也被鎖住
    => UPDATE accounts SET amount = amount + 10.00 WHERE id = 1;

這個循環等待永遠不會自行化解。一秒內無法取得資源的 T1 發起死結檢查，並被伺服器中止：

ERROR: deadlock detected
DETAIL: Process 30423 waits for ShareLock on transaction 122877;
blocked by process 30723.
Process 30723 waits for ShareLock on transaction 122876; blocked by
process 30423.
HINT: See server log for query details.
CONTEXT: while updating tuple (0,2) in relation "accounts"

兩個 UPDATE 之間的死結#

有些情況死結看似不可能，卻真的會發生。

我們通常假設 SQL 指令是原子的，但真的是嗎？ 仔細看 UPDATE：它是在列被更新的當下逐一上鎖，而非一次全部鎖住，也不是同時發生。

因此若一個 UPDATE 以某種順序修改數列，而另一個以不同順序做同樣的事，死結就可能發生。

=> CREATE INDEX ON accounts(amount DESC);
=> CREATE FUNCTION inc_slow(n numeric)
RETURNS numeric
AS $$
  SELECT pg_sleep(1);
  SELECT n + 100.00;
$$ LANGUAGE sql;

=> UPDATE accounts SET amount = inc_slow(amount);

    => SET enable_seqscan = off;
    => UPDATE accounts SET amount = inc_slow(amount)
    WHERE amount > 100.00;

=> SELECT locked_row, locker, modes FROM pgrowlocks('accounts');
 locked_row | locker |       modes
−−−−−−−−−−−−+−−−−−−−−+−−−−−−−−−−−−−−−−−−−
 (0,1)      | 122883 | {"No Key Update"}     ← 第一個
 (0,3)      | 122884 | {"No Key Update"}     ← 第二個
(2 rows)

ERROR: deadlock detected
CONTEXT: while updating tuple (0,2) in relation "accounts"

儘管這類情況看似不可能，在執行批次列更新的高負載系統中確實會發生。