淺談MySQL的交易隔離等級

標籤：logs   完成   結束   取資料   共用   區分   自動認可   cti   交易隔離等級   

　　希望這篇文章能夠闡述清楚跟資料庫相關的四個概念：事務、資料庫讀現象、隔離等級、鎖機制

一、事務

先來看下百度百科對資料庫事務的定義：

　　作為單個邏輯單元執行一系列操作，要麼完全執行，要麼完全不執行。交易處理可以確保除非事務性單元內的所有操作都成功完成，否則不會永久更新面向資料的資源。

事務有四個屬性，稱為ACID屬性：

1、原子性(Atomicity):事務是一個原子單位，要麼全部執行，要麼全部不執行。

2、一致性(Consistent):事務的開始和結束，資料都必須保持一致狀態。

3、隔離性(isolation):資料庫系統提供隔離機制，保證並發事務之間是互相不干擾的。也就意味著交易處理過程中的中間狀態對其他的事務是透明的。

4、持久性(Durable):事務完成之後，對資料的修改是永久性的，即使出現系統故障也能夠保持

 

事務是一系列SQL語句的集合，如果沒有事務，會出現什麼問題？或者說SQL只能一條一條的單個執行，會出現什麼問題？

這個很簡單，如果沒有事務，我們平時生活中的銀行轉賬就無法操作。

二、資料庫讀現象

　　ACID屬性裡面有一個是隔離等級，即並發事務之間互相不干擾。互相不干擾只是一個終極狀態，且需要消耗巨大的效能。在我們實際應用過程中，是存在很大的灰階空間的：隔離等級有程度的區分。所以如果隔離程度控制的比較弱的話，就會產生髒讀、不可重複讀取以及幻讀的現象。

1、髒讀

事務T1修改某個欄位的值，然後事務T2讀取該值，此後T1撤銷了對該欄位的更新，或者更新成另外的值才commit到資料庫中，這樣T2讀取的資料是無效的或者錯誤的。導致T2依據髒資料所做的操作也是錯誤的。

---------思聰同學中午去食堂吃飯，看到窗邊的座位被如花同學佔有了，思聰認為這個座位已經被佔有了，就轉身去找其他的座位。不料，如花同學起身離開了。事實是：如花並不是吃飯，而是臨時坐在那裡等她的約會對象，只是臨時小坐一會，並沒有真正“commit”。

2、不可重複讀取

在資料庫訪問中，一個事務範圍內的兩次相同的查詢卻返回了不同的資料。

事務T1讀取某一資料，事務T2讀取並修改了該資料，T1為了對讀取值進行驗證而重新讀取，卻發現得到了不同的結果。

---------思聰同學中午去食堂吃飯，看到窗邊的座位是空的，便屁顛屁顛的跑去打飯，回來後卻發現這個座位被如花同學搶去了。

3、幻讀

幻讀解決了不可重複讀取的問題，即在同一個事務範圍內，兩次相同的查詢結果是相同的。但是可以新增表中的資料記錄。

幻讀是指事務T1對錶中的資料進行修改，假設修改涉及了表中全部的資料行，同時第二個事務也修改這個表中的資料，這種修改是向表中插入一條新的資料。後面就會出現操作了T1事務的使用者發現表中還有沒有修改的資料行，彷彿出現了幻覺一樣。

--------思聰同學中午去食堂吃飯，看到窗邊的座位是空的，便屁顛屁顛的跑去打飯，回來後窗邊的座位還是空的，便很高興坐上去準備開始吃飯，這時候卻發現如花同學搬了一個小板凳坐在旁邊狼吞虎咽，思聰頓時沒有了胃口。

如果需要解決髒讀、不可重複讀取、幻讀等這些資料庫讀現象，就必須相應提高事務的隔離等級。但是資料庫的隔離等級越高，對應的並發能力就越弱，效能也就相應的越差，所以我們還需根據具體的應用情境去權衡。

三、交易隔離等級1、未提交讀

事務的最低隔離等級，在這種隔離等級下，一個事務可以讀取另外一個事務未提交的資料。

資料庫鎖實現原理：

事務T在讀資料的時候並未對資料進行加鎖，事務T在修改資料的時候對資料增加行級共用鎖定

T1在讀取資料時，T2可以對相同資料進行讀取、修改。因為T1沒有進行任何鎖操作；當T2對記錄進行修改時，T1再次讀取資料可以讀取到T2修改後的資料。因為T2對資料進行修改只增加了行級共用鎖定，T1可以再增加共用讀鎖進行資料讀取（儘管T2沒有提交事務）

如上所述，這種隔離等級，會導致髒讀現象

