之前在数据库中,发现一条sql语句会导致死锁,但是其中查询的键都加了索引了,这死锁从何而来呢?
于是去查看了sql语句和建表语句,如下:
一、问题
UPDATE eagle_riskcon_third SET msessage = ? , time = ?
where ip_id = ? and type = ?
查看时,报错为Deadlock found when trying to get lock
二、查错
那么确实是发生了死锁,究竟是因为什么呢?经过查询和找寻资料,发现了问题所在:
update时,如果where条件里面涉及多个字段,区分度都比较高且字段都分别建了索引的话,mysql会多个索引各走一遍,然后结果取个交集;
单条记录更新不会引发问题; 多条记录并发更新时,如果索引行数有重叠,因加锁顺序可能不同,互相等待可能会导致死锁,为什么加锁顺序会不同呢?
我们的sql中where条件的顺序是一定的,那么加锁顺序也应该一定,为什么会有加锁顺序不同情况。情况是这样的:因为我们使用的是两个单值索引,where条件中是复合条件,那么mysql会使用index merge进行优化,优化过程是mysql会先用索引1进行扫表,在用索引2进行扫表,然后求交集形成一个合并索引。这个使用索引扫表的过程和我们本身的sql使用索引的顺序可能存在互斥,所以造成了死锁。
三、解决
第一、添加ip_id+type的组合索引,这样就可以避免掉index merge;
第二、将优化器的index merge优化关闭;
建议选择第一种方法来避免此问题的发生。