淺談mysql中不同交易隔離等級下資料的顯示效果

事務的概念

事務是一組原子性的SQL查詢語句，也可以被看做一個工作單元。如果資料庫引擎能夠成功地對資料庫應用所有的查詢語句，它就會執行所有查詢，如果任何一條查詢語句因為崩潰或其他原因而無法執行，那麼所有的語句就都不會執行。也就是說，事務內的語句要麼全部執行，要麼一句也不執行。

事務的特性：acid，也稱為事務的四個測試(原子性，一致性，隔離性，持久性)automicity：原子性，事務所引起的資料庫操作，要麼都完成，要麼都不執行
consisitency：一致性，事務執行前的總和和事務執行後的總和是不變的
isolation：隔離性， 某個事務的結果只有在完成之後才對其他事務可見
durability：持久性，一旦事務成功完成，系統必須保證任何故障都不會引起事務表現出不一致性

事務的狀態：
    活動
    部分提交
    失敗
    中止
    提交

事務在某一時刻，一定處於上邊五種狀態中的一種，事務各狀態之間的轉換如下所示：

650) this.width=650;" src="/e/u/themes/default/images/spacer.gif" style="url( /e/u/lang/zh-cn/images/localimage.png) no-repeat center center;border:1px solid #ddd" word_img="file:///C:/Users/ADMINI~1/AppData/Local/Temp/enhtmlclip/%E5%9B%BE%E5%83%8F%203.jpg" />650) this.width=650;" src="http://www.bkjia.com/uploads/allimg/131228/22293I421-0.jpg" title="映像 3.jpg" />

事務並發導致的問題髒讀(Drity Read)：某個事務已更新一份資料，另一個事務在此時讀取了同一份資料，由於某些原因，前一個RollBack了操作，則後一個事務所讀取的資料就會是不正確的。不可重複讀取(Non-repeatable read):在一個事務的兩次查詢之中資料不一致，這可能是兩次查詢過程中間插入了一個事務更新了原有的資料。

幻讀(Phantom Read):在一個事務的兩次查詢中資料不一致，例如有一個事務查詢了幾列(Row)資料，而另一個事務卻在此時插入了新的幾列資料，先前的事務在接下來的查詢中，就會發現有幾列資料是它先前所沒有的。

並發控制
多版本並發控制： Multiversion concurrency control，MVCC
    每個使用者操作資料時都是來源資料的時間快照，當使用者操作完成後，依據各快照的時間點在合并到來源資料中鎖：要想實現並發控制，最簡單的實現機制就是鎖(MVCC採用的不是鎖機制)。     讀鎖：共用鎖定，由讀表操作加上的鎖，加鎖後其他使用者只能擷取該表或行的共用鎖定，不能擷取排它鎖，也就是說只能讀不能寫     寫鎖：獨佔鎖，由寫表操作加上的鎖，加鎖後其他使用者不能擷取該表或行的任何鎖鎖粒度：從大到小，MySQL伺服器僅支援表級鎖，行鎖需要儲存引擎完成。     表鎖：鎖定某個表
    頁鎖：鎖定某個頁
    行鎖：鎖定某行

粒度越精細，並發性越好。即行鎖的並發性最好，但需要儲存引擎的支援。  

事務的四種隔離等級讀未提交(read uncommitted): 允許髒讀，也就是可能讀取到其他會話中未提交事務修改的資料讀提交(read committed): 只能讀取到已經提交的資料。oracle等多數資料庫預設都是該層級可重讀(repeatable read): 在同一個事務內的查詢都是事務開始時刻一致的，innodb的預設層級。在SQL標準中，該隔離等級消除了不可重複讀取，但是還存在幻象讀可串列(serializable): 完全序列化的讀，每次讀都需要獲得表級共用鎖定，讀寫相互都會阻塞在MySQL中，在並發控制情況下，不同隔離等級分別有可能產生問題如下所示：650) this.width=650;" src="/e/u/themes/default/images/spacer.gif" style="url( /e/u/lang/zh-cn/images/localimage.png) no-repeat center center;border:1px solid #ddd" word_img="file:///C:/Users/ADMINI~1/AppData/Local/Temp/enhtmlclip/%E5%9B%BE%E5%83%8F%202.jpg" />650) this.width=650;" src="http://www.bkjia.com/uploads/allimg/131228/22293H516-1.jpg" title="映像 2.jpg" />

