鎖（case篇）每日速訊

2023-06-25 18:43:29 來(lái)源 : 博客園

case1（表鎖的讀-寫(xiě)-讀阻塞）

上篇文檔中提到過(guò)

WRITE locks normally have higher priority than READ locks to ensure that updates are processed as soon as possible. This means that if one session obtains a READ lock and then another session requests a WRITE lock, subsequent READ lock requests wait until the session that requested the WRITE lock has obtained the lock and released it.對(duì)于讀-寫(xiě)-讀的情況，由于鎖的優(yōu)先級(jí)較高，如果申請(qǐng)寫(xiě)的session遲遲獲取不到鎖，會(huì)阻塞后續(xù)其他session申請(qǐng)讀鎖；

先看正常情況，表鎖的讀鎖是可以加多個(gè)的，如下，通過(guò)兩個(gè)查詢命令也可以看到確實(shí)同時(shí)加上了，沒(méi)有阻塞；

(資料圖片僅供參考)

//console1lock tables simple read;//console2lock tables simple read;

select * from performance_schema.metadata_locks;

show OPEN TABLES where In_use > 0;

但是在兩次讀中間插入一次寫(xiě)鎖的獲取，后面的讀鎖也會(huì)同時(shí)被阻塞

//console1lock tables simple read;//console2lock tables simple write;//被console1阻塞//console3lock tables simple read;//被console2阻塞

實(shí)驗(yàn)證明確實(shí)如文檔所說(shuō)，原理還在研究中...

case2（元數(shù)據(jù)鎖讀-寫(xiě)-讀）

mysql45講中提到的一個(gè)問(wèn)題，具體分析見(jiàn)mysql MDL讀寫(xiě)鎖阻塞，以及online ddl造成的“插隊(duì)”現(xiàn)象_花落的速度的博客-CSDN博客

case3(next-key lock 和 primary key)

在分析之前，先貼一下45講的總結(jié),該總結(jié)版本是 5.x 系列 <=5.7.24，8.0 系列 <=8.0.13，而我測(cè)試的版本是8.0.33

原則 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è)值為止。

目前的數(shù)據(jù)

CREATE TABLE `simple` (  `id` bigint NOT NULL AUTO_INCREMENT COMMENT "主鍵",  `name` varchar(256) COLLATE utf8mb4_bin DEFAULT NULL COMMENT "字符",  `seq` bigint NOT NULL COMMENT "消息序號(hào)",  `type` tinyint NOT NULL COMMENT "類型，tinyint值",  `version` int NOT NULL DEFAULT "1" COMMENT "版本值",  `msg` text COLLATE utf8mb4_bin COMMENT "消息",  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT "創(chuàng)建時(shí)間",  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT "修改時(shí)間",  `yn` tinyint NOT NULL DEFAULT "1" COMMENT "是否有效",  `uni` int NOT NULL COMMENT "唯一索引",  PRIMARY KEY (`id`),  UNIQUE KEY `unidx` (`uni`),  KEY `seqidx` (`seq`)) ENGINE=InnoDB AUTO_INCREMENT=301 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT="簡(jiǎn)單測(cè)試表"

單一查詢且查詢結(jié)果存在(id=15)

存在一個(gè)意向表鎖和行級(jí)讀鎖，理論上鎖住的應(yīng)該是(5, 15]這部分，但是由于是主鍵索引（唯一），所以只會(huì)鎖15這一行，沒(méi)有必要鎖前面的間隙；這是優(yōu)化1的體現(xiàn)；LOCK_MODE為S,REC_NOT_GAP，我理解應(yīng)該是說(shuō)只有行鎖，行鎖類型是讀鎖；

start transaction ;select * from simple where id = 15 lock in share mode ;select * from performance_schema.data_locks;

單一查詢且結(jié)果不存在(id=16）

將查詢條件從15換成了16，理論上鎖住的是（15,20]這部分，但是實(shí)驗(yàn)表明，20這行不會(huì)加行鎖，所以最終表現(xiàn)為(15,20)；這是優(yōu)化2的體現(xiàn)；LOCK_MODE為S,GAP，我理解應(yīng)該是說(shuō)只有間隙鎖，即（15,20）；

start transaction ;select * from simple where id = 16 lock in share mode ;select * from performance_schema.data_locks;//console2start transaction;insert into simple (id,name,type,seq) value (16,5,5,5);//會(huì)被阻塞select * from simple where id=20 for update ;//發(fā)現(xiàn)這行可以執(zhí)行成功

既然可以成功，那就證明id = 16 的查詢并沒(méi)有鎖20這一行，不然不可能加的上寫(xiě)鎖

console2執(zhí)行id=20后的結(jié)果

那這里如果我把id為20的更新成id為16會(huì)怎樣？

update simple set id=16 where id=20;

經(jīng)實(shí)驗(yàn)16-19都不能更新，20以后可以更，比如update simple set id=21 where id=20就可以成功；所以間隙鎖是不是也能防止更新；又或者說(shuō)，其實(shí)是因?yàn)楦碌谋举|(zhì)是刪除再插入，再插入的被阻塞了，這里感興趣的可以研究一下；

