关于mysql:一次-MySQL-线上死锁分析实战

关键词:MySQL Index Merge

前言

MySQL 的锁机制置信大家在学习 MySQL 的时候都有简略的理解过,那既然有锁就必然绕不开死锁这个问题。其实 MySQL 在大部分场景下是不会存在死锁问题的(比方并发量不高,SQL 写得不至于太拉胯的状况),然而在高并发的业务场景下,一不注意就会产生死锁,而这个死锁剖析起来也比拟麻烦。

【腾讯云】云产品限时秒杀,爆款1核2G云服务器,首年99元

前段时间在公司实习的时候就遇到了一个比拟奇怪的死锁,之前始终没来得及好好整顿,最近有空复现了一下,算是积攒一点教训。

业务场景

简略说一下业务背景,公司做的是电商直播,我负责的是主播端相干的业务。而这个死锁就呈现在主播后盾对商品信息进行更新的时候。

咱们的一个商品会有两个关联的 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_idteacher_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_idteacher_id 单个字段均无奈惟一确定一条数据,只能联结两个字段,能力确定一条数据,并且设定 class_idteacher_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_idteacher_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 = 6teacher_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 的时候多加留神就好了!

我是小桔,欢送关注我的微信公众号,带你理解更多前后端常识。

阿里云限时活动-2核2G-5M带宽-40-100G SSD服务器,特惠价86元/年(原价724元/年,限时99元续购三次),速抢

本文由乐趣区整理发布,转载请注明出处,谢谢。

You may also like...

发表评论

邮箱地址不会被公开。 必填项已用*标注

此站点使用Akismet来减少垃圾评论。了解我们如何处理您的评论数据