前言

Transaction isolation level 是資料庫最核心但也最容易被忽略的概念之一。很多後端工程師寫了好幾年的程式，都是用資料庫預設的隔離級別，從來沒有去想過「為什麼要有隔離級別」、「不同級別之間有什麼差異」。

直到某天遇到了詭異的 bug——明明剛寫入的資料為什麼讀不到？為什麼兩個並行的交易算出來的餘額不一致？為什麼我的 SELECT 結果在同一個交易裡變了？

這篇文章不會只講理論，我會用 PostgreSQL 的實際實驗帶你「看到」每種隔離級別的行為差異。打開你的 terminal，跟著一起跑這些實驗，比讀十遍教科書都有用。

三種異常現象

在討論隔離級別之前，先定義三種 SQL 標準裡描述的異常現象：

髒讀（Dirty Read）

交易 A                          交易 B
─────                          ─────
BEGIN;
UPDATE accounts SET balance = 500
  WHERE id = 1;
-- balance 從 1000 改為 500（尚未 COMMIT）
                                BEGIN;
                                SELECT balance FROM accounts WHERE id = 1;
                                -- 讀到 500（A 尚未 commit 的資料）← 髒讀！
ROLLBACK;
-- A 回滾了，balance 回到 1000
                                -- 但 B 已經用了 500 做計算... 災難！

不可重複讀（Non-Repeatable Read）

交易 A                          交易 B
─────                          ─────
BEGIN;
SELECT balance FROM accounts WHERE id = 1;
-- 讀到 1000
                                BEGIN;
                                UPDATE accounts SET balance = 500 WHERE id = 1;
                                COMMIT;
SELECT balance FROM accounts WHERE id = 1;
-- 讀到 500 ← 同一個交易裡，同樣的查詢得到不同結果！
COMMIT;

幻讀（Phantom Read）

交易 A                          交易 B
─────                          ─────
BEGIN;
SELECT COUNT(*) FROM orders WHERE status = 'pending';
-- 得到 10
                                BEGIN;
                                INSERT INTO orders (status) VALUES ('pending');
                                COMMIT;
SELECT COUNT(*) FROM orders WHERE status = 'pending';
-- 得到 11 ← 多了一筆「幻影」資料！
COMMIT;

四種隔離級別

隔離級別              髒讀    不可重複讀   幻讀
──────────────────────────────────────────────
READ UNCOMMITTED      可能     可能       可能
READ COMMITTED        不可能   可能       可能
REPEATABLE READ       不可能   不可能     可能*
SERIALIZABLE          不可能   不可能     不可能


* PostgreSQL 的 REPEATABLE READ 實際上也防止幻讀（比 SQL 標準更嚴格）

實驗環境準備

-- 建立實驗用的表
CREATE TABLE accounts (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50),
    balance NUMERIC(10, 2)
);


INSERT INTO accounts (name, balance) VALUES
    ('Alice', 1000.00),
    ('Bob', 2000.00),
    ('Charlie', 3000.00);



-- 確認資料
SELECT * FROM accounts;
--  id |  name   | balance
-- ----+---------+---------
--   1 | Alice   | 1000.00
--   2 | Bob     | 2000.00
--   3 | Charlie | 3000.00

你需要打開兩個 terminal（或 psql session），分別執行交易 A 和交易 B。

實驗一：READ COMMITTED（PostgreSQL 預設）

-- === Terminal A ===
BEGIN;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;


-- 第一次讀取 Alice 的餘額
SELECT balance FROM accounts WHERE id = 1;
-- 結果：1000.00

-- === Terminal B ===
BEGIN;
UPDATE accounts SET balance = 500.00 WHERE id = 1;
COMMIT;
-- Alice 的餘額被改為 500
-- === Terminal A（繼續）===
-- 第二次讀取 Alice 的餘額
SELECT balance FROM accounts WHERE id = 1;
-- 結果：500.00 ← 讀到了 B 提交後的新值！
-- 這就是「不可重複讀」


