1、鎖的類型

InnoDB實現(xiàn)了如下兩種標(biāo)準(zhǔn)的行級鎖：

• 共享鎖（S Lock）：允許事務(wù)對一條行數(shù)據(jù)進(jìn)行讀取

• 排他鎖（X Lock）：允許事務(wù)對一條行數(shù)據(jù)進(jìn)行刪除或更新

如果一個事務(wù)T1已經(jīng)獲得了行r的共享鎖初橘， 那么另外的事務(wù)T2可以立即獲得行r的共享鎖验游， 因為讀取并沒有改變行 r 的數(shù)據(jù)充岛， 稱這種情況為鎖兼容 (Lock Compatible)。 但若有其他的事務(wù)T3想獲得行r的排他鎖耕蝉， 則其必須等待事務(wù)T1, T2釋放行r上的共享鎖——這種情況稱為鎖不兼容崔梗。因為獲取排他鎖一般是為了改變數(shù)據(jù)，所以不能同時進(jìn)行讀取或則其他寫入操作垒在。

image.png

從上表可以發(fā)現(xiàn)蒜魄，X鎖與任何鎖都不兼容，而S鎖僅和S鎖兼容爪膊。

此外权悟， InnoDB 存儲引擎支持多粒度鎖定， 這種鎖定允許事務(wù)在行級上的鎖和表級上的鎖同時存在推盛。為了支待在不同粒度上進(jìn)行加鎖操作峦阁， InnoDB 存儲引擎支持 一種額外的鎖方式， 稱之為意向鎖 (Intention Lock)耘成。意向鎖是將鎖定的對象分為多個層次榔昔， 意向鎖意味著事務(wù)希望在更細(xì)粒度上進(jìn)行加鎖。

InnoDB存儲引擎支持意向鎖設(shè)計比較簡練瘪菌，其意向鎖即為表級別的鎖撒会。設(shè)計目的主要是為了在事務(wù)中揭示下一行將被請求的鎖類型。其支持兩種意向鎖：

• 意向共享鎖（IS Lock）：事務(wù)想要獲得一張表中某幾行的共享鎖

• 意向排他鎖（IX Lock）：事務(wù)想要獲得一張表中某幾行的排他鎖

由于InnoDB存儲引擎支持的是行級別的鎖师妙，因此意向鎖不會阻塞除全表掃描以外的任何請求诵肛，它們的主要目的是為了表示是否有人請求鎖定表中的某一行數(shù)據(jù)。

如果沒有意向鎖默穴，當(dāng)已經(jīng)有人使用行鎖對表中的某一行進(jìn)行修改時怔檩，如果另外一個請求要對全表進(jìn)行修改，那么就需要對所有的行是否被鎖定進(jìn)行掃描蓄诽，在這種情況下薛训，效率是非常低的；不過仑氛，在引入意向鎖之后乙埃，當(dāng)有人使用行鎖對表中的某一行進(jìn)行修改之前，會先為表添加意向互斥鎖（IX）锯岖，再為行記錄添加互斥鎖（X）介袜，在這時如果有人嘗試對全表進(jìn)行修改就不需要判斷表中的每一行數(shù)據(jù)是否被加鎖了，只需要通過等待意向互斥鎖被釋放就可以了嚎莉。

故表級意向鎖和行級鎖的兼容性如下表所示：

image.png

2米酬、鎖的算法

InnoDB有三種行鎖的算法：

• Record Lock

  簡單說就是單個行記錄上加鎖，防止事務(wù)間修改或刪除數(shù)據(jù)趋箩。Record Lock總是會去鎖住索引記錄赃额，如果表建立的時候沒有設(shè)置任何一個索引加派，InnoDB存儲引擎會使用隱式的主鍵來進(jìn)行鎖定。

• Gap Lock

  間隙鎖跳芳，表示只鎖住一段范圍芍锦，不鎖記錄本身，通常表示兩個索引記錄之間飞盆，或者索引上的第一條記錄之前娄琉，或者最后一條記錄之后的鎖。

• Next-Key Lock

  Gap Lock + Record Lock吓歇，鎖定一個范圍及鎖定記錄本身孽水。例如一個索引有10, 11, 13, 20這四個值，那么該索引可能被Next-key Locking的區(qū)間為(負(fù)無窮, 10), (10, 11), (11, 13), (13, 20), (20, 正無窮)城看。需要理解一點女气，InnoDB中加鎖都是給所有記錄一條一條加鎖，并沒有一個直接的范圍可以直接鎖住测柠，所以會生成多個區(qū)間炼鞠。

MySQL默認(rèn)情況下使用RR的隔離級別，而Next-key Lock正是為了解決RR隔離級別下的不可重復(fù)讀問題和幻讀問題轰胁。所謂不可重復(fù)讀就是一個事務(wù)內(nèi)執(zhí)行相同的查詢谒主，會看到不同的行記錄，在RR隔離級別下這是不允許的赃阀。

不可重復(fù)讀和幻讀的區(qū)別：

1霎肯、不可重復(fù)讀的重點是修改；同樣的條件榛斯，第1次和第2次讀取的值不一樣姿现。
2、幻讀的重點在于新增或者刪除肖抱；同樣的條件， 第1次和第2次讀出來的記錄數(shù)不一樣

從控制角度來看

1异旧、不可重復(fù)讀只需要鎖住滿足條件的記錄
2意述、幻讀要鎖住滿足條件及其相近的記錄

