PostgreSQL MVCC、Isolation 與 Transaction Retry 深入版

0. 先用圖建立全貌：版本、快照、衝突與重試

先看兩張圖。第一張回答「為什麼 reader 和 writer 可以同時前進」；第二張回答「isolation level 真正逐級增加的是什麼」。

0.1 MVCC 不是複製整座 database

Snapshot 是 visibility 規則與 transaction 邊界，不是一份完整資料副本。Query 仍讀 shared heap／index，只是不同 transaction 對同一批 versions 得出不同「可見或不可見」結果。

0.2 Isolation ladder

Isolation 越高不代表等待永遠越久，也不代表不會失敗。PostgreSQL Serializable 的核心策略是：允許 concurrency，但把無法序列化的執行轉成明確 abort。

1. 先定義真正問題：Concurrent transaction 如何維持 invariant

1.1 生活直覺：多人同時編輯不是只有「最後存檔的人贏」

兩個店員同時看到庫存剩 1，各自接受一張訂單，再把庫存寫成 0。最後欄位看起來合理，卻接受了兩筆訂單。問題不是 SQL 語法錯，而是「讀取 → 決策 → 寫入」跨越多個步驟，兩個 transaction 使用了相同的舊事實。

1.2 工程術語橋接

1.3 High-level 定義

Transaction isolation 控制一個 transaction 在 concurrent execution 中允許觀察哪些資料狀態。MVCC 透過多個 row versions 與 snapshot visibility，讓一般讀取不必阻塞一般寫入；但 MVCC 不會自動保護所有跨 row、跨 statement 或業務層 invariant。

1.4 先寫 invariant，再選工具

例子：

• inventory.available >= 0
• 同一 idempotency_key 最多對應一個已接受 operation。
• 至少一位 doctor 必須 on-call。
• account A + account B 的總額保持不變。
• 每個 username 唯一。

如果無法用一句可驗證的句子寫出 invariant，就很難判斷某個 isolation level 是否足夠。

2. Transaction、statement 與 snapshot 是不同邊界

2.1 Transaction 的責任

Transaction 把一組 database operations 形成 commit／rollback 邊界。ACID 中與本課最相關的是：

• Atomicity：整組變更全成或全不成。
• Consistency：transaction 若從合法狀態開始，應維持已定義 invariant。
• Isolation：concurrent transactions 的中間互動受到限制。
• Durability：commit 後的結果能在故障模型內保存。

Isolation 不替 application 自動定義 consistency。資料庫不知道「至少一位醫師值班」是你的業務規則，除非它被 constraint、lock protocol 或 Serializable transaction 的 reads/writes 表達出來。

2.2 Autocommit 的隱藏邊界

若每條 SQL 都自動 commit，以下兩句不是一個 atomic operation：

SELECT available FROM inventory WHERE sku = 'book-1';
UPDATE inventory SET available = 0 WHERE sku = 'book-1';

你必須明確知道 driver／ORM 何時 begin、flush、commit、rollback。Python async with session.begin() 是 application transaction boundary，不是單純語法裝飾。

2.3 Statement snapshot 與 transaction snapshot

PostgreSQL 預設 Read Committed：每個 statement 開始時取得新的 snapshot。Repeatable Read／Serializable：transaction 中第一個非 transaction-control statement 建立穩定 snapshot，之後一般 reads 使用同一視圖。

3. MVCC 底層心智模型：row 不是被原地覆寫

3.1 多版本的直覺

在 PostgreSQL heap 中，UPDATE 通常建立新的 tuple version，舊版本暫時保留。不同 snapshot 可以同時判定不同版本可見，因此 reader 不必等 writer commit 才能繼續讀自己的舊視圖。

tuple v1: available=1, xmin=100, xmax=120
tuple v2: available=0, xmin=120, xmax=0

這是概念圖，不應把 xmax=0 當成所有內部情況的完整描述。tuple header 還有 hint bits、command IDs、MultiXact 等細節。

