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

前言:MYSQL應用

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

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

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

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

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

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

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

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

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

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

Read Committed(讀取提交內(nèi)容)
在RC級別中,數(shù)據(jù)的讀取都是不加鎖的,但是數(shù)據(jù)的寫入、修改和刪除是需要加鎖的.效果如下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; 

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

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

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

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

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

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

RC(不可重讀)模式下的展現(xiàn)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修改的數(shù)據(jù),和第一次查詢的結(jié)果不一樣,是不可重讀的.MYSQL應用

commit; 

事務B修改id=1的數(shù)據(jù)提交之后,事務A同樣的查詢,后一次和前一次的結(jié)果不一樣,這就是不可重讀(重新讀取產(chǎn)生的結(jié)果不一樣).這就很可能帶來一些問題,那么我們來看看在RR級別中MySQL的表現(xiàn)：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修改的數(shù)據(jù),和第一次sql讀取的一樣,是可重復讀的.MYSQL應用

沒有讀到事務C新添加的數(shù)據(jù).MYSQL應用

commit; 

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

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

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

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

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

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

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

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

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

要說明的是,MVCC的實現(xiàn)沒有固定的規(guī)范,每個數(shù)據(jù)庫都會有不同的實現(xiàn)方式,這里討論的是InnoDB的MVCC.MYSQL應用

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

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

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

MYSQL應用

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

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

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

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

快照讀：就是select
MYSQL應用

select * from table ....;

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

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

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

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

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

行鎖可以防止不同事務版本的數(shù)據(jù)修改提交時造成數(shù)據(jù)沖突的情況.但如何避免別的事務插入數(shù)據(jù)就成了問題.我們可以看看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執(zhí)行.
通過對比我們可以發(fā)現(xiàn),在RC級別中,事務A修改了所有teacher_id=30的數(shù)據(jù),但是當事務Binsert進新數(shù)據(jù)后,事務A發(fā)現(xiàn)莫名其妙多了一行teacher_id=30的數(shù)據(jù),而且沒有被之前的update語句所修改,這就是“當前讀”的幻讀.MYSQL應用

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

MySQL是這么實現(xiàn)的：MYSQL應用

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

MYSQL應用

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

Innodb將這段數(shù)據(jù)分成幾個個區(qū)間MYSQL應用

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

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

受限于這種實現(xiàn)方式,Innodb很多時候會鎖住不需要鎖的區(qū)間.如下所示：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]區(qū)間,即使沒有修改任何數(shù)據(jù),Innodb也會在這個區(qū)間加gap鎖,而其它區(qū)間不會影響,事務C正常插入.MYSQL應用

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

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

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

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

轉(zhuǎn)載請注明本頁網(wǎng)址：
http://www.fzlkiss.com/jiaocheng/4066.html