MySQL隔離級別解析:資料一致性與高併發之間的平衡術!
來源:碼農本農
DDL:資料定義,它用來定義資料庫物件,包括庫,表,列,透過ddl我們可以建立,刪除,修改資料庫和表結構;
DML:資料操作語言,增加刪除修改資料表中的記錄;
DCL:資料控制語言,定義訪問許可權和安全級別;
DQL:資料查詢語言,用它來查詢想要的記錄。
SQL執行順序:
from; join on where; group by; avg,sum.... 使用聚集函式進行計算; having; select; distinct; order by; limit;
今天來討論mysql中的事物隔離級別
1事物概念
事務是由一組SQL語句組成的邏輯處理單元。
事務具有以下4個屬性,通常簡稱為事務的ACID屬性:
原子性:事務是一個原子操作單元,其對資料的修改,要麼全都執行,要麼全都不執行。
一致性:在事務開始和完成時,資料都必須保持一致狀態。這意味著所有相關的資料規則都必須應用於事務的修改,以保持資料的完整性。
隔離性:資料庫系統提供一定的隔離機制,保證事務在不受外部併發操作影響的“獨立”環境執行。這意味著事務處理過程中的中間狀態對外部是不可見的,反之亦然。
永續性:事務完成之後,它對於資料的修改是永久性的,即使出現系統故障也能夠保持。
事務的啟動方式
顯式啟動 set autocommit=1 begin 或 start transaction。配套的提交語句是 commit,回滾語句是 rollback。
set autocommit=0 自動提交關掉,意味著如果你只執行一個 select 語句,這個事務就啟動了,而且並不會自動提交。這個事務持續存在直到你主動執行 commit 或 rollback 語句,或者斷開連線。
用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機制和鎖機制,我們後續分別討論。
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/70027826/viewspace-2998899/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- MySQL事務的隔離級別與併發問題MySql
- Mysql資料庫的隔離級別MySql資料庫
- MySQL的隔離級別MySql
- 資料庫之事務、隔離級別和併發問題資料庫
- Mysql 隔離級別MySql
- MySQL資料庫中的四種隔離級別MySql資料庫
- 資料庫事務與隔離級別資料庫
- 資料庫隔離級別資料庫
- Mysql鎖與事務隔離級別MySql
- MySQL 事務隔離級別解析和實戰MySql
- PostgreSQL 併發控制機制(4):RR隔離級別,MySQL vs PostgreSQLMySql
- 資料庫ACID、隔離級別與MVCC資料庫MVC
- PostgreSQL 併發控制機制(1):隔離級別SQL
- 論 MySQL 之事務隔離級別 | 資料庫篇MySql資料庫
- MySQL資料庫引擎、事務隔離級別、鎖MySql資料庫
- MySQL事務的隔離級別MySql
- MySQL的事務隔離級別MySql
- MySQL 的隔離級別 自理解MySql
- MySQL 事務隔離級別MySql
- MySQL入門--隔離級別MySql
- MySQL設定隔離級別MySql
- MySQL事務隔離級別MySql
- [Mysql]事務/隔離級別MySql
- 資料庫事務與事務的隔離級別資料庫
- 資料庫的四種隔離級別資料庫
- DB2併發控制-事務隔離級別DB2
- Mysql事務隔離級別與鎖機制MySql
- 淺析MySQL InnoDB的隔離級別MySql
- 理解mysql的事務隔離級別MySql
- 資料庫事務隔離級別資料庫
- 資料庫事務併發產生的問題以及事務的隔離級別資料庫
- 理解MySQL事務隔離級別MySql
- mysql修改事務隔離級別MySql
- MySQL 5.5 隔離級別測試MySql
- 【MySQL】MySQL的四種事務隔離級別MySql
- mysql 5.5 innodb事務隔離級別之開篇MySql
- 聊聊資料庫的事務隔離級別資料庫
- 【眼見為實】資料庫併發問題 封鎖協議 隔離級別資料庫協議