3.2 xmin 與 xmax

• xmin：建立此 tuple version 的 transaction ID。
• xmax：刪除、更新或鎖定此 version 的 transaction／MultiXact 資訊。
• visibility 判斷還需要 transaction commit status、snapshot 的 in-progress set 與目前 command。

低 transaction ID 不必然代表 transaction 較早開始；官方文件說明，non-virtual xid 通常在 transaction 首次寫入時才配置。

3.3 Snapshot 概念

一個 snapshot 必須能區分：

• snapshot 建立前已 commit 的 transactions。
• snapshot 當下仍 in progress 的 transactions。
• snapshot 之後才開始／取得 xid 的 transactions。
• 自己 transaction 先前 commands 的結果。

不要把 snapshot 想成完整複製 database。它更像一套 visibility 判斷邊界，query 仍從 shared heap／index 找 tuple，再判定哪個 version 可見。

3.4 讀不擋寫、寫不擋讀的精確含義

PostgreSQL 官方概括為一般讀鎖與寫鎖不互相衝突，所以 reading never blocks writing、writing never blocks reading。但這不代表「PostgreSQL 沒有 locks」：

• writer 彼此仍可能在 row 上等待。
• DDL 的 ACCESS EXCLUSIVE 可以阻塞普通 SELECT。
• SELECT ... FOR UPDATE 是 locking read。
• Serializable 的 SIRead predicate locks 用於偵測 dependency，但本身不阻塞。

4. Read Committed：每條 statement 都可能看到新的世界

4.1 官方行為

Read Committed 是 PostgreSQL 預設 isolation level。普通 SELECT 看見 statement 開始前已 commit 的資料，加上自己 transaction 先前的變更；同一 transaction 的兩次 SELECT 可能看見不同結果。

4.2 Nonrepeatable read 時間線

T1                                      T2
BEGIN;
SELECT balance; -- 100
                                        BEGIN;
                                        UPDATE ... SET balance = 80;
                                        COMMIT;
SELECT balance; -- 80
COMMIT;

兩次 SELECT 各有 statement snapshot，所以結果可不同。這不等於 dirty read；T1 只看到 T2 已 commit 的值。

4.3 Lost update：application read-modify-write

T1 reads available=1
T2 reads available=1
T1 decides yes; writes 0; commits
T2 decides yes; writes 0; commits

若 application 把新值算好後做 SET available = :new_value，兩邊都可能寫 0，表面值合法但接受兩次操作。

4.4 優先改寫成單一 atomic statement

UPDATE inventory
SET available = available - 1
WHERE sku = :sku
  AND available >= 1
RETURNING available;

同一 row 的 concurrent UPDATE 會透過 row-level conflict 協調，等待後重新檢查條件。application 以 affected row count／RETURNING 判斷成功，不先讀再猜。

這通常比 SELECT 後 UPDATE 更簡潔，但只能保護能表達在單一 statement／constraint 的 invariant。

4.5 Read Committed 的 updating command 特性

UPDATE、DELETE、locking SELECT 先依 statement snapshot 找 target；若 target row 被 concurrent transaction 改過，會等待其結束，之後可能在 updated version 上重新檢查條件。這讓簡單 conditional update 很實用，但複雜跨 row 邏輯仍可能看到不一致組合。

5. Pessimistic locking：先取得修改權，再做決策

5.1 SELECT ... FOR UPDATE

BEGIN;

SELECT available
FROM inventory
WHERE sku = 'book-1'
FOR UPDATE;

-- application validates available >= 1
UPDATE inventory
SET available = available - 1
WHERE sku = 'book-1';

COMMIT;

第二個 transaction 對同一 row 取得 incompatible lock 時會等待。鎖通常持有到 transaction end，因此 transaction 中不要做緩慢 HTTP call、人工輸入或不必要 CPU 工作。