假設(shè)索引上有記錄1，4吮蛹，5荤崇，8，12潮针，我們執(zhí)行類似語句SELECT … WHERE col > 10 FOR UPDATE术荤。如果我們不在(8, 12)之間加上Next-key Lock，另外一個會話就可能向其中插入一條記錄9每篷，再執(zhí)行一次相同的SELECT ... FOR UPDATE瓣戚，就會看到新插入的記錄端圈。這也是為什么MySQL插入一條記錄時，需要判斷下一條記錄上是否加鎖了子库，如果加鎖就需要等待舱权。

InnoDB對行的查詢默認(rèn)采用Next-key算法

然而，當(dāng)查詢條件為等值時仑嗅，且索引有唯一屬性時（就是只鎖定一條記錄）宴倍，InnoDB存儲引擎會對Next-Key Lock進(jìn)行優(yōu)化，將其降級為Record Lock仓技，即僅鎖住索引本身鸵贬，而不是一個范圍，因為此時不會產(chǎn)生重復(fù)讀問題脖捻。

3阔逼、鎖讀取

一致性非鎖定讀(consistent nonlocking read)是指InnoDB存儲引擎通過多版本控制(MVVC)讀取當(dāng)前數(shù)據(jù)庫中行數(shù)據(jù)的方式。如果讀取的行正在執(zhí)行DELETE或UPDATE操作郭变，這時讀取操作不會因此去等待行上鎖的釋放颜价。相反地，InnoDB會去讀取行的一個快照诉濒。

image.png

上圖直觀地展現(xiàn)了InnoDB一致性非鎖定讀的機制周伦。之所以稱其為非鎖定讀，是因為不需要等待行上排他鎖的釋放未荒∽ㄅ玻快照數(shù)據(jù)是指該行的之前版本的數(shù)據(jù)，每行記錄可能有多個版本片排，一般稱這種技術(shù)為行多版本技術(shù)寨腔。由此帶來的并發(fā)控制，稱之為多版本并發(fā)控制(Multi Version Concurrency Control, MVVC)率寡。InnoDB是通過undo log來實現(xiàn)MVVC迫卢。undo log本身用來在事務(wù)中回滾數(shù)據(jù)，因此快照數(shù)據(jù)本身是沒有額外開銷冶共。此外乾蛤，讀取快照數(shù)據(jù)是不需要上鎖的，因為沒有事務(wù)需要對歷史的數(shù)據(jù)進(jìn)行修改操作捅僵。

一致性非鎖定讀是InnoDB默認(rèn)的讀取方式家卖，即讀取不會占用和等待行上的鎖。但是并不是在每個事務(wù)隔離級別下都是采用此種方式庙楚。此外上荡，即使都是使用一致性非鎖定讀，但是對于快照數(shù)據(jù)的定義也各不相同馒闷。

在事務(wù)隔離級別READ COMMITTED和REPEATABLE READ下酪捡，InnoDB使用一致性非鎖定讀叁征。然而，對于快照數(shù)據(jù)的定義卻不同沛善。

我們下面舉個例子來詳細(xì)說明一下上述的情況航揉。

image.png

首先在會話A中顯示地開啟一個事務(wù)，然后讀取test表中的id為1的數(shù)據(jù)金刁，但是事務(wù)并沒有提交帅涂。與此同時，在開啟另一個會話B尤蛮，將test表中id為1的記錄修改為id=3媳友，但是事務(wù)同樣也沒有提交，這樣id=1的行其實加了一個排他鎖产捞。

由于InnoDB在READ COMMITTED和REPEATABLE READ事務(wù)隔離級別下使用一致性非鎖定讀醇锚，這時如果會話A再次讀取id為1的記錄，仍然能夠讀取到相同的數(shù)據(jù)坯临。此時焊唬，READ COMMITTED和REPEATABLE READ事務(wù)隔離級別沒有任何區(qū)別。

當(dāng)會話B提交事務(wù)后看靠，會話A再次運行SELECT * FROM test WHERE id = 1的SQL語句時赶促，兩個事務(wù)隔離級別下得到的結(jié)果就不一樣了：

• 對于READ COMMITTED的事務(wù)隔離級別，它總是讀取行的最新版本挟炬，如果行被鎖定了鸥滨，則讀取該行版本的最新一個快照。因為會話B的事務(wù)已經(jīng)提交谤祖，所以在該隔離級別下上述SQL語句的結(jié)果集是空的婿滓。

• 對于REPEATABLE READ的事務(wù)隔離級別，總是讀取事務(wù)開始時的行數(shù)據(jù)粥喜，因此凸主，在該隔離級別下，上述SQL語句仍然會獲得相同的數(shù)據(jù)额湘。

在默認(rèn)情況下秕铛，即事務(wù)的隔離級別是repeatable read模式下，InnoDB存儲引擎的SELECT操作使用的是一致性非鎖定讀缩挑。但是在某些情況下，用戶需要顯示的讀取數(shù)據(jù)操作進(jìn)行加鎖保證數(shù)據(jù)邏輯的一致性鬓梅。

InnoDB提供了兩種方式實現(xiàn)一致性鎖定讀：

• select … for udpate供置，對讀取的行加了X鎖

• select … lock in share mode，對讀取的行加了S鎖

需要注意的是绽快，以上兩種語句必須在一個事務(wù)當(dāng)中芥丧，當(dāng)事務(wù)提交了紧阔，鎖也就釋放了。

4续担、阻塞

因為不同鎖之間的兼容性關(guān)系擅耽，有時候一個事務(wù)中的鎖需要等待另一個事務(wù)中的鎖釋放它所占用的資源，這就是阻塞物遇。

在InnoDB中乖仇，參數(shù)innodb_lock_wait_timeout用來控制等待的時間，innodb_rollback_on_timeout用來設(shè)定是否在等待超時后回滾询兴。前者是動態(tài)的乃沙，后者是靜態(tài)的。

