引言
開始我們的内容,相信大家一定遇到過下面的一個面試場景
面試官:“講講mysql有幾個事務隔離級别?”
你:“讀未送出,讀已送出,可重複讀,串行化四個!預設是可重複讀”
面試官:“為什麼mysql選可重複讀作為預設的隔離級别?”
(你面露苦色,不知如何回答!)
面試官:"你們項目中選了哪個隔離級别?為什麼?"
你:“當然是預設的可重複讀,至于原因。。呃。。。”
(然後你就可以回去等通知了!)
為了避免上述尴尬的場景,請繼續往下閱讀!
Mysql預設的事務隔離級别是可重複讀(Repeatable Read),那網際網路項目中Mysql也是用預設隔離級别,不做修改麼?
OK,不是的,我們在項目中一般用讀已送出(Read Commited)這個隔離級别!
what!居然是讀已送出,網上不是說這個隔離級别存在不可重複讀和幻讀問題麼?不用管麼?好,帶着我們的疑問開始本文!
正文
我們先來思考一個問題,在Oracle,SqlServer中都是選擇讀已送出(Read Commited)作為預設的隔離級别,為什麼Mysql不選擇讀已送出(Read Commited)作為預設隔離級别,而選擇可重複讀(Repeatable Read)作為預設的隔離級别呢?
Why?Why?Why?
這個是有曆史原因的,當然要從我們的主從複制開始講起了!
主從複制,是基于什麼複制的?
是基于binlog複制的!這裡不想去搬binlog的概念了,就簡單了解為binlog是一個記錄資料庫更改的檔案吧~
binlog有幾種格式?
OK,三種,分别是
- statement:記錄的是修改SQL語句
- row:記錄的是每行實際資料的變更
- mixed:statement和row模式的混合
那Mysql在5.0這個版本以前,binlog隻支援STATEMENT這種格式!而這種格式在讀已送出(Read Commited)這個隔離級别下主從複制是有bug的,是以Mysql将可重複讀(Repeatable Read)作為預設的隔離級别!
接下來,就要說說當binlog為STATEMENT格式,且隔離級别為讀已送出(Read Commited)時,有什麼bug呢?如下圖所示,在主(master)上執行如下事務
此時在主(master)上執行下列語句
select * from test;
輸出如下
+---+
| b |
+---+
| 3 |
+---+
1 row in set
但是,你在此時在從(slave)上執行該語句,得出輸出如下
Empty set
這樣,你就出現了主從不一緻性的問題!原因其實很簡單,就是在master上執行的順序為先删後插!而此時binlog為STATEMENT格式,它記錄的順序為先插後删!從(slave)同步的是binglog,是以從機執行的順序和主機不一緻!就會出現主從不一緻!
如何解決?
解決方案有兩種!
(1)隔離級别設為可重複讀(Repeatable Read),在該隔離級别下引入間隙鎖。當Session 1執行delete語句時,會鎖住間隙。那麼,Ssession 2執行插入語句就會阻塞住!
(2)将binglog的格式修改為row格式,此時是基于行的複制,自然就不會出現sql執行順序不一樣的問題!奈何這個格式在mysql5.1版本開始才引入。是以由于曆史原因,mysql将預設的隔離級别設為可重複讀(Repeatable Read),保證主從複制不出問題!
那麼,當我們了解完mysql選可重複讀(Repeatable Read)作為預設隔離級别的原因後,接下來我們将其和讀已送出(Read Commited)進行對比,來說明為什麼在網際網路項目為什麼将隔離級别設為讀已送出(Read Commited)!
對比
ok,我們先明白一點!項目中是不用讀未送出(Read UnCommitted)和串行化(Serializable)兩個隔離級别,原因有二
- 采用讀未送出(Read UnCommitted),一個事務讀到另一個事務未送出讀資料,這個不用多說吧,從邏輯上都說不過去!
- 采用串行化(Serializable),每個次讀操作都會加鎖,快照讀失效,一般是使用mysql自帶分布式事務功能時才使用該隔離級别!(筆者從未用過mysql自帶的這個功能,因為這是XA事務,是強一緻性事務,性能不佳!網際網路的分布式方案,多采用最終一緻性的事務解決方案!)
也就是說,我們該糾結都隻有一個問題,究竟隔離級别是用讀已經送出呢還是可重複讀?
接下來對這兩種級别進行對比,講講我們為什麼選讀已送出(Read Commited)作為事務隔離級别!
假設表結構如下
CREATE TABLE `test` (
`id` int(11) NOT NULL,
`color` varchar(20) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB
資料如下
+----+-------+
| id | color |
+----+-------+
| 1 | red |
| 2 | white |
| 5 | red |
| 7 | white |
+----+-------+
為了便于描述,下面将
- 可重複讀(Repeatable Read),簡稱為RR;
- 讀已送出(Read Commited),簡稱為RC;
緣由一:在RR隔離級别下,存在間隙鎖,導緻出現死鎖的幾率比RC大的多!
此時執行語句
select * from test where id <3 for update;
在RR隔離級别下,存在間隙鎖,可以鎖住(2,5)這個間隙,防止其他事務插入資料!
而在RC隔離級别下,不存在間隙鎖,其他事務是可以插入資料!
ps:在RC隔離級别下并不是不會出現死鎖,隻是出現幾率比RR低而已!
緣由二:在RR隔離級别下,條件列未命中索引會鎖表!而在RC隔離級别下,隻鎖行
此時執行語句
update test set color = 'blue' where color = 'white';
在RC隔離級别下,其先走聚簇索引,進行全部掃描。加鎖如下:
但在實際中,MySQL做了優化,在MySQL Server過濾條件,發現不滿足後,會調用unlock_row方法,把不滿足條件的記錄放鎖。
實際加鎖如下
然而,在RR隔離級别下,走聚簇索引,進行全部掃描,最後會将整個表鎖上,如下所示
緣由三:在RC隔離級别下,半一緻性讀(semi-consistent)特性增加了update操作的并發性!
在5.1.15的時候,innodb引入了一個概念叫做“semi-consistent”,減少了更新同一行記錄時的沖突,減少鎖等待。
所謂半一緻性讀就是,一個update語句,如果讀到一行已經加鎖的記錄,此時InnoDB傳回記錄最近送出的版本,由MySQL上層判斷此版本是否滿足update的where條件。若滿足(需要更新),則MySQL會重新發起一次讀操作,此時會讀取行的最新版本(并加鎖)!
具體表現如下:
此時有兩個Session,Session1和Session2!
Session1執行
update test set color = 'blue' where color = 'red';
先不Commit事務!
與此同時Ssession2執行
update test set color = 'blue' where color = 'white';
session 2嘗試加鎖的時候,發現行上已經存在鎖,InnoDB會開啟semi-consistent read,傳回最新的committed版本(1,red),(2,white),(5,red),(7,white)。MySQL會重新發起一次讀操作,此時會讀取行的最新版本(并加鎖)!
而在RR隔離級别下,Session2隻能等待!
兩個疑問
在RC級别下,不可重複讀問題需要解決麼?
不用解決,這個問題是可以接受的!畢竟你資料都已經送出了,讀出來本身就沒有太大問題!Oracle的預設隔離級别就是RC,你們改過Oracle的預設隔離級别麼?
在RC級别下,主從複制用什麼binlog格式?
OK,在該隔離級别下,用的binlog為row格式,是基于行的複制!Innodb的創始人也是建議binlog使用該格式!
總結
本文啰裡八嗦了一篇文章隻是為了說明一件事,網際網路項目請用:讀已送出(Read Commited)這個隔離級别!
文章來源:孤獨煙_http://rjzheng.cnblogs.com/