範例MySQL交易隔離等級以及髒讀取、幻讀、不可重複讀取

推薦（免費）：mysql影片教學

交易的隔離性

MySQL是一個客戶端/伺服器架構的軟體，對於同一個伺服器來說，可以有若干個客戶端與之連接，每個客戶端與伺服器連接上之後，就可以稱為一個會話（Session）。每個客戶端都可以在自己的會話中向伺服器發出請求語句，一個請求語句可能是某個事務的一部分，也就是對於伺服器來說可能同時處理多個事務。當資料庫上有多個事務同時執行的時候，就可能出現髒讀（Dirty Read）、不可重複讀（Non-Repeatable Read）、幻讀（Phantom Read）的問題，為了解決這些問題，就有了“隔離等級” 的概念。

理論上在某個事務對某個資料進行存取時，其他事務應該進行排隊，當該事務提交之後，其他事務才可以繼續存取這個資料。但一般情況下隔離得越嚴實，效率就會越低。因此很多時候，我們都要在隔離性和效率二者之間找一個平衡點。

交易並發執行遇到的問題

髒讀（Dirty Read）： 髒讀是指一個交易讀到了另一個未提交事務修改過的資料。

如小王的帳戶中有100的餘額，接下來有兩個事務對小王的帳戶進行存取。

會話A	會話B
#begin;
update xxx set balance = balance 50 where client_no = '小王客戶號碼' ;	begin;
	#select balance from xxx where client_no = '小王客戶號碼' ; （如果讀到150，則表示發生了髒讀）
#rollback;	commit;

#如上，會話A和會話B各開啟了一個事務，會話A先給小王帳戶餘額加了50，此時帳戶B查詢小王帳戶餘額為150，接下來會話A進行了回滾，那會話B查詢到的150就成一個不正確的髒數據。

無法重複讀取（Non-Repeatable Read）： 不可重複讀取是指在同一個交易內多次讀取相同資料集合，但查到的結果卻不相同。發生不可重複讀的原因是在多次搜尋期間查詢的資料被其它事務修改了。

看如下的兩個會話請求。

會話A	會話B
#begin;
select balance from xxx where client_no = '小王客戶號碼' ; （讀到餘額為100）	begin;
	update xxx set balance = balance 50 where client_no = '小王客戶號碼' ;
	#commit;
select balance from xxx where client_no = '小王客戶號碼' ; （如果讀到150，則表示發生了不可重複讀)
commit;

在會話A的同一個事務中，兩次相同查詢的結果不同，表示發生了不可重複讀。

幻讀（Phantom Read）： 所謂幻讀，指的是當某個事務在讀取某個範圍內的記錄時，另外一個事務又在該範圍內插入了新的記錄，當先前的事務再次讀取該範圍的記錄時，會讀取到先前沒有讀到的資料。

假如帳戶表中目前只有小王的餘額為100，再看下如下的兩個會話請求。

會話A	會話B
#begin;
select name from xxx where balance = 100 ; （讀到name為'小王'）	begin;
	#insert into xxx(client_no,name,balance) values('小客戶號','小張',100)；
	#commit;
select name from xxx where balance = 100 ; （如果讀到了'小王'和'小張'，則表示發生了幻讀）
#commit;

會話A事務中的第二次查詢，查到了第一次查詢沒有查到的 name ‘小張’，這就意味著出現了幻讀。

SQL標準制定的四種隔離等級

ISO 和ANIS SQL 標準制定了四種交易隔離等級的標準，分別為：讀取未提交（read uncommitted ）、讀取提交（read committed）、可重複讀取（repeatable read）和串行化（serializable ）。

我們先來看下這四種隔離等級的意思。

• 讀取未提交： 當一個交易還沒提交時，它所做的變更就能被別的交易看到。
• 讀取提交： 一個交易提交之後，它所做的變更才會被其他交易看到。
• 可重複讀取： 一個事務執行過程中看到的數據，總是跟著這個事務在啟動時看到的數據是一致的。當然在可重複讀取隔離等級下，未提交的變更對其他交易也是不可見的。
• 串行化： 顧名思義是對於同一行記錄，“寫”會加“寫鎖”，“讀”會加“讀鎖”。當出現讀寫鎖定衝突的時候，後存取的事務必須等前一個事務執行完成，才能繼續執行。

SQL 標準中規定，針對不同的隔離級別，並發事務可以發生不同嚴重程度的問題，具體情況如下：
（ √ 表示可以發生；× 表示不可以發生）

##√讀取提交（read committed）可重複讀取（repeatable read )#序列化（serializable ）

隔離等級	髒讀	無法重複讀取	幻讀
讀未提交（read uncommitted）	√	√
##×	√	#√
×	×	√
×	×	×

MySQL對四個隔離等級的支援情況

#ISO 和ANIS SQL 標準制定了四種交易隔離等級的標準，但不是所有資料庫廠商都遵循這些標準，例如Oracle 資料庫就不支援讀取未提交（read uncommitted）和可重複讀取（repeatable read）的交易隔離等級。

MySQL InnoDB 儲存引擎支援4種隔離級別，但與SQL 標準中定義的不同的是，

InnoDB 儲存引擎在預設的可重複讀取（repeatable read）事務隔離級別下，使用Next -Key Lock 鎖的演算法，避免了幻讀的產生

。也就是說 InnoDB 儲存引擎在可重複讀取（repeatable read）的事務隔離等級下，已經可以完全保證交易的隔離性要求，即達到了 SQL 標準中的串行化（serializable ）隔離等級的要求。

如何設定交易的隔離等級

在InnoDB 儲存引擎中，可以使用下列指令來設定全域或目前會話的交易隔離等級：

SET [GLOBAL|SESSION] TRANSACTION ISOLATION LEVEL{	READ UNCOMMITTED
	| READ COMMITTED
	| REPEATABLE READ
	| SERIALIZABLE}

如想設定當前會話的隔離級別為讀取提交，可以使用如下語句：

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

如果想在MySQL 資料庫啟動時就設定事務的預設隔離級別，那就需要修改設定檔中transaction- isolation 的值，比方說，我們在啟動前指定了

transaction-isolation = READ COMMITTED

，那麼事務的預設隔離等級就從原來的REPEATABLE READ 變成了READ COMMITTED。 查看當前會話的事務隔離級別，可以用如下語句：

SELECT @@transaction_isolation;

查看全域的事務隔離級別，可以使用以下語句：

SELECT @@global.transaction_isolation;

注意： transaction_isolation 是在MySQL 5.7.20 的版本中引入來替換tx_isolation的，如果你使用的是先前版本的MySQL，請將上述用到的transaction_isolation 的地方替換為tx_isolation 。

更多程式相關知識，請造訪：

程式設計影片

！ ！

以上是範例MySQL交易隔離等級以及髒讀取、幻讀、不可重複讀取的詳細內容。更多資訊請關注PHP中文網其他相關文章！