-- 但是不會讀到未提交的修改（沒有髒讀）
COMMIT;



-- 還原資料
UPDATE accounts SET balance = 1000.00 WHERE id = 1;

READ COMMITTED 的行為特點

-- 重要行為：UPDATE 會等待鎖
-- === Terminal A ===
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- Alice: 1000 → 900（尚未 COMMIT）


-- === Terminal B ===
BEGIN;
UPDATE accounts SET balance = balance - 200 WHERE id = 1;
-- 這裡會 BLOCK！等待 A 釋放鎖



-- === Terminal A ===
COMMIT;
-- A 提交後，B 立即繼續執行
-- B 會重新讀取 balance（900），然後減 200
-- 最終 Alice balance = 700



-- === Terminal B ===
COMMIT;
SELECT balance FROM accounts WHERE id = 1;
-- 結果：700（正確！900 - 200 = 700）



-- 還原
UPDATE accounts SET balance = 1000.00 WHERE id = 1;

實驗二：REPEATABLE READ

-- === Terminal A ===
BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;


SELECT balance FROM accounts WHERE id = 1;
-- 結果：1000.00

-- === Terminal B ===
BEGIN;
UPDATE accounts SET balance = 500.00 WHERE id = 1;
COMMIT;
-- === Terminal A（繼續）===
-- 第二次讀取
SELECT balance FROM accounts WHERE id = 1;
-- 結果：1000.00 ← 還是 1000！看不到 B 的修改
-- 這就是 REPEATABLE READ 的保證：同一個交易裡的讀取結果一致


-- 幻讀測試
SELECT COUNT(*) FROM accounts;
-- 結果：3

-- === Terminal B ===
INSERT INTO accounts (name, balance) VALUES ('Dave', 4000.00);
-- === Terminal A（繼續）===
SELECT COUNT(*) FROM accounts;
-- 結果：3 ← 還是 3！看不到 B 新增的 Dave
-- PostgreSQL 的 REPEATABLE READ 也防止幻讀


COMMIT;



-- 交易結束後才看得到
SELECT COUNT(*) FROM accounts;
-- 結果：4



-- 還原
UPDATE accounts SET balance = 1000.00 WHERE id = 1;
DELETE FROM accounts WHERE name = 'Dave';

REPEATABLE READ 的寫入衝突

-- REPEATABLE READ 下的寫入衝突會直接報錯（不像 READ COMMITTED 會等待並重試）


-- === Terminal A ===
BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- 900



-- === Terminal B ===
BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
UPDATE accounts SET balance = balance - 200 WHERE id = 1;
-- BLOCK... 等待 A



-- === Terminal A ===
COMMIT;
-- A 提交後...



-- === Terminal B ===
-- ERROR: could not serialize access due to concurrent update
-- B 直接失敗！不會像 READ COMMITTED 那樣重試



ROLLBACK;  -- B 必須回滾並重試



-- 還原
UPDATE accounts SET balance = 1000.00 WHERE id = 1;

實驗三：SERIALIZABLE

-- SERIALIZABLE 是最嚴格的隔離級別
-- 它保證所有並行交易的結果等同於「某種序列執行順序」


-- 經典場景：兩個交易同時讀取並基於讀取結果做決策



-- === Terminal A ===
BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;



-- 檢查所有帳戶的總餘額
SELECT SUM(balance) FROM accounts;
-- 結果：6000.00



-- 根據總餘額做決策：如果 < 10000 就給 Alice 加薪
UPDATE accounts SET balance = balance + 500 WHERE id = 1;
-- Alice: 1000 → 1500

-- === Terminal B ===
BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;


-- 也檢查總餘額
SELECT SUM(balance) FROM accounts;
-- 結果：6000.00（看不到 A 的未提交修改）



-- B 也做類似的決策：給 Bob 加薪
UPDATE accounts SET balance = balance + 500 WHERE id = 2;
-- Bob: 2000 → 2500

