腳本之家服務器常用軟件

快捷導航

mysql間隙鎖的具體使用

更新時間：2024年02月04日 11:51:09 作者：好大的月亮

MySQL中有多種鎖類型,本文主要介紹了mysql間隙鎖的具體使用,文中通過示例代碼介紹的非常詳細,需要的朋友們下面隨著小編來一起學習學習吧

案例一：間隙鎖簡單案例
案例二：間隙鎖死鎖問題
案例三：等值查詢—唯一索引
案例四：等值查詢—普通索引
案例五：范圍查詢—唯一索引
案例六：范圍查詢—普通索引
案例八：普通索引-等值問題
案例九：普通索引-等值Limit問題

概述

通常用的mysql都是innodb引擎；
一般在update的時候用id都會認為是給行記錄加鎖；

在使用非唯一索引更新時，會遇到臨鍵鎖（范圍鎖）；
臨鍵鎖和表中的數(shù)據(jù)有關；

mysq版本:8隔離級別：RR可重復讀

mysql的lockMode

查看鎖的活動信息

-- 當前活動的鎖信息
SELECT * FROM performance_schema.data_locks;

X,REC_NOT_GAP：X代表排他鎖，REC_NOT_GAP代表行鎖。綜合起來就是對這條數(shù)據(jù)（索引項）添加了行級排他鎖;
IX：意向排它鎖。上述案例中，給表添加了一個意向排它鎖。當其他事務要對全表的數(shù)據(jù)進行加鎖時，那么就不需要判斷每一條數(shù)據(jù)是否被加鎖了。
X,GAP:X代表排他鎖；GAP代表間隙鎖（前開后開,即當前的lock_data中的索引值對應的id到最近的上一個id值之間的空隙被鎖定了）
supremum pseudo-record：是 InnoDB 中定義的一種特殊記錄，我們可以理解為 +∞

X,GAP demo說明

假設表中的數(shù)據(jù)如下,age有普通BTree索引，然后事務1更新其中age為24的數(shù)據(jù)；
可以看到age=24對應的id=3加上了X排它鎖,id=3的加上了行級排它鎖；然后對age=24后面的age索引加了一個排他間隙鎖，鎖住了id范圍為(3,5)時間的間隙;

綜上獲得了id范圍為[3,5)之間的鎖

-- 事務1操作
UPDATE user SET name = 'Vladimir' WHERE age = 24;

在這里插入圖片描述

臨鍵鎖

假設有如下表；且表中數(shù)據(jù)如下；

-- 表結構如下
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='用戶表';

撈點網(wǎng)圖,間隙鎖可以看做一個左開右閉的區(qū)間

在這里插入圖片描述

那么在事務A中執(zhí)行sql;
先select查詢，查詢到age=24的記錄，然后繼續(xù)向后查詢，發(fā)現(xiàn)后一條是age=32,age不等于24，向后查詢結束，所以在獲取間隙鎖(24,32]，同時當前age=24的記錄會加上排它鎖;

然后再向前查詢前一條記錄是age=10，不等于24，向前查詢結束，然后會獲取(10，24]的間隙鎖;

綜上所述，INNDB加鎖首先定位到等于或者第一個大于目標值的葉子節(jié)點

事務A

-- 根據(jù)非唯一索引列 UPDATE 某條記錄
UPDATE user SET name = 'Vladimir' WHERE age = 24;
-- 或根據(jù)非唯一索引列 鎖住某條記錄
SELECT * FROM user WHERE age = 24 FOR UPDATE;

在事務B中執(zhí)行sql；會遇到間隙鎖，會阻塞

INSERT INTO user VALUES(100, 'Ezreal', 30);

試驗結果如下

在這里插入圖片描述

-- 當前活動的鎖信息
SELECT * FROM performance_schema.data_locks;

在這里插入圖片描述

案例

案例一：間隙鎖簡單案例

步驟	事務A	事務B
1	begin; select * from t where id = 11 for update;	-
2	-	insert into user value(12,12,12)
3	commit;	-

當有如下事務A和事務B時，事務A會對數(shù)據(jù)庫表增加（10，15]這個區(qū)間鎖，這時insert id = 12 的數(shù)據(jù)的時候就會因為區(qū)間鎖（10，15]而被鎖住無法執(zhí)行。

案例二：間隙鎖死鎖問題

步驟	事務A	事務B
1	begin; select * from t where id = 9 for update;	-
2	-	begin; select * from t where id = 6 for update;
3	-	insert into user value(7,7,7)
4	insert into user value(7,7,7)	-

不同于寫鎖相互之間是互斥的原則，間隙鎖之間不是互斥的，如果一個事務A獲取到了（5,10]之間的間隙鎖，另一個事務B也可以獲取到（5,10]之間的間隙鎖。這時就可能會發(fā)生死鎖問題，如下案例。
事務A獲取到（5,10]之間的間隙鎖不允許其他的DDL操作，在事務提交，間隙鎖釋放之前，事務B也獲取到了間隙鎖（5,10]，這時兩個事務就處于死鎖狀態(tài)

案例三：等值查詢—唯一索引

步驟	事務A	事務B	事務C
1	begin; update u set d= d+ 1 where id = 7;	-	-
2	-	insert into u (8,8,8);	-
4	-	-	update set d = d+ 1 where id = 10

1.加鎖的范圍是（5,10]的范圍鎖
2.由于數(shù)據(jù)是等值查詢，并且表中最后數(shù)據(jù)id = 10 不滿足id= 7的查詢要求，故id=10 的行級鎖退化為間隙鎖，（5,10）
3.所以事務B中id=8會被鎖住，而id=10的時候不會被鎖住

案例四：等值查詢—普通索引

步驟	事務A	事務B	事務C
1	begin; select id form t where c = 5 lock in share mode;	-	-
2	-	update t set d = d + 1 where id = 5	-
4	-	-	insert into values (7,7,7)

1.加鎖的范圍是（0,5]，（5,10]的范圍鎖
2.由于c是普通索引，根據(jù)原則4，搜索到5后繼續(xù)向后遍歷直到搜索到10才放棄，故加鎖范圍為（5,10]
3.由于查詢是等值查詢，并且最后一個值不滿足查詢要求，故間隙鎖退化為（5,10）
4.因為加鎖是對普通索引c加鎖，而且因為索引覆蓋，沒有對主鍵進行加鎖，所以事務B執(zhí)行正常
5.因為加鎖范圍（5,10）故事務C執(zhí)行阻塞
6.需要注意的是，lock in share mode 因為覆蓋索引故沒有鎖主鍵索引，如果使用for update 程序會覺得之后會執(zhí)行更新操作故會將主鍵索引一同鎖住

案例五：范圍查詢—唯一索引

步驟	事務A	事務B	事務C
1	begin; select * form t where id >= 10 and id <11 for update	-	-
2	-	insert into values(8,8,8) insert into values(13,13,13)	-
4	-	-	update t set d = d+ 1 where id = 15

next-key lock 增加范圍鎖（5,10]
根據(jù)原則5，唯一索引的范圍查詢會到第一個不符合的值位置，故增加（10，15]
3.因為等值查詢有id =10 根據(jù)原則3間隙鎖升級為行鎖，故剩余鎖[10,15]
4.因為查詢并不是等值查詢，故[10,15]不會退化成[10,15)
5.故事務B（13,13,13）阻塞，事務C阻塞