在 Postgres 中，FOR UPDATE 鎖定父資料列，而非子資料列

在 PostgreSQL 中，一個資料列鎖（row lock）保護一個資料列。它不保護指向它的那組資料列。在預設的 READ COMMITTED 隔離等級下，對父資料列執行 SELECT ... FOR UPDATE 並不表示任何關於哪些子資料列存在、另一個會話（session）即將插入哪些子資料列，或哪些子資料列正在改變狀態的資訊。這篇文章追蹤了三個因將資料列鎖誤讀為對關聯關係的鎖而產生的並行性錯誤，以及修正它們的查詢形式、交易邊界和逾時設定。

資料列鎖定並非述詞鎖定

SELECT ... FOR UPDATE 會標記該陳述式傳回的資料列。另一個嘗試更新、刪除或鎖定相同資料列的交易，會一直等到您提交或回復為止。這就是全部的保證。它對於您的陳述式執行時不存在的資料列，以及其他表格中參考您鎖定之資料列的資料列，隻字未提。

這種誤解來自於我們的談論方式。「我鎖定了父項，所以沒有人可以動它」悄悄地變成了「沒有人可以動任何屬於它的東西」。在文件模型中，這種解讀是成立的，因為子項存在於父項記錄之內。在關聯式綱要中，它們是各自擁有獨立鎖定的不同資料列。

PostgreSQL 確實有一種模式可以對集合進行推論：在 SERIALIZABLE 模式下，它會追蹤述詞讀取，並在兩個交易的讀取和寫入集合發生衝突時，中止其中一個交易，這也包括透過尚未存在的資料列所造成的衝突。預設是 READ COMMITTED，在該模式下，每個陳述式都會取得一個新的快照，且不會追蹤任何述詞。在這種情況下，涵蓋一組資料列的不變性必須由一個觸及整個集合的陳述式來強制執行，或是透過將所有寫入者序列化，使其都在它們共同鎖定的單一資料列後排隊。

陷阱 1：兩個狀態範圍更新之間的間隙

情境設定：父資料列可以被停用（一種軟刪除）。當父資料列被停用時，其下的每個子資料列都會移至 rejected 狀態，而呼叫者需要為稽核紀錄取得每個先前狀態的計數。直覺的作法是為每個狀態撰寫一個陳述式。

-- Two statements, one logical intent. The gap between them is the bug.
UPDATE child SET status = 'rejected'
 WHERE parent_id = $1 AND status = 'confirmed';   -- count A

UPDATE child SET status = 'rejected'
 WHERE parent_id = $1 AND status = 'candidate';   -- count B

這兩個陳述句的作用域都限定於父項，本身也都是不可分割的，並且由一個交易包覆，所以這看起來是安全的。但事實並非如此。在這兩者之間，另一個工作階段可以提交一個子資料列，將其狀態從 candidate 變更為 confirmed。第一個陳述句已經執行，且沒有看到它為已確認狀態；第二個陳述句根據 candidate 進行篩選，也再也看不到它是候選狀態。該資料列不符合任何一個篩選條件，並在一個已淘汰的父項下保持 confirmed 狀態。

在頂部加上 SELECT ... FROM parent WHERE id = $1 FOR UPDATE 並不能彌補這個漏洞。該鎖定涵蓋了 parent 中的一筆資料列。被提升的子資料列存在於 child 中，其寫入者從不需要父項鎖定，因為外來鍵檢查會在插入和鍵欄位更新時觸發，而不是在狀態變更時觸發。這兩個計數也都是錯的，因為該資料列從 A 和 B 中都遺失了。

此修復為單一陳述，其鎖定目標集合，然後確切地寫入該集合。

WITH target AS MATERIALIZED (
  SELECT id, status AS old_status FROM child
   WHERE parent_id = $1 AND status IN ('confirmed','candidate') FOR UPDATE
), upd AS (
  UPDATE child c SET status = 'rejected'
    FROM target t WHERE c.id = t.id RETURNING t.id, t.old_status
) SELECT id, old_status FROM upd

target 內的 FOR UPDATE 會鎖定每個匹配的子資料列。如果另一個會話在該陳述式的快照之後，對其中一個資料列提交了變更，PostgreSQL 會等待鎖定，然後針對新提交的版本重新評估 WHERE 子句。該機制就是 EvalPlanQual。一個剛變成 confirmed 的資料列仍然滿足 status IN ('confirmed','candidate')，並與其餘的一起被降級。一個剛移至 rejected 的資料列會正確地被排除，因為它已經處於目標狀態。

計數結果會是正確的，也是出於相同的原因：它們描述的是該陳述式實際鎖定並變更的資料列，而非在不同時刻取樣的兩個計數。MATERIALIZED 在此不會改變 CTE 的執行頻率，因為它只被引用一次；它陳述了意圖，也就是固定目標集合，並確切地對該集合進行操作。

陷阱 2：無鎖讀取的防護，並非防護

