首先我們要明確，加鎖的對(duì)象是索引，加鎖的基本單位是next-key lock，由記錄鎖和間隙鎖組成。next-key是前開(kāi)后閉區(qū)間，間隙鎖是前開(kāi)后開(kāi)區(qū)間。根據(jù)不同的查詢條件next-key 可能會(huì)退化成記錄鎖或間隙鎖。

在能使用記錄鎖或者間隙鎖就能避免幻讀現(xiàn)象的場(chǎng)景下， next-key lock 就會(huì)退化成記錄鎖或間隙鎖。

數(shù)據(jù)準(zhǔn)備

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;

唯一索引等值查詢

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

記錄存在

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

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

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

圖中 LOCK_TYPE 中的 RECORD 表示行級(jí)鎖，而不是記錄鎖的意思
通過(guò) LOCK_MODE 可以確認(rèn)是 next-key 鎖，還是間隙鎖，還是記錄鎖：
如果 LOCK_MODE 為 X，說(shuō)明是next-key 鎖；
如果 LOCK_MODE 為 X, REC_NOT_GAP，說(shuō)明是記錄鎖；
如果 LOCK_MODE 為 X,GAP，說(shuō)明是間隙鎖；

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

記錄不存在

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

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

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

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

唯一索引范圍查詢

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

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

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

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

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

>=情況

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

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

從本案例中可以獲取當(dāng)大于等于時(shí)，如果有等于就會(huì)針對(duì)等于的記錄加記錄鎖。

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

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

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

< 情況

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

非唯一索引等值查詢

對(duì)非唯一索引進(jìn)行等值查詢的時(shí)候，因?yàn)榇嬖趦蓚€(gè)索引，一個(gè)是主鍵索引，一個(gè)是二級(jí)索引。所以在加鎖時(shí)，同時(shí)對(duì)這兩個(gè)索引都加鎖。但是對(duì)主鍵索引加鎖的時(shí)候，只有滿足查詢條件的記錄才會(huì)對(duì)主鍵索引加鎖。
非唯一性 二級(jí)索引、主鍵索引都加鎖
主鍵索引查詢 只針對(duì)主鍵索引加鎖

沒(méi)有加索引的查詢

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

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

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ì)的語(yǔ)句，一定要檢查語(yǔ)句是否走了索引，如果是全表掃描的話，會(huì)對(duì)每一個(gè)索引加 next-key 鎖，相當(dāng)于把整個(gè)表鎖住了

小結(jié)

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

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

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

最新評(píng)論