一條update語句到底加了多少鎖？帶你深入理解底層原理

一燈架構發表於2022-06-28

迎面走來了你的面試官，身穿格子衫，挺著啤酒肚，髮際線嚴重後移的中年男子。
手拿泡著枸杞的保溫杯，胳膊夾著MacBook，MacBook上還貼著公司標語：“我愛加班”。

面試開始，直入正題。

面試官： 看你簡歷上面寫著精通MySQL，我問你一個MySQL鎖相關的問題，你看一下這條SQL會對哪些資料加鎖？

update user set name='一燈' where age=5;

表結構是這樣的：

CREATE TABLE `user` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '主鍵',
  `name` varchar(255) DEFAULT NULL COMMENT '姓名',
  `age` int DEFAULT NULL COMMENT '年齡',
  PRIMARY KEY (`id`),
  KEY `idx_age` (`age`)
) ENGINE=InnoDB COMMENT='使用者表';

我： age是非唯一性索引，MySQL的鎖是加在索引上面的，應該只會對age=10的資料加鎖。

面試官： 確定嗎？

我： 嗯...，應該是的。

面試官： 【嘲諷】，這就是你精通MySQL的水平嗎？今天面試就先到這裡吧，後面有訊息會主動聯絡你。

後面還可能有訊息嗎？你們啥時候主動聯絡過我？
實話實說的被拒，八股文背得溜反而被錄取。
好吧，等我看看一燈怎麼總結的MySQL的八股文。

我： 這條SQL具體對哪些資料加鎖，還需要看錶中有哪些資料。

MySQL有三種型別的行鎖：

記錄鎖（Record Locks）：

即對某條記錄加鎖。

# 對id=1的使用者加鎖
update user set age=age+1 where id=1;

間隙鎖（Gap Locks）：

即對某個範圍加鎖，但是不包含範圍的臨界資料。

# 對id大於1並且小於10的使用者加鎖
update user set age=age+1 where id>1 and id<10;

上面SQL的加鎖範圍是(1,10)。

臨鍵鎖（Next-Key Locks）：

由記錄鎖和間隙鎖組成，既包含記錄本身又包含範圍，左開右閉區間。

# 對id大於1並且小於等於10的使用者加鎖
update user set age=age+1 where id>1 and id<=10;

假如表中只有這樣兩條資料的話：

id name age
1 張三 1
10 李四 10

針對age索引，很產生這樣三個索引範圍：

(-∞,1]，(1,10]，(10,+∞)

剛才的這條SQL：

update user set name='一燈' where age=5;

由於表中不存在age=5的記錄，並且age=5剛好落在 (1,10] 的區間範圍內，所以會對 (1,10] 的範圍加鎖。

我們可以用實際資料測試一下：

當我們執行update語句的時候，age=2和age=8的資料範圍都被加鎖了。

面試官： 小夥子回答的不錯啊。如果已經存在age=5的資料，剛才的那條update語句會對哪些資料加鎖？

我： 假如表中資料是這樣的。

id name age
1 張三 1
5 一燈架構 5
10 李四 10

針對age索引，很產生這樣四個索引範圍：

(-∞,1]，(1,5]，(5,10]，(10,+∞)

剛才的這條SQL：

update user set name='一燈' where age=5;

age=5的資料落在 (1,5] 的區間範圍內，所以會對 (1,5] 的範圍加鎖。

你以為這就完了嗎？MySQL鎖為了保證資料的安全性，還會向右遍歷到不滿足條件為止，還會再加一個間隙鎖，也就是 (5,10] 的範圍。

所以，這條SQL的加鎖返回是 (1,5] 和 (5,10] 。

跟剛才age=5不存在的加鎖範圍 (1,10] 是一樣的。不信可以再用剛才的測試用例跑一遍。

面試官： 小夥子有點東西。如果我把SQL中where條件換成主鍵ID，加鎖範圍是什麼樣的？

update user set name='一燈' where id=5;

我： 由於鎖是加在索引上面的。

如果不存在id=5的資料，加鎖範圍跟上條SQL是一樣的， (1,10] 。

如果存在id=5的資料，MySQL的 Next-Key Locks 會退化成 Record Locks ，也就是隻在id=5的這一行記錄上加鎖。

面試官： 小夥子，升級加薪的機會就是留給你這樣的人。薪資double，明天就來上班吧。

知識點總結：

  1. MySQL鎖是加在索引記錄上面的。
  2. 如果是非唯一性索引，不論表中是否存在該記錄，除了會對該記錄所在範圍加鎖，還會向右遍歷到不滿足條件的範圍進行加鎖。
  3. 如果是唯一索引，如果表中存在該記錄，只對該行記錄加鎖。如果表中不存在該記錄，除了會對該記錄所在範圍加鎖，還會向右遍歷到不滿足條件的範圍進行加鎖。

文章持續更新，可以微信搜一搜「 一燈架構 」第一時間閱讀更多技術乾貨。

相關文章