鎖（case篇）

来源:https://www.cnblogs.com/qisi/archive/2023/06/25/innodb_lock_case.html
-Advertisement-
Play Games

## case1（表鎖的讀-寫-讀阻塞） 上篇文檔中提到過 >WRITE locks normally have higher priority than READ locks to ensure that updates are processed as soon as possible. Thi ...


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;

image

show OPEN TABLES where In_use > 0;
image

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

//console1
lock tables simple read;
//console2
lock tables simple write;//被console1阻塞
//console3
lock tables simple read;//被console2阻塞

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

case2（元數據鎖讀-寫-讀）

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

case3(next-key lock 和 primary key)

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

原則 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='簡單測試表'

image

單一查詢且查詢結果存在(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;

image

單一查詢且結果不存在(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這一行，不然不可能加的上寫鎖

image

console2執行id=20後的結果

image

那這裡如果我把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);

image

id>=5

和上面的唯一區別就是多了個等於5，那麼5上是臨鍵鎖還是行鎖呢？我覺得是行鎖，因為優化1，而且這樣和我們的認知也是比較符合的；
實際看到確實是這樣；

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

image

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;

image

id>5 and id<=20

假如是5<x<=20，那就會是(5,20]；
但是註意我們前面提到過一個bug，可是我們看到目前就是鎖到20為止，並不是(5,23)，翻看評論區說在MySQL 8.0.18 已經修複,而我的版本是8.0.33，這裡難道是修複了嗎？先存疑，因為這裡只能證明主鍵索引修複了，後面唯一索引那裡還是亂的一批
image

id>30

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

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;

image

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

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

image

可以看到primary那行應該是因為回表操作，而unidx那行應該則是對應唯一索引的查詢，實際鎖的範圍邏輯和主鍵索引是一致的，只不過鎖的內容我不理解，lock_data為115,15，為什麼？
select id from simple where uni = 115 lock in share mode ;
而且如果我們查詢的不是select *,而是select id ,鎖的信息就不包含primary那行了；
image

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

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

image

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

uni>105

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

image

我理解到每個索引節點的時候，都會執行一次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 ;//被阻塞

image

這裡和上面不一樣的是，這裡把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;

image

這裡更離譜，這裡為什麼把123都給鎖上了？？感覺bug依然存在，多鎖了一個區間

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

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

case5（索引加在哪）

image

//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會認為你要更新語句，自動給主鍵加鎖了
image

case6（next-key lock 和index）

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

seq=215

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

image

除了意向鎖，其他三個我們一個個看；
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;

image

我理解，應該是從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;

image

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

image

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

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

image

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

參考文檔：

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

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


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • 一天吃透MySQL面試八股文
    > 內容摘自我的學習網站：topjavaer.cn ## 什麼是MySQL MySQL是一個關係型資料庫，它採用表的形式來存儲數據。你可以理解成是Excel表格，既然是表的形式存儲數據，就有表結構（行和列）。行代表每一行數據，列代表該行中的每個值。列上的值是有數據類型的，比如：整數、字元串、日期等等 ...
  • 小試Blazor——實現Ant Design Blazor動態表單
    大家好，我是沙漠盡頭的狼。 網站使用Blazor重構上線一天了，用Blazor開發是真便捷，空閑時間查查gpt和github，又上線一個 [正則表達式線上驗證工具](https://dotnet9.com/tools/regextester) 和幾個線上小游戲，比如 [井字棋游戲](https:// ...
  • WPF 入門筆記 - 06 - 命令
    在`WPF`中，命令是一種用於處理用戶交互操作的機制。它將操作行為與界面元素解耦，使得界面元素可以通過命令進行觸發和執行相應的邏輯。`WPF`中的命令模型通過`ICommand`介面和相關的實現類來實現。命令模式的設計思想是將命令的發送者（例如按鈕）與命令的執行者（例如視圖模型中的方法）解耦，使得它... ...
  • centos7-datax和datax-web安裝以及安裝中問題的解決
    問題應該算挺常見的但是一句話還挺難說清楚，所以百度特別難搜。 場景就是，有一堆以員工名稱命名的文件（名稱可能還有字母數字等前尾碼），現在給定一個員工清單，需要從這些文件中篩選出員工清單上列出的員工的文件，並複製到另外一個目錄中。 輸入： 1. 許多文件名包含員工名稱的文件 2.一個清單文件，裡面包含 ...
  • ps插件Camera Raw新增功能讓您的智能修圖更簡單~
    哈嘍大家好，我是鹹魚 今天跟大家分享一個關於 zabbix Timeout 值設置不當導致的問題，這個問題不知道大家有沒有碰到過 ## 問題 事情經過是這樣的： 把某一臺 zabbix agent 的模板由原來的 `Template OS Windows by Zabbix agent` 換成了 ` ...
  • Spark簡介
    # Spark Spark是一種快速、通用、可擴展的大數據分析引擎，2009年誕生於加州大學伯克利分校AMPLab，2010年開源，2013年6月成為Apache孵化項目，2014年2月成為Apache的頂級項目，2014年5月發佈spark1.0，2016年7月發佈spark2.0，2020年6月 ...
  • SeaTunnel 發佈成為 Apache 頂級項目後首個版本 2.3.2，進一步提高 Zeta 引擎穩定性和易用性
    ![file](https://img2023.cnblogs.com/other/3195851/202306/3195851-20230625185718639-2144905227.jpg) > 近日，Apache SeaTunnel 正式發佈 2.3.2 版本。此時距離上一版本 2.3.1 ...
  • 構建高可用性的 SQL Server：Docker 容器下的主從同步實現
    摘要：本文將介紹如何在 Docker 環境下搭建 MS SQL Server 的主從同步，幫助讀者瞭解主從同步的原理和實現方式，進而提高數據的可靠性和穩定性。 一、前言 在當今信息化的時代，數據的安全性和穩定性顯得尤為重要。資料庫是許多企業和組織存儲和管理數據的核心，因此如何保證資料庫的高可用性和數 ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類