我打賭！這個 SQL 題，大部分人答不出來

大家好，我是小林。

週末的時候，一個讀者問了我一個很有意思的問題，是關於 MySQL 中 update 加鎖的問題。

他用下面這張資料庫表，做了個 MySQL 實驗的時候。

發現事務 B 的 update 不會阻塞，而事務 C 的 update 會阻塞，都是對 id = 10 這條記錄進行 update， 為什麼一個會阻塞，一個不會阻塞？

首先，我們先來分析下，事務 A 這條 SQL 加了什麼鎖。

// 事務 A 
select * from t_person where id < 10 for update;

我直接說結論，事務 A  加了這三個行級鎖：

• 在 id 為 1 的主鍵索引上，加了 X 型的 next-key 鎖，範圍是 (-∞,1]。意味著，其他事務無法對 id = 1 的記錄進行刪除和更新操作，同時無法插入 id 小於 1 的新記錄。
• 在 id 為 5 的主鍵索引上，加了 X 型的 next-key 鎖，範圍是 (1, 5]。意味著，其他事務無法對 id = 5 的記錄進行刪除和更新操作，同時無法插入 id 為 2、3、4 的新記錄。
• 在 id 為 10 的主鍵索引上，加了 X 型的間隙鎖，範圍是 (5, 10)。意味著，其他事務無法插入 id 為 6、7、8、9 的新紀錄。

PS：如果你不清楚什麼是 MySQL 這些行級鎖（記錄鎖、間隙鎖、next-key 鎖），以及不清楚行級鎖的加鎖規則，強烈建議先看我之前寫的這篇：MySQL 是怎麼加行級鎖的？，看完後，你回頭看我這篇文章，就會有感覺的了。

事務 B 的 update 語句為什麼不會阻塞？

事務 B 的 update 語句是對 id = 10 的行記錄的 name 欄位進行更新。

// 事務 B
update t_person set name = "小林" where id = 10;

事務 B 會在 id = 10 的主鍵索引上加 X 型記錄鎖，僅鎖住這一行。因為當我們用唯一索引進行等值查詢的時候，查詢的記錄是「存在」的，在索引樹上定位到這一條記錄後，該記錄的索引中的 next-key 鎖會退化成「記錄鎖」。

事務 A 並沒有對 id = 10 的主鍵索引上加 X 型記錄鎖，而是對 id = 10 的主鍵索引上加 X 型間隙鎖。間隙鎖和記錄鎖之間是沒有互斥關係的，所以事務 B 的 update 語句不會阻塞。

事務 C 的 update 語句為什麼會阻塞？

事務 C 的 update 語句是將 id = 10 的行記錄的 id 更新為 2。

// 事務 C
update t_person set id = 2 where id = 10;

這條 update 很特殊，特殊之處在於更新了主鍵索引。你以為它只是一個更新操作，實際上它在背後執行了兩個操作：

• 操作 1：delete from t_person where id = 10;
• 操作 2：insert into t_person (2, 陳某,  30, 廣州市海珠區);

也就是先刪除 id = 10 的記錄，然後再插入 id = 2 的新紀錄。

為什麼當 update 語句更新了索引值，會被拆分成刪除和插入操作？

要回答這個問題，我們先要清楚 B+ 樹的特點。

Innodb（MySQL 儲存引擎）在實現索引的時候，採用的資料結構是 B+ 樹。B+ 樹是基於二分查詢樹演變過來的，所以 B+ 樹在儲存索引的時候，是按順序儲存的，因為這樣才能利用二分查詢快速檢索到索引。

現在有一顆這樣的  B+ 樹，可以看到葉子節點的索引值是從小到大的順序。

假設這時候需要將索引值為 25 更新為 3，如果直接索引值為 25 的位置上，將值改為 3 的話。

這時候你就會發現這棵 B+ 樹不滿足順序性了！

所以更新索引的值，不能只是修改一個索引值就完事，而是還要保證更新後的索引值能繼續滿足  B+ 樹的順序性。

解決的方法就是，先刪除索引值為 25 的節點，再插入索引值為 3 的節點，這樣，這顆 B+ 樹才能滿足順序性。

事務 C 的 update  語句具體阻塞在哪個「操作」？

現在我們知道，事務 C 的 update 特殊語句背後執行了兩個操作，分別是刪除和插入操作，那具體是阻塞在哪個「操作 」？

「操作 1 」是刪除 id = 10 的記錄，事務 C 是會在 id = 10 的主鍵索引上加 X 型記錄鎖，而事務 A 並沒有對 id = 10 的主鍵索引上加 X 型記錄鎖，而是對 id = 10 的主鍵索引上加 X 型間隙鎖。間隙鎖和記錄鎖之間是沒有互斥關係的，所以「操作 1 」不會阻塞。

根據排除法，既然 「操作 1 」不會阻塞，那事務 C 的 update 語句阻塞的原因就是因為 「操作 2」發生了阻塞。

為什麼「操作2」會發生阻塞呢？

我們先要知道，插入操作什麼時候會發生阻塞：插入語句在插入一條新記錄之前，需要先定位到該記錄在 B+樹的位置，如果插入的位置的下一條記錄的索引上有間隙鎖，此時會生成一個插入意向鎖，然後鎖的狀態設定為等待狀態，現象就是插入語句會被阻塞。

「操作 2」插入的是 id = 2 的新記錄，在主鍵索引的 B+樹定位到插入的位置如下圖。

插入位置的下一條記錄是 id = 5 的記錄，而事務 A 在 id 為 5 的主鍵索引上已經加了 X 型的 next-key 鎖，這裡麵包含了間隙鎖。所以「操作 2」的插入操作會發生阻塞，這就是事務 C 的 update 語句阻塞的原因。

從這我們也可以知道間隙鎖的作用，就是阻止其他事務在間隙鎖的範圍內插入新記錄，從而避免可重複讀隔離級別下幻讀的現象。

我們也可以透過 select * from performance_schema.data_locks\G; 這條語句，檢視事務 C 在加什麼鎖的時候導致阻塞。

從上面的輸出資訊，可以看到事務 C 在加「插入意向鎖」的時候，發生了阻塞。

插入意向鎖是插入操作才會有的鎖，而事務 C 只是執行 update 語句，卻出現了插入意向鎖，從這裡也可以證明，事務 C 這條特殊的 update 語句執行的時候，被拆分成了兩個操作，一個是刪除，另一個是插入。

總之，如果 update 語句更新的是普通欄位的值，就會對發生更新的記錄加 X 型記錄鎖。

但是，如果 update 語句更新的是索引的值，那麼在執行的時候會被拆分成刪除和插入操作，這時候分析鎖的時候，要從這兩個操作的角度去分析。

完啦！

怎麼樣，夠不夠細節？