mysql中不同事務(wù)隔離級(jí)別下數(shù)據(jù)的顯示效果

2013/6/20 22:04:00  出處：本站原創(chuàng)   人氣：589次    字號(hào)：小  中  大

事務(wù)是一組原子性的SQL查詢語句，也可以被看做一個(gè)工作單元。如果數(shù)據(jù)庫引擎能夠成功地對(duì)數(shù)據(jù)庫應(yīng)用所有的查詢語句，它就會(huì)執(zhí)行所有查詢，如果任何一條查詢語句因?yàn)楸罎⒒蚱渌�原因而無法執(zhí)行，那么所有的語句就都不會(huì)執(zhí)行。也就是說，事務(wù)內(nèi)的語句要么全部執(zhí)行，要么一句也不執(zhí)行。

事務(wù)的特性：acid，也稱為事務(wù)的四個(gè)測(cè)試(原子性，一致性，隔離性，持久性)

automicity：原子性，事務(wù)所引起的數(shù)據(jù)庫操作，要么都完成，要么都不執(zhí)行

consisitency：一致性，事務(wù)執(zhí)行前的總和和事務(wù)執(zhí)行后的總和是不變的

isolation：隔離性， 某個(gè)事務(wù)的結(jié)果只有在完成之后才對(duì)其他事務(wù)可見

durability：持久性，一旦事務(wù)成功完成，系統(tǒng)必須保證任何故障都不會(huì)引起事務(wù)表現(xiàn)出不一致性

事務(wù)的狀態(tài)：

活動(dòng)

部分提交

失敗

中止

提交

事務(wù)在某一時(shí)刻，一定處于上邊五種狀態(tài)中的一種，事務(wù)各狀態(tài)之間的轉(zhuǎn)換如下所示：

事務(wù)并發(fā)導(dǎo)致的問題

臟讀(Drity Read)：某個(gè)事務(wù)已更新一份數(shù)據(jù)，另一個(gè)事務(wù)在此時(shí)讀取了同一份數(shù)據(jù)，由于某些原因，前一個(gè)RollBack了操作，則后一個(gè)事務(wù)所讀取的數(shù)據(jù)就會(huì)是不正確的。

不可重復(fù)讀(Non-repeatable read):在一個(gè)事務(wù)的兩次查詢之中數(shù)據(jù)不一致，這可能是兩次查詢過程中間插入了一個(gè)事務(wù)更新了原有的數(shù)據(jù)。

幻讀(Phantom Read):在一個(gè)事務(wù)的兩次查詢中數(shù)據(jù)不一致，例如有一個(gè)事務(wù)查詢了幾列(Row)數(shù)據(jù)，而另一個(gè)事務(wù)卻在此時(shí)插入了新的幾列數(shù)據(jù)，先前的事務(wù)在接下來的查詢中，就會(huì)發(fā)現(xiàn)有幾列數(shù)據(jù)是它先前所沒有的。

并發(fā)控制

多版本并發(fā)控制： Multiversion concurrency control，MVCC

每個(gè)用戶操作數(shù)據(jù)時(shí)都是源數(shù)據(jù)的時(shí)間快照，當(dāng)用戶操作完成后，依據(jù)各快照的時(shí)間點(diǎn)在合并到源數(shù)據(jù)中

鎖：要想實(shí)現(xiàn)并發(fā)控制，最簡(jiǎn)單的實(shí)現(xiàn)機(jī)制就是鎖(MVCC采用的不是鎖機(jī)制)。

讀鎖：共享鎖，由讀表操作加上的鎖，加鎖后其他用戶只能獲取該表或行的共享鎖，不能獲取排它鎖，也就是說只能讀不能寫

寫鎖：獨(dú)占鎖，由寫表操作加上的鎖，加鎖后其他用戶不能獲取該表或行的任何鎖

鎖粒度：從大到小，MySQL服務(wù)器僅支持表級(jí)鎖，行鎖需要存儲(chǔ)引擎完成。

表鎖：鎖定某個(gè)表

頁鎖：鎖定某個(gè)頁

行鎖：鎖定某行

粒度越精細(xì)，并發(fā)性越好。即行鎖的并發(fā)性最好，但需要存儲(chǔ)引擎的支持。

事務(wù)的四種隔離級(jí)別

讀未提交(read uncommitted): 允許臟讀，也就是可能讀取到其他會(huì)話中未提交事務(wù)修改的數(shù)據(jù)

