1. mysql鎖知多少
我們進(jìn)行insert顽素，update，delete烛占，select會(huì)加鎖嗎胎挎，如果加鎖沟启，加鎖步驟是什么，加的什么類型的鎖犹菇？
行鎖里面LOCK_REC_NOT_GAP德迹，LOCK_GAP，LOCK_ORDINARY(Next-Key Lock)揭芍，LOCK_INSERT_INTENTION這都是些什么鎖胳搞。
鎖是在數(shù)據(jù)庫(kù)中是怎么構(gòu)成的，鎖之間兼容與互斥關(guān)系
表上有無(wú)唯一索引称杨，增刪改動(dòng)作加索引步驟有何區(qū)別
死鎖是怎么形成的肌毅，如何分析
2. 前方高能
這是一篇冗余啰嗦的文章，如果要完整看完姑原，保持耐心悬而，或者自動(dòng)忽略標(biāo)記為非重點(diǎn)的內(nèi)容
3. 鎖類型
在InnoDB內(nèi)部用uint32類型數(shù)據(jù)表示鎖的類型, 最低的 4 個(gè) bit 表示 lock_mode, 5-8 bit 表示 lock_type(目前只用了 5 和 6 位，大小為 16 和 32 锭汛，表示 LOCK_TABLE 和 LOCK_REC), 剩下的高位 bit 表示行鎖的類型record_lock_type
| record_lock_type | lock_type | lock_mode |
|---|
我們說(shuō)鎖的時(shí)候笨奠，一般都是講什么lock_mode的record_lock_type鎖。因?yàn)槲覀兒苌俜治霰礞i唤殴，一般分析行鎖般婆。比如LOCK_S的LOCK_REC_NOT_GAP鎖，表示共享的記錄鎖（非間隙鎖）
3.1 lock_mode
3.1.1 lock_is/lock_ix（非重點(diǎn)）
LOCK_IS: 表級(jí)鎖眨八，意向共享鎖腺兴。表示將要在表上加共享鎖。
LOCK_IX：表級(jí)鎖廉侧，意向排他鎖页响。表示是將要在表上加排他鎖。
當(dāng)對(duì)記錄加LOCK_S或LOCK_X鎖的時(shí)候段誊，要確保在表上加了LOCK_IS或LOCK_IX鎖闰蚕。
3.1.2 lock_s
表共享鎖、也是行共享鎖
3.1.2.1 表共享鎖（非重點(diǎn)）
ALTER語(yǔ)句第一階段连舍，當(dāng)ALTER語(yǔ)句不能ONLINE執(zhí)行的時(shí)間添加
3.1.2.2 行共享鎖
- 事務(wù)讀在隔離級(jí)別為SERIALIZABLE時(shí)會(huì)給記錄加 LOCK_S 鎖
- SELECT … IN SHARE MODE
- 普通insert語(yǔ)句遇到duplicate key（普通INSERT語(yǔ)句如果沒(méi)有duplicate key是不用加行鎖的没陡，當(dāng)遇到duplicate key就需要加LOCK_S鎖。 5.6版本加的是LOCK_S類型的LOCK_REC_NOT_GAP鎖索赏，導(dǎo)致了一個(gè)bug盼玄，5.7版本改為了LOCK_S類型的LOCK_ORDINARY鎖，更嚴(yán)格了潜腻。具體可以參考如下鏈接：https://m.aliyun.com/yunqi/articles/50886 埃儿。這里水很深，小心淹死融涣，后續(xù)會(huì)舉例說(shuō)明（總共4種 1.三事務(wù)并行插入相同記錄童番，事務(wù)1回滾（有無(wú)ON DUPLICATE KEY UPDATE精钮，兩種不同隔離級(jí)別）2. 2個(gè)事務(wù),事務(wù)1插入記錄，事務(wù)2插入不同記錄剃斧，事務(wù)1插入記錄（或者事務(wù)1轨香，2都只插入1條，速度夠快） 3. 事務(wù)1幼东，2臂容，3都做刪除，同一條記錄 4. 事務(wù)1 刪除1條記錄筋粗，事務(wù)2策橘，3插入同一條記錄，為什么不死鎖(on duplicate update 不會(huì)死鎖（RR隔離級(jí)別）娜亿，如果沒(méi)有on duplicate update 就會(huì)死鎖丽已。 有on duplicate update RC隔離級(jí)別還未知) ））
3.1.3 lock_x
表排他鎖、也是行排他鎖
3.1.3.1 表排他鎖（非重點(diǎn)）
略
3.1.3.2 行排他鎖
UPDATE/DELETE需要阻止并發(fā)對(duì)同一行數(shù)據(jù)進(jìn)行修改語(yǔ)句的執(zhí)行
3.2 record_lock_type
3.2.1 LOCK_ORDINARY (next-key lock)
鎖住記錄本身和記錄之前的 gap买决。當(dāng)用RR隔離級(jí)別的時(shí)候沛婴，為了防止當(dāng)前讀語(yǔ)句的幻讀使用。比如update t set a=3 where b>=4, b列有索引督赤，會(huì)鎖住b=4的行嘁灯，并且b>4的間隙。(查看鎖信息的時(shí)候躲舌，表現(xiàn)為lock_mode X丑婿，比較奇怪。應(yīng)該叫next-key lock 或者lock_ordinary没卸。我也是一臉懵逼)
3.2.2 LOCK_GAP（間隙鎖）
只鎖住索引記錄之間或者第一條索引記錄前或者最后一條索引記錄之后的范圍羹奉，并不鎖住記錄本身(查看鎖信息的時(shí)候，表現(xiàn)為lock_mode X locks gap before rec. lock_mode還有可能是S)
你可以通過(guò)切換到RC隔離級(jí)別约计，或者開(kāi)啟選項(xiàng)innodb_locks_unsafe_for_binlog來(lái)避免GAP鎖诀拭。這時(shí)候只有在檢查外鍵約束或者duplicate key檢查時(shí)才會(huì)使用到GAP LOCK。
例如在RR隔離級(jí)別下煤蚌，非唯一索引條件上的等值當(dāng)前讀耕挨，會(huì)在等值記錄上加NEXT-KEY LOCK同時(shí)鎖住行和前面范圍的記錄，同時(shí)會(huì)在后面一個(gè)值上加LOCK_GAP鎖住下一個(gè)值前面的范圍尉桩。下面的例子就會(huì)在索引i_c2上給c2 = 5上NEXT-KEY LOCK(LOCK_ORDINARY|LOCK_X)筒占，同時(shí)給c2 = 10加上LOCK_GAP|LOCK_X鎖。這里是因?yàn)榉俏ㄒ凰饕├纾瑢?duì)同一個(gè)值可以多次插入翰苫，為確保當(dāng)前讀的可重復(fù)讀，需要鎖住前后的范圍沽瘦，確保不會(huì)有相同等值插入革骨。
create table t1(c1 int primary key, c2 int, c3 int, index i_c2(c2));
insert into t1 values(1, 2, 3), (2, 5, 7), (3, 10, 9);
set tx_isolation='repeatable-read';
select * from t1 where c2 = 5 for update;
3.2.3 LOCK_REC_NOT_GAP（記錄鎖）
僅鎖住記錄行，不鎖范圍析恋。(查看鎖信息的時(shí)候良哲，表現(xiàn)為lock_mode X locks rec but not gap。 lock_mode還有可能是S)
RC隔離級(jí)別下的當(dāng)前讀大多是該方式助隧。同時(shí)在上述例子中筑凫，RR隔離級(jí)別下，非唯一索引上的等值當(dāng)前讀并村，也會(huì)給主鍵上對(duì)應(yīng)行加LOCK_X|LOCK_REC_NOT_GAP鎖
3.2.4 LOCK_INSERT_INTENTION（插入意向鎖）
插入意向鎖巍实，當(dāng)插入索引記錄的時(shí)候用來(lái)判斷是否有其他事務(wù)的范圍鎖沖突，如果有就需要等待哩牍。插入意向鎖之間并不沖突棚潦，在一個(gè)GAP鎖上可以有多個(gè)意向鎖等待。主要表明我要在某某間隙插入記錄膝昆，如果不想幻讀丸边，別來(lái)這個(gè)區(qū)間讀〖苑酰或者妹窖，某某事務(wù)已經(jīng)在這個(gè)區(qū)間加了鎖，我就等待收叶，避免造成幻讀骄呼。也就是GAP鎖會(huì)阻塞插入意向鎖。
4. 事務(wù)隔離級(jí)別和行鎖
快照讀和當(dāng)前讀判没，快照讀使用MVCC讀取數(shù)據(jù)記錄某一個(gè)版本數(shù)據(jù)蜓萄，不需要加鎖。當(dāng)前讀讀取最新數(shù)據(jù)哆致，需要對(duì)記錄或者某一個(gè)查詢范圍加鎖绕德。（某個(gè)版本的數(shù)據(jù)不會(huì)變，當(dāng)然不用加鎖（如果不是Serializable級(jí)別摊阀，所有普通select都是mvcc讀耻蛇，無(wú)需加鎖）。如果是當(dāng)前讀胞此，存在多個(gè)事務(wù)同時(shí)操作臣咖，需要鎖來(lái)保證）
InnoDB支持的隔離級(jí)別有：
Read Uncommited
可以讀未提交記錄
Read Committed (RC)
讀取已提交數(shù)據(jù)。會(huì)存在幻讀漱牵。
Repeatable Read (RR)
可重復(fù)讀夺蛇。當(dāng)前讀的時(shí)候，部分語(yǔ)句會(huì)加范圍鎖酣胀，保證當(dāng)前讀的可重復(fù)刁赦。
Serializable
可串行化娶聘。不存在快照讀，所有讀操作都會(huì)加鎖甚脉。
5. 加鎖分析
mysql加鎖2個(gè)目的：
- 當(dāng)前讀丸升，避免多個(gè)事務(wù)同時(shí)操作某一行數(shù)據(jù)
- 隔離級(jí)別要求，比如RR隔離級(jí)別牺氨，要求可以重復(fù)讀狡耻，mysql還要求實(shí)現(xiàn)避免幻讀（如果主從同步模式是row-Statement），會(huì)加間隙鎖
5.1 準(zhǔn)備環(huán)境
- 打開(kāi)鎖監(jiān)控
set GLOBAL innodb_status_output=ON;
set GLOBAL innodb_status_output_locks=ON;
具體可以參考這篇文章：
http://www.ywnds.com/?p=9767 - 查看和修改隔離級(jí)別（需要的時(shí)候改動(dòng)）
查看 select @@tx_isolation;
修改當(dāng)前會(huì)話：set tx_isolation='repeatable-read'; - 查看和修改鎖等待時(shí)間（打開(kāi)會(huì)話后修改猴凹，便于觀察）
查看 select @@innodb_lock_wait_timeout
設(shè)置 set innodb_lock_wait_timeout=1000; - 人為停止表的purge操作
flush tables tbname for export (會(huì)加表鎖夷狰，沒(méi)啥用，本來(lái)想讓停止purge然后看看插入和刪除操作加鎖是否有區(qū)別)
可以google一把什么是purge操作（刪除的記錄并不會(huì)立刻消失郊霎，會(huì)有另外一個(gè)任務(wù)來(lái)進(jìn)行清理） - 準(zhǔn)備數(shù)據(jù)
create table t(c1 int primary key, c2 int, c3 int, c4 int, unique index i_c2(c2), index i_c3(c3));
insert into t values (10, 11, 12, 13), (20, 21, 22, 23), (30, 31, 32, 33), (40, 41, 42, 43);
5.2 加鎖分析
無(wú)論select, update 還是delete都是要找到數(shù)據(jù)之后沼头，才能夠加鎖。而mysql加鎖是加在索引上书劝，那么查詢條件就是我們分析加鎖的關(guān)鍵了瘫证，查詢條件的不同會(huì)導(dǎo)致使用不同的索引。我們從查詢條件的維度來(lái)開(kāi)始分析吧庄撮。（如果不懂mysql索引結(jié)構(gòu)和加鎖邏輯背捌，可以先查看如下文章補(bǔ)習(xí)下：《非常好的加鎖邏輯分析》：http://hedengcheng.com/?p=771
《mysql索引介紹》：https://blog.csdn.net/u010558660/article/details/53414456）
5.2.1 查詢條件為主鍵等值
- select * from t where c1 = 20 for update;
只需要在c1 = 20的主鍵記錄上加X(jué)鎖即可，加鎖為L(zhǎng)OCK_X|LOCK_REC_NOT_GAP（RC（read-committed）還是RR（repeatable-read）都一樣） - update t set c4 = 12 where c1 = 20;（未更新索引列）
只需要在c1 = 20的主鍵記錄上加X(jué)鎖即可洞斯，加鎖為L(zhǎng)OCK_X|LOCK_REC_NOT_GAP（RC毡庆，RR一樣。以下如果未說(shuō)明不一樣烙如，就是一樣） - update t set c2 = 12 where c1 = 20;（更新了索引列）
除了主鍵記錄加X(jué)鎖么抗，還需要在c2的索引上加LOCK_X|LOCK_REC_NOT_GAP。這里細(xì)想以下亚铁，可能引起死鎖蝇刀。因?yàn)橄全@得主鍵X鎖，然后才獲得C2索引的X鎖徘溢，如果另外一個(gè)事務(wù)（針對(duì)同一條記錄）吞琐，先獲得C2索引的X鎖，后獲得主鍵X鎖然爆，就會(huì)引起死鎖（后續(xù)會(huì)說(shuō)明） - delete from t where c1 = 20;
對(duì)主鍵站粟、各個(gè)索引對(duì)應(yīng)的記錄都要加X(jué)鎖，LOCK_X|LOCK_REC_NOT_GAP曾雕。主鍵奴烙，C2，C3索引列都加鎖
5.2.2 查詢條件為主鍵范圍
-
select * from t where c1 >= 20 for update;
1.1 RC隔離級(jí)別：會(huì)分別對(duì)c1 in (20, 30, 40)加鎖LOCK_X|LOCK_REC_NOT_GAP
1.2 RR隔離級(jí)別：這里會(huì)對(duì)c1=20加X(jué)鎖(LOCK_X|LOCK_REC_NOT_GAP)，對(duì)c1=30, c1=40對(duì)應(yīng)的行加exclusive next-key lock(LOCK_X|LOCK_ORDINARY)切诀，同時(shí)會(huì)對(duì)表示記錄上界的’supremum’加exclusive next-key lock揩环。這樣做到阻塞其他事務(wù)對(duì)c1>=20的加鎖操作
image.png
如上圖所示：20行record_lock鎖，30行next-key lock(鎖住的是30和gap3間隙)幅虑，40行和30行雷同（鎖住40行和gap4間隙）检盼。40行前面有一個(gè)gap5，這個(gè)是記錄上界supremum（可以看做是無(wú)窮大）的next-key lock鎖住的翘单。
- update t set c2 = c2 + 1 where c1 >= 20;
2.1 RC隔離級(jí)別：會(huì)分別對(duì)c1 in (20, 30, 40)依次對(duì)主鍵行加X(jué)鎖，對(duì)應(yīng)的索引行做加X(jué)鎖操作
2.2 RR隔離級(jí)別：對(duì)主鍵c1=20加X(jué)鎖蹦渣，i_c2索引行加X(jué)鎖哄芜，然后對(duì)c1=30,c1=40的主鍵行加exclusive next-key lock(LOCK_X|LOCK_ORDINARY)，同時(shí)對(duì)應(yīng)的i_c2索引行加X(jué)鎖柬唯，最后對(duì)表示記錄上界的’supremum’加exclusive next-key lock认臊。（與RC區(qū)別是，主鍵上面的GAP會(huì)被鎖壮荨）
5.2.3 查詢條件為唯一索引等值
- update t set c4 = 12 where c2 = 21;(未更新索引列)
對(duì)唯一索引上數(shù)據(jù)加X(jué)鎖（LOCK_X|LOCK_REC_NOT_GAP）失晴，然后對(duì)應(yīng)的主鍵行也需要加X(jué)鎖 - update t set c3 = 12 where c2 = 21; (更新了索引列)
依次對(duì)唯一索引數(shù)據(jù)、主鍵行拘央、索引數(shù)據(jù)加X(jué)鎖涂屁。 - delete from t where c2 = 21;
會(huì)對(duì)唯一索引數(shù)據(jù)加X(jué)鎖，根據(jù)唯一索引找到主鍵行后灰伟，會(huì)再依次對(duì)主鍵行拆又、唯一索引、索引數(shù)據(jù)加X(jué)鎖
以上3小點(diǎn)栏账，RC和RR隔離級(jí)別加鎖行為一樣帖族。
5.2.4 查詢條件為唯一索引范圍
-
select * from t force index(i_c2) where c2 >= 21 for update;
1.1 RC隔離級(jí)別：對(duì)滿足條件的唯一索引、主鍵記錄加X(jué)鎖
1.2 RR隔離級(jí)別：那么會(huì)對(duì)c2 in (21, 31, 41)分別加exclusive next-key lock挡爵，對(duì)應(yīng)主鍵行加X(jué)鎖竖般，同時(shí)對(duì)i_c2上’supremum’ record加exclusive next-key lock。如下圖所示：
image.png
c2索引上21茶鹃，31涣雕，41行都是next-key lock（為什么21行要next-lock鎖，理論上record-lock鎖就應(yīng)該可以了闭翩，這里是因?yàn)?1行可能已經(jīng)刪除還沒(méi)有purge胞谭，需要鎖住間隙，我猜的）男杈。 gap5也被鎖住丈屹，是無(wú)窮大的next-key lock。
查
主鍵索引上，20旺垒，30彩库，40record lock。 - update t force index (i_c2) set c4 = 1 where c2 >= 21;
與1加鎖模式一樣 - update t force index (i_c2) set c3 = 1 where c2 >= 21;（更新索引列）
與2模式一樣先蒋，增加c3索引上面22骇钦，32，42記錄鎖 - delete from t where c2 >= 41;
RC隔離級(jí)別：c2索引滿足條件的41記錄record鎖竞漾，對(duì)應(yīng)的主鍵索引40行record鎖眯搭，對(duì)應(yīng)的c3索引42記錄record鎖
RR隔離級(jí)別：上述語(yǔ)句選擇了i_c2索引，會(huì)對(duì)c2 = 41加exclusive next-key lock业岁，對(duì)應(yīng)主鍵行加X(jué)鎖鳞仙，i_c2，i_c3上數(shù)據(jù)行進(jìn)行加X(jué)鎖操作笔时，對(duì)i_c2上’supremum’ record加exclusive next-key lock棍好。
5.2.5 查詢條件為非唯一索引
RC隔離級(jí)別： 與唯一索引相同
RR隔離級(jí)別：
- 查詢條件為非唯一索引等值
1.1 select * from t where c3 = 22 for update;
會(huì)對(duì)c3 =22在i_c3索引上加exclusive next-key lock(LOCK_X|LOCK_ORDINARY)，對(duì)應(yīng)主鍵加X(jué)鎖(LOCK_X|LOCK_REC_NOT_GAP)允耿，然后在下一條記錄上加exclusive gap lock(LOCK_X|LOCK_GAP)借笙。即該語(yǔ)句會(huì)鎖定范圍(11, 31)
1.2 update t set c4 = 2 where c3 = 22;
加鎖與上述FOR UPDATE一致
1.3 update t set c2 = 2 where c3 = 22;（更新索引列）
除了上述鎖，對(duì)c1 = 20對(duì)應(yīng)的唯一索引(i_c2)行加X(jué)鎖 - 查詢條件為非唯一索引范圍
這里加鎖與唯一索引的當(dāng)前讀范圍查詢一致
5.2.6 查詢條件上無(wú)索引
select * from t where c4 = 23 for update;
RC隔離級(jí)別：會(huì)依次對(duì)c1 in (10, 20, 30, 40)依次加X(jué)鎖较锡，分析是否滿足條件业稼，不滿足即釋放。為c1 = 10行加鎖蚂蕴，不滿足條件釋放鎖盼忌；c1=20加鎖，滿足條件掂墓，保留鎖谦纱；c1=30加鎖，不滿足條件君编，釋放跨嘉；c1=40行加鎖，不滿足條件吃嘿，釋放
RR隔離級(jí)別：c1上的記錄都加record鎖祠乃，所有的gap都加間隙鎖。這個(gè)表相當(dāng)于被鎖死了兑燥，只能快照讀亮瓷，其他的都干不了。（原因是降瞳，沒(méi)辦法確定要鎖住哪一行或者哪個(gè)范圍嘱支。因?yàn)閏4=23可能出現(xiàn)在c1索引的任何位置蚓胸，假設(shè)你鎖住了20行（20行的c4等于23）和20行的間隙，有的會(huì)話可能直接將40行的c4改為了23除师，就會(huì)有引起數(shù)據(jù)污染（RR隔離級(jí)別沛膳，row-statement的bin log）。如果c4上存在索引汛聚，那就是有一個(gè)控制點(diǎn)锹安，在這個(gè)點(diǎn)上鎖記錄或者鎖范圍都是有的放矢。）
5.2.7 Serializable 級(jí)別加鎖分析
Serializable的加鎖與RR隔離級(jí)別下一致倚舀，不同點(diǎn)是Serializable下普通SELECT語(yǔ)句查詢也是當(dāng)前讀叹哭。例如下面語(yǔ)句：
select * from t where c1 = 20就會(huì)對(duì)c1=20的主鍵行加S鎖(LOCK_S|LOCK_REC_NOT_GAP)。