mysql> show variables like 'innodb_lock_wait_timeout'\G;
*************************** 1. row
Variable_name: innodb_lock_wait_timeout
Value: 50


mysql> show variables like 'innodb_rollback_on_timeout'\G;
1. row
: innodb_rollback_on_timeout
Value: OFF

5诗舰、死鎖

死鎖是指兩個或兩個以上的事務(wù)在執(zhí)行過程中警儒，因爭奪資源而造成的一種相互等待的現(xiàn)象。若無外力作用眶根，事務(wù)都將無法推進(jìn)下去蜀铲。

解決死鎖做簡單的方法就是超時，即當(dāng)兩個事務(wù)互相等待時属百，當(dāng)一個等待時間超過了某一閾值记劝，其中一個事務(wù)進(jìn)行回滾，另一個等待的事務(wù)就能繼續(xù)進(jìn)行诸老。

但是如果超時的事務(wù)所占權(quán)重比較大隆夯，如事務(wù)更新了很多行，占用了較多的undo log别伏，回滾這個事務(wù)的時間相對于另一個事務(wù)所占用的時間可能會更多蹄衷，就顯得不合適了。

因此厘肮，除了超時機制愧口，當(dāng)前數(shù)據(jù)庫都普遍采用等待圖（wait-for graph）的方式來進(jìn)行死鎖檢測。

wait-for graph要求數(shù)據(jù)庫保存以下兩種信息:

• 鎖的信息鏈表

• 事務(wù)等待鏈表

通過上述鏈表可以構(gòu)造出一張圖类茂，而在這個圖中若存在回路耍属，就代表存在死鎖，因此資源間相互發(fā)生等待巩检。在 wait-for graph中,事務(wù)為圖中的節(jié)點厚骗。而在圖中,事務(wù)T1指向T2邊的定義為:

• 事務(wù)T1等待事務(wù)T2所占用的資源

• 事務(wù)T1最終等待T2所占用的資源,也就是事務(wù)之間在等待相同的資源,而事務(wù)T1發(fā)生在事務(wù)T2的后面

來看一個例子：

image.png

在 Transaction Wait Lists中可以看到共有4個事務(wù)t1、t2兢哭、t3领舰、t4,故在wait-for graph中應(yīng)有4個節(jié)點。

image.png

通過上圖可以發(fā)現(xiàn)存在回路(t1,t2)，因此存在死鎖冲秽∩峥В可以發(fā)現(xiàn)wait-for graph是一種較為主動的死鎖檢測機制，在每個事務(wù)請求鎖并發(fā)生等待時都會判斷是否存在回路锉桑，若存在則有死鎖排霉，通常來說InnoDB存儲引擎選擇回滾undo量最小的事務(wù)。

6民轴、InnoDB鎖相關(guān)狀態(tài)查詢

用戶可以使用INFOMATION_SCHEMA庫下的INNODB_TRX攻柠、INNODB_LOCKS和INNODB_LOCK_WAITS表來監(jiān)控當(dāng)前事務(wù)并分析可能出現(xiàn)的鎖問題。的關(guān)鍵字段如下：

mysql> SELECT * FROM information_schema.INNODB_TRX\G;
************************************* 1.row *********************************************
trx_id: 7311F4
trx_state: LOCK WAIT
trx_started: 2010-01-04 10:49:33
trx_requested_lock_id: 7311F4:96:3:2
trx_wait_started: 2010-01-04 10:49:33
trx_weight: 2
trx_mysql_thread_id: 471719
trx_query: select * from parent lock in share mode

表只能顯示當(dāng)前運行的InnoDB事務(wù)杉武，并不能直接判斷鎖的一些情況辙诞。如果需要查看鎖，則還需要訪問表轻抱，該表的關(guān)鍵字段組成如下：

mysql> SELECT * FROM.INNODB_LOCKS\G;
1. row
lock_id: 16219:56:4:5
lock_trx_id: 16219
lock_mode: X,GAP
lock_type: RECORD
lock_table: `test`.`z`
lock_index: b
lock_space: 56
lock_page: 4
lock_rec: 5
lock_data: 6, 7
2. row
lock_id: 16218:56:4:5
: 16218
lock_mode: X,GAP
lock_type: RECORD
lock_table: `test`.`z`
lock_index: b
lock_space: 56
lock_page: 4
lock_rec: 5
lock_data: 6, 7
2 rows in set, 1 warning (0.00 sec)

通過表INNODB_LOCKS查看每張表上鎖的情況后飞涂，用戶就可以來判斷由此引發(fā)的等待情況。當(dāng)時當(dāng)事務(wù)量非常大祈搜，其中鎖和等待也時常發(fā)生较店，這個時候就不那么容易判斷。但是通過表INNODB_LOCK_WAITS容燕，可以很直觀的反應(yīng)當(dāng)前事務(wù)的等待梁呈。表INNODB_LOCK_WAITS由四個字段組成，如下表所示蘸秘。

mysql> SELECT * FROM.INNODB_LOCK_WAITS\G;
*******************************************1.row************************************
requesting_trx_id: 7311F4
requesting_lock_id: 7311F4:96:3:2
blocking_trx_id: 730FEE
blocking_lock_id: 730FEE:96:3:2

通過上述的SQL語句官卡，用戶可以清楚直觀地看到哪個事務(wù)阻塞了另一個事務(wù)，然后使用上述的事務(wù)ID和鎖ID醋虏，去INNODB_TRX和INNDOB_LOCKS表中查看更加詳細(xì)的信息寻咒。

7、鎖的應(yīng)用實例解析