讀提交(read committed): 只能讀取到已經(jīng)提交的數(shù)據(jù)。oracle等多數(shù)數(shù)據(jù)庫默認(rèn)都是該級(jí)別

可重讀(repeatable read): 在同一個(gè)事務(wù)內(nèi)的查詢都是事務(wù)開始時(shí)刻一致的，innodb的默認(rèn)級(jí)別。在SQL標(biāo)準(zhǔn)中，該隔離級(jí)別消除了不可重復(fù)讀，但是還存在幻象讀

可串行(serializable): 完全串行化的讀，每次讀都需要獲得表級(jí)共享鎖，讀寫相互都會(huì)阻塞

在MySQL中，在并發(fā)控制情況下，不同隔離級(jí)別分別有可能產(chǎn)生問題如下所示：

上邊之所以介紹那么多理論知識(shí)，是為了便于理解。在上邊的表格中已經(jīng)列出來了，在不同隔離級(jí)別下，數(shù)據(jù)的顯示效果可能出現(xiàn)的問題，現(xiàn)在在linux上安裝好mysql，通過我們的實(shí)驗(yàn)來一起看一下在不同隔離級(jí)別下數(shù)據(jù)的顯示效果吧。

實(shí)驗(yàn)環(huán)境：

linux系統(tǒng)：RedHat 5.8

linux內(nèi)核：linux-2.6.18-308.el5

mysql版本：mysql-5.6.10-linux-glibc2.5-i686

本次實(shí)驗(yàn)的所有操作均在虛擬機(jī)中完成，通過Xmanager連接虛擬機(jī)，然后打開兩個(gè)會(huì)話連接，在兩個(gè)會(huì)話中，同時(shí)更改隔離級(jí)別，然后查看數(shù)據(jù)的顯示效果。

本次實(shí)驗(yàn)中mysql采用源碼編譯安裝的方式安裝mysql，你也可以使用rpm包的方式直接安裝mysql。具體源碼安裝的方式及過程，這里不再演示，在前面的博客中，我已經(jīng)介紹了很多次。如果你采用源碼編譯安裝的方式，不知道如何安裝mysql，可參看我以前寫的博客，里邊都有介紹。采用源碼編譯安裝的方式，在mysql的配置文件中，最好啟用每表一個(gè)表空間。這里我們直接啟用。

因?yàn)槭菍?shí)驗(yàn)，這里沒有對(duì)mysql設(shè)置密碼，因此，我們直接使用命令進(jìn)入mysql。命令及顯示效果如下：

50[root@mysql ~]# mysql -uroot -p #使用該命令進(jìn)入mysql，因?yàn)闆]有設(shè)置密碼，在要求輸入密碼時(shí)直接按回車鍵即可

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 2

Server 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 its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show variables like '%iso%'; #查看mysql默認(rèn)的事務(wù)隔離級(jí)別，默認(rèn)為可重讀。也可以使用select @@tx_isolation命令查看

+-----------------+------------------+

| Variable_name | Value |

+-----------------+------------------+

| tx_isolation | REPEATABLE-READ |

+-----------------+------------------+

1 row in set (0.36 sec)

mysql> show databases; #查看系統(tǒng)已經(jīng)存在的數(shù)據(jù)庫

+---------------------+

| Database |

+---------------------+

| information_schema |

| mysql |

| performance_schema |

| test |

+---------------------+

4 rows in set (0.00 sec)

現(xiàn)在導(dǎo)入我們實(shí)驗(yàn)所使用的數(shù)據(jù)庫。

[root@mysql ~]# mysql < jiaowu.sql #導(dǎo)入實(shí)驗(yàn)所用的jiaowu數(shù)據(jù)庫

[root@mysql ~]# mysql -uroot -p

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 7

Server 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 its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases; #查看導(dǎo)入的jiaowu數(shù)據(jù)庫是否存在

+----------------------+

| Database |

+----------------------+

| information_schema |

| jiaowu |

| mysql |

| performance_schema |

| test |

+----------------------+

5 rows in set (0.01 sec)

