【MySQL】记录锁?间隙锁?临键锁?到底锁了些什么?这一篇帮你捋清楚( ̄∇ ̄)/

这篇具有很好参考价值的文章主要介绍了【MySQL】记录锁?间隙锁?临键锁?到底锁了些什么?这一篇帮你捋清楚( ̄∇ ̄)/。希望对大家有所帮助。如果存在错误或未考虑完全的地方,请大家不吝赐教,您也可以点击"举报违法"按钮提交疑问。

特别强调~

本测试使用的是MySQL 8.0.27~ 8.0.27~ 8.0.27(因为不同版本命令可能会有差异哈)

打开两个终端,分别连接上MySQL,使用select @@global.transaction_isolation;查看隔离级别(间隙锁要在可重复读的隔离级别下)

【MySQL】记录锁?间隙锁?临键锁?到底锁了些什么?这一篇帮你捋清楚( ̄∇ ̄)/

​如果报类似ERROR 1193 (HY000): Unknown system variable 'tx_isolation'的错,一般是版本问题

# 老版本:select @@global.tx_isolation;

select @@global.tx_isolation;

# 5.8版本之后使用:select @@global.transaction_isolation;

select @@global.transaction_isolation;

我们的测试表中的数据长这样

【MySQL】记录锁?间隙锁?临键锁?到底锁了些什么?这一篇帮你捋清楚( ̄∇ ̄)/

​事务1

终端A开启事务,查询id=5的数据(注意加上for update使用当前读)

select * from app_user_copy1 where id = 5 for update;

【MySQL】记录锁?间隙锁?临键锁?到底锁了些什么?这一篇帮你捋清楚( ̄∇ ̄)/

查看当前事务的锁信息

select * from performance_schema.data_locks;

【MySQL】记录锁?间隙锁?临键锁?到底锁了些什么?这一篇帮你捋清楚( ̄∇ ̄)/

  • 在MySQL 5.5以上、5.7.14以下的版本中,用户可以通过INFORMATION_SCHEMA下的INNODB_TRX、INNODB_LOCKS以及INNODB_LOCK_WAITS这三张表简单地监控并分析可能存在的锁问题

  • 在MySQL 8.0版本中,则需要使用performance_schema下的data_locks以及data_lock_waits获取相关的锁以及锁等待信息

  • 而MySQL版本在5.7.14到8.0之间的用户,只能通过其它手段间接的获取上述信息

我们从LOCK_MODE列中可以看到此事当前事务有两把锁(后面附有各个列的含义介绍)

  • 第一行LOCK_MODE为IX,即意向排他锁,属于表级锁

  • 第二行LOCK_MODE为X,REC_NOT_GAP,表示当前仅为行记录锁,且非间隙锁,属于行级锁

打开一个终端B,同样开启事务,更新id=5的行数据,会进入阻塞

update app_user_copy1 set name='test' where id=5;

【MySQL】记录锁?间隙锁?临键锁?到底锁了些什么?这一篇帮你捋清楚( ̄∇ ̄)/

​等到超时了就报错

【MySQL】记录锁?间隙锁?临键锁?到底锁了些什么?这一篇帮你捋清楚( ̄∇ ̄)/

​分别插入 id=3 和 id=7 的数据

INSERT INTO `app_user_copy1` (`id`, `name`, `email`, `phone`, `gender`, `password`, `age`, `create_time`, `update_time`) VALUES (3, '用户0', '123456@qq.com', '18582305042', 1, 'ef0641a4-7a7a-11ec-970f-7a9ea76b236f', 98, '2022-01-21 13:28:15', '2022-01-21 13:28:15');

【MySQL】记录锁?间隙锁?临键锁?到底锁了些什么?这一篇帮你捋清楚( ̄∇ ̄)/

​INSERT INTO `app_user_copy1` (`id`, `name`, `email`, `phone`, `gender`, `password`, `age`, `create_time`, `update_time`) VALUES (7, '用户0', '123456@qq.com', '18582305042', 1, 'ef0641a4-7a7a-11ec-970f-7a9ea76b236f', 98, '2022-01-21 13:28:15', '2022-01-21 13:28:15');

