纯净、安全、绿色的下载网站

首页|软件分类|下载排行|最新软件|IT学院

当前位置:首页IT学院IT技术

MySQL next-key lock 加锁范围 浅谈MySQL next-key lock 加锁范围

刘志航   2021-06-07 我要评论
想了解浅谈MySQL next-key lock 加锁范围的相关内容吗刘志航在本文为您仔细讲解MySQL next-key lock 加锁范围 的相关知识和一些Code实例欢迎阅读和指正我们先划重点: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 45 讲》中丁奇老师的结论:

看了这结论应该可以解答一大部分问题不过有一句非常非常重点的话需要关注:MySQL 后面的版本可能会改变加锁策略所以这个规则只限于截止到现在的最新版本即 5.x 系列<=5.7.248.0 系列 <=8.0.13

所以以上的规则对现在的版本并不一定适用下面我以 MySQL 8.0.25 版本为例进行多角度验证 next-key lock 加锁范围

环境准备

MySQL 版本:8.0.25

隔离级别:可重复读(RR)

存储引擎:InnoDB

mysql> select @@global.transaction_isolation,@@transaction_isolation\G
mysql> show create table t\G

如何使用 Docker 安装 MySQL可以参考另一篇文章《使用 Docker 安装并连接 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_locks\G

具体字段含义可以参考 官方文档

结果主要包含引擎、库、表等信息咱们需要重点关注以下几个字段:

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

如果是数据不存在的时候会加什么锁呢?锁的范围又是什么?

在验证之前分析一下数据的间隙

  • id = 11 是肯定不存在的但是加了 for update这时需要加 next-key lockid = 11 所属区间为 (10,15] 的前开后闭区间
  • 因为是等值查询不需要锁 id = 15 那条记录next-key lock 会退化为间隙锁
  • 最终区间为 (10,15) 的前开后开区间

使用 data_locks 分析一下锁信息:

看下锁的信息 X,GAP 表示加了间隙锁其中 LOCK_DATA = 15表示锁的是 主键索引 id = 15 之前的间隙

此时在另一个 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

可以看到除了表锁之外还有 id = 10 的行锁(X,REC_NOT_GAP)以及主键索引 id = 15 之前的间隙锁(X,GAP)

所以实际上 id = 15 是可以进行更新的也就是说前开后闭区间出现了问题个人认为应该是 id < 11 这个条件判断导致不需要进行了锁 15 这个行锁

结果验证也是正确的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

可以看出只添加了一个主键索引 id = 15 的 X 锁

验证下 id = 15 是否可以更新?再验证 id = 16 是否可以插入?

事实证明是没有问题的!

当然这里有小伙伴会说在 《MySQL 45 讲》 里面说这里有一个 bug会锁住下一个 next-key

事实证明这个 bug 已经被修复了修复版本为 MySQL 8.0.18但是并没有完全修复!!!

参考链接地址:

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

搜索关键字:Bug #29508068)

咱们可以分别用 8.0.17 进行复现一下:

在 8.0.17 中 id <= 15 会将 id = 20 这条数据也锁着而在 8.0.25 版本中则不会所以这个 bug 是被修复了的

再来看下是前开后闭还是前开后开的问题严谨一下使用 8.0.17 和 8.0.18 做比较

现在我估计大概率是在 8.0.18 版本修复 Bug #29508068 的时候把这个前开后闭给优化成了前开后开了

对比 data_locks 数据:

注意红色下划线部分在 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​

文章篇幅有限小伙伴可以先自己思考一下尽量自己操作试一试实践出真知至于具体答案那就需要下一篇文章进行验证并总结结论了


相关文章

猜您喜欢

  • Python虚拟环境 简单介绍Python虚拟环境及使用方法

    想了解简单介绍Python虚拟环境及使用方法的相关内容吗风烟倦在本文为您仔细讲解Python虚拟环境的相关知识和一些Code实例欢迎阅读和指正我们先划重点:Python虚拟环境用法,Python虚拟环境的介绍下面大家一起来学习吧..
  • C标准库堆内存函数 详解C标准库堆内存函数

    想了解详解C标准库堆内存函数的相关内容吗可可西在本文为您仔细讲解C标准库堆内存函数的相关知识和一些Code实例欢迎阅读和指正我们先划重点:c,堆内存下面大家一起来学习吧..

网友评论

Copyright 2020 www.fresh-weather.com 【世纪下载站】 版权所有 软件发布

声明:所有软件和文章来自软件开发商或者作者 如有异议 请与本站联系 点此查看联系方式