信不信？這個(gè) SQL 題，可以難倒大部分人
大家好，我是小富～
周末的時(shí)候，一個(gè)讀者問(wèn)了我一個(gè)很有意思的問(wèn)題，是關(guān)于 MySQL 中 update 加鎖的問(wèn)題。
他用下面這張數(shù)據(jù)庫(kù)表，做了個(gè) MySQL 實(shí)驗(yàn)的時(shí)候。
發(fā)現(xiàn)事務(wù) B 的 update 不會(huì)阻塞，而事務(wù) C 的 update 會(huì)阻塞，都是對(duì) id = 10 這條記錄進(jìn)行 update， 為什么一個(gè)會(huì)阻塞，一個(gè)不會(huì)阻塞？
首先，我們先來(lái)分析下，事務(wù) A 這條 SQL 加了什么鎖。
// 事務(wù) A
select * from t_person where id < 10 for update;
我直接說(shuō)結(jié)論，事務(wù) A 加了這三個(gè)行級(jí)鎖：
在 id 為 1 的主鍵索引上，加了 X 型的 next-key 鎖，范圍是 (-∞,1]。意味著，其他事務(wù)無(wú)法對(duì) id = 1 的記錄進(jìn)行刪除和更新操作，同時(shí)無(wú)法插入 id 小于 1 的新記錄。 在 id 為 5 的主鍵索引上，加了 X 型的 next-key 鎖，范圍是 (1, 5]。意味著，其他事務(wù)無(wú)法對(duì) id = 5 的記錄進(jìn)行刪除和更新操作，同時(shí)無(wú)法插入 id 為 2、3、4 的新記錄。 在 id 為 10 的主鍵索引上，加了 X 型的間隙鎖，范圍是 (5, 10)。意味著，其他事務(wù)無(wú)法插入 id 為 6、7、8、9 的新紀(jì)錄。
PS：如果你不清楚什么是 MySQL 這些行級(jí)鎖（記錄鎖、間隙鎖、next-key 鎖），以及不清楚行級(jí)鎖的加鎖規(guī)則，強(qiáng)烈建議先看我之前寫(xiě)的這篇：MySQL 是怎么加行級(jí)鎖的？，看完后，你回頭看我這篇文章，就會(huì)有感覺(jué)的了。
事務(wù) B 的 update 語(yǔ)句為什么不會(huì)阻塞？
事務(wù) B 的 update 語(yǔ)句是對(duì) id = 10 的行記錄的 name 字段進(jìn)行更新。
// 事務(wù) B
update t_person set name = "小林" where id = 10;
事務(wù) B 會(huì)在 id = 10 的主鍵索引上加 X 型記錄鎖，僅鎖住這一行。因?yàn)楫?dāng)我們用唯一索引進(jìn)行等值查詢的時(shí)候，查詢的記錄是「存在」的，在索引樹(shù)上定位到這一條記錄后，該記錄的索引中的 next-key 鎖會(huì)退化成「記錄鎖」。
事務(wù) A 并沒(méi)有對(duì) id = 10 的主鍵索引上加 X 型記錄鎖，而是對(duì) id = 10 的主鍵索引上加 X 型間隙鎖。間隙鎖和記錄鎖之間是沒(méi)有互斥關(guān)系的，所以事務(wù) B 的 update 語(yǔ)句不會(huì)阻塞。
事務(wù) C 的 update 語(yǔ)句為什么會(huì)阻塞？
事務(wù) C 的 update 語(yǔ)句是將 id = 10 的行記錄的 id 更新為 2。
// 事務(wù) C
update t_person set id = 2 where id = 10;
這條 update 很特殊，特殊之處在于更新了主鍵索引。你以為它只是一個(gè)更新操作，實(shí)際上它在背后執(zhí)行了兩個(gè)操作：
操作 1：delete from t_person where id = 10; 操作 2：insert into t_person (2, 陳某, 30, 廣州市海珠區(qū));
也就是先刪除 id = 10 的記錄，然后再插入 id = 2 的新紀(jì)錄。
為什么當(dāng) update 語(yǔ)句更新了索引值，會(huì)被拆分成刪除和插入操作？
要回答這個(gè)問(wèn)題，我們先要清楚 B+ 樹(shù)的特點(diǎn)。
Innodb（MySQL 存儲(chǔ)引擎）在實(shí)現(xiàn)索引的時(shí)候，采用的數(shù)據(jù)結(jié)構(gòu)是 B+ 樹(shù)。B+ 樹(shù)是基于二分查找樹(shù)演變過(guò)來(lái)的，所以 B+ 樹(shù)在存儲(chǔ)索引的時(shí)候，是按順序存儲(chǔ)的，因?yàn)檫@樣才能利用二分查找快速檢索到索引。
現(xiàn)在有一顆這樣的 B+ 樹(shù)，可以看到葉子節(jié)點(diǎn)的索引值是從小到大的順序。
假設(shè)這時(shí)候需要將索引值為 25 更新為 3，如果直接索引值為 25 的位置上，將值改為 3 的話。
這時(shí)候你就會(huì)發(fā)現(xiàn)這棵 B+ 樹(shù)不滿足順序性了！
所以更新索引的值，不能只是修改一個(gè)索引值就完事，而是還要保證更新后的索引值能繼續(xù)滿足 B+ 樹(shù)的順序性。
解決的方法就是，先刪除索引值為 25 的節(jié)點(diǎn)，再插入索引值為 3 的節(jié)點(diǎn)，這樣，這顆 B+ 樹(shù)才能滿足順序性。
事務(wù) C 的 update 語(yǔ)句具體阻塞在哪個(gè)「操作」？
現(xiàn)在我們知道，事務(wù) C 的 update 特殊語(yǔ)句背后執(zhí)行了兩個(gè)操作，分別是刪除和插入操作，那具體是阻塞在哪個(gè)「操作 」？
「操作 1 」是刪除 id = 10 的記錄，事務(wù) C 是會(huì)在 id = 10 的主鍵索引上加 X 型記錄鎖，而事務(wù) A 并沒(méi)有對(duì) id = 10 的主鍵索引上加 X 型記錄鎖，而是對(duì) id = 10 的主鍵索引上加 X 型間隙鎖。間隙鎖和記錄鎖之間是沒(méi)有互斥關(guān)系的，所以「操作 1 」不會(huì)阻塞。
根據(jù)排除法，既然 「操作 1 」不會(huì)阻塞，那事務(wù) C 的 update 語(yǔ)句阻塞的原因就是因?yàn)?「操作 2」發(fā)生了阻塞。
為什么「操作2」會(huì)發(fā)生阻塞呢？
我們先要知道，插入操作什么時(shí)候會(huì)發(fā)生阻塞：插入語(yǔ)句在插入一條新記錄之前，需要先定位到該記錄在 B+樹(shù)的位置，如果插入的位置的下一條記錄的索引上有間隙鎖，此時(shí)會(huì)生成一個(gè)插入意向鎖，然后鎖的狀態(tài)設(shè)置為等待狀態(tài)，現(xiàn)象就是插入語(yǔ)句會(huì)被阻塞。
「操作 2」插入的是 id = 2 的新記錄，在主鍵索引的 B+樹(shù)定位到插入的位置如下圖。
插入位置的下一條記錄是 id = 5 的記錄，而事務(wù) A 在 id 為 5 的主鍵索引上已經(jīng)加了 X 型的 next-key 鎖，這里面包含了間隙鎖。所以「操作 2」的插入操作會(huì)發(fā)生阻塞，這就是事務(wù) C 的 update 語(yǔ)句阻塞的原因。
從這我們也可以知道間隙鎖的作用，就是阻止其他事務(wù)在間隙鎖的范圍內(nèi)插入新記錄，從而避免可重復(fù)讀隔離級(jí)別下幻讀的現(xiàn)象。
我們也可以通過(guò) select * from performance_schema.data_locks\G; 這條語(yǔ)句，查看事務(wù) C 在加什么鎖的時(shí)候?qū)е伦枞?/p>
從上面的輸出信息，可以看到事務(wù) C 在加「插入意向鎖」的時(shí)候，發(fā)生了阻塞。
插入意向鎖是插入操作才會(huì)有的鎖，而事務(wù) C 只是執(zhí)行 update 語(yǔ)句，卻出現(xiàn)了插入意向鎖，從這里也可以證明，事務(wù) C 這條特殊的 update 語(yǔ)句運(yùn)行的時(shí)候，被拆分成了兩個(gè)操作，一個(gè)是刪除，另一個(gè)是插入。
總之，如果 update 語(yǔ)句更新的是普通字段的值，就會(huì)對(duì)發(fā)生更新的記錄加 X 型記錄鎖。
但是，如果 update 語(yǔ)句更新的是索引的值，那么在運(yùn)行的時(shí)候會(huì)被拆分成刪除和插入操作，這時(shí)候分析鎖的時(shí)候，要從這兩個(gè)操作的角度去分析。
完啦！
怎么樣，夠不夠細(xì)節(jié)？
·········· END ··············
在看、點(diǎn)贊、轉(zhuǎn)發(fā)，是對(duì)我最大的鼓勵(lì)。
技術(shù)書(shū)籍公眾號(hào)內(nèi)回復(fù)[ pdf ] Get。
面試筆記、springcloud進(jìn)階實(shí)戰(zhàn)PDF，公眾號(hào)內(nèi)回復(fù)[ 1222 ] Get。
有幾個(gè)技術(shù)群，想進(jìn)的同學(xué)可以加我好友，備注：進(jìn)群，一起成長(zhǎng)。