快捷導(dǎo)航

一文掌握MySQL唯一索引是如何加鎖的

更新時間：2024年06月11日 10:16:52 作者：qxlxi

這篇文章主要介紹了一文掌握MySQL唯一索引是如何加鎖的,本案例其實就是在主鍵索引上進行等值查詢,取決于查詢記錄是否存在,存在退化成記錄鎖,否則就是在索引樹中找到第一個大于該查詢記錄的記錄后,將改記錄的索引中的next-key lock退換成間隙鎖,需要的朋友可以參考下

數(shù)據(jù)準備

CREATE TABLE user (
id bigint NOT NULL AUTO_INCREMENT,
name varchar(30) COLLATE utf8mb4_unicode_ci NOT NULL,
age int NOT NULL,
PRIMARY KEY (id),
KEY index_age (age) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

在這里插入圖片描述

唯一索引等值查詢

本案例其實就是在主鍵索引上進行等值查詢，取決于查詢記錄是否存在，存在退化成記錄鎖，否則就是在索引樹中找到第一個大于該查詢記錄的記錄后，將改記錄的索引中的next-key lock退換成間隙鎖。

記錄存在

當執(zhí)行如下 id=1的鎖，會給id=1的記錄jiashangX型的記錄鎖

BEGIN;
SELECT * from user where id = 1 for update;

在這里插入圖片描述

可以發(fā)現(xiàn)對，id=1的記錄加了記錄鎖。update user set age = 25 where id = 1; DELETE FROM user WHERE id = 1; 語句都會被阻塞。

圖中 LOCK_TYPE 中的 RECORD 表示行級鎖，而不是記錄鎖的意思
通過 LOCK_MODE 可以確認是 next-key 鎖，還是間隙鎖，還是記錄鎖：
如果 LOCK_MODE 為 X，說明是next-key 鎖；
如果 LOCK_MODE 為 X, REC_NOT_GAP，說明是記錄鎖；
如果 LOCK_MODE 為 X,GAP，說明是間隙鎖；

這里簡單聊下，為什么唯一索引下等值查詢就可以將next-key lock退化成記錄鎖，因為對指定行的操作加鎖，可以直接避免其他事務(wù)對該行的刪除、插入的時候，就可以避免幻讀問題。

在這里插入圖片描述

記錄不存在

好了上面主要說的是針對記錄存在，針對記錄不存在的唯一索引添加鎖。

BEGIN;
SELECT * from user where id = 2 for update;

在這里插入圖片描述

當LOCK_MODE是間隙鎖或者Next-key LOCK時，LOCK_DATA代表的就是鎖的右邊界，鎖的左邊界就是表中id為5的上一條記錄的id值，也就是1，所以本次間隙鎖的范圍就是(1,5)。當執(zhí)行INSERT INTOtest.user (id, name, age) VALUES (3, 'zhangsan2', 21); 會獲取鎖失敗，阻塞。

這里由一個疑問就是為什么唯一索引記錄不存在的情況下，會從next-key lock退化成間隙鎖。
其實這種情況僅靠間隙鎖就能解決?；米x的問題。
為什么不可以針對不存在的記錄加記錄鎖，鎖是加在索引上的，記錄不存在，自然沒辦法鎖住這條不存在的記錄。

唯一索引范圍查詢

針對唯一索引范圍查詢，會對掃描到的索引加next-key鎖

大于或者大于等于的范圍查詢

情況1:針對大于等于的范圍查詢，存在等值條件，那么該記錄索引中的next-key 退化成記錄鎖。

BEGIN;
SELECT * from user where id > 15  for update;

在這里插入圖片描述

1.首先找到的是id=20這一行，然后對該(15,20] 添加間隙鎖。
2.由于是范圍查詢，innodb存儲引擎中，有特殊的記錄標識最后一條記錄。supremum pseudo-record 所以掃描第二行的時候加的是(20,+8]的next- key
當對 16 17 18 19 20 以及20以上的記錄進行刪除更新插入操作時，會被阻塞。

>=情況

BEGIN;
SELECT * from user where id >= 15  for update;

在這里插入圖片描述

從圖中可以看到加了三個鎖，由于可以定位到id=15這樣記錄，所以針對ID=15的記錄添加的就是記錄鎖，而接著掃描的就是20這行記錄，對(15,20] 加間隙鎖，(20,+8)加間隙鎖。

從本案例中可以獲取當大于等于時，如果有等于就會針對等于的記錄加記錄鎖。

小于或者小于等于的范圍查詢

BEGIN;
SELECT * from user where id < 6  for update;

在這里插入圖片描述

針對「小于或者小于等于」的唯一索引范圍查詢，如果條件值的記錄不在表中，那么不管是「小于」還是「小于等于」的范圍查詢，掃描到終止范圍查詢的記錄時，該記錄中索引的 next-key 鎖會退化成間隙鎖，其他掃描的記錄，則是在這些記錄的索引上加 next-key 鎖。

< 情況

BEGIN;
SELECT * from user where id < 5  for update;

在這里插入圖片描述

非唯一索引等值查詢

對非唯一索引進行等值查詢的時候，因為存在兩個索引，一個是主鍵索引，一個是二級索引。所以在加鎖時，同時對這兩個索引都加鎖。但是對主鍵索引加鎖的時候，只有滿足查詢條件的記錄才會對主鍵索引加鎖。
非唯一性二級索引、主鍵索引都加鎖
主鍵索引查詢只針對主鍵索引加鎖

沒有加索引的查詢

如果鎖定讀查詢語句，沒有使用索引列作為查詢條件，或者查詢語句沒有走索引查詢，導(dǎo)致掃描是全表掃描。那么，每一條記錄的索引上都會加 next-key 鎖，這樣就相當于鎖住的全表，這時如果其他事務(wù)對該表進行增、刪、改操作的時候，都會被阻塞。

update和delete語句如果查詢條件不加索引，掃描方式也是全表掃描，對每一條記錄加next-key 鎖，相當于鎖住的全表。

BEGIN;
SELECT * from user where name = 'qxlxi'  for update;
update user set age = age + 1 WHERE name = 'qxlxi';
DELETE FROM user WHERE name = 'qxlxi';
select * from performance_schema.data_locks;

在這里插入圖片描述

在線上在執(zhí)行 update、delete、select … for update 等具有加鎖性質(zhì)的語句，一定要檢查語句是否走了索引，如果是全表掃描的話，會對每一個索引加 next-key 鎖，相當于把整個表鎖住了

小結(jié)

本篇主要從實操方面介紹是如何針對不同的查詢條件進行加鎖。當查詢條件是主鍵索引、普通索引會出現(xiàn)各種不同的情況，但是總體上其實主要解決的就是通過next-key lock、gap lock，record lock，取避免可能出現(xiàn)幻讀的情況。

原則 1：加鎖的基本單位是 next-key lock，next-key lock 是前開后閉區(qū)間。
原則 2：查找過程中訪問到的對象才會加鎖。
優(yōu)化 1：索引上的等值查詢，給唯一索引加鎖的時候，next-key lock 退化為行鎖。
優(yōu)化 2：索引上的等值查詢，向右遍歷時且最后一個值不滿足等值條件的時候，next-key lock 退化為間隙鎖。
一個 bug：唯一索引上的范圍查詢會訪問到不滿足條件的第一個值為止。

到此這篇關(guān)于一文掌握MySQL唯一索引是如何加鎖的的文章就介紹到這了,更多相關(guān)MySQL唯一索引加鎖內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: