postgresql - PostgreSQL 鎖機制

與 MySQL 鎖機制的根本差異

面向	PostgreSQL	MySQL (InnoDB)
鎖的對象	Tuple（行）和 Table	索引記錄和間隙
Gap Lock	沒有	有（Next-Key Lock）
寫衝突處理	RR 下直接報錯（first-updater-wins）	等鎖
讀寫互斥	不互斥（MVCC）	當前讀互斥
Advisory Lock	有（應用層分散式鎖）	有（`GET_LOCK`）但功能較弱

PostgreSQL 不需要 Gap Lock 是因為 MVCC（Snapshot Isolation）天生防幻讀——不需要靠鎖去阻止 INSERT。MySQL 的 RR 要靠 Next-Key Lock 防幻讀，所以鎖更多、死鎖更頻繁。

表級鎖（Table-Level Lock）

PostgreSQL 有 8 種表級鎖，從最弱到最強：

鎖模式	簡稱	觸發
AccessShareLock	讀	`SELECT`
RowShareLock	行共享	`SELECT FOR UPDATE/SHARE`
RowExclusiveLock	行排他	`INSERT` `UPDATE` `DELETE`
ShareUpdateExclusiveLock	共享更新排他	`VACUUM` `CREATE INDEX CONCURRENTLY`
ShareLock	共享	`CREATE INDEX`（非 CONCURRENTLY）
ShareRowExclusiveLock	共享行排他	明確 `LOCK TABLE ... IN SHARE ROW EXCLUSIVE MODE`
ExclusiveLock	排他	`REFRESH MATERIALIZED VIEW CONCURRENTLY`
AccessExclusiveLock	完全排他	`ALTER TABLE` `DROP TABLE` `VACUUM FULL` `LOCK TABLE`

大部分鎖之間是相容的。核心衝突：AccessExclusiveLock 與所有鎖互斥——這就是為什麼 ALTER TABLE 會阻塞所有查詢，也是為什麼 DDL migration 在 PostgreSQL 是高風險操作。

DDL 的陷阱

-- 這條語句需要 AccessExclusiveLock
ALTER TABLE orders ADD COLUMN notes TEXT;
-- 如果此時有長交易持有 AccessShareLock（普通 SELECT），ALTER TABLE 會等待
-- 更糟的是，等待 ALTER TABLE 的請求會阻塞後續所有新的 SELECT
-- → 整張表完全不可用

解法：

SET lock_timeout = '3s' → 等不到就失敗，不要無限等
用 pg_stat_activity 找出阻塞的長交易先解決
能用 CREATE INDEX CONCURRENTLY 就不用 CREATE INDEX

行級鎖（Row-Level Lock）

PostgreSQL 的行鎖直接標記在 tuple 的 header 上（t_infomask 的 lock flag），不像 MySQL 需要在鎖表（lock table）中維護。

四種行鎖模式：

模式	觸發	與其他行鎖的相容性
FOR KEY SHARE	外鍵檢查	只與 FOR UPDATE 衝突
FOR SHARE	`SELECT ... FOR SHARE`	與 FOR UPDATE / FOR NO KEY UPDATE 衝突
FOR NO KEY UPDATE	`UPDATE`（不改 key 欄位）	與 FOR SHARE 以上衝突
FOR UPDATE	`SELECT ... FOR UPDATE` / `UPDATE`（改 key 欄位）/ `DELETE`	與所有行鎖衝突

FOR NO KEY UPDATE

這是 PostgreSQL 獨有的精細化設計。普通 UPDATE（不修改主鍵或 UNIQUE 欄位）只拿 FOR NO KEY UPDATE，不與 FOR KEY SHARE 衝突。好處是：外鍵檢查（加 FOR KEY SHARE）不會被普通 UPDATE 阻塞。

-- Session A
UPDATE orders SET amount = 100 WHERE id = 1;  -- FOR NO KEY UPDATE（不改 PK）

-- Session B
-- 外鍵檢查：SELECT ... FROM orders WHERE id = 1 FOR KEY SHARE
-- ✓ 不衝突！在 MySQL 中會衝突

沒有 Gap Lock

PostgreSQL 的行鎖只鎖存在的 tuple，不鎖間隙。因為 Snapshot Isolation 天生防幻讀（你的 snapshot 建立後，別人的 INSERT 你看不到），不需要靠鎖阻止 INSERT。

