MySQL隔離級別解析：資料一致性與高併發之間的平衡術！

來源：碼農本農

DDL:資料定義，它用來定義資料庫物件，包括庫，表，列，透過ddl我們可以建立，刪除，修改資料庫和表結構；

DML:資料操作語言，增加刪除修改資料表中的記錄；

DCL:資料控制語言，定義訪問許可權和安全級別；

DQL:資料查詢語言，用它來查詢想要的記錄。

SQL執行順序:

1. from;
2. join
3. on
4. where;
5. group by;
6. avg,sum.... 使用聚集函式進行計算;
7. having;
8. select;
9. distinct;
10. order by;
11. limit;

今天來討論mysql中的事物隔離級別

1事物概念

事務是由一組SQL語句組成的邏輯處理單元。

事務具有以下4個屬性,通常簡稱為事務的ACID屬性：

原子性：事務是一個原子操作單元,其對資料的修改,要麼全都執行,要麼全都不執行。

一致性：在事務開始和完成時,資料都必須保持一致狀態。這意味著所有相關的資料規則都必須應用於事務的修改,以保持資料的完整性。

隔離性：資料庫系統提供一定的隔離機制,保證事務在不受外部併發操作影響的“獨立”環境執行。這意味著事務處理過程中的中間狀態對外部是不可見的,反之亦然。

永續性：事務完成之後,它對於資料的修改是永久性的,即使出現系統故障也能夠保持。

事務的啟動方式

1. 顯式啟動 set autocommit=1 begin 或 start transaction。配套的提交語句是 commit，回滾語句是 rollback。

2. set autocommit=0 自動提交關掉,意味著如果你只執行一個 select 語句，這個事務就啟動了，而且並不會自動提交。這個事務持續存在直到你主動執行 commit 或 rollback 語句，或者斷開連線。

3. 用commit work and chain代替 commit可以提交一個事務，並且開啟另一個新的事務。

2事物帶來的問題

我們的資料庫一般都會併發執行多個事務，多個事務可能會併發的對相同的一批資料進行增刪改查操作，可能就會導致我們說的髒寫、髒讀、不可重複讀、幻讀這些問題。

這些問題的本質都是資料庫的多事務併發問題，為了解決多事務併發問題，資料庫設計了事務隔離機制、鎖機制、MVCC多版本併發控制隔離機制，用一整套機制來解決多事務併發問題。接下來，我們會深入講解這些機制，讓大家徹底理解資料庫內部的執行原理。

髒寫

當兩個或多個事務選擇同一行，然後基於最初選定的值更新該行時，由於每個事務都不知道其他事務的存在，就會發生丟失更新問題，最後的更新覆蓋了由其他事務所做的更新。

髒讀

一個事務正在對一條記錄做修改，在這個事務完成並提交前，這條記錄的資料就處於不一致的狀態；這時，另一個事務也來讀取同一條記錄，如果不加控制，第二個事務讀取了這些“髒”資料，並據此作進一步的處理，就會產生未提交的資料依賴關係。這種現象被形象的叫做“髒讀”。

例：事務A讀取到了事務B已經修改但尚未提交的資料，還在這個資料基礎上做了操作。此時，如果B事務回滾，A讀取的資料無效，不符合一致性要求。

不可重讀

一個事務在讀取某些資料後的某個時間，再次讀取以前讀過的資料，卻發現其讀出的資料已經發生了改變、或某些記錄已經被刪除了！這種現象就叫做“不可重複讀”。

例：事務A內部的相同查詢語句在不同時刻讀出的結果不一致，不符合隔離性

幻讀

一個事務按相同的查詢條件重新讀取以前檢索過的資料，卻發現其他事務插入了滿足其查詢條件的新資料，這種現象就稱為“幻讀”。

例：事務A讀取到了事務B提交的新增資料，不符合隔離性

不可重複讀與幻讀有什麼區別？