有一點需要特別注意颈嚼，MySQL怎么使用鎖毛秘，與事務(wù)的隔離級別、列上的索引狀況等密切相關(guān)阻课。單拎出一條SQL語句來討論加鎖的方式以及范圍而不考慮使用場景叫挟，都是耍流氓的行為。

下面就不同場景下鎖的使用情況做一下對比限煞，深入理解上面的理論知識抹恳。

7.1 場景1：RR隔離級別+主鍵索引

首先根據(jù)如下代碼創(chuàng)建測試表t，然后開啟兩個事務(wù)進(jìn)行操作署驻。

create table t(a int primary key);
insert into t select 1;
insert into t select 2;
insert into t select 5;
insert into t select 7;
insert into t select 9;

開啟事務(wù)1奋献，保持未提交的狀態(tài)：

begin;
select * from t where a=5 for update;

開啟事務(wù)2并提交：

begin;
insert into t select 4;
commit;

表t共有1绊序、2、5秽荞、7、9五個值抚官，在上面的例子中扬跋，在事務(wù)1中首先對a=5進(jìn)行X鎖定，而由于a是主鍵且唯一凌节，因此鎖定的僅是5這個值钦听，而不是（2,5）這個范圍。這樣在事務(wù)2中插入值4而不會阻塞倍奢，可以立即插入并返回朴上。即鎖定由Next-key Lock算法降級為Record Lock，從而提高應(yīng)用的并發(fā)性卒煞。

需要注意的一點是痪宰，對于唯一鍵值的鎖定，Next-Key Lock降級為Record Lock僅存在于查詢所有的唯一索引列畔裕。若唯一索引由多個列組成衣撬，而查詢僅是查找多個唯一索引列中的其中一個，那么查詢其實是range類型查詢扮饶，而不是const類型查詢具练，故InnoDB存儲引擎依然使用Next-Key Lock進(jìn)行鎖定。

但如果對主鍵進(jìn)來范圍查詢時甜无，鎖的范圍是怎么樣的呢扛点？

begin;
select * from t where a<=5 for update;

針對下面的sql語句：

insert into test.t select 6;
insert into test.t select 8

第一條會產(chǎn)生鎖等待，而第二條可以插入成功岂丘。

表t共有1陵究、2、5元潘、7畔乙、9五個值，在上面的例子中翩概，在事務(wù)1中執(zhí)行a<=5范圍查詢時靴跛，事務(wù)1加鎖范圍是(負(fù)無窮, 1)、(1, 2)毁习、(2, 5)歼指、(5, 7)，且1评姨、2难述、5萤晴、7這四條記錄也都加鎖了，也就是說再插入<=7的記錄都是會產(chǎn)生鎖等待胁后，插入記錄8是成功的店读。

那么InnoDB如何判斷是否允許插入數(shù)據(jù)呢？對于普通索引攀芯，insert的加鎖策略是：查找小于等于 insert_rec 的第一條記錄屯断，然后查看第一條記錄的下一個記錄是否有Gap鎖，有則等待侣诺，沒有則插入殖演。比如說我們插入6這條記錄，首先定位<=6的記錄年鸳，也就是5趴久，然后確認(rèn)5的下一條記錄是否鎖住了Gap，這里也就是7搔确，當(dāng)7這條記錄有鎖是代表鎖住的是(5, 7)這個范圍彼棍，就不允許插入（會申請一把插入意向鎖），保證了可重復(fù)讀妥箕。證明了在RR隔離級別下使用了Next-key Lock來保證其“可重復(fù)讀”的特性滥酥。如果沒有鎖就直接插入即可。

但是如果插入的記錄有唯一約束時畦幢，只判斷下一條記錄是否鎖住了Gap就不行了坎吻，顯然會插入重復(fù)數(shù)據(jù)破壞唯一性。這時還會把插入的記錄與前一條數(shù)據(jù)進(jìn)行比較宇葱，如果相同則給插入記錄的前一條記錄加S Lock（lock in share mode）瘦真，加鎖成功則返回duplicate key，否則等待S Lock黍瞧。

這個地方可能有人會有一個疑問诸尽，為什么MySQL在加鎖時，不直接加5這條記錄本身以及<5的記錄呢印颤？為什么還要給加鎖呢您机？因為(5, 7)加不加鎖并不會影響RR級別可重復(fù)讀的特性。其實這就跟B+樹有關(guān)系了年局，首先MySQL定位到1這條記錄并加鎖际看，然后順著1往后讀取數(shù)據(jù)并加鎖，直到讀取到第一條不匹配數(shù)據(jù)才能確定是否停止繼續(xù)讀取數(shù)據(jù)矢否，而在RR隔離級別下只要被讀到的數(shù)據(jù)都需要進(jìn)行加鎖仲闽。如果查詢條件是<5，那么加鎖只會加到5這條記錄為止僵朗。

如果是在RC隔離級別下赖欣，只會對符合條件的記錄進(jìn)行加記錄鎖屑彻，不會對滿足條件的下一條記錄進(jìn)行加鎖。

7.2 場景2：RR隔離級別+普通索引

正如上面所介紹的顶吮，Next-Key Lock降級為Record Lock僅在查詢的列是唯一索引且條件為等值查詢的情況下社牲。若是輔助索引，則情況會完全不同悴了，

首先根據(jù)如下代碼創(chuàng)建測試表z：

create table z(id int primary key,b int,index(b));
insert into z values(1,1);
insert into z values(3,1);
insert into z values(5,3);
insert into z values(7,6);
insert into z values(10,8);

begin;
select * from z where b=3 for update;

