mysql在項目中怎么選事務(wù)隔離級別
引言
開始我們的內(nèi)容,相信大家一定遇到過下面的一個面試場景
面試官:“講講mysql有幾個事務(wù)隔離級別?”
你:“讀未提交,讀已提交,可重復(fù)讀,串行化四個!默認(rèn)是可重復(fù)讀”
面試官:“為什么mysql選可重復(fù)讀作為默認(rèn)的隔離級別?”
(你面露苦色,不知如何回答!)
面試官:"你們項目中選了哪個隔離級別?為什么?"
你:“當(dāng)然是默認(rèn)的可重復(fù)讀,至于原因。。呃。。?!?br />
(然后你就可以回去等通知了!)
為了避免上述尷尬的場景,請繼續(xù)往下閱讀!
Mysql默認(rèn)的事務(wù)隔離級別是可重復(fù)讀(Repeatable Read),那互聯(lián)網(wǎng)項目中Mysql也是用默認(rèn)隔離級別,不做修改么?
OK,不是的,我們在項目中一般用讀已提交(Read Commited)這個隔離級別!
what!居然是讀已提交,網(wǎng)上不是說這個隔離級別存在不可重復(fù)讀
和幻讀
問題么?不用管么?好,帶著我們的疑問開始本文!
正文
我們先來思考一個問題,在Oracle,SqlServer中都是選擇讀已提交(Read Commited)作為默認(rèn)的隔離級別,為什么Mysql不選擇讀已提交(Read Commited)作為默認(rèn)隔離級別,而選擇可重復(fù)讀(Repeatable Read)作為默認(rèn)的隔離級別呢?
Why?Why?Why?
這個是有歷史原因的,當(dāng)然要從我們的主從復(fù)制開始講起了!
主從復(fù)制,是基于什么復(fù)制的?
是基于binlog復(fù)制的!這里不想去搬binlog的概念了,就簡單理解為binlog是一個記錄數(shù)據(jù)庫更改的文件吧~
binlog有幾種格式?
OK,三種,分別是
- statement:記錄的是修改SQL語句
- row:記錄的是每行實際數(shù)據(jù)的變更
- mixed:statement和row模式的混合
那Mysql在5.0這個版本以前,binlog只支持STATEMENT
這種格式!而這種格式在讀已提交(Read Commited)這個隔離級別下主從復(fù)制是有bug的,因此Mysql將可重復(fù)讀(Repeatable Read)作為默認(rèn)的隔離級別!
接下來,就要說說當(dāng)binlog為STATEMENT
格式,且隔離級別為讀已提交(Read Commited)時,有什么bug呢?如下圖所示,在主(master)上執(zhí)行如下事務(wù)
此時在主(master)上執(zhí)行下列語句
select * from test;
輸出如下
+---+
| b |
+---+
| 3 |
+---+
1 row in set
但是,你在此時在從(slave)上執(zhí)行該語句,得出輸出如下
Empty set
這樣,你就出現(xiàn)了主從不一致性的問題!原因其實很簡單,就是在master上執(zhí)行的順序為先刪后插!而此時binlog為STATEMENT格式,它記錄的順序為先插后刪!從(slave)同步的是binglog,因此從機(jī)執(zhí)行的順序和主機(jī)不一致!就會出現(xiàn)主從
不一致!
如何解決?
解決方案有兩種!
(1)隔離級別設(shè)為可重復(fù)讀(Repeatable Read),在該隔離級別下引入間隙鎖。當(dāng)Session 1
執(zhí)行delete語句時,會鎖住間隙。那么,Ssession 2
執(zhí)行插入語句就會阻塞住!
(2)將binglog的格式修改為row格式,此時是基于行的復(fù)制,自然就不會出現(xiàn)sql執(zhí)行順序不一樣的問題!奈何這個格式在mysql5.1版本開始才引入。因此由于歷史原因,mysql將默認(rèn)的隔離級別設(shè)為可重復(fù)讀(Repeatable Read),保證主從復(fù)制不出問題!
那么,當(dāng)我們了解完mysql選可重復(fù)讀(Repeatable Read)作為默認(rèn)隔離級別的原因后,接下來我們將其和讀已提交(Read Commited)進(jìn)行對比,來說明為什么在互聯(lián)網(wǎng)項目為什么將隔離級別設(shè)為讀已提交(Read Commited)!
對比
ok,我們先明白一點(diǎn)!項目中是不用讀未提交(Read UnCommitted)和串行化(Serializable)兩個隔離級別,原因有二
- 采用讀未提交(Read UnCommitted),一個事務(wù)讀到另一個事務(wù)未提交讀數(shù)據(jù),這個不用多說吧,從邏輯上都說不過去!
- 采用串行化(Serializable),每個次讀操作都會加鎖,快照讀失效,一般是使用mysql自帶分布式事務(wù)功能時才使用該隔離級別!(筆者從未用過mysql自帶的這個功能,因為這是XA事務(wù),是強(qiáng)一致性事務(wù),性能不佳!互聯(lián)網(wǎng)的分布式方案,多采用最終一致性的事務(wù)解決方案!)
也就是說,我們該糾結(jié)都只有一個問題,究竟隔離級別是用讀已經(jīng)提交呢還是可重復(fù)讀?
接下來對這兩種級別進(jìn)行對比,講講我們?yōu)槭裁催x讀已提交(Read Commited)作為事務(wù)隔離級別!
假設(shè)表結(jié)構(gòu)如下
CREATE TABLE `test` ( `id` int(11) NOT NULL, `color` varchar(20) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB
數(shù)據(jù)如下
+----+-------+ | id | color | +----+-------+ | 1 | red | | 2 | white | | 5 | red | | 7 | white | +----+-------+
為了便于描述,下面將
- 可重復(fù)讀(Repeatable Read),簡稱為RR;
- 讀已提交(Read Commited),簡稱為RC;
緣由一:在RR隔離級別下,存在間隙鎖,導(dǎo)致出現(xiàn)死鎖的幾率比RC大的多!
此時執(zhí)行語句
select * from test where id <3 for update;
在RR隔離級別下,存在間隙鎖,可以鎖住(2,5)這個間隙,防止其他事務(wù)插入數(shù)據(jù)!
而在RC隔離級別下,不存在間隙鎖,其他事務(wù)是可以插入數(shù)據(jù)!
ps
:在RC隔離級別下并不是不會出現(xiàn)死鎖,只是出現(xiàn)幾率比RR低而已!
緣由二:在RR隔離級別下,條件列未命中索引會鎖表!而在RC隔離級別下,只鎖行
此時執(zhí)行語句
update test set color = 'blue' where color = 'white';
在RC隔離級別下,其先走聚簇索引,進(jìn)行全部掃描。加鎖如下:
但在實際中,MySQL做了優(yōu)化,在MySQL Server過濾條件,發(fā)現(xiàn)不滿足后,會調(diào)用unlock_row方法,把不滿足條件的記錄放鎖。
實際加鎖如下
然而,在RR隔離級別下,走聚簇索引,進(jìn)行全部掃描,最后會將整個表鎖上,如下所示
緣由三:在RC隔離級別下,半一致性讀(semi-consistent)特性增加了update操作的并發(fā)性!
在5.1.15的時候,innodb引入了一個概念叫做“semi-consistent”,減少了更新同一行記錄時的沖突,減少鎖等待。
所謂半一致性讀就是,一個update語句,如果讀到一行已經(jīng)加鎖的記錄,此時InnoDB返回記錄最近提交的版本,由MySQL上層判斷此版本是否滿足update的where條件。若滿足(需要更新),則MySQL會重新發(fā)起一次讀操作,此時會讀取行的最新版本(并加鎖)!
具體表現(xiàn)如下:
此時有兩個Session,Session1和Session2!
Session1執(zhí)行
update test set color = 'blue' where color = 'red';
先不Commit事務(wù)!
與此同時Ssession2執(zhí)行
update test set color = 'blue' where color = 'white';
session 2嘗試加鎖的時候,發(fā)現(xiàn)行上已經(jīng)存在鎖,InnoDB會開啟semi-consistent read,返回最新的committed版本(1,red),(2,white),(5,red),(7,white)。MySQL會重新發(fā)起一次讀操作,此時會讀取行的最新版本(并加鎖)!
而在RR隔離級別下,Session2只能等待!
兩個疑問
在RC級別下,不可重復(fù)讀問題需要解決么?
不用解決,這個問題是可以接受的!畢竟你數(shù)據(jù)都已經(jīng)提交了,讀出來本身就沒有太大問題!Oracle的默認(rèn)隔離級別就是RC,你們改過Oracle的默認(rèn)隔離級別么?
在RC級別下,主從復(fù)制用什么binlog格式?
OK,在該隔離級別下,用的binlog為row格式,是基于行的復(fù)制!Innodb的創(chuàng)始人也是建議binlog使用該格式!
總結(jié)
本文啰里八嗦了一篇文章只是為了說明一件事,互聯(lián)網(wǎng)項目請用:讀已提交(Read Commited)這個隔離級別!
到此這篇關(guān)于mysql在項目中怎么選事務(wù)隔離級別的文章就介紹到這了,更多相關(guān)mysql 事務(wù)隔離級別內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!
相關(guān)文章
Windows10下MySQL5.7.31解壓版安裝與卸載方法
這篇文章主要介紹了Windows10下MySQL5.7.31解壓版安裝與卸載,本文給大家介紹的非常詳細(xì),對大家的學(xué)習(xí)或工作具有一定的參考借鑒價值,需要的朋友可以參考下2023-12-12mysql數(shù)據(jù)庫從服務(wù)器移植到個人PC的方法
有時候本地也需要數(shù)據(jù)庫進(jìn)行測試,那么就需要將服務(wù)器的東西移植到本地,如果有服務(wù)器控制權(quán)限,可以直接復(fù)制mysql的目錄(windows下),如果是別的那么就需要下面的方法了。2011-08-08Centos7.5安裝mysql5.7.24二進(jìn)制包方式部署
這篇文章主要介紹了Centos7.5安裝mysql5.7.24二進(jìn)制包方式部署,本文分步驟給大家介紹的非常詳細(xì),具有一定的參考借鑒價值,需要的朋友可以參考下2018-12-12Mysql數(shù)據(jù)庫的日志管理、備份與回復(fù)詳細(xì)圖文教程
備份的主要目的是災(zāi)難恢復(fù),備份還可以測試應(yīng)用、回滾數(shù)據(jù)修改、查詢歷史數(shù)據(jù)、審計等,這篇文章主要給大家介紹了關(guān)于Mysql數(shù)據(jù)庫的日志管理、備份與回復(fù)的相關(guān)資料,文中通過代碼介紹的非常詳細(xì),需要的朋友可以參考下2024-08-08