查询mysql 死锁(MySQL线上死锁分析实战)
查询mysql 死锁
MySQL线上死锁分析实战前言
mysql 的锁机制相信大家在学习 mysql 的时候都有简单的了解过,那既然有锁就必定绕不开死锁这个问题。其实 mysql 在大部分场景下是不会存在死锁问题的(比如并发量不高,sql 写得不至于太拉胯的情况),但是在高并发的业务场景下,一不注意就会产生死锁,而这个死锁分析起来也比较麻烦。
前段时间在公司实习的时候就遇到了一个比较奇怪的死锁,之前一直没来得及好好整理,最近有空复现了一下,算是积累一点经验。
业务场景
简单说一下业务背景,公司做的是电商直播,我负责的是主播端相关的业务。而这个死锁就出现在主播后台对商品信息进行更新的时候。
我们的一个商品会有两个关联的 id,通过其中任何一个 id 都无法确定唯一一件商品(也就是说这个 id 和商品是一对多的关系),只能同时查询两个 id,才能确定一件商品。所以在更新商品信息的时候,需要在 where 条件中同时指定两个 id,下面是死锁 sql 的结构(已脱敏):
|
update test_table set ` name `= "zhangsan" where class_id = 10 and teacher_id = 8; |
这个 sql 非常简单,根据两个等值条件,对一个字段进行更新。
不知道你看到这个 sql 会不会懵逼,按常理来说,应该是一个事务里有多条 sql 才会有可能出现死锁,这一条 sql 怎么可能出现死锁呢?
是的,我当时也有这样的疑惑,甚至怀疑是不是报警系统瞎报(最后证明不是…),当时是真的摸不着头脑。并且因为数据库权限的原因,想看死锁日志都看不到,又是临近下班的时候,找 dba 能麻烦死,所以就直接搜索引擎走起了……(关键词:update 死锁 单条 sql),最后查出来是由于 mysql 的索引合并优化导致的,即 index merge,下面会进行详细讲解并复现一下死锁场景。
索引合并
index merge 是 mysql 在 5.0 的时候引入的一项优化功能,主要是用于优化一条 sql 使用多个索引的情况。
我们来看刚刚的 sql,假设 class_id
和 teacher_id
分别是两个普通索引:
|
update test_table set ` name `= "zhangsan" where class_id = 10 and teacher_id = 8; |
如果没有 index merge 优化的时候,mysql 查询数据的步骤如下:
-
根据 class_id 或 teacher_id (具体使用哪个索引由优化器根据实际数据情况自行判断,这里假设使用
class_id
的索引)在二级索引上查询到对应数据的主键 id - 根据查询到的主键 id 进行回标查询(即查询聚簇索引),得到相应的数据行
-
从数据行中获取
teacher_id
,判断其是否等于 8,满足条件则返回
从这个过程中,不难看出,mysql 只使用到了一个索引,至于为什么不使用多个索引,简单来说就是因为多个索引在多棵树上,强行使用反而降低性能。
再来看看引入了 index merge 优化后,mysql 查询数据的步骤如下:
-
根据
class_id
查询到相应的主键,再根据主键回表查询到对应的数据行(记为结果集 a) -
根据
teacher_id
查询到相应的主键,再根据主键回表查询到对应的数据行(记为结果集 b) - 将结果集 a 和结果集 b 执行交集操作,获得最终满足条件的结果集
这里可以看出,有了 index merge 之后,mysql 将一条 sql 语句拆分成了两个查询步骤,分别使用两个索引,再用交集操作优化性能。
死锁分析
分析完了 index merge 的步骤,我们再回过头想一下为什么会出现死锁呢?
还记得上面说的 index merge 将一条 sql 查询拆分成了两个步骤吗,问题就出现在这里。我们知道 update
语句是会加上一个行级排他锁的,在分析加锁步骤之前,我们假设有如下一个数据表:
上表数据满足我们文章开头说的特点,根据 class_id
和 teacher_id
单个字段均无法唯一确定一条数据,只能联合两个字段,才能确定一条数据,并且设定 class_id
和 teacher_id
分别为两个普通索引。
假设有如下两条 sql 语句并发执行,它们的参数完全不同,直觉告诉我们应该不会出现死锁,但直觉往往是错误的:
|
// 线程 a 执行 update test_table set ` name `= "zhangsan" where class_id = 2 and teacher_id = 1; // 线程 b 执行 update test_table set ` name `= "zhangsan" where class_id = 1 and teacher_id = 2; |
那么在 index merge 的优化下,并发执行如上 sql 的时候,mysql 的加锁步骤如下:
最终,两个事务互相等待,形成死锁
解决方案
因为这个死锁本质上还是由于 index merge 这个优化导致的,所以要解决这个场景的死锁问题,本质上只要让 mysql 不走 index merge 优化即可。
方案一
手动将一条 sql 拆分成多条 sql,在逻辑层做交集操作,阻止 mysql 的憨憨优化行为,比如这里我们可以先根据 class_id
查询到相应主键,再根据 teacher_id
查询相应主键,最后根据交集后的主键查询数据。
方案二
建立联合索引,比如这里可以将 class_id
和 teacher_id
建立一个联合索引,mysql 就不会走 index merge 了
方案三
强制走单个索引,在表名后添加 for index(class_id)
可以指定该语句仅走 class_id 索引
方案四
关闭 index merge 优化:
-
永久关闭:
set [global|session] optimizer_switch='index_merge=off';
-
临时关闭:
update /*+ no_index_merge(test_table) */ test_table set
name="zhangsan" where class_id = 10 and teacher_id = 8;
场景复现
数据准备
为了方便测试,这里提供一个 sql 脚本,将其用 navicat 导入后即可得到需要的测试数据:
下载地址:https://cdn.juzibiji.top/file/index_merge_student.sql
导入之后,我们会得到如下格式的 10000 条测试数据:
测试代码
由于篇幅限制,这里仅给出代码 gist 链接:https://gist.github.com/juzi214032/17c0f7a51bd8d1c0ab39fa203f930c60
上述代码主要是开启 100 个线程执行我们的数据修改 sql 语句,来模拟线上并发情况,在运行几秒钟后,我们会得到下面这样一个报错:
com.mysql.cj.jdbc.exceptions.mysqltransactionrollbackexception: deadlock found when trying to get lock; try restarting transaction
这代表已经产生了死锁异常
死锁分析
上面我们用代码已经构造出了一个死锁,接下来我们进入 mysql 看看死锁日志,在 mysql 中执行如下命令即可查看死锁日志:
|
show engine innodb status; |
在日志中,我们找到 latest detected deadlock
这一行,这里开始便是我们上次产生的死锁,接下来我们开始分析。
通过第 29 行可以看到,事务 1 执行的 sql 的条件是 class_id = 6
和 teacher_id = 16
,它目前持有了一个行锁,第 34~39 行是该行数据,34 行是主键的十六进制表示,我们转换为 10 进制即为 1616。同样的,看 45 行,其等待拿锁的是主键 id 1517 的数据。
接下来用同样的方法分析事务 2,可知事务 2 持有了 3 把锁,分别是主键 id 为1317、1417、1517 的数据行,等待的是 1616 。
看到这里我们就已经发现了,事务 1 持有 1616 等待 1517,事务 2 持有1517 等待 1616,所以形成了一个死锁。此时 mysql 的处理方法是回滚持有锁最少的事务,并且 jdbc 会抛出我们前面的 mysqltransactionrollbackexception 回滚异常。
总结
这个死锁在排查的时候其实非常不好排查,如果你不知道 mysql 的 index merge,那么在排查的时候其实是毫无头绪的,因为呈现在你面前的就只有一条非常简单的 sql,就算看死锁日志,也是一样的不明所以。
所以处理这类问题,更多的还是考验你的知识储备量和经验,只要遇到过一次,后面在写 sql 的时候多加注意就好了!
到此这篇关于mysql线上死锁分析实战的文章就介绍到这了,更多相关mysql线上死锁分析内容请搜索开心学习网以前的文章或继续浏览下面的相关文章希望大家以后多多支持开心学习网!
原文链接:https://www.cnblogs.com/shixiaojula/p/14438621.html
- mysql密码过期怎么改(年底了,你的mysql密码安全吗)
- php不能用mysql(php写入mysql中文乱码的实例解决方法)
- mysql 自定义排序
- mysqlorder工作原理(详解Mysql order by与limit混用陷阱)
- 阿里云mysql配置升级注意事项(详解如何在阿里云上安装mysql)
- 安装mysql中要注意什么问题(安装Mysql时可能会遇到的一些疑难杂症)
- python中怎么连接mysql(python远程连接MySQL数据库)
- 修改mysql安装服务名称(Apache为mysql以及自己的项目设置虚拟路径)
- centos7离线安装mysql5.7(CentOS7.5 安装MySql的教程)
- 将SQL Server数据迁移到MySQL的方法
- docker如何搭建mysql(docker容器访问宿主机的MySQL操作)
- mariadb导入数据库命令(MySQL/MariaDB 如何实现数据透视表的示例代码)
- 5.6以上版本mysql数据复制(MySQL5.7并行复制原理及实现)
- mysql客户端怎么运行程序(MySQL 如何连接对应的客户端进程)
- mysql死锁是什么原因(MySQL的MaxIdleConns不合理,会变成短连接的原因)
- mysql如何删除整个表及数据(Mysql删除数据以及数据表的方法实例)
- 成功破圈,小牛电动SQi强势开 跨(小牛电动SQi强势开)
- 挑战新国标电自天花板,九号机械师MMAX 110P深度体验(挑战新国标电自天花板)
- 《满江红》不要只当电影看,学生应该这样做(满江红不要只当电影看)
- 电影《民间怪谈录之走阴人》定档8月5日,开启一场中式惊悚之旅(电影民间怪谈录之走阴人定档8月5日)
- 原创图画书,以儿童视角讲述中国故事(以儿童视角讲述中国故事)
- 八月再见 愿你岁月不扰,余生静好(八月再见愿你岁月不扰)
热门推荐
- js定时器几分钟执行(利用JS定时器实现元素移动)
- MVC中JSON字符长度超出限制
- 开发者微信公众号开发流程(微信公众平台开发教程⑥ 微信开发集成类的使用图文详解)
- python中输入背景颜色的代码(Python 给屏幕打印信息加上颜色的实现方法)
- python获取当前时间戳(Python基于datetime或time模块分别获取当前时间戳的方法实例)
- python 暗弱目标提取(Python提取频域特征知识点浅析)
- python爬虫10分钟教程(浅谈Python爬虫基本套路)
- http403错误怎么解决(HTTP 错误 403.1 - 禁止访问:执行访问被拒绝。 解决方法该页无法显示)
- mysqlinnodb锁使用教程(MySQL Innodb关键特性之插入缓冲insert buffer)
- 云服务器有必要买吗(云服务器好用吗?云服务器值得买吗?)
排行榜
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9