這時SQL語句通過索引列b進(jìn)行查詢膳沽，因此其使用傳統(tǒng)的Next-key Locking技術(shù)加鎖，并且由于有兩個索引让禀，其需要分別進(jìn)行鎖定。對于聚集索引陨界，其僅對列id等于5的索引加上Record Lock巡揍。那么，為什么主鍵索引上的記錄也要加鎖呢菌瘪？因為有可能其他事務(wù)會根據(jù)主鍵對 z 表進(jìn)行更新腮敌，試想一下，如果主鍵索引沒有加鎖俏扩，那么顯然會存在并發(fā)問題糜工。

而對于輔助索引，其加上的是Next-key Lock录淡，鎖定的范圍是（1, 3）捌木，特別需要注意的是，InnoDB存儲引擎還會對輔助索引下一個鍵值加上Gap lock嫉戚，即還有一個輔助索引范圍為（3, 6）的鎖刨裆。

此時，若在新的事務(wù)2中運行下面的SQL語句：

select * from z where id=5 lock in share mode;
insert into z select 2,1;
insert into z select 4,2;
insert into z select 6,6;

第一個SQL語句彬檀，在事務(wù)1中執(zhí)行的SQL語句已經(jīng)對聚集索引中的列“a=5”的值加上X鎖帆啃，因此執(zhí)行會被阻塞。

第二個SQL語句窍帝，主鍵插入2努潘，沒有問題，插入的輔助索引值1也不在鎖定的范圍（1,3）中坤学，因此可以執(zhí)行成功疯坤。

第三個SQL語句，主鍵插入4拥峦，沒有問題贴膘，插入的輔助索引值2在鎖定的范圍（1,3）中，因此執(zhí)行會被阻塞略号。

第四個SQL語句刑峡，插入的主鍵6沒有被鎖定洋闽，6也不在范圍（1,3）之間。但插入的值6在另一個鎖定的范圍（3,6）中突梦，故同樣需要等待诫舅。

在RR隔離級別下，對于INSERT的操作宫患，其會檢查插入記錄的下一條記錄是否被鎖定刊懈，若已經(jīng)被鎖定，則不允許操作娃闲，從而避免不可重復(fù)讀問題虚汛。而下面的SQL語句，不會被阻塞皇帮，可以立即執(zhí)行：

insert into z select 8,6;
insert into z select 2,0;
insert into z select 6,7;

從上面的例子中可以看到卷哩，Gap Lock的作用是為了阻止多個事務(wù)將記錄插入到同一個范圍內(nèi)，解決“不可重復(fù)讀”問題的產(chǎn)生属拾。例如在上面的例子中将谊，事務(wù)1中用戶已經(jīng)鎖定了b=3的記錄，若此時沒有Gap Lock鎖定(3, 6)渐白，那么用戶可以插入索引b列為3的記錄尊浓，這會導(dǎo)致事務(wù)1中的用戶再次執(zhí)行同樣的查詢時會返回不同的記錄，即產(chǎn)生不可重復(fù)讀問題纯衍。

這里有一個問題值得思考一下栋齿，為什么插入(6, 6)不允許，而插入(8, 6)是允許的襟诸。這跟InnoDB索引結(jié)構(gòu)有關(guān)系褒颈，我們知道二級索引是指向主鍵，所以結(jié)構(gòu)如：(1, 1), (1, 3), (3, 5), (6, 7), (8, 10)励堡。真正的Gap鎖鎖住的也是((1, 1), (1, 3))這樣的結(jié)構(gòu)谷丸，所以當(dāng)我們插入(6, 6)時，需要插入到(3, 5), (6, 7)之間应结，這區(qū)間被鎖刨疼，所以無法插入；而我們插入(8, 6)是需要插入到(6, 7), (8, 10)之間鹅龄，沒有鎖存在揩慕，所以可以插入成功。

另外扮休，在RR隔離級別下迎卤，我們訪問條件為二級索引的情況下，就算訪問一條不存在的記錄同樣需要加Next-key Lokcs玷坠，比如我們查詢

select * from z where b=7 for update;

InnoDB會對(6, 8)這個區(qū)間加了Gap Lock蜗搔，也就是說插入這個區(qū)間的數(shù)據(jù)都會被阻塞劲藐。

雖然在RR隔離級別默認(rèn)使用Gap Lock，但用戶可以通過以下兩種方式來顯式地關(guān)閉Gap Lock：

• 將事務(wù)的隔離級別設(shè)置為READ COMMITTED樟凄；

• 將參數(shù)innodb_locks_unsafe_for_binlog設(shè)置為1聘芜；

當(dāng)設(shè)置了上述參數(shù)或隔離級別調(diào)整到READ COMMITTED時，除了外鍵約束和唯一性檢查（duplicate key）依然需要Gap Lock缝龄，其余情況僅使用Record Lock進(jìn)行鎖定汰现。但需要知道的是，上述設(shè)置破壞了事務(wù)的隔離性叔壤，并且對于MySQL復(fù)制來說瞎饲，可能會導(dǎo)致主從數(shù)據(jù)的不一致。雖然MySQL目前默認(rèn)隔離級別是RR炼绘，但是基本生產(chǎn)環(huán)境標(biāo)配基本都是RC隔離級別+ROW格式企软。

7.3 場景3：RC隔離級別+主鍵索引

將事務(wù)隔離級別切為RC，開啟事務(wù)1饭望，保持未提交的狀態(tài)：

begin;
select * from z where id>3 for update;

執(zhí)行下面的SQL：

insert into z select 6,6;

可以執(zhí)行成功。證明在事務(wù)1執(zhí)行主鍵范圍for update時形庭，事務(wù)2對這個范圍扔可以申請寫鎖铅辞。證明RC隔離級別沒有使用NEXT-KEY Lock，而是使用行級鎖鎖住對應(yīng)的記錄萨醒。

