鎖（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申請讀鎖；

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

//console1
lock tables simple read;
//console2
lock tables simple read;

select * from performance_schema.metadata_locks;

show OPEN TABLES where In_use > 0;

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

//console1
lock tables simple read;
//console2
lock tables simple write;//被console1阻塞
//console3
lock 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;
//console2
start 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的，都會被阻塞

//console1
start 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<x<20，那么正常情況應該是(5,15]和(15,20]，然后20因為不等于會被優化（觸發了優化2)，所以是(5,20)

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<x<=20，那就會是(5,20]；
但是注意我們前面提到過一個bug，可是我們看到目前就是鎖到20為止，并不是(5,23)，翻看評論區說在MySQL 8.0.18 已經修復,而我的版本是8.0.33，這里難道是修復了嗎？先存疑，因為這里只能證明主鍵索引修復了，后面唯一索引那里還是亂的一批

id>30

應該會直接鎖(23,super...)

case4(next-key lock和 unique key)

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

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

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

理想很美好，現實很骨感；這是什么？？突然想到行鎖和間隙鎖都是鎖在索引上的鎖，由于我查詢結果是所有欄位，所以會發生回表查詢；當命中到唯一索引的時候會鎖一次，然后根據主鍵id再鎖一次；
但是現在我的uni和id欄位值是一樣的，所以為了區分，我將uni這一列都加了100，然后執行下面的句子

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

可以看到primary那行應該是因為回表操作，而unidx那行應該則是對應唯一索引的查詢，實際鎖的范圍邏輯和主鍵索引是一致的，只不過鎖的內容我不理解，lock_data為115,15，為什么？
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;

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

uni>105

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

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

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

uni>105 and uni<120

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

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

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依然存在，多鎖了一個區間

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

總結：對于唯一索引來說，因為存在主鍵，那么會產生回表操作，回表操作會給主鍵再加一把鎖；而那個bug依舊存在，只有主鍵索引的修復了，非主鍵唯一索引依然存在這個bug;

case5（索引加在哪）

//console1
start transaction ;
select id from simple where  uni=105 lock in share mode ;
select * from performance_schema.data_locks;
//console2
start transaction ;
update simple set name='new' where id=5;

現在我們已經清楚，執行完console1之后，會給unidx加一個行鎖，因為沒有回表，所以主鍵上沒有鎖；那么console2能否成功執行呢？

答案是

可以的；

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

//console1
start transaction ;
select * from simple where  id=5 lock in share mode ;
select * from performance_schema.data_locks;
//console2
start transaction ;
update simple set name='new' where uni=105;

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

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;

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

case6（next-key lock 和index）

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

seq=215

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

除了意向鎖，其他三個我們一個個看；
seqidx(S)這行是普通索引執行時加的臨鍵鎖，由于不是唯一索引，所以不能優化（因為可能存在重復）
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;

我理解，應該是從205開始查，查到第一個不符合條件的值是215，加上中間沒有回表，所以就這一個鎖；理論應該是(215,220]，但由于優化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;

這里和上面區別就是不符合條件的會到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;

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

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

參考檔案：

06 | 全域鎖和表鎖：給表加個欄位怎么有這么多阻礙？-極客時間
mysql MDL讀寫鎖阻塞，以及online ddl造成的“插隊”現象_花落的速度的博客-CSDN博客

本文來自博客園，作者：起司啊，轉載請注明原文鏈接：https://www.cnblogs.com/qisi/p/innodb_lock_case.html

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/555968.html

標籤：MySQL

上一篇：記一次字串末尾空白丟失的排查 → MySQL 是會玩的！

下一篇：返回列表