鎖定與衝突監控

想像一下你在辦公室，結果因為某個人把鑰匙忘在房間裡，所有門都被鎖住了。PostgreSQL 的鎖定就有點像這樣。如果一個 query 或 transaction 把資源鎖住，其他想要存取同一個資源的操作就得等它結束。這種情況會造成延遲、衝突場景，嚴重的話甚至讓系統卡住。

什麼時候會有鎖定？

在 PostgreSQL 裡，鎖定（Locks）是用來管理資料的並發存取。會發生在：

1. 執行寫入操作時：UPDATE、DELETE、INSERT。
2. 用 transaction 把資源鎖太久，超過必要時間。
3. 不同 transaction 同時搶同一個資源時發生衝突。

真實的資料庫就是一個「資源戰場」，就算你覺得系統很穩，一個不小心的 transaction 也可能讓一切「卡住」，就像 Git merge 失敗一樣。

鎖定分析工具：pg_locks

pg_locks 是 PostgreSQL 的系統視圖，會顯示目前有哪些 transaction 持有或等待鎖定。它可以回答：「誰鎖住了資源，誰在等？」

pg_locks 主要欄位：

• locktype：鎖定類型（例如 relation、transaction、page、tuple）。
• database：資料庫 ID。
• relation：資料表 ID（如果鎖定跟資料表有關）。
• mode：鎖定模式（例如 RowExclusiveLock、AccessShareLock）。
• granted：flag，表示鎖定是否已經給了（true）還是 transaction 還在等（false）。

注意： PostgreSQL 採用「階層式鎖定模式」。意思是不同操作會加上不同嚴格程度的鎖（像 AccessShareLock 只讀，ExclusiveLock 則是改資料表結構）。

範例：看所有目前的鎖定

SELECT *
FROM pg_locks;

但如果直接把 pg_locks 全部列出來，會超級亂。我們來點有意義的吧！

範例：還沒拿到的鎖（也就是 transaction 還在等）

SELECT pid, locktype, relation::regclass AS table_name, mode, granted
FROM pg_locks
WHERE NOT granted;

這裡發生什麼事？

• 我們只挑 granted = false 的紀錄，也就是還沒拿到鎖的。
• relation::regclass 會把資料表 ID 轉成名字，比較好讀。

查詢結果可能長這樣：

這些查詢可以幫你找出是哪個資料表/資源被鎖，還有是哪個 transaction 造成的。

衝突分析：pg_blocking_pids()

鎖定還算小事，如果一個 transaction 卡住另一個怎麼辦？PostgreSQL 有個超方便的 function pg_blocking_pids()，可以直接找出「兇手」。

pg_blocking_pids() 會回傳一個 process ID（pid）清單，這些 process 就是正在卡住你這個 transaction 的傢伙。

範例：找出哪些 transaction 卡住別人

SELECT pid, pg_blocking_pids(pid) AS blocking_pids
FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0;

這裡發生什麼事？

• 我們用 pg_stat_activity 這個視圖，抓出系統裡的 active process。
• pg_blocking_pids(pid) 會回傳每個 pid 被哪些 process 卡住。如果不是空的（長度大於 0），就代表這個 process 被鎖住了。

查詢結果範例：

pid = 4567 這個 transaction 被 1234 跟 5678 這兩個 process 卡住。我們找到「兇手」啦！

終止鎖定 process

找到卡住別人的 process 之後，你可以用 pg_terminate_backend() 把它砍掉：

SELECT pg_terminate_backend(1234); -- 「砍掉」process 1234

但要小心！強制終止 process 會讓目前 transaction 的資料全部 rollback。這招就像「核彈鍵」，真的沒辦法再用。

實戰應用：鎖定分析情境

假設我們有一個大學資料庫，裡面有 students 跟 enrollments 兩張表。有好幾個 transaction 同時要寫 enrollments，結果就卡住了。

1. 找出鎖定：

SELECT pid, locktype, relation::regclass AS table_name, mode, granted
FROM pg_locks
WHERE NOT granted;

2. 找出卡住別人的 process：

SELECT pid, pg_blocking_pids(pid) AS blocking_pids
FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0;

3. 解決鎖定：

強制終止其中一個衝突的 process：

SELECT pg_terminate_backend(1234); -- 終止 process 1234

注意： 在「砍」process 之前，先搞清楚為什麼會卡住。也許你該重新設計 transaction 的邏輯。

常見錯誤與預防方法

鎖定通常是因為 transaction 沒管理好。例如：

錯誤： 一個 transaction 把鎖拿太久，卻什麼都沒做（狀態是「idle in transaction」）。

解法： 用 pg_stat_activity 盯著 transaction 狀態，把「掛住」的 transaction 結束掉。

SELECT pid, state, query
FROM pg_stat_activity
WHERE state = 'idle in transaction';

錯誤： 查詢沒用 index，結果整張表都被鎖住。

解法： 最好針對常用條件加 index，優化查詢。

「誰等誰」依賴樹表格

為了方便診斷，你可以畫出一棵依賴樹，顯示哪個 transaction 卡住哪個：

WITH RECURSIVE blocking_tree AS (
  SELECT pid, pg_blocking_pids(pid) AS blocked_by
  FROM pg_stat_activity
  WHERE cardinality(pg_blocking_pids(pid)) > 0
  UNION ALL
  SELECT a.pid, pg_blocking_pids(a.pid)
  FROM pg_stat_activity a
  JOIN blocking_tree b ON a.pid = ANY(b.blocked_by)
)
SELECT pid, blocked_by FROM blocking_tree;

結果：

這裡可以看到 process 5678 卡住 1234，而 1234 又卡住 4567。