快捷導(dǎo)航

mysql在項(xiàng)目中怎么選事務(wù)隔離級(jí)別

更新時(shí)間：2021年05月25日 09:08:12 作者：孤獨(dú)煙

Mysql默認(rèn)的事務(wù)隔離級(jí)別是可重復(fù)讀，那互聯(lián)網(wǎng)項(xiàng)目中Mysql也是用默認(rèn)隔離級(jí)別，但在項(xiàng)目中呢，下面我們就一起來(lái)了解一下

引言

開(kāi)始我們的內(nèi)容，相信大家一定遇到過(guò)下面的一個(gè)面試場(chǎng)景

面試官：“講講mysql有幾個(gè)事務(wù)隔離級(jí)別？”
你：“讀未提交，讀已提交，可重復(fù)讀，串行化四個(gè)！默認(rèn)是可重復(fù)讀”
面試官：“為什么mysql選可重復(fù)讀作為默認(rèn)的隔離級(jí)別？”
(你面露苦色，不知如何回答！)
面試官:"你們項(xiàng)目中選了哪個(gè)隔離級(jí)別？為什么？"
你：“當(dāng)然是默認(rèn)的可重復(fù)讀，至于原因。。呃。。?！?br /> (然后你就可以回去等通知了！)

為了避免上述尷尬的場(chǎng)景，請(qǐng)繼續(xù)往下閱讀！

Mysql默認(rèn)的事務(wù)隔離級(jí)別是可重復(fù)讀(Repeatable Read)，那互聯(lián)網(wǎng)項(xiàng)目中Mysql也是用默認(rèn)隔離級(jí)別，不做修改么？
OK，不是的，我們?cè)陧?xiàng)目中一般用讀已提交(Read Commited)這個(gè)隔離級(jí)別！

what！居然是讀已提交，網(wǎng)上不是說(shuō)這個(gè)隔離級(jí)別存在不可重復(fù)讀和幻讀問(wèn)題么？不用管么？好，帶著我們的疑問(wèn)開(kāi)始本文！

正文

我們先來(lái)思考一個(gè)問(wèn)題，在Oracle，SqlServer中都是選擇讀已提交(Read Commited)作為默認(rèn)的隔離級(jí)別，為什么Mysql不選擇讀已提交(Read Commited)作為默認(rèn)隔離級(jí)別，而選擇可重復(fù)讀(Repeatable Read)作為默認(rèn)的隔離級(jí)別呢？

Why?Why?Why?

這個(gè)是有歷史原因的，當(dāng)然要從我們的主從復(fù)制開(kāi)始講起了！
主從復(fù)制，是基于什么復(fù)制的？

是基于binlog復(fù)制的！這里不想去搬binlog的概念了，就簡(jiǎn)單理解為binlog是一個(gè)記錄數(shù)據(jù)庫(kù)更改的文件吧～

binlog有幾種格式？

OK，三種，分別是

statement:記錄的是修改SQL語(yǔ)句
row：記錄的是每行實(shí)際數(shù)據(jù)的變更
mixed：statement和row模式的混合

那Mysql在5.0這個(gè)版本以前，binlog只支持STATEMENT這種格式！而這種格式在讀已提交(Read Commited)這個(gè)隔離級(jí)別下主從復(fù)制是有bug的，因此Mysql將可重復(fù)讀(Repeatable Read)作為默認(rèn)的隔離級(jí)別！
接下來(lái)，就要說(shuō)說(shuō)當(dāng)binlog為STATEMENT格式，且隔離級(jí)別為讀已提交(Read Commited)時(shí)，有什么bug呢？如下圖所示，在主(master)上執(zhí)行如下事務(wù)

此時(shí)在主(master)上執(zhí)行下列語(yǔ)句

select * from test；

輸出如下

+---+
| b |
+---+
| 3 |
+---+
1 row in set

但是，你在此時(shí)在從(slave)上執(zhí)行該語(yǔ)句，得出輸出如下

Empty set

這樣，你就出現(xiàn)了主從不一致性的問(wèn)題！原因其實(shí)很簡(jiǎn)單，就是在master上執(zhí)行的順序?yàn)橄葎h后插！而此時(shí)binlog為STATEMENT格式，它記錄的順序?yàn)橄炔搴髣h！從(slave)同步的是binglog，因此從機(jī)執(zhí)行的順序和主機(jī)不一致！就會(huì)出現(xiàn)主從

不一致！

如何解決？

解決方案有兩種！
(1)隔離級(jí)別設(shè)為可重復(fù)讀(Repeatable Read),在該隔離級(jí)別下引入間隙鎖。當(dāng)Session 1執(zhí)行delete語(yǔ)句時(shí)，會(huì)鎖住間隙。那么，Ssession 2執(zhí)行插入語(yǔ)句就會(huì)阻塞住！
(2)將binglog的格式修改為row格式，此時(shí)是基于行的復(fù)制，自然就不會(huì)出現(xiàn)sql執(zhí)行順序不一樣的問(wèn)題！奈何這個(gè)格式在mysql5.1版本開(kāi)始才引入。因此由于歷史原因，mysql將默認(rèn)的隔離級(jí)別設(shè)為可重復(fù)讀(Repeatable Read)，保證主從復(fù)制不出問(wèn)題！

那么，當(dāng)我們了解完mysql選可重復(fù)讀(Repeatable Read)作為默認(rèn)隔離級(jí)別的原因后，接下來(lái)我們將其和讀已提交(Read Commited)進(jìn)行對(duì)比，來(lái)說(shuō)明為什么在互聯(lián)網(wǎng)項(xiàng)目為什么將隔離級(jí)別設(shè)為讀已提交(Read Commited)！

對(duì)比

ok，我們先明白一點(diǎn)！項(xiàng)目中是不用讀未提交(Read UnCommitted)和串行化(Serializable)兩個(gè)隔離級(jí)別，原因有二

采用讀未提交(Read UnCommitted),一個(gè)事務(wù)讀到另一個(gè)事務(wù)未提交讀數(shù)據(jù)，這個(gè)不用多說(shuō)吧，從邏輯上都說(shuō)不過(guò)去！
采用串行化(Serializable)，每個(gè)次讀操作都會(huì)加鎖，快照讀失效，一般是使用mysql自帶分布式事務(wù)功能時(shí)才使用該隔離級(jí)別！(筆者從未用過(guò)mysql自帶的這個(gè)功能，因?yàn)檫@是XA事務(wù)，是強(qiáng)一致性事務(wù)，性能不佳！互聯(lián)網(wǎng)的分布式方案，多采用最終一致性的事務(wù)解決方案！)

也就是說(shuō)，我們?cè)摷m結(jié)都只有一個(gè)問(wèn)題，究竟隔離級(jí)別是用讀已經(jīng)提交呢還是可重復(fù)讀？
接下來(lái)對(duì)這兩種級(jí)別進(jìn)行對(duì)比，講講我們?yōu)槭裁催x讀已提交(Read Commited)作為事務(wù)隔離級(jí)別！
假設(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)，簡(jiǎn)稱(chēng)為RR；
讀已提交(Read Commited)，簡(jiǎn)稱(chēng)為RC；

緣由一：在RR隔離級(jí)別下，存在間隙鎖，導(dǎo)致出現(xiàn)死鎖的幾率比RC大的多！
此時(shí)執(zhí)行語(yǔ)句

select * from test where id <3 for update;

在RR隔離級(jí)別下，存在間隙鎖，可以鎖住(2,5)這個(gè)間隙，防止其他事務(wù)插入數(shù)據(jù)！
而在RC隔離級(jí)別下，不存在間隙鎖，其他事務(wù)是可以插入數(shù)據(jù)！

ps:在RC隔離級(jí)別下并不是不會(huì)出現(xiàn)死鎖，只是出現(xiàn)幾率比RR低而已！

緣由二：在RR隔離級(jí)別下，條件列未命中索引會(huì)鎖表！而在RC隔離級(jí)別下，只鎖行
此時(shí)執(zhí)行語(yǔ)句

update test set color = 'blue' where color = 'white';

在RC隔離級(jí)別下，其先走聚簇索引，進(jìn)行全部掃描。加鎖如下：

但在實(shí)際中，MySQL做了優(yōu)化，在MySQL Server過(guò)濾條件，發(fā)現(xiàn)不滿(mǎn)足后，會(huì)調(diào)用unlock_row方法，把不滿(mǎn)足條件的記錄放鎖。

實(shí)際加鎖如下

然而，在RR隔離級(jí)別下，走聚簇索引，進(jìn)行全部掃描，最后會(huì)將整個(gè)表鎖上，如下所示

緣由三：在RC隔離級(jí)別下，半一致性讀(semi-consistent)特性增加了update操作的并發(fā)性！

在5.1.15的時(shí)候，innodb引入了一個(gè)概念叫做“semi-consistent”，減少了更新同一行記錄時(shí)的沖突，減少鎖等待。
所謂半一致性讀就是，一個(gè)update語(yǔ)句，如果讀到一行已經(jīng)加鎖的記錄，此時(shí)InnoDB返回記錄最近提交的版本，由MySQL上層判斷此版本是否滿(mǎn)足update的where條件。若滿(mǎn)足(需要更新)，則MySQL會(huì)重新發(fā)起一次讀操作，此時(shí)會(huì)讀取行的最新版本(并加鎖)！
具體表現(xiàn)如下:
此時(shí)有兩個(gè)Session，Session1和Session2！
Session1執(zhí)行

update test set color = 'blue' where color = 'red';

先不Commit事務(wù)！
與此同時(shí)Ssession2執(zhí)行

update test set color = 'blue' where color = 'white';

session 2嘗試加鎖的時(shí)候，發(fā)現(xiàn)行上已經(jīng)存在鎖，InnoDB會(huì)開(kāi)啟semi-consistent read，返回最新的committed版本(1,red),(2，white),(5,red),(7,white)。MySQL會(huì)重新發(fā)起一次讀操作，此時(shí)會(huì)讀取行的最新版本(并加鎖)!
而在RR隔離級(jí)別下，Session2只能等待！

兩個(gè)疑問(wèn)

在RC級(jí)別下，不可重復(fù)讀問(wèn)題需要解決么？
不用解決，這個(gè)問(wèn)題是可以接受的！畢竟你數(shù)據(jù)都已經(jīng)提交了，讀出來(lái)本身就沒(méi)有太大問(wèn)題！Oracle的默認(rèn)隔離級(jí)別就是RC，你們改過(guò)Oracle的默認(rèn)隔離級(jí)別么？

在RC級(jí)別下，主從復(fù)制用什么binlog格式？
OK,在該隔離級(jí)別下，用的binlog為row格式，是基于行的復(fù)制！Innodb的創(chuàng)始人也是建議binlog使用該格式！