ACID 與交易隔離等級
資料庫交易的四個保證:原子性(Atomicity,全有或全無)、一致性(Consistency,維持約束)、隔離性(Isolation,互不干擾)、持久性(Durability,寫入不會丟)。隔離等級越低越快,但會出現髒讀、遺失更新、幻讀這些異常。
性質
異常
隔離等級
1/7
T1
- 讀 x
- 寫 x = x + 10
- 提交
T2
- 讀 x
- 寫 x = x + 20
- 提交
已提交
| x | 100 |
兩筆交易都讀 x,各自加上 10 和 20 再寫回。正確結果是 130。
所有寫入者都登記未提交值;是否看得到,取決於讀取者的隔離等級。此示範讓兩筆交易使用所選等級。模型呈現快照與樂觀提交檢查,未模擬資料列鎖等待、死結或完整 SSI(Serializable Snapshot Isolation)衝突圖。
亮起來的是這一步執行的程式碼
type Level = "ru" | "rc" | "rr" | "serializable";type Version = { value: number; ts: number }; class Store { versions = new Map<string, Version[]>(); dirty = new Map<string, Array<{ value: number; tx: number }>>(); // uncommitted writes, latest last wal: string[] = []; clock = 0; nextTx = 1; committed(key: string, at = this.clock): number | undefined { const list = this.versions.get(key) ?? []; for (let i = list.length - 1; i >= 0; i--) if (list[i].ts <= at) return list[i].value; return undefined; } changedSince(key: string, ts: number): boolean { return (this.versions.get(key) ?? []).some((v) => v.ts > ts); } keys(): string[] { return [...new Set([...this.versions.keys(), ...this.dirty.keys()])].sort(); }} class Tx { snapshot: number; id: number; writes = new Map<string, number>(); reads = new Set<string>(); scans = new Set<string>(); constructor(private db: Store, private level: Level) { this.snapshot = db.clock; this.id = db.nextTx++; } read(key: string): number | undefined { if (this.writes.has(key)) return this.writes.get(key); this.reads.add(key); if (this.level === "ru" && this.db.dirty.has(key)) { return this.db.dirty.get(key)!.at(-1)!.value; } const fromSnapshot = this.level === "rr" || this.level === "serializable"; return this.db.committed(key, fromSnapshot ? this.snapshot : this.db.clock); } scan(prefix: string, min: number): number { this.scans.add(prefix); let count = 0; for (const key of this.db.keys()) { if (key.startsWith(prefix) && (this.read(key) ?? -Infinity) >= min) count++; } return count; } write(key: string, value: number): void { this.writes.set(key, value); // Visibility depends on the reader's level, not the writer's. const pending = (this.db.dirty.get(key) ?? []).filter(e => e.tx !== this.id); pending.push({ value, tx: this.id }); this.db.dirty.set(key, pending); } commit(): boolean { if (this.level === "rr" || this.level === "serializable") { for (const key of this.writes.keys()) { if (this.db.changedSince(key, this.snapshot)) return this.abort(); } } if (this.level === "serializable") { for (const key of this.reads) { if (this.db.changedSince(key, this.snapshot)) return this.abort(); } for (const prefix of this.scans) for (const key of this.db.keys()) { if (key.startsWith(prefix) && this.db.changedSince(key, this.snapshot)) return this.abort(); } } this.db.wal.push(JSON.stringify([...this.writes])); // A real engine fsyncs the log here, before acknowledging. const ts = ++this.db.clock; for (const [key, value] of this.writes) { const list = this.db.versions.get(key) ?? []; list.push({ value, ts }); this.db.versions.set(key, list); } this.clearDirty(); return true; } abort(): false { this.writes.clear(); this.clearDirty(); return false; } private clearDirty(): void { for (const [key, entries] of this.db.dirty) { const pending = entries.filter(e => e.tx !== this.id); if (pending.length) this.db.dirty.set(key, pending); else this.db.dirty.delete(key); } }}模型假設與範圍
- 這是可重現的教學模型;延遲、容量、故障率與工作負載是設定或樣本,不能直接當作正式系統的效能承諾。
- 小型多版本資料庫展示隔離現象;snapshot isolation 並不自動等於 serializable。沒有完整 SQL、predicate lock、WAL 或磁碟 crash recovery。
什麼時候用
- 錢、庫存、名額這類「不能多也不能少」的資料:至少用 READ COMMITTED,涉及「先讀再改」就要 REPEATABLE READ 或 SERIALIZABLE,或明確加鎖。
- 規則跨好幾筆資料(「至少一人值班」「總額不超過上限」)時,只有 SERIALIZABLE 或
SELECT … FOR UPDATE擋得住寫入偏斜。 - 報表、統計這種允許一點誤差的唯讀查詢,用較低的等級換效能。
和其他主題的關係
- 被這些用到
- MVCC、鎖等待與死鎖
- 延伸閱讀
- SQL 與 NoSQL分散式交易
時間與空間複雜度(Big O)
| 操作 | 平均 | 最差 |
|---|---|---|
| 讀一個鍵 v 是這個鍵保留的版本數;真正的資料庫用索引找版本,並定期清掉舊版本(VACUUM) | O(v) | O(v) |
| 提交(含驗證) r、w 是讀寫過的鍵;這個簡化版驗證範圍查詢時要看過全部 n 個鍵 | O(r + w) | O(r + w + n) |
| 範圍查詢 這裡逐一比對;有 B-tree 索引時是 O(log n + 結果數) | O(n) | O(n) |
空間:O(n · v),每個鍵保留舊版本,讓快照讀得到交易開始時的值
和其他做法比
| 髒讀 | 不可重複讀 | 遺失更新 | 幻讀 | 寫入偏斜 | 被迫中止 | |
|---|---|---|---|---|---|---|
| READ UNCOMMITTED | 67 / 200 | 64 / 200 | 140 / 200 | 64 / 200 | 133 / 200 | 0 / 1000 |
| READ COMMITTED | 不會發生 | 46 / 200 | 184 / 200 | 46 / 200 | 180 / 200 | 0 / 1000 |
| REPEATABLE READ(快照) | 不會發生 | 不會發生 | 不會發生 | 不會發生 | 193 / 200 | 184 / 1000 |
| SERIALIZABLE | 不會發生 | 不會發生 | 不會發生 | 不會發生 | 不會發生 | 597 / 1000 |
每個情境各跑 200 種隨機交錯順序(每筆交易內部的順序不變)。越高的隔離等級擋下越多異常,代價是更多交易在提交時被中止、要由應用程式重試。這裡的 SERIALIZABLE 用樂觀驗證模擬,連唯讀交易也會中止;PostgreSQL 的 SSI 更聰明,用鎖實作的資料庫則改成等待而不是中止。REPEATABLE READ 以快照隔離模擬,這正是 PostgreSQL 的做法——它擋得住幻讀,卻擋不住寫入偏斜。
| 等待(約 330 筆/秒) | 等待(約 2,000 筆/秒) | 當機平均丟失 | |
|---|---|---|---|
| 先回覆,之後再 fsync | 0.0 ms | 0.0 ms | 92.2 |
| 每筆 fsync 完才回覆 | 4.2 ms | 無限增加 | 0.0 |
| 分組提交 | 4.5 ms | 4.5 ms | 0.0 |
fsync 一次 2 ms,所以逐筆 fsync 每秒最多 500 筆;分組提交讓一次 fsync 帶走一整批,在高負載下又快又不丟資料。
真實世界裡的它
- PostgreSQL 預設 READ COMMITTED,REPEATABLE READ 是快照隔離,SERIALIZABLE 用 SSI。
- MySQL InnoDB 預設 REPEATABLE READ,用 next-key lock 擋住大部分幻讀。
- PostgreSQL 的
synchronous_commit、MySQL 的innodb_flush_log_at_trx_commit:決定提交回覆前要不要等日誌寫到磁碟。
取捨與陷阱
- 遺失更新最常見:先
SELECT再UPDATE … SET x = old + 1(old 是剛讀到的值)。改成UPDATE … SET x = x + 1,或用SELECT … FOR UPDATE。 - 提高隔離等級後,交易會因為衝突而失敗:程式要準備好重試,不然只是把資料錯誤換成了使用者看到的錯誤。
- 同一個隔離等級名稱,在不同資料庫的實際行為不一樣:要看那個資料庫的文件,不要只看 SQL 標準。
- ACID 只管單一資料庫;跨服務的操作要看分散式交易。