鎖機制與死結處理

並行是資料庫效能的關鍵，但也帶來競爭問題。本篇將介紹 PostgreSQL 的 MVCC 和鎖機制，以及如何處理死結。

---

一、 MVCC 多版本並行控制

PostgreSQL 使用 MVCC（Multi-Version Concurrency Control）來處理並行。

原理

每次 UPDATE 不會覆蓋原資料，而是建立新版本：

Row Version 1: { id: 1, name: 'Alice', xmin: 100, xmax: 150 }
Row Version 2: { id: 1, name: 'Alicia', xmin: 150, xmax: ∞ }

• xmin：建立此版本的交易 ID
• xmax：刪除此版本的交易 ID（∞ 表示尚未刪除）

快照

每個交易根據自己的「快照」決定看到哪個版本：

-- 交易 200 開始時
-- 快照：可見的交易 ID < 150

-- 交易 200 看到 Version 1（因為 Version 2 的 xmin=150 不可見）

MVCC 的好處

• 讀不阻塞寫：SELECT 不需要鎖
• 寫不阻塞讀：UPDATE 期間其他人仍可讀舊版本
• 一致性快照：整個交易看到一致的資料

---

二、 鎖的類型

表級鎖

鎖模式	用途	衝突
ACCESS SHARE	SELECT	只和 ACCESS EXCLUSIVE 衝突
ROW SHARE	SELECT FOR UPDATE
ROW EXCLUSIVE	INSERT/UPDATE/DELETE
SHARE UPDATE EXCLUSIVE	VACUUM
SHARE	CREATE INDEX
SHARE ROW EXCLUSIVE	觸發器
EXCLUSIVE	阻塞讀寫
ACCESS EXCLUSIVE	DROP TABLE, ALTER TABLE	和所有鎖衝突

列級鎖

-- SELECT FOR UPDATE：獨佔鎖
SELECT * FROM accounts WHERE user_id = 'A' FOR UPDATE;
-- 其他交易不能 UPDATE 或 DELETE 這列

-- SELECT FOR SHARE：共享鎖
SELECT * FROM accounts WHERE user_id = 'A' FOR SHARE;
-- 其他交易不能 UPDATE 或 DELETE，但可以 FOR SHARE

-- SELECT FOR NO KEY UPDATE：不鎖外鍵
SELECT * FROM accounts WHERE user_id = 'A' FOR NO KEY UPDATE;

-- SKIP LOCKED：跳過已鎖定的列
SELECT * FROM tasks WHERE status = 'pending' FOR UPDATE SKIP LOCKED LIMIT 1;

---

三、 SELECT FOR UPDATE 實戰

悲觀鎖模式

確保更新時資料不被其他人修改：

-- 扣款流程
BEGIN;

-- 先鎖定帳戶
SELECT balance FROM accounts WHERE user_id = 'A' FOR UPDATE;

-- 確認餘額足夠
IF balance >= 1000 THEN
    UPDATE accounts SET balance = balance - 1000 WHERE user_id = 'A';
END IF;

COMMIT;

SKIP LOCKED：工作佇列

多個 Worker 處理任務，避免爭搶：

-- Worker 取得一個未處理的任務
BEGIN;

SELECT id, data FROM tasks
WHERE status = 'pending'
FOR UPDATE SKIP LOCKED
LIMIT 1;

-- 處理任務...

UPDATE tasks SET status = 'done' WHERE id = ?;

COMMIT;

NOWAIT：立即失敗

BEGIN;

SELECT * FROM inventory WHERE product_id = 1 FOR UPDATE NOWAIT;
-- 如果已被鎖定，立即報錯而非等待

COMMIT;

---

四、 死結 (Deadlock)

兩個交易互相等待對方釋放鎖。

死結範例

-- Session 1
BEGIN;
UPDATE accounts SET balance = 0 WHERE user_id = 'A';  -- 鎖定 A
-- 等待...

-- Session 2
BEGIN;
UPDATE accounts SET balance = 0 WHERE user_id = 'B';  -- 鎖定 B
UPDATE accounts SET balance = 0 WHERE user_id = 'A';  -- 等待 A（Session 1 持有）

-- Session 1（繼續）
UPDATE accounts SET balance = 0 WHERE user_id = 'B';  -- 等待 B（Session 2 持有）
-- 死結！

死結偵測

PostgreSQL 自動偵測死結並終止其中一個交易：

ERROR: deadlock detected
DETAIL: Process 12345 waits for ShareLock on transaction 67890; blocked by process 67891.
Process 67891 waits for ShareLock on transaction 12345; blocked by process 12345.
HINT: See server log for query details.

避免死結

1. 固定鎖定順序

-- 不好：順序不固定
-- Session 1: UPDATE A, UPDATE B
-- Session 2: UPDATE B, UPDATE A

-- 好：永遠按 user_id 順序
-- Session 1: UPDATE A, UPDATE B
-- Session 2: UPDATE A, UPDATE B

2. 減少鎖定時間

-- 不好：長時間持有鎖
BEGIN;
SELECT * FROM accounts WHERE user_id = 'A' FOR UPDATE;
-- 做很多計算...
COMMIT;

