概述#

資料庫系統必須允許對底層資料的並行存取，同時確保資料完整性不受損害。Transaction 是一組讀寫操作的集合，要麼全部成功、要麼全部失敗。1981 年 Jim Gray 最初定義了 Atomicity、Consistency、Durability 三個屬性，SQL-92 再加入 Isolation Levels，形成了著名的 ACID 四大特性。

理解 transaction 的運作方式至關重要：

• 有效的資料存取：追求高效能時不應犧牲資料完整性
• 高效的資料存取：降低 contention 可以縮短 transaction response time，進而提升 throughput

Atomicity#

Atomicity 將多個操作組合為全有或全無的工作單元。只有當所有個別操作都成功時，整個 transaction 才會成功；否則資料庫必須能 rollback 所有已執行的操作。

• 所有 statement 都直接作用於實際資料結構（tables、indexes、in-memory buffers），在 commit 時才實體化
• 發生 rollback 時，資料庫必須將所有已變更的資料還原為先前狀態
• 資料庫透過 write-write conflict 防護來避免兩個 transaction 同時寫入同一筆記錄

各資料庫的 rollback 機制：

• Oracle：使用 undo tablespace 儲存前版本資料於 undo segments
• SQL Server：使用 transaction log 記錄修改，透過掃描 log 還原 before image
• PostgreSQL：不使用獨立的 undo log，每個資料庫物件自帶版本歷史，rollback 只需切換版本；舊版本空間透過 VACUUMING 回收
• MySQL：undo log 儲存在 rollback segment，purge 程序負責清理已刪除的儲存空間

長時間執行的 transaction 會延遲各資料庫的清理程序（log truncation、VACUUM、purge），造成 undo log 持續膨脹，尤其在寫入密集的場景下影響更大。

Consistency#

Consistency 確保所有已提交的 transaction 使資料庫保持在合法狀態。關聯式資料庫 schema 透過以下規則確保一致性：

• Column types、column length、column nullability
• Foreign key constraints
• Unique key constraints
• Custom check constraints

若任何一個 constraint 被違反，整個 transaction 會被 rollback。雖然應用程式也應在 statement 之前驗證使用者輸入，但多個並行請求可能來自不同 web server，因此以資料庫作為主要整合點時，嚴格的 schema 更顯重要。

ACID 中的 Consistency 與 CAP Theorem 中的 Consistency 含義不同。ACID 的 consistency 是關於遵守 constraints；CAP 的 consistency 則是指 linearizability，屬於 isolation 的保證。

Isolation#

若資料庫只有單一使用者，便不存在資料衝突風險。但透過多個並行連線，資料庫系統可以處理更多流量。Isolation 的核心挑戰是：如何在不損害資料完整性的前提下交錯執行 transaction。

當所有並行 transaction 的執行結果等同於逐一序列執行時，該排程稱為 serializable。

Concurrency Control#

管理資料衝突的兩大策略：

Two-Phase Locking (2PL)#

• 透過 locking 來避免衝突，控制對共享資源的存取
• 鎖的基本類型：shared (read) lock（允許讀取但禁止寫入）和 exclusive (write) lock（禁止讀寫）
• 2PL 協議將 transaction 分為兩個階段：
  • Expanding phase：取得鎖，不釋放任何鎖
  • Shrinking phase：釋放所有鎖，不再取得新鎖
• 資料庫支援 Multiple Granularity Locking（row、page、table 等不同層級）；持有過多低層級鎖時可能觸發 lock escalation
• 使用 locking 容易產生 deadlock：兩個 transaction 互相等待對方釋放鎖。資料庫引擎會偵測 lock-wait cycle 並中止其中一個 transaction

Multi-Version Concurrency Control (MVCC)#

• 透過偵測衝突來提供更好的 concurrency，代價是放寬 serializability
• 核心承諾：readers 不阻塞 writers，writers 不阻塞 readers；唯一的 contention 來源是 writer 阻塞其他 writer
• 資料庫可重建記錄的先前版本，對並行 readers 隱藏未提交的變更

各資料庫的 MVCC 實作：

• Oracle：完全不使用 2PL，純 MVCC；基於 System Change Number (SCN) 提供 point-in-time snapshot
• SQL Server：預設使用 lock；可啟用 Read Committed Snapshot 或 Snapshot Isolation 來使用 MVCC
• PostgreSQL：純 MVCC 模型；每個 row 有 xmin/xmax 欄位追蹤版本；Vacuum cleaner 回收舊版本空間
• MySQL (InnoDB)：使用 MVCC；舊版本存於 rollback segment，purge thread 負責清理

