觀熱點：鎖（case篇）
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.對于讀-寫-讀的情況，由于鎖的優先級較高，如果申請寫的session遲遲獲取不到鎖，會阻塞后續其他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（元數據鎖讀-寫-讀）
mysql45講中提到的一個問題，具體分析見mysql MDL讀寫鎖阻塞，以及online ddl造成的“插隊”現象_花落的速度的博客-CSDN博客
case3(next-key lock 和 primary key)
在分析之前，先貼一下45講的總結,該總結版本是 5.x 系列 <=5.7.24，8.0 系列 <=8.0.13，而我測試的版本是8.0.33
原則 1：加鎖的基本單位是 next-key lock。希望你還記得，next-key lock 是前開后閉區間。原則 2：查找過程中訪問到的對象才會加鎖。優化 1：索引上的等值查詢，給唯一索引加鎖的時候，next-key lock 退化為行鎖。優化 2：索引上的等值查詢，向右遍歷時且最后一個值不滿足等值條件的時候，next-key lock 退化為間隙鎖。一個 bug：唯一索引上的范圍查詢會訪問到不滿足條件的第一個值為止。
目前的數據
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 "創建時間", `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="簡單測試表"單一查詢且查詢結果存在(id=15)
存在一個意向表鎖和行級讀鎖，理論上鎖住的應該是(5, 15]這部分，但是由于是主鍵索引（唯一），所以只會鎖15這一行，沒有必要鎖前面的間隙；這是優化1的體現；LOCK_MODE為S,REC_NOT_GAP，我理解應該是說只有行鎖，行鎖類型是讀鎖；
start transaction ;select * from simple where id = 15 lock in share mode ;select * from performance_schema.data_locks;單一查詢且結果不存在(id=16）
將查詢條件從15換成了16，理論上鎖住的是（15,20]這部分，但是實驗表明，20這行不會加行鎖，所以最終表現為(15,20)；這是優化2的體現；LOCK_MODE為S,GAP，我理解應該是說只有間隙鎖，即（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 ;//發現這行可以執行成功既然可以成功，那就證明id = 16 的查詢并沒有鎖20這一行，不然不可能加的上寫鎖
console2執行id=20后的結果
那這里如果我把id為20的更新成id為16會怎樣？
update simple set id=16 where id=20;
經實驗16-19都不能更新，20以后可以更，比如update simple set id=21 where id=20就可以成功；所以間隙鎖是不是也能防止更新；又或者說，其實是因為更新的本質是刪除再插入，再插入的被阻塞了，這里感興趣的可以研究一下；
id>5
按照理論，應該鎖住的后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
和上面的唯一區別就是多了個等于5，那么5上是臨鍵鎖還是行鎖呢？我覺得是行鎖，因為優化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 假如是5 應該會直接鎖(23,super...) 和case3唯一的區別就是將主鍵索引換成了唯一索引，猜測應該是一模一樣的，因為文檔里的特殊規則說的也都是唯一索引，而沒有限制到主鍵上； 理想很美好，現實很骨感；這是什么？？突然想到行鎖和間隙鎖都是鎖在索引上的鎖，由于我查詢結果是所有字段，所以會發生回表查詢；當命中到唯一索引的時候會鎖一次，然后根據主鍵id再鎖一次；但是現在我的uni和id字段值是一樣的，所以為了區分，我將uni這一列都加了100，然后執行下面的句子 可以看到primary那行應該是因為回表操作，而unidx那行應該則是對應唯一索引的查詢，實際鎖的范圍邏輯和主鍵索引是一致的，只不過鎖的內容我不理解，lock_data為115,15，為什么？ 由于查詢不到，所以也不會回表查詢，就不存在primary那行了 我理解到每個索引節點的時候，都會執行一次select * from simple where id = x；所以會多出幾行只有行鎖primary的記錄； 這里和上面不一樣的是，這里把120這行也鎖上了，主鍵索引鎖20是間隙鎖，這里是臨鍵鎖；為什么這里會鎖上呢？就很像是bug并沒有修復，依然鎖到了第一個不滿足條件的，并且加了臨鍵鎖 這里更離譜，這里為什么把123都給鎖上了？？感覺bug依然存在，多鎖了一個區間 總結：對于唯一索引來說，因為存在主鍵，那么會產生回表操作，回表操作會給主鍵再加一把鎖；而那個bug依舊存在，只有主鍵索引的修復了，非主鍵唯一索引依然存在這個bug; 現在我們已經清楚，執行完console1之后，會給unidx加一個行鎖，因為沒有回表，所以主鍵上沒有鎖；那么console2能否成功執行呢？ 我個人理解，是因為鎖是加在索引上的，而索引是列維度的，不是行維度的；console2執行語句只會去判斷id這個索引上，有沒有5這個鎖；接下來我們反過來 你試著一起敲一下就會發現，咦，console2怎么阻塞了呢？按上面所說的，不是不應該嗎？實際上console1的執行鎖的確實是id；但是你console2的執行，會回表啊，會嘗試給id加寫鎖，但是id已經加了讀鎖了，所以自然不行了；所以，不要盲目的只看查詢條件，要理解當前語句都會加什么鎖，是否和已經加的鎖沖突；最后，我們再來看一個附加題，下面兩個語句加的鎖是否一樣呢？ 在我沒有嘗試之前，我理解都沒有回表，那么就應該一個是唯一索引加讀鎖，一個是唯一索引加寫鎖；但是實際結果卻是lock in share mode是對的，for update會認為你要更新語句，自動給主鍵加鎖了 吸取uni的教訓，我給seq的值都加了200，現在這個表是這樣的 除了意向鎖，其他三個我們一個個看；seqidx(S)這行是普通索引執行時加的臨鍵鎖，由于不是唯一索引，所以不能優化（因為可能存在重復）primary(S,REC_NOT_GAP)這是回表操作帶來的seqidx(S,GAP)這行是因為不是唯一索引，所以在查詢到匹配的值之后不會立馬停止（因為后面可能還存在相同的值），所以必須要到不符合條件的值為止，而所有查詢過的都會加索引，所以存在一個間隙鎖。 我理解，應該是從205開始查，查到第一個不符合條件的值是215，加上中間沒有回表，所以就這一個鎖；理論應該是(215,220]，但由于優化2，所以退化為間隙鎖； 從215開始匹配，第一個不符合條件的是220，所以只能是（215,220] 這里和上面區別就是不符合條件的會到223為止，另外中間因為匹配成功會回一次表seq>230和前面unidx>130和id>30都一樣 前面提到過，查詢條件匹配不到索引或者只是索引的一部分，這個時候為了保證數據的準確性，會給整個表“加鎖”，其實給表里所有的記錄都加鎖(這里我不知道描述的對不對，因為表鎖！=所有記錄加鎖，雖然效果相似，但并不是一個東西）. 同時因為這個表存在意向讀鎖，通過lock tables simple write 加寫的表鎖會沖突； 06 | 全局鎖和表鎖 ：給表加個字段怎么有這么多阻礙？-極客時間mysql MDL讀寫鎖阻塞，以及online ddl造成的“插隊”現象_花落的速度的博客-CSDN博客 關鍵詞：
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
id>30
case4(next-key lock和 unique key)
單一查詢且查詢結果存在(uni=15)
start transaction ;select * from simple where uni = 15 lock in share mode ;select * from performance_schema.data_locks;start transaction ;select * from simple where uni = 115 lock in share mode ;select * from performance_schema.data_locks;select id from simple where uni = 115 lock in share mode ;而且如果我們查詢的不是select *,而是select id ,鎖的信息就不包含primary那行了；單一查詢且結果不存在(uni=116）
start transaction ;select * from simple where uni = 116 lock in share mode ;select * from performance_schema.data_locks;uni>105
start transaction ;select id from simple where simple.uni>105 lock in share mode ;select * from performance_schema.data_locks;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 ;//被阻塞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;uni>130和上面的id>30結果一樣，就不貼了
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;答案是
可以的；//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;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;case6（next-key lock 和index）
seq=215
start transaction ;select * from simple where seq=215 lock in share mode ;select * from performance_schema.data_locks;seq=216
start transaction ;select * from simple where seq=216 lock in share mode ;select * from performance_schema.data_locks;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;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;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;參考文檔：
相關文章
加大實體經濟支持力度 商業銀行“補血”忙 永續債發行提速
在加大實體經濟支持力度的背景下，銀行資本補充的需求旺盛。平安銀行2月21日-25日即將發行永續債，主要用于補充其他一級資本，將成為今年第
精彩推送
佳華科技(688051.SH)：布局的雙碳相關產品已在多個政企用戶端進入試用及功能驗證階段
格隆匯6月25日丨佳華科技(688051 SH)于2023年6月21日13：00-14：00召開
