Transaction 管理：PostgreSQL 的 ACID 實現與進階控制 | PostgreSQL

Transaction（交易） 是資料庫操作的基本單位，確保一組操作要麼全部成功，要麼全部失敗。PostgreSQL 對 ACID 屬性的實現是業界標竿，同時提供 SAVEPOINT、Two-Phase Commit、Advisory Lock 等進階控制機制，讓開發者在複雜業務場景中也能精準掌控資料一致性。

ACID 在 PostgreSQL 中的實現

Atomicity（原子性）

一個 Transaction 內的所有操作要麼全部提交，要麼全部回滾。PostgreSQL 透過 WAL 和 MVCC 共同保證：

• WAL 確保在 Crash Recovery 時能重放（redo）或撤銷操作
• MVCC 確保中間狀態對其他 Transaction 不可見

Consistency（一致性）

Transaction 前後資料必須滿足所有定義的約束：

• CHECK Constraints：行級資料驗證
• Foreign Key Constraints：參照完整性
• UNIQUE / NOT NULL：欄位約束
• Triggers：複雜的業務邏輯驗證
• Deferrable Constraints：可延遲到 Transaction 結束時驗證

Durability（持久性）

COMMIT 成功後，資料必須在任何故障後仍能恢復：

WAL 確保 Durability 的流程：

  SQL 操作
     │
     ▼
  修改 Shared Buffer（記憶體）
     │
     ▼
  寫入 WAL Buffer
     │
     ▼  COMMIT 時
  WAL flushed to disk ← fsync 確保真正落盤
     │
     ▼
  回應 client：COMMIT 成功
     │（之後）
     ▼
  Checkpointer 定期將 dirty pages 寫入 data files

synchronous_commit 參數控制效能與持久性的平衡。

Transaction 狀態機

idle ──BEGIN──→ active ──SQL──→ active
                  │
                  ├──COMMIT──────────────→ idle
                  │
                  ├──ROLLBACK────────────→ idle
                  │
                  └──SQL Error──→ aborted
                                    │
                                    └──ROLLBACK──→ idle

重要：進入 aborted 狀態後，只能執行 ROLLBACK，其他 SQL 均報錯：

ERROR: current transaction is aborted, commands ignored until
       end of transaction block

Autocommit：PostgreSQL 預設 autocommit 模式。若不顯式 BEGIN，每條 SQL 自動包裝在隱式 Transaction 中並立即提交。

Transaction 基本操作

-- 基本 Transaction
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

-- 帶回滾的 Transaction
BEGIN;
UPDATE orders SET status = 'processing' WHERE id = 42;
-- 某些錯誤條件
ROLLBACK;  -- 撤銷所有修改

-- 指定隔離等級
BEGIN ISOLATION LEVEL SERIALIZABLE READ WRITE;

SAVEPOINT：部分回滾

SAVEPOINT 允許在 Transaction 內設置檢查點，出錯時只回滾到該檢查點，而非整個 Transaction：

BEGIN
  │
  ├─ INSERT A ─ (成功)
  │
  ├─ SAVEPOINT sp1 ← 設定檢查點
  │
  ├─ INSERT B ─ (成功)
  ├─ UPDATE C ─ (失敗)
  │
  ├─ ROLLBACK TO SAVEPOINT sp1 ← A 保留，B/C 撤銷
  │
  ├─ INSERT D ─ (成功，替代 B/C)
  │
  └─ COMMIT ← 只提交 A 和 D

BEGIN;

INSERT INTO orders (user_id, amount) VALUES (1, 500);

SAVEPOINT before_items;

INSERT INTO order_items (order_id, product_id, qty) VALUES (1001, 5, 2);
INSERT INTO order_items (order_id, product_id, qty) VALUES (1001, 99, 1);
-- 假設 product_id=99 不存在，觸發 FK violation

ROLLBACK TO SAVEPOINT before_items;
-- order_items 的插入撤銷，但 orders 的 INSERT 保留

INSERT INTO order_items (order_id, product_id, qty) VALUES (1001, 7, 3);

COMMIT;
-- 最終結果：orders(1001) 和 order_items(1001, 7, 3) 均提交

SAVEPOINT 可以嵌套，但要注意效能：大量 SAVEPOINT（如循環中使用）會顯著影響效能，因為每個 Subtransaction 需要在 pg_subtrans/ 記錄狀態。

Two-Phase Commit（2PC）

Two-Phase Commit 用於跨多個資源管理器的分散式 Transaction：

Phase 1: PREPARE
  應用程式 → PostgreSQL: PREPARE TRANSACTION 'txid-1'
  應用程式 → 另一個資源: PREPARE
  （所有資源回應「ready」）

Phase 2: COMMIT / ROLLBACK
  應用程式 → PostgreSQL: COMMIT PREPARED 'txid-1'
  應用程式 → 另一個資源: COMMIT

-- 需先設定 max_prepared_transactions > 0

-- Phase 1：準備
BEGIN;
UPDATE inventory SET quantity = quantity - 1 WHERE sku = 'PROD-001';
PREPARE TRANSACTION 'order-txn-001';
-- Transaction 進入 prepared 狀態，可跨 crash 恢復

-- Phase 2：確認提交
COMMIT PREPARED 'order-txn-001';

-- 或回滾
-- ROLLBACK PREPARED 'order-txn-001';

-- 監控 prepared transactions（重要！）
SELECT gid, prepared, owner, database, age(transaction) AS xid_age
FROM pg_prepared_xacts;

注意：遺忘的 Prepared Transaction 會阻止 VACUUM 推進 xid_horizon，需要定期監控。