上邊之所以介紹那麼多理論知識，是為了便於理解。在上邊的表格中已經列出來了，在不同隔離等級下，資料的顯示效果可能出現的問題，現在在linux上安裝好mysql，通過我們的實驗來一起看一下在不同隔離等級下資料的顯示效果吧。

實驗環境：linux系統：RedHat 5.8linux核心：linux-2.6.18-308.el5mysql版本：mysql-5.6.10-linux-glibc2.5-i686

本次實驗的所有操作均在虛擬機器中完成，通過Xmanager串連虛擬機器，然後開啟兩個會話串連，在兩個會話中，同時更改隔離等級，然後查看資料的顯示效果。

本次實驗中mysql採用源碼編譯安裝的方式安裝mysql，你也可以使用rpm包的方式直接安裝mysql。具體源碼安裝的方式及過程，這裡不再示範，在前面的部落格中，我已經介紹了很多次。如果你採用源碼編譯安裝的方式，不知道如何安裝mysql，可參看我以前寫的部落格，裡邊都有介紹。採用源碼編譯安裝的方式，在mysql的設定檔中，最好啟用每表一個資料表空間。這裡我們直接啟用。因為是實驗，這裡沒有對mysql設定密碼，因此，我們直接使用命令進入mysql。命令及顯示效果如下：

[root@mysql ~]# mysql -uroot -p       #使用該命令進入mysql，因為沒有設定密碼，在要求輸入密碼時直接按斷行符號鍵即可Enter password:Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 2Server version: 5.6.10 MySQL Community Server (GPL)Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> show variables like '%iso%';            #查看mysql預設的交易隔離等級，預設為可重讀。也可以使用select @@tx_isolation命令查看+-----------------+------------------+| Variable_name   | Value            |+-----------------+------------------+| tx_isolation    | REPEATABLE-READ  |          +-----------------+------------------+1 row in set (0.36 sec)mysql> show databases;    #查看系統已經存在的資料庫+---------------------+| Database            |+---------------------+| information_schema  || mysql               || performance_schema  || test                |+---------------------+4 rows in set (0.00 sec)現在匯入我們實驗所使用的資料庫。[root@mysql ~]# mysql < jiaowu.sql     #匯入實驗所用的jiaowu資料庫[root@mysql ~]# mysql -uroot -p      Enter password:Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 7Server version: 5.6.10 MySQL Community Server (GPL)Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> show databases;         #查看匯入的jiaowu資料庫是否存在+----------------------+| Database             |+----------------------+| information_schema   || jiaowu               || mysql                || performance_schema   || test                 |+----------------------+5 rows in set (0.01 sec)

我們在mysql命令介面下，沒有明確啟用事務時，輸入的每個命令都是直接提交的，因為mysql中有個變數的值，可實現自動認可。也就是說我們每輸入一個語句，都會自動認可，這會產生大量的磁碟IO，降低系統的效能。在我們做實驗時，因為我們要明確使用事務，所以，建議關閉自動認可的功能，如果不關閉也沒有關係，但是如果你沒有明確使用事務，想要做下邊的實驗，那就需要關閉此功能了。這裡，我們明確使用事務，且關閉自動認可功能。假如你關閉了自動認可功能，需明確使用事務，否則你輸入的所有語句會被當成一個事務進行處理。命令如下：

mysql> select @@autocommit;        #查看該值，為1表示啟動自動認可+--------------+| @@autocommit |+--------------+|      1       |+--------------+1 row in set (0.00 sec)mysql> set autocommit=0;          #關閉自動認可功能Query OK, 0 rows affected (0.00 sec)mysql> select @@autocommit;     #重新查看該值，為0表示關閉自動認可功能+--------------+| @@autocommit |+--------------+|       0      |+--------------+1 row in set (0.00 sec)

現在開啟兩個會話，在這兩個會話中分別進入mysql，首先記得要就修改兩個回話中的autocommit變數，關閉自動認可功能，然後查看事務的隔離等級，預設為REPEATABLE-READ。在兩個會話中都需要修改隔離等級。我們先從最低的隔離等級開始示範。

