老王私房錢之MYSQL事務及隔離級別
文章目錄
一、理論
1.事務簡介
2.事務的基本要素（ACID）
3.事務的實現
4.事務的隔離級別
（1）Read Uncommitted（讀取未提交內容）
（2）Read Committed（讀取提交內容）
（3）Repeatable Read（可重讀）
（4）Serializable（可串行化）
5.并發場景下不同隔離級別會產生的問題
臟讀(Drity Read)：
不可重復讀(Non-repeatable read):
幻讀(Phantom Read):
二、實踐
1.Read Uncommitted（讀取未提交內容）
2.Read Committed（讀取提交內容）
3.Repeatable Read（可重讀）
4.Serializable（可串行化）
其他資料
一、理論
1.事務簡介
事務可由一條非常簡單的SQL語句組成，也可以由一組復雜的SQL語句組成。
事務是訪問并更新數據庫中的各種數據項的一個程序執行單元
。事務中的操作要么都做修改，要么都不做Mysql包含諸如InnoDB存儲引擎嚴格遵循ACID模型，因此數據不會損壞，結果不會因軟件崩潰和硬件故障等異常情況而失真。當您依靠符合ACID的功能時，無需重新發明一致性檢查和崩潰恢復機制。如果您有其他軟件保護措施，超可靠的硬件或可以容忍少量數據丟失或不一致的應用程序，則可以調整MySQL設置，以權衡一些ACID可靠性以獲得更高的性能或吞吐量。
2.事務的基本要素（ACID）
A(Atomicity),原子性：
是指整個數據庫事務是不可分割的工作單位。
只有使事務中所有的數據庫操作都執行成功
，才算整個事務成功，事務中任何一個SQL語句執行失敗，已經執行成功的SQL語句必須撤銷，數據庫應退回到執行事務前的狀態
C(consistency),一致性：
一致性指事務從一種狀態轉變為下一種一致的狀態。在事務開始之前和事務結束以后，數據庫的完整性約束沒有被破壞
I(isolation),隔離性：
要求每個讀寫事務的對象對其他事務的操作對象能相互分離，即
該事務提交前對其他事務不可見，通常使用鎖來實現
D(durability),持久性：
事務一旦提交，其結果是永久性的。即使發生宕機等故障，數據庫也能將數據恢復
3.事務的實現
事務隔離性是由鎖來實現。原子性、一致性、持久性通過數據庫的redo log和undo log來完成。redo log用來保證事務的原子性和持久性。undo log用來保證事務的一致性。
4.事務的隔離級別
（1）Read Uncommitted（讀取未提交內容）
在該隔離級別，所有事務都可以看到其他未提交事務的執行結果。本隔離級別很少用于實際應用，因為它的性能也不比其他級別好多少。讀取未提交的數據，也被稱之為臟讀（Dirty Read）。
（2）Read Committed（讀取提交內容）
這是大多數數據庫系統的默認隔離級別（但不是MySQL默認的）。它滿足了隔離的簡單定義：一個事務只能看見已經提交事務所做的改變。這種隔離級別 也支持所謂的不可重復讀（Nonrepeatable Read），因為同一事務的其他實例在該實例處理其間可能會有新的commit，所以同一select可能返回不同結果。
（3）Repeatable Read（可重讀）
這是MySQL的默認事務隔離級別，它確保同一事務的多個實例在并發讀取數據時，會看到同樣的數據行。不過理論上，這會導致另一個棘手的問題：
幻讀
（Phantom Read）。簡單的說，
幻讀指當用戶讀取某一范圍的數據行時，另一個事務又在該范圍內插入了新行，當用戶再讀取該范圍的數據行時，會發現有新的“幻影” 行。
InnoDB和Falcon存儲引擎通過多版本并發控制（MVCC，Multiversion Concurrency Control）機制解決了該問題。
（4）Serializable（可串行化）
這是最高的隔離級別，它通過強制事務排序，使之不可能相互沖突，從而解決幻讀問題。簡言之，它是在每個讀的數據行上加上共享鎖。在這個級別，可能導致大量的超時現象和鎖競爭。
5.并發場景下不同隔離級別會產生的問題
臟讀(Drity Read)：
某個事務已更新一份數據，另一個事務在此時讀取了同一份數據，由于某些原因，前一個RollBack了操作，則后一個事務所讀取的數據就會是不正確的。
不可重復讀(Non-repeatable read):
在一個事務的兩次查詢之中數據不一致，這可能是兩次查詢過程中間插入了一個事務更新的原有的數據。
幻讀(Phantom Read):
在一個事務的兩次查詢中數據筆數不一致，例如有一個事務查詢了幾列(Row)數據，而另一個事務卻在此時插入了新的幾列數據，先前的事務在接下來的查詢中，就會發現有幾列數據是它先前所沒有的。
二、實踐
1.Read Uncommitted（讀取未提交內容）
小白在小白銀行做數據庫管理員，這一天老王要從一張存有一萬塊錢的銀行卡里給兄弟轉賬一千
session1
mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> select * from deposit; +----+---------+----------+ | id | name | deposit | +----+---------+----------+ | 1 | laowang | 10000.00 | +----+---------+----------+ 1 row in set (0.00 sec) mysql> update deposit set deposit=deposit-1000 where id=1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0
1
2
3
4
5
6
7
8
9
10
11
12
此時恰逢老王的老婆王太太正在查看銀行余額
session2
mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> select * from deposit; +----+---------+---------+ | id | name | deposit | +----+---------+---------+ | 1 | laowang | 9000.00 | +----+---------+---------+ 1 row in set (0.00 sec) mysql> commit; Query OK, 0 rows affected (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
這時老王心里想自己這張卡自己的老婆也有密碼，不安全，還是用私房錢給兄弟轉吧，于是取消了轉賬的操作
session1
mysql> rollback; Query OK, 0 rows affected (0.00 sec) mysql> select * from deposit; +----+---------+----------+ | id | name | deposit | +----+---------+----------+ | 1 | laowang | 10000.00 | +----+---------+----------+ 1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
不過就是這樣，老王回到家里還是被罰跪了一晚搓衣板，老王百思不得其解，自己的老婆是怎么知道自己要轉賬的，于是聯系了做DBA的小白，小白多方查找資料后發現是隔離級別的問題（注：我這里為了不影響環境，用的是session級別），所產生的這種現象就叫做臟讀.
mysql> show variables like 'transaction_isolation'; +-----------------------+------------------+ | Variable_name | Value | +-----------------------+------------------+ | transaction_isolation | READ-UNCOMMITTED | +-----------------------+------------------+
1
2
3
4
5
6
于是小白調整了隔離級別以解決這個事情
mysql> set session transaction_isolation='read-committed';
1
2.Read Committed（讀取提交內容）
改了隔離級別之后，也就沒有臟讀的現象發生了，今天王太太的外甥結婚，讓老王去小白銀行給外甥轉1000元的份子錢
session1
mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> select * from deposit; +----+---------+----------+ | id | name | deposit | +----+---------+----------+ | 1 | laowang | 10000.00 | +----+---------+----------+ 1 row in set (0.00 sec) mysql> update deposit set deposit=deposit-1000 where id=1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0
1
2
3
4
5
6
7
8
9
10
11
12
此時恰逢王太太又在查看銀行余額
session2
mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> select * from deposit; +----+---------+----------+ | id | name | deposit | +----+---------+----------+ | 1 | laowang | 10000.00 | +----+---------+----------+ 1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
此時王太太知道老王還沒有轉錢，于是告訴老王 快點轉錢，老王也很快把錢轉了過去
session1
mysql> commit; Query OK, 0 rows affected (0.00 sec)
1
2
而這時候王太太沒有點擊刷新按鈕（注：這里的刷新代表commit;）,重新查詢卻發現金額已經變了
session2
mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> select * from deposit; +----+---------+----------+ | id | name | deposit | +----+---------+----------+ | 1 | laowang | 10000.00 | +----+---------+----------+ 1 row in set (0.00 sec) # 下面的內容為老王轉賬提交之后的查詢 mysql> select * from deposit; +----+---------+---------+ | id | name | deposit | +----+---------+---------+ | 1 | laowang | 9000.00 | +----+---------+---------+ 1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
王太太覺得這種用戶體驗很不好，于是找到了小白，小白經過多方查找發現這還是隔離級別的問題，王太太在同一個事務中兩次查詢結果的不同就叫做不可重復讀 ，于是小白再次調整了隔離級別.
3.Repeatable Read（可重讀）
mysql> set @@session.transaction_isolation='repeatable-read'; Query OK, 0 rows affected (0.00 sec)
1
2
這里為了模擬傳統的可重復讀，我們需要關閉間隙鎖，在my.cnf中加入這么一項，然后重啟數據庫
innodb_locks_unsafe_for_binlog=1
1
這一天，王太太來到小白銀行開通了一張銀行卡并準備往除第一張卡之外的卡里轉錢
session1
mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> select * from deposit where id>1 for update; +----+---------+---------+ | id | name | deposit | +----+---------+---------+ | 2 | laowang | 0.00 | +----+---------+---------+ 1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
就在這時，老王以迅雷不及掩耳盜鈴之鈴兒響叮當仁不讓世界充滿愛你沒商量商量因我愛你之勢迅速辦了一張卡
session2
mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> insert into deposit values(3,'laowang',0); Query OK, 1 row affected (0.00 sec) mysql> commit; Query OK, 0 rows affected (0.01 sec)
1
2
3
4
5
6
7
8
這時候王太太開始轉錢了
session1
mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> select * from deposit where id>1 for update; +----+---------+---------+ | id | name | deposit | +----+---------+---------+ | 2 | laowang | 0.00 | +----+---------+---------+ 1 row in set (0.00 sec) # 下面為老王辦完卡之后的操作 mysql> update deposit set deposit=deposit+1000 where id>1; Query OK, 2 rows affected (0.00 sec) Rows matched: 2 Changed: 2 Warnings: 0 mysql> commit; Query OK, 0 rows affected (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
王太太發現自己竟然往兩個卡里轉了錢，感覺很奇怪，逼問之下，老王承認了自己偷偷辦了張卡，可憐的老王又要跪搓衣板了，不過這個問題還是要解決的，王太太找到小白之后，小白發現這是傳統Repeatable Read（可重讀）所造成的
幻讀
問題，于是小白采用了帶有Next-Key Lock算法的Mysql的可重復讀，重新進行了上面的操作
session1
mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> select * from deposit where id>1 for update; +----+---------+----------+ | id | name | deposit | +----+---------+----------+ | 2 | laowang | 10000.00 | +----+---------+----------+ 1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
session2
mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> insert into deposit values(3,'laowang',0); ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
1
2
3
4
5
可以發現,這種情況下，老王沒有辦法辦新卡了，可憐的老王，獲得私房錢的方法又少了一種
4.Serializable（可串行化）
少用到。
其他資料
（1）一文講清楚MySQL事務隔離級別和實現原理
（2）事務的四種隔離級別-帶栗子
MySQL 數據庫
版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。
相關文章