5.2 四種 row locking clause

選擇應依實際 conflict matrix，不要只憑名稱猜。

5.3 NOWAIT 與 SKIP LOCKED

SELECT id
FROM jobs
WHERE status = 'ready'
ORDER BY id
FOR UPDATE SKIP LOCKED
LIMIT 1;

SKIP LOCKED 適合多 worker queue claim，因為允許跳過正在處理的 rows；它產生的是刻意不一致的 view，不適合一般報表或需要完整集合的業務查詢。

NOWAIT 適合快速失敗，由 application 決定稍後 retry／回應 conflict。

5.4 Table locks 仍存在

普通 SELECT 取得 ACCESS SHARE；INSERT/UPDATE/DELETE 對 target table 取得 ROW EXCLUSIVE。只有 ACCESS EXCLUSIVE 會阻塞普通 SELECT，但許多 DDL、TRUNCATE、VACUUM FULL 會取得它。Migration 必須評估 lock acquisition 與持有時間。

6. Optimistic concurrency：寫入時證明讀到的版本仍有效

6.1 Version column

UPDATE inventory
SET available = :new_available,
    version = version + 1
WHERE sku = :sku
  AND version = :expected_version
RETURNING version;

若 affected rows = 0，代表 row 不存在或 version 已變。Application 必須重新讀取、重新執行 business decision，或回傳 conflict；不能只把舊的 new_available 再寫一次。

6.2 適用情境

• conflict 相對少。
• transaction 不適合長時間持鎖。
• 使用者編輯表單後可能數秒才提交。
• API 已有 ETag／If-Match 或版本欄位。

6.3 限制

• 只檢查一個 row version，不自動保護跨 row invariant。
• retry 必須重跑 decision，而不是只重送 UPDATE。
• 高 contention 下可能反覆衝突，pessimistic lock 更可預測。

7. Repeatable Read：穩定 snapshot 不等於 serializable

7.1 PostgreSQL 的 Repeatable Read

PostgreSQL Repeatable Read 使用 transaction-level snapshot，並且比 SQL standard 最低要求更強：不允許 phantom read。但仍可能有 serialization anomaly。

7.2 Concurrent update

若 Repeatable Read transaction 嘗試更新一個在它 snapshot 後被別人改過並 commit 的 row，可能收到：

ERROR: could not serialize access due to concurrent update
SQLSTATE: 40001

必須 rollback 並從 transaction 開頭重跑。

7.3 Write skew：各寫不同 row

Invariant：至少一位 doctor on-call。

T1 snapshot: Alice=true, Bob=true       T2 snapshot: Alice=true, Bob=true
T1 decides Bob remains; Alice=false     T2 decides Alice remains; Bob=false
T1 writes Alice row                     T2 writes Bob row
T1 commits                              T2 commits
Final: nobody on-call

兩個 transaction 寫不同 row，沒有直接 write-write conflict；每個 snapshot 都穩定，卻沒有任何 serial order 能產生相同決策結果。這就是「一致 snapshot」和「可序列化結果」的差異。

7.4 如何修正 write skew

• 將 invariant 改造成可由 constraint／單 row atomic update 表達。
• 對代表 invariant 的 rows 取得一致的 FOR UPDATE locks。
• 使用 Serializable 並正確 retry。
• 重新設計 aggregate root，讓 conflict 集中到一個 versioned row。

8. Serializable：成功 commit 的結果必須有 serial order

8.1 High-level 保證

PostgreSQL Serializable 讓所有成功 commit 的 concurrent transactions，其效果等同以某個順序逐一執行。它不是把所有 transaction 實際排成單線；它在 Repeatable Read snapshot 基礎上監控可能形成 serialization anomaly 的 read/write dependencies。

8.2 SSI 與 predicate locks

