一條update語句到底加了多少鎖？帶你深入理解底層原理
迎面走來了你的面試官，身穿格子衫，挺著啤酒肚，髮際線嚴重後移的中年男子。
手拿泡著枸杞的保溫杯，胳膊夾著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，明天就來上班吧。
知識點總結：
- MySQL鎖是加在索引記錄上面的。
- 如果是非唯一性索引，不論表中是否存在該記錄，除了會對該記錄所在範圍加鎖，還會向右遍歷到不滿足條件的範圍進行加鎖。
- 如果是唯一索引，如果表中存在該記錄，只對該行記錄加鎖。如果表中不存在該記錄，除了會對該記錄所在範圍加鎖，還會向右遍歷到不滿足條件的範圍進行加鎖。
文章持續更新，可以微信搜一搜「 一燈架構 」第一時間閱讀更多技術乾貨。