1. 鎖類型
鎖是數(shù)據(jù)庫(kù)區(qū)別與文件系統(tǒng)的一個(gè)關(guān)鍵特性河闰，鎖機(jī)制用于管理對(duì)共享資源的并發(fā)訪問(wèn)。
InnoDB使用的鎖類型椎麦，分別有：
- 共享鎖（S）和排他鎖（X）
- 意向鎖（IS和IX）
- 自增長(zhǎng)鎖（AUTO-INC Locks）
1.1. 共享鎖和排他鎖
InnoDB實(shí)現(xiàn)了兩種標(biāo)準(zhǔn)的行級(jí)鎖：共享鎖（S）和排他鎖（X）
共享鎖：允許持有該鎖的事務(wù)讀取行記錄。如果事務(wù) T1 擁有記錄 r 的 S 鎖，事務(wù) T2 對(duì)記錄 r 加鎖請(qǐng)求：若想要加 S 鎖茅坛，能馬上獲得；若想要獲得 X 鎖则拷，則請(qǐng)求會(huì)阻塞灰蛙。
排他鎖：允許持有該鎖的事務(wù)更新或刪除行記錄。如果事務(wù) T1 擁有記錄 r 的 X 鎖隔躲，事務(wù) T2 對(duì)記錄 r 加鎖請(qǐng)求：無(wú)論想獲取 r 的 S 鎖或 X 鎖都會(huì)被阻塞摩梧。
S 鎖和 X 鎖都是行級(jí)鎖。
1.2. 意向鎖
InnoDB 支持多粒度的鎖宣旱，允許一行記錄同時(shí)持有兼容的行鎖和表鎖仅父。意向鎖是表級(jí)鎖，表明一個(gè)事務(wù)之后要獲取表中某些行的 S 鎖或 X 鎖。
InnoDB中使用了兩種意向鎖
- 意向共享鎖（IS）：事務(wù) T 想要對(duì)表 t 中的某些記錄加上 S 鎖
- 意向排他鎖（IX）：事務(wù) T 想要對(duì)表 t 中的某些記錄加上 X 鎖
例如：
-
SELECT ... LOCK IN SHARE MODE笙纤，設(shè)置了 IS 鎖 -
SELECT ... FOR UPDATE耗溜，設(shè)置了 IX 鎖
意向鎖協(xié)議如下所示：
- 在一個(gè)事務(wù)對(duì)表 t 中某一記錄 r 加 S 鎖之前，他必須先獲取表 t 的 IS 鎖
- 在一個(gè)事務(wù)對(duì)表 t 中某一記錄 r 加 X 鎖之前省容，他必須先獲取表 t 的 IX 鎖
這些規(guī)則可以總結(jié)為下面的圖表（橫向表示一個(gè)事務(wù)已經(jīng)獲取了對(duì)應(yīng)的鎖抖拴，縱向表示另外一個(gè)事務(wù)想要獲取對(duì)應(yīng)的鎖)：
IX，IS是表級(jí)鎖腥椒，不會(huì)和行級(jí)的X阿宅，S鎖發(fā)生沖突。只會(huì)和表級(jí)的X笼蛛，S發(fā)生沖突
| X | IX | S | IS | |
|---|---|---|---|---|
| X | 不兼容 | 不兼容 | 不兼容 | 不兼容 |
| IX | 不兼容 | 兼容 | 不兼容 | 兼容 |
| S | 不兼容 | 不兼容 | 兼容 | 兼容 |
| IS | 不兼容 | 兼容 | 兼容 | 兼容 |
當(dāng)請(qǐng)求的鎖與已持有的鎖兼容時(shí)洒放，則加鎖成功；如果沖突的話滨砍，事務(wù)將會(huì)等待已有的沖突的鎖釋放
IX 和 IS 鎖的主要目的是表明：某個(gè)請(qǐng)求正在或者將要鎖定一行記錄往湿。意向鎖的作用：意向鎖是在添加行鎖之前添加。當(dāng)再向一個(gè)表添加表級(jí) X 鎖的時(shí)候
- 如果沒(méi)有意向鎖的話惋戏，則需要遍歷所有整個(gè)表判斷是否有行鎖的存在领追，以免發(fā)生沖突
- 如果有了意向鎖，只需要判斷該意向鎖與即將添加的表級(jí)鎖是否兼容即可响逢。因?yàn)橐庀蜴i的存在代表了绒窑，有行級(jí)鎖的存在或者即將有行級(jí)鎖的存在。因而無(wú)需遍歷整個(gè)表龄句，即可獲取結(jié)果
意向鎖使用 SHOW ENGINE INNODB STATUS 查看當(dāng)前鎖請(qǐng)求的信息：
TABLE LOCK table `test`.`t` trx id 10080 lock mode IX
1.3. 自增長(zhǎng)鎖
InnoDB中回论，對(duì)每個(gè)含有自增長(zhǎng)值的表都有一個(gè)自增長(zhǎng)計(jì)數(shù)器（aito-increment counter）。當(dāng)對(duì)含有自增長(zhǎng)計(jì)數(shù)器的表進(jìn)行插入操作時(shí)分歇，這個(gè)計(jì)數(shù)器會(huì)被初始化傀蓉。執(zhí)行如下語(yǔ)句會(huì)獲得自增長(zhǎng)的值
SELECT MAX(auto_inc_col) FROM t FOR UPDATE;
插入操作會(huì)依據(jù)這個(gè)自增長(zhǎng)的計(jì)數(shù)器值加1賦予到自增長(zhǎng)列。這種實(shí)現(xiàn)方式是AUTO_INC Locking职抡。這種鎖采用了一種特殊的表鎖機(jī)制葬燎，為提高插入的性能，鎖不是在一個(gè)事務(wù)完成后釋放缚甩，而是在完成對(duì)自增長(zhǎng)值插入的SQL語(yǔ)句后立即釋放谱净。雖然AUTO-INC Locking一定方式提升了并發(fā)插入的效率，但還是存在性能上的一些問(wèn)題：
- 首先擅威，對(duì)自增長(zhǎng)值的列并發(fā)插入性能較差壕探，事務(wù)必須等待前一個(gè)插入SQL的完成
- 其次，對(duì)于 insert... select 的大數(shù)據(jù)量插入會(huì)影響插入的性能郊丛，因?yàn)榱硪粋€(gè)插入的事務(wù)會(huì)被阻塞
InnoDB提供了一種輕量級(jí)互斥量的自增長(zhǎng)實(shí)現(xiàn)機(jī)制李请，大大提高了自增長(zhǎng)值插入的性能瞧筛。提供參數(shù)innodb_autoinc_lock_mode來(lái)控制自增長(zhǎng)鎖使用的算法，默認(rèn)值為1导盅。他允許你在可預(yù)測(cè)的自增長(zhǎng)值和最大化并發(fā)插入操作之間進(jìn)行權(quán)衡较幌。
插入類型的分類：
| 插入類型 | 說(shuō)明 |
|---|---|
| insert-like | 指所有的插入語(yǔ)句，例如：insert白翻、replace乍炉、insert ... select、replace... select滤馍、load data |
| simple inserts | 指再插入前就確定插入行數(shù)的語(yǔ)句岛琼。例如：insert、replace等纪蜒。注意：simple inserts不包含 insert ... on duplicate key update 這類sql語(yǔ)句 |
| bulk inserts | 指在插入前不能確定得到插入行數(shù)的語(yǔ)句衷恭，例如：insert ... select此叠、 replace ... select纯续、load data |
| mixed-mode inserts | 指插入中有一部分的值是自增長(zhǎng)的，一部分是確定的灭袁。例如：insert into t1(c1, c2) values (1, 'a'), (NULL, 'b'), (5, 'c'), (NULL,'d'); 也可以指 insert ... on duplicate key update 這類sql語(yǔ)句 |
innodb_autoinc_lock_mode 在不同設(shè)置下對(duì)自增長(zhǎng)的影響：
innodb_autoinc_lock_mode = 0
MySQL 5.1.22版本之前自增長(zhǎng)的實(shí)現(xiàn)方式猬错，通過(guò)表鎖的AUTO-INC Locking方式
innodb_autoinc_lock_mode = 1（默認(rèn)值）
對(duì)于『simple inserts』，該值會(huì)用互斥量（mutex）對(duì)內(nèi)存中的計(jì)數(shù)器進(jìn)行累加操作茸歧。對(duì)于『bulk inserts』會(huì)用傳統(tǒng)的AUTO-INC Locking方式倦炒。這種配置下，如果不考慮回滾软瞎，自增長(zhǎng)列的增長(zhǎng)還是連續(xù)的逢唤。需要注意的是：如果已經(jīng)使用AUTO-INC Locking方式去產(chǎn)生自增長(zhǎng)的值，而此時(shí)需要『simple inserts』操作時(shí)涤浇，還需要等待AUTO-INC Locking的釋放
innodb_autoinc_lock_mode = 2
對(duì)于所有『insert-like』自增長(zhǎng)的產(chǎn)生都是通過(guò)互斥量鳖藕，而不是AUTO-INC Locking方式。這是性能最高的方式只锭。但會(huì)帶來(lái)一些問(wèn)題：
- 因?yàn)椴l(fā)插入的存在著恩，每次插入時(shí)，自增長(zhǎng)的值是不連續(xù)的
- 基于statement-base replication會(huì)出現(xiàn)問(wèn)題
因此蜻展，使用這種方式喉誊，任何情況下都需要使用row-base replication，這樣才能保證最大并發(fā)性能和replication的主從數(shù)據(jù)的一致 |
2. 鎖的算法
InnoDB存儲(chǔ)引擎行鎖的算法
- Record Locks：?jiǎn)蝹€(gè)行記錄上的鎖
- Gap Locks：間隙鎖纵顾，鎖定一個(gè)范圍伍茄，不包含記錄本身
- Next-Key Locking：Record Locks + Gap Locks，鎖住一個(gè)范圍 + 記錄本身
- Insert Intention Locks：插入易向鎖
2.1. 行鎖
行鎖是加在索引記錄上的鎖施逾，例如：SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE敷矫，會(huì)阻止其他事務(wù)插入贞盯、更新或刪除 t.c1 = 10 的記錄
行鎖總是在索引記錄上面加鎖，即使一張表沒(méi)有設(shè)置任何索引沪饺，InnoDB會(huì)創(chuàng)建一個(gè)隱藏的聚簇索引躏敢，然后在這個(gè)索引上加上行鎖。
行鎖使用 SHOW ENGINE INNODB STATUS 的輸出如下：
RECORD LOCKS space id 58 page no 3 n bits 72 index `PRIMARY` of table `test`.`t`
trx id 10078 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 8000000a; asc ;;
1: len 6; hex 00000000274f; asc 'O;;
2: len 7; hex b60000019d0110; asc ;;
2.2. 間隙鎖
間隙鎖是加在索引記錄間隙之間的鎖整葡，或者在第一條索引記錄之前件余、最后一條索引記錄之后的區(qū)間上加的鎖。例如：SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE; 這條語(yǔ)句阻止其他的事務(wù)插入一條 t.c1 = 15 的記錄遭居，因?yàn)樵?0-20的范圍值都已經(jīng)被加上了鎖啼器。
間隙鎖只在RR隔離級(jí)別中使用。如果一條sql使用了唯一索引（包括主鍵索引）俱萍，那么不會(huì)使用到間隙鎖
例如：id 列是唯一索引端壳，下面的語(yǔ)句只會(huì)在 id = 100 行上面使用Record Lock，而不會(huì)關(guān)心別的事務(wù)是否在上述的間隙中插入數(shù)據(jù)枪蘑。如果 id 列沒(méi)有索引或者不是唯一索引损谦，這個(gè)語(yǔ)句會(huì)在上述的間隙上加鎖。
SELECT * FROM child WHERE id = 100 FOR UPDATE;
2.3. Next-Key鎖
Next-Key Lock是結(jié)合了Gap Lock 和 Record Lock的一種鎖算法岳颇。
當(dāng)掃描表的索引時(shí)照捡，InnoDB以這種形式實(shí)現(xiàn)行級(jí)的鎖：遇到匹配的的索引記錄，在上面加上對(duì)應(yīng)的 S 鎖或 X 鎖话侧。因此栗精，行級(jí)鎖實(shí)際上是索引記錄鎖。如果一個(gè)事務(wù)擁有索引上記錄 r 的一個(gè) S 鎖或 X 鎖瞻鹏，另外的事務(wù)無(wú)法立即在 r 記錄索引順序之前的間隙上插入一條新的記錄悲立。
假設(shè)有一個(gè)索引包含值：10，11新博，13和20薪夕。下列的間隔上都可能加上一個(gè)Next-Key 鎖（左開(kāi)右閉）
(negative infinity, 10]
(10, 11]
(11, 13]
(13, 20]
(20, positive infinity)
在最后一個(gè)區(qū)間中，Next-Key鎖 鎖定了索引中的最大值到 正無(wú)窮叭披。
默認(rèn)情況下寥殖，InnoDB啟用 RR 事務(wù)隔離級(jí)別。此時(shí)涩蜘，InnoDB在查找和掃描索引時(shí)會(huì)使用 Next-Key 鎖嚼贡，其設(shè)計(jì)的目的是為了解決『幻讀』的出現(xiàn)。
當(dāng)查詢的列是唯一索引情況下同诫，InnoDB會(huì)對(duì)Next-Key Lock進(jìn)行優(yōu)化粤策，降級(jí)為Record Lock，即只鎖住索引本身误窖，而不是范圍叮盘。
next-key 鎖 使用 SHOW ENGINE INNODB STATUS 輸出如下：
RECORD LOCKS space id 58 page no 3 n bits 72 index `PRIMARY` of table `test`.`t`
trx id 10080 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 8000000a; asc ;;
1: len 6; hex 00000000274f; asc 'O;;
2: len 7; hex b60000019d0110; asc ;;
2.4. 插入意向鎖
插入意向鎖是一種在數(shù)據(jù)行插入前設(shè)置的gap鎖秩贰。這種鎖用于在多事務(wù)插入同一索引間隙時(shí)，如果這些事務(wù)不是往這段gap的同一位置插入數(shù)據(jù)柔吼，那么就不用互相等待毒费。假如有4和7兩個(gè)索引記錄值。不同的事務(wù)嘗試插入5和6的值愈魏。在不同事務(wù)獲取分別的 X 鎖之前觅玻，他們都獲得了4到7范圍的插入意向鎖，但是他們無(wú)需互相等待培漏，因?yàn)?和6這兩行不沖突溪厘。
例如：客戶端A和B，在插入記錄獲取互斥鎖之前牌柄，事務(wù)正在獲取插入意向鎖畸悬。
客戶端A創(chuàng)建了一個(gè)表，包含90和102兩條索引記錄珊佣，然后去設(shè)置一個(gè)互斥鎖在大于100的所有索引記錄上蹋宦。這個(gè)互斥鎖包含了在102記錄前的gap鎖。
mysql> CREATE TABLE child (id int(11) NOT NULL, PRIMARY KEY(id)) ENGINE=InnoDB;
mysql> INSERT INTO child (id) values (90),(102);
mysql> START TRANSACTION;
mysql> SELECT * FROM child WHERE id > 100 FOR UPDATE;
+-----+
| id |
+-----+
| 102 |
+-----+
客戶端B 開(kāi)啟一個(gè)事務(wù)在這段gap上插入新紀(jì)錄彩扔，這個(gè)事務(wù)在等待獲取互斥鎖之前妆档，獲取了一把插入意向鎖僻爽。
mysql> START TRANSACTION;
mysql> INSERT INTO child (id) VALUES (101);
插入意向鎖 使用 SHOW ENGINE INNODB STATUS 輸出如下：
RECORD LOCKS space id 31 page no 3 n bits 72 index `PRIMARY` of table `test`.`child`
trx id 8731 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 80000066; asc f;;
1: len 6; hex 000000002215; asc " ;;
2: len 7; hex 9000000172011c; asc r ;;...
3. SQL加鎖分析
給定兩個(gè)SQL來(lái)分析InnoDB下加鎖的過(guò)程：
SQL1：select * from t1 where id = 10;
SQL2：delete * from t1 where id = 10;
事務(wù)隔離級(jí)別為默認(rèn)隔離級(jí)別Repeatable Read虫碉。而對(duì)于id不同的索引類型，會(huì)有不同的結(jié)論胸梆。（總結(jié)自何登成大神的 MySQL 加鎖處理分析）
SQL1：在RC和RR下敦捧，因?yàn)镸VCC并發(fā)控制，select操作不需要加鎖碰镜，采用快照讀兢卵。讀取記錄的可見(jiàn)版本（可能是歷史版本）
針對(duì)SQL2：如下分不同情況
3.1. id主鍵
將主鍵上，id=10的記錄加上 X 鎖
3.2. id唯一索引
id不是主鍵绪颖，而是一個(gè)唯一的二級(jí)索引秽荤，主鍵是name列。加鎖步驟如下：
- 會(huì)選擇走id列的索引進(jìn)行where條件的過(guò)濾柠横。找到id=10的記錄后窃款，首先將唯一索引上id=10的索引記錄加上 X 鎖
- 同時(shí)，根據(jù)讀取到的name列回主鍵索引（聚簇索引）牍氛，然后將聚簇索引上的 name='d' 對(duì)應(yīng)的主鍵索引記錄添加 X 鎖
聚簇索引加鎖的原因：如果并發(fā)的一個(gè)SQL是通過(guò)主鍵索引來(lái)更新：update t1 set id = 100 where name = 'd'; 此時(shí)晨继，如果delete語(yǔ)句沒(méi)有將主鍵索引上的記錄加鎖，那么并發(fā)的update就會(huì)感知不到delete語(yǔ)句的存在搬俊。違背同一條記錄的更新/刪除需要串行執(zhí)行的約束紊扬。
3.3. id非唯一索引
加鎖步驟如下：
- 通過(guò)id索引定位到第一條滿足條件的記錄蜒茄，加上 X 鎖
- 這條記錄的間隙上加上 GAP鎖
- 根據(jù)讀取到的name列回主鍵聚簇索引，對(duì)應(yīng)記錄加上 X 鎖
- 返回讀取下一條餐屎，重復(fù)進(jìn)行... 直到第一條不滿足 where id = 10 條件的記錄 [11, f]檀葛，此時(shí)不需要加 X 鎖，仍舊需要加 GAP 鎖腹缩。結(jié)束返回
幻讀解決：
這幅圖中多了個(gè)GAP鎖驻谆，并不是加到記錄上的，而是加在兩個(gè)記錄之間的位置庆聘。GAP 鎖就是 RR 隔離級(jí)別相對(duì)于 RC 隔離級(jí)別胜臊，不會(huì)出現(xiàn)幻讀的關(guān)鍵。GAP鎖保證兩次當(dāng)前讀之前伙判，其他的事務(wù)不會(huì)插入新的滿足條件的記錄并提交象对。
所謂幻讀，就是同一個(gè)事務(wù)宴抚，連續(xù)做兩次當(dāng)前讀 (例如：select * from t1 where id = 10 for update;)勒魔，那么這兩次當(dāng)前讀返回的是完全相同的記錄 (記錄數(shù)量一致，記錄本身也一致)菇曲，第二次的當(dāng)前讀冠绢，不會(huì)比第一次返回更多的記錄 (幻象)。
如圖中所示：考慮到B+樹(shù)索引的有序性常潮，有哪些位置可以插入新的滿足條件的項(xiàng) (id = 10)：
- [6,c] 之前弟胀，不會(huì)插入id=10的記錄
- [6,c] 與 [10,b] 間，可以插入 [10, aa]
- [10,b] 與 [10,d] 間喊式，可以插入[10,bb]孵户，[10,c]
- [10,d] 與 [11, f] 間，可以插入[10,e]岔留，[10,z]
- [11,f] 之后夏哭，不會(huì)插入id=10的記錄
因此，不僅將滿足條件的記錄鎖上 (X鎖)献联，同時(shí)還通過(guò)GAP鎖竖配，將可能插入滿足條件記錄的3個(gè)GAP給鎖上，保證后續(xù)的Insert不能插入新的id=10的記錄里逆，也就杜絕了同一事務(wù)的第二次當(dāng)前讀进胯，出現(xiàn)幻象的情況。
當(dāng)id是唯一索引時(shí)运悲，則不需要加GAP鎖龄减。因?yàn)槲ㄒ凰饕軌虮ＷC唯一性，對(duì)于where id = 10 的查詢班眯，最多只能返回一條記錄希停，而且新的 id= 10 的記錄烁巫，一定不會(huì)插入進(jìn)來(lái)。
3.4. id無(wú)索引
當(dāng)id無(wú)索引時(shí)宠能，只能進(jìn)行全表掃描亚隙，加鎖步驟：
- 聚簇索引上的所有記錄都加 X 鎖
- 聚簇索引每條記錄間的GAP都加上了GAP鎖。
如果表中有上千萬(wàn)條記錄违崇，這種情況是很恐怖的阿弃。這個(gè)情況下，MySQL也做了一些優(yōu)化羞延，就是所謂的semi-consistent read渣淳。semi-consistent read開(kāi)啟的情況下，對(duì)于不滿足查詢條件的記錄伴箩，MySQL會(huì)提前放鎖入愧。針對(duì)上面的這個(gè)用例，就是除了記錄[d,10]嗤谚，[g,10]之外棺蛛，所有的記錄鎖都會(huì)被釋放，同時(shí)不加GAP鎖
4. 死鎖分析與案例
死鎖避免的一些辦法：
1巩步、如果不同程序會(huì)并發(fā)存取多個(gè)表旁赊，盡量約定以相同的順序訪問(wèn)表，可以大大降低死鎖機(jī)會(huì)椅野。
2终畅、在同一個(gè)事務(wù)中，盡可能做到一次鎖定所需要的所有資源鳄橘，減少死鎖產(chǎn)生概率声离；