Serializable Snapshot Isolation（SSI）追蹤「某 transaction 的 write 是否影響另一 transaction 先前 read 的 predicate」。pg_locks 中可看到 SIReadLock。這些 predicate locks 用於偵測，不像 row lock 那樣阻塞，因此不造成 deadlock。

當 dependency 組合可能形成 cycle，PostgreSQL 會 abort 某個 transaction，回傳 40001，保留 serializability。

8.3 Serializable 不是「不會失敗」

它把 silent anomaly 轉換成明確 abort。Application contract 必須接受：

attempt 1 → serialization_failure → rollback
backoff/jitter
attempt 2 → re-read → re-decide → commit

8.4 Read-only deferrable

SERIALIZABLE READ ONLY DEFERRABLE 可等待一個已知安全 snapshot，適合長時間一致報表。它可能在開始前等待，但一旦取得安全 snapshot，就能避免後續 serialization failure 類型的風險。

8.5 使用條件

• 所有參與 invariant 的 transaction 都必須遵守相容 protocol。
• transaction 要短小，避免 idle in transaction。
• 控制 active connections。
• 必須有 generalized retry mechanism。
• 不能在 transaction 內做不可回滾的外部 side effect，除非使用 outbox 等設計。

9. Transaction retry：重跑完整決策，不是重送最後一條 SQL

9.1 官方 SQLSTATE

• 40001：serialization_failure，應無條件重試完整 transaction。
• 40P01：deadlock_detected，通常也可重試完整 transaction。
• 23505／23P01：某些 concurrency protocol 下可能適合 retry，但也可能是永久性資料錯誤，必須依業務語意判斷。

9.2 正確 retry boundary

import asyncio
import random

from sqlalchemy.exc import DBAPIError


RETRYABLE_SQLSTATES = {"40001", "40P01"}


def sqlstate(exc: DBAPIError) -> str | None:
    return getattr(exc.orig, "sqlstate", None)


async def run_transaction(session_factory, operation, attempts: int = 5):
    for attempt in range(attempts):
        try:
            async with session_factory() as session:
                async with session.begin():
                    return await operation(session)
        except DBAPIError as exc:
            if sqlstate(exc) not in RETRYABLE_SQLSTATES or attempt + 1 == attempts:
                raise
            upper = min(0.5, 0.01 * (2**attempt))
            await asyncio.sleep(random.uniform(0.0, upper))

每個 attempt 使用新 transaction／session，讓 snapshot、reads 與 decisions 全部重建。

9.3 不可把外部 side effect 放在可重試 transaction 內

BEGIN
UPDATE orders
send_email()       ← database rollback 不會收回 email
COMMIT → 40001
retry → send_email again

改用 transactional outbox：transaction 只寫 business row 與 outbox row；commit 後由獨立 publisher 可靠送出並做 deduplication。

9.4 Retry storm

高 contention 下所有 transactions 立即 retry 會再次碰撞。使用 capped exponential backoff＋jitter、限制 attempts、觀測 retry rate；若持續衝突，應重新設計 contention point，而不是無限 retry。

10. Deadlock：等待圖形成 cycle

10.1 經典時間線

T1 locks account A       T2 locks account B
T1 waits for B           T2 waits for A

PostgreSQL 偵測 deadlock 後 abort 其中一個 transaction，回傳 40P01。沒有 deadlock 不代表沒有長等待；單方向 lock wait 仍可能卡到 statement／lock timeout。

10.2 預防方式

• 全系統以一致順序取得 locks，例如 account ID 小到大。
• transaction 保持短小。
• 不在持鎖期間呼叫網路。
• 一次取得確定集合：ORDER BY id FOR UPDATE，並理解 query plan／實際 lock 行為。
• 監控 pg_stat_activity、pg_locks、blocked duration。

10.3 lock_timeout、statement_timeout、deadlock detection

• lock_timeout：等待 lock 太久時取消 statement。
• statement_timeout：statement 執行總時間上限，包括等待。
• deadlock detector：偵測 cycle；不是一般 latency timeout。

