概述

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

在使用非唯一索引更新時(shí)，會遇到臨鍵鎖（范圍鎖）；
臨鍵鎖和表中的數(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ù)（索引項(xiàng)）添加了行級排他鎖;
• IX：意向排它鎖。上述案例中，給表添加了一個(gè)意向排它鎖。當(dāng)其他事務(wù)要對全表的數(shù)據(jù)進(jìn)行加鎖時(shí)，那么就不需要判斷每一條數(shù)據(jù)是否被加鎖了。
• X,GAP:X代表排他鎖；GAP代表間隙鎖（前開后開,即當(dāng)前的lock_data中的索引值對應(yīng)的id到最近的上一個(gè)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索引加了一個(gè)排他間隙鎖，鎖住了id范圍為(3,5)時(shí)間的間隙;

綜上獲得了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='用戶表';

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

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

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

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

事務(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);

試驗(yàn)結(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時(shí)，事務(wù)A會對數(shù)據(jù)庫表增加（10，15]這個(gè)區(qū)間鎖，這時(shí)insert id = 12 的數(shù)據(jù)的時(shí)候就會因?yàn)閰^(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)	-

不同于寫鎖相互之間是互斥的原則，間隙鎖之間不是互斥的，如果一個(gè)事務(wù)A獲取到了（5,10]之間的間隙鎖，另一個(gè)事務(wù)B也可以獲取到（5,10]之間的間隙鎖。這時(shí)就可能會發(fā)生死鎖問題，如下案例。
事務(wù)A獲取到（5,10]之間的間隙鎖不允許其他的DDL操作，在事務(wù)提交，間隙鎖釋放之前，事務(wù)B也獲取到了間隙鎖（5,10]，這時(shí)兩個(gè)事務(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的時(shí)候不會被鎖住

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

步驟	事務(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.由于查詢是等值查詢，并且最后一個(gè)值不滿足查詢要求，故間隙鎖退化為（5,10）
4.因?yàn)榧渔i是對普通索引c加鎖，而且因?yàn)樗饕采w，沒有對主鍵進(jìn)行加鎖，所以事務(wù)B執(zhí)行正常
5.因?yàn)榧渔i范圍（5,10）故事務(wù)C執(zhí)行阻塞
6.需要注意的是，lock in share mode 因?yàn)楦采w索引故沒有鎖主鍵索引，如果使用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，唯一索引的范圍查詢會到第一個(gè)不符合的值位置，故增加（10，15]
  3.因?yàn)榈戎挡樵冇衖d =10 根據(jù)原則3間隙鎖升級為行鎖，故剩余鎖[10,15]
  4.因?yàn)椴樵儾⒉皇堑戎挡樵?，故[10,15]不會退化成[10,15)
  5.故事務(wù)B（13,13,13）阻塞，事務(wù)C阻塞

案例六： 范圍查詢—普通索引

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

• next-key lock 增加范圍鎖（5,10]，（10，15]
  2.因?yàn)閏是非唯一索引，故（5,10]不會退化為10
  3.因?yàn)椴樵儾⒉皇堑戎挡樵儯蔥10,15]不會退化成[10,15)
  4.所以事務(wù)B和事務(wù)C全部堵塞

案例八： 普通索引-等值問題

上面的數(shù)據(jù)增加一行（30,10,30），這樣在數(shù)據(jù)庫中存在的c=10的就有兩條記錄

步驟	事務(wù)A	事務(wù)B	事務(wù)C
1	begin; delete from t where c = 10	-	-
2	-	insert into values(12,12,12)	-
4	-	-	update t set d = d+ 1 where c = 15

• next-key lock 增加范圍鎖（5,10]，（10，15]
  2.因?yàn)槭堑戎挡樵児释嘶癁椋?,10]，（10，15），故事務(wù)B阻塞，事務(wù)C執(zhí)行成功
  加鎖的范圍如下圖

案例九： 普通索引-等值Limit問題

步驟	事務(wù)A	事務(wù)B	事務(wù)C
1	begin; delete from t where c = 10 limit 2	-	-
2	-	insert into values(12,12,12)	-
4	-	-	update t set d = d+ 1 where c = 15

1.根據(jù)上面案例8改造，將delete增加limit操作2的操作
2.因?yàn)橹懒藬?shù)據(jù)加鎖值加2條，故在加鎖（5，10]之后發(fā)現(xiàn)已經(jīng)有兩條數(shù)據(jù)，故后面不在向后匹配加鎖。所以事務(wù)B執(zhí)行成功，加鎖范圍如下

 到此這篇關(guān)于mysql間隙鎖的具體使用的文章就介紹到這了,更多相關(guān)mysql間隙鎖內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論