7.4 場景4：RC隔離級別+普通索引

將事務(wù)隔離級別切為RC斟珊，開啟事務(wù)1，保持未提交的狀態(tài)：

begin;
select * from z where b>3 for update;

此時富纸，若在新的事務(wù)2中運行下面的SQL語句：

insert into z select 8,4;
update z set b = 10 where b = 6;
update z set b = 10 where b = 1;

第一個SQL語句囤踩，插入數(shù)據(jù)到事務(wù)1的for update范圍內(nèi)是可以的，因為這里事務(wù)1在RC模式下沒有加NEXT-KEY LOCK鎖晓褪，所以可以插入數(shù)據(jù)堵漱。

第二個SQL語句，會出現(xiàn)鎖等待涣仿，我們選擇普通索引作為條件勤庐，此時MySQL給普通索引b>3的記錄都會加行鎖。同時好港，這些記錄對應(yīng)主鍵索引上的記錄也都加上了鎖

第三個SQL語句愉镰，插入成功，說明b<3的記錄都無鎖钧汹。

7.5 場景5：RR + 無索引

InnoDB行鎖是通過給索引上的索引項加鎖來實現(xiàn)的丈探，這一點MySQL與Oracle不同，后者是通過在數(shù)據(jù)塊中對相應(yīng)數(shù)據(jù)行加鎖來實現(xiàn)的拔莱。InnoDB這種行鎖實現(xiàn)特點意味著：只有通過索引條件檢索數(shù)據(jù)碗降，InnoDB才使用行級鎖隘竭，否則，InnoDB將會給所有記錄加鎖遗锣。

在RR隔離級別下货裹，由于查詢條件沒有索引，那么InnoDB需要掃描所有數(shù)據(jù)來查找數(shù)據(jù)精偿，對于掃描過的數(shù)據(jù)InnoDB都會加上鎖弧圆，并且是加Next-key lock。

建立測試表：

create table tab_no_index(id int,b int) engine=innodb;
insert into tab_no_index values(1,1),(2,2),(3,3),(4,4),(100,100);

開啟事務(wù)1笔咽，保持未提交的狀態(tài)：

begin;
select * from tab_no_index where id = 1 for update;

執(zhí)行下面的SQL：

insert into tab_no_index values(5,5);

這時候搔预，并不能插入成功∫蹲椋看起來事務(wù)1只給一行加了排他鎖拯田，但事務(wù)2在請求其他行的排他鎖時，卻出現(xiàn)了鎖等待甩十。原因就在于沒有索引的情況下船庇，InnoDB只能掃描所有記錄（鎖住所有記錄）。當(dāng)我們給其增加一個唯一索引后侣监，InnoDB就只鎖定了符合條件的行鸭轮。

當(dāng)我們給其增加一個唯一索引后，InnoDB就只鎖定了符合條件的行橄霉。

create table tab_with_index(id int,b int,primary key(id));
insert into tab_with_index values(1,1),(2,2),(3,3),(4,4),(100,100);

開啟事務(wù)1窃爷，保持未提交的狀態(tài)：

begin;
select * from tab_with_index where id = 1 for update;

執(zhí)行下面的SQL：

select * from tab_with_index where id = 2 for update;

由這個例子可以看出，對于id是主鍵索引的情況下姓蜂，只鎖了id=1這一行記錄按厘。其余的行都是可以進(jìn)行DML操作的，但前提條件是以id為條件钱慢。如果是以b字段為條件逮京，那么還是會鎖的。

7.6 場景6：RC + 無索引

上面演示了在RR隔離級別下束莫，對于where條件無索引的情況下造虏，InnoDB是對所有記錄加Next-key Locks。

但是在RC隔離級別下麦箍，對于where條件無索引的情況下漓藕，則不會對所有記錄加鎖，而是只對命中的數(shù)據(jù)的聚簇索引加X鎖挟裂。

同樣針對tab_no_index``表享钞，開啟事務(wù)1，保持未提交的狀態(tài)：

begin;
delete from tab_no_index where id = 1;

執(zhí)行下面的SQL：

delete from tab_no_index where id = 99;
update tab_no_index set id=2 where id=100;
insert into tab_no_index select 99,99;

都可以執(zhí)行成功。

7.7 沙場練兵

有了上面的分場景演習(xí)栗竖，下面就兩條簡單SQL進(jìn)行一場加鎖分析的實戰(zhàn)暑脆。

下面兩條簡單的SQL，加什么鎖狐肢？

select * from t1 where id = 10;
delete from t1 where id = 10;

一眼看去添吗，不經(jīng)大腦的回答是：

• SQL1：不加鎖。因為MySQL是使用多版本并發(fā)控制的份名，讀不加鎖

• SQL2：對id = 10的記錄加寫鎖 (走主鍵索引)

可能是正確的碟联，也有可能是錯誤的，已知條件不足僵腺，要回答這個問題鲤孵，還缺少幾個前提條件：

• id列是不是主鍵？

• 當(dāng)前系統(tǒng)的隔離級別是什么辰如？

• id列如果不是主鍵普监，那么id列上有索引嗎？

• id列上如果有二級索引琉兜，那么這個索引是唯一索引嗎凯正？

組合一：id列是主鍵，RC隔離級別

這個組合豌蟋，是最簡單廊散，最容易分析的組合。id是主鍵夺饲，Read Committed隔離級別，給定SQL：delete from t1 where id = 10; 只需要將主鍵上施符，id = 10的記錄加上X鎖即可往声。

image.png

組合二：id唯一索引+RC

