並行控制與鎖機制：PostgreSQL 如何協調多用戶同時存取 | PostgreSQL
2026/06/16
PostgreSQL 的並行控制建立在 MVCC 基礎之上，輔以多層次的鎖機制（Locking），確保在高並行場景下資料的一致性與正確性。理解鎖的分層設計、衝突矩陣、HOT Update 優化，以及 SKIP LOCKED 工作佇列模式，是調校高並行系統的關鍵。
鎖的分層設計
PostgreSQL 的鎖分為三個層次，各自服務不同的並行控制需求：
鎖的分層架構：
┌──────────────────────────────────────────────────┐
│ Table-Level Locks │
│ 保護整個 table 的結構或大範圍操作 │
│ 8 種模式：ACCESS SHARE → ACCESS EXCLUSIVE │
├──────────────────────────────────────────────────┤
│ Row-Level Locks │
│ 保護單行資料的修改（存在 tuple header 中） │
│ 4 種模式：FOR KEY SHARE → FOR UPDATE │
├──────────────────────────────────────────────────┤
│ Page-Level Locks │
│ 保護 shared buffer 中 page 的並行讀寫 │
│ 由 LWLock 實現，極短暫，內部使用 │
└──────────────────────────────────────────────────┘
Table-Level Lock 衝突矩陣
PostgreSQL 定義了 8 種 Table-Level Lock 模式，按強度由弱到強排列。✗ 表示衝突（需等待），○ 表示相容（可並行）：
衝突矩陣（行=持有，列=請求）：
AS RS RE SUE SH SRE EX AE
ACCESS SHARE │ ○ ○ ○ ○ ○ ○ ○ ✗
ROW SHARE │ ○ ○ ○ ○ ○ ○ ✗ ✗
ROW EXCLUSIVE │ ○ ○ ○ ○ ✗ ✗ ✗ ✗
SHARE UPD EX │ ○ ○ ○ ✗ ✗ ✗ ✗ ✗
SHARE │ ○ ○ ✗ ✗ ○ ✗ ✗ ✗
SHR ROW EX │ ○ ○ ✗ ✗ ✗ ✗ ✗ ✗
EXCLUSIVE │ ○ ✗ ✗ ✗ ✗ ✗ ✗ ✗
ACCESS EX │ ✗ ✗ ✗ ✗ ✗ ✗ ✗ ✗
各 Lock 模式的典型場景：
| Lock 模式 | 由誰獲取 | 說明 |
|---|---|---|
| ACCESS SHARE | SELECT | 最弱，只與 ACCESS EXCLUSIVE 衝突 |
| ROW SHARE | SELECT FOR UPDATE/SHARE | 允許並行 SELECT，與 EX/AE 衝突 |
| ROW EXCLUSIVE | INSERT/UPDATE/DELETE | 允許並行讀，阻止 DDL |
| SHARE UPDATE EXCLUSIVE | VACUUM/ANALYZE/CIC | 防止並行 schema 變更 |
| SHARE | CREATE INDEX（非 CONCURRENTLY） | 允許並行讀，阻止寫 |
| SHARE ROW EXCLUSIVE | CREATE TRIGGER, 部分 ALTER TABLE | 較少使用 |
| EXCLUSIVE | 手動 LOCK TABLE … IN EXCLUSIVE MODE | 允許 ACCESS SHARE（普通 SELECT） |
| ACCESS EXCLUSIVE | DROP TABLE, TRUNCATE, REINDEX, 大部分 ALTER TABLE | 最強，阻止所有並行操作 |
Row-Level Lock 四種強度
SELECT 的 FOR 子句可獲取行級鎖，由強到弱：
FOR UPDATE ← 最強：阻止所有並行修改和鎖
FOR NO KEY UPDATE ← 次強：允許 FOR KEY SHARE
FOR SHARE ← 共享讀鎖：允許多個 FOR SHARE，阻止修改
FOR KEY SHARE ← 最弱：主要用於 FK 檢查
Row-Level Lock 衝突矩陣：
FK SHARE FOR SHARE NO KEY UPD FOR UPDATE
FOR KEY SHARE │ ○ ○ ○ ✗
FOR SHARE │ ○ ○ ✗ ✗
NO KEY UPDATE │ ○ ✗ ✗ ✗
FOR UPDATE │ ✗ ✗ ✗ ✗
Foreign Key 的隱式鎖：當更新父表的主鍵或刪除父表行時，PostgreSQL 會對子表的相關行加 FOR KEY SHARE 鎖。插入或更新子表的 FK 欄位時，也會對父表的被引用行加 FOR KEY SHARE 鎖。
NOWAIT 與 SKIP LOCKED
-- 預設行為：等待直到鎖可用（可能死鎖）
SELECT * FROM jobs WHERE id = 42 FOR UPDATE;
-- NOWAIT：立即失敗，拋出錯誤
SELECT * FROM jobs WHERE id = 42 FOR UPDATE NOWAIT;
-- ERROR: could not obtain lock on row in relation "jobs"
-- SKIP LOCKED：跳過已鎖定的行，返回可獲取鎖的行子集
SELECT * FROM jobs WHERE status = 'pending'
ORDER BY created_at
LIMIT 1
FOR UPDATE SKIP LOCKED;
SKIP LOCKED 工作佇列模式
SKIP LOCKED 是實現分散式工作佇列的最佳方式，多個 Worker 並行消費任務而不互相阻塞：
-- 建立工作佇列表
CREATE TABLE job_queue (
id BIGSERIAL PRIMARY KEY,
payload JSONB NOT NULL,
status TEXT NOT NULL DEFAULT 'pending',
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
started_at TIMESTAMPTZ,
completed_at TIMESTAMPTZ,
worker_id TEXT
);
CREATE INDEX idx_job_queue_pending
ON job_queue (created_at)
WHERE status = 'pending';
-- Worker 消費任務（關鍵查詢）
BEGIN;
WITH next_job AS (
SELECT id
FROM job_queue
WHERE status = 'pending'
ORDER BY created_at
LIMIT 1
FOR UPDATE SKIP LOCKED -- 跳過其他 worker 已鎖定的任務
)
UPDATE job_queue
SET status = 'processing',
started_at = now(),
worker_id = pg_backend_pid()::text
WHERE id = (SELECT id FROM next_job)
RETURNING *;
-- 執行任務後標記完成
-- UPDATE job_queue SET status = 'completed', completed_at = now() WHERE id = $1;
COMMIT;
HOT Update（Heap-Only Tuple）
HOT Update 是 PostgreSQL 的重要優化。當以下條件同時成立時，UPDATE 不需要更新索引：
- 更新的欄位不包含在任何索引中
- 新版本 Tuple 與舊版本在同一個 Heap Page 內
一般 UPDATE（非 HOT）：
heap page A index
┌─────────────┐ ┌─────────────┐
│ tuple v1 │←───────────│ index entry │
│ (xmax=TX2) │ │ (ctid→v1) │
└─────────────┘ └─────────────┘
heap page B
┌─────────────┐ ┌─────────────┐
│ tuple v2 │←───────────│ index entry │（新增！）
│ (xmin=TX2) │ │ (ctid→v2) │
└─────────────┘ └─────────────┘
→ 索引膨脹，需 VACUUM 清理
HOT Update（同 page，非索引欄位）：
heap page A
┌──────────────────────────────┐
│ tuple v1 → tuple v2 │← HOT chain
│ (xmax=TX2) (xmin=TX2) │
│ (HEAP_ONLY=1)│
└──────────────────────────────┘
index
┌─────────────┐
│ index entry │← 仍指向 v1，透過 HOT chain 找到 v2
│ (ctid→v1) │← 索引不需更新！
└─────────────┘
→ 無索引膨脹，page 內自動清理（mini-vacuum）
-- 監控 HOT Update 比例（越高越好）
SELECT
schemaname,
tablename,
n_tup_upd AS total_updates,
n_tup_hot_upd AS hot_updates,
CASE WHEN n_tup_upd > 0
THEN round(100.0 * n_tup_hot_upd / n_tup_upd, 2)
ELSE 0
END AS hot_update_ratio_pct
FROM pg_stat_user_tables
WHERE n_tup_upd > 0
ORDER BY total_updates DESC
LIMIT 20;
-- 若 HOT Update 比例低，調整 fillfactor
ALTER TABLE accounts SET (fillfactor = 70);
-- 預留 30% 空間給 HOT Update
-- 需 VACUUM FULL 或 CLUSTER 才能對既有 page 立即生效
查看鎖狀態
-- 查看所有等待中的鎖
SELECT
pid,
pg_blocking_pids(pid) AS blocked_by,
query,
state,
locktype,
relation::regclass AS table_name,
mode,
granted
FROM pg_locks
LEFT JOIN pg_stat_activity USING (pid)
WHERE NOT granted;
-- 更完整的鎖等待鏈查詢
WITH lock_chain AS (
SELECT
blocked.pid AS blocked_pid,
blocked_activity.query AS blocked_query,
blocking.pid AS blocking_pid,
blocking_activity.query AS blocking_query,
blocked.mode AS waiting_for_mode,
blocked.relation::regclass AS table_name
FROM pg_locks AS blocked
JOIN pg_stat_activity AS blocked_activity
ON blocked.pid = blocked_activity.pid
JOIN pg_locks AS blocking
ON blocking.relation = blocked.relation
AND blocking.locktype = blocked.locktype
AND blocking.pid != blocked.pid
AND blocking.granted = true
AND NOT blocked.granted
JOIN pg_stat_activity AS blocking_activity
ON blocking.pid = blocking_activity.pid
)
SELECT
blocked_pid,
left(blocked_query, 80) AS blocked_query,
blocking_pid,
left(blocking_query, 80) AS blocking_query,
waiting_for_mode,
table_name
FROM lock_chain;
-- PG9.6+：快速查詢被阻塞的 session
SELECT pid, pg_blocking_pids(pid) AS blocked_by
FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0;
Deadlock 預防策略
-- 預防 Deadlock：固定順序存取 rows
-- 錯誤做法（可能 Deadlock）：
-- TX1: UPDATE accounts WHERE id = 1; UPDATE accounts WHERE id = 2;
-- TX2: UPDATE accounts WHERE id = 2; UPDATE accounts WHERE id = 1;
-- 正確做法（固定 ORDER BY 確保相同順序）：
BEGIN;
SELECT id FROM accounts
WHERE id IN (1, 2)
ORDER BY id -- 固定順序！
FOR UPDATE;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;
-- 設定鎖超時保護
SET lock_timeout = '10s';
-- 超過 10 秒無法取得鎖則報錯
-- 啟用 Deadlock 日誌
-- postgresql.conf:
-- log_lock_waits = on
-- deadlock_timeout = 1s
CREATE INDEX CONCURRENTLY
避免建索引時長時間鎖表：
-- 一般建索引：獲取 SHARE 鎖，阻止寫入
CREATE INDEX idx_accounts_email ON accounts (email);
-- CONCURRENTLY：不鎖表，但耗時更長
CREATE INDEX CONCURRENTLY idx_accounts_email ON accounts (email);
-- CONCURRENTLY 的代價：
-- 1. 需要掃描 table 兩次
-- 2. 需要等待所有使用該 table 的 transaction 結束
-- 3. 若過程中失敗，留下 INVALID 索引
-- 檢查 INVALID 索引
SELECT indexrelid::regclass AS index_name,
indrelid::regclass AS table_name,
indisvalid
FROM pg_index
WHERE NOT indisvalid;
-- 清理 INVALID 索引後重建
DROP INDEX CONCURRENTLY idx_accounts_email;
CREATE INDEX CONCURRENTLY idx_accounts_email ON accounts (email);
ALTER TABLE 的鎖影響
-- PG11 之前：ALTER TABLE ADD COLUMN DEFAULT value 需要重寫整個 table！
-- PG11+：使用 attmissingval 存儲預設值，只需短暫的 ACCESS EXCLUSIVE 鎖
-- 高風險操作（長時間 ACCESS EXCLUSIVE）：
ALTER TABLE big_table ADD CONSTRAINT check_positive CHECK (amount > 0);
-- 安全做法：先加 NOT VALID 約束，再 VALIDATE（可並行）
ALTER TABLE big_table
ADD CONSTRAINT check_positive CHECK (amount > 0) NOT VALID;
ALTER TABLE big_table VALIDATE CONSTRAINT check_positive;
-- VALIDATE 使用 SHARE UPDATE EXCLUSIVE，允許並行讀寫
Foreign Key 效能注意事項
-- FK 的隱式鎖在高並行下可能成為瓶頸
-- 大量並行 INSERT 子表時，都需要對父表加 FOR KEY SHARE
-- 關鍵：子表上的 FK 欄位必須建索引（PostgreSQL 不自動建立！）
CREATE INDEX idx_order_items_order_id ON order_items (order_id);
-- 否則 DELETE 父表時，需要全表掃描子表檢查 FK
-- 查找缺少索引的外鍵
SELECT
tc.table_schema,
tc.table_name,
kcu.column_name,
ccu.table_name AS foreign_table_name
FROM information_schema.table_constraints AS tc
JOIN information_schema.key_column_usage AS kcu
ON tc.constraint_name = kcu.constraint_name
JOIN information_schema.constraint_column_usage AS ccu
ON ccu.constraint_name = tc.constraint_name
WHERE tc.constraint_type = 'FOREIGN KEY'
AND NOT EXISTS (
SELECT 1 FROM pg_index pi
JOIN pg_attribute pa ON pa.attrelid = pi.indrelid
AND pa.attnum = ANY(pi.indkey)
WHERE pi.indrelid = (tc.table_schema||'.'||tc.table_name)::regclass
AND pa.attname = kcu.column_name
);
LWLock 與 Spinlock
PostgreSQL 內部使用兩種輕量級鎖保護共享記憶體結構：
| 類型 | 特性 | 用途 |
|---|---|---|
| Spinlock | 極短暫 busy-waiting，不能睡眠 | 保護非常短暫的臨界區 |
| LWLock | 可持有較長時間，可睡眠等待 | 保護 Buffer Pool、CLOG、WAL 等 |
-- 查看 LWLock 等待（效能瓶頸診斷）
SELECT wait_event_type, wait_event, count(*)
FROM pg_stat_activity
WHERE wait_event_type IN ('LWLock', 'Lock', 'BufferPin')
GROUP BY wait_event_type, wait_event
ORDER BY count DESC;
Predicate Lock（SSI 用）
Serializable Snapshot Isolation（SSI）使用 Predicate Lock 追蹤 read dependencies：
- Predicate Lock 不阻塞操作，只記錄讀取範圍
- 用於偵測 rw-conflict graph 中的 dangerous structure
- COMMIT 時若偵測到 serialization failure，回滾並報錯
-- 查看 Predicate Locks
SELECT pid, locktype, relation::regclass AS table_name, mode, granted
FROM pg_locks
WHERE mode = 'SIReadLock';
常見陷阱
VACUUM FULL 的鎖危險
-- 普通 VACUUM：使用 SHARE UPDATE EXCLUSIVE，允許並行讀寫
VACUUM ANALYZE accounts; -- 安全
-- VACUUM FULL：使用 ACCESS EXCLUSIVE，阻止所有並行操作！
VACUUM FULL accounts; -- 危險：會鎖表
-- 替代方案：pg_repack（不鎖表的表重建）
FOR UPDATE vs FOR NO KEY UPDATE
優先使用 FOR NO KEY UPDATE 而非 FOR UPDATE——前者允許並行的 FOR KEY SHARE（FK 檢查），減少不必要的鎖衝突。只有在需要修改主鍵或唯一鍵時才需要 FOR UPDATE。
總結
並行控制與鎖機制 是 PostgreSQL 高並行環境下資料正確性的保障：
- Table-Level Lock 有 8 種模式，理解衝突矩陣有助於預測 DDL 對線上服務的影響
- Row-Level Lock 有 4 種強度，優先使用
FOR NO KEY UPDATE減少鎖衝突 - SKIP LOCKED 是實現高效工作佇列的最佳方式
- HOT Update 透過在同一 Page 內更新避免索引維護開銷，搭配
fillfactor效果更佳 - Deadlock 可透過固定存取順序預防，搭配
lock_timeout和log_lock_waits監控 - CREATE INDEX CONCURRENTLY 和 NOT VALID + VALIDATE 是不鎖表的 DDL 最佳實踐
下一篇，我們將深入探討 PostgreSQL 的後台進程——Autovacuum、Checkpointer、BGWriter 等背景工作者如何協同運作。