innodb之事務隔離級別示例
- 2019 年 11 月 6 日
- 筆記
下面我們用真實的例子來說明各個級別的情況，首先我們創建一個資料庫test，然後再資料庫中創建一個表city，在這個city表中來進行測試：
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ rows in set (0.03 sec) mysql> create database test; Query OK, row affected (0.00 sec) mysql> use test; Database changed mysql> CREATE TABLE `city`(`id` int() NOT NULL AUTO_INCREMENT , -> `name` varchar() not NULL DEFAULT ' ' , -> `state` varchar() not NULL DEFAULT ' ' , -> PRIMARY KEY (`id`) -> )ENGINE=InnoDB DEFAULT CHARACTER SET=utf8 COMMENT='城市'; Query OK, rows affected (0.05 sec) mysql> exit Bye
為了更加方便觀察執行時間，我們給mysql的命令行前面打上時間戳，也就是在/etc/my.cnf中添加prompt參數
--prompt="\u@\h : \d\r:\m:\s>"，其中u表示user，h表示host、d表示默認資料庫，r、m、s是表示當前時間精確到秒，具體參數可以上網查看，這樣我們的命令行中就打上了時間戳，更加方便我們理解。
首先來看未提交讀(read uncommitted)
會話1：
root@localhost :test11::>select @@tx_isolation; +-----------------+ | @@tx_isolation | +-----------------+ | REPEATABLE-READ | +-----------------+ row in set, warning (. sec) root@localhost :test11::>insert into city (id,name,state) values (,'西安','陝西'); Query OK, row affected (. sec) root@localhost :test11::>insert into city (id,name,state) values (,'寶雞','陝西'); Query OK, row affected (. sec) root@localhost :test11::>insert into city (id,name,state) values (,'陝北','陝西'); Query OK, row affected (. sec) root@localhost :test11::>select * from city; +----+--------+--------+ | id | name | state | +----+--------+--------+ | 1 | 西安 | 陝西 | | 2 | 寶雞 | 陝西 | | 3 | 陝北 | 陝西 | +----+--------+--------+ rows in set (. sec) root@localhost :test11::>begin; Query OK, rows affected (. sec) root@localhost :test11::>insert into city (id,name,state) values (,'陝南','陝西'); Query OK, row affected (. sec) root@localhost :test11::>
我們可以看到，我們插入了三條記錄，然後開啟事務，插入第四條記錄，此時我們打開會話2，如下圖所示：
會話2
root@localhost :test11::>select @@tx_isolation; +-----------------+ | @@tx_isolation | +-----------------+ | REPEATABLE-READ | +-----------------+ row in set, warning (. sec) root@localhost :test11::>select * from city; +----+--------+--------+ | id | name | state | +----+--------+--------+ | 1 | 西安 | 陝西 | | 2 | 寶雞 | 陝西 | | 3 | 陝北 | 陝西 | +----+--------+--------+ rows in set (. sec) root@localhost :test11::>set session transaction isolation level read uncommitted; Query OK, rows affected (. sec) root@localhost :test11::>select @@tx_isolation; +------------------+ | @@tx_isolation | +------------------+ | READ-UNCOMMITTED | +------------------+ row in set, warning (. sec) root@localhost :test11::>select * from city; +----+--------+--------+ | id | name | state | +----+--------+--------+ | 1 | 西安 | 陝西 | | 2 | 寶雞 | 陝西 | | 3 | 陝北 | 陝西 | | 4 | 陝南 | 陝西 | +----+--------+--------+ rows in set (. sec) root@localhost :test11::>
我們可以發現，innodb默認的隔離級別是可重複讀，即repeated-read，當處於這種事務隔離級別的時候，我們現在不能看到第四條記錄，因為第四條記錄屬於第一個會話的事務，還沒有提交。當我們使用set session語法將隔離級別改為未提交讀，然後重新查看記錄的時候，我們發現這條記錄已經可以看到了，這就是常說的"臟讀"，這種隔離級別下是不能避免臟讀的。
再來看看已提交讀read committed:
會話1：
root@localhost :test12:09:>select * from city; +----+--------+--------+ | id | name | state | +----+--------+--------+ | 1 | 西安 | 陝西 | | 2 | 寶雞 | 陝西 | | 3 | 陝北 | 陝西 | | 4 | 陝南 | 陝西 | +----+--------+--------+ rows in set (. sec) root@localhost :test12::>select @@tx_isolation; +-----------------+ | @@tx_isolation | +-----------------+ | REPEATABLE-READ | +-----------------+ row in set, warning (. sec) root@localhost :test12::>begin; Query OK, rows affected (. sec) root@localhost :test12::>insert into city (id,name,state) values (,'壺口','陝西'); Query OK, row affected (. sec) root@localhost :test12::>select * from city; +----+--------+--------+ | id | name | state | +----+--------+--------+ | 1 | 西安 | 陝西 | | 2 | 寶雞 | 陝西 | | 3 | 陝北 | 陝西 | | 4 | 陝南 | 陝西 | | 5 | 壺口 | 陝西 | +----+--------+--------+ rows in set (. sec)
還跟上次一致，我們在可重複讀的情況下開啟事務，然後插入一條記錄5，然後打開會話2，可以看到如下結果：
會話2：
root@localhost :test12::>set session transaction isolation level read committed; Query OK, rows affected (. sec) root@localhost :test12::>select @@tx_isolation; +----------------+ | @@tx_isolation | +----------------+ | READ-COMMITTED | +----------------+ row in set, warning (. sec) root@localhost :test12::>begin; Query OK, rows affected (. sec) root@localhost :test12::>select * from city; +----+--------+--------+ | id | name | state | +----+--------+--------+ | 1 | 西安 | 陝西 | | 2 | 寶雞 | 陝西 | | 3 | 陝北 | 陝西 | | 4 | 陝南 | 陝西 | +----+--------+--------+ rows in set (. sec)
可以看到，當我們把事務的隔離級別調整至read committed(簡稱RC)時，我們無法看到第5條記錄，因為它沒有在會話1當中進行提交，此時我們在會話1中進行提交：
root@localhost :test12::>commit; Query OK, 0 rows affected (0.10 sec)
然後重新查看會話2，我們發現
root@localhost :test12::>select * from city; +----+--------+--------+ | id | name | state | +----+--------+--------+ | 1 | 西安 | 陝西 | | 2 | 寶雞 | 陝西 | | 3 | 陝北 | 陝西 | | 4 | 陝南 | 陝西 | | 5 | 壺口 | 陝西 | +----+--------+--------+ rows in set (. sec)
這條記錄已經可以查看了，說明這種情況避免了"臟讀",數據的真實性得到了保證，但是帶來了另外一個問題，那就是同樣的數據查詢操作，得出了不一樣的結果，不能避免重複讀導致的數據不一致情況。RC級別下雖然避免的臟讀的情況，但是不可避免重複讀導致的兩次查詢不一致的情況。
然後看看可重複讀Repeatable Read的情況，我們在記錄中插入第6條，然後進行提交，操作如下：
會話1：
root@localhost :test12::>begin; Query OK, rows affected (. sec) root@localhost :test12::>insert into city (id,name,state) values (,'富平','陝西'); Query OK, row affected (. sec) root@localhost :test12::>select * from city; +----+--------+--------+ | id | name | state | +----+--------+--------+ | 1 | 西安 | 陝西 | | 2 | 寶雞 | 陝西 | | 3 | 陝北 | 陝西 | | 4 | 陝南 | 陝西 | | 5 | 壺口 | 陝西 | | 6 | 富平 | 陝西 | +----+--------+--------+ rows in set (. sec) root@localhost :test12::>commit; Query OK, rows affected (. sec)
然後我們打開會話2，開啟事務，然後去查詢數據，發現第6條數據沒有被推過來，即使我們已經在會話1中進行了提交，會話2中依舊沒有看到這條資訊：
會話2：
root@localhost :test12::>begin; Query OK, rows affected (. sec) root@localhost :test12::>select @@tx_isolation; +-----------------+ | @@tx_isolation | +-----------------+ | REPEATABLE-READ | +-----------------+ row in set, warning (. sec) root@localhost :test12::>select * from city; +----+--------+--------+ | id | name | state | +----+--------+--------+ | 1 | 西安 | 陝西 | | 2 | 寶雞 | 陝西 | | 3 | 陝北 | 陝西 | | 4 | 陝南 | 陝西 | | 5 | 壺口 | 陝西 | +----+--------+--------+ rows in set (. sec) root@localhost :test12::>commit; Query OK, rows affected (. sec) root@localhost :test12::>select * from city; +----+--------+--------+ | id | name | state | +----+--------+--------+ | 1 | 西安 | 陝西 | | 2 | 寶雞 | 陝西 | | 3 | 陝北 | 陝西 | | 4 | 陝南 | 陝西 | | 5 | 壺口 | 陝西 | | 6 | 富平 | 陝西 | +----+--------+--------+ rows in set (. sec)
可以看到，只有在會話2也進行提交的情況下，才能看到會話1更新的數據，換句話說，在事務沒有結束的時候，兩個會話裡面的操作互不影響。所以說RR級別下可以重複讀。
再看一個例子，這個例子說明了RR是如何避免幻讀的。在此之前，我們再重複下並發事務帶來的問題概念：
1、臟讀：事務A讀取了事務B更新的數據，然後B回滾操作，那麼A讀取到的數據是臟數據
2、不可重複讀：事務 A 多次讀取同一數據，事務 B 在事務A多次讀取的過程中，對數據作了更新並提交，導致事務A多次讀取同一數據時，結果 不一致。
3、幻讀：系統管理員A將資料庫中所有學生的成績從具體分數改為ABCDE等級，但是系統管理員B就在這個時候插入了一條具體分數的記錄，當系統管理員A改結束後發現還有一條記錄沒有改過來，就好像發生了幻覺一樣，這就叫幻讀。
現在來看會話1：
root@localhost :test08::>select @@tx_isolation; +-----------------+ | @@tx_isolation | +-----------------+ | REPEATABLE-READ | +-----------------+ row in set, warning (. sec) root@localhost :test08::>begin -> ; Query OK, rows affected (. sec) root@localhost :test08::>select * from city; +----+--------+--------+ | id | name | state | +----+--------+--------+ | 1 | 西安 | 陝西 | | 2 | 寶雞 | 陝西 | | 3 | 陝北 | 陝西 | | 4 | 陝南 | 陝西 | | 5 | 壺口 | 陝西 | | 6 | 富平 | 陝西 | +----+--------+--------+ rows in set (. sec) root@localhost :test08::>insert into city (id,name,state) values (,'楊凌','陝西'); Query OK, row affected (. sec) root@localhost :test08::>select * from city; +----+--------+--------+ | id | name | state | +----+--------+--------+ | 1 | 西安 | 陝西 | | 2 | 寶雞 | 陝西 | | 3 | 陝北 | 陝西 | | 4 | 陝南 | 陝西 | | 5 | 壺口 | 陝西 | | 6 | 富平 | 陝西 | | 7 | 楊凌 | 陝西 | +----+--------+--------+ rows in set (. sec) root@localhost :test08::>commit; Query OK, rows affected (.08 sec)
再來看看會話2：
root@localhost :test08::>select @@tx_isolation; +-----------------+ | @@tx_isolation | +-----------------+ | REPEATABLE-READ | +-----------------+ row in set, warning (. sec) root@localhost :test08::>begin -> ; Query OK, rows affected (. sec) root@localhost :test08::>select * from city; +----+--------+--------+ | id | name | state | +----+--------+--------+ | 1 | 西安 | 陝西 | | 2 | 寶雞 | 陝西 | | 3 | 陝北 | 陝西 | | 4 | 陝南 | 陝西 | | 5 | 壺口 | 陝西 | | 6 | 富平 | 陝西 | +----+--------+--------+ rows in set (. sec) root@localhost :test08::>insert into city (id,name,state) values (,'楊凌','陝西'); Query OK, row affected (. sec) root@localhost :test08::>select @@tx_isolation; +-----------------+ | @@tx_isolation | +-----------------+ | REPEATABLE-READ | +-----------------+ row in set, warning (. sec) root@localhost :test08::>begin; Query OK, rows affected (. sec) root@localhost :test09::>select * from city; +----+--------+--------+ | id | name | state | +----+--------+--------+ | 1 | 西安 | 陝西 | | 2 | 寶雞 | 陝西 | | 3 | 陝北 | 陝西 | | 4 | 陝南 | 陝西 | | 5 | 壺口 | 陝西 | | 6 | 富平 | 陝西 | +----+--------+--------+ rows in set (. sec) root@localhost :test09::08>select * from city for update; +----+--------+--------+ | id | name | state | +----+--------+--------+ | 1 | 西安 | 陝西 | | 2 | 寶雞 | 陝西 | | 3 | 陝北 | 陝西 | | 4 | 陝南 | 陝西 | | 5 | 壺口 | 陝西 | | 6 | 富平 | 陝西 | | 7 | 楊凌 | 陝西 | +----+--------+--------+ rows in set (15.74 sec) root@localhost :test09::>select * from city lock in share mode; +----+--------+--------+ | id | name | state | +----+--------+--------+ | 1 | 西安 | 陝西 | | 2 | 寶雞 | 陝西 | | 3 | 陝北 | 陝西 | | 4 | 陝南 | 陝西 | | 5 | 壺口 | 陝西 | | 6 | 富平 | 陝西 | | 7 | 楊凌 | 陝西 | +----+--------+--------+ rows in set (. sec)
我們可以看到，當我們使用加鎖的方法時，可以讀取到最新的數據，當使用快照的方法，我們雖然已經插入了記錄7，但是還是無法看到這條記錄，所以說：RR情況下，不加鎖，得到的數據就是舊的。
一點結論：
1.innodb中的已提交讀（Read committed） 和可重複讀（Repeatable read） 兩個級別是矛盾的。在同一個事務里，如果保證了可重複讀，就會看不到其他事務的提交，違背了提交讀；如果保證了已提交讀，就會導致前後兩次讀到的結果不一致，違背了可重複讀。所以針對這種情況可以使用加鎖去查詢最新的數據。
2.MySQL InnoDB的可重複讀並不保證避免幻讀，需要應用使用加鎖讀來保證。
3.四個級別逐漸增強，每個級別解決一個問題。事務級別越高,性能越差
4.未提交讀RU會導致臟讀的情況，已提交讀RC避免臟讀，但是會導致數據不一致，可重複讀保證了數據的一致性，但是不能避免幻讀。
