不恰当的update语句使用主键和索引导致mysql死锁

妖狐艹你老母 2022-02-21 15:15 230阅读 0赞

转载：[https://blog.csdn.net/lzy\_lizhiyang/article/details/52678446][https_blog.csdn.net_lzy_lizhiyang_article_details_52678446]

**经验教训：**  
电商无论前台后台的程序，都不应该存在仅根据非主键的几个字段一查就要update/delete的场景。即使有，也应该改为先把要更新的记录查出来然后逐条按主键id更新。

背景知识：  
MySQL有三种锁的级别：页级、表级、行级。  
MyISAM和MEMORY存储引擎采用的是表级锁（table-level locking）；BDB存储引擎采用的是页面锁（page-level locking），但也支持表级锁；InnoDB存储引擎既支持行级锁（row-level locking），也支持表级锁，但默认情况下是采用行级锁。

MySQL这3种锁的特性可大致归纳如下：

表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高,并发度最低。  
行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低,并发度也最高。  
页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

行级锁并不是直接锁记录，而是锁索引，如果一条SQL语句用到了主键索引，mysql会锁住主键索引；如果一条语句操作了非主键索引，mysql会先锁住非主键索引，再锁定主键索引。

问题现象：

线上出现错误日志：mysql死锁问题。

排查过程：

请DBA协助查看mysql错误日志，发现确实存在死锁问题。

发生死锁的就是以上两条sql，两次请求时间仅间隔1毫秒。  
涉及到的数据库表如下：

根据背景知识里的红色描述：如果用到了主键索引，mysql会锁定主键索引，如果用到了非主键索引，msyql会先锁定非主键索引，再锁定主键索引。因此，在SQL(1)中先锁定了next\_consume\_time这个非主键索引，还需要锁定主键索引，此时SQL(2)直接锁定了主键索引，而其update语句中set使用了next\_consume\_time，同时还需要next\_consume\_time这个非主键索引。因此两条SQL就出现了对索引资源的竞争，造成死锁。

从业务方面考虑：两条SQL是从两台机器发起的请求，而这两条SQL在业务上是存在了先后顺序的，先执行SQL(1)占用需要执行的表记录，在执行SQL(2)进行业务操作。从日志中发现，54机器执行的是SQL(2)，那么54机器肯定已经执行过了SQL(1)，此时该条记录已经是被占用状态了，55机器又怎么会执行SQL(2)呢？难道是没被占用吗？  
DBA又查看mysql的binlog，发现queue\_id为283410的记录，确实正常执行过SQL(1)，那55机器为什么还要再次执行SQL(1)呢？  
又查看开放平台日志，54机器线程启动时间为：  
2016-09-25 11:12:40,463\] \[dbMsgConsumer-3\] \[INFO\] \[taskLogger\] \[\] - \[QueueConsumeTask started\]  
55机器线程启动时间为：  
2016-09-25 11:12:40,583\] \[dbMsgConsumer-2\] \[INFO\] \[taskLogger\] \[\] - \[QueueConsumeTask started\]  
两者仅相差120毫秒。

目前咱们mysql默认的事务隔离级别是REPETABLE READ（可重复读），即在同一个事务内，多次查询结果是一致的。  
当54机器开启事务，执行SQL(1)时，事务还未提交，55机器也执行SQL(1)，此时55机器查询到的记录是更新前的，54机器提交事务，再去执行SQL(2)，此时由于SQL(1)是范围查询，SQL(2)是主键查询，SQL(2)的执行时间要远远少于SQL(1)，会造成54机器执行SQL(2)时，55机器还未执行完成SQL(1)，造成两条机器互相抢占资源，造成死锁。54和55两台机器执行示意图如下：

解决办法：  
修改SQL(1)为两步操作，首先通过条件查询出符合条件的记录，然后根据查询出的结果的主键id再进行update操作。  
修改后sql，先执行查询操作：

再执行修改操作，使用获取到的主键ID

代码修改如下：

经验教训：  
电商无论前台后台的程序，都不应该存在仅根据非主键的几个字段一查就要update/delete的场景。即使有，也应该改为先把要更新的记录查出来然后逐条按主键id更新。

*  [tian\_dongze：][tian_dongze] 代码仍然会产生死锁。问题在于使用查询出来的queueid集合用in去做范围lock,如果多节点同时查询到queue,又同时都使用in queue去update。还是有可能发生死锁。而且查询和更新都在一个事务里，事务未真正提交。多个节点情况下，死锁还是会存在。(9个月前\#2楼)收起回复
    
    0
 *   *  [![lzy\_lizhiyang][lzy_lizhiyang]][lzy_lizhiyang_lzy_lizhiyang]
        
        [在远行的路上][lzy_lizhiyang_lzy_lizhiyang]回复 tian\_dongze： 多节点的情况下，确实会存在同时查询到queue，但由于sql都是根据主键queue\_id做查询条件，使用的都是主键索引，当一个节点执行时，其他节点获取不到锁会等待，不会造成死锁。(9个月前)

[https_blog.csdn.net_lzy_lizhiyang_article_details_52678446]: https://blog.csdn.net/lzy_lizhiyang/article/details/52678446
[tian_dongze]: https://me.csdn.net/tian_dongze
[lzy_lizhiyang]: https://avatar.csdn.net/0/7/7/3_lzy_lizhiyang.jpg
[lzy_lizhiyang_lzy_lizhiyang]: /images/20220221/4f604b344bff45ecaa8466ca94c67459.png