PostgreSQL 並發寫入

重點。 不要在程式碼中執行「讀取—檢查—寫入」：在 read committed 隔離級別下，這會遺失其他交易的修改。根據不變量的類型選擇適當的工具：

不變量	工具
若條件仍成立則修改資料列	單一 `UPDATE ... WHERE <條件>` + 檢查受影響資料列數
讀取後在程式碼中決定，再寫入同一資料列	在同一交易中執行 `SELECT ... FOR NO KEY UPDATE` 後寫入
無重複資料	唯一索引 + `INSERT ... ON CONFLICT`
區間不重疊	`EXCLUDE USING gist` (btree_gist)
無法用約束表達的跨資料列規則	所有參與者使用 serializable + 重試
任務佇列	`FOR UPDATE SKIP LOCKED`
單一處理器	`pg_advisory_xact_lock`

資料庫層級的約束比程式碼更可靠：無論隔離級別或客戶端為何，它都能維持不變量。Go 交易與重試程式碼請參閱 pgx-txmanager 技能。斷言檢查請參閱 CHECKS.md。

隔離級別

Read committed（預設）：每個指令都看到最新的快照。UPDATE 在遇到被鎖定的資料列時會等待其他交易，然後重新讀取該資料列並再次檢查 WHERE 條件。
Repeatable read 和 serializable 不會重新讀取：發生衝突時會出現錯誤 40001（「could not serialize access」），serializable 更常觸發此錯誤。提高隔離級別後，請立即撰寫整個交易的重試邏輯，否則使用者會看到錯誤。40001 也可能在 COMMIT 時發生。
Repeatable read 的快照是在 BEGIN 之後的第一個指令建立，而不是在 BEGIN 本身。關鍵時刻——第一個查詢請進行讀取。
Serializable 僅保證 serializable 交易之間的結果一致性。 若要用它來保護不變量，所有會碰觸該不變量的交易都必須使用 serializable。
在 serializable 內請查看 EXPLAIN。 Seq Scan 會對整個資料表取得述詞鎖定，並提高 40001 的發生率；透過索引讀取只會鎖定已讀取的資料列和索引頁面。請建立所需的索引。在小資料表上，Seq Scan 是正常的。

若條件成立則修改：UPDATE 中的條件

將操作（而非計算好的數值）傳遞給資料庫：SET sent = sent + 1，SET left = left - $1 WHERE left >= $1，WHERE id = $1 AND status = 'free'。零受影響資料列（RowsAffected() == 0 或 RETURNING 回傳 pgx.ErrNoRows）表示條件不成立或資料列不存在。這是領域錯誤，而非成功。

tag, err := q.Exec(ctx,
	`UPDATE seats SET status = 'booked', customer_id = $2
	 WHERE id = $1 AND status = 'free'`,
	seatID, customerID,
)
if err != nil {
	return fmt.Errorf("book seat %d: %w", seatID, err)
}
if tag.RowsAffected() == 0 { // 座位已被預訂或不存在
	return fmt.Errorf("book seat %d: %w", seatID, ErrConflict)
}

在 repeatable read 和 serializable 下，同樣的衝突會導致 40001，需要重試。

讀取後寫入同一資料列

SELECT ... FOR NO KEY UPDATE（模式請見「鎖定模式」章節）和寫入必須在同一交易中（trManager.Do）。在自動提交模式下，鎖定會在 SELECT 後立即釋放。鎖定無法保護尚未存在的資料列：防止插入重複資料的唯一方法是唯一索引或約束。

基於多個資料列的決策（write skew）

「處理人員有空」、「預訂落在區間內」、「班表中仍保留值班人員」：兩個交易讀取同一個快照，各自寫入自己的資料列，然後兩個都提交。在 read committed 下，這會產生兩個重疊的預訂。Repeatable read 也無法防止：它只會捕捉對同一資料列的寫入。

關於現有資料列的決策——請鎖定（FOR NO KEY UPDATE / FOR UPDATE）您決定時所依據的確切資料列。
關於資料列不存在的決策（「沒有重疊的預訂」）——沒有東西可以鎖定。需要使用約束：
- 基於值重複——唯一索引；
- 區間重疊——排除約束（exclusion constraint）。在 read committed 下，第二個交易會等待第一個交易完成，然後收到 23P01，無需 serializable 和重試：
```
CREATE EXTENSION IF NOT EXISTS btree_gist; -- trusted：需要資料庫的 CREATE 權限
ALTER TABLE bookings ADD CONSTRAINT bookings_no_overlap
    EXCLUDE USING gist (resource_id WITH =, during WITH &&); -- during tstzrange
```
  將 23P01（exclusion_violation）轉換為領域衝突錯誤。
無法用約束表達的規則——所有參與者使用 serializable，並在遇到 40001 時重試。

資料列鎖定模式

如果您要鎖定資料列以便讀取和更新，且不會變更適用於外部鍵的唯一索引欄位（主鍵、唯一鍵；部分索引和運算式索引不計），請使用 FOR NO KEY UPDATE。FOR UPDATE 保留給 DELETE 和變更這類欄位時使用。原因：在插入子資料列時，外部鍵檢查會對父資料列取得 FOR KEY SHARE。它與 FOR UPDATE 衝突，但與 FOR NO KEY UPDATE 不衝突。在父資料列（客戶、資源、訂單）上使用不必要的 FOR UPDATE 會阻止插入參照它的資料列。

