2024-08-13|閱讀時間 ‧ 約 6 分鐘

SQL語法修改資料庫 - TRANSACTION (交易)

    ※ 為什麼我們需要 Transaction?

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

    raw-image


    讓我們先用一個情境來說明。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 不能莫名其妙的拿到美金。

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

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

    1. 持久性(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. 資料一致性: 在多步驟的資料更新過程中,確保所有步驟都成功完成,否則回滾到初始狀態。例如:
      • 預訂系統,如機票預訂、酒店預訂等。
      • 資料庫更新。
      • 系統配置的文件修改。

    ※ Transaction高併發的使用情境:

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

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

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

    ※ 使用隔離級別(Isolation Level)來管理交易(Transaction):

    1. Read Uncommitted(未提交讀):
      • 特點: 允許交易讀取未提交的變更。
      • 優點: 提高並發性。
      • 缺點: 可能會讀取到髒數據(Dirty Read)。
      • 不建議在需要確保資料在任何情況下都是正確且一致的應用中使用(如金融交易系統、電子商務網站等)。
    2. Read Committed(已提交讀):
      • 特點: 只允許交易讀取已提交的變更。
      • 優點: 避免髒讀(Dirty Read)。
      • 缺點: 可能會出現不可重複讀(Non-repeatable Read)。
      • 適合場景:電子商務網站、網路銀行系統、社交媒體平台、一般業務應用(報表生成、數據查詢)。
      • 適合大多數需要平衡資料一致性和性能的應用場景。
    3. Repeatable Read(可重複讀):
      • 特點: 保證在同一交易中多次讀取同一資料時,結果是一致的。
      • 優點: 避免不可重複讀。
      • 缺點: 可能會出現幻讀(Phantom Read)。
      • 適合場景:金融交易系統、電子商務網站、一般業務應用(報表生成、數據查詢)。
    4. Serializable(可序列化):
    • 特點: 完全隔離,交易按序執行。
    • 優點: 避免所有並發問題(髒讀、不可重複讀、幻讀)。
    • 缺點: 性能較低,並發性差。
    • 提供了最高的一致性,適合需要確保數據絕對正確且可以容忍較低性能的應用場景。
    • 適合場景:金融交易系統、會計系統、訂單處理系統、法律和合規系統。



    分享至
    成為作者繼續創作的動力吧!
    © 2024 vocus All rights reserved.