你好，我是yes。

前段時(shí)間寫了一篇關(guān)于 MySQL 鎖的文章，一些小伙伴們?cè)陂喿x之后產(chǎn)生了一些疑問，這些問題還挺有代表性的，所以在這里做個(gè)實(shí)驗(yàn)，來用事實(shí)探究一番。

那篇文章提到了記錄鎖（Record Locks），顧名思義鎖的是記錄，作用在索引上的記錄。

鎖是作用在索引上這句話可能不太好理解，并且對(duì)于在可重復(fù)讀和讀提交兩個(gè)隔離級(jí)別下，關(guān)于是否命中二級(jí)索引的鎖之間的阻塞也不太清晰。

這句話讀著可能有點(diǎn)拗口，沒事，我來給你看幾個(gè)實(shí)驗(yàn)，對(duì)這一切就異常清晰了。

實(shí)驗(yàn)的 MySQL 版本為：5.7.26。

實(shí)驗(yàn)一：隔離級(jí)別為讀提交，鎖定非索引列的實(shí)驗(yàn)

先建個(gè)非常簡(jiǎn)單的表，只有主鍵索引，沒有二級(jí)索引。

CREATE?TABLE?`yes`?(
??`id`?bigint(20)?NOT?NULL?AUTO_INCREMENT,
??`name`?varchar(45)?DEFAULT?NULL,
??`address`?varchar(45)?DEFAULT?NULL,
??PRIMARY?KEY?(`id`)
)?ENGINE=InnoDB?AUTO_INCREMENT=4?DEFAULT?CHARSET=utf8mb4
隔離級(jí)別如下：

關(guān)閉自動(dòng)提交事務(wù)：

已經(jīng)準(zhǔn)備好的數(shù)據(jù)：

此時(shí)，發(fā)起事務(wù) A，執(zhí)行如下語句，且事務(wù)未提交：

接著，再發(fā)起事務(wù) B，執(zhí)行如下語句：

你可能以為事務(wù) B 不會(huì)被阻塞，因?yàn)槭聞?wù) B 鎖的是name=xx和事務(wù)A鎖name=yes講道理相互之間沒有沖突，但是從結(jié)果來看，事務(wù) B 被阻塞了，調(diào)用select * from innodb_lock_waits;看下誰等誰

可以看到，事務(wù)6517(B)在等待事務(wù)6516(A)。

此時(shí)，調(diào)用 SELECT * FROM innodb_locks; 查看相關(guān)鎖的信息

鎖的類型就是行級(jí)鎖，此時(shí)的鎖為 X 鎖，鎖的索引就是主鍵索引，這個(gè)結(jié)果表明的意思是事務(wù) B（6517）想要 id 為 1 的記錄鎖，但是這個(gè)記錄此時(shí)被事務(wù)A（6516）占有。

是的，這里的 1 其實(shí)不是指第一個(gè)記錄的意思，是 id 為 1 的記錄。

可能有人疑惑， 為啥 lock_data 為 1 ？

（我沒看過源碼，個(gè)人推斷如下：）執(zhí)行 select ... for update ，由于 name 字段沒有索引，索引事務(wù) A、B 只能加鎖到主鍵索引上，此時(shí)需要搜索 name 為 yes 的記錄，但是又沒有索引，只能全表掃描，恰巧掃描第一條記錄就符合要求了，于是上鎖，然后接著往后掃描，后面不符合條件所以沒有上鎖。此時(shí)事務(wù) B 加鎖，過程和事務(wù) A 一樣需要從第一條記錄開始掃描上鎖，但此時(shí)第一條記錄已經(jīng)被事務(wù) A 鎖了，所以第一條記錄就沖突了，而第一條記錄的 id 就是為 1，因此 lock_data 為 1。

現(xiàn)在，我把事務(wù) A 提交，則事務(wù) B 里面能立馬得到結(jié)果。

從上面這個(gè)實(shí)驗(yàn)可以得知，如果查詢條件上鎖，但是沒有對(duì)應(yīng)的二級(jí)索引可以命中，那么鎖就會(huì)鎖到主鍵(聚簇)索引上。

而聚簇索引的非葉子節(jié)點(diǎn)只有主鍵的信息，沒有 name 的信息，所以只能按順序的全表掃描，加鎖符合條件的記錄，但是在掃描過程中遇到已經(jīng)被加鎖的記錄就會(huì)被阻塞，即使這個(gè)記錄不是目標(biāo)記錄。

看下面這個(gè)實(shí)驗(yàn)，你就清晰了。

這個(gè)實(shí)驗(yàn)其實(shí)就是把事務(wù) A、B的語句執(zhí)行的順序換了一下。

此時(shí)，新起一個(gè)事務(wù) C，先執(zhí)行如下語句，鎖的是id為2的記錄：

然后，再起一個(gè)事務(wù) D，執(zhí)行：

此時(shí)同樣被阻塞了，但是查看下鎖信息你會(huì)發(fā)現(xiàn)：

lock_data 變?yōu)?id 為 2 的記錄了，也就是說事務(wù) C 掃描了 id 為 1 的記錄之后，發(fā)現(xiàn)不符合條件，就釋放了，（不然 lock_data ?的值應(yīng)該為 1）然后繼續(xù)掃描 id 為 2 的記錄，符合條件，于是上鎖。

而事務(wù) D 也掃描了 id 為 1 的記錄，符合條件，于是上鎖，然后接著向后掃描到 ?id 為 2 的記錄，但是此時(shí)已經(jīng)被事務(wù)C 加鎖了，于是被阻塞。

這結(jié)果也符合了我上面的推斷。

我們?cè)倮^續(xù)實(shí)驗(yàn)。

這次來試試 update 的，此時(shí)新起事務(wù) E ：

再起一個(gè)事務(wù) F ：

并沒有發(fā)生阻塞，這其實(shí)是符合我們預(yù)期的。但從中我們可以得知，在讀提交級(jí)別下，即使沒有索引，update 的全表掃描并不是和select ... for update那樣全表按順先加鎖再判斷條件，而是先找到符合的記錄，然后再上鎖。

我們?cè)倮^續(xù)實(shí)驗(yàn)。

此時(shí)，把上面的事務(wù)都提交之后，再新起一個(gè)事務(wù) G 執(zhí)行以下語句，且不提交事務(wù)：

接著，再起一個(gè)事務(wù) H 執(zhí)行以下語句：

可以看到，事務(wù) H 沒有被阻塞，絲滑。

說明在讀提交級(jí)別下，鎖的只是已經(jīng)存在的記錄，對(duì)于插入還是防不住的，即使插入的 name 是 yes，也一樣不會(huì)被阻塞。

實(shí)驗(yàn)二：隔離級(jí)別為可重復(fù)讀，鎖定非索引列的實(shí)驗(yàn)

隔離級(jí)別為可重復(fù)讀：

還是之前的數(shù)據(jù)：

此時(shí)，發(fā)起事務(wù) A，執(zhí)行如下語句，且事務(wù)未提交：

接著，再發(fā)起事務(wù) B，執(zhí)行如下語句：

意料之中的結(jié)果，即事務(wù) B 被阻塞，鎖信息如下，還是 id 為 1 的記錄出了鎖沖突。

此時(shí)提交事務(wù)A、B，然后再新起一個(gè)事務(wù) C：

然后再新起一個(gè)事務(wù) D：

沒錯(cuò)，事務(wù) C、D 就是和 A、B 來個(gè)反順序執(zhí)行，重點(diǎn)來了，此時(shí)的鎖信息如下：

可以看到，沖突的還是 id 為 1 的這條記錄，那說明事務(wù) C 在全表掃描，從第一條開始遍歷，即使訪問到了不符合條件的記錄，加鎖之后在事務(wù)提交之前就不會(huì)釋放！

這里就和讀已提交有差別了。

我們?cè)倮^續(xù)實(shí)驗(yàn)，此時(shí)提交事務(wù)A、B、C、D之后，再新起一個(gè)事務(wù) E：

接著，再起事務(wù) F 執(zhí)行如下語句：

可以看到，事務(wù) F 被阻塞了，此時(shí)再看下鎖的一些信息：

起沖突的 lock_data 是最大記錄(supremum)，這個(gè)記錄之前的文章提過的，MySQL頁默認(rèn)有最大和最小兩條記錄，不存儲(chǔ)數(shù)據(jù)，作用類似于鏈表的 dummy ?節(jié)點(diǎn)。

從這個(gè)結(jié)果來看，這個(gè)最大記錄也被事務(wù) F 鎖了，這個(gè)表的 ID 是自增的，所以此時(shí)的插入記錄，剛好要插入到最后面，這樣就發(fā)生了沖突。

這其實(shí)有點(diǎn)出乎我的意料，我以為事務(wù) F 插入應(yīng)該是被事務(wù) E 加的間隙鎖給擋了才對(duì)。

這時(shí)候，我又做了個(gè)實(shí)驗(yàn)，我先造了一條 id 為 6 的記錄，此時(shí)表內(nèi)的數(shù)據(jù)如下：

同樣再起一個(gè)事務(wù)執(zhí)行，且未提交：

接著，我再起一個(gè)事務(wù)執(zhí)行插入，但是指明了插入的 id 是 4 ，這樣這條記錄會(huì)將插入到記錄 id 為 6 的前面。

此時(shí)被阻塞了，查看鎖信息：

看到截圖的 X,GAP 沒，結(jié)果顯示插入的事務(wù)需要記錄鎖 間隙鎖，但是被前一個(gè)事務(wù)占用的 id 為 6 的記錄鎖給阻塞了。

這涉及到我的盲區(qū)了，上面的插入還只要記錄鎖，這時(shí)候的插入就又要申請(qǐng)間隙鎖了？但是也不是因?yàn)殚g隙被阻塞?。课抑笤僬覀€(gè)時(shí)間研究下，如果有大佬知道，請(qǐng)?jiān)u論區(qū)指導(dǎo)我下。

我們?cè)倮^續(xù)實(shí)驗(yàn)，清理下數(shù)據(jù)，還原到初始狀態(tài)：

啟動(dòng)一個(gè)事務(wù) G 執(zhí)行：

接著再啟動(dòng)一個(gè)事務(wù) H 執(zhí)行：

此時(shí)發(fā)生了阻塞，看下鎖的信息：

可以看到，可重復(fù)讀級(jí)別下 update 的加鎖與讀提交不太一樣，加鎖的 lock_data 是 1，說明事務(wù) G 掃描的 id 為 1 的記錄之后沒有釋放鎖。

如果把事務(wù)G、H 的啟動(dòng)順序反過來，也就是先執(zhí)行 H 的語句再執(zhí)行 G 的語句，結(jié)果也是一樣的，同樣加鎖的 lock_data 是 1，這說明可重復(fù)讀的 update 不是先判斷條件是否符合再上鎖，而是先上鎖再判斷條件是否符合。

update 都會(huì)被阻塞，最終結(jié)論就是：

在可重復(fù)讀級(jí)別下，加鎖非索引列導(dǎo)致的全表記錄上鎖會(huì)使得所有插入和修改都會(huì)被阻塞。

小結(jié)一下：

此時(shí)把讀者問題列上：

留言的回答語境是在可重復(fù)讀級(jí)別下，現(xiàn)在我再來總結(jié)回答下：

在讀提交級(jí)別下：

如果鎖定的列為非索引列，加鎖都是加到主鍵索引上的，select ..for update的加鎖的順序是從前往后全表掃描的順序，遍歷的記錄先上鎖，上鎖之后發(fā)現(xiàn)不滿足條件，則釋放鎖，然后繼續(xù)往后遍歷，直到全表掃描結(jié)束。

insert 都不會(huì)被阻塞。

而 update 其它字段值，其實(shí)也是找記錄，如果找到的記錄已經(jīng)被上鎖了，那么就會(huì)阻塞，如果找到的記錄沒有被鎖則不會(huì)被阻塞。

在可重復(fù)讀級(jí)別下：

如果鎖定的列為非索引列，加鎖都是加到主鍵索引上的，select ..for update的加鎖的順序是從前往后全表掃描的順序，遍歷的記錄先上鎖，上鎖之后發(fā)現(xiàn)不滿足條件，則不會(huì)釋放鎖，然后繼續(xù)往后遍歷，直到全表掃描結(jié)束。

