《Mysql必讀MySQL中Innodb的事務隔離級別和鎖的關系的講解教程》要點：
本文介紹了Mysql必讀MySQL中Innodb的事務隔離級別和鎖的關系的講解教程，希望對您有用。如果有疑問，可以聯系我們。

前言:MYSQL應用

我們都知道事務的幾種性質,數據庫為了維護這些性質,尤其是一致性和隔離性,一般使用加鎖這種方式.同時數據庫又是個高并發的應用,同一時間會有大量的并發訪問,如果加鎖過度,會極大的降低并發處理能力.所以對于加鎖的處理,可以說就是數據庫對于事務處理的精髓所在.這里通過分析MySQL中InnoDB引擎的加鎖機制,來拋磚引玉,讓讀者更好的理解,在事務處理中數據庫到底做了什么.MYSQL應用

一次封鎖or兩段鎖?
因為有大量的并發訪問,為了預防死鎖,一般應用中推薦使用一次封鎖法,就是在方法的開始階段,已經預先知道會用到哪些數據,然后全部鎖住,在方法運行之后,再全部解鎖.這種方式可以有效的避免循環死鎖,但在數據庫中卻不適用,因為在事務開始階段,數據庫并不知道會用到哪些數據.
數據庫遵循的是兩段鎖協議,將事務分成兩個階段,加鎖階段和解鎖階段(所以叫兩段鎖)MYSQL應用

加鎖階段：在該階段可以進行加鎖操作.在對任何數據進行讀操作之前要申請并獲得S鎖(共享鎖,其它事務可以繼續加共享鎖,但不能加排它鎖),在進行寫操作之前要申請并獲得X鎖(排它鎖,其它事務不能再獲得任何鎖).加鎖不成功,則事務進入等待狀態,直到加鎖成功才繼續執行.
解鎖階段：當事務釋放了一個封鎖以后,事務進入解鎖階段,在該階段只能進行解鎖操作不能再進行加鎖操作.
事務?????????????????????? 加鎖/解鎖處理
begin；?
insert into test .....?加insert對應的鎖
update test set...?加update對應的鎖
delete from test ....?加delete對應的鎖
commit;?事務提交時,同時釋放insert、update、delete對應的鎖
這種方式雖然無法避免死鎖,但是兩段鎖協議可以保證事務的并發調度是串行化(串行化很重要,尤其是在數據恢復和備份的時候)的.MYSQL應用

事務中的加鎖方式
事務的四種隔離級別
在數據庫操作中,為了有效保證并發讀取數據的正確性,提出的事務隔離級別.我們的數據庫鎖,也是為了構建這些隔離級別存在的.MYSQL應用

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

• 未提交讀(Read uncommitted)?可能?可能?可能
• 已提交讀(Read committed)?不可能?可能?可能
• 可重復讀(Repeatable read)?不可能?不可能?可能
• 可串行化(Serializable )?不可能?不可能?不可能

未提交讀(Read Uncommitted)：允許臟讀,也就是可能讀取到其他會話中未提交事務修改的數據MYSQL應用

提交讀(Read Committed)：只能讀取到已經提交的數據.Oracle等多數數據庫默認都是該級別 (不重復讀)
可重復讀(Repeated Read)：可重復讀.在同一個事務內的查詢都是事務開始時刻一致的,InnoDB默認級別.在SQL標準中,該隔離級別消除了不可重復讀,但是還存在幻象讀
串行讀(Serializable)：完全串行化的讀,每次讀都需要獲得表級共享鎖,讀寫相互都會阻塞
Read Uncommitted這種級別,數據庫一般都不會用,而且任何操作都不會加鎖,這里就不討論了.MYSQL應用

MySQL中鎖的種類
MySQL中鎖的種類很多,有常見的表鎖和行鎖,也有新加入的Metadata Lock等等,表鎖是對一整張表加鎖,雖然可分為讀鎖和寫鎖,但畢竟是鎖住整張表,會導致并發能力下降,一般是做ddl處理時使用.MYSQL應用

行鎖則是鎖住數據行,這種加鎖方法比較復雜,但是由于只鎖住有限的數據,對于其它數據不加限制,所以并發能力強,MySQL一般都是用行鎖來處理并發事務.這里主要討論的也就是行鎖.MYSQL應用

Read Committed(讀取提交內容)
在RC級別中,數據的讀取都是不加鎖的,但是數據的寫入、修改和刪除是需要加鎖的.效果如下MYSQL應用

MySQL> show create table class_teacher \G\
Table: class_teacher
Create Table: CREATE TABLE `class_teacher` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `class_name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
 `teacher_id` int(11) NOT NULL,
 PRIMARY KEY (`id`),
 KEY `idx_teacher_id` (`teacher_id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

1 row in set (0.02 sec)

MySQL> select * from class_teacher;

+----+--------------+------------+
| id | class_name  | teacher_id |
+----+--------------+------------+
| 1 | 初三一班   |     1 |
| 3 | 初二一班   |     2 |
| 4 | 初二二班   |     2 |
+----+--------------+------------+

由于MySQL的InnoDB默認是使用的RR級別,所以我們先要將該session開啟成RC級別,并且設置binlog的模式MYSQL應用

SET session transaction isolation level read committed;
SET SESSION binlog_format = 'ROW';

(或者是MIXED)
事務A?事務B
begin;?begin;
MYSQL應用

update class_teacher set class_name='初三二班' where teacher_id=1; update class_teacher set class_name='初三三班' where teacher_id=1;

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

commit; 

為了防止并發過程中的修改沖突,事務A中MySQL給teacher_id=1的數據行加鎖,并一直不commit(釋放鎖),那么事務B也就一直拿不到該行鎖,wait直到超時.MYSQL應用

這時我們要注意到,teacher_id是有索引的,如果是沒有索引的class_name呢?update class_teacher set teacher_id=3 where class_name = '初三一班';
那么MySQL會給整張表的所有數據行的加行鎖.這里聽起來有點不可思議,但是當sql運行的過程中,MySQL并不知道哪些數據行是 class_name = '初三一班'的(沒有索引嘛),如果一個條件無法通過索引快速過濾,存儲引擎層面就會將所有記錄加鎖后返回,再由MySQL Server層進行過濾.MYSQL應用

但在實際使用過程當中,MySQL做了一些改進,在MySQL Server過濾條件,發現不滿足后,會調用unlock_row方法,把不滿足條件的記錄釋放鎖 (違背了二段鎖協議的約束).這樣做,保證了最后只會持有滿足條件記錄上的鎖,但是每條記錄的加鎖操作還是不能省略的.可見即使是MySQL,為了效率也是會違反規范的.(參見《高性能MySQL》中文第三版p181)MYSQL應用

這種情況同樣適用于MySQL的默認隔離級別RR.所以對一個數據量很大的表做批量修改的時候,如果無法使用相應的索引,MySQL Server過濾數據的的時候特別慢,就會出現雖然沒有修改某些行的數據,但是它們還是被鎖住了的現象.MYSQL應用

Repeatable Read(可重讀)
這是MySQL中InnoDB默認的隔離級別.我們姑且分“讀”和“寫”兩個模塊來講解.MYSQL應用

讀
讀就是可重讀,可重讀這個概念是一事務的多個實例在并發讀取數據時,會看到同樣的數據行,有點抽象,我們來看一下效果.MYSQL應用

RC(不可重讀)模式下的展現MYSQL應用

事務A?事務B
MYSQL應用

begin; 
begin;

select id,class_name,teacher_id from class_teacher where teacher_id=1;
id class_name teacher_id
1 初三二班 1
2 初三一班 1
update class_teacher set class_name='初三三班' where id=1;
commit;
select id,class_name,teacher_id from class_teacher where teacher_id=1;
id class_name teacher_id
1 初三三班 1
2 初三一班 1

讀到了事務B修改的數據,和第一次查詢的結果不一樣,是不可重讀的.MYSQL應用

commit; 

事務B修改id=1的數據提交之后,事務A同樣的查詢,后一次和前一次的結果不一樣,這就是不可重讀(重新讀取產生的結果不一樣).這就很可能帶來一些問題,那么我們來看看在RR級別中MySQL的表現：MYSQL應用

事務A?事務B?事務C
MYSQL應用

begin; 
begin;
begin;
select id,class_name,teacher_id from class_teacher where teacher_id=1;
id class_name teacher_id
1 初三二班 1
2 初三一班 1
update class_teacher set class_name='初三三班' where id=1;
commit;
insert into class_teacher values (null,'初三三班',1);
commit;
select id,class_name,teacher_id from class_teacher where teacher_id=1;
id class_name teacher_id
1 初三二班 1
2 初三一班 1

沒有讀到事務B修改的數據,和第一次sql讀取的一樣,是可重復讀的.MYSQL應用

沒有讀到事務C新添加的數據.MYSQL應用

commit; 

我們注意到,當teacher_id=1時,事務A先做了一次讀取,事務B中間修改了id=1的數據,并commit之后,事務A第二次讀到的數據和第一次完全相同.所以說它是可重讀的.那么MySQL是怎么做到的呢?這里姑且賣個關子,我們往下看.MYSQL應用

不可重復讀和幻讀的區別
很多人容易搞混不可重復讀和幻讀,確實這兩者有些相似.但不可重復讀重點在于update和delete,而幻讀的重點在于insert.MYSQL應用

如果使用鎖機制來實現這兩種隔離級別,在可重復讀中,該sql第一次讀取到數據后,就將這些數據加鎖,其它事務無法修改這些數據,就可以實現可重復讀了.但這種方法卻無法鎖住insert的數據,所以當事務A先前讀取了數據,或者修改了全部數據,事務B還是可以insert數據提交,這時事務A就會發現莫名其妙多了一條之前沒有的數據,這就是幻讀,不能通過行鎖來避免.需要Serializable隔離級別 ,讀用讀鎖,寫用寫鎖,讀鎖和寫鎖互斥,這么做可以有效的避免幻讀、不可重復讀、臟讀等問題,但會極大的降低數據庫的并發能力.MYSQL應用

所以說不可重復讀和幻讀最大的區別,就在于如何通過鎖機制來解決他們產生的問題.MYSQL應用

上文說的,是使用悲觀鎖機制來處理這兩種問題,但是MySQL、ORACLE、PostgreSQL等成熟的數據庫,出于性能考慮,都是使用了以樂觀鎖為理論基礎的MVCC(多版本并發控制)來避免這兩種問題.MYSQL應用

悲觀鎖和樂觀鎖
悲觀鎖
正如其名,它指的是對數據被外界(包括本系統當前的其他事務,以及來自外部系統的事務處理)修改持保守態度,因此,在整個數據處理過程中,將數據處于鎖定狀態.悲觀鎖的實現,往往依靠數據庫提供的鎖機制(也只有數據庫層提供的鎖機制才能真正保證數據訪問的排他性,否則,即使在本系統中實現了加鎖機制,也無法保證外部系統不會修改數據).MYSQL應用

在悲觀鎖的情況下,為了保證事務的隔離性,就需要一致性鎖定讀.讀取數據時給加鎖,其它事務無法修改這些數據.修改刪除數據時也要加鎖,其它事務無法讀取這些數據.MYSQL應用

樂觀鎖
相對悲觀鎖而言,樂觀鎖機制采取了更加寬松的加鎖機制.悲觀鎖大多數情況下依靠數據庫的鎖機制實現,以保證操作最大程度的獨占性.但隨之而來的就是數據庫性能的大量開銷,特別是對長事務而言,這樣的開銷往往無法承受.MYSQL應用

而樂觀鎖機制在一定程度上解決了這個問題.樂觀鎖,大多是基于數據版本( Version )記錄機制實現.何謂數據版本?即為數據增加一個版本標識,在基于數據庫表的版本解決方案中,一般是通過為數據庫表增加一個 “version” 字段來實現.讀取出數據時,將此版本號一同讀出,之后更新時,對此版本號加一.此時,將提交數據的版本數據與數據庫表對應記錄的當前版本信息進行比對,如果提交的數據版本號大于數據庫表當前版本號,則予以更新,否則認為是過期數據.MYSQL應用

要說明的是,MVCC的實現沒有固定的規范,每個數據庫都會有不同的實現方式,這里討論的是InnoDB的MVCC.MYSQL應用