mysql> select @@tx_isolation;+-------------------+| @@tx_isolation    |+-------------------+| REPEATABLE-READ   |+-------------------+1 row in set (0.00 sec)mysql> set tx_isolation='read-uncommitted';    #修改隔離等級，將隔離等級可重讀改為讀未提交Query OK, 0 rows affected (0.00 sec)mysql> select @@tx_isolation;+-------------------+| @@tx_isolation    |+-------------------+| READ-UNCOMMITTED  |+-------------------+在兩個回話中，修改完隔離等級後，使用匯入的資料庫，用tutors表來驗證顯示效果。mysql> use jiaowu;           #使用jaiowu資料庫Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> show tables;       #查看該資料庫中都有那些表+-------------------+| Tables_in_jiaowu  |+-------------------+| courses           || scores            || students          || tutors            |+-------------------+4 rows in set (0.00 sec)

在會話1中，我們來修改tutors中的資料，在會話2中我們來查看資料，看會是什麼情況。本打算使用tutors表來示範下邊的實驗，但修改完資料時比較麻煩，所以，自己就寫了個指令碼，比著tutors表的各欄位建立了一個新表teachers。指令碼寫的有點拙劣，有興趣可自己動手寫個更好的指令碼來實現建立及插入資料。建立表及插入資料的指令碼如下：

#!/bin/bash##Author: hulunbeier, http://lq2419.blog.51cto.com/#Description: creating table and inserting data#let B=0mysql -e "use jiaowu;create table teachers like tutors;"read -p "Input a number to create NUMBER data. You choice : "  NUM          #執行該指令碼是，會讓輸入一個數字，因為是實驗，所以我們這裡進插入5行資料，讀者可自行修改for I in `seq 1 $NUM`; do    NAME=tech$I    A=`echo $RANDOM/365 | bc`     until [ $A -ge 40 ] && [ $A -le 100 ]; do          A=`echo $RANDOM/365 | bc`     done     B=`echo $RANDOM%2 | bc`    if [ $B = 0 ]; then        GD=F    else        GD=M    fi    mysql -e "insert into jiaowu.teachers (Tname,Gender,Age) values ('$NAME','$GD',$A);"     echo "create tech$I success."done

執行上邊的指令碼即可建立相應表及插入資料。查看下我們建立的新表是否成功，裡邊是否有資料。查詢命令及顯示結果如下所示：

650) this.width=650;" src="http://www.bkjia.com/uploads/allimg/131228/22293H536-2.jpg" title="映像 4.jpg" />

建立的新表已經存在，且插入資料也已成功，現在我們就用teachers表來示範以下各實驗。示範實驗從低隔離等級開始，到高隔離等級結束。

Read-uncommitted：讀未提交

首先，修改兩個會話中的自動認可功能，將其關閉，然後修改系統預設的隔離等級，從低層級開始，將預設的可重讀改為讀未提交。

650) this.width=650;" src="http://www.bkjia.com/uploads/allimg/131228/22293I0L-3.jpg" title="映像 5.jpg" />

以上修改在兩個會話中完成後，我們明確啟動事務，查詢下表中的所有資料資訊，顯示TID為5的老師的年齡為61，然後在會話1中更新teachers表中TID為5的老師的年齡，將原來的61改為50，接著，在兩個會話中在重新查詢下所有的資料，看TID為5的老師的年齡是多少。命令及顯示效果如下所示：

650) this.width=650;" src="http://www.bkjia.com/uploads/allimg/131228/22293MG9-4.jpg" title="映像 6.jpg" />

當我們在會話1中使用rollback復原之後，再在兩個會話中查看資料，發現還是61。從上邊兩個會話的顯示效果，可以看到，在隔離等級為讀未提交時，當我們開啟一個事務時，在該事務中修改了某個資料行的資訊，且在該事務中，並未提交，但在另一個事務中，如果都是對同一個資料集的操作，會發現我們前後兩次查詢的結果不一樣了，在同一個事務中，兩次查詢得到的結果不一樣，這種情況是不允許出現。此時就出現了髒讀、不可重複讀取及幻讀的現象。

Read-committed：讀提交首先在上邊修改的基礎上再次修改隔離等級，將讀未提交改為讀提交。然後，我們還去修改TID為5的老師的年齡，將61改為40。接著，在兩個會話中再次查看顯示效果。命令及顯示效果如下所示：650) this.width=650;" src="http://www.bkjia.com/uploads/allimg/131228/22293K153-5.jpg" style="cursor:default;" title="映像 7.jpg" />

現在，在會話1中，我們使用commit命令提交事務，然後再會話2中，查看下顯示效果，看又是怎麼樣的。結果會發現，在會話2中，TID為5的老師的年齡變成了40。從上邊的顯示效果，會發現，當隔離等級為read-committed(讀提交)時，當我們在會話1中開啟事務，並修改了某一行資料的資訊時，在會話1中可以看到修改後的效果，但在會話2中並不會看到修改後的結果。當我們在會話1中提交事務後，在再會話2中查詢，會發現，跟我們上次查詢的不一樣了，顯示的是會話1修改後的結果。在該隔離等級下，雖然可以避免髒讀的現象發生，但還是會出現不可重複讀取和幻讀的現象。

Repeatable-read：可重讀首先修改隔離等級，將讀提交改為可重讀。然後，在會話1中，依然修改TID為5的老師的年齡，將其年齡改為60。最後，兩個會話中再來查看結果。650) this.width=650;" src="http://www.bkjia.com/uploads/allimg/131228/22293K1U-6.jpg" style="cursor:default;" title="映像 8.jpg" />650) this.width=650;" src="http://www.bkjia.com/uploads/allimg/131228/22293JR3-7.jpg" title="映像 10.jpg" />

從上邊的顯示結果會發現，在該隔離等級下，當我們在會話1中修改了某個值時，會話1會立即顯示修改後的結果，而會話2中不會顯示。當我們在會話1中提交事務後，得到永久結果，在會話1中在查看，還是修改後的結果，但在會話2中，還是原來的結果。但當我們在會話2中提交事務後，再來查詢，發現是會話1中修改後的結果，在會話2中，我們沒做任何修改，我一提交事務，發現，資料竟然變了。起碼，事務提交前和提交後看到的資料是不一樣的。此時就出現了幻讀的現象。

Serializable：可串列首先，我們依然修改隔離等級，將可重讀改為可串列。然後再會話1中，啟動事務，並將TID為5的老師的年齡由60改為100，然後，在會話1和2中查看。在沒有啟動事務前，我們先來看下TID為5的老師年齡是多少。650) this.width=650;" src="http://www.bkjia.com/uploads/allimg/131228/22293J318-8.jpg" style="cursor:default;" title="映像 17.jpg" />

在上邊的顯示效果中，發現，在可串列隔離等級下，當我們啟動兩個事務時，如果在其中一個事務中，修改了某個資料行，在另一個事務中，我們是無法查詢到該資料集的資訊的，也就是說系統不會顯示出任何資訊，除非在修改的事務中，我們提交了，或者是執行了rollback命令。假如在修改的事務中，我們既沒有執行commit命令提交，也沒有執行rollback命令復原，那麼，在另一個事務中，當我們查詢時，會一直卡著不動，直到鎖時間逾時，然後提示我們重新開啟事務。在中，發現，在會話1中，當我們啟動一個事務，並修改了一個資料後，在會話2中，我們是不能查詢到任何資訊的，當我們在會話1中執行了rollback命令後，會話2中才會顯示查詢結果，此時的查詢所用時間會比以前查詢所用時間長很多。因為在可串列層級下，是不允許通知開啟多個事務的，或者說是不允許對同一個資料集執行任何操作的。此時，既不會出現髒讀、不可重複讀取，也不會出現幻讀現象。但是此時的並發性會受影響。

綜上所述，在低隔離等級下，當有多個事務並發執行時，雖然會產生很多問題，如髒讀、不可重複讀取、幻讀等現象，但事務的並發性較好，可同時執行多個事務；在高隔離等級下，當有多個事務並發執行時，因在高隔離等級下，不支援多事務並發執行，雖然不會出現諸如髒讀、不可重複讀取及幻讀等現象，但並發性較低。InnoDB預設的隔離等級是repeatable-read(可重讀)，而在大多數的資料庫中，oracle等多數資料庫，一般預設的隔離等級是read-committed(讀提交)。一般來說在實際應用中，除了在銀行、股票等對資料安全要求較高的情境外，必須使用較高隔離等級外，其他對資料要求不高的場合，可採用低隔離等級，以提高並發性。然而，究竟哪種隔離等級更適合，那就需要看你對資料的安全性要求有多高了。

本文出自 “呼倫貝爾—寫在人生路上” 部落格，請務必保留此出處http://lq2419.blog.51cto.com/1365130/1226000