我們?cè)趍ysql命令界面下，沒有明確啟用事務(wù)時(shí)，輸入的每個(gè)命令都是直接提交的，因?yàn)閙ysql中有個(gè)變量的值，可實(shí)現(xiàn)自動(dòng)提交。也就是說我們每輸入一個(gè)語句，都會(huì)自動(dòng)提交，這會(huì)產(chǎn)生大量的磁盤IO，降低系統(tǒng)的性能。在我們做實(shí)驗(yàn)時(shí)，因?yàn)槲覀円�明確使用事務(wù)，所以，建議關(guān)閉自動(dòng)提交的功能，如果不關(guān)閉也沒有關(guān)系，但是如果你沒有明確使用事務(wù)，想要做下邊的實(shí)驗(yàn)，那就需要關(guān)閉此功能了。這里，我們明確使用事務(wù)，且關(guān)閉自動(dòng)提交功能。假如你關(guān)閉了自動(dòng)提交功能，需明確使用事務(wù)，否則你輸入的所有語句會(huì)被當(dāng)成一個(gè)事務(wù)進(jìn)行處理。命令如下：

16mysql> select @@autocommit; #查看該值，為1表示啟動(dòng)自動(dòng)提交

+--------------+

| @@autocommit |

+--------------+

| 1 |

+--------------+

1 row in set (0.00 sec)

mysql> set autocommit=0; #關(guān)閉自動(dòng)提交功能

Query OK, 0 rows affected (0.00 sec)

mysql> select @@autocommit; #重新查看該值，為0表示關(guān)閉自動(dòng)提交功能

+--------------+

| @@autocommit |

+--------------+

| 0 |

+--------------+

1 row in set (0.00 sec)

現(xiàn)在打開兩個(gè)會(huì)話，在這兩個(gè)會(huì)話中分別進(jìn)入mysql，首先記得要就修改兩個(gè)回話中的autocommit變量，關(guān)閉自動(dòng)提交功能，然后查看事務(wù)的隔離級(jí)別，默認(rèn)為REPEATABLE-READ。在兩個(gè)會(huì)話中都需要修改隔離級(jí)別。我們先從最低的隔離級(jí)別開始演示。

30mysql> select @@tx_isolation;

+-------------------+

| @@tx_isolation |

+-------------------+

| REPEATABLE-READ |

+-------------------+

1 row in set (0.00 sec)

mysql> set tx_isolation='read-uncommitted'; #修改隔離級(jí)別，將隔離級(jí)別可重讀改為讀未提交

Query OK, 0 rows affected (0.00 sec)

mysql> select @@tx_isolation;

+-------------------+

| @@tx_isolation |

+-------------------+

| READ-UNCOMMITTED |

+-------------------+

在兩個(gè)回話中，修改完隔離級(jí)別后，使用導(dǎo)入的數(shù)據(jù)庫，用tutors表來驗(yàn)證顯示效果。

mysql> use jiaowu; #使用jaiowu數(shù)據(jù)庫

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

mysql> show tables; #查看該數(shù)據(jù)庫中都有那些表

+-------------------+

| Tables_in_jiaowu |

+-------------------+

| courses |

| scores |

| students |

| tutors |

+-------------------+

4 rows in set (0.00 sec)

在會(huì)話1中，我們來修改tutors中的數(shù)據(jù)，在會(huì)話2中我們來查看數(shù)據(jù)，看會(huì)是什么情況。本打算使用tutors表來演示下邊的實(shí)驗(yàn)，但修改完數(shù)據(jù)截圖時(shí)比較麻煩，所以，自己就寫了個(gè)腳本，比著tutors表的各字段創(chuàng)建了一個(gè)新表teachers。腳本寫的有點(diǎn)拙劣，有興趣可自己動(dòng)手寫個(gè)更好的腳本來實(shí)現(xiàn)創(chuàng)建及插入數(shù)據(jù)。創(chuàng)建表及插入數(shù)據(jù)的腳本如下：

23#!/bin/bash

#

#Author: hulunbeier, http://lq2419.blog.51cto.com/

#Description: creating table and inserting data

#

let B=0

mysql -e "use jiaowu;create table teachers like tutors;"

read -p "Input a number to create NUMBER data. You choice : " NUM #執(zhí)行該腳本是，會(huì)讓輸入一個(gè)數(shù)字，因?yàn)槭菍?shí)驗(yàn)，所以我們這里進(jìn)插入5行數(shù)據(jù)，讀者可自行修改

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

執(zhí)行上邊的腳本即可創(chuàng)建相應(yīng)表及插入數(shù)據(jù)。查看下我們創(chuàng)建的新表是否成功，里邊是否有數(shù)據(jù)。查詢命令及顯示結(jié)果如下所示：