Advisory Lock：應用層分散式鎖

Advisory Lock 是 PostgreSQL 提供的輕量級應用層鎖，不依附於任何資料庫物件：

-- Transaction-level Advisory Lock（推薦：transaction 結束自動釋放）
BEGIN;
SELECT pg_try_advisory_xact_lock(hashtext('cron-job:daily-report'));
-- 返回 true = 獲取成功，false = 已被其他 session 持有

DO $$
DECLARE
    got_lock BOOLEAN;
BEGIN
    got_lock := pg_try_advisory_xact_lock(hashtext('cron-job:daily-report'));
    IF NOT got_lock THEN
        RAISE NOTICE 'Another instance is running, skipping.';
        RETURN;
    END IF;
    -- 執行任務...
    PERFORM process_daily_report();
END;
$$;
COMMIT;  -- 鎖自動釋放

-- 查看當前持有的 Advisory Locks
SELECT pid, locktype, objid, mode, granted
FROM pg_locks
WHERE locktype = 'advisory';

Deadlock Detection

PostgreSQL 維護一個 Wait-For Graph，定期（由 deadlock_timeout 控制，預設 1 秒）檢查環狀等待：

TX1                          TX2
 │                            │
 ├─ LOCK row A（成功）        ├─ LOCK row B（成功）
 │                            │
 ├─ LOCK row B（等待 TX2）    ├─ LOCK row A（等待 TX1）
 │                            │
 └─ Wait-For Graph：
    TX1 → TX2
    TX2 → TX1  ← 環！偵測到 Deadlock
    PostgreSQL 回滾其中一個 TX 並報錯：
    ERROR: deadlock detected

設定超時保護

-- idle in transaction 超時（推薦生產環境設定）
ALTER SYSTEM SET idle_in_transaction_session_timeout = '5min';

-- 單條 SQL 最大執行時間
ALTER SYSTEM SET statement_timeout = '30s';

-- 等待鎖的超時
ALTER SYSTEM SET lock_timeout = '10s';

-- 套用設定
SELECT pg_reload_conf();

-- 在角色層級設定（針對特定應用帳號）
ALTER ROLE app_user SET statement_timeout = '30s';
ALTER ROLE app_user SET idle_in_transaction_session_timeout = '5min';
ALTER ROLE app_user SET lock_timeout = '5s';

監控 Transaction 狀態

-- 查看所有非 idle 的 session
SELECT
    pid,
    usename,
    application_name,
    now() - xact_start AS xact_duration,
    state,
    wait_event_type,
    wait_event,
    left(query, 100) AS query_snippet
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY xact_start NULLS LAST;

-- 找出長時間運行的 Transaction（超過 5 分鐘）
SELECT pid, state, xact_start, now() - xact_start AS age, query
FROM pg_stat_activity
WHERE xact_start < now() - interval '5 minutes'
  AND state != 'idle';

-- 終止問題 session
SELECT pg_cancel_backend(pid);     -- 取消當前查詢（溫和）
SELECT pg_terminate_backend(pid);  -- 強制終止

Connection Pooler 對 Transaction 的影響

PgBouncer 在 Transaction Pooling 模式下，每條 SQL 可能由不同的 backend 連線執行：

PgBouncer Transaction Pooling 的限制：

  SESSION-LEVEL Advisory Locks、TEMPORARY TABLE、
  SET LOCAL 設定等跨 statement 的 session 狀態
  無法保證在同一 backend！

  解決方案：
  - 使用 TRANSACTION-level 的 Advisory Lock
  - 避免在 pool 模式下使用 session-level 功能
  - 或使用 session pooling（效能較差但行為一致）

Deferrable Constraints

處理循環依賴時，可延遲外鍵檢查到 Transaction 結束：

-- 建立可延遲的外鍵
ALTER TABLE order_items
    ADD CONSTRAINT fk_order
    FOREIGN KEY (order_id) REFERENCES orders(id)
    DEFERRABLE INITIALLY DEFERRED;

-- 在 Transaction 中動態設定
BEGIN;
SET CONSTRAINTS fk_order DEFERRED;
-- 現在可以先插入 order_items，再插入 orders
COMMIT;  -- 此時才檢查 FK

常見陷阱

Autocommit 誤解

-- psql 預設每條 SQL autocommit，以下不是一個 Transaction！
UPDATE accounts SET balance = balance - 100 WHERE id = 1;  -- 立即提交
UPDATE accounts SET balance = balance + 100 WHERE id = 2;  -- 立即提交
-- 如果第二條失敗，第一條已無法回滾！

-- 正確做法：
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

忘記 COMMIT 的後果

• idle in transaction 的 session 持有鎖，阻塞其他操作
• 阻礙 VACUUM，導致 Dead Tuple 堆積
• 解決方案：設定 idle_in_transaction_session_timeout

總結

Transaction 管理 是 PostgreSQL 資料一致性的核心保障：

• ACID 透過 WAL（原子性、持久性）、MVCC（隔離性）、約束（一致性）共同實現
• SAVEPOINT 提供部分回滾能力，但要注意 Subtransaction 的效能開銷
• Two-Phase Commit 實現跨資源的分散式 Transaction，但 Prepared Transaction 需要監控
• Advisory Lock 是輕量級的應用層分散式鎖，推薦使用 Transaction-level 版本
• Deadlock Detection 透過 Wait-For Graph 自動偵測並解決死鎖
• 生產環境務必設定 idle_in_transaction_session_timeout、statement_timeout、lock_timeout

下一篇，我們將深入探討 並行控制與鎖機制——PostgreSQL 如何透過多層次鎖設計協調並行存取。