-- === Terminal A ===
COMMIT;
-- OK，A 成功提交


-- === Terminal B ===
COMMIT;
-- ERROR: could not serialize access due to read/write dependencies among transactions
-- B 被拒絕！因為如果 B 成功，等同於 A 和 B 同時基於 6000 的總餘額做了決策
-- 但實際上 A 已經改了餘額，B 的前提已經不成立



ROLLBACK;



-- 結果：只有 A 成功
SELECT * FROM accounts;
--  id |  name   | balance
-- ----+---------+---------
--   1 | Alice   | 1500.00  ← A 成功
--   2 | Bob     | 2000.00  ← B 失敗，沒改到
--   3 | Charlie | 3000.00



-- 還原
UPDATE accounts SET balance = 1000.00 WHERE id = 1;

SERIALIZABLE 的應用場景

# SERIALIZABLE 交易可能被回滾，應用程式必須準備好重試


import psycopg2
import time



def transfer_with_retry(conn_string, from_id, to_id, amount, max_retries=3):
    """帶重試機制的轉帳操作。"""
    for attempt in range(max_retries):
        conn = psycopg2.connect(conn_string)
        try:
            conn.set_isolation_level(
                psycopg2.extensions.ISOLATION_LEVEL_SERIALIZABLE
            )
            with conn.cursor() as cur:
                # 檢查餘額
                cur.execute(
                    "SELECT balance FROM accounts WHERE id = %s", (from_id,)
                )
                balance = cur.fetchone()[0]



if balance < amount:
                    raise ValueError(f"餘額不足: {balance} < {amount}")



# 執行轉帳
                cur.execute(
                    "UPDATE accounts SET balance = balance - %s WHERE id = %s",
                    (amount, from_id)
                )
                cur.execute(
                    "UPDATE accounts SET balance = balance + %s WHERE id = %s",
                    (amount, to_id)
                )



conn.commit()
            print(f"轉帳成功（第 {attempt + 1} 次嘗試）")
            return True



except psycopg2.errors.SerializationFailure:
            conn.rollback()
            print(f"序列化衝突，第 {attempt + 1} 次重試...")
            time.sleep(0.1 * (2 ** attempt))  # exponential backoff



except Exception as e:
            conn.rollback()
            raise e



finally:
            conn.close()



raise Exception(f"重試 {max_retries} 次後仍然失敗")

MVCC 原理深入

PostgreSQL 的 MVCC 實作

-- PostgreSQL 的每一筆 tuple 都帶有隱藏的系統欄位
-- xmin: 建立這筆 tuple 的 transaction ID
-- xmax: 刪除（或更新）這筆 tuple 的 transaction ID


-- 查看隱藏欄位
SELECT xmin, xmax, * FROM accounts;
--  xmin  | xmax |  id |  name   | balance
-- -------+------+-----+---------+---------
--  1234  |    0 |   1 | Alice   | 1000.00
--  1234  |    0 |   2 | Bob     | 2000.00
--  1234  |    0 |   3 | Charlie | 3000.00



-- 更新 Alice
BEGIN;
UPDATE accounts SET balance = 1500 WHERE id = 1;



-- 查看變化
SELECT xmin, xmax, * FROM accounts WHERE id = 1;
--  xmin  | xmax |  id | name  | balance
-- -------+------+-----+-------+---------
--  5678  |    0 |   1 | Alice | 1500.00
-- xmin 變了！PostgreSQL 其實建立了一個新 tuple
-- 舊的 tuple（xmin=1234）的 xmax 會被設為 5678



COMMIT;



-- 還原
UPDATE accounts SET balance = 1000 WHERE id = 1;

可見性判斷（Visibility Rules）

PostgreSQL 判斷一筆 tuple 是否可見的簡化規則：


READ COMMITTED:
  可見條件：
    - xmin 已提交 且 xmin < 當前 command 開始時的最大已提交 txid
    - xmax 尚未提交 或 xmax > 當前 command 開始時的最大已提交 txid



