🎯 什麼情境該想到我
當多個請求同時讀寫同一批資料、出現「錯亂/覆蓋/看到中間狀態」的併發問題時。
⚙️ 怎麼用（依你要防的異常挑隔離等級）
- 讀已提交（Read Committed）：防髒讀、髒寫。最低堪用等級。
- 快照隔離 / 可重複讀（Snapshot Isolation）：整個交易看同一份快照（MVCC），防不可重複讀。
- 可序列化（Serializable）：最強，行為等同交易一個個依序執行，防所有異常（含寫偏斜 write skew / 幻讀）。實作：真序列化、2PL、或 SSI。
步驟：先想清楚你要防哪種併發異常，再選剛好夠的等級（越強越慢）。
★ 四種異常與對應的防線（背這張表）
| 異常 | 長什麼樣 | Read Committed | Snapshot / RR | Serializable |
|---|---|---|---|---|
| 髒讀 | 讀到別人未提交的資料 | ✅ 防 | ✅ | ✅ |
| 不可重複讀 | 同一交易內讀兩次，值不同 | ❌ | ✅ 防 | ✅ |
| 幻讀 | 同一查詢，第二次多出幾列 | ❌ | ⚠️ 看實作 | ✅ 防 |
| ★ 寫偏斜 write skew | 兩個交易各自讀了對方要改的東西，各自的決定都合法，合起來違反不變式 | ❌ | ❌ 防不了 | ✅ 防 |
★ 寫偏斜：最陰險的一種（快照隔離擋不住）
規則：任何時刻至少要有一位醫生值班。目前 Alice 和 Bob 都值班。
交易 A（Alice 請假） 交易 B（Bob 請假）
SELECT count(*) → 2 ✅ SELECT count(*) → 2 ✅
「還有 2 個，我可以走」 「還有 2 個，我可以走」
UPDATE 自己 off_duty UPDATE 自己 off_duty
COMMIT COMMIT
★ 結果：0 個醫生值班
★ 兩個交易改的是「不同的列」，所以沒有寫衝突——
快照隔離只偵測「寫同一列」，偵測不到「讀了一個會被對方改掉的集合」。
三種修法：
| 方法 | 做法 |
|---|---|
| ① 升到 Serializable | PostgreSQL 的 SSI 會偵測到並讓其中一個失敗（★ 必須寫重試邏輯） |
| ② 明確加鎖 | SELECT ... FOR UPDATE 把讀到的列鎖住（★ 但鎖不住「還不存在的列」） |
| ③ ★ 實體化衝突 | 建一張代表「衝突對象」的表（例如每個班次一列），把「集合的競爭」變成「單列的競爭」 |
★ 實務上最重要的一件事：各家的命名不一致
| 標準 SQL | PostgreSQL 實際行為 | MySQL (InnoDB) |
|---|---|---|
READ COMMITTED | ✅ 一致（預設） | 一致 |
REPEATABLE READ | ★ 實際上是 Snapshot Isolation（比標準強，防幻讀） | ★ 預設；用 gap lock 防幻讀，但仍有寫偏斜 |
SERIALIZABLE | ★ SSI（樂觀，衝突時 abort） | 2PL（悲觀，會鎖） |
READ UNCOMMITTED | ★ 等同 READ COMMITTED（PG 根本不實作髒讀） | 真的允許髒讀 |
★ 「我用的是 REPEATABLE READ」這句話在 PG 和 MySQL 是不同的意思。
換資料庫時這是最容易出事的地方——一定要查你那一家的文件，不要憑標準 SQL 的名字推論。
★ 樂觀 vs 悲觀：怎麼選
-- 悲觀：先鎖住（適合衝突頻繁、交易短）
BEGIN;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE; -- ★ 其他人要等
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;
-- ★ 樂觀：不鎖，靠版本號偵測（適合衝突罕見、交易長）
UPDATE accounts SET balance = ?, version = version + 1
WHERE id = 1 AND version = ?; -- ★ 一定要檢查 rows affected！
-- rows affected = 0 → 被別人改過了 → 重試| 悲觀鎖 | ★ 樂觀鎖 | |
|---|---|---|
| 適合 | 衝突頻繁、交易短 | 衝突罕見、交易長、跨請求 |
| 代價 | 阻塞、死鎖風險 | 要寫重試邏輯 |
| 跨 HTTP 請求 | ❌ 不可行（不能握著交易等使用者） | ★ 唯一可行的做法 |
→ 完整說明見 樂觀離線鎖、工具-狀態變數的設計訣竅。
★ 單一 UPDATE 通常就夠了
-- ❌ 讀-改-寫：有競態
SELECT balance FROM accounts WHERE id=1; -- 100
UPDATE accounts SET balance = 90 WHERE id=1;
-- ✅ 讓資料庫做運算：原子的，不需要任何隔離等級的幫忙
UPDATE accounts SET balance = balance - 10 WHERE id=1 AND balance >= 10;★ 能用一句原子的 UPDATE 解決，就不要用交易 + 鎖。 這是最便宜也最可靠的做法。
🧪 我實際套用的紀錄
- 2026-07-15：（待填）
⚠️ 注意 / 什麼時候不適用
- ★ 隔離等級只管單一資料庫內部。跨服務、跨資料庫的一致性是完全不同的問題（見 工具-分散式事務的取捨）。
- ★ Serializable 一定要寫重試。PG 的 SSI 會回
40001 serialization_failure——
沒有重試邏輯就等於把錯誤丟給使用者。 重試要有上限與退避。 - ★ 樂觀鎖一定要檢查 rows affected。
UPDATE ... WHERE version = ?影響 0 列代表衝突，
不檢查就是靜默地丟失更新——這是很難發現的資料錯誤。 - 長交易是萬惡之源：持鎖久、阻塞別人、PG 裡會讓 vacuum 無法清理舊版本（表膨脹）。
交易裡不要有網路呼叫、不要等使用者輸入。 - 死鎖是正常現象不是 bug。資料庫會偵測並殺掉其中一個——應用端必須能重試。
降低機率的做法：所有交易都用同樣的順序存取資源。 - ORM 會隱藏交易邊界。要知道你的框架什麼時候開/關交易，以及 lazy loading 會不會在交易外觸發查詢。
- 隔離等級越強越慢，但**「慢」的形態不同**：悲觀鎖是阻塞，SSI 是 abort + 重試。要量的是 goodput 不是單次延遲。
🔗 相關工具
- 工具-分散式事務的取捨 —— 跨服務時怎麼辦
- 樂觀離線鎖、悲觀離線鎖 —— 跨請求的並行控制
- 工具-預寫日誌與崩潰復原 —— 交易的持久性怎麼實現
- 工具-儲存引擎B-Tree與LSM-Tree —— MVCC 與儲存層的關係
- 工具-狀態變數的設計訣竅 —— 用原子 UPDATE 取代讀-改-寫
- 狀態模式 —— 狀態轉換的並行安全
- 工具-Transactional-Outbox-Pattern —— 交易與副作用的原子性
- 設計資料密集型應用