MVCC在MySQL的InnoDB中的實現
在InnoDB中,會在每行數據后添加兩個額外的隱藏的值來實現MVCC,這兩個值一個記錄這行數據何時被創建,另外一個記錄這行數據何時過期(或者被刪除). 在實際操作中,存儲的并不是時間,而是事務的版本號,每開啟一個新事務,事務的版本號就會遞增. 在可重讀Repeatable reads事務隔離級別下：MYSQL應用

• SELECT時,讀取創建版本號<=當前事務版本號,刪除版本號為空或>當前事務版本號.
• INSERT時,保存當前事務版本號為行的創建版本號
• DELETE時,保存當前事務版本號為行的刪除版本號
• UPDATE時,插入一條新紀錄,保存當前事務版本號為行創建版本號,同時保存當前事務版本號到原來刪除的行
• 通過MVCC,雖然每行記錄都需要額外的存儲空間,更多的行檢查工作以及一些額外的維護工作,但可以減少鎖的使用,大多數讀操作都不用加鎖,讀數據操作很簡單,性能很好,并且也能保證只會讀取到符合標準的行,也只鎖住必要行.

我們不管從數據庫方面的教課書中學到,還是從網絡上看到,大都是上文中事務的四種隔離級別這一模塊列出的意思,RR級別是可重復讀的,但無法解決幻讀,而只有在Serializable級別才能解決幻讀.于是我就加了一個事務C來展示效果.在事務C中添加了一條teacher_id=1的數據commit,RR級別中應該會有幻讀現象,事務A在查詢teacher_id=1的數據時會讀到事務C新加的數據.但是測試后發現,在MySQL中是不存在這種情況的,在事務C提交后,事務A還是不會讀到這條數據.可見在MySQL的RR級別中,是解決了幻讀的讀問題的.參見下圖
MYSQL應用

MYSQL應用

讀問題解決了,根據MVCC的定義,并發提交數據時會出現沖突,那么沖突時如何解決呢?我們再來看看InnoDB中RR級別對于寫數據的處理.MYSQL應用

“讀”與“讀”的區別
可能有讀者會疑惑,事務的隔離級別其實都是對于讀數據的定義,但到了這里,就被拆成了讀和寫兩個模塊來講解.這主要是因為MySQL中的讀,和事務隔離級別中的讀,是不一樣的.MYSQL應用

我們且看,在RR級別中,通過MVCC機制,雖然讓數據變得可重復讀,但我們讀到的數據可能是歷史數據,是不及時的數據,不是數據庫當前的數據!這在一些對于數據的時效特別敏感的業務中,就很可能出問題.MYSQL應用

對于這種讀取歷史數據的方式,我們叫它快照讀 (snapshot read),而讀取數據庫當前版本數據的方式,叫當前讀 (current read).很顯然,在MVCC中：MYSQL應用

快照讀：就是select
MYSQL應用

select * from table ....;

當前讀：特殊的讀操作,插入/更新/刪除操作,屬于當前讀,處理的都是當前的數據,需要加鎖.
MYSQL應用

select * from table where ? lock in share mode;
select * from table where ? for update;
insert;
update ;
delete;

事務的隔離級別實際上都是定義了當前讀的級別,MySQL為了減少鎖處理(包括等待其它鎖)的時間,提升并發能力,引入了快照讀的概念,使得select不用加鎖.而update、insert這些“當前讀”,就需要另外的模塊來解決了.MYSQL應用

寫("當前讀")
事務的隔離級別中雖然只定義了讀數據的要求,實際上這也可以說是寫數據的要求.上文的“讀”,實際是講的快照讀；而這里說的“寫”就是當前讀了.
為了解決當前讀中的幻讀問題,MySQL事務使用了Next-Key鎖.MYSQL應用

Next-Key鎖
Next-Key鎖是行鎖和GAP(間隙鎖)的合并,行鎖上文已經介紹了,接下來說下GAP間隙鎖.MYSQL應用

行鎖可以防止不同事務版本的數據修改提交時造成數據沖突的情況.但如何避免別的事務插入數據就成了問題.我們可以看看RR級別和RC級別的對比MYSQL應用

RC級別：MYSQL應用

事務A?事務B
MYSQL應用