id是unique索引，而主鍵是name列戳吝。由于id是unique索引浩销，因此delete語句會選擇id列的索引進(jìn)行where條件的過濾，在找到id=10的記錄后听哭，首先會將unique索引上的id=10索引記錄加上X鎖慢洋，同時，會根據(jù)讀取到的name列陆盘，回主鍵索引(聚簇索引)普筹，然后將聚簇索引上的name = ‘d’ 對應(yīng)的主鍵索引項加X鎖。

為什么聚簇索引上的記錄也要加鎖隘马？如果并發(fā)的一個SQL太防，是通過主鍵索引來更新：update t1 set id = 100 where name = ‘d’; 此時，如果delete語句沒有將主鍵索引上的記錄加鎖酸员，那么并發(fā)的update就會感知不到delete語句的存在蜒车，違背了同一記錄上的更新/刪除需要串行執(zhí)行的約束讳嘱。

若id列是unique列，其上有unique索引酿愧。那么SQL需要加兩個X鎖沥潭，一個對應(yīng)于id unique索引上的id = 10的記錄，另一把鎖對應(yīng)于聚簇索引上的[name=’d’,id=10]的記錄嬉挡。

image.png

組合三：id非唯一索引+RC

相對于組合一钝鸽、二，id列不再唯一棘伴，只有一個普通的索引寞埠。

滿足id = 10查詢條件的記錄，均已加鎖。同時鲁僚，這些記錄對應(yīng)的主鍵索引上的記錄也都加上了鎖。與組合二唯一的區(qū)別在于，組合二最多只有一個滿足等值查詢的記錄，而組合三會將所有滿足查詢條件的記錄都加鎖赁炎。

image.png

組合四：id無索引+RC

這個過濾條件，沒法通過索引進(jìn)行過濾，那么只能走全表掃描做過濾荧关。對應(yīng)于這個組合同波，SQL會加什么鎖？換句話說悲雳，全表掃描時歪玲，會加什么鎖？

由于id列上沒有索引导匣，因此只能走聚簇索引，進(jìn)行全部掃描签杈。聚簇索引上所有的記錄踢涌，都被加上了X鎖。無論記錄是否滿足條件睁壁，全部被加上X鎖背苦。既不是加表鎖，也不是在滿足條件的記錄上加行鎖潘明。

image.png

為什么不是只在滿足條件的記錄上加鎖呢行剂？這是由于MySQL的實現(xiàn)決定的。如果一個條件無法通過索引快速過濾钳降，那么存儲引擎層面就會將所有記錄加鎖后返回厚宰，然后由MySQL Server層進(jìn)行過濾。因此也就把所有的記錄都鎖上了遂填。

在實際的實現(xiàn)中铲觉，MySQL有一些改進(jìn)，在MySQL Server過濾條件吓坚，發(fā)現(xiàn)不滿足后撵幽，會調(diào)用unlock_row方法，把不滿足條件的記錄放鎖 (違背了2PL的約束)礁击。這樣做盐杂，保證了最后只會持有滿足條件記錄上的鎖逗载，但是每條記錄的加鎖操作還是不能省略的。

組合五：id主鍵+RR

上面的四個組合链烈，都是在Read Committed隔離級別下的加鎖行為厉斟，接下來的四個組合，是在Repeatable Read隔離級別下的加鎖行為强衡。

id列是主鍵列捏膨，Repeatable Read隔離級別，針對delete from t1 where id = 10; 這條SQL食侮，加鎖與組合一：[id主鍵号涯，Read Committed]一致。

組合六：id唯一索引+RR

與組合二：[id唯一索引锯七，Read Committed]一致链快。兩個X鎖，id唯一索引滿足條件的記錄上一個眉尸，對應(yīng)的聚簇索引上的記錄一個域蜗。

組合七：id非唯一索引+RR

RC隔離級別允許幻讀，而RR隔離級別噪猾，不允許存在幻讀霉祸。那么RR隔離級別下，如何防止幻讀呢袱蜡？

組合七丝蹭，Repeatable Read隔離級別，id上有一個非唯一索引坪蚁，執(zhí)行delete from t1 where id = 10; 假設(shè)選擇id列上的索引進(jìn)行條件過濾奔穿，最后的加鎖行為，是怎么樣的呢敏晤？

image.png

相對于組合三：[id列上非唯一鎖贱田，Read Committed]看似相同，其實卻有很大的區(qū)別嘴脾。

最大的區(qū)別在于男摧，多了一個GAP鎖，而且GAP鎖看起來也不是加在記錄上的译打，是加載兩條記錄之間的位置耗拓，GAP鎖有何用？

這個多出來的GAP鎖扶平，就是RR隔離級別帆离，相對于RC隔離級別蔬蕊，不會出現(xiàn)幻讀的關(guān)鍵结澄。

GAP鎖鎖住的位置哥谷，不是記錄本身，而是兩條記錄之間的GAP麻献。所謂幻讀们妥，就是同一個事務(wù)，連續(xù)做兩次當(dāng)前讀(例如：select * from t1 where id = 10 for update;)勉吻，那么這兩次當(dāng)前讀返回的是完全相同的記錄 (記錄數(shù)量一致监婶，記錄本身也一致)，第二次的當(dāng)前讀齿桃，不會比第一次返回更多的記錄 (幻象)惑惶。

如何保證兩次當(dāng)前讀返回一致的記錄，那就需要在第一次當(dāng)前讀與第二次當(dāng)前讀之間短纵，其他的事務(wù)不會插入新的滿足條件的記錄并提交带污。為了實現(xiàn)這個功能，GAP鎖應(yīng)運而生香到。

