Mysql學習筆記-臨鍵鎖實驗

邱志強發表於2022-03-25
MySql筆記

前言
昨天同事跟我聊到一個問題：InnoDB裡面間隙鎖鎖住的資料可以update麼？我們經常都說間隙鎖是InnoDB在RR隔離級別下防止幻讀的一種處理手段。它可以防止資料在間隙範圍中insert資料，但是對於update?很多資料都沒有明顯說明，今天我們們就通過幾個實驗來揭開間隙鎖的神祕面紗。

mysql命令

  • 檢視自動提交事務開關狀態：show variables like 'autocommit';
  • 關閉自動事務：set autocommit = 0;
  • 檢視事務隔離級別:show variables like 'transaction%';
  • 設定事務隔離級別：set session transaction isolation level read committed;
  • 檢視當前伺服器鎖情況:select * from performance_schema.data_locks;

環境

Mysql Server 8.0.28 / InnoDB / RR

實驗表

CREATE TABLE `user` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `card` varchar(18) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  `sex` int DEFAULT NULL,
  `age` int DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
初始化資料

image

下面讓我們開啟實驗

實驗一：foru update查詢下,主鍵更新

sql
-- 事務一：主鍵寫鎖查詢,左開右開
select * from user where id > 3 and id < 8 for update;
-- 事務二：範圍內更新，根據主鍵更新值
update user set name = 1 where id = 7;

實驗結果(鎖衝突等待)

image

結果分析：
  • 紅框部分為更新事務，持有IX鎖，等待行鎖獲取，行鎖加鎖物件為主鍵索引7;
  • 非紅框部分為查詢事務，範圍內記錄均持有X鎖(臨鍵鎖)，鎖定的都是LOCK_DATA值前面的間隙和值本身，但是主鍵索引為8的資料，不包含本身。
示意圖如下

image

ps:
x：間隙鎖(左開右閉)，即臨鍵鎖
x,GAP：間隙鎖(雙端開口)
X,REC_NOT_GAP：記錄鎖即行鎖

實驗二：foru update查詢下,不使用索引

sql
-- 事務一：非索引寫鎖查詢,左開右閉
select * from user where age > 18 and age <= 40 for update;

-- 事務二：非索引更新，此條記錄主鍵:6，age:33
update user set sex = 1 where name ='吳八';

實驗結果(鎖衝突等待)

image

結果分析：

1、由於查詢和更新均未使用索引，導致鎖全表。
2、查詢事務全部獲取臨鍵鎖
3、更新事務由於全表鎖，所以從主鍵最小的節點開始等待獲取鎖

示意圖如下

image
注意：由於查詢事務會把主鍵最小節點前面間隙也都的都鎖上

實驗三:輔助索引寫鎖查詢，非索引更新

sql
-- 事務一：二級索引寫鎖查詢,左開右閉
select * from user where card > '0002' and card <= '0008' for update;3
-- 事務二：非索引更新，此條記錄主鍵:3，card：0005
update user set sex = 1 where name ='王五';

實驗結果(鎖等待衝突)

image

結果分析
  • 查詢事務對於輔助索引增加臨鍵鎖，對於輔助索引對應的主鍵增加行鎖。
  • 由於我們查詢區間為左開右閉，查詢事務還會給臨界索引資料的後一個節點加上臨鍵鎖。然後由於000x不在查詢範圍內，所以主鍵鎖定不包含000x輔助索引對應的主鍵。
  • 更新事務由於無索引，走全表，從主鍵最小節點開始逐步加鎖，1和2都獲取到了，3開始等待。
示意圖如下:

image

小結：
1、間隙鎖範圍內部資料其他事務需要等待鎖，否則無法修改、插入、刪除。
2、對於查詢或者更新條件包含主鍵索引的資料會加行鎖或臨鍵鎖(行鎖+間隙鎖)。
3、對於查詢或者更新條件為輔助索引的資料會加行鎖或臨鍵鎖(行鎖+間隙鎖)。對於左開右閉區間的索引範圍查詢更改，間隙鎖邊界會後移一個節點，參考實驗二。
4、對於查詢或者更新條件不包含索引的資料會鎖全表(所有行記錄的臨鍵鎖)，等待事務會從主鍵最小節點逐步嘗試加鎖，獲取不到則進入等待。
5、等值查詢帶索引使用行鎖，範圍查詢或者無索引情況下，使用臨鍵鎖，全表鎖也是體現在每一行上面加臨鍵鎖。
6、lock in share mode和for update類似，對於上述例子基本沒有區別，唯一區別只是查詢事務獲取的意向鎖由IX變為IS。(如果兩個都是查詢的話，lock in share mode不會被阻塞,共享鎖，也就是讀鎖可以重入)。

ps:大家常說的 next-key lock其實就是臨鍵鎖,左邊的間隙鎖 + 右邊行的行鎖。

還有很多場景沒有去做實驗，一是太多實驗寫起來費事，看起來也費事，二是我覺得絕大場景都可以通過以上三個例子推匯出來，比如查有索引、更新也有索引，那就是鎖定指定的索引，如果是二級索引還需要鎖定對應的主鍵索引。如果大家對間隙鎖還有不明白的，可以留言一起討論下。有需要實驗的也可以留言給我，我幫你實現圖片！

願每一個人都能找到自己心中的方向！

關注我的公眾號
image

相關文章