不可重複讀的重點是修改：在同一事務中，同樣的條件，第一次讀的資料和第二次讀的「資料不一樣」。（因為中間有其他事務提交了修改）

幻讀的重點在於新增或者刪除：在同一事務中，同樣的條件，第一次和第二次讀出來的「記錄數不一樣」。（因為中間有其他事務提交了插入/刪除）

3事物的隔離級別

在 MySQL 中，事務支援是在引擎層實現的。你現在知道，MySQL 是一個支援多引擎的系統，但並不是所有的引擎都支援事務。比如 MySQL 原生的 MyISAM 引擎就不支援事務，這也是 MyISAM 被 InnoDB 取代的重要原因之一

InnoDB實現了四個標準的隔離級別，每一種級別都規定了一個事務中所做的修改，哪些在事務內和事務間是可見的，哪些是不可見的。低階別的隔離級一般支援更高的併發處理，並擁有更低的系統開銷。

檢視當前資料庫的事務隔離級別:

show variables like 'tx_isolation';

設定事務隔離級別：

set tx_isolation='REPEATABLE-READ';

查詢mysql的長事務（大於60秒的事務）: select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>60

Mysql預設的事務隔離級別是可重複讀.

事務隔離級別

在談隔離級別之前，你首先要知道，你隔離得越嚴實，效率就會越低。因此很多時候，我們都要在二者之間尋找一個平衡點。

標準的事務隔離級別包括：

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

隔離級別是如何實現的呢？

在實現上，資料庫裡面會建立一個檢視，訪問的時候以檢視的邏輯結果為準；

在“可重複讀”隔離級別下，這個檢視是在事務啟動時建立的，整個事務存在期間都用這個檢視；

在“讀提交”隔離級別下，這個檢視是在每個 SQL 語句開始執行的時候建立的；

在“讀未提交”隔離級別下直接返回記錄上的最新值，沒有檢視概念；

在“序列化”隔離級別下直接用加鎖的方式來避免並行訪問。

在 MySQL 中，不同時刻啟動的事務會有不同的一致性檢視read-view，並且每條記錄在更新的時候都會同時記錄一條回滾操作。記錄上的最新值，透過回滾操作，都可以得到前一個狀態的值，這就意味著同一條資料在資料庫中維護了多個版本，就是資料庫的多版本併發控制（MVCC），我們後續詳細討論MVCC機制。

從圖中可以看到每種隔離級別可以解決的問題，我們可以看出可重複隔離級別下幻讀是沒有解決的，而且即便是加行鎖也解決不了問題，我們上面說了幻讀問題說的是新增刪除造成的問題，而無論是可重複讀隔離級別還是行鎖操作的物件都是當前行，所以幻讀問題需要其他的方式解決。

注意：長事務會造成回滾日誌不斷增大，會有空間佔用劇增的風險，儘量不要使用長事務。

幻讀的解決

產生幻讀的原因是，行鎖只能鎖住行，但是新插入記錄這個動作，要更新的是記錄之間的“間隙”。因此，為了解決幻讀問題，InnoDB 只好引入新的鎖，也就是間隙鎖 (Gap Lock)，間隙鎖是在可重複讀隔離級別下才會生效的。所以，你如果把隔離級別設定為讀提交的話，就沒有間隙鎖了，間隙鎖是開區間。間隙鎖和行鎖合稱 next-key lock，每個 next-key lock 是前開後閉區間。也就是說，我們的表 t 初始化以後，如果用 select * from t for update 要把整個表所有記錄鎖起來，就形成了 7 個 next-key lock，分別是 (-∞,0]、(0,5]、(5,10]、(10,15]、(15,20]、(20, 25]、(25, +supremum]。間隙鎖和 next-key lock 的引入，幫我們解決了幻讀的問題，但同時也帶來了一些“困擾”，間隙鎖的引入，可能會導致同樣的語句鎖住更大的範圍，這其實是影響了併發度的。

簡單總結

解決上述問題其實就是依賴於mysql的MVCC機制和鎖機制，我們後續分別討論。