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

问题发生场景

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

Mysql锁类型分析

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

类型	特性
表级锁 (table-level locking)	开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高,并发度最低。
行级锁 (row-level locking)	开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低,并发度也最高。
页面锁 (page-level locking)	开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

行级锁在使用的时候并不是直接锁掉这行记录,而是锁索引
如果一条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 14237

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

经验教训

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