Transaction Isolation Levels
PostgreSQL 的三種隔離等級實作、SSI（Serializable Snapshot Isolation）的原理，以及與 MySQL 在隔離策略上的差異
PostgreSQL 只實作三種隔離等級
SQL 標準定義四種隔離等級，但 PostgreSQL 的實作只有三種——Read Uncommitted 被自動升級為 Read Committed：
| SQL 標準 | PostgreSQL 實際行為 | 預設？ |
|---|---|---|
| Read Uncommitted | → Read Committed（不支援髒讀） | — |
| Read Committed | Read Committed | 是 |
| Repeatable Read | Snapshot Isolation | — |
| Serializable | Serializable Snapshot Isolation（SSI） | — |
PostgreSQL 認為髒讀毫無用處，所以直接拿掉了。
Read Committed — 預設
每條 SQL 語句開始時建立一個新的 snapshot。同一筆交易內不同語句可能看到不同的資料（如果中間有其他交易 COMMIT）。
-- Session A
BEGIN;
SELECT amount FROM orders WHERE id = 1; -- 看到 1000
-- Session B
UPDATE orders SET amount = 500 WHERE id = 1;
COMMIT;
-- Session A（同一筆交易）
SELECT amount FROM orders WHERE id = 1; -- 看到 500（新 snapshot）
RC 下的特殊行為：UPDATE 重試
RC 下如果 UPDATE 遇到另一個交易正在修改的行，會等對方 COMMIT 後重新評估 WHERE 條件：
-- Session A
BEGIN;
UPDATE orders SET status = 'shipped' WHERE amount > 100 AND status = 'pending';
-- 找到 id=1 (amount=200, status='pending')，但 Session B 正在改它 → 等待
-- Session B
UPDATE orders SET amount = 50 WHERE id = 1;
COMMIT;
-- Session A 解除等待
-- 重新評估 WHERE: amount=50 > 100? → false → 跳過這行
這是 RC 獨有的——MySQL 的 RC 也有類似行為，但 PostgreSQL 文件對這個語義描述得更清楚。
Repeatable Read — Snapshot Isolation
交易開始時建立一個 snapshot，之後所有語句都用同一個 snapshot。在這個 snapshot 之後其他交易 COMMIT 的修改完全不可見。
-- Session A
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT amount FROM orders WHERE id = 1; -- 看到 1000
-- Session B
UPDATE orders SET amount = 500 WHERE id = 1;
COMMIT;
-- Session A
SELECT amount FROM orders WHERE id = 1; -- 仍然看到 1000
寫衝突偵測（First-Updater-Wins）
如果兩個 RR 交易同時修改同一行，後到的會報錯：
-- Session A (RR)
BEGIN;
UPDATE orders SET amount = 600 WHERE id = 1;
-- Session B (RR)
BEGIN;
UPDATE orders SET amount = 700 WHERE id = 1; -- 等待 Session A
-- Session A
COMMIT;
-- Session B
-- ERROR: could not serialize access due to concurrent update
PostgreSQL 選擇讓後到的交易失敗而非等待。應用層必須 catch 這個 error 並 retry。
MySQL 的 RR 不會這樣——MySQL 會讓 Session B 等待 Session A 釋放鎖，然後在最新版本上修改（因為 UPDATE 是當前讀）。PostgreSQL 認為在舊 snapshot 上做決策然後改到新版本是不安全的，所以直接報錯。
RR 不等於 Serializable
PostgreSQL 的 RR 實作的是 Snapshot Isolation（SI），不是真正的 Serializable。SI 可以防止 dirty read、non-repeatable read、phantom read，但允許一種叫 write skew 的異常：
-- 醫院至少要有一個醫生在班。目前 Alice 和 Bob 都在班。
-- Session A (RR)
BEGIN;
SELECT count(*) FROM doctors WHERE on_call = true; -- 看到 2
UPDATE doctors SET on_call = false WHERE name = 'Alice'; -- 還有 Bob 在
COMMIT;
-- Session B (RR)，幾乎同時
BEGIN;
SELECT count(*) FROM doctors WHERE on_call = true; -- 也看到 2（snapshot）
UPDATE doctors SET on_call = false WHERE name = 'Bob'; -- 還有 Alice 在
COMMIT;
-- 結果：兩個都下班了！violation!
兩個交易各自看到的 snapshot 都顯示「還有另一個人在」，所以各自判斷可以下班。但結果違反了業務規則。
要防止 write skew，需要用 Serializable 或手動加 SELECT ... FOR UPDATE。
Serializable — SSI
PostgreSQL 9.1 引入了 Serializable Snapshot Isolation（SSI），是學術界的重要成果。它在 Snapshot Isolation 之上加入依賴偵測，自動找出可能導致 serialization anomaly 的交易組合並中止其中一個。
SSI 的原理
SSI 追蹤交易之間的三種 dependency（依賴關係）：
- wr-dependency（寫→讀）：T1 寫了一筆資料，T2 讀到了。例如 T1 INSERT 一行，T2 SELECT 看到它
- ww-dependency（寫→寫）：T1 寫了一筆資料，T2 又覆蓋了它。例如兩個交易先後 UPDATE 同一行
- rw-anti-dependency（讀→寫，反向）：T1 讀了某個範圍的資料，T2 在這個範圍內寫入了新資料（但 T1 看不到，因為 snapshot）
用醫生排班例子走一遍 dependency graph
回到前面的例子：Alice 和 Bob 都在班，兩個交易各自讓一個人下班。
Session A：讀 doctors (on_call=true) → 看到 {Alice, Bob}
→ UPDATE Alice 下班
Session B：讀 doctors (on_call=true) → 看到 {Alice, Bob}
→ UPDATE Bob 下班
dependency graph：
Session A ──rw-anti-dependency──→ Session B
（A 讀了 on_call=true 的範圍，B 在這個範圍內寫入了 Bob 下班）
Session B ──rw-anti-dependency──→ Session A
（B 讀了 on_call=true 的範圍，A 在這個範圍內寫入了 Alice 下班）
出現了兩條相鄰的 rw-anti-dependency 形成環路——這正是 SSI 理論中證明的「危險模式」。只要 dependency graph 中有這種結構（稱為 dangerous structure），就可能產生 serialization anomaly。
SSI 偵測到這個環路後，會中止其中一個交易（例如後 COMMIT 的那個），另一個正常完成。被中止的交易收到錯誤後 retry，此時它的 snapshot 會看到第一個交易的結果（只剩一個人在班），就不會再讓第二個人下班了。
為什麼只有 rw-anti-dependency 的環路才危險？
wr-dependency 和 ww-dependency 在 Snapshot Isolation 下本來就被正確處理了（版本可見性 + first-updater-wins）。只有 rw-anti-dependency 是 SI 的盲區——T1 讀的時候 T2 的寫入還不存在（在 T1 的 snapshot 中看不到），所以 T1 的決策是基於不完整的資訊做的。兩條這樣的盲區交叉，就會產生「雙方都基於不完整資訊做決策」的局面。
SSI 的實作
PostgreSQL 維護兩個額外的結構：
- SIREAD Lock：記錄每個 Serializable 交易讀了哪些 tuple/page/table（predicate lock），用來偵測 rw-anti-dependency
- Conflict Detection：在 COMMIT 時檢查是否形成危險的 dependency 模式
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT count(*) FROM doctors WHERE on_call = true; -- 加 SIREAD lock
UPDATE doctors SET on_call = false WHERE name = 'Alice';
COMMIT; -- SSI 檢查 dependency → 如果有 anomaly 風險 → 中止
SSI vs MySQL 的 Serializable
| 面向 | PostgreSQL SSI | MySQL Serializable |
|---|---|---|
| 實作方式 | Snapshot + 依賴偵測 | 所有 SELECT 加 S Lock |
| 讀寫互斥 | 不互斥（仍然走 MVCC） | 互斥（讀阻塞寫、寫阻塞讀） |
| 併發度 | 高（樂觀策略） | 極低（悲觀鎖） |
| 代價 | COMMIT 時可能被中止 | 等待鎖 |
| False positive | 有（偶爾不必要的中止） | 無（但併發差） |
SSI 是樂觀併發控制——先做，COMMIT 時才驗證。MySQL 是悲觀鎖——先鎖再做。SSI 在大部分 workload 下併發度遠勝 MySQL 的 Serializable。
使用 SSI 的建議
- 應用層必須有 retry 邏輯：SSI 中止的交易需要重試
max_pred_locks_per_transaction（預設 64）：predicate lock 的上限，超過會升級為 page/table 級別，增加 false positive- 短交易比長交易好——長交易持有 SIREAD lock 更久，增加衝突概率
- 適合所有交易都用 Serializable 的場景——如果混用不同隔離等級，SSI 的保護可能有漏洞
查看與設定
-- 查看當前隔離等級
SHOW transaction_isolation;
-- Session 級別
SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- 單筆交易
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- postgresql.conf 全域預設
-- default_transaction_isolation = 'read committed'
三種隔離等級比較
| 面向 | Read Committed | Repeatable Read | Serializable |
|---|---|---|---|
| Snapshot 建立 | 每條語句 | 交易開始 | 交易開始 |
| Phantom Read | 有 | 無（SI 天生防） | 無 |
| Write Skew | 有 | 有 | 無（SSI 偵測） |
| 寫衝突 | 等待 + 重新評估 | 報錯（first-updater-wins） | 報錯（SSI 中止） |
| 併發度 | 最高 | 高 | 高（比 MySQL 好很多） |
| 需要 retry? | 否 | 是 | 是 |
與 MySQL 隔離等級的對比
| 面向 | PostgreSQL RR (SI) | MySQL RR |
|---|---|---|
| Snapshot | 交易開始 | 第一次 SELECT |
| Phantom 防護 | SI 天生防 | 快照讀防、當前讀靠 Next-Key Lock |
| 寫衝突 | 報錯 → retry | 等鎖 → 在最新版本上修改 |
| Gap Lock | 無（不需要） | 有（Next-Key Lock） |
| 死鎖風險 | 低（衝突直接報錯，不等待） | 較高（gap lock 交叉） |
| Write Skew | 可能發生 | 也可能發生（但模式不同） |