[[405301]]

本文轉載自微信公眾號「java寶典」，作者iTengyu。轉載本文請聯系java寶典公眾號。

 群里有小伙伴面試時,碰到面試官提了個很刁鉆的問題:Mysql為何使用可重復讀(Repeatable read)為默認隔離級別???

下面進入正題:

我們都知道事務的幾種性質 :原子性、一致性、隔離性和持久性 (ACID)

為了維持一致性和隔離性,一般使用加鎖這種方式來處理,但是加鎖相對帶來的是并發處理能力的降低

而數據庫是個高并發的應用,因此對于加鎖的處理是事務的精髓.

下面我們來了解一下封鎖協議,以及事務在數據庫中做了什么

封鎖協議(Locking Protocol)

MySQL的鎖系統：shared lock 和 exclusive lock 即共享鎖和排他鎖，也叫讀鎖(S)和寫鎖(X),共享鎖和排他鎖都屬于悲觀鎖。排他鎖又可以可以分為行鎖和表鎖。

封鎖協議(Locking Protocol): 在使用X鎖或S鎖對數據加鎖時,約定的一些規則.例如何時申請X或S鎖,持續時間,何時釋放鎖等.

一級、二級、三級封鎖協議

對封鎖方式規定不同的規則，就形成了各種不同的封鎖協議,不同的封鎖協議，為并發操作的正確性提供不同程度的保證

一級封鎖協議

一級封鎖協議定義：事務T在修改數據R之前必須先對其加X鎖(排他鎖)，直到事務結束才釋放。事務結束包括正常結束(COMMIT)和非正常結束(ROLLBACK)。

一級封鎖協議可以防止丟失修改，并保證事務T是可恢復的。使用一級封鎖協議可以解決丟失修改問題。

在一級封鎖協議中，如果僅僅是讀數據不對其進行修改，是不需要加鎖的，它不能保證可重復讀和不讀“臟”數據。

二級封鎖協議

二級封鎖協議定義：一級封鎖協議加上事務T在讀取數據R之前必須先對其加S鎖(共享鎖)，讀完后釋放S鎖。事務的加鎖和解鎖嚴格分為兩個階段，第一階段加鎖，第二階段解鎖。

• 加鎖階段: 在對任何數據進行讀操作之前要申請并獲得S鎖(共享鎖，其它事務可以繼續加共享鎖，但不能加排它鎖)，在進行寫操作之前要申請并獲得X鎖(排它鎖，其它事務不能再獲得任何鎖)。加鎖不成功，則事務進入等待狀態，直到加鎖成功才繼續執行。
• 解鎖階段:當事務釋放了一個封鎖以后，事務進入解鎖階段，在該階段只能進行解鎖操作不能再進行加鎖操作。

二級封鎖協議除防止了丟失修改，還可以進一步防止讀“臟”數據。但在二級封鎖協議中，由于讀完數據后釋放S鎖，所以它不能保證可重復讀。

二級封鎖的目的是保證并發調度的正確性。就是說，如果事務滿足兩段鎖協議，那么事務的并發調度策略是串行性的。保證事務的并發調度是串行化(串行化很重要，尤其是在數據恢復和備份的時候)

三級封鎖協議

三級封鎖協議定義：一級封鎖協議加上事務T在讀取數據R之前必須先對其加S鎖(共享鎖)，直到事務結束才釋放。在一級封鎖協議(一級封鎖協議：修改之前先加X鎖，事務完成釋放)的基礎上加上S鎖，事務結束后釋放S鎖

三級封鎖協議除防止了丟失修改和不讀“臟”數據外，還進一步防止了不可重復讀。上述三級協議的主要區別在于什么操作需要申請封鎖，以及何時釋放。

事務四種隔離級別

在數據庫操作中，為了有效保證并發讀取數據的正確性，提出的事務隔離級別。上面提到的封鎖協議 ，也是為了構建這些隔離級別存在的。

隔離級別 臟讀(Dirty Read) 不可重復讀(NonRepeatable Read) 幻讀(Phantom Read)