id>5

按照理論，應(yīng)該鎖住的后5往后的所有范圍，即(5,15],(15,20],(20,23],(23,super..];所以我推測(cè)LOCK_MODE只有一個(gè)S，代表加的是臨鍵鎖，類型是讀鎖，沒(méi)有特殊表明缺少行鎖或者間隙鎖就是完整的臨建鎖，并且我在console2嘗試插入id為6或者36的，都會(huì)被阻塞

//console1start transaction ;select * from simple where id>5 lock in share mode ;select * from performance_schema.data_locks;//console2都會(huì)被阻塞insert into simple (id,name,type,seq) value (6,5,5,5);insert into simple (id,name,type,seq) value (36,5,5,5);

id>=5

和上面的唯一區(qū)別就是多了個(gè)等于5，那么5上是臨鍵鎖還是行鎖呢？我覺(jué)得是行鎖，因?yàn)閮?yōu)化1，而且這樣和我們的認(rèn)知也是比較符合的；實(shí)際看到確實(shí)是這樣；

start transaction ;select * from simple where id>=5 lock in share mode ;select * from performance_schema.data_locks;

id>5 and id<20

首先5

start transaction ;select * from simple where id>5 and id<20 lock in share mode ;select * from performance_schema.data_locks;

id>5 and id<=20

假如是5

id>30

應(yīng)該會(huì)直接鎖(23,super...)

case4(next-key lock和 unique key)

和case3唯一的區(qū)別就是將主鍵索引換成了唯一索引，猜測(cè)應(yīng)該是一模一樣的，因?yàn)槲臋n里的特殊規(guī)則說(shuō)的也都是唯一索引，而沒(méi)有限制到主鍵上；

單一查詢且查詢結(jié)果存在(uni=15)

start transaction ;select * from simple where uni = 15 lock in share mode ;select * from performance_schema.data_locks;

理想很美好，現(xiàn)實(shí)很骨感；這是什么？？突然想到行鎖和間隙鎖都是鎖在索引上的鎖，由于我查詢結(jié)果是所有字段，所以會(huì)發(fā)生回表查詢；當(dāng)命中到唯一索引的時(shí)候會(huì)鎖一次，然后根據(jù)主鍵id再鎖一次；但是現(xiàn)在我的uni和id字段值是一樣的，所以為了區(qū)分，我將uni這一列都加了100，然后執(zhí)行下面的句子

start transaction ;select * from simple where uni = 115 lock in share mode ;select * from performance_schema.data_locks;

可以看到primary那行應(yīng)該是因?yàn)榛乇聿僮?，而unidx那行應(yīng)該則是對(duì)應(yīng)唯一索引的查詢，實(shí)際鎖的范圍邏輯和主鍵索引是一致的，只不過(guò)鎖的內(nèi)容我不理解，lock_data為115,15，為什么？select id from simple where uni = 115 lock in share mode ;而且如果我們查詢的不是select *,而是select id ,鎖的信息就不包含primary那行了；

單一查詢且結(jié)果不存在(uni=116）

start transaction ;select * from simple where uni = 116 lock in share mode ;select * from performance_schema.data_locks;

由于查詢不到，所以也不會(huì)回表查詢，就不存在primary那行了

uni>105

start transaction ;select id from simple where simple.uni>105 lock in share mode ;select * from performance_schema.data_locks;

我理解到每個(gè)索引節(jié)點(diǎn)的時(shí)候，都會(huì)執(zhí)行一次select * from simple where id = x；所以會(huì)多出幾行只有行鎖primary的記錄；

uni>=105只是會(huì)在unidx和primary上各多一個(gè)鎖，但范圍和唯一索引邏輯依然一致，就不貼了

uni>105 and uni<120

//console1commit ;start transaction ;select * from simple where uni>105 and uni<120 lock in share mode ;select * from performance_schema.data_locks;//console2select * from simple where uni=120 for update ;//被阻塞

這里和上面不一樣的是，這里把120這行也鎖上了，主鍵索引鎖20是間隙鎖，這里是臨鍵鎖；為什么這里會(huì)鎖上呢？就很像是bug并沒(méi)有修復(fù)，依然鎖到了第一個(gè)不滿足條件的，并且加了臨鍵鎖

uni>105 and uni<=120

start transaction ;select * from simple where uni>105 and uni<=120 lock in share mode ;select * from performance_schema.data_locks;

這里更離譜，這里為什么把123都給鎖上了？？感覺(jué)bug依然存在，多鎖了一個(gè)區(qū)間

uni>130和上面的id>30結(jié)果一樣，就不貼了

總結(jié)：對(duì)于唯一索引來(lái)說(shuō)，因?yàn)榇嬖谥麈I，那么會(huì)產(chǎn)生回表操作，回表操作會(huì)給主鍵再加一把鎖；而那個(gè)bug依舊存在，只有主鍵索引的修復(fù)了，非主鍵唯一索引依然存在這個(gè)bug;

case5（索引加在哪）

