原创 一次 MySQL 线上死锁分析实战

发布时间:2021-06-24 08:01:02 浏览 66 来源:猿笔记 作者:是小桔啦

    MySQL的锁机制相信大家在学习MySQL的时候都有简单的了解过,其实MySQL在大部分场景下是不会存在死锁问题的(比如并发量不高,而这个死锁就出现在主播后台对商品信息进行更新的时候,通过其中任何一个ID都无法确定唯一一件商品(也就是说这个ID和商品是**一对多**的关系),应该是一个事务里有多条SQL才会有可能出现死锁,这一条SQL怎么可能出现死锁呢,update死锁单条sql),最后查出来是由于MySQL的索引合并优化导致的,下面会进行详细讲解并复现一下死锁场景,主要是用于优化一条SQL使用多个索引的情况。


    #主题列表:juejin,github,smartblue,cyanosis,channing-cyan,fancy,hydrogen,condensed-night-purple,greenwillow,v-green,vue-pro,healer-readable,mk-cute,jzman,geek-black,awesome-green,qklhk-chocolate

    #投稿主题:

    theme:awesome-green

    highlight:

    关键词:MySQLIndexMerge

    ##前言

    相信大家在学习MySQL的时候对MySQL的锁机制都有一个简单的了解。由于有锁,死锁问题无法避免。实际上MySQL在大多数场景下(比如并发性不高,SQL写得不太蹩脚的情况)都不会出现死锁问题,但是在高并发的业务场景下,一不小心就会出现死锁,这个死锁分析起来比较麻烦。

    前段时间在公司实习,遇到一个奇怪的僵局。之前没来得及整理,最近有空再来一遍,算是积累了一点经验。

    # #业务场景

    简单说下业务背景,公司做活电商,我负责主播相关的业务。当锚点后台更新商品信息时,就会出现这种死锁。

    我们的产品会有两个关联的id,一个产品不能用任意一个id来标识(也就是说这个id和产品的关系是* *一对多* *),只能同时查询两个id来标识一个产品。因此,在更新商品信息时,需要在where条件中指定两个id。以下是死锁SQL(脱敏)的结构:

    sqlUPDATEtest_tableSET`name`="zhangsan"WHEREclass_id=10ANDteacher_id=8;

    * *这个SQL很简单,根据两个等价条件更新一个字段。**

    不知道你看到这个SQL会不会很迷茫。根据常识,在死锁可能发生之前,一个事务中应该有多个SQL。这种SQL死锁是怎么发生的?

    是的,我当时也有这样的疑惑,甚至怀疑报警系统是不是瞎了(原来不是……),当时真的很困惑。而且因为数据库权限的原因,我看不到死锁日志,下班的时候还会和DBA闹矛盾,于是就启动了直接搜索引擎...(关键词:更新死锁单sql),最后发现是MySQL的IndexMerge优化导致的,也就是Index Merge,下面会详细说明,转载。

    # #索引合并

    IndexMerge是MySQL在5.0中引入的优化函数,主要用于优化一个SQL使用多个索引的情况。

    我们来看刚刚的SQL,假设`class_id`和`teacher_id`分别是两个普通索引:

    sqlUPDATEtest_tableSET`name`="zhangsan"WHEREclass_id=10ANDteacher_id=8;

    **如果没有IndexMerge优化的时候**,MySQL查询数据的步骤如下:

    -根据class_ID或teacher_id(使用哪个索引由优化器根据实际数据情况确定,假设这里使用的是‘class _ ID’的索引),在二级索引上找到对应数据的主键ID

    -根据查询到的主键标识,进行回标查询(即查询聚集索引),得到相应的数据行

    -从数据线获取teacher_id ',判断是否等于8,满足条件返回。

    从这个过程中,不难看出**MySQL只使用了一个索引* *。至于为什么不使用多个索引,简单来说就是因为多个索引在多个树上,强制使用会降低性能。

    **再来看看引入了IndexMerge优化后**,MySQL查询数据的步骤如下:

    -根据class_id '查询相应的主键,然后根据主键将相应的数据行查询回表中(记录为结果集a)

    -根据teacher_id '查询相应的主键,然后根据主键将相应的数据行查询回表中(记录为结果集b)

    -对结果集a和结果集B**进行交集* *运算,得到最终满足条件的结果集

    这里可以看到,使用IndexMerge,MySQL将一条SQL语句拆分为两个查询步骤,* *分别使用两个索引,然后用交集运算* *优化性能。

    # #死锁分析

    在分析了IndexMerge的步骤之后,我们再回头想想为什么会出现死锁。

    请记住,索引合并将一个SQL查询分成两步,这就是问题所在。我们知道,` UPDATE '语句将添加一个* *行级独占锁* *。在分析锁定步骤之前,我们假设有如下数据表:

    上表中的数据符合我们文章开头提到的特征。根据' class_id '和' teacher_id ',单个字段不能唯一确定一条数据。只有把这两个字段结合起来才能确定一条数据,` class_id '和` teacher_id '分别设置为两个公共索引。

    假设下面两条SQL语句并发执行,它们的参数完全不同。直觉告诉我们不应该出现死锁,但直觉往往是错的:

    sql//线程A执行UPDATEtest_tableSET`name`="zhangsan"WHEREclass_id=2ANDteacher_id=1;//线程B执行UPDATEtest_tableSET`name`="zhangsan"WHEREclass_id=1ANDteacher_id=2;

    那么**在IndexMerge的优化下**,并发执行如上SQL的时候,MySQL的加锁步骤如下:

    * *最终,两个事务相互等待,形成死锁* *

    # #解决方案

    因为这个死锁本质上是由IndexMerge的优化造成的,为了解决这种场景下的死锁问题,本质上MySQL不应该通过IndexMerge来优化。

    * *备选方案1 * *

    手动将一个SQL拆分成多个SQL,在逻辑层面做交集运算,防止MySQL变傻~ ~

作者信息

是小桔啦 [等级:3]
发布了 3 篇专栏 · 获得点赞 5 · 获得阅读 375

相关推荐 更多