一條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. 如果是唯一索引,如果表中存在該記錄,只對該行記錄加鎖。如果表中不存在該記錄,除了會對該記錄所在範圍加鎖,還會向右遍歷到不滿足條件的範圍進行加鎖。

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

相關文章