电脑知识|欧美黑人一区二区三区|软件|欧美黑人一级爽快片淫片高清|系统|欧美黑人狂野猛交老妇|数据库|服务器|编程开发|网络运营|知识问答|技术教程文章 - 好吧啦网

您的位置:首頁技術文章
文章詳情頁

淺談MySQL next-key lock 加鎖范圍

瀏覽:77日期:2023-10-02 08:12:56
前言

某天,突然被問到 MySQL 的 next-key lock,我瞬間的反應就是:

淺談MySQL next-key lock 加鎖范圍

這都是啥啥啥???

淺談MySQL next-key lock 加鎖范圍

這一個截圖我啥也看不出來呀?

仔細一看,好像似曾相識,這不是《MySQL 45 講》里面的內容么?

什么是 next-key lock

A next-key lock is a combination of a record lock on the index record and a gap lock on the gap before the index record.

官網的解釋大概意思就是:next-key 鎖是索引記錄上的記錄鎖和索引記錄之前的間隙上的間隙鎖的組合。

先給自己來一串小問號???

在主鍵、唯一索引、普通索引以及普通字段上加鎖,是鎖住了哪些索引? 不同的查詢條件,分別鎖住了哪些范圍的數據? for share 和 for update 等值查詢和范圍查詢的鎖范圍? 當查詢的等值不存在時,鎖范圍是什么? 當查詢條件分別是主鍵、唯一索引、普通索引時有什么區別?

淺談MySQL next-key lock 加鎖范圍

既然啥都不懂,那只好從頭開始操作實踐一把了!

先看看看 《MySQL 45 講》中丁奇老師的結論:

淺談MySQL next-key lock 加鎖范圍

看了這結論,應該可以解答一大部分問題,不過有一句非常非常重點的話需要關注:MySQL 后面的版本可能會改變加鎖策略,所以這個規則只限于截止到現在的最新版本,即 5.x 系列<=5.7.24,8.0 系列 <=8.0.13

所以,以上的規則,對現在的版本并不一定適用,下面我以 MySQL 8.0.25 版本為例,進行多角度驗證 next-key lock 加鎖范圍。

環境準備

MySQL 版本:8.0.25

隔離級別:可重復讀(RR)

存儲引擎:InnoDB

mysql> select @@global.transaction_isolation,@@transaction_isolationGmysql> show create table tG

淺談MySQL next-key lock 加鎖范圍

如何使用 Docker 安裝 MySQL,可以參考另一篇文章《使用 Docker 安裝并連接 MySQL》

主鍵索引

首先來驗證主鍵索引的 next-key lock 的范圍

淺談MySQL next-key lock 加鎖范圍

此時數據庫的數據如圖所示,對主鍵索引來說此時數據間隙如下:

淺談MySQL next-key lock 加鎖范圍

主鍵等值查詢 —— 數據存在

mysql> begin; select * from t where id = 10 for update;

這條 SQL,對 id = 10 進行加鎖,可以先思考一下加了什么鎖?鎖住了什么數據?

可以通過 data_locks 查看鎖信息,SQL 如下:

# mysql> select * from performance_schema.data_locks;mysql> select * from performance_schema.data_locksG

具體字段含義可以參考 官方文檔

淺談MySQL next-key lock 加鎖范圍

結果主要包含引擎、庫、表等信息,咱們需要重點關注以下幾個字段:

INDEX_NAME:鎖定索引的名稱 LOCK_TYPE:鎖的類型,對于 InnoDB,允許的值為 RECORD 行級鎖 和 TABLE 表級鎖。 LOCK_MODE:鎖的類型:S, X, IS, IX, and gap locks LOCK_DATA:鎖關聯的數據,對于 InnoDB,當 LOCK_TYPE 是 RECORD(行鎖),則顯示值。當鎖在主鍵索引上時,則值是鎖定記錄的主鍵值。當鎖是在輔助索引上時,則顯示輔助索引的值,并附加上主鍵值。

結果很明顯,這里是對表添加了一個 IX 鎖 并對主鍵索引 id = 10 的記錄,添加了一個 X,REC_NOT_GAP 鎖,表示只鎖定了記錄。