相同的功能需要相反的防禦機制：一旦父節點被停用，就不能在其下插入新的子節點，也不能確認任何現有的子節點。這個檢查最自然的位置，就是寫入路徑的頂端。

// Broken: the read runs in its own implicit transaction, so any lock it
// takes is released before the write begins.
func AddChild(ctx context.Context, pool *pgxpool.Pool, parentID int64, label string) error {
	status, err := parentStatus(ctx, pool, parentID) // own connection, own snapshot
	if err != nil {
		return err
	}
	if status == statusRejected {
		return ErrParentRejected
	}
	return insertChild(ctx, pool, parentID, label) // new transaction, new snapshot
}

這段程式碼可以編譯、通過審查，但什麼也沒強制執行。原因在於一個程式碼從未請求的鎖定。

對 child 的插入操作必須驗證其外來鍵，而該驗證會在被參考的父資料列上取得一個 FOR KEY SHARE 鎖定。FOR KEY SHARE 與 FOR UPDATE 衝突，因此當級聯操作持有父資料列的鎖定時，插入操作會在進行外來鍵檢查時被阻塞並等待。這部分是您所期望的行為。

問題在於防護性讀取相對於該等待的位置。它在較早的時間點、於一個不同的連線中、在其自己的單一陳述式交易裡執行，並且因為級聯操作尚未提交，所以看到了 candidate。接著插入操作被阻塞，級聯操作提交，然後插入操作被喚醒並成功執行。外來鍵鎖定並未讓這種交錯執行變得不太可能。它反而使其成為偏好的情況，因為它會將插入操作擱置，直到級聯操作完成為止。

修正方法是將防護性讀取與寫入操作在同一個交易中執行，並持有一個與級聯操作衝突的鎖定。

SELECT status FROM parent WHERE id = $1 FOR SHARE

FOR SHARE 與 FOR UPDATE 衝突，因此守衛會等待級聯，而不是繞過它讀取，且一旦鎖定被授予，EvalPlanQual 會交回新提交的版本。守衛看到 rejected 並拒絕。FOR SHARE 也與 FOR NO KEY UPDATE 衝突，這是一個普通 UPDATE parent SET status = ... 隱含採用的模式，因此即使是從未寫入鎖定子句的級聯，它也會等待。兩個同時存在的守衛仍然不會互相阻塞，因為共享鎖是相容的。

// Fixed: guard and write share one transaction, and the guard holds a lock
// that conflicts with the cascade until that transaction commits.
func AddChildTx(ctx context.Context, tx pgx.Tx, parentID int64, label string) error {
	var status string
	err := tx.QueryRow(ctx,
		`SELECT status FROM parent WHERE id = $1 FOR SHARE`, parentID).Scan(&status)
	if err != nil {
		return err
	}
	if status == statusRejected {
		return ErrParentRejected
	}
	return insertChildTx(ctx, tx, parentID, label)
}

通則就是：函式簽章決定了 guard 是否能正常運作。接收 transaction handle 的函式會繼承呼叫端的 transaction，因此 guard 的鎖會持續到該 transaction commit 為止。接收 connection pool 的函式會開啟自己的 transaction，並在返回前釋放鎖。兩者都能通過型別檢查，在 diff 中看起來也一樣，但只有其中一種能真正執行任何強制措施。

陷阱 3：每個新的鎖都會增加一個新的等待點

一個取得鎖的守衛會改變先前沒有鎖的路徑的時序，而它的對手不僅僅是你剛寫的級聯操作。任何長時間持有父資料列的交易，現在都會延遲該父資料列下的每一次寫入。一個鎖定父資料列、緩慢工作並在最後才提交的夜間作業，將在其整個執行期間卡住使用者的確認點擊。資料列鎖的等待沒有預設的逾時時間。

所以，請將鎖及其界限一起引入。

BEGIN;
SET LOCAL lock_timeout = '3s';
SELECT status FROM parent WHERE id = $1 FOR SHARE;

SET LOCAL 將設定的作用域限定在目前的交易，因此它不會洩漏到該連線池連線的下一個使用者。當等待時間超過界限時，PostgreSQL 會引發 SQLSTATE 55P03 (lock_not_available)，這需要轉譯成呼叫者可以據以行動的內容。

var pgErr *pgconn.PgError
if errors.As(err, &pgErr) && pgErr.Code == "55P03" { // lock_not_available
	return ErrBusyRetryLater
}

對於使用者正在等待的回應，55P03 的意思是「此記錄正忙，請稍後再試」。對於遍歷許多父項的背景處理，它的意思是「跳過這筆，下次執行再處理」，而不是「中止批次處理」。搞錯這一點會讓一次點擊變成一次失敗的夜間執行：某人確認了一筆子項，批次處理觸及 3 秒的界限，將其視為致命錯誤，並在還有數百個父項未處理的情況下停止。NOWAIT 和 SKIP LOCKED 是相同概念的更精確版本。

鎖定順序、時間戳記，以及固定行為的測試

有三個細節決定了上述修正在真實程式碼庫中能否保持正確。