Phenomena（資料異常現象）#

SQL-92 標準定義了三種現象，加上後續研究補充的四種：

• Dirty Write：兩個並行 transaction 可同時修改同一 row。所有資料庫都會防止此現象，因為它會破壞 atomicity

• Dirty Read：讀到其他 transaction 未提交的變更。僅 Read Uncommitted 允許此現象

• Non-repeatable Read：同一 transaction 內兩次讀取同一 row 得到不同結果（因其他 transaction 已修改並提交）

• Phantom Read：同一 transaction 內兩次相同 range query 得到不同結果集（因其他 transaction 新增了匹配記錄）

• Read Skew：涉及跨多個 table 的 constraint，transaction 在兩次 SELECT 之間看到不一致的資料狀態

• Write Skew：兩個 transaction 各自讀取相關記錄後分別更新不同 row，打破跨 table 的 constraint

• Lost Update：一個 transaction 讀取 row 後，另一 transaction 修改並提交了該 row，第一個 transaction 再更新時覆蓋了變更

選擇 isolation level 是在提高 concurrency與承認可能的 anomaly 之間做取捨。Isolation level 越低，locking 越少、應用越可擴展，但資料完整性的責任就從資料庫轉移到應用程式。

Isolation Levels#

SQL-92 標準定義的四個 isolation level：

大多數資料庫預設使用較低的 isolation level：

• Read Committed：Oracle、SQL Server、PostgreSQL
• Repeatable Read：MySQL

Read Uncommitted#

• 僅防止 dirty write，所有其他現象都可能發生
• Oracle 和 PostgreSQL 不支援此級別（會自動升級為 Read Committed）
• 適用於對 dirty read 可接受的報表查詢場景

Read Committed#

• 防止 dirty write 和 dirty read，但仍允許 non-repeatable read、phantom read、read/write skew 和 lost update
• 是跨資料庫行為最一致的 isolation level
• 各資料庫均使用 statement-level snapshot（每次查詢看到查詢開始時的資料快照）

Repeatable Read#

• 各資料庫的實作差異最大：
  • Oracle：不支援，JDBC driver 會拋出 exception
  • SQL Server：使用 shared lock 保持到 transaction 結束；仍允許 phantom read
  • PostgreSQL：使用 Snapshot Isolation，同時防止 phantom read；但仍允許 write skew 和 lost update
  • MySQL：transaction-level snapshot，防止 non-repeatable read；但仍允許 lost update 和 write skew

Serializable#

• 應該提供完全等同於序列執行的 transaction 排程
• 實務上各資料庫的實作仍有差異：
  • Oracle：MVCC Snapshot Isolation 實作，仍允許 write skew
  • SQL Server (2PL)：完全防止所有現象
  • SQL Server (MVCC)：類似 Oracle，允許 write skew
  • PostgreSQL：使用 Serializable Snapshot Isolation (SSI)，防止所有現象（但可能有 false positive 導致不必要的 transaction abort）
  • MySQL：在 Repeatable Read 之上加上 shared lock 保護每個 SELECT，透過 locking 防止 write skew

Durability#

Durability 確保所有已提交的 transaction 變更成為永久性的。即使系統崩潰，重啟後先前已處理的 transaction 仍必須有效。

• 資料庫透過 redo log（append-only 的循序資料結構）持久化已提交的變更
• Undo log 不適合用於 recoverability，因為過期的 undo segments 會被定期回收

各資料庫的 redo log 機制：

• Oracle：redo log 由多個 redo records 組成，Log Writer 將記憶體中的 redo records flush 到磁碟
• SQL Server：將 undo 和 redo 合併為單一 transaction log；支援 configurable durability（延遲 flush 以換取效能）
• PostgreSQL：使用 Write-Ahead Log (WAL)，commit 時 flush；支援 asynchronous flush
• MySQL：redo log entries 透過 mini transaction buffer flush 到 global redo buffer，commit 時寫入磁碟

除非業務需求允許資料遺失，否則應堅持使用同步 flush 機制。延遲 durability 保證只有在 redo log flush 確實成為效能瓶頸時才是合理選項。

Read-only Transactions#

JDBC Connection.setReadOnly(true) 可提示 driver 對即將進行的 read-only transaction 進行最佳化。此方法必須在 transaction 開始前呼叫。

• Oracle / SQL Server：read-only 狀態不會傳遞到資料庫引擎，仍允許修改操作
• PostgreSQL：在 read-only connection 上執行修改語句會拋出 exception；支援 deferrable serializable snapshot，適合長時間執行的唯讀 transaction
• MySQL：read-only connection 上的修改語句會拋出 exception；InnoDB 可跳過 transaction ID 生成來最佳化 read-only transaction

Read-only Transaction Routing#

在 Master-Slave replication 架構中，可將 read-write 流量導向 Master、read-only 流量導向 Slave：

• 各資料庫提供不同程度的 routing 支援（Oracle ADG、SQL Server Availability Group、PostgreSQL load balancing、MySQL ReplicationDriver）
• 即使 JDBC driver 不支援，應用程式也可透過多個 DataSource 實例自行實作 routing

Transaction Boundaries#

每個 database statement 都在 transaction 內執行。預設的 auto-commit 模式將每個 statement 視為獨立 transaction，這對於多 statement 的工作單元是不適用的。

應盡量避免 auto-commit。即使是單一 statement 的 transaction，明確標示 transaction 邊界也是良好實踐。Transaction 失敗時必須立即 rollback，讓資料庫盡快釋放鎖和還原未提交的變更。

Distributed Transactions#

• Local transaction 使用單一 resource manager；global transaction 操作多個異質 resource manager
• JTA (Java Transaction API) 驅動 Java EE 的 distributed transaction 需求，遵循 X/Open XA 架構

Two-Phase Commit (2PC)#

分散式 transaction 透過兩階段提交協議協調：

1. Prepare phase：所有 resource manager 準備 transaction 以進行 commit
2. Commit phase：僅當所有 resource manager 都成功確認 prepare 後，transaction manager 才進行 commit

若有 resource 在 prepare phase 失敗，transaction manager 會 rollback 所有參與者。若在 commit phase 失敗，transaction manager 必須在背景重試直到成功。

當只有一個 resource adapter 時，可使用 one-phase commit (1PC) optimization 跳過 prepare phase。

Declarative Transactions#

• 使用 annotation（如 @Transactional）宣告式地定義 transaction 邊界，將 transaction 管理邏輯與資料存取邏輯解耦
• Java EE 和 Spring 都支援多種 propagation 策略（REQUIRED、REQUIRES_NEW、SUPPORTS、MANDATORY、NESTED 等）
• Exception handling：預設在 RuntimeException 時 rollback，checked exception 時 commit
• Read-only 宣告：Java EE 不支援；Spring 透過 @Transactional(readOnly = true) 支援
• Isolation level 設定：Java EE 不支援 transaction 級別的 isolation 設定；Spring 在使用 JpaTransactionManager 時支援

Application-level Transactions#

業務流程可能跨越多個 physical database transaction（例如多個 web request，包含 user think time）。此時資料庫的 ACID 保證不再足夠，需要應用層級的 concurrency control。

• 跨多個 web request 持有資料庫 transaction 是不可行的（鎖會在 user think time 期間被持有，損害 scalability）

• 保留跨請求的狀態（conversational context）可提供 application-level repeatable reads，但仍不足以防止 lost update

Pessimistic and Optimistic Locking#

Pessimistic Locking#

• 假設衝突一定會發生，因此透過顯式取得鎖來預防
• 適合用在 long conversation 的最後一個 database transaction，確保取得鎖後不會有其他 transaction 干擾
• 可防止 non-repeatable read、lost update、read skew 和 write skew

Optimistic Locking#

• 實際上不涉及任何 locking，更準確的名稱是 optimistic concurrency control
• 假設衝突不太可能發生，透過偵測 anomaly 並中止違反 invariant 的 transaction
• 每個 row 必須有關聯的 version，每次修改時 version 遞增
• 每個 UPDATE/DELETE statement 使用先前載入的 version 作為條件，若 version 不匹配表示記錄已過期
• ORM 工具（如 Hibernate）可自動整合 row version 到修改語句中，在 version mismatch 時 rollback transaction

即使是最低的 isolation level 也能防止 write-write conflict，因此 row version 可以被單調遞增，應用程式藉此偵測 stale record 並中止 transaction 以防止 lost update。