所以只要有一個(gè)全表掃描的加鎖，則 insert 的時(shí)候就會(huì)被阻塞。

而 update 其它字段值，其實(shí)也是找記錄，如果找到的記錄已經(jīng)被上鎖了，那么就會(huì)阻塞，如果找到的記錄沒有被鎖則不會(huì)被阻塞。

與之相關(guān)的還有一個(gè)問題：

圖里已經(jīng)有答案了，包括前面的截圖也可以看到所有的 lock_type 都是 RECORD ，也就是行級(jí)鎖。

實(shí)驗(yàn)三：隔離級(jí)別為讀提交，鎖定索引列的實(shí)驗(yàn)

此時(shí)在 name 列建立索引。

CREATE?TABLE?`yes`?(
??`id`?bigint(20)?NOT?NULL?AUTO_INCREMENT,
??`name`?varchar(45)?DEFAULT?NULL,
??`address`?varchar(45)?DEFAULT?NULL,
??PRIMARY?KEY?(`id`),
??KEY?`idx_name`?(`name`)
)?ENGINE=InnoDB?AUTO_INCREMENT=4?DEFAULT?CHARSET=utf8mb4
同樣準(zhǔn)備數(shù)據(jù)如下：

發(fā)起事務(wù) A，執(zhí)行如下語句，且事務(wù)未提交：

接著發(fā)起事務(wù) B，執(zhí)行如下語句：

可以看到，不會(huì)被阻塞，絲滑。

這個(gè)結(jié)果符合認(rèn)知，因?yàn)榇藭r(shí) name 已經(jīng)有索引了，在讀提交級(jí)別下，只會(huì)在 name 索引上加相關(guān)記錄的鎖，而不會(huì)加全表行鎖，因此事務(wù) A、B 之間不會(huì)被阻塞。

此時(shí)再起一個(gè)事務(wù) C，執(zhí)行如下語句：

可以看到，發(fā)生了阻塞，此時(shí)查看鎖信息：

可以看到，鎖的索引確實(shí)變成了 idx_name，lock_data 顯示鎖的是 yes 這個(gè)記錄，id 為 1。

從結(jié)果看：在可以命中二級(jí)索引的情況下，鎖的是對(duì)應(yīng)的二級(jí)索引。

我們繼續(xù)做實(shí)驗(yàn)。

將上面所有事務(wù)提交之后。

啟動(dòng)事務(wù) C 執(zhí)行以下語句，且未提交事務(wù)：

接著，事務(wù) D 執(zhí)行以下語句：

并不會(huì)發(fā)生阻塞，絲滑地插入了數(shù)據(jù)。

執(zhí)行 name 一樣的插入，也不會(huì)阻塞。

所以在讀提交級(jí)別下，對(duì)插入都不會(huì)產(chǎn)生阻塞。

關(guān)于 update 我就不實(shí)驗(yàn)了，和實(shí)驗(yàn)一的差別就是加鎖索引換成了 name 的索引，其他表現(xiàn)一致。

實(shí)驗(yàn)四：隔離級(jí)別為可重復(fù)讀，鎖定索引列的實(shí)驗(yàn)

同樣準(zhǔn)備數(shù)據(jù)如下：

在可重復(fù)讀級(jí)別下，事務(wù)A執(zhí)行：

接著，事務(wù) B 執(zhí)行：

此時(shí)發(fā)生了阻塞，查看鎖信息：

這是預(yù)期之內(nèi)的阻塞，因?yàn)榘凑?name 為索引，yes這條記錄是排在最后的(字母序)，為了防止幻讀，可重讀隔離級(jí)別下會(huì)在對(duì)應(yīng)記錄前后加入間隙鎖，而新的記錄的插入恰巧需要排 yes 這條記錄的后面。

但是從截圖結(jié)果來看此時(shí)lock_mode是記錄鎖，且 lock_data 是 supremum，這又涉及到我的盲區(qū)了，難道是最后的記錄插入比較特殊？所以不是因?yàn)殚g隙鎖被阻塞，而是被最大記錄行鎖阻塞？

