MySQL-事務

目錄

• MySQL-事務介紹
• MySQL事務
  • ACID
  • 事務的隔離級別
  • 不同的隔離級別可能引發不同的一致性問題
  • 併發事務下可能導致的資料不一致
    • 1. 髒讀（Dirty Read）> 讀取到中間值
    • 2. 不可重複讀（UnRepeatable Read）> 更新場景，資料不一致
    • 3. 幻讀（Phantom Read）>插入or刪除場景，資料不一致
    • 4. 更新丟失（lost update）>幻讀中資料更新丟失

MySQL-事務介紹

標籤（空格分隔）： Mysql 事務

---

---

MySQL事務

ACID

1.原子性（Atomcity）
一個事務的最小單元，要麼全部成功要麼全部失敗，執行的過程中是不能被打斷或者執行其他操作的。
2.一致性（Consistent）
事務開始前和結束後，資料庫的完整性約束沒有被破壞。比如A向B轉賬，不可能A扣了錢，B卻沒收到，事務開始前A+B=500，事務結束後A+B不可能!=500。
3.隔離性(Isolation)
隔離性表示各個事務之間不會互相影響，資料庫一般會提供多種級別的隔離。實際上多個事務是併發執行的，但是他們之間不會互相影響。
4.永續性（Durability)
事務提交後，事務對資料庫的所有更新將被儲存到資料庫，不能回滾。

事務的隔離級別

事務分為以下4個級別

• Read UnCommitted(可以讀取未提交資料)

• Read Committed(只能讀到已提交資料)

• Read Repeatable(一個事務中重複讀取，資料保持一致性)

• Serializable(序列執行，不會造成不一致問題，但會影響併發)

不同的隔離級別可能引發不同的一致性問題

隔離級別	髒讀	不可重複讀	幻讀
Read UnCommitted	Y	Y	Y
Read Committed	N	Y	Y
Read Repeatable	N	N	Y
serializable	N	N	N

併發事務下可能導致的資料不一致

1. 髒讀（Dirty Read）> 讀取到中間值

事務A開啟事務，做更新運算元據由300更新為400，並未commit，此時事務B會讀取到A更新但未提交的值400。此時A事務 rollback，但是B事務已經讀取到A更新的值，造成髒讀。

事例：

• A連線

#設定當前會話事務隔離級別為讀未提交
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
#開始執行事務
START TRANSACTION;
SELECT user_id,balance FROM tb_user_account;
+---------+---------+
| user_id | balance |
+---------+---------+
|      80 |       0 |
|      86 |     300 |
+---------+---------+
#②在事務B第一次查詢之後進行更新操作
UPDATE tb_user_account SET balance = 400 WHERE user_id = 86;
#④發生異常進行回滾
ROLLBACK;

• B連線

#查詢當前連線事務級別
SELECT @@tx_isolation;
#設定當前會話事務隔離級別為讀未提交
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
#開始執行事務
START TRANSACTION;
#①在事務A更新之前執行
SELECT * FROM tb_user_account;
+---------+---------+
| user_id | balance |
+---------+---------+
|      80 |       0 |
|      86 |     300 |
+---------+---------+
#③在事務A更新之後執行，此時讀取到了未提交的資料400
 SELECT user_id,balance FROM tb_user_account;
+---------+---------+
| user_id | balance |
+---------+---------+
|      80 |       0 |
|      86 |     400 |
+---------+---------+
#⑤在事務A回滾之後執行，兩次讀取到的資料不一致，發生的了髒讀
 SELECT user_id,balance FROM tb_user_account;
+---------+---------+
| user_id | balance |
+---------+---------+
|      80 |       0 |
|      86 |     300 |
+---------+---------+

A=>operation: 事務A 更新為400
BS1=>operation: 事務B 第一次查詢到300
BS2=>operation: 事務B 第二次查詢到400（中間值）髒讀
ROLL=>operation: 事務A 回滾
BS3=>operation: 事務B 第三次查詢到300 （資料不一致）

BS1->A->BS2->ROLL->BS3

2. 不可重複讀（UnRepeatable Read）> 更新場景，資料不一致

事務A開啟事務，做更新運算元據由300更新為400，並未commit，此時事務B查詢值為300，解決了髒讀問題。此時A提交事務，事務B再次查詢值為400,兩次查詢資料不一致。（不可重複讀）
事例：

• A連線

