Mysql Deadlock

问题描述

生产环境每隔一段时间会出现mysql数据库的死锁日志:

quest-show

同一条update语句,where 条件不同,但是会触发死锁,于是查看阿里云的数据库死锁日志,如下:

quest-show

update 语句如果使用的是主键索引,会将主键索引锁住,如果是普通索引,会先将普通索引锁住,然后根据普通索引的主键id再锁住主键索引,同一条update语句的索引执行应该是一样的,不应该存在互相等待释放的情况,于是有点陷入僵局,google一下有遇到相似问题的帖子,查看了执行计划,之前也看了执行计划,但是忽略了type字段和extra字段,结果如下:

explain-show

explain-show

索引合并查询,会同时使用idx_status_vmstatusuniq_instance 扫描记录并给普通索引加锁,然后通过普通索引中的主键ID去锁定主键索引,问题就出现在这里,由于 idx_status_vmstatus 索引扫描和 uniq_instance索引扫描是同时的,如果两条update语句同时执行,则 事务2 先锁定 锁定 uniq_instance 成功后锁定对应的主键,然后事务1 锁定idx_status_vmstatus 成功后也去锁定主键,此时主键已被事务2锁定,于是阻塞等待primary释放,接着事务2开始扫描 idx_status_vmstatus 发现普通索引被事务1锁住,于是阻塞等待idx_status_vmstatus,于是出现最终的 事务2等待 事务2释放idx_status_vmstatus,事务1等待事务1释放primary,即出现死锁。

解决方案也比较简单,先查出主键ID,使用主键ID再更新记录,因为使用主键ID直接加锁的话,锁粒度更小,即使同时更新一条记录,也不会出现同时等待对方将锁释放的场景。问题描述的比较简单,但在排查过程中还是走了不少弯路的。

Licensed under CC BY-NC-SA 4.0
皖ICP备20014602号
Built with Hugo
Theme Stack designed by Jimmy