隔離級別	臟讀（Dirty Read）	不可重復讀（NonRepeatable Read）	幻讀（Phantom Read）
未提交讀（Read uncommitted）	可能	可能	可能
已提交讀（Read committed）	不可能	可能	可能
可重復讀（Repeatable read）	不可能	不可能	可能
可串行化（Serializable ）	不可能	不可能	不可能

為什么是RR

一般的DBMS系統，默認都會使用讀提交(Read-Comitted，RC)作為默認隔離級別，如Oracle、SQL Server等，而MySQL卻使用可重復讀(Read-Repeatable，RR)。要知道，越高的隔離級別，能解決的數據一致性問題越多，理論上性能的損耗更大，且并發性越低。隔離級別依次為: SERIALIZABLE > RR > RC > RU

我們可以通過以下語句設置和獲取數據庫的隔離級別:

查看系統的隔離級別:

mysql> select @@global.tx_isolation isolation; 
+-----------------+ 
| isolation       | 
+-----------------+ 
| REPEATABLE-READ | 
+-----------------+ 
1 row in set, 1 warning (0.00 sec) 

查看當前會話的 隔離級別:

mysql> select @@tx_isolation;

mysql> select @@tx_isolation; 
+----------------+ 
| @@tx_isolation | 
+----------------+ 
| READ-COMMITTED | 
+----------------+ 
1 row in set, 1 warning (0.00 sec) 

設置會話的隔離級別，隔離級別由低到高設置依次為:

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; 

設置當前系統的隔離級別，隔離級別由低到高設置依次為:

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; 

可重復讀(Repeated Read)：可重復讀。基于鎖機制并發控制的DBMS需要對選定對象的讀鎖(read locks)和寫鎖(write locks)一直保持到事務結束，但不要求“范圍鎖(range-locks)”，因此可能會發生“幻影讀(phantom reads)” 在該事務級別下，保證同一個事務從開始到結束獲取到的數據一致。是Mysql的默認事務級別。

下面我們先來思考2個問題

• 在讀已提交(Read Commited)級別下，出現不可重復讀問題怎么辦?需要解決么?

不用解決，這個問題是可以接受的!畢竟你數據都已經提交了，讀出來本身就沒有太大問題!Oracle ,SqlServer 默認隔離級別就是RC，我們也沒有更改過它的默認隔離級別.

• 在Oracle，SqlServer中都是選擇讀已提交(Read Commited)作為默認的隔離級別，為什么Mysql不選擇讀已提交(Read Commited)作為默認隔離級別，而選擇可重復讀(Repeatable Read)作為默認的隔離級別呢?

歷史原因,早階段Mysql(5.1版本之前)的Binlog類型Statement是默認格式，即依次記錄系統接受的SQL請求;5.1及以后，MySQL提供了Row,Mixed,statement 3種Binlog格式, 當binlog為statement格式,使用RC隔離級別時,會出現BUG因此Mysql將可重復讀(Repeatable Read)作為默認的隔離級別!

Binlog簡介

Mysql binlog是二進制日志文件，用于記錄mysql的數據更新或者潛在更新(比如DELETE語句執行刪除而實際并沒有符合條件的數據)，在mysql主從復制中就是依靠的binlog。可以通過語句“show binlog events in 'binlogfile'”來查看binlog的具體事件類型。binlog記錄的所有操作實際上都有對應的事件類型的

MySQL binlog的三種工作模式：Row(用到MySQL的特殊功能如存儲過程、觸發器、函數，又希望數據最大化一直則選擇Row模式，我們公司選擇的是row) 簡介：日志中會記錄每一行數據被修改的情況，然后在slave端對相同的數據進行修改。優點：能清楚的記錄每一行數據修改的細節 缺點：數據量太大

Statement (默認)簡介：每一條被修改數據的sql都會記錄到master的bin-log中，slave在復制的時候sql進程會解析成和原來master端執行過的相同的sql再次執行。在主從同步中一般是不建議用statement模式的，因為會有些語句不支持，比如語句中包含UUID函數，以及LOAD DATA IN FILE語句等 優點：解決了 Row level下的缺點，不需要記錄每一行的數據變化，減少bin-log日志量，節約磁盤IO，提高新能 缺點：容易出現主從復制不一致

Mixed(混合模式)簡介：結合了Row level和Statement level的優點，同時binlog結構也更復雜。

我們可以簡單理解為binlog是一個記錄數據庫更改的文件,主從復制時需要此文件,具體細節先略過

主從不一致實操

binlog為STATEMENT格式，且隔離級別為**讀已提交(Read Commited)**時，有什么bug呢?測試表:

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此時輸出

select * from test; 
+----+------+------+ 
| id | name | age  | 
+----+------+------+ 
|  7 | name |  100 | 
+----+------+------+ 
1 row in set (0.00 sec) 

但是，你在此時在從(slave)上執行該語句，得出輸出

mysql> select * from test; 
Empty set (0.00 sec) 

在master上執行的順序為先刪后插!而此時binlog為STATEMENT格式，是基于事務記錄，在事務未提交前，二進制日志先緩存，提交后再寫入記錄的,因此順序為先插后刪!slave同步的是binglog，因此從機執行的順序和主機不一致!slave在插入后刪除了所有數據.

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

RU和Serializable

項目中不太使用**讀未提交(Read UnCommitted)和串行化(Serializable)**兩個隔離級別，原因:

讀未提交(Read UnCommitted)

允許臟讀，也就是可能讀取到其他會話中未提交事務修改的數據 一個事務讀到另一個事務未提交讀數據

串行化(Serializable)

使用的悲觀鎖的理論，實現簡單，數據更加安全，但是并發能力非常差。如果你的業務并發的特別少或者沒有并發，同時又要求數據及時可靠的話，可以使用這種模式。一般是使用mysql自帶分布式事務功能時才使用該隔離級別

RC和 RR

此時我們糾結的應該就只有一個問題了:隔離級別是用讀已提交還是可重復讀?

接下來對這兩種級別進行對比的第一種情況:

在RR隔離級別下，存在間隙鎖，導致出現死鎖的幾率比RC大的多!

實現一個簡單的間隙鎖例子

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隔離級別下，可以鎖住(-∞，10] 這個間隙，防止其他事務插入數據!而在RC隔離級別下，不存在間隙鎖，其他事務是可以插入數據!

ps:在RC隔離級別下并不是不會出現死鎖，只是出現幾率比RR低而已

鎖表和鎖行

在RR隔離級別下，條件列未命中索引會鎖表!而在RC隔離級別下，只鎖行

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插入失敗 查詢 數據顯示:

select * from test; 
+----+------+------+ 
| id | name | age  | 
+----+------+------+ 
|  8 | name |   11 | 
|  9 | name |    9 | 
| 10 | name |   16 | 
| 11 | name |   16 | 
| 12 | name |   16 | 
+----+------+------+ 

半一致性讀(semi-consistent)特性

在RC隔離級別下，半一致性讀(semi-consistent)特性增加了update操作的并發性!

在5.1.15的時候，innodb引入了一個概念叫做“semi-consistent”，減少了更新同一行記錄時的沖突，減少鎖等待。所謂半一致性讀就是，一個update語句，如果讀到一行已經加鎖的記錄，此時InnoDB返回記錄最近提交的版本,判斷此版本是否滿足where條件。若滿足則重新發起一次讀操作，此時會讀取行的最新版本并加鎖!

建議

在RC級別下,用的binlog為row格式，是基于行的復制,Innodb的創始人也是建議binlog使用該格式

互聯網項目請用：讀已提交(Read Commited)這個隔離級別

總結

由于歷史原因,老版本Mysql的binlog使用statement格式,不使用RR隔離級別會導致主從不一致的情況

目前(5.1版本之后)我們使用row格式的binlog 配合RC隔離級別可以實現更好的并發性能.