此時(shí)把事務(wù)A、B都提交了 ，然后我們?cè)賵?zhí)行事務(wù) C：

接著再執(zhí)行事務(wù) D：

此時(shí)的插入不會(huì)被阻塞，因?yàn)槭聞?wù) C 鎖的是記錄 yes 左右的間隙和 yes 本身，而事務(wù)B提交了，因此事務(wù)D插入的不是被鎖定的位置。

如果此時(shí)事務(wù) C 接著再執(zhí)行：

則會(huì)被阻塞，我們看下鎖的信息：

可以看到，此時(shí)被阻塞的鎖是記錄鎖 間隙鎖（next-key lock），這符合我們的認(rèn)知和上面的圖，因?yàn)橐迦氲臄?shù)據(jù)在 yes 和公眾號(hào)：yes的練級(jí)攻略之間。

update我就不實(shí)驗(yàn)了，不是全表掃描，只會(huì)根據(jù)索引加鎖掃描到的記錄。

小結(jié)

在命中索引列的前提下，只會(huì)在索引列上加鎖。

如果此時(shí)在讀已提交級(jí)別下：

select..for update和update的所查找的記錄本身會(huì)被加上記錄鎖，因此這個(gè)位置的插入會(huì)被阻塞，其他位置的插入則沒有影響。

如果此時(shí)在可重復(fù)讀級(jí)別下：

select..for update和update的所查找的記錄在索引位置前后會(huì)被加間隙鎖，記錄本身加記錄鎖，因此這些位置的插入會(huì)被阻塞，其他位置的插入則沒有影響。

最后

分了四個(gè)實(shí)驗(yàn)大類，一個(gè)做了十三個(gè)實(shí)驗(yàn)。

還是挺有收獲的，驚喜就是發(fā)現(xiàn)了細(xì)節(jié)盲區(qū)，之后研究一下再出一篇文章。

從實(shí)驗(yàn)來看，這里再做個(gè)概念性的總結(jié)：

• 鎖是作用在索引上的，因此如果能命中二級(jí)索引就在二級(jí)索引上加鎖，不然就得被迫在聚簇索引上加鎖。
• 被迫在聚簇索引上加鎖，會(huì)導(dǎo)致全表掃描式的加鎖。
• 在可重復(fù)讀下，不論命中哪個(gè)索引，不論是select..for update還是update，只要被掃描到的記錄，都會(huì)被加鎖，不論是否符合條件，在事務(wù)提交之后才會(huì)釋放。
• 在讀提交下，select..for update表現(xiàn)出來的結(jié)果是掃描到的記錄先加鎖，再判斷條件，不符合就立馬釋放，不需要等到事務(wù)提交，而 update 的掃描是先判斷是否符合條件，符合了才上鎖。

聲明：以上實(shí)驗(yàn)是基于 MySQL 5.7.26 版本，存儲(chǔ)引擎為 InnoDB 。

這些實(shí)驗(yàn)我之前花了三個(gè)工作日晚上做的，由于時(shí)間是零散的，導(dǎo)致中間實(shí)驗(yàn)出錯(cuò)，期間設(shè)置事務(wù)隔離級(jí)別語句有問題，導(dǎo)致我在錯(cuò)誤的前提下做實(shí)驗(yàn)，實(shí)驗(yàn)結(jié)果不斷地沖擊我的認(rèn)知，我整個(gè)人都快搞崩潰了....

然后周六花了一天的時(shí)間重新理了一下，實(shí)驗(yàn)圖很多，可能看了后面就忘了前面，建議結(jié)合著結(jié)論來回看，這樣對(duì)結(jié)論會(huì)有更深刻的認(rèn)識(shí)，但是有些實(shí)驗(yàn)結(jié)論我是根據(jù)實(shí)驗(yàn)現(xiàn)象來推斷的，我沒有去找相關(guān)的官網(wǎng)說明，如有錯(cuò)誤，懇請(qǐng)指正，如有疑惑還請(qǐng)自行實(shí)驗(yàn)，可以在評(píng)論區(qū)交流一番。