首先是鎖定順序。死鎖的發生需要兩個路徑以相反的順序取得相同的兩筆資料列。如果每個路徑都在處理子項前先取得父項，就不會形成循環，而新的防護機制也會遵循這個順序，因為它會先讀取父項。只要有一個路徑在鎖定父項前先鎖定子項，就足以產生循環，所以要透過搜尋來驗證，而不是憑記憶：用 grep 指令在寫入路徑中尋找對子資料表的鎖定讀取和更新，並檢查每個路徑已持有哪些鎖。

其次是時間戳記，它所造成的錯誤是無聲無息的。像這樣的大量轉換通常會動到例如 reviewed_at 這樣的欄位，這個欄位是用來記錄某人對該資料列做出判斷的時間。如果儀表板依據該欄位將計數分組，在連鎖反應中蓋上時間戳記會改寫歷史紀錄：昨天判斷的資料列現在會被算作今天的拒絕項目，而上週的數字在沒有任何人編輯的情況下也跟著變動。大量操作的後果並非人為判斷，所以不要動那個時間戳記。

第三是測試。轉換分支是否互斥，是目前狀態和請求狀態的一個純函數，所以應將其從 SQL 路徑中抽離出來，並用表格測試將其行為固定下來。在這次的工作中，一個停用已停用父項的請求，掉入了恢復分支，因為分支條件是各自獨立且不互斥的條件式。程式碼編譯通過，現有測試也通過了，手動檢查也沒發現，因為當前的客戶端使用了一個較新的端點，只有一個較舊的路徑會走到那個分支。

tests := []struct {
	name    string
	current string
	request string
	want    action
}{
	{"retire a live parent", statusActive, statusRejected, actionCascade},
	{"retire an already retired parent", statusRejected, statusRejected, actionNoop},
	{"revive a retired parent", statusRejected, statusActive, actionRevive},
	{"revive a live parent", statusActive, statusActive, actionNoop},
}

常見問題

我應該改用 SERIALIZABLE 嗎？

它解決了根本原因：述詞衝突（predicate conflicts），包括插入尚不存在的資料列，會被偵測到，其中一個交易會因 SQLSTATE 40001 而中止。代價是每個交易都變得可重試，因此每個寫入路徑都需要一個重試迴圈，且每個副作用都必須能容忍重播。述詞鎖定追蹤也會耗費記憶體，且可能從資料列層級擴大到頁面層級，這會表現為從未重疊的交易之間的衝突。將一個熱門路徑設為可序列化，而其餘部分保持 READ COMMITTED，這樣做收效甚微，因為檢查僅適用於都在該層級運行的交易之間。

使用諮詢鎖（advisory lock）會不會更簡單？

在從父 ID 衍生的鍵上使用 pg_advisory_xact_lock，可以序列化該父項下的所有寫入者，即使沒有資料列可供鎖定，它也能運作。有兩個缺點：這個鎖是一種慣例而非約束，所以如果新的程式碼路徑忘記使用它，不會收到錯誤也不會等待，只會產生一個沒人看見的競爭條件；而且鍵空間是全域的，所以不相關的功能可能會發生衝突，除非你使用帶有命名空間的雙參數形式。

那 FOR NO KEY UPDATE 呢？

這是一個對非鍵欄位進行普通 UPDATE 時所採用的較弱模式，它的存在是為了讓外鍵檢查不用等待那些不會破壞鍵的更新。這就是重點：FOR NO KEY UPDATE 不會與 FOR KEY SHARE 衝突，所以它不會讓子項的插入等待。要阻止插入，請用 FOR UPDATE 鎖定父項。對於防護機制來說，FOR SHARE 就足夠了，因為它與兩種寫入模式都會衝突。

資料庫應該用觸發器或約束來強制執行嗎？

在 child 上的一個 BEFORE INSERT 觸發器，如果它讀取父項的狀態，會有與應用程式防護機制相同的缺陷，原因也相同：它的讀取需要 FOR SHARE，而本應會阻擋的外鍵檢查卻在陳述式的稍後才會執行。一個宣告式的版本是可行的：在子項的外鍵中攜帶父項的狀態，建立一個對 (id, status) 的複合參照，並加上 ON UPDATE CASCADE，再加上一個 CHECK 約束來禁止子項使用已停用的值。沒有任何程式碼路徑可以繞過這個機制，代價是鍵變得更寬，以及一個反轉的失敗模式：現在會失敗的是停用父項的操作。

有一個邊界值得明確說明。這裡的每個修復方法都是在單一父資料列上序列化寫入者，這使得成本低廉，因為對不同父項的兩個操作永遠不會競爭。一個橫跨多個父項的不變量，例如對已確認子項的全域上限，沒有單一資料列可供鎖定，因此需要一個所有寫入者都去佔用的專用資料列、一個針對該規則的諮詢鎖，或是 SERIALIZABLE。大多數時候，父資料列是正確的單位，唯一的問題是你的陳述式和交易邊界是否善用了它。