Transaction

by Aaron • 10/23/2022, 8:51:42 AM

Table of Content

• 為什麼需要 Transaction
• ACID
  • 高併發引發的問題
• transaction 的隔離級別
• 如何使用 transaction

Transaction 是可以 commit 或 rollback 的原子工作單位, 當在一個 tramsaction 內對資料庫進行多次修改, 在提交 transaction 時所有更改都成功或者都失敗

為什麼需要 Transaction

EX: Ａ轉帳 200 給Ｂ需要

1. 讀取Ａ餘額
2. 扣掉Ａ餘額 200 元
3. 儲存A餘額
4. 讀取Ｂ餘額
5. Ｂ餘額加 200 元
6. 儲存B餘額

一個完整的轉帳過程需要 6 個操作全部成功，其中有一個操作失敗就 rollback。 例如第三步失敗後，Ａ的餘額會回到原本得餘額不然就會白扣Ａ200 元

ACID

1. A (Atomicity 原子性): 一個 transaction 不是全部成功就是全部都失敗
2. C (Consistency 一致性)：資料庫始終保持一致的狀態, 在 transaction commit / rollback 後, 以及 transaction 內查詢會看到所有舊值或所有新值，而不是新舊值的混合
3. I (Isolate 隔離性)：多個 transaction 之間使用相同資料不會互相干擾, 這種隔離是透過鎖定機制實現的。當有經驗的使用者可以確定事務確實不會相互幹擾時， 他們可以調整隔離級別，以減少保護來提高效能和 並發性
4. D (Durability 持久性)： transaction 結束後, 該 transaction 所做的修改不會受到電源故障, 系統故障等等問題影響。

高併發引發的問題

1. dirty read, 2. non-repeatable, 3. Phantom read.

• dirty read: 一個 transaction 讀到另一個 transaction 未 commit 的修改後的資料

      begin                                       commit
        ↓         data=2                            ↓
t1     ----------------------------------------------->
            (read)↓ ↑       (set data=3)↓ ↑                 
data=2 ----------------------------------------------->
                                        (read) ↑ ↓ <--------t1 not commit yet
t2     ----------------------------------------------->
        ↑                                    data=3
      begin

• non-repeatable: 一個 transaction 中連續多次讀取相同 attribute 得到不同值

      begin                                commit
        ↓         data=2                     ↓
t1     ----------------------------------------------->
            (read)↓ ↑       (set data=3)↓ ↑                 
data=2 ----------------------------------------------->
           (read) ↑ ↓                       (read) ↑ ↓ <--------t1 committed
t2     ----------------------------------------------->
        ↑         data=2 <--- different value ---> data=3
      begin

• phantom read: 一個 transaction 多讀取某個範圍內的記錄，卻得到不同結果

              begin                                commit
                ↓         count(d)=2                 ↓
t1         ---------------------------------------------------------->
                        (read)↓ ↑       (insert)↓ ↑                 
count(d)=2 ---------------------------------------------------------->
                        (read) ↑ ↓                       (read) ↑ ↓ <--------t1 committed
t2         ---------------------------------------------------------->
                ↑        count(d)=2 <--- different value ---> count(d)=3
              begin

transaction 的隔離級別

1. read uncommitted: transaction 未提交前，他做得更動可以被其他 transaction 看到
2. read committed: transaction 提交後，變更才能被其他 transaction 看到
3. repeatable read (default): transaction 看到的資料跟 transaction 一開始看到的資料一樣
4. serializable: 對記錄加 讀寫lock，後訪問得 transaction 需等先訪問的 transaction commit才能執行

	dirty read	non-repeatable	phantom read
read-uncommited	V	V	V
read-commited	X	V	V
repeatable read	X	X	V
serializable	X	X	X

如何使用 transaction

使用 transaction 的方式有分兩種, 隱示和顯示

• 顯示開啟: 可以用 BEGIN, 或 START TRANSACTION 顯示開啟一個 transaction

  BEGIN; # START TRANSACTION
  # DML operation
  ...
  
  # SAVEPOINT name;
  SAVEPOINT first 
  # DML operation
  ...
  
  # ROLLBACK to name; 可以只 rollback 到特定 savepoint
  ROLLBACK to first;
  
  # ROLLBACK / COMMIT 選擇 rollback 全部或者提交 change
  COMMIT;
  ...
  

• 隱示開啟: 系統變數有一個 autocommit

SHOW VARIABLE LIKE 'autocommit';

當 autocommit = 1 的時候在執行以下動作會自動 commit 前一個 transaction

• DDL
• BEGIN / START TRANSACTION
• LOCK / UNLOCK TABLE 等等