如圖中所示鱼冀，有哪些位置可以插入新的滿足條件的項 (id = 10)，考慮到B+樹索引的有序性悠就，滿足條件的項一定是連續(xù)存放的千绪。記錄[6,c]之前，不會插入id=10的記錄梗脾；[6,c]與[10,b]間荸型、[10,b]與[10,d]間、[10,d]與[11,f]間可以插入滿足條件的[10,e],[10,z]等炸茧；而[11,f]之后也不會插入滿足條件的記錄帆疟。

因此，為了保證[6,c]與[10,b]間宇立，[10,b]與[10,d]間踪宠，[10,d]與[11,f]不會插入新的滿足條件的記錄，MySQL選擇了用GAP鎖妈嘹，將這三個GAP給鎖起來柳琢。

Insert操作，如insert [10,a]润脸，首先會定位到[6,c]與[10,b]間柬脸，然后在插入前毙驯，會檢查這個GAP是否已經(jīng)被鎖上垦巴，如果被鎖上等限，則Insert不能插入記錄锰霜。因此，通過第一遍的當(dāng)前讀爬立，不僅將滿足條件的記錄鎖上 (X鎖)吟策，與組合三類似匾委。同時還是增加3把GAP鎖崩溪，將可能插入滿足條件記錄的3個GAP給鎖上砸民，保證后續(xù)的Insert不能插入新的id=10的記錄，也就杜絕了同一事務(wù)的第二次當(dāng)前讀奋救，出現(xiàn)幻象的情況岭参。

既然防止幻讀，需要靠GAP鎖的保護(hù)尝艘，為什么組合五演侯、組合六，也是RR隔離級別背亥，卻不需要加GAP鎖呢秒际？ GAP鎖的目的，是為了防止同一事務(wù)的兩次當(dāng)前讀狡汉，出現(xiàn)幻讀的情況娄徊。而組合五，id是主鍵盾戴；組合六寄锐，id是unique鍵，都能夠保證唯一性尖啡。一個等值查詢橄仆，最多只能返回一條記錄，而且新的相同取值的記錄衅斩，一定不會在新插入進(jìn)來盆顾，因此也就避免了GAP鎖的使用。

結(jié)論：Repeatable Read隔離級別下畏梆，id列上有一個非唯一索引椎扬，對應(yīng)SQL：delete from t1 where id = 10;首先，通過id索引定位到第一條滿足查詢條件的記錄具温，加記錄上的X鎖蚕涤，加GAP上的GAP鎖，然后加主鍵聚簇索引上的記錄X鎖铣猩，然后返回揖铜；然后讀取下一條，重復(fù)進(jìn)行达皿。直至進(jìn)行到第一條不滿足條件的記錄[11,f]天吓，此時贿肩，不需要加記錄X鎖，但是仍舊需要加GAP鎖龄寞，最后返回結(jié)束汰规。

組合八：id無索引+RR

id列上沒有索引。此時SQL：delete from t1 where id = 10;沒有其他的路徑可以選擇物邑，只能進(jìn)行全表掃描溜哮。最終的加鎖情況，如下圖所示：

image.png

這是一個很恐怖的現(xiàn)象色解。首先茂嗓，聚簇索引上的所有記錄，都被加上了X鎖科阎。其次述吸，聚簇索引每條記錄間的間隙(GAP)，也同時被加上了GAP鎖锣笨。這個示例表蝌矛，只有6條記錄，一共需要6個記錄鎖错英，7個GAP鎖朴读。試想，如果表上有1000萬條記錄呢走趋？

在這種情況下衅金，這個表上，除了不加鎖的快照度簿煌，其他任何加鎖的并發(fā)SQL氮唯，均不能執(zhí)行，不能更新姨伟，不能刪除惩琉，不能插入，全表被鎖死夺荒。

當(dāng)然瞒渠，跟組合四：[id無索引, Read Committed]類似，這個情況下技扼，MySQL也做了一些優(yōu)化伍玖，就是所謂的semi-consistent read。semi-consistent read開啟的情況下剿吻，對于不滿足查詢條件的記錄窍箍，MySQL會提前放鎖。

結(jié)論：在Repeatable Read隔離級別下，如果進(jìn)行全表掃描的當(dāng)前讀，那么會鎖上表中的所有記錄，同時會鎖上聚簇索引內(nèi)的所有GAP瘦赫，杜絕所有的并發(fā) 更新/刪除/插入 操作。當(dāng)然玩郊，也可以通過觸發(fā)semi-consistent read，來緩解加鎖開銷與并發(fā)影響，但是semi-consistent read本身可能會帶來其他問題。

組合九：Serializable

Serializable隔離級別巨朦。對于SQL2：delete from t1 where id = 10; 來說，Serializable隔離級別與Repeatable Read隔離級別完全一致

Serializable隔離級別鳞疲，影響的是SQL1：select * from t1 where id = 10;這條SQL罪郊，在RC蠕蚜，RR隔離級別下尚洽，都是快照讀，不加鎖靶累。但是在Serializable隔離級別腺毫，SQL1會加讀鎖，也就是說快照讀不復(fù)存在挣柬，MVCC并發(fā)控制降級為Lock-Based CC潮酒。

MVCC 最大的好處：讀不加鎖，讀寫不沖突邪蛔。在讀多寫少的OLTP應(yīng)用中急黎，讀寫不沖突是非常重要的，極大的增加了系統(tǒng)的并發(fā)性能侧到，這也是為什么現(xiàn)階段勃教，幾乎所有的RDBMS，都支持了MVCC

在MySQL/InnoDB中匠抗，所謂的讀不加鎖故源，并不適用于所有的情況，而是隔離級別相關(guān)的汞贸。Serializable隔離級別绳军，讀不加鎖就不再成立，所有的讀操作矢腻，都是當(dāng)前讀