這些錯誤是否 retry 仍取決於 transaction 語意與 outer deadline。

11. Constraints、atomic SQL、locks、version、Serializable 如何選

11.1 優先順序不是絕對，但可用以下思考

1. 能否用 database constraint 直接宣告 invariant？
2. 能否用單一 conditional statement 原子完成？
3. Conflict 低且適合 version check 嗎？
4. Conflict 高且 row 集合明確，應鎖定嗎？
5. Invariant 跨動態 predicate，Serializable 是否較清楚？

11.2 對照表

11.3 不要用「isolation 越高越安全」取代設計

Serializable 的安全來自明確 retry protocol；若 application 捕獲 40001 後回傳 partial success、在 transaction 中發送外部 side effect，或其他 writer 不遵守相同 protocol，仍可能出錯。

12. Vacuum：MVCC 的版本必須被回收

12.1 Dead tuples 從哪裡來

UPDATE/DELETE 留下不再被任何合法 snapshot 需要的旧 tuple versions。VACUUM 回收空間供 table 內重用，更新 visibility map，並協助防止 transaction ID wraparound。

12.2 為什麼不能立刻刪舊版本

仍在執行的舊 snapshot 可能需要看見它。只有當系統能證明沒有 relevant transaction 需要該 version，才能清理。

12.3 Long transaction 的代價

長時間 transaction 或 idle in transaction 可能保留舊 snapshot，阻礙 vacuum 移除 dead tuples，造成：

• table／index bloat。
• 更多 heap fetch 與 I/O。
• autovacuum 壓力。
• transaction ID age 風險。
• locks 持有過久。

12.4 Visibility map

VACUUM 維護 visibility map，標記 page 是否全部 tuple 對所有 active/future transactions 可見。Index-only scan 可利用它跳過 heap visibility check；頻繁更新或 vacuum 落後會降低 index-only scan 效益。

12.5 XID wraparound

PostgreSQL 內部 32-bit xid 會環繞。VACUUM freezing 讓舊 tuples 不再依賴普通 xid age 判斷。這不是可有可無的空間清理；忽略 wraparound 會威脅 database availability。

13. SQLAlchemy Async transaction 邊界

13.1 Session 不是可跨 tasks 任意共享的全域物件

每個 concurrent operation 應有清楚 session／transaction ownership。不要讓多個 tasks 同時操作同一 AsyncSession，也不要把 session 跨 request 保存。

13.2 Pessimistic lock

from sqlalchemy import select


async def reserve(session, sku: str) -> None:
    result = await session.execute(
        select(Inventory)
        .where(Inventory.sku == sku)
        .with_for_update()
    )
    inventory = result.scalar_one()
    if inventory.available < 1:
        raise OutOfStock(sku)
    inventory.available -= 1

呼叫端必須把它放在 session.begin() 中，且不要在 transaction 裡呼叫 HTTP。

13.3 Atomic update

from sqlalchemy import update


statement = (
    update(Inventory)
    .where(Inventory.sku == sku, Inventory.available >= 1)
    .values(available=Inventory.available - 1)
    .returning(Inventory.available)
)
remaining = (await session.execute(statement)).scalar_one_or_none()
if remaining is None:
    raise OutOfStock(sku)

13.4 Isolation level 要在 transaction 開始前確定

Isolation 的設定方式依 engine／driver integration。不要在 transaction 已執行 queries 後才試圖升級；建立專用 engine、connection execution options 或明確 transaction configuration，並用 integration test 查 SHOW transaction_isolation。

14. 可重現 Lab：不要只閱讀 anomaly 名稱

14.1 實驗 schema

CREATE TABLE inventory (
    sku text PRIMARY KEY,
    available integer NOT NULL CHECK (available >= 0),
    version bigint NOT NULL DEFAULT 0
);

