在講mysql事物隔離級别之前,我們先簡單說說mysql的鎖和事務。
一:資料庫鎖
因為資料庫要解決并發控制問題。在同一時刻,可能會有多個用戶端對同一張表進行操作,比如有的在讀取該行資料,其他的嘗試去删除它。為了保證資料的一緻性,資料庫就要對這種并發操作進行控制,是以就有了鎖的概念。
鎖的分類
從對資料庫操作的類型分
讀鎖(共享鎖):針對同一塊資料,多個讀操作可以同時進行而不會互相影響。由讀表操作加上的鎖,加鎖後其他使用者隻能擷取該表或行的共享鎖,不能擷取排它鎖,也就是說隻能讀不能寫。
寫鎖(排它鎖):當目前寫操作沒有完成之前,它會阻斷其他寫鎖和讀鎖。由寫表操作加上的鎖,加鎖後其他使用者不能擷取該表或行的任何鎖。
從鎖定的資料範圍分
表鎖:鎖定某個表。
行鎖 :鎖定某行。
為了盡可能 提高資料庫的并發度,每次鎖定的資料範圍越小越好。理論上每次隻鎖定目前操作的資料的方案會得到最大的并發度,但是管理鎖是很耗費資源的事情。是以資料庫系統需要在高并發響應和系統性能兩方面進行平衡,這樣就産生了“鎖粒度”的概念。
鎖粒度
表鎖:管理鎖的開銷最小,同時允許的并發量也最小的鎖機制。MyIsam存儲引擎使用的鎖機制。當要寫入資料時,把整個表都鎖上,此時其他讀、寫動作一律等待。在MySql中,除了MyIsam存儲引擎使用這種鎖政策外,MySql本身也使用表鎖來執行某些特定動作,比如alter table.
行鎖:可以支援最大并發的鎖政策。InnoDB和Falcon兩張存儲引擎都采用這種政策。
MySql是一種開放的架構,你可以實作自己的存儲引擎,并實作自己的鎖粒度政策,不像Oracle,你沒有機會改變鎖政策,Oracle采用的是行鎖。從大到小,mysql伺服器僅支援表級鎖,行鎖需要存儲引擎完成。粒度越精細,并發性越好。即行鎖的并發性最好,但需要存儲引擎的支援。
二:事務
從業務角度出發 ,對資料庫的一組操作要求保持4個特征:
Atomicity:原子性。
Consistency:一緻性。
Isolation:隔離性。
Durability:持久性。
為了更好地了解ACID,以銀行賬戶轉賬為例:
1 START TRANSACTION;
2 SELECT balance FROM checking WHERE customer_id = 10233276;
3 UPDATE checking SET balance = balance - 200.00 WHERE customer_id = 10233276;
4 UPDATE savings SET balance = balance + 200.00 WHERE customer_id = 10233276;
5 COMMIT;
原子性:要麼完全送出(10233276的checking餘額減少200,savings 的餘額增加200),要麼完全復原(兩個表的餘額都不發生變化)。
一緻性:這個例子的一緻性展現在 200元不會因為資料庫系統運作到第3行之後,第4行之前時崩潰而不翼而飛,因為事物還沒有送出。
隔離性:允許在一個事務中的操作語句會與其他事務的語句隔離開,比如事務A運作到第3行之後,第4行之前,此時事務B去查詢checking餘額時,它仍然能夠看到在事務A中被減去的200元,因為事務A和B是彼此隔離的。在事務A送出之前,事務B觀察不到資料的改變。
持久性:這個很好了解。
事務跟鎖一樣都會需要大量工作,是以你可以根據你自己的需要來決定是否需要事務支援,進而選擇不同的存儲引擎。
三:事務隔離級别
SQL标準定義了4中隔離級别,包括了一些具體規則,用來限定事務内外的哪些改變是可見的,哪些是不可見的。低級别的隔離級一般支援更高的并發處理,并擁有更低的系統開銷。
Read Uncommitted(讀取未送出内容)
在該隔離級别,所有事務都可以看到其他未送出事務的執行結果。本隔離級别很少用于實際應用,因為它的性能也不比其他級别好多少。讀取未送出的資料,也被稱之為髒讀(Dirty Read)。
Read Committed(讀取送出内容)
這是大多數資料庫系統的預設隔離級别(但不是MySQL預設的)。它滿足了隔離的簡單定義:一個事務隻能看見已經送出事務所做的改變。這種隔離級别 也支援所謂的不可重複讀(Nonrepeatable Read),因為同一事務的其他執行個體在該執行個體處理其間可能會有新的commit,是以同一select可能傳回不同結果。
Repeatable Read(可重讀)
這是MySQL的預設事務隔離級别,它確定同一事務的多個執行個體在并發讀取資料時,會看到同樣的資料行。 不過理論上,這會導緻另一個棘手的問題:幻讀 (Phantom Read)。簡單的說,幻讀指當使用者讀取某一範圍的資料行時, 另一個事務又在該範圍内插入了新行,當使用者再讀取該範圍的資料行時,會發現有新的“幻影” 行。 InnoDB和Falcon存儲引擎通過多版本并發控制(MVCC,Multiversion Concurrency Control)機制解決了該問題。
Serializable(可串行化)
這是最高的隔離級别,它通過強制事務排序,使之不可能互相沖突,進而解決幻讀問題。簡言之,它是在每個讀的資料行上加上共享鎖。在這個級别,可能導緻大量的逾時現象和鎖競争。
這四種隔離級别采取不同的鎖類型來實作,若讀取的是同一個資料的話,就容易發生問題。例如:
髒讀(Drity Read):某個事務已更新一份資料,另一個事務在此時讀取了同一份資料,由于某些原因,前一個RollBack了操作,則後一個事務所讀取的資料就會是不正确的。
幻讀(Phantom Read):在一個事務的兩次查詢中資料筆數不一緻,例如有一個事務查詢了幾列(Row)資料,而另一個事務卻在此時插入了新的幾列資料,先前的事務在接下來的查詢中,就會發現有幾列資料是它先前所沒有的。
不可重複讀(Non-repeatable read):在一個事務的兩次查詢之中資料不一緻,這可能是兩次查詢過程中間插入了一個事務更新的原有的資料。
案例:
在該隔離級别,所有事務都可以看到其他未送出事務的執行結果。本隔離級别很少用于實際應用,因為它的性能也不比其他級别好多少。讀取未送出的資料,也被稱之為髒讀(Dirty Read)。
1.分别在A、B兩個用戶端執行:
2.
3. A:
4. root@(none) 10:54>SET GLOBAL tx_isolation='READ-UNCOMMITTED';
5. Query OK, 0 rows affected (0.00 sec)
6.
7. root@(none) 10:54>SELECT @@tx_isolation;
8. +------------------+
9. | @@tx_isolation |
10. +------------------+
11. | READ-UNCOMMITTED |
12. +------------------+
13. 1 row in set (0.00 sec)
14.
15. root@(none) 10:54>use test;
16. Database changed
17. root@test 10:55>begin;
18. Query OK, 0 rows affected (0.00 sec)
19.
20. root@test 10:55>select * from test1;
21. +------+
22. | a |
23. +------+
24. | 1 |
25. | 2 |
26. | 3 |
27. | 4 |
28. +------+
29. 4 rows in set (0.00 sec)
30.
31. B上:
32. root@test 10:58>select @@tx_isolation;
33. +------------------+
34. | @@tx_isolation |
35. +------------------+
36. | READ-UNCOMMITTED |
37. +------------------+
38. 1 row in set (0.00 sec)
39.
40. root@test 10:58>
41. root@test 10:58>begin;
42. Query OK, 0 rows affected (0.00 sec)
43.
44. root@test 10:58>insert into test.test1 values (999);
45. Query OK, 1 row affected (0.00 sec)
46.
47. root@test 10:58>select * from test.test1;
48. +------+
49. | a |
50. +------+
51. | 1 |
52. | 2 |
53. | 3 |
54. | 4 |
55. | 999 |
56. +------+
57. 5 rows in set (0.00 sec)
58. 此處B用戶端并未commit;
59.
60. 再檢視A用戶端:
61. root@test 10:58>select * from test1;
62. +------+
63. | a |
64. +------+
65. | 1 |
66. | 2 |
67. | 3 |
68. | 4 |
69. | 999 |
70. +------+
71. 5 rows in set (0.00 sec)
72.
73. 此處A可以看到新的記錄了。
這是大多數資料庫系統的預設隔離級别(但不是MySQL預設的)。它滿足了隔離的簡單定義:一個事務隻能看見已經送出事務所做的改變。 這種隔離級别 也支援所謂的不可重複讀(Nonrepeatable Read),因為同一事務的其他執行個體在該執行個體處理其間可能會有新的commit,是以同一select可能傳回不同結果。
1.在A用戶端:
2. root@(none) 11:10>SET GLOBAL tx_isolation='READ-COMMITTED';
3. Query OK, 0 rows affected (0.00 sec)
4.
5. root@(none) 11:10>SELECT @@tx_isolation;
6. +----------------+
7. | @@tx_isolation |
8. +----------------+
9. | READ-COMMITTED |
10. +----------------+
11. 1 row in set (0.00 sec)
12.
13. root@(none) 11:10>
14. root@(none) 11:10>begin;
15. Query OK, 0 rows affected (0.00 sec)
16.
17. root@(none) 11:10>select * from test.test1;
18. +------+
19. | a |
20. +------+
21. | 1 |
22. | 2 |
23. | 3 |
24. | 4 |
25. +------+
26.
27. 在B用戶端執行:
28. root@test 11:11>begin;
29. Query OK, 0 rows affected (0.00 sec)
31. root@test 11:11>select * from test.test1;
32. +------+
33. | a |
34. +------+
35. | 1 |
36. | 2 |
37. | 3 |
38. | 4 |
39. +------+
40. 4 rows in set (0.00 sec)
41.
42. root@test 11:11>
43. root@test 11:11>delete from test.test1 where a=1;
44. Query OK, 1 row affected (0.00 sec)
45.
46. root@test 11:12>select * from test.test1;
47. +------+
48. | a |
49. +------+
50. | 2 |
51. | 3 |
52. | 4 |
53. +------+
54.
55. 此時查詢A用戶端:
56.
57. root@(none) 11:12>select * from test.test1;
58. +------+
59. | a |
60. +------+
61. | 1 |
62. | 2 |
63. | 3 |
64. | 4 |
65. +------+
66. 此處看出A用戶端無變化,在B用戶端執行commit後再檢視A用戶端:
67.
68. root@(none) 11:13>select * from test.test1;
69. +------+
70. | a |
71. +------+
72. | 2 |
73. | 3 |
74. | 4 |
75. +------+
76.
77. 可以看到A用戶端的資料已經變了。已送出讀隻允許讀取已送出的記錄,但不要求可重複讀。
78. 用MVCC來說就是讀取目前行的最新版本。
Repeatable Read(可重讀)
這是MySQL的預設事務隔離級别,它確定同一事務的多個執行個體在并發讀取資料時,會看到同樣的資料行。 不過理論上,這會導緻另一個棘手的問題:幻讀 (Phantom Read)。 簡單的說,幻讀指當使用者讀取某一範圍的資料行時,另一個事務又在該範圍内插入了新行,當使用者再讀取該範圍的資料行時,會發現有新的“幻影” 行。 InnoDB和Falcon存儲引擎通過多版本并發控制(MVCC,Multiversion Concurrency Control)機制解決了該問題。
1.在A用戶端上:
2. root@(none) 11:17>SET GLOBAL tx_isolation='REPEATABLE-READ';
5. root@(none) 11:17>
6. root@(none) 11:17>
7. root@(none) 11:17>SELECT @@tx_isolation;
8. +-----------------+
10. +-----------------+
11. | REPEATABLE-READ |
12. +-----------------+
15. root@(none) 11:17>BEGIN;
16. Query OK, 0 rows affected (0.00 sec)
17.
18. 在B用戶端上:
19. root@test 11:20>select @@tx_isolation;
20. +-----------------+
21. | @@tx_isolation |
22. +-----------------+
23. | REPEATABLE-READ |
24. +-----------------+
25. 1 row in set (0.00 sec)
27. root@test 11:20>insert into test.test1 values (555);
28. Query OK, 1 row affected (0.00 sec)
29.
30. root@test 11:20>commit;
31. Query OK, 0 rows affected (0.00 sec)
32.
33. root@test 11:21>
34. root@test 11:21>select * from test.test1;
35. +------+
36. | a |
37. +------+
38. | 2 |
39. | 3 |
40. | 4 |
41. | 555 |
42. +------+
43. 4 rows in set (0.00 sec)
44. 此處在B用戶端上已經commit.
46. 然後檢視A用戶端:
47.
48. root@(none) 11:22>SELECT * FROM test.test1;
50. | a |
51. +------+
55. +------+
56. 3 rows in set (0.00 sec)
57.
58. root@(none) 11:22>commit;
59. Query OK, 0 rows affected (0.00 sec)
60.
61.
62. root@(none) 11:22>SELECT * FROM test.test1;
63. +------+
64. | a |
69. | 555 |
71. 4 rows in set (0.00 sec)
73. 在A用戶端上送出後可以看到新資料。
74. 也就是說在可重複讀隔離級别隻能讀取已經送出的資料,并且在一個事務内,讀取的資料就是事務開始時的資料。
Serializable(可串行化)
這是最高的隔離級别,它通過強制事務排序,使之不可能互相沖突,進而解決幻讀問題。簡言之,它是在每個讀的資料行上加上共享鎖。在這個級别,可能導緻大量的逾時現象和鎖競争。
該類型在A用戶端操作test.test1表時會鎖定該資料,如果B用戶端想要操作test.test1就需要等待A用戶端釋放。