好處：沒有 Gap Lock 交叉死鎖（MySQL 常見），鎖衝突更少。代價：如果用 RC 隔離等級，沒有幻讀防護（跟 MySQL RC 一樣）。

Advisory Lock

應用層可以自行定義的鎖，PostgreSQL 負責管理但不綁定任何資料。適合實作分散式鎖、任務去重、串行化處理。

-- 取得 lock（阻塞直到拿到）
SELECT pg_advisory_lock(12345);

-- 嘗試取得（不阻塞，回傳 true/false）
SELECT pg_try_advisory_lock(12345);

-- 釋放
SELECT pg_advisory_unlock(12345);

-- Transaction-level：COMMIT/ROLLBACK 時自動釋放
SELECT pg_advisory_xact_lock(12345);

實務範例：防止重複處理

-- 只有一個 worker 能處理某個 order
BEGIN;
SELECT pg_try_advisory_xact_lock(hashtext('order:' || order_id::text))
FROM orders WHERE id = 42;
-- 如果拿到 lock → 處理
-- 如果沒拿到 → 跳過（別人正在處理）
COMMIT;  -- 自動釋放

比 MySQL 的 GET_LOCK() 強：

功能	PostgreSQL Advisory Lock	MySQL GET_LOCK
同時持有多個	✓	✓（5.7+）
Transaction-level	✓ `pg_advisory_xact_lock`	✗（只有 session level）
64-bit key	✓（bigint 或兩個 int）	✗（字串）
效能	高（in-memory）	中等

死鎖

PostgreSQL 的死鎖偵測

和 MySQL 類似，PostgreSQL 用 wait-for graph 偵測死鎖。由 deadlock detector 定期檢查（deadlock_timeout 預設 1 秒）。

偵測到死鎖後，選擇一個交易 rollback（通常是最近開始的），其他繼續。

-- 死鎖偵測的等待時間（超過才開始偵測）
SET deadlock_timeout = '1s';

為什麼 PostgreSQL 死鎖比 MySQL 少

沒有 Gap Lock：少了 gap lock 交叉死鎖這個常見場景
RR 下直接報錯不等待：first-updater-wins 策略讓寫衝突快速失敗，不會形成等待鏈
MVCC 讀不加鎖：讀寫不互斥，減少鎖衝突的組合

主要的死鎖場景是 RC 下兩個交易以相反順序 UPDATE 同一批行（和 MySQL 一樣）。

鎖的監控

-- 查看所有鎖
SELECT locktype, relation::regclass, mode, granted, pid
FROM pg_locks
WHERE NOT granted  -- 只看等待中的鎖
ORDER BY pid;

-- 查看阻塞關係
SELECT
    blocked.pid AS blocked_pid,
    blocked_activity.query AS blocked_query,
    blocking.pid AS blocking_pid,
    blocking_activity.query AS blocking_query
FROM pg_locks blocked
JOIN pg_locks blocking
    ON blocking.locktype = blocked.locktype
    AND blocking.database IS NOT DISTINCT FROM blocked.database
    AND blocking.relation IS NOT DISTINCT FROM blocked.relation
    AND blocking.page IS NOT DISTINCT FROM blocked.page
    AND blocking.tuple IS NOT DISTINCT FROM blocked.tuple
    AND blocking.transactionid IS NOT DISTINCT FROM blocked.transactionid
    AND blocking.pid != blocked.pid
    AND blocking.granted
JOIN pg_stat_activity blocked_activity ON blocked_activity.pid = blocked.pid
JOIN pg_stat_activity blocking_activity ON blocking_activity.pid = blocking.pid
WHERE NOT blocked.granted;

-- PG 14+ 簡化版
SELECT * FROM pg_blocking_pids(target_pid);

-- 殺掉阻塞的 query
SELECT pg_cancel_backend(pid);     -- 取消 query（溫和）
SELECT pg_terminate_backend(pid);  -- 終止連線（強硬）

鎖的最佳實踐

DDL 操作加 lock_timeout：避免 ALTER TABLE 無限等待阻塞所有查詢
用 CONCURRENTLY：CREATE INDEX CONCURRENTLY、REINDEX CONCURRENTLY 不阻塞 DML
短交易：長交易持鎖久，增加衝突概率
固定更新順序：和 MySQL 一樣，多行更新按固定順序減少死鎖
用 Advisory Lock 替代 SELECT FOR UPDATE：需要應用層鎖時，advisory lock 更輕量
監控 pg_locks：定期檢查等待中的鎖和阻塞鏈