你好，我是yes。

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

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

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

這句話讀著可能有點拗口，沒事，我來給你看幾個實驗，對這一切就異常清晰了。

實驗的 MySQL 版本為：5.7.26。

實驗一：隔離級別為讀提交，鎖定非索引列的實驗

先建個非常簡單的表，只有主鍵索引，沒有二級索引。

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
隔離級別如下：

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

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

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

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

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

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

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

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

是的，這里的 1 其實不是指第一個記錄的意思，是 id 為 1 的記錄。

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

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

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

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

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

看下面這個實驗，你就清晰了。

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

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

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

此時同樣被阻塞了，但是查看下鎖信息你會發(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 的記錄，但是此時已經(jīng)被事務(wù)C 加鎖了，于是被阻塞。

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

我們再繼續(xù)實驗。

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

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

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

我們再繼續(xù)實驗。

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

我們再繼續(xù)實驗，此時提交事務(wù)A、B、C、D之后，再新起一個事務(wù) E：

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

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

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

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

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

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

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

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

此時被阻塞了，查看鎖信息：

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

這涉及到我的盲區(qū)了，上面的插入還只要記錄鎖，這時候的插入就又要申請間隙鎖了？但是也不是因為間隙被阻塞??？我之后再找個時間研究下，如果有大佬知道，請評論區(qū)指導(dǎo)我下。

我們再繼續(xù)實驗，清理下數(shù)據(jù)，還原到初始狀態(tài)：

啟動一個事務(wù) G 執(zhí)行：

接著再啟動一個事務(wù) H 執(zhí)行：

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

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

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

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

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

小結(jié)一下：

此時把讀者問題列上：

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

在讀提交級別下：

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

insert 都不會被阻塞。

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

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

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

所以只要有一個全表掃描的加鎖，則 insert 的時候就會被阻塞。

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

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

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

實驗三：隔離級別為讀提交，鎖定索引列的實驗

此時在 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í)行如下語句：

可以看到，不會被阻塞，絲滑。

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

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

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

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

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

我們繼續(xù)做實驗。

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

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

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

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

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

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

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

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

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

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

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

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

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

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

此時把事務(wù)A、B都提交了 ，然后我們再執(zhí)行事務(wù) C：

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

此時的插入不會被阻塞，因為事務(wù) C 鎖的是記錄 yes 左右的間隙和 yes 本身，而事務(wù)B提交了，因此事務(wù)D插入的不是被鎖定的位置。

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

則會被阻塞，我們看下鎖的信息：

可以看到，此時被阻塞的鎖是記錄鎖 間隙鎖（next-key lock），這符合我們的認(rèn)知和上面的圖，因為要插入的數(shù)據(jù)在 yes 和公眾號：yes的練級攻略之間。

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

小結(jié)

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

如果此時在讀已提交級別下：

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

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

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

最后

分了四個實驗大類，一個做了十三個實驗。

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

從實驗來看，這里再做個概念性的總結(jié)：

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

聲明：以上實驗是基于 MySQL 5.7.26 版本，存儲引擎為 InnoDB 。

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

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