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

資料庫工作筆記發表於2023-12-06

來源:碼農本農

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隔離級別解析:資料一致性與高併發之間的平衡術!

在 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機制和鎖機制,我們後續分別討論。

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/70027826/viewspace-2998899/,如需轉載,請註明出處,否則將追究法律責任。

相關文章