創(chuàng)建的新表已經(jīng)存在，且插入數(shù)據(jù)也已成功，現(xiàn)在我們就用teachers表來演示以下各實(shí)驗(yàn)。演示實(shí)驗(yàn)從低隔離級(jí)別開始，到高隔離級(jí)別結(jié)束。

Read-uncommitted：讀未提交

首先，修改兩個(gè)會(huì)話中的自動(dòng)提交功能，將其關(guān)閉，然后修改系統(tǒng)默認(rèn)的隔離級(jí)別，從低級(jí)別開始，將默認(rèn)的可重讀改為讀未提交。

以上修改在兩個(gè)會(huì)話中完成后，我們明確啟動(dòng)事務(wù)，查詢下表中的所有數(shù)據(jù)信息，顯示TID為5的老師的年齡為61，然后在會(huì)話1中更新teachers表中TID為5的老師的年齡，將原來的61改為50，接著，在兩個(gè)會(huì)話中在重新查詢下所有的數(shù)據(jù)，看TID為5的老師的年齡是多少。命令及顯示效果如下所示：

當(dāng)我們?cè)跁?huì)話1中使用rollback回滾之后，再在兩個(gè)會(huì)話中查看數(shù)據(jù)，發(fā)現(xiàn)還是61。從上邊兩個(gè)會(huì)話的顯示效果，可以看到，在隔離級(jí)別為讀未提交時(shí)，當(dāng)我們開啟一個(gè)事務(wù)時(shí)，在該事務(wù)中修改了某個(gè)數(shù)據(jù)行的信息，且在該事務(wù)中，并未提交，但在另一個(gè)事務(wù)中，如果都是對(duì)同一個(gè)數(shù)據(jù)集的操作，會(huì)發(fā)現(xiàn)我們前后兩次查詢的結(jié)果不一樣了，在同一個(gè)事務(wù)中，兩次查詢得到的結(jié)果不一樣，這種情況是不允許出現(xiàn)。此時(shí)就出現(xiàn)了臟讀、不可重復(fù)讀及幻讀的現(xiàn)象。

Read-committed：讀提交

首先在上邊修改的基礎(chǔ)上再次修改隔離級(jí)別，將讀未提交改為讀提交。然后，我們還去修改TID為5的老師的年齡，將61改為40。接著，在兩個(gè)會(huì)話中再次查看顯示效果。命令及顯示效果如下所示：

現(xiàn)在，在會(huì)話1中，我們使用commit命令提交事務(wù)，然后再會(huì)話2中，查看下顯示效果，看又是怎么樣的。結(jié)果會(huì)發(fā)現(xiàn)，在會(huì)話2中，TID為5的老師的年齡變成了40。從上邊的顯示效果，會(huì)發(fā)現(xiàn)，當(dāng)隔離級(jí)別為read-committed(讀提交)時(shí)，當(dāng)我們?cè)跁?huì)話1中開啟事務(wù)，并修改了某一行數(shù)據(jù)的信息時(shí)，在會(huì)話1中可以看到修改后的效果，但在會(huì)話2中并不會(huì)看到修改后的結(jié)果。當(dāng)我們?cè)跁?huì)話1中提交事務(wù)后，在再會(huì)話2中查詢，會(huì)發(fā)現(xiàn)，跟我們上次查詢的不一樣了，顯示的是會(huì)話1修改后的結(jié)果。在該隔離級(jí)別下，雖然可以避免臟讀的現(xiàn)象發(fā)生，但還是會(huì)出現(xiàn)不可重復(fù)讀和幻讀的現(xiàn)象。

Repeatable-read：可重讀

首先修改隔離級(jí)別，將讀提交改為可重讀。然后，在會(huì)話1中，依然修改TID為5的老師的年齡，將其年齡改為60。最后，兩個(gè)會(huì)話中再來查看結(jié)果。