//console1start transaction ;select id from simple where  uni=105 lock in share mode ;select * from performance_schema.data_locks;//console2start transaction ;update simple set name="new" where id=5;

現(xiàn)在我們已經(jīng)清楚，執(zhí)行完console1之后，會(huì)給unidx加一個(gè)行鎖，因?yàn)闆](méi)有回表，所以主鍵上沒(méi)有鎖；那么console2能否成功執(zhí)行呢？

答案是

可以的；

我個(gè)人理解，是因?yàn)殒i是加在索引上的，而索引是列維度的，不是行維度的；console2執(zhí)行語(yǔ)句只會(huì)去判斷id這個(gè)索引上，有沒(méi)有5這個(gè)鎖；接下來(lái)我們反過(guò)來(lái)

//console1start transaction ;select * from simple where  id=5 lock in share mode ;select * from performance_schema.data_locks;//console2start transaction ;update simple set name="new" where uni=105;

你試著一起敲一下就會(huì)發(fā)現(xiàn)，咦，console2怎么阻塞了呢？按上面所說(shuō)的，不是不應(yīng)該嗎？實(shí)際上console1的執(zhí)行鎖的確實(shí)是id；但是你console2的執(zhí)行，會(huì)回表啊，會(huì)嘗試給id加寫(xiě)鎖，但是id已經(jīng)加了讀鎖了，所以自然不行了；所以，不要盲目的只看查詢條件，要理解當(dāng)前語(yǔ)句都會(huì)加什么鎖，是否和已經(jīng)加的鎖沖突；最后，我們?cè)賮?lái)看一個(gè)附加題，下面兩個(gè)語(yǔ)句加的鎖是否一樣呢？

start transaction ;select id from simple where  uni=105 lock in share mode ;select * from performance_schema.data_locks;start transaction ;select id from simple where  uni=105 for update ;select * from performance_schema.data_locks;

在我沒(méi)有嘗試之前，我理解都沒(méi)有回表，那么就應(yīng)該一個(gè)是唯一索引加讀鎖，一個(gè)是唯一索引加寫(xiě)鎖；但是實(shí)際結(jié)果卻是lock in share mode是對(duì)的，for update會(huì)認(rèn)為你要更新語(yǔ)句，自動(dòng)給主鍵加鎖了

case6（next-key lock 和index）

吸取uni的教訓(xùn)，我給seq的值都加了200，現(xiàn)在這個(gè)表是這樣的

seq=215

start transaction ;select * from simple where  seq=215 lock in share mode ;select * from performance_schema.data_locks;

除了意向鎖，其他三個(gè)我們一個(gè)個(gè)看；seqidx(S)這行是普通索引執(zhí)行時(shí)加的臨鍵鎖，由于不是唯一索引，所以不能優(yōu)化（因?yàn)榭赡艽嬖谥貜?fù)）primary(S,REC_NOT_GAP)這是回表操作帶來(lái)的seqidx(S,GAP)這行是因?yàn)椴皇俏ㄒ凰饕?，所以在查詢到匹配的值之后不?huì)立馬停止（因?yàn)楹竺婵赡苓€存在相同的值），所以必須要到不符合條件的值為止，而所有查詢過(guò)的都會(huì)加索引，所以存在一個(gè)間隙鎖。

seq=216

start transaction ;select * from simple where  seq=216 lock in share mode ;select * from performance_schema.data_locks;

我理解，應(yīng)該是從205開(kāi)始查，查到第一個(gè)不符合條件的值是215，加上中間沒(méi)有回表，所以就這一個(gè)鎖；理論應(yīng)該是(215,220]，但由于優(yōu)化2，所以退化為間隙鎖；

seq>215 and seq<220

start transaction ;select * from simple where  seq>215 and seq <220 lock in share mode ;select * from performance_schema.data_locks;

從215開(kāi)始匹配，第一個(gè)不符合條件的是220，所以只能是（215,220]

seq>215 and seq <=220

start transaction ;select * from simple where  seq>215 and seq <=220 lock in share mode ;select * from performance_schema.data_locks;

這里和上面區(qū)別就是不符合條件的會(huì)到223為止，另外中間因?yàn)槠ヅ涑晒?huì)回一次表seq>230和前面unidx>130和id>30都一樣

case7(next-key和沒(méi)有索引）

alter table simple drop index  seqidx;start transaction ;select * from simple where  seq=215 lock in share mode ;select * from performance_schema.data_locks;

前面提到過(guò)，查詢條件匹配不到索引或者只是索引的一部分，這個(gè)時(shí)候?yàn)榱吮ＷC數(shù)據(jù)的準(zhǔn)確性，會(huì)給整個(gè)表“加鎖”，其實(shí)給表里所有的記錄都加鎖(這里我不知道描述的對(duì)不對(duì)，因?yàn)楸礞i！=所有記錄加鎖，雖然效果相似，但并不是一個(gè)東西）.

同時(shí)因?yàn)檫@個(gè)表存在意向讀鎖，通過(guò)lock tables simple write 加寫(xiě)的表鎖會(huì)沖突；