SQL語法修改資料庫 - TRANSACTION （交易）

※ 為什麼我們需要 Transaction？

當我們談到 Transaction（交易）時，指的是一組不可分割的 SQL 操作。這些操作結果只能成功或失敗，以確保資料庫的一致性和完整性。Transaction 是資料庫操作中的一個「邏輯單位」，包含多個操作步驟。如果其中任何一個步驟失敗，整個 Transaction 會回滾（rollback），恢復到初始狀態。因此，我們可以將一組 SQL 操作包裝成一個完整的 Transaction，確保這些操作只能有兩種結果：成功或失敗。

讓我們先用一個情境來說明。Alice 和 Bob 在帳戶裡都有 1000 元，而 Alice 轉帳給 Bob 200 元，那麼一筆完整的「交易處理」會是這樣：

• 設定 Alice 的帳戶總金額為 800 元
• 設定 Bob 的帳戶總金額為 1200 元

set autocommit = 0;
START TRANSACTION;
UPDATE balances SET balance = 800 WHERE account = "Alice";
UPDATE balances SET balance = 1200 WHERE account = "Bob";
COMMIT;

交易的基本操作

• set autocommit = 0:指令關閉（原本每一行 SQL 完都會自動 commit）。
• START TRANSACTION: 宣告交易開始。
• COMMIT: 提交交易，將所有變更永久保存到資料庫。
• ROLLBACK: 回滾交易，取消所有未提交的變更。

※ ACID 特性：

針對資料庫來說，一個成功的交易必須要符合 ACID 特性。ACID 代表：原子性 (Atomicity)、一致性(Consistency)、隔離性 (Isolation)、永續性 (Durabilily)，他們可說是關聯式資料庫核心的特性。

1. 原子性（Atomicity）：交易中的所有操作要麼全部完成，要麼全部不完成。這意味著如果交易中的某一步失敗，整個交易會被回滾 (rollback)，資料庫會回到交易開始前的狀態。

例如 A 想要給 B 一百元，A 要從錢包拿出鈔票、B 要把鈔票收進錢包，若交易中途突然來了一陣風把鈔票吹走了，由於 B 沒有收到鈔票，所以交易失敗，此時就必須把鈔票放回 A 的錢包。

2. 一致性（Consistency）：交易（Transaction）確保資料庫從一個一致的狀態轉換到另一個一致的狀態。這意味著在交易開始前和結束後，資料庫的完整性約束不會被破壞。換句話說，交易完成前後，資料必須始終符合 schema 的規範，包括資料格式、資料限制和資料關聯。

例如 A 想要給 B 一百元，如果 A 給的是新台幣，完成交易時 B 不能莫名其妙的拿到美金。

3. 隔離性（Isolation）：確保交易之間是相互隔離的。一個交易的操作不會影響到其他交易，確保並發操作的正確性。這意味著同時執行的事務彼此之間不應互相影響，一個事務的中間狀態對其他事務是不可見的。

例如 A 跟 C 同時要給 B 一百元，在交易時大風吹走了其中一張鈔票。B 手上確實拿到一張一百元，但他不確定是從誰拿到的，而被吹走的鈔票又是屬於誰的。

4. 持久性（Durability）：一旦交易提交，對資料庫的改變是永久的，就算儲存資料的硬碟毀損也能重建。

例如 A 和 B 之間時常互相借錢，每次借錢都會寫下借據，並把借據拍下來上傳到像 Google Drive 這樣雲端硬碟備份。就算實體借據不見了，兩人都可以在雲端硬碟裡找到過去的紀錄。

※ 語法

假設我們要進行一個轉帳操作，從 Alice 的帳戶轉 200 元到 Bob 的帳戶：

SET autocommit = 0;  -- 關閉自動提交模式
START TRANSACTION;   -- 開始交易

UPDATE balances SET balance = balance - 200 WHERE account = 'Alice';
UPDATE balances SET balance = balance + 200 WHERE account = 'Bob';

COMMIT;  -- 提交交易

如果在事務過程中發生錯誤，我們可以使用 ROLLBACK 來回滾交易：

ROLLBACK;  -- 回滾交易

※ 什麼時候會用到 Transaction？

1. 金融交易: 如轉帳操作，確保資金從一個帳戶轉移到另一個帳戶的過程中不會出現錯誤。
2. 訂單處理: 在電子商務系統中，確保訂單的創建、庫存的更新和付款的處理是不可分割的。
3. 資料一致性: 在多步驟的資料更新過程中，確保所有步驟都成功完成，否則回滾到初始狀態。例如：
4. • 預訂系統，如機票預訂、酒店預訂等。
   • 資料庫更新。
   • 系統配置的文件修改。

※ Transaction高併發的使用情境：

在同一時間內，有大量的使用者或應用程式同時對資料庫進行讀取或寫入操作時，資料庫需要能夠有效地處理多個交易，以確保資料的一致性和完整性，同時保持高效能。

以下是一些常見的高併發情境：

1. 電子商務網站：在促銷活動期間，可能會有大量的使用者同時進行購物、下單和支付操作。
2. 社交媒體平台：大量使用者同時發佈、評論和點讚。
3. 金融交易系統：股票交易平台在市場開盤時，會有大量的交易請求。
4. 線上遊戲：大量玩家同時進行遊戲操作和數據同步。

※ 使用隔離級別（Isolation Level）來管理交易（Transaction）：

1. Read Uncommitted（未提交讀）:
2. • 特點: 允許交易讀取未提交的變更。
   • 優點: 提高並發性。
   • 缺點: 可能會讀取到髒數據（Dirty Read）。
   • 不建議在需要確保資料在任何情況下都是正確且一致的應用中使用（如金融交易系統、電子商務網站等）。
2. Read Committed（已提交讀）:
3. • 特點: 只允許交易讀取已提交的變更。
   • 優點: 避免髒讀（Dirty Read）。
   • 缺點: 可能會出現不可重複讀（Non-repeatable Read）。
   • 適合場景:電子商務網站、網路銀行系統、社交媒體平台、一般業務應用（報表生成、數據查詢）。
   • 適合大多數需要平衡資料一致性和性能的應用場景。
3. Repeatable Read（可重複讀）:
4. • 特點: 保證在同一交易中多次讀取同一資料時，結果是一致的。
   • 優點: 避免不可重複讀。
   • 缺點: 可能會出現幻讀（Phantom Read）。
   • 適合場景:金融交易系統、電子商務網站、一般業務應用（報表生成、數據查詢）。
4. Serializable（可序列化）:

• 特點: 完全隔離，交易按序執行。
• 優點: 避免所有並發問題（髒讀、不可重複讀、幻讀）。
• 缺點: 性能較低，並發性差。
• 提供了最高的一致性，適合需要確保數據絕對正確且可以容忍較低性能的應用場景。
• 適合場景:金融交易系統、會計系統、訂單處理系統、法律和合規系統。