任務佇列

多個工作者透過單一陳述式領取任務：

UPDATE tasks
SET status = 'running', taken_at = now()
WHERE id = (
	SELECT id FROM tasks
	WHERE status = 'ready'
	ORDER BY id
	LIMIT 1
	FOR UPDATE SKIP LOCKED
)
RETURNING id

SKIP LOCKED 會跳過已被鎖定的資料列，因此工作者不會互相等待。在同一交易中先執行 SELECT ... FOR UPDATE SKIP LOCKED 再執行 UPDATE 也是正確的。請勿使用不帶鎖定的 SELECT 和單獨的 UPDATE：這會導致兩個工作者領取同一個任務。
子查詢中的 ORDER BY 是為了可預測的順序。
工作者可能帶著任務崩潰。實務做法：根據 taken_at 設定租約到期時間——執行中的任務若超過時限，應再次視為準備就緒。
SKIP LOCKED 會提供不一致的資料檢視——僅適用於佇列。

避免卡在其他鎖定上

預設情況下，查詢會無限期等待鎖定，導致使用者看到應用程式凍結。

在交易內使用 SET LOCAL lock_timeout，而非工作階段參數：連線從連線池返回後可能會用於其他查詢。此逾時適用於每次鎖定等待，而非整個交易。
UPDATE 和 DELETE 沒有 NOWAIT。 如果需要立即收到錯誤，請在同一交易中先對這些資料列執行 SELECT ... FOR UPDATE NOWAIT。
NOWAIT 和 SKIP LOCKED 僅適用於資料列。 查詢仍會像往常一樣等待資料表鎖定，例如 ALTER TABLE。因此，即使使用它們，也需要 lock_timeout。
這些情況下的錯誤是 55P03（lock_not_available）。請重試整個交易。

return s.trManager.Do(ctx, func(ctx context.Context) error {
	q := s.getter.DefaultTrOrDB(ctx, s.pool)
	// 常數，無需輸入；根據操作調整數值
	if _, err := q.Exec(ctx, `SET LOCAL lock_timeout = '1s'`); err != nil {
		return err
	}
	return s.repo.MoveBooking(ctx, from, to)
})

等待佇列是不公平的。 新的 FOR SHARE / FOR KEY SHARE 可能會超越在熱門資料列上等待的 UPDATE，這類鎖定的流量可能會無限期地阻塞它；等待者之間的順序無法保證。對於熱門資料列（共用計數器、熱門商品庫存），請設定 lock_timeout 並重試交易。

分析卡住情況。 資料列鎖定在 pg_locks 中不可見：等待中的程序會顯示為等待持有者的 transactionid。pg_blocking_pids(pid) 會顯示誰在阻塞（請勿頻繁呼叫）。資料表的資料列鎖定可以透過 contrib 中的 pgrowlocks 擴充功能顯示（需要超級使用者安裝）。

諮詢鎖（Advisory Locks）

「僅一個處理器」（單一 outbox 工作者、遷移工具）——在交易內使用 pg_advisory_xact_lock：鎖定會隨著提交或回滾自動釋放。

const advisoryOutboxRelay int64 = 1 // 子系統的固定金鑰

err := s.trManager.Do(ctx, func(ctx context.Context) error {
	q := s.getter.DefaultTrOrDB(ctx, s.pool)
	if _, err := q.Exec(ctx, `SELECT pg_advisory_xact_lock($1)`, advisoryOutboxRelay); err != nil {
		return err
	}
	return s.relayBatch(ctx) // 一批任務；鎖定會隨著交易一起釋放
})

工作階段級 pg_advisory_lock 僅用於專用連線（來自 pool.Acquire）；必須在相同連線上釋放與取得相同次數。工作階段級鎖定不會因 COMMIT 或回滾而釋放，且同一個工作階段可以重複取得。在連線池中，連線會帶著鎖定返回，而 unlock 會發送到其他連線，導致鎖定「洩漏」。
不要為了無限工作者的鎖定而保持交易開啟：對每批工作使用 xact 鎖。對於長時間運行的領導者，請在專用連線上使用工作階段級鎖定。
金鑰是每個子系統的固定數值常數，或是一對 (int, int)。請勿使用 hashtext：該函數未文件化，且 32 位元雜湊會產生碰撞。

查詢中的函數

對於在查詢中被呼叫且會讀取資料表的 plpgsql 或 SQL 函數，請標記為 STABLE。VOLATILE 會在函數內部為每個查詢建立新的快照，因此在 read committed 下，資料表和函數的資料可能來自不同時間點。IMMUTABLE 僅適用於不讀取資料表的函數；VOLATILE 適用於有副作用的函數。

錯誤代碼

代碼	含義	處理方式
`40001` serialization_failure	repeatable read / serializable 下的衝突	重試整個交易
`40P01` deadlock_detected	死結	重試整個交易；以一致的順序取得鎖定
`55P03` lock_not_available	`NOWAIT`、`lock_timeout`	重試整個交易或回傳「忙碌」
`23505` unique_violation	重複資料	領域衝突；預期的重複請使用 `ON CONFLICT`
`23P01` exclusion_violation	違反排除約束	領域衝突

scary-production/claude-plugins

claude-plugins 是什麼？

在你喜歡的 AI 中提問

說明文件