【MySQL】记录锁?间隙锁?临键锁?到底锁了些什么?这一篇帮你捋清楚( ̄∇ ̄)/

​都插入成功,说明当命中🎯注解索引时,临键锁退化为行级锁,是不会加间隙锁的

事务1结束,将数据恢复至测试开始前

事务2

终端A开启事务,查询id=3的数据(注意加上for update使用当前读)

select * from app_user_copy1 where id = 3 for update;

【MySQL】记录锁?间隙锁?临键锁?到底锁了些什么?这一篇帮你捋清楚( ̄∇ ̄)/

查看当前事务的锁信息

select * from performance_schema.data_locks;

【MySQL】记录锁?间隙锁?临键锁?到底锁了些什么?这一篇帮你捋清楚( ̄∇ ̄)/

​可以看到,此时LOCK_MODE为X,GAP,LOCK_DATA为5,即加了间隙锁,锁住 id=5 的行数据前的间隙;

终端B开启事务,更新 id=1 跟 id=5 两个边界信息

update app_user_copy1 set name='test' where id = 1; update app_user_copy1 set name='test' where id = 5;

【MySQL】记录锁?间隙锁?临键锁?到底锁了些什么?这一篇帮你捋清楚( ̄∇ ̄)/

【MySQL】记录锁?间隙锁?临键锁?到底锁了些什么?这一篇帮你捋清楚( ̄∇ ̄)/

​都更新成功,即当命中间隙时,会锁住当前间隙,并且不包括前后两条数据(即开区间)

事务2结束,将数据恢复至测试开始前

事务3

终端A开启事务,查询 id > 3 and id <=5 的数据(注意加上for update使用当前读)

select * from app_user_copy1 where id > 3 and id <= 5 for update;

【MySQL】记录锁?间隙锁?临键锁?到底锁了些什么?这一篇帮你捋清楚( ̄∇ ̄)/

​查看当前事务的锁信息

select * from performance_schema.data_locks;

【MySQL】记录锁?间隙锁?临键锁?到底锁了些什么?这一篇帮你捋清楚( ̄∇ ̄)/

可以看到,此时LOCK_MODE为X,LOCK_DATA为5,即加了临键锁,锁住 id=5 的行数据以及其前的间隙;

终端B开启事务,更新 id=1 、id=5 的信息

update app_user_copy1 set name='test' where id = 1; update app_user_copy1 set name='test' where id = 5;

id=1 更新成功

【MySQL】记录锁?间隙锁?临键锁?到底锁了些什么?这一篇帮你捋清楚( ̄∇ ̄)/

​id=5 更新失败

【MySQL】记录锁?间隙锁?临键锁?到底锁了些什么?这一篇帮你捋清楚( ̄∇ ̄)/

​插入id=2数据,插入失败

INSERT INTO `app_user_copy1` (`id`, `name`, `email`, `phone`, `gender`, `password`, `age`, `create_time`, `update_time`) VALUES (2, '用户0', '123456@qq.com', '18582305042', 1, 'ef0641a4-7a7a-11ec-970f-7a9ea76b236f', 98, '2022-01-21 13:28:15', '2022-01-21 13:28:15');

【MySQL】记录锁?间隙锁?临键锁?到底锁了些什么?这一篇帮你捋清楚( ̄∇ ̄)/

插入id=7数据,插入成功

INSERT INTO `app_user_copy1` (`id`, `name`, `email`, `phone`, `gender`, `password`, `age`, `create_time`, `update_time`) VALUES (7, '用户0', '123456@qq.com', '18582305042', 1, 'ef0641a4-7a7a-11ec-970f-7a9ea76b236f', 98, '2022-01-21 13:28:15', '2022-01-21 13:28:15');

【MySQL】记录锁?间隙锁?临键锁?到底锁了些什么?这一篇帮你捋清楚( ̄∇ ̄)/

即临键锁会锁住当前记录以及记录前的间隙(左开右闭区间)