CREATE TABLE doctors (
    name text PRIMARY KEY,
    on_call boolean NOT NULL
);

INSERT INTO inventory(sku, available) VALUES ('book-1', 1);
INSERT INTO doctors(name, on_call) VALUES ('alice', true), ('bob', true);

14.2 Lab A：Lost update

1. 兩個 connections 都 SELECT available=1。
2. 各自在 application 算成 0。
3. 依序 UPDATE SET available=0 並 commit。
4. 證明兩個 operation 都回報成功但只反映一次扣減。
5. 分別用 atomic UPDATE、FOR UPDATE、version column 修正。

14.3 Lab B：Write skew

1. 以 Repeatable Read 開兩個 transactions。
2. 兩邊都確認 on-call count=2。
3. 各把不同 doctor 設為 off-call。
4. 證明兩邊可 commit，invariant 被破壞。
5. 改用 Serializable，觀察其中一邊收到 40001。
6. 加入完整 retry，證明 retry 後重新讀取會拒絕第二次 off-call。

14.4 Lab C：Deadlock

1. T1 lock inventory A，T2 lock inventory B。
2. T1 再 lock B，T2 再 lock A。
3. 記錄 PostgreSQL 選擇哪個 transaction abort、SQLSTATE 與 elapsed time。
4. 改成固定排序取得 locks，證明 deadlock 消失。

14.5 Lab D：Long transaction 與 vacuum

1. 開啟長時間 Repeatable Read snapshot。
2. 另一 connection 大量 UPDATE rows。
3. 執行 VACUUM 並觀察 n_dead_tup、table size、oldest xmin。
4. 結束長 transaction 後再 vacuum，比較結果。

14.6 必須提交的證據

• 每個 anomaly 的雙 session SQL transcript。
• transaction timeline，標示 snapshot、read、write、wait、commit／abort。
• SQLSTATE 40001、40P01 的實際 log。
• 四種解法的 throughput、latency、retry／wait 指標。
• pytest integration tests 使用真實 PostgreSQL，不使用 SQLite 模擬 isolation。

15. Production observability 與 runbook

15.1 監控訊號

transaction_duration_seconds
transactions_total{outcome,sqlstate,isolation}
transaction_retries_total{sqlstate,operation}
lock_wait_duration_seconds{relation,mode}
deadlocks_total
idle_in_transaction_sessions
oldest_transaction_age
dead_tuples / autovacuum lag

避免把 raw SQL、user ID、order ID 放進 metric labels。Trace 可以記 route／operation name 與 sanitized statement fingerprint。

15.2 診斷查詢方向

• pg_stat_activity：active／idle in transaction、query start、wait event。
• pg_locks：granted、lock mode、relation／transaction ID、SIReadLock。
• pg_stat_database：deadlocks、transactions。
• pg_stat_user_tables：dead/live tuples、vacuum／analyze timestamps。
• pg_blocking_pids(pid)：blocking chain。

15.3 Incident 問題順序

1. 是 lock wait、CPU／I/O 慢，還是 connection pool queue？
2. 哪個 transaction 持 lock？它是否 idle in transaction？
3. 這是 row conflict、DDL table lock、deadlock 還是 SSI abort？
4. retry rate 是否正在放大 load？
5. 哪個 invariant／operation 造成 hotspot？能否改 atomic statement 或 partition contention？

16. 自我檢查

17. 官方來源

• PostgreSQL 18 Concurrency Control
• MVCC Introduction
• Transaction Isolation
• Explicit Locking
• Data Consistency Checks at the Application Level
• Serialization Failure Handling
• Transactions and Identifiers
• Routine Vacuuming
• The Statistics Collector

閱讀任何 isolation 文章時，最後都要回到 PostgreSQL 當前版本官方文件與實驗：它使用 statement 還是 transaction snapshot？同 row write conflict 如何處理？跨 row anomaly 是否會 abort？application 是否完整 retry？