#設定當前會話事務隔離級別為讀已提交
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
#開始執行事務
START TRANSACTION;
SELECT user_id,balance FROM tb_user_account;
+---------+---------+
| user_id | balance |
+---------+---------+
|      80 |       0 |
|      86 |     300 |
+---------+---------+
#②在事務B第一次查詢之後進行更新操作
UPDATE tb_user_account SET balance = 400 WHERE user_id = 86;
#④提交資料
COMMIT;

• B連線

#查詢當前連線事務級別
SELECT @@tx_isolation;
#設定當前會話事務隔離級別為讀未提交
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
#開始執行事務
START TRANSACTION;
#①在事務A提交之前執行
SELECT * FROM tb_user_account;
+---------+---------+
| user_id | balance |
+---------+---------+
|      80 |       0 |
|      86 |     300 |
+---------+---------+
#③在事務A提交之後執行，此時讀取到的值還是300，解決了髒讀問題。
 SELECT user_id,balance FROM tb_user_account;
+---------+---------+
| user_id | balance |
+---------+---------+
|      80 |       0 |
|      86 |     300 |
+---------+---------+
#⑤在事務A提交之後執行，兩次讀取到的資料不一致，發生的了不可重複讀。
 SELECT user_id,balance FROM tb_user_account;
+---------+---------+
| user_id | balance |
+---------+---------+
|      80 |       0 |
|      86 |     400 |
+---------+---------+

A=>operation: 事務A 更新為400
BS1=>operation: 事務B 第一次查詢到300
BS2=>operation: 事務B 第二次查詢到300，保證了不發生髒讀
ROLL=>operation: 事務A 提交
BS3=>operation: 事務B 第三次查詢到400 （不可重複讀，資料不一致）

BS1->A->BS2->ROLL->BS3

3. 幻讀（Phantom Read）>插入or刪除場景，資料不一致

事務A開啟事務，做查詢大於0的資料，並未commit，此時B連線插入一條大於0的資料。A再次查詢大於0的資料，但是並查詢到B插入的資料，造成（幻讀）。
事例：

• A連線

#設定當前會話事務隔離級別為可重複讀
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
#開始執行事務
START TRANSACTION;
#①查詢大於0的資料
SELECT count(1) FROM tb_user_account WHERE balance > 0;
+----------+
| count(1) |
+----------+
|        1 |
+----------+
#③查詢大於0的資料,查到新增資料(造成幻讀，BUT MySQL，MVVC解決了此問題）
SELECT count(1) FROM tb_user_account WHERE balance > 0;
+----------+
| count(1) |
+----------+
|        2 |
+----------+

• B連線

#②在事務A提交之前執行
INSERT INTO tb_user_account(balance) VALUES(100);

A=>operation: 事務A  查詢大於0的資料
BS1=>operation: 事務B 插入一條大於0的資料
AS2=>operation: 事務A 查詢大於0的資料,查到了新增資料。（幻讀，資料不一致）

A->BS1->AS2

4. 更新丟失（lost update）>幻讀中資料更新丟失

使用 READ COMMITTED隔離級別，事務A開啟事務，做更新運算元據增加100，並未commit，此時事務B也做更新運算元據增加100。此時B先提交事務，之後A再提交事務，會造成B事務更新丟失。（MySQL的InnoDB使用了MVCC，在提交B時會鎖住行資料，避免此種問題）。
事例：

• A連線

#設定當前會話事務隔離級別為讀已提交
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
#開始執行事務
START TRANSACTION;
SELECT user_id,balance FROM tb_user_account;
+---------+---------+
| user_id | balance |
+---------+---------+
|      80 |       0 |
|      86 |     300 |
+---------+---------+
#①更新運算元據增加100。
UPDATE tb_user_account SET balance = balance + 100 WHERE user_id = 86;
#④提交資料,此時會造成事務B的更新丟失。
COMMIT;

• B連線

#查詢當前連線事務級別
SELECT @@tx_isolation;
#設定當前會話事務隔離級別為讀未提交
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
#開始執行事務
START TRANSACTION;
#②在事務A提交之前執行，增加100。
UPDATE tb_user_account SET balance = balance + 100 WHERE user_id = 86;

#③事務B提交更新
COMMIT;

A=>operation: 事務A 增加100
BS1=>operation: 事務B 增加100
BCOMMIT=>operation: 事務B 提交
ACOMMIT=>operation: 事務A 提交(A未讀取到B的更新，造成更新丟失）


A->BS1->BCOMMIT->ACOMMIT