MySQL: Transaction

Table of Content

• 為什麼需要 Transaction
• ACID
  • 高併發引發的問題
• 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 前後資料完整
3. I (Isolate 隔離性)：多個 transaction 之間使用相同資料不會互相干擾
4. D (Durability 持久性)： 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 un-committed: 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