MySQL資料庫交易隔離等級(Transaction Isolation Level)

今天在學習JDBC的時候看到了關於MySql的事務的隔離等級的問題，感覺內容挺進階的，所以記錄一篇文章，以備後面使用。

資料庫隔離等級有四種，應用《高效能mysql》一書中的說明：

 

然後說說修改交易隔離等級的方法：

1.全域修改，修改mysql.ini設定檔，在最後加上

1 #選擇性參數有：READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE.2 [mysqld]3 transaction-isolation = REPEATABLE-READ

這裡全域預設是REPEATABLE-READ,其實MySQL本來預設也是這個層級

2.對當前session修改，在登入mysql用戶端後，執行命令：

set session transaction isolation level read uncommitted;

 

要記住mysql有一個autocommit參數，預設是on，他的作用是每一條單獨的查詢都是一個事務，並且自動開始，自動認可（執行完以後就自動結束了，如果你要適用select for update，而不手動調用 start transaction，這個for update的行鎖機制等於沒用，因為行鎖在自動認可後就釋放了），所以交易隔離等級和鎖機制即使你不顯式調用start transaction，這種機制在單獨的一條查詢語句中也是適用的，分析鎖的運作的時候一定要注意這一點

 

再來說說鎖機制：
共用鎖定：由讀表操作加上的鎖，加鎖後其他使用者只能擷取該表或行的共用鎖定，不能擷取排它鎖，也就是說只能讀不能寫

排它鎖：由寫表操作加上的鎖，加鎖後其他使用者不能擷取該表或行的任何鎖，典型是mysql事務中

start transaction;

select * from user where userId = 1 for update;

執行完這句以後

　　1）當其他事務想要擷取共用鎖定，比如交易隔離等級為SERIALIZABLE的事務，執行

　　select * from user;

 　　將會被掛起，因為SERIALIZABLE的select語句需要擷取共用鎖定

　　2）當其他事務執行

　　select * from user where userId = 1 for update;

　　update user set userAge = 100 where userId = 1; 

　　也會被掛起，因為for update會擷取這一行資料的排它鎖，需要等到前一個事務釋放該排它鎖才可以繼續進行

 

鎖的範圍:

行鎖: 對某行記錄加上鎖

表鎖: 對整個表加上鎖

這樣組合起來就有,行級共用鎖定,表級共用鎖定,行級獨佔鎖定,表級獨佔鎖定

下面來說說不同的交易隔離等級的執行個體效果，例子使用InnoDB，開啟兩個用戶端A，B，在A中修改交易隔離等級，在B中開啟事務並修改資料，然後在A中的事務查看B的事務修改效果(兩個用戶端相當於是兩個串連，在一個用戶端中的修改參數變數的值是不會影響到另外的一個用戶端的)：

 

1.READ-UNCOMMITTED(讀取未提交內容)層級

　　1)A修改事務層級並開始事務，對user表做一次查詢

　　　

 

　　2)B更新一條記錄

　　　

 

　　3）此時B事務還未提交，A在事務內做一次查詢，發現查詢結果已經改變

　　　

 

　　4）B進行交易回復

　　　

 

　　5）A再做一次查詢，查詢結果又變回去了

　　　

 

　　6）A表對user表資料進行修改

　　　

 

　　7）B表重新開始事務後，對user表記錄進行修改，修改被掛起，直至逾時，但是對另一條資料的修改成功，說明A的修改對user表的資料行加行共用鎖定(因為可以使用select)

　　　

 

　　可以看出READ-UNCOMMITTED隔離等級，當兩個事務同時進行時，即使事務沒有提交，所做的修改也會對事務內的查詢做出影響，這種層級顯然很不安全。但是在表對某行進行修改時，會對該行加上行共用鎖定

 

2. READ-COMMITTED（讀取提交內容）

　　1）設定A的交易隔離等級，並進入事務做一次查詢

　　　

 

　　2）B開始事務，並對記錄進行修改

　　　

 

　　3）A再對user表進行查詢，發現記錄沒有受到影響

　　　

 

　　4）B提交事務

　　　

 

　　5）A再對user表查詢，發現記錄被修改

　　　

 

　　6）A對user表進行修改

　　　

 

　　7）B重新開始事務，並對user表同一條進行修改，發現修改被掛起，直到逾時，但對另一條記錄修改，卻是成功，說明A的修改對user表加上了行共用鎖定(因為可以select)

　　　

　　　

 

　　READ-COMMITTED交易隔離等級，只有在事務提交後，才會對另一個事務產生影響，並且在對錶進行修改時，會對錶資料行加上行共用鎖定

 

3. REPEATABLE-READ(可重讀)

　　1）A設定交易隔離等級，進入事務後查詢一次

　　　

 

　　2）B開始事務，並對user表進行修改

　　　

 

　　3）A查看user表資料，資料未發生改變

　　　

 

　　4）B提交事務

　　　

 

　　5）A再進行一次查詢，結果還是沒有變化

　　　

 

　　6）A提交事務後，再查看結果，結果已經更新

　　　

 

　　7）A重新開始事務，並對user表進行修改

　　　

　　　

　　8）B表重新開始事務，並對user表進行修改，修改被掛起，直到逾時，對另一條記錄修改卻成功，說明A對錶進行修改時加了行共用鎖定(可以select)

　　　

　　　

 

　　REPEATABLE-READ交易隔離等級，當兩個事務同時進行時，其中一個事務修改資料對另一個事務不會造成影響，即使修改的事務已經提交也不會對另一個事務造成影響。

　　在事務中對某條記錄修改，會對記錄加上行共用鎖定，直到事務結束才會釋放。

 

4.SERIERLIZED(可序列化)

　　1）修改A的交易隔離等級，並作一次查詢

　　　

 

　　2）B對錶進行查詢，正常得出結果，可知對user表的查詢是可以進行的

　　　

 

　　3）B開始事務，並對記錄做修改，因為A事務未提交，所以B的修改處於等待狀態，等待A事務結束，最後逾時，說明A在對user表做查詢操作後，對錶加上了共用鎖定

　　　

 

　　SERIALIZABLE交易隔離等級最嚴厲，在進行查詢時就會對錶或行加上共用鎖定，其他事務對該表將只能進行讀操作，而不能進行寫操作。