腳本之家服務(wù)器常用軟件

快捷導(dǎo)航

mysql間隙鎖的具體使用

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

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

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

概述

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

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

mysq版本:8隔離級別：RR可重復(fù)讀

mysql的lockMode

查看鎖的活動信息

-- 當(dāng)前活動的鎖信息
SELECT * FROM performance_schema.data_locks;

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

X,GAP demo說明

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

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

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

在這里插入圖片描述

臨鍵鎖

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

-- 表結(jié)構(gòu)如下
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ū)間

在這里插入圖片描述

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

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

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

事務(wù)A

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

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

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

試驗結(jié)果如下

在這里插入圖片描述

-- 當(dāng)前活動的鎖信息
SELECT * FROM performance_schema.data_locks;

在這里插入圖片描述

案例

案例一：間隙鎖簡單案例

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

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

案例二：間隙鎖死鎖問題

步驟	事務(wù)A	事務(wù)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)	-

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

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

步驟	事務(wù)A	事務(wù)B	事務(wù)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.所以事務(wù)B中id=8會被鎖住，而id=10的時候不會被鎖住

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

步驟	事務(wù)A	事務(wù)B	事務(wù)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加鎖，而且因為索引覆蓋，沒有對主鍵進(jìn)行加鎖，所以事務(wù)B執(zhí)行正常
5.因為加鎖范圍（5,10）故事務(wù)C執(zhí)行阻塞
6.需要注意的是，lock in share mode 因為覆蓋索引故沒有鎖主鍵索引，如果使用for update 程序會覺得之后會執(zhí)行更新操作故會將主鍵索引一同鎖住

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

步驟	事務(wù)A	事務(wù)B	事務(wù)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.故事務(wù)B（13,13,13）阻塞，事務(wù)C阻塞