2、已提交讀

在一個事務修改資料過程中，如果事務沒有進行提交，其他事務不能讀取該資料

資料庫鎖實現原理：

事務T在讀取資料時增加行級共用鎖定，讀取一旦結束，立即釋放；事務T在修改資料時增加行級獨佔鎖定，直到事務結束才釋放。

T1在讀取資料的過程中，T2也可以對相同資料進行讀取，但是不能進行修改（T1增加的是共用鎖定，T2也可以增加共用鎖定，但是不能增加獨佔鎖定）。T1讀取結束後，會立即釋放共用鎖定，這時T2可以增加獨佔鎖定，對資料進行修改，而此時T1既不能對資料進行讀取也不能進行修改，直到T2事務結束。

如上所述，這種隔離等級，解決了髒讀問題，但是不能解決不可重複讀取現象。

3、可重複讀

事務T在資料讀取時，必須增加行級共用鎖定，直到事務結束；事務T在修改資料過程中，必須增加行級獨佔鎖定，直到資料結束。

資料庫鎖實現原理：

T1在讀取資料的過程中，T2也可以對相同資料進行讀取，但是不能進行修改（T1增加的是共用鎖定，T2也可以增加共用鎖定，但是不能增加獨佔鎖定）。直到T1事務結束後，才會釋放共用鎖定，這時T2才可以增加獨佔鎖定，對資料進行修改。

如上所述，這種隔離等級，解決了不可重複讀取現象，但是這種隔離等級解決不了幻讀的問題：

T1進行查詢，讀取了10條記錄，並對十條記錄增加了行級鎖，此時T2是無法對這10行資料進行修改操作的，但是由於沒有表級鎖，它可以增加一條滿足T1查詢條件的記錄。隨後T1在進行查詢時，會發現雖然10條記錄沒有改變，但是突然多了一條記錄。

4、序列化

產生幻讀是由於沒有進行範圍查詢時沒有增加範圍鎖。

 資料庫鎖實現原理：

 事務T在讀取資料時，必須先增加表級共用鎖定，直到事務結束才釋放；事務T在修改資料時，必須先增加表級獨佔鎖定，直到事務結束才釋放。

T1在讀取A表時，增加了表級共用鎖定，此時T2也可以讀取A表，但是不能進行任何資料的修改，直到T1事務結束。隨後T2可以增加對A表的表級獨佔鎖定，此時T1不能讀取A表中的任何資料，更不能進行修改。

如上所述，可序列化解決了髒讀、不可重複讀取、幻讀等讀現象，但是隔離等級越來越高的同時，在並發性上也就越來越低。

四、事務操作實踐 

預設情況下，MYSQL是自動認可的，也就意味著平時我們執行一條update語句時，MYSQL是自動幫我們提交的，儘快我們沒有顯示執行commit命令。但是這種只適用於單條SQL的執行。

如果我們想要同時執行多條SQL，並且執行過程中有SQL執行異常，需要復原前面已經成功執行的SQL或者最終想復原全部，則必須顯示的使用事務。

1、開始一項事務：start tr ansaction或者begin；

2、提交事務：commit；

3、復原事務：rollback；

4、事務提交之後的操作：chain；

5、交易回復之後的操作：release；

6、修改當前串連的提交方式：set autocommit；如果設定了set autocommit=0，則設定之後所有的事務都需要顯式的通過命令來進行提交或者復原。

查詢當前會話的交易隔離等級

查詢當前系統的交易隔離等級

修改當前會話的交易隔離等級

 

提交讀示範

用戶端A 開啟事務，並更新資料

 此時事務還沒有提交，開啟用戶端B，並進行查詢，此時的資料還是未更新前的

 用戶端A進行事務提交，然後用戶端B查詢，此時是最新的資料

 

 commit and chain的示範

如果在提交的時候使用commit and chain，那麼在提交後立即開始一個新的事務

 A提交事務後，B再進行查詢

 開啟事務會隱式解鎖

鎖表期間，用start transaction 命令開始一個新事務，則會隱式的執行unlock tables

A對錶進行寫鎖操作

此時B進行查詢：由於被A鎖表，所以查詢被阻塞

A開啟一個事務

由於A開啟事務，隱式的釋放了寫鎖，所以B的查詢不再被阻塞

 

 SAVEPOINT的使用

事務中可以通過定義SAVEPOINT，指定復原事務的一個部分
A開啟事務並insert一條記錄，並設定savepoint

 B進行查詢，查詢到的是開啟事務前的資料

 A又插入一條資料，然後復原到savepoint

 

 B進行查詢

 

 

淺談MySQL的交易隔離等級