同樣 for share 是對表添加了一個 IS 鎖并對主鍵索引 id = 10 的記錄,添加了一個 S 鎖。

可以得出結論:

對主鍵等值加鎖,且值存在時,會對表添加意向鎖,同時會對主鍵索引添加行鎖。

主鍵等值查詢 —— 數據不存在

mysql> select * from t where id = 11 for update;

如果是數據不存在的時候,會加什么鎖呢?鎖的范圍又是什么?

在驗證之前,分析一下數據的間隙。

淺談MySQL next-key lock 加鎖范圍

id = 11 是肯定不存在的。但是加了 for update,這時需要加 next-key lock,id = 11 所屬區間為 (10,15] 的前開后閉區間; 因為是等值查詢,不需要鎖 id = 15 那條記錄,next-key lock 會退化為間隙鎖; 最終區間為 (10,15) 的前開后開區間。

使用 data_locks 分析一下鎖信息:

淺談MySQL next-key lock 加鎖范圍

看下鎖的信息 X,GAP 表示加了間隙鎖,其中 LOCK_DATA = 15,表示鎖的是 主鍵索引 id = 15 之前的間隙。

淺談MySQL next-key lock 加鎖范圍

此時在另一個 Session 執行 SQL,答案顯而易見,是 id = 12 不可以插入,而 id = 15 是可以更新的。

可以得出結論,在數據不存在時,主鍵等值查詢,會鎖住該主鍵查詢條件所在的間隙。

主鍵范圍查詢(重點)

mysql> begin; select * from t where id >= 10 and id < 11 for update;

根據 《MySQL 45 講》分析得出下面結果:

id >= 10 定位到 10 所在的區間 (10,+∞); 因為是 >= 存在等值判斷,所以需要包含 10 這個值,變為 [10,+∞) 前閉后閉區間; id < 11 限定后續范圍,則根據 11 判斷下一個區間為 15 的前開后閉區間; 結合起來則是 [10,15]。(不完全正確)

先看下 data_locks

淺談MySQL next-key lock 加鎖范圍

可以看到除了表鎖之外,還有 id = 10 的行鎖(X,REC_NOT_GAP)以及主鍵索引 id = 15 之前的間隙鎖(X,GAP)。

所以實際上 id = 15 是可以進行更新的。也就是說前開后閉區間出現了問題,個人認為應該是 id < 11 這個條件判斷,導致不需要進行了鎖 15 這個行鎖。

淺談MySQL next-key lock 加鎖范圍

結果驗證也是正確的,id = 12 插入阻塞,id = 15 更新成功。

當范圍的右側是包含等值查詢呢?

mysql> begin; select * from t where id > 10 and id <= 15 for update;

來分析一下這個 SQL:

id > 10 定位到 10 所在的區間 (10,+∞);id <= 15 定位是 (-∞, 15];結合起來則是 (10,15]。

同樣先看一下 data_locks

淺談MySQL next-key lock 加鎖范圍

可以看出只添加了一個主鍵索引 id = 15 的 X 鎖。

驗證下 id = 15 是否可以更新?再驗證 id = 16 是否可以插入?

淺談MySQL next-key lock 加鎖范圍

事實證明是沒有問題的!

當然,這里有小伙伴會說,在 《MySQL 45 講》 里面說這里有一個 bug,會鎖住下一個 next-key。

淺談MySQL next-key lock 加鎖范圍

事實證明,這個 bug 已經被修復了。修復版本為 MySQL 8.0.18。但是并沒有完全修復!!!

參考鏈接地址:

https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-18.html

搜索關鍵字:Bug #29508068)

淺談MySQL next-key lock 加鎖范圍

咱們可以分別用 8.0.17 進行復現一下:

淺談MySQL next-key lock 加鎖范圍

在 8.0.17 中 id <= 15 會將 id = 20 這條數據也鎖著,而在 8.0.25 版本中則不會。所以這個 bug 是被修復了的。