-- 好：先計算，後鎖定
-- 計算...
BEGIN;
SELECT * FROM accounts WHERE user_id = 'A' FOR UPDATE;
UPDATE ...;
COMMIT;

3. 使用 NOWAIT 或 SKIP LOCKED

SELECT * FROM inventory
WHERE product_id = 1
FOR UPDATE NOWAIT;  -- 無法取得就立即失敗

---

五、 Advisory Locks（諮詢鎖）

應用程式層級的鎖，不綁定特定資料。

用途

• 防止同一個任務重複執行
• 分散式鎖
• 限制並行數量

Session 級別

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

-- 嘗試取得鎖（立即返回 true/false）
SELECT pg_try_advisory_lock(12345);

-- 釋放鎖
SELECT pg_advisory_unlock(12345);

-- 釋放所有 Session 鎖
SELECT pg_advisory_unlock_all();

Transaction 級別

-- 取得交易級別的鎖（COMMIT/ROLLBACK 時自動釋放）
SELECT pg_advisory_xact_lock(12345);

BEGIN;
SELECT pg_advisory_xact_lock(12345);
-- 操作...
COMMIT;  -- 自動釋放

實戰：防止重複執行

async function processUniqueTask(taskId) {
  const lockId = taskId; // 使用 taskId 作為鎖 ID

  // 嘗試取得鎖
  const { rows } = await db.query("SELECT pg_try_advisory_lock($1)", [lockId]);

  if (!rows[0].pg_try_advisory_lock) {
    console.log("Task already running");
    return;
  }

  try {
    // 執行任務...
  } finally {
    await db.query("SELECT pg_advisory_unlock($1)", [lockId]);
  }
}

---

六、 監控鎖

查看當前鎖

SELECT
    l.pid,
    l.locktype,
    l.mode,
    l.granted,
    a.usename,
    a.query
FROM pg_locks l
JOIN pg_stat_activity a ON l.pid = a.pid
WHERE l.locktype = 'relation';

查看鎖等待

SELECT
    blocked.pid AS blocked_pid,
    blocked.query AS blocked_query,
    blocking.pid AS blocking_pid,
    blocking.query AS blocking_query
FROM pg_stat_activity blocked
JOIN pg_locks blocked_locks ON blocked.pid = blocked_locks.pid AND NOT blocked_locks.granted
JOIN pg_locks blocking_locks ON
    blocked_locks.locktype = blocking_locks.locktype AND
    blocked_locks.relation = blocking_locks.relation AND
    blocking_locks.granted
JOIN pg_stat_activity blocking ON blocking_locks.pid = blocking.pid
WHERE blocked.pid != blocking.pid;

強制終止阻塞的連線

-- 溫和終止（等待當前查詢完成）
SELECT pg_cancel_backend(12345);

-- 強制終止
SELECT pg_terminate_backend(12345);

---

七、 樂觀鎖 vs 悲觀鎖

悲觀鎖（Pessimistic）

假設衝突會發生，先鎖定再操作。

BEGIN;
SELECT * FROM inventory WHERE product_id = 1 FOR UPDATE;
-- 其他人無法修改
UPDATE inventory SET stock = stock - 1 WHERE product_id = 1;
COMMIT;

樂觀鎖（Optimistic）

假設衝突不常發生，更新時檢查版本。

-- 資料表有 version 欄位
SELECT id, stock, version FROM inventory WHERE product_id = 1;
-- stock=10, version=5

-- 更新時檢查版本
UPDATE inventory
SET stock = stock - 1, version = version + 1
WHERE product_id = 1 AND version = 5;

-- 如果更新了 0 列，表示被其他人修改過，需要重試

選擇建議

情況	推薦
衝突頻繁	悲觀鎖
衝突罕見	樂觀鎖
長時間操作	樂觀鎖
需要嚴格一致性	悲觀鎖

---

八、 鎖相關參數

# postgresql.conf

# 死結偵測間隔（預設 1 秒）
deadlock_timeout = 1s

# 鎖等待超時（預設無限）
lock_timeout = 10s

# 語句超時
statement_timeout = 30s

---

總結

概念	說明
MVCC	多版本並行，讀不阻塞寫
FOR UPDATE	悲觀鎖，防止並行修改
SKIP LOCKED	跳過已鎖定的列
NOWAIT	無法取得鎖時立即失敗
死結	互相等待，PostgreSQL 自動偵測並終止一方
Advisory Lock	應用程式級別的自定義鎖
樂觀鎖	版本號檢查，適合低衝突場景

正確使用鎖機制，才能在保證資料一致性的同時維持高並行效能。

---

進階挑戰

1. 模擬一個死結場景，觀察 PostgreSQL 的自動偵測和處理機制
2. 使用 Advisory Lock 實作一個分散式任務鎖，確保同一任務不會被多個 worker 同時執行
3. 查詢 pg_locks 系統表，分析當前資料庫的鎖等待情況

---

延伸閱讀與資源

• PostgreSQL 鎖機制官方文件
• 死結處理
• Advisory Locks

---

← 上一章：交易與 ACID 原則 | 返回專題首頁 | 下一章：預存程序與函數 →