鎖（case篇）每日速訊

2023-06-25 18:43:29 來源 : 博客園

case1（表鎖的讀-寫-讀阻塞）

上篇文檔中提到過

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.對于讀-寫-讀的情況，由于鎖的優(yōu)先級較高，如果申請寫的session遲遲獲取不到鎖，會阻塞后續(xù)其他session申請讀鎖；

先看正常情況，表鎖的讀鎖是可以加多個的，如下，通過兩個查詢命令也可以看到確實同時加上了，沒有阻塞；

(資料圖片僅供參考)

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

select * from performance_schema.metadata_locks;

show OPEN TABLES where In_use > 0;

但是在兩次讀中間插入一次寫鎖的獲取，后面的讀鎖也會同時被阻塞

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

實驗證明確實如文檔所說，原理還在研究中...

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

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

case3(next-key lock 和 primary key)

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

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

目前的數(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 "消息序號",  `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)建時間",  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT "修改時間",  `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é)果存在(id=15)

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

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]這部分，但是實驗表明，20這行不會加行鎖，所以最終表現(xiàn)為(15,20)；這是優(yōu)化2的體現(xiàn)；LOCK_MODE為S,GAP，我理解應(yīng)該是說只有間隙鎖，即（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);//會被阻塞select * from simple where id=20 for update ;//發(fā)現(xiàn)這行可以執(zhí)行成功

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

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

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

update simple set id=16 where id=20;

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

id>5

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

//console1start transaction ;select * from simple where id>5 lock in share mode ;select * from performance_schema.data_locks;//console2都會被阻塞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ū)別就是多了個等于5，那么5上是臨鍵鎖還是行鎖呢？我覺得是行鎖，因為優(yōu)化1，而且這樣和我們的認知也是比較符合的；實際看到確實是這樣；

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)該會直接鎖(23,super...)

case4(next-key lock和 unique key)

和case3唯一的區(qū)別就是將主鍵索引換成了唯一索引，猜測應(yīng)該是一模一樣的，因為文檔里的特殊規(guī)則說的也都是唯一索引，而沒有限制到主鍵上；

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

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

理想很美好，現(xiàn)實很骨感；這是什么？？突然想到行鎖和間隙鎖都是鎖在索引上的鎖，由于我查詢結(jié)果是所有字段，所以會發(fā)生回表查詢；當命中到唯一索引的時候會鎖一次，然后根據(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)該是因為回表操作，而unidx那行應(yīng)該則是對應(yīng)唯一索引的查詢，實際鎖的范圍邏輯和主鍵索引是一致的，只不過鎖的內(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;

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

uni>105

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

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

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

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是間隙鎖，這里是臨鍵鎖；為什么這里會鎖上呢？就很像是bug并沒有修復(fù)，依然鎖到了第一個不滿足條件的，并且加了臨鍵鎖

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都給鎖上了？？感覺bug依然存在，多鎖了一個區(qū)間

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

總結(jié)：對于唯一索引來說，因為存在主鍵，那么會產(chǎn)生回表操作，回表操作會給主鍵再加一把鎖；而那個bug依舊存在，只有主鍵索引的修復(fù)了，非主鍵唯一索引依然存在這個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之后，會給unidx加一個行鎖，因為沒有回表，所以主鍵上沒有鎖；那么console2能否成功執(zhí)行呢？

答案是

可以的；

我個人理解，是因為鎖是加在索引上的，而索引是列維度的，不是行維度的；console2執(zhí)行語句只會去判斷id這個索引上，有沒有5這個鎖；接下來我們反過來

//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;

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

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;

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

case6（next-key lock 和index）

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

seq=215

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

除了意向鎖，其他三個我們一個個看；seqidx(S)這行是普通索引執(zhí)行時加的臨鍵鎖，由于不是唯一索引，所以不能優(yōu)化（因為可能存在重復(fù)）primary(S,REC_NOT_GAP)這是回表操作帶來的seqidx(S,GAP)這行是因為不是唯一索引，所以在查詢到匹配的值之后不會立馬停止（因為后面可能還存在相同的值），所以必須要到不符合條件的值為止，而所有查詢過的都會加索引，所以存在一個間隙鎖。

seq=216

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

我理解，應(yīng)該是從205開始查，查到第一個不符合條件的值是215，加上中間沒有回表，所以就這一個鎖；理論應(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開始匹配，第一個不符合條件的是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ū)別就是不符合條件的會到223為止，另外中間因為匹配成功會回一次表seq>230和前面unidx>130和id>30都一樣

case7(next-key和沒有索引）

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

前面提到過，查詢條件匹配不到索引或者只是索引的一部分，這個時候為了保證數(shù)據(jù)的準確性，會給整個表“加鎖”，其實給表里所有的記錄都加鎖(這里我不知道描述的對不對，因為表鎖！=所有記錄加鎖，雖然效果相似，但并不是一個東西）.

同時因為這個表存在意向讀鎖，通過lock tables simple write 加寫的表鎖會沖突；