MySQL為何使用可重復(fù)讀(Repeatable Read)為默認(rèn)隔離級(jí)別?
本文轉(zhuǎn)載自微信公眾號(hào)「java寶典」，作者iTengyu。轉(zhuǎn)載本文請(qǐng)聯(lián)系java寶典公眾號(hào)。
群里有小伙伴面試時(shí),碰到面試官提了個(gè)很刁鉆的問(wèn)題:Mysql為何使用可重復(fù)讀(Repeatable read)為默認(rèn)隔離級(jí)別???
下面進(jìn)入正題:
我們都知道事務(wù)的幾種性質(zhì) :原子性、一致性、隔離性和持久性 (ACID)
為了維持一致性和隔離性,一般使用加鎖這種方式來(lái)處理,但是加鎖相對(duì)帶來(lái)的是并發(fā)處理能力的降低
而數(shù)據(jù)庫(kù)是個(gè)高并發(fā)的應(yīng)用,因此對(duì)于加鎖的處理是事務(wù)的精髓.
下面我們來(lái)了解一下封鎖協(xié)議,以及事務(wù)在數(shù)據(jù)庫(kù)中做了什么
封鎖協(xié)議(Locking Protocol)
MySQL的鎖系統(tǒng)：shared lock 和 exclusive lock 即共享鎖和排他鎖，也叫讀鎖(S)和寫(xiě)鎖(X),共享鎖和排他鎖都屬于悲觀鎖。排他鎖又可以可以分為行鎖和表鎖。
封鎖協(xié)議(Locking Protocol): 在使用X鎖或S鎖對(duì)數(shù)據(jù)加鎖時(shí),約定的一些規(guī)則.例如何時(shí)申請(qǐng)X或S鎖,持續(xù)時(shí)間,何時(shí)釋放鎖等.
一級(jí)、二級(jí)、三級(jí)封鎖協(xié)議
對(duì)封鎖方式規(guī)定不同的規(guī)則，就形成了各種不同的封鎖協(xié)議,不同的封鎖協(xié)議，為并發(fā)操作的正確性提供不同程度的保證
一級(jí)封鎖協(xié)議
一級(jí)封鎖協(xié)議定義：事務(wù)T在修改數(shù)據(jù)R之前必須先對(duì)其加X(jué)鎖(排他鎖)，直到事務(wù)結(jié)束才釋放。事務(wù)結(jié)束包括正常結(jié)束(COMMIT)和非正常結(jié)束(ROLLBACK)。
一級(jí)封鎖協(xié)議可以防止丟失修改，并保證事務(wù)T是可恢復(fù)的。使用一級(jí)封鎖協(xié)議可以解決丟失修改問(wèn)題。
在一級(jí)封鎖協(xié)議中，如果僅僅是讀數(shù)據(jù)不對(duì)其進(jìn)行修改，是不需要加鎖的，它不能保證可重復(fù)讀和不讀“臟”數(shù)據(jù)。
二級(jí)封鎖協(xié)議
二級(jí)封鎖協(xié)議定義：一級(jí)封鎖協(xié)議加上事務(wù)T在讀取數(shù)據(jù)R之前必須先對(duì)其加S鎖(共享鎖)，讀完后釋放S鎖。事務(wù)的加鎖和解鎖嚴(yán)格分為兩個(gè)階段，第一階段加鎖，第二階段解鎖。
- 加鎖階段: 在對(duì)任何數(shù)據(jù)進(jìn)行讀操作之前要申請(qǐng)并獲得S鎖(共享鎖，其它事務(wù)可以繼續(xù)加共享鎖，但不能加排它鎖)，在進(jìn)行寫(xiě)操作之前要申請(qǐng)并獲得X鎖(排它鎖，其它事務(wù)不能再獲得任何鎖)。加鎖不成功，則事務(wù)進(jìn)入等待狀態(tài)，直到加鎖成功才繼續(xù)執(zhí)行。
- 解鎖階段:當(dāng)事務(wù)釋放了一個(gè)封鎖以后，事務(wù)進(jìn)入解鎖階段，在該階段只能進(jìn)行解鎖操作不能再進(jìn)行加鎖操作。
二級(jí)封鎖協(xié)議除防止了丟失修改，還可以進(jìn)一步防止讀“臟”數(shù)據(jù)。但在二級(jí)封鎖協(xié)議中，由于讀完數(shù)據(jù)后釋放S鎖，所以它不能保證可重復(fù)讀。
二級(jí)封鎖的目的是保證并發(fā)調(diào)度的正確性。就是說(shuō)，如果事務(wù)滿足兩段鎖協(xié)議，那么事務(wù)的并發(fā)調(diào)度策略是串行性的。保證事務(wù)的并發(fā)調(diào)度是串行化(串行化很重要，尤其是在數(shù)據(jù)恢復(fù)和備份的時(shí)候)
三級(jí)封鎖協(xié)議
三級(jí)封鎖協(xié)議定義：一級(jí)封鎖協(xié)議加上事務(wù)T在讀取數(shù)據(jù)R之前必須先對(duì)其加S鎖(共享鎖)，直到事務(wù)結(jié)束才釋放。在一級(jí)封鎖協(xié)議(一級(jí)封鎖協(xié)議：修改之前先加X(jué)鎖，事務(wù)完成釋放)的基礎(chǔ)上加上S鎖，事務(wù)結(jié)束后釋放S鎖
三級(jí)封鎖協(xié)議除防止了丟失修改和不讀“臟”數(shù)據(jù)外，還進(jìn)一步防止了不可重復(fù)讀。上述三級(jí)協(xié)議的主要區(qū)別在于什么操作需要申請(qǐng)封鎖，以及何時(shí)釋放。
事務(wù)四種隔離級(jí)別
在數(shù)據(jù)庫(kù)操作中，為了有效保證并發(fā)讀取數(shù)據(jù)的正確性，提出的事務(wù)隔離級(jí)別。上面提到的封鎖協(xié)議 ，也是為了構(gòu)建這些隔離級(jí)別存在的。
隔離級(jí)別 臟讀(Dirty Read) 不可重復(fù)讀(NonRepeatable Read) 幻讀(Phantom Read)
| 隔離級(jí)別 | 臟讀（Dirty Read） | 不可重復(fù)讀（NonRepeatable Read） | 幻讀（Phantom Read） |
|---|---|---|---|
| 未提交讀（Read uncommitted） | 可能 | 可能 | 可能 |
| 已提交讀（Read committed） | 不可能 | 可能 | 可能 |
| 可重復(fù)讀（Repeatable read） | 不可能 | 不可能 | 可能 |
| 可串行化（Serializable ） | 不可能 | 不可能 | 不可能 |
為什么是RR
一般的DBMS系統(tǒng)，默認(rèn)都會(huì)使用讀提交(Read-Comitted，RC)作為默認(rèn)隔離級(jí)別，如Oracle、SQL Server等，而MySQL卻使用可重復(fù)讀(Read-Repeatable，RR)。要知道，越高的隔離級(jí)別，能解決的數(shù)據(jù)一致性問(wèn)題越多，理論上性能的損耗更大，且并發(fā)性越低。隔離級(jí)別依次為: SERIALIZABLE > RR > RC > RU
我們可以通過(guò)以下語(yǔ)句設(shè)置和獲取數(shù)據(jù)庫(kù)的隔離級(jí)別:
查看系統(tǒng)的隔離級(jí)別:
- mysql> select @@global.tx_isolation isolation;
- +-----------------+
- | isolation |
- +-----------------+
- | REPEATABLE-READ |
- +-----------------+
- 1 row in set, 1 warning (0.00 sec)
查看當(dāng)前會(huì)話的 隔離級(jí)別:
mysql> select @@tx_isolation;
- mysql> select @@tx_isolation;
- +----------------+
- | @@tx_isolation |
- +----------------+
- | READ-COMMITTED |
- +----------------+
- 1 row in set, 1 warning (0.00 sec)
設(shè)置會(huì)話的隔離級(jí)別，隔離級(jí)別由低到高設(shè)置依次為:
- set session transacton isolation level read uncommitted;
- set session transacton isolation level read committed;
- set session transacton isolation level repeatable read;
- set session transacton isolation level serializable;
設(shè)置當(dāng)前系統(tǒng)的隔離級(jí)別，隔離級(jí)別由低到高設(shè)置依次為:
- set global transacton isolation level read uncommitted;
- set global transacton isolation level read committed;
- set global transacton isolation level repeatable read;
- set global transacton isolation level serializable;
可重復(fù)讀(Repeated Read)：可重復(fù)讀?；阪i機(jī)制并發(fā)控制的DBMS需要對(duì)選定對(duì)象的讀鎖(read locks)和寫(xiě)鎖(write locks)一直保持到事務(wù)結(jié)束，但不要求“范圍鎖(range-locks)”，因此可能會(huì)發(fā)生“幻影讀(phantom reads)” 在該事務(wù)級(jí)別下，保證同一個(gè)事務(wù)從開(kāi)始到結(jié)束獲取到的數(shù)據(jù)一致。是Mysql的默認(rèn)事務(wù)級(jí)別。
下面我們先來(lái)思考2個(gè)問(wèn)題
- 在讀已提交(Read Commited)級(jí)別下，出現(xiàn)不可重復(fù)讀問(wèn)題怎么辦?需要解決么?
不用解決，這個(gè)問(wèn)題是可以接受的!畢竟你數(shù)據(jù)都已經(jīng)提交了，讀出來(lái)本身就沒(méi)有太大問(wèn)題!Oracle ,SqlServer 默認(rèn)隔離級(jí)別就是RC，我們也沒(méi)有更改過(guò)它的默認(rèn)隔離級(jí)別.
- 在Oracle，SqlServer中都是選擇讀已提交(Read Commited)作為默認(rèn)的隔離級(jí)別，為什么Mysql不選擇讀已提交(Read Commited)作為默認(rèn)隔離級(jí)別，而選擇可重復(fù)讀(Repeatable Read)作為默認(rèn)的隔離級(jí)別呢?
歷史原因,早階段Mysql(5.1版本之前)的Binlog類(lèi)型Statement是默認(rèn)格式，即依次記錄系統(tǒng)接受的SQL請(qǐng)求;5.1及以后，MySQL提供了Row,Mixed,statement 3種Binlog格式, 當(dāng)binlog為statement格式,使用RC隔離級(jí)別時(shí),會(huì)出現(xiàn)BUG因此Mysql將可重復(fù)讀(Repeatable Read)作為默認(rèn)的隔離級(jí)別!
Binlog簡(jiǎn)介
Mysql binlog是二進(jìn)制日志文件，用于記錄mysql的數(shù)據(jù)更新或者潛在更新(比如DELETE語(yǔ)句執(zhí)行刪除而實(shí)際并沒(méi)有符合條件的數(shù)據(jù))，在mysql主從復(fù)制中就是依靠的binlog?？梢酝ㄟ^(guò)語(yǔ)句“show binlog events in 'binlogfile'”來(lái)查看binlog的具體事件類(lèi)型。binlog記錄的所有操作實(shí)際上都有對(duì)應(yīng)的事件類(lèi)型的
MySQL binlog的三種工作模式：Row(用到MySQL的特殊功能如存儲(chǔ)過(guò)程、觸發(fā)器、函數(shù)，又希望數(shù)據(jù)最大化一直則選擇Row模式，我們公司選擇的是row) 簡(jiǎn)介：日志中會(huì)記錄每一行數(shù)據(jù)被修改的情況，然后在slave端對(duì)相同的數(shù)據(jù)進(jìn)行修改。優(yōu)點(diǎn)：能清楚的記錄每一行數(shù)據(jù)修改的細(xì)節(jié) 缺點(diǎn)：數(shù)據(jù)量太大
Statement (默認(rèn))簡(jiǎn)介：每一條被修改數(shù)據(jù)的sql都會(huì)記錄到master的bin-log中，slave在復(fù)制的時(shí)候sql進(jìn)程會(huì)解析成和原來(lái)master端執(zhí)行過(guò)的相同的sql再次執(zhí)行。在主從同步中一般是不建議用statement模式的，因?yàn)闀?huì)有些語(yǔ)句不支持，比如語(yǔ)句中包含UUID函數(shù)，以及LOAD DATA IN FILE語(yǔ)句等 優(yōu)點(diǎn)：解決了 Row level下的缺點(diǎn)，不需要記錄每一行的數(shù)據(jù)變化，減少bin-log日志量，節(jié)約磁盤(pán)IO，提高新能 缺點(diǎn)：容易出現(xiàn)主從復(fù)制不一致
Mixed(混合模式)簡(jiǎn)介：結(jié)合了Row level和Statement level的優(yōu)點(diǎn)，同時(shí)binlog結(jié)構(gòu)也更復(fù)雜。
我們可以簡(jiǎn)單理解為binlog是一個(gè)記錄數(shù)據(jù)庫(kù)更改的文件,主從復(fù)制時(shí)需要此文件,具體細(xì)節(jié)先略過(guò)
主從不一致實(shí)操
binlog為STATEMENT格式，且隔離級(jí)別為**讀已提交(Read Commited)**時(shí)，有什么bug呢?測(cè)試表:
- mysql> select * from test;
- +----+------+------+
- | id | name | age |
- +----+------+------+
- | 1 | NULL | NULL |
- | 2 | NULL | NULL |
- | 3 | NULL | NULL |
- | 4 | NULL | NULL |
- | 5 | NULL | NULL |
- | 6 | NULL | NULL |
- +----+------+------+
- 6 rows in set (0.00 sec)
| Session1 | Session2 |
|---|---|
| mysql> set tx_isolation = 'read-committed'; | |
| Query OK, 0 rows affected, 1 warning (0.00 sec) | mysql> set tx_isolation = 'read-committed'; |
| Query OK, 0 rows affected, 1 warning (0.00 sec) | |
| begin; Query OK, 0 rows affected (0.00 sec) |
begin; Query OK, 0 rows affected (0.00 sec) |
| delete from test where 1=1; | |
| Query OK, 6 rows affected (0.00 sec) | |
| insert into test values (null,'name',100); | |
| Query OK, 1 row affected (0.00 sec) | |
| commit; | |
| Query OK, 0 rows affected (0.01 sec) | |
| commit; | |
| Query OK, 0 rows affected (0.01 sec) |
Master此時(shí)輸出
- select * from test;
- +----+------+------+
- | id | name | age |
- +----+------+------+
- | 7 | name | 100 |
- +----+------+------+
- 1 row in set (0.00 sec)
但是，你在此時(shí)在從(slave)上執(zhí)行該語(yǔ)句，得出輸出
- mysql> select * from test;
- Empty set (0.00 sec)
在master上執(zhí)行的順序?yàn)橄葎h后插!而此時(shí)binlog為STATEMENT格式，是基于事務(wù)記錄，在事務(wù)未提交前，二進(jìn)制日志先緩存，提交后再寫(xiě)入記錄的,因此順序?yàn)橄炔搴髣h!slave同步的是binglog，因此從機(jī)執(zhí)行的順序和主機(jī)不一致!slave在插入后刪除了所有數(shù)據(jù).
解決方案有兩種!(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)題!
RU和Serializable
項(xiàng)目中不太使用**讀未提交(Read UnCommitted)和串行化(Serializable)**兩個(gè)隔離級(jí)別，原因:
讀未提交(Read UnCommitted)
允許臟讀，也就是可能讀取到其他會(huì)話中未提交事務(wù)修改的數(shù)據(jù) 一個(gè)事務(wù)讀到另一個(gè)事務(wù)未提交讀數(shù)據(jù)
串行化(Serializable)
使用的悲觀鎖的理論，實(shí)現(xiàn)簡(jiǎn)單，數(shù)據(jù)更加安全，但是并發(fā)能力非常差。如果你的業(yè)務(wù)并發(fā)的特別少或者沒(méi)有并發(fā)，同時(shí)又要求數(shù)據(jù)及時(shí)可靠的話，可以使用這種模式。一般是使用mysql自帶分布式事務(wù)功能時(shí)才使用該隔離級(jí)別
RC和 RR
此時(shí)我們糾結(jié)的應(yīng)該就只有一個(gè)問(wèn)題了:隔離級(jí)別是用讀已提交還是可重復(fù)讀?
接下來(lái)對(duì)這兩種級(jí)別進(jìn)行對(duì)比的第一種情況:
在RR隔離級(jí)別下，存在間隙鎖，導(dǎo)致出現(xiàn)死鎖的幾率比RC大的多!
實(shí)現(xiàn)一個(gè)簡(jiǎn)單的間隙鎖例子
- select * from test where id <11 ;
- +----+------+------+
- | id | name | age |
- +----+------+------+
- | 1 | NULL | NULL |
- | 2 | NULL | NULL |
- | 3 | NULL | NULL |
- | 4 | NULL | NULL |
- | 5 | NULL | NULL |
- | 6 | NULL | NULL |
- | 7 | name | 7 |
- +----+------+------+
- 7 rows in set (0.00 sec)
| session1 | session2 |
|---|---|
| mysql> set tx_isolation = 'repeatable-read'; | |
| Query OK, 0 rows affected, 1 warning (0.00 sec) | mysql> set tx_isolation = 'repeatable-read'; |
| Query OK, 0 rows affected, 1 warning (0.00 sec) | |
| Begin; | |
| select * from test where id <11 for update; | |
| insert into test values(null,'name',9); //被阻塞! | |
| commit; | |
| Query OK, 0 rows affected (0.00 sec) | |
| Query OK, 1 row affected (12.23 sec) //鎖釋放后完成了操作 |
在RR隔離級(jí)別下，可以鎖住(-∞，10] 這個(gè)間隙，防止其他事務(wù)插入數(shù)據(jù)!而在RC隔離級(jí)別下，不存在間隙鎖，其他事務(wù)是可以插入數(shù)據(jù)!
ps:在RC隔離級(jí)別下并不是不會(huì)出現(xiàn)死鎖，只是出現(xiàn)幾率比RR低而已
鎖表和鎖行
在RR隔離級(jí)別下，條件列未命中索引會(huì)鎖表!而在RC隔離級(jí)別下，只鎖行
- select * from test;
- +----+------+------+
- | id | name | age |
- +----+------+------+
- | 8 | name | 11 |
- | 9 | name | 9 |
- | 10 | name | 15 |
- | 11 | name | 15 |
- | 12 | name | 16 |
- +----+------+------+
鎖表的例子:
| session1 | session2 |
|---|---|
| Begin; | |
| update test set age = age+1 where age = 15; | |
| Rows matched: 2 Changed: 2 Warnings: 0 | |
| insert into test values(null,'test',15); | |
| ERROR 1205 (HY000): Lock wait timeout exceeded; | |
| Commit; |
session2插入失敗 查詢(xún) 數(shù)據(jù)顯示:
- select * from test;
- +----+------+------+
- | id | name | age |
- +----+------+------+
- | 8 | name | 11 |
- | 9 | name | 9 |
- | 10 | name | 16 |
- | 11 | name | 16 |
- | 12 | name | 16 |
- +----+------+------+
半一致性讀(semi-consistent)特性
在RC隔離級(jí)別下，半一致性讀(semi-consistent)特性增加了update操作的并發(fā)性!
在5.1.15的時(shí)候，innodb引入了一個(gè)概念叫做“semi-consistent”，減少了更新同一行記錄時(shí)的沖突，減少鎖等待。所謂半一致性讀就是，一個(gè)update語(yǔ)句，如果讀到一行已經(jīng)加鎖的記錄，此時(shí)InnoDB返回記錄最近提交的版本,判斷此版本是否滿足where條件。若滿足則重新發(fā)起一次讀操作，此時(shí)會(huì)讀取行的最新版本并加鎖!
建議
在RC級(jí)別下,用的binlog為row格式，是基于行的復(fù)制,Innodb的創(chuàng)始人也是建議binlog使用該格式
互聯(lián)網(wǎng)項(xiàng)目請(qǐng)用：讀已提交(Read Commited)這個(gè)隔離級(jí)別
總結(jié)
由于歷史原因,老版本Mysql的binlog使用statement格式,不使用RR隔離級(jí)別會(huì)導(dǎo)致主從不一致的情況
目前(5.1版本之后)我們使用row格式的binlog 配合RC隔離級(jí)別可以實(shí)現(xiàn)更好的并發(fā)性能.