REPEATABLE READ / SERIALIZABLE:
  可見條件：
    - xmin 已提交 且 xmin < 當前 transaction 開始時的 snapshot
    - xmax 尚未提交 或 xmax > 當前 transaction 開始時的 snapshot



關鍵差異：
  READ COMMITTED  → 每個 command（每次 SELECT）取新的 snapshot
  REPEATABLE READ → 整個 transaction 用同一個 snapshot

-- 觀察 snapshot
-- PostgreSQL 提供 txid_current_snapshot() 函式


BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;



SELECT txid_current_snapshot();
-- 結果類似：5680:5682:5681
-- 格式：xmin:xmax:xip_list
-- xmin: 最小的活躍交易 ID（小於此值的都已提交或回滾）
-- xmax: 下一個要分配的交易 ID（大於等於此值的都尚未開始）
-- xip_list: 在 snapshot 建立時仍在執行中的交易 ID 列表



COMMIT;

各隔離級別的效能影響

-- SERIALIZABLE 會比 READ COMMITTED 有更多的鎖衝突和回滾
-- 但如果你的業務邏輯需要強一致性，效能代價是值得的


-- 監控因序列化失敗而回滾的交易數量
SELECT
    datname,
    conflicts,
    deadlocks,
    xact_commit,
    xact_rollback,
    round(xact_rollback::numeric / greatest(xact_commit + xact_rollback, 1) * 100, 2) AS rollback_pct
FROM pg_stat_database
WHERE datname = current_database();

實務建議

# 不同場景使用不同隔離級別


def get_isolation_level(operation_type: str) -> str:
    """根據操作類型選擇合適的隔離級別。"""



recommendations = {
        # 一般的 CRUD：用預設的 READ COMMITTED 就好
        "simple_crud": "READ COMMITTED",



# 報表查詢：需要一致的快照
        "reporting": "REPEATABLE READ",



# 金融交易：需要最強的一致性保證
        "financial_transfer": "SERIALIZABLE",



# 庫存扣減：用 SELECT FOR UPDATE 比改隔離級別更實用
        "inventory_deduction": "READ COMMITTED + SELECT FOR UPDATE",



# 批次處理：通常不需要高隔離級別
        "batch_processing": "READ COMMITTED",
    }



return recommendations.get(operation_type, "READ COMMITTED")

-- 常見替代方案：SELECT FOR UPDATE（比提高隔離級別更精準）
-- 只鎖特定的行，不影響其他查詢


BEGIN;
-- 鎖定 Alice 的帳戶行（其他交易讀 Alice 會 block）
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;
-- 做業務邏輯判斷...
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;



-- FOR UPDATE NOWAIT：不想等待就立即報錯
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE NOWAIT;
-- 如果被鎖：ERROR: could not obtain lock on row in relation "accounts"



-- FOR UPDATE SKIP LOCKED：跳過被鎖的行（適用於 job queue 模式）
SELECT id, task FROM job_queue
WHERE status = 'pending'
ORDER BY created_at
LIMIT 1
FOR UPDATE SKIP LOCKED;

小結

Transaction isolation level 的核心心得：

  1. READ COMMITTED 是 90% 場景的正確選擇：PostgreSQL 預設就是這個，大部分 CRUD 操作夠用了
  2. REPEATABLE READ 適合需要一致性快照的場景：報表、對帳、需要在同一交易裡多次讀取相同資料
  3. SERIALIZABLE 是最安全但最貴的選擇：金融交易等強一致性場景，但必須搭配重試機制
  4. SELECT FOR UPDATE 往往比提高隔離級別更實用：精準鎖定需要的行，不影響其他操作
  5. PostgreSQL 的 REPEATABLE READ 比 SQL 標準更嚴格：它實際上也防止幻讀

最重要的建議：自己動手做實驗。打開兩個 psql session，實際操作一遍，比讀任何文章都能讓你真正理解隔離級別的行為。

延伸閱讀建議：