再來看下是前開后閉還是前開后開的問題,嚴謹一下,使用 8.0.17 和 8.0.18 做比較。

淺談MySQL next-key lock 加鎖范圍

淺談MySQL next-key lock 加鎖范圍

現在我估計大概率是在 8.0.18 版本修復 Bug #29508068 的時候,把這個前開后閉給優化成了前開后開了。

對比 data_locks 數據:

淺談MySQL next-key lock 加鎖范圍

注意紅色下劃線部分,在 8.0.17 版本中 id < 17 時 LOCK_MODE 是 X,而在 8.0.25 版本中則是 X,GAP。

總結

本文主要通過實際操作,對主鍵加鎖時的 next-key lock 范圍進行了驗證,并查閱資料,對比版本得出不同的結論。

結論一: 加鎖時,會先給表添加意向鎖,IX 或 IS; 加鎖是如果是多個范圍,是分開加了多個鎖,每個范圍都有鎖;(這個可以實踐下 id < 20 的情況) 主鍵等值查詢,數據存在時,會對該主鍵索引的值加行鎖 X,REC_NOT_GAP; 主鍵等值查詢,數據不存在時,會對查詢條件主鍵值所在的間隙添加間隙鎖 X,GAP; 主鍵等值查詢,范圍查詢時情況則比較復雜: 8.0.17 版本是前開后閉,而 8.0.18 版本及以后,進行了優化,主鍵時判斷不等,不會鎖住后閉的區間。臨界 <= 查詢時,8.0.17 會鎖住下一個 next-key 的前開后閉區間,而 8.0.18 及以后版本,修復了這個 bug。

優化后,導致后開,這個不知道是因為優化后,主鍵的區間會直接后開,還是因為是個 bug。具體小伙伴可以嘗試一下。

結論二

通過使用 select * from performance_schema.data_locks; 和操作實踐,可以看出 LOCK_MODE 和 LOCK_DATE 的關系:

LOCK_MODE LOCK_DATA 鎖范圍 X,REC_NOT_GAP 15 15 那條數據的行鎖 X,GAP 15 15 那條數據之前的間隙,不包含 15 X 15 15 那條數據的間隙,包含 15

LOCK_MODE = X 是前開后閉區間;X,GAP 是前開后開區間(間隙鎖);X,REC_NOT_GAP 行鎖。

基本已經摸清主鍵的 next-key lock 范圍,注意版本使用的是 8.0.25。

疑問 那唯一索引的 next-key lock 范圍是什么? 當索引覆蓋時鎖的范圍和加鎖的索引分別是什么? 我為什么說這個 bug 沒有完全修復,也是在非主鍵唯一索引中復現了這個 bug​。

文章篇幅有限,小伙伴可以先自己思考一下,盡量自己操作試一試,實踐出真知。至于具體答案,那就需要下一篇文章進行驗證并總結結論了。

到此這篇關于淺談MySQL next-key lock 加鎖范圍 的文章就介紹到這了,更多相關MySQL next-key lock 加鎖范圍 內容請搜索好吧啦網以前的文章或繼續瀏覽下面的相關文章希望大家以后多多支持好吧啦網!

標簽: MySQL 數據庫
相關文章:
主站蜘蛛池模板: 非标压力容器_碳钢储罐_不锈钢_搪玻璃反应釜厂家-山东首丰智能环保装备有限公司 | 扬尘在线监测系统_工地噪声扬尘检测仪_扬尘监测系统_贝塔射线扬尘监测设备「风途物联网科技」 | 长江船运_国内海运_内贸船运_大件海运|运输_船舶运输价格_钢材船运_内河运输_风电甲板船_游艇运输_航运货代电话_上海交航船运 | 东莞画册设计_logo/vi设计_品牌包装设计 - 华略品牌设计公司 | 北京亦庄厂房出租_经开区产业园招商信息平台| 美侍宠物-专注宠物狗及宠物猫训练|喂养|医疗|繁育|品种|价格 | 上海租奔驰_上海租商务车_上海租车网-矢昂汽车服务公司 | 标准光源箱|对色灯箱|色差仪|光泽度仪|涂层测厚仪_HRC大品牌生产厂家 | 湖南长沙商标注册专利申请,长沙公司注册代理记账首选美创! | 胜为光纤光缆_光纤跳线_单模尾纤_光纤收发器_ODF光纤配线架厂家直销_北京睿创胜为科技有限公司 - 北京睿创胜为科技有限公司 | MVE振动电机_MVE震动电机_MVE卧式振打电机-河南新乡德诚生产厂家 | 「安徽双凯」自动售货机-无人售货机-成人用品-自动饮料食品零食售货机 | 刮板输送机,粉尘加湿搅拌机,螺旋输送机,布袋除尘器 | 昆明化妆培训-纹绣美甲-美容美牙培训-昆明博澜培训学校 | 大倾角皮带机-皮带输送机-螺旋输送机-矿用皮带输送机价格厂家-河南坤威机械 | 柴油发电机组_柴油发电机_发电机组价格-江苏凯晨电力设备有限公司 | 品牌策划-品牌设计-济南之式传媒广告有限公司官网-提供品牌整合丨影视创意丨公关活动丨数字营销丨自媒体运营丨数字营销 | 深圳激光打标机_激光打标机_激光焊接机_激光切割机_同体激光打标机-深圳市创想激光科技有限公司 深圳快餐店设计-餐饮设计公司-餐饮空间品牌全案设计-深圳市勤蜂装饰工程 | 土壤有机碳消解器-石油|表层油类分析采水器-青岛溯源环保设备有限公司 | 深圳APP开发_手机软件APP定制外包_小程序开发公司-来科信 | 东莞喷砂机-喷砂机-喷砂机配件-喷砂器材-喷砂加工-东莞市协帆喷砂机械设备有限公司 | 南京种植牙医院【官方挂号】_南京治疗种植牙医院那个好_南京看种植牙哪里好_南京茀莱堡口腔医院 尼龙PA610树脂,尼龙PA612树脂,尼龙PA1010树脂,透明尼龙-谷骐科技【官网】 | 电子元器件呆滞料_元器件临期库存清仓尾料_尾料优选现货采购处理交易商城 | SPC工作站-连杆综合检具-表盘气动量仪-内孔缺陷检测仪-杭州朗多检测仪器有限公司 | 衬塑设备,衬四氟设备,衬氟设备-淄博鲲鹏防腐设备有限公司 | 合肥白癜风医院_[治疗白癜风]哪家好_合肥北大白癜风医院 | 阀门智能定位器_电液动执行器_气动执行机构-赫尔法流体技术(北京)有限公司 | wika威卡压力表-wika压力变送器-德国wika代理-威卡总代-北京博朗宁科技 | 成都热收缩包装机_袖口式膜包机_高速塑封机价格_全自动封切机器_大型套膜机厂家 | 旋转气浴恒温振荡器-往复式水浴恒温振荡器-金怡百科 | 光照全温振荡器(智能型)-恒隆仪器 | 首页 - 张店继勇软件开发工作室 兰州UPS电源,兰州山特UPS-兰州万胜商贸 | 浩方智通 - 防关联浏览器 - 跨境电商浏览器 - 云雀浏览器 | 锂电池生产厂家-电动自行车航模无人机锂电池定制-世豹新能源 | 手表腕表维修保养鉴定售后服务中心网点 - 名表维修保养 | 对辊式破碎机-对辊制砂机-双辊-双齿辊破碎机-巩义市裕顺机械制造有限公司 | 河南道路标志牌_交通路标牌_交通标志牌厂家-郑州路畅交通 | 艾乐贝拉细胞研究中心 | 国家组织工程种子细胞库华南分库 | Magnescale探规,Magnescale磁栅尺,Magnescale传感器,Magnescale测厚仪,Mitutoyo光栅尺,笔式位移传感器-苏州连达精密量仪有限公司 | 细胞染色-流式双标-试剂盒免费代做-上海研谨生物科技有限公司 | 老城街小面官网_正宗重庆小面加盟技术培训_特色面馆加盟|牛肉拉面|招商加盟代理费用多少钱 |