從上邊的顯示結(jié)果會(huì)發(fā)現(xiàn)，在該隔離級(jí)別下，當(dāng)我們?cè)跁?huì)話1中修改了某個(gè)值時(shí)，會(huì)話1會(huì)立即顯示修改后的結(jié)果，而會(huì)話2中不會(huì)顯示。當(dāng)我們?cè)跁?huì)話1中提交事務(wù)后，得到永久結(jié)果，在會(huì)話1中在查看，還是修改后的結(jié)果，但在會(huì)話2中，還是原來的結(jié)果。但當(dāng)我們?cè)跁?huì)話2中提交事務(wù)后，再來查詢，發(fā)現(xiàn)是會(huì)話1中修改后的結(jié)果，在會(huì)話2中，我們沒做任何修改，我一提交事務(wù)，發(fā)現(xiàn)，數(shù)據(jù)竟然變了。起碼，事務(wù)提交前和提交后看到的數(shù)據(jù)是不一樣的。此時(shí)就出現(xiàn)了幻讀的現(xiàn)象。

Serializable：可串行

首先，我們依然修改隔離級(jí)別，將可重讀改為可串行。然后再會(huì)話1中，啟動(dòng)事務(wù)，并將TID為5的老師的年齡由60改為100，然后，在會(huì)話1和2中查看。在沒有啟動(dòng)事務(wù)前，我們先來看下TID為5的老師年齡是多少。

在上邊的顯示效果中，發(fā)現(xiàn)，在可串行隔離級(jí)別下，當(dāng)我們啟動(dòng)兩個(gè)事務(wù)時(shí)，如果在其中一個(gè)事務(wù)中，修改了某個(gè)數(shù)據(jù)行，在另一個(gè)事務(wù)中，我們是無法查詢到該數(shù)據(jù)集的信息的，也就是說系統(tǒng)不會(huì)顯示出任何信息，除非在修改的事務(wù)中，我們提交了，或者是執(zhí)行了rollback命令。假如在修改的事務(wù)中，我們既沒有執(zhí)行commit命令提交，也沒有執(zhí)行rollback命令回滾，那么，在另一個(gè)事務(wù)中，當(dāng)我們查詢時(shí)，會(huì)一直卡著不動(dòng)，直到鎖時(shí)間超時(shí)，然后提示我們重新開啟事務(wù)。在上圖中，發(fā)現(xiàn)，在會(huì)話1中，當(dāng)我們啟動(dòng)一個(gè)事務(wù)，并修改了一個(gè)數(shù)據(jù)后，在會(huì)話2中，我們是不能查詢到任何信息的，當(dāng)我們?cè)跁?huì)話1中執(zhí)行了rollback命令后，會(huì)話2中才會(huì)顯示查詢結(jié)果，此時(shí)的查詢所用時(shí)間會(huì)比以前查詢所用時(shí)間長(zhǎng)很多。因?yàn)樵诳纱�行�?jí)別下，是不允許通知開啟多個(gè)事務(wù)的，或者說是不允許對(duì)同一個(gè)數(shù)據(jù)集執(zhí)行任何操作的。此時(shí)，既不會(huì)出現(xiàn)臟讀、不可重復(fù)讀，也不會(huì)出現(xiàn)幻讀現(xiàn)象。但是此時(shí)的并發(fā)性會(huì)受影響。

綜上所述，在低隔離級(jí)別下，當(dāng)有多個(gè)事務(wù)并發(fā)執(zhí)行時(shí)，雖然會(huì)產(chǎn)生很多問題，如臟讀、不可重復(fù)讀、幻讀等現(xiàn)象，但事務(wù)的并發(fā)性較好，可同時(shí)執(zhí)行多個(gè)事務(wù)；在高隔離級(jí)別下，當(dāng)有多個(gè)事務(wù)并發(fā)執(zhí)行時(shí)，因在高隔離級(jí)別下，不支持多事務(wù)并發(fā)執(zhí)行，雖然不會(huì)出現(xiàn)諸如臟讀、不可重復(fù)讀及幻讀等現(xiàn)象，但并發(fā)性較低。InnoDB默認(rèn)的隔離級(jí)別是repeatable-read(可重讀)，而在大多數(shù)的數(shù)據(jù)庫中，oracle等多數(shù)數(shù)據(jù)庫，一般默認(rèn)的隔離級(jí)別是read-committed(讀提交)。一般來說在實(shí)際應(yīng)用中，除了在銀行、股票等對(duì)數(shù)據(jù)安全要求較高的場(chǎng)景外，必須使用較高隔離級(jí)別外，其他對(duì)數(shù)據(jù)要求不高的場(chǎng)合，可采用低隔離級(jí)別，以提高并發(fā)性。然而，究竟哪種隔離級(jí)別更適合，那就需要看你對(duì)數(shù)據(jù)的安全性要求有多高了。