Mysql报Deadlock found when trying to get lock; try restarting transaction问题解决

问题发生场景

今天记录一下最近项目中遇到的一个问题,前几天在部署项目后,在线上运行过程中,突然报了入下这样的错误,从报错信息中我们可以看到,是mysql在执行update操作的时候报了一个死锁的问题,今天解决了,特此记录一下.
在这里插入图片描述

Mysql锁类型分析
MySQL有三种锁的级别：页级、表级、行级,这个地方我遇到的问题是来自于行级锁,所以重点说一下。

• 行级锁在使用的时候并不是直接锁掉这行记录,而是锁索引
• 如果一条sql用到了主键索引(mysql主键自带索引),mysql会锁住主键索引;
• 如果一条sql操作了非主键索引,mysql会先锁住非主键索引,再锁定主键索引

问题排查过程

通过上面log日志中的报错信息,能很快确认报错的准确位置,

这个地方是执行了一个update的操作,我找到了了这个表,看了一下这个表的索引是有三个,两个非主键索引,一个主键索引.
在这里插入图片描述
果然,在图中这两条索引在那个update语句中都有进行的操作,具体如下

然后我又找了一下有可能会跟这条语句发生冲突的地方,果然在这个报错信息的上面,执行了一条这样的sql,这两条sql执行的间隔仅仅不超过1毫秒

根据上面所说的,如果一条sql用到了主键索引(mysql主键自带索引),mysql会锁住主键索引;
如果一条sql操作了非主键索引,mysql会先锁住非主键索引,再锁定主键索引.
因此sql(2)在使用的时候用到了schedu_id这个非主键索引,还需要锁定主键索引,然而此时sql(1)开始执行
,然后锁定了主键索引,但是在set操作中还用到了schedu_id这个非主键索引,但是这个索引在sql(1)执行的时候还在处于被锁的状态,因此两条sql就出现了对索引资源的竞争,造成了死锁.

解决方法

这个地方,代码的问题需要根据情况自己去修改,可以试着把索引去掉(有风险),或者在进行update的时候尽量避开非主键索引,我这里记录一下被锁后应该怎么去解决的方法,首先先用sql查询一下mysql的事务处理表

select * from information_schema.INNODB_TRX

正常情况下的状态都是RUNNING,但是在被锁之后就会变成LOCK WAIT ,一旦出现这种情况,就得杀死这个进程,如果进程杀不死就只能重启Mysql服务了
杀死进程

kill 进程ID

然后系统就能继续运行了,

经验教训

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

版权声明：本文为CSDN博主「光头2强」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/qq_44240587/article/details/108400666