begin; 
begin;
select id,class_name,teacher_id from class_teacher where teacher_id=30;
id class_name teacher_id
2 初三二班 30
update class_teacher set class_name='初三四班' where teacher_id=30; 
insert into class_teacher values (null,'初三二班',30);
commit;
select id,class_name,teacher_id from class_teacher where teacher_id=30;
id class_name teacher_id
2 初三四班 30
10 初三二班 30
RR級別：
事務A 事務B
begin; 
begin;
select id,class_name,teacher_id from class_teacher where teacher_id=30;
id class_name teacher_id
2 初三二班 30 
update class_teacher set class_name='初三四班' where teacher_id=30; 
insert into class_teacher values (null,'初三二班',30);
waiting....
select id,class_name,teacher_id from class_teacher where teacher_id=30;
id class_name teacher_id
2 初三四班 30 
commit; 

事務Acommit后,事務B的insert執行.
通過對比我們可以發現,在RC級別中,事務A修改了所有teacher_id=30的數據,但是當事務Binsert進新數據后,事務A發現莫名其妙多了一行teacher_id=30的數據,而且沒有被之前的update語句所修改,這就是“當前讀”的幻讀.MYSQL應用

RR級別中,事務A在update后加鎖,事務B無法插入新數據,這樣事務A在update前后讀的數據保持一致,避免了幻讀.這個鎖,就是Gap鎖.MYSQL應用

MySQL是這么實現的：MYSQL應用

在class_teacher這張表中,teacher_id是個索引,那么它就會維護一套B+樹的數據關系,為了簡化,我們用鏈表結構來表達(實際上是個樹形結構,但原理相同)
MYSQL應用

MYSQL應用

如圖所示,InnoDB使用的是聚集索引,teacher_id身為二級索引,就要維護一個索引字段和主鍵id的樹狀結構(這里用鏈表形式表現),并保持順序排列.MYSQL應用

Innodb將這段數據分成幾個個區間MYSQL應用

(negative infinity, 5],
(5,30],
(30,positive infinity)；
update class_teacher set class_name='初三四班' where teacher_id=30;

不僅用行鎖,鎖住了相應的數據行；同時也在兩邊的區間,(5,30]和(30,positive infinity),都加入了gap鎖.這樣事務B就無法在這個兩個區間insert進新數據.MYSQL應用

受限于這種實現方式,Innodb很多時候會鎖住不需要鎖的區間.如下所示：MYSQL應用

事務A?事務B?事務C
MYSQL應用

begin; begin; begin;
select id,class_name,teacher_id from class_teacher;
id class_name teacher_id
1 初三一班 
5
2 初三二班 30 
update class_teacher set class_name='初一一班' where teacher_id=20; 
insert into class_teacher values (null,'初三五班',10);
waiting .....
insert into class_teacher values (null,'初三五班',40);
commit; 事務A commit之后,這條語句才插入成功 commit;
commit; 

update的teacher_id=20是在(5,30]區間,即使沒有修改任何數據,Innodb也會在這個區間加gap鎖,而其它區間不會影響,事務C正常插入.MYSQL應用

如果使用的是沒有索引的字段,比如update class_teacher set teacher_id=7 where class_name='初三八班(即使沒有匹配到任何數據)',那么會給全表加入gap鎖.同時,它不能像上文中行鎖一樣經過MySQL Server過濾自動解除不滿足條件的鎖,因為沒有索引,則這些字段也就沒有排序,也就沒有區間.除非該事務提交,否則其它事務無法插入任何數據.MYSQL應用

行鎖防止別的事務修改或刪除,GAP鎖防止別的事務新增,行鎖和GAP鎖結合形成的的Next-Key鎖共同解決了RR級別在寫數據時的幻讀問題.MYSQL應用

Serializable
這個級別很簡單,讀加共享鎖,寫加排他鎖,讀寫互斥.使用的悲觀鎖的理論,實現簡單,數據更加安全,但是并發能力非常差.如果你的業務并發的特別少或者沒有并發,同時又要求數據及時可靠的話,可以使用這種模式.MYSQL應用

這里要吐槽一句,不要看到select就說不會加鎖了,在Serializable這個級別,還是會加鎖的!MYSQL應用

轉載請注明本頁網址：
http://www.snjht.com/jiaocheng/4066.html