事务3结束,将数据恢复至测试开始前

事务4

终端A开启事务,查询 id > 3 and id < 9 的数据(注意加上for update使用当前读)

select * from app_user_copy1 where id > 3 and id < 9 for update;

【MySQL】记录锁?间隙锁?临键锁?到底锁了些什么?这一篇帮你捋清楚( ̄∇ ̄)/

​查看当前事务的锁信息

 select * from performance_schema.data_locks;

【MySQL】记录锁?间隙锁?临键锁?到底锁了些什么?这一篇帮你捋清楚( ̄∇ ̄)/

可以看到,此时有两个行级锁

  • 第一个LOCK_MODE为X,LOCK_DATA为5,即加了临键锁,锁住 id=5 的行数据以及其前的间隙;

  • 第二个LOCK_MODE为X,GAP,LOCK_DATA为9,即加了间隙锁,锁住 id=9 的行数据前的间隙;

终端B开启事务,更新 id=1 、id=5 、id=9 的信息

update app_user_copy1 set name='test' where id = 1; update app_user_copy1 set name='test' where id = 5; update app_user_copy1 set name='test' where id = 9; 

id=1 更新成功

【MySQL】记录锁?间隙锁?临键锁?到底锁了些什么?这一篇帮你捋清楚( ̄∇ ̄)/

id=5 更新失败

【MySQL】记录锁?间隙锁?临键锁?到底锁了些什么?这一篇帮你捋清楚( ̄∇ ̄)/

​id=9 更新成功

【MySQL】记录锁?间隙锁?临键锁?到底锁了些什么?这一篇帮你捋清楚( ̄∇ ̄)/

​插入id=2数据,插入失败

INSERT INTO `app_user_copy1` (`id`, `name`, `email`, `phone`, `gender`, `password`, `age`, `create_time`, `update_time`) VALUES (2, '用户0', '123456@qq.com', '18582305042', 1, 'ef0641a4-7a7a-11ec-970f-7a9ea76b236f', 98, '2022-01-21 13:28:15', '2022-01-21 13:28:15');

【MySQL】记录锁?间隙锁?临键锁?到底锁了些什么?这一篇帮你捋清楚( ̄∇ ̄)/

​插入id=7数据,插入失败

INSERT INTO `app_user_copy1` (`id`, `name`, `email`, `phone`, `gender`, `password`, `age`, `create_time`, `update_time`) VALUES (7, '用户0', '123456@qq.com', '18582305042', 1, 'ef0641a4-7a7a-11ec-970f-7a9ea76b236f', 98, '2022-01-21 13:28:15', '2022-01-21 13:28:15');

【MySQL】记录锁?间隙锁?临键锁?到底锁了些什么?这一篇帮你捋清楚( ̄∇ ̄)/

​即当查询的是一段范围时,会锁住在符合查询条件的所有数据行,以及范围内的所有间隙(开区间,除非该数据行符合查询条件) 事务5 前面4个测试我们都是使用的唯一的主键索引,下面我们用普通索引试下( ̄∇ ̄)/ 终端A开启事务,查询 age=35 的数据(注意加上for update使用当前读)

select * from app_user_copy1 where age = 35 for update;

【MySQL】记录锁?间隙锁?临键锁?到底锁了些什么?这一篇帮你捋清楚( ̄∇ ̄)/

​查看当前事务的锁信息

 select * from performance_schema.data_locks;

【MySQL】记录锁?间隙锁?临键锁?到底锁了些什么?这一篇帮你捋清楚( ̄∇ ̄)/

可以看到,此时有3把行锁

  • 第一行LOCK_MODE列中为X,即加了临键锁

    • LOCK_DATA为35, 5指的是 age=35 的 id=5 的数据

    • 锁住的范围是 age=35 的行数据以及其前间隙

  • 第二行LOCK_MODE列中为X,REC_NOT_GAP

    • LOCK_DATA为5,即给 id=5 的行记录加了记录锁

  • 第三行LOCK_MODE列中为X,GAP,即加了间隙锁

    • LOCK_DATA为37, 24的指的是 age=37 的 id=24 的数据

    • 锁住的范围是 age=37 的行数据前的间隙

我们把数据库数据按照age升序排列,如下图

【MySQL】记录锁?间隙锁?临键锁?到底锁了些什么?这一篇帮你捋清楚( ̄∇ ̄)/

终端B开启事务,做如下更新操作

update app_user_copy1 set name='test' where age = 33; update app_user_copy1 set name='test' where age = 35; update app_user_copy1 set name='test' where age = 37;

age=33 更新成功

【MySQL】记录锁?间隙锁?临键锁?到底锁了些什么?这一篇帮你捋清楚( ̄∇ ̄)/

​age=35 更新失败

【MySQL】记录锁?间隙锁?临键锁?到底锁了些什么?这一篇帮你捋清楚( ̄∇ ̄)/

age=37 更新成功

【MySQL】记录锁?间隙锁?临键锁?到底锁了些什么?这一篇帮你捋清楚( ̄∇ ̄)/

插入 age=34 数据,插入失败

INSERT INTO `app_user_copy1` (`name`, `email`, `phone`, `gender`, `password`, `age`, `create_time`, `update_time`) VALUES ('用户0', '123456@qq.com', '18582305042', 1, 'ef0641a4-7a7a-11ec-970f-7a9ea76b236f', 34, '2022-01-21 13:28:15', '2022-01-21 13:28:15');

【MySQL】记录锁?间隙锁?临键锁?到底锁了些什么?这一篇帮你捋清楚( ̄∇ ̄)/

​插入 age=36 数据,插入失败

INSERT INTO `app_user_copy1` (`name`, `email`, `phone`, `gender`, `password`, `age`, `create_time`, `update_time`) VALUES ('用户0', '123456@qq.com', '18582305042', 1, 'ef0641a4-7a7a-11ec-970f-7a9ea76b236f', 36, '2022-01-21 13:28:15', '2022-01-21 13:28:15');

【MySQL】记录锁?间隙锁?临键锁?到底锁了些什么?这一篇帮你捋清楚( ̄∇ ̄)/

​插入 age=38 数据,插入成功

INSERT INTO `app_user_copy1` (`name`, `email`, `phone`, `gender`, `password`, `age`, `create_time`, `update_time`) VALUES ('用户0', '123456@qq.com', '18582305042', 1, 'ef0641a4-7a7a-11ec-970f-7a9ea76b236f', 38, '2022-01-21 13:28:15', '2022-01-21 13:28:15');

【MySQL】记录锁?间隙锁?临键锁?到底锁了些什么?这一篇帮你捋清楚( ̄∇ ̄)/

​上面测试中WHERE后的条件都是加了索引的,如果该字段未加索引,则会锁表(未命中不锁)

总结

  • LOCK_MODE列中为X,即加了临键锁,锁住的范围是LOCK_DATA中的行数据以及其前间隙(左开右闭)

  • LOCK_MODE列中为X,REC_NOT_GAP,锁住的是LOCK_DATA中的行数据

  • LOCK_MODE列中为X,GAP,锁住的是LOCK_DATA中的行数据前面的间隙(左开右开)文章来源地址https://www.toymoban.com/news/detail-441597.html

到了这里,关于【MySQL】记录锁?间隙锁?临键锁?到底锁了些什么?这一篇帮你捋清楚( ̄∇ ̄)/的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处: 如若内容造成侵权/违法违规/事实不符,请点击违法举报进行投诉反馈,一经查实,立即删除!

领支付宝红包 赞助服务器费用

相关文章

  • mysql 间隙锁原理深度详解

    目录 一、前言 二、mysql之mvcc 2.1 什么是mvcc 2.2 mvcc组成 2.2.1 Undo log 多版本链 2.2.2 ReadView

    2024年02月10日
    浏览(38)
  • 聊一聊mysql中的间隙锁

    间隙锁在mysql中经常使用到,今天就聊一聊mysql的间隙锁的内容。 间隙锁是为了解决幻读的问题,并且在当前读的场景下解决的。 当前读包含:update,delete,insert,select…lock in share mode,select…for update 一基本概念 1、行锁:给某一行进行加锁 2、间隙锁:两个值之间的间隙,为解

    2024年02月12日
    浏览(43)
  • MySQL innoDB 间隙锁产生的死锁问题

    线上经常偶发死锁问题,当时处理一张表,也没有联表处理,但是有两个mq入口,并且消息体存在一样的情况,频率还不是很低,这么一个背景,我非常容易怀疑到,两个消息同时近到这一个事务里面导致的,但是是偶发的,又模拟不出来什么场景会导致死锁,只能进行代码

    2024年02月08日
    浏览(39)
  • 线上SQL超时场景分析-MySQL超时之间隙锁

    之前遇到过一个由MySQL间隙锁引发线上sql执行超时的场景,记录一下。 分布式事务消息表 :业务上使用消息表的方式,依赖本地事务,实现了一套分布式事务方案 消息表名 :mq_messages 数据量 :3000多万 索引 :create_time 和 status status :有两个值,1 和 2, 其中99%以上的状态都

    2024年02月05日
    浏览(51)
  • 微软独家付费功能,也被完美解锁了

    我们在刷短视频的时候经常会听到一些AI合成声音,它们有各种音色、语调,甚至不同的情绪,听起来与人声无异 其实这些大都是利用微软Azure的文字转语音技术来实现的 虽然国内也有很多配音工具,但体验下来还是微软的效果最好,语气最为自然 不过Azure的文字转语音功能

    2024年02月03日
    浏览(35)
  • 到底什么是哈希值,哈希值到底是怎么生成的,有什么用?

    哈希值,即HASH值,通常用一个短的随机字母和数位组成的字串来代表,是一组任意长度的输入信息通过哈希算法得到的“数据指纹”,即进行加密运算得到的一组二进制值。 因为电脑在底层机器码是采用二进位的模式,因此通过哈希算法得到的任意长度的二进位值映像为较

    2024年02月19日
    浏览(46)
  • .gitignore到底是什么?有什么用?

    研究者在github等网站0下载代码的时经常会看到一个“.gitinore”文件,它到底是一个什么东西呢? 在弄懂.gitignore文件是什么及作用之前,我们先要了解概念: Git项目是什么? 在上一段中我们提到了Git项目,那Git是什么呢?Git(读音为/gɪt/。)是一个开源的分布式版本控制系统,

    2024年02月07日
    浏览(37)
  • Nginx到底是什么,他能干什么?

    目录 Ngnix是什么,它是用来做什么的呢? 一。Nginx简介 二,为什么要用Nginx呢? 二。Nginx应用 1.HTTP代理和反向代理  2.负载均衡 Ngnix是什么,它是用来做什么的呢? Nginx是enginex的简写,是一款很优秀的开源的高性能HTTP和反向代理服务器,由于它是用C语言写的,所以速度非常快

    2024年02月11日
    浏览(35)
  • 【Linux基本指令(3)】几十条指令快速入手Linux/到底什么是日志?/指令的运行原理到底是什么?

    本文思维导图: head 与 tail 就像它的名字一样的浅显易懂,它是用来显示开头或结尾某个数量的文字区块,head 用来显示档案的开头至标准输出中而 tail 想当然尔就是看档案的结尾。 语法: head [参数]… [文件]… 功能: head 用来显示档案的开头至标准输出中,默认head命令打印

    2024年02月06日
    浏览(38)
  • 区块链到底是什么,为什么这么火爆

    一、相关背景介绍 互联网上的贸易,几乎都需要借助可资信赖的第三方信用机构来处理电子支付信息。这类系统仍然内生性地受制于“基于信用的模式”。 区块链技术是构建比特币区块链网络与交易信息加密传输的基础技术。它基于密码学原理而不基于信用,使得任何达成

    2023年04月08日
    浏览(44)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包,优惠每天领

二维码1

领取红包

二维码2

领红包