问题:
在多访问的情况下,一个删除计划的操作会出现死锁现象,报错如下:
### Error updating database. Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction### The error may involve com.longfor.tender.mapper.TdPlaInfoMapper.updatePlanDelById-Inline### The error occurred while setting parameters### SQL: UPDATE td_plan SET is_delete = ?, last_update_time=?, last_update_by = ? WHERE id = ?### Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction; SQL []; Deadlock found when trying to get lock; try restarting transaction; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:263) at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73) at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:71) at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:364) at com.sun.proxy.$Proxy20.update(Unknown Source) at org.mybatis.spring.Sq
解决方案:
此删除涉及到事物,在删除计划的过程中,还要删除立项信息、合同需求信息关联的计划信息。在删除开始之前设置事物,是不够严谨的。所以,把开启事物放置到删除立项的方法、合同需求方法、计划的方法,这样,每一模块是一个单独的事物。锁的范围缩小,基本并发可用。
网上搜索到的资料,以备参考:
表结构:
CREATE TABLE `test` ( `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT ‘主键’, `test_id` bigint(20) DEFAULT NULL, `group_id` bigint(20) DEFAULT NULL COMMENT ‘Id,对应test_group.id’, `gmt_created` datetime DEFAULT NULL COMMENT ‘创建时间’, `gmt_modified` datetime DEFAULT NULL COMMENT ‘修改时间’, `is_deleted` tinyint(4) DEFAULT ‘0’ COMMENT ‘删除。’, PRIMARY KEY (`id`), KEY `idx_testid` (`test_id`), KEY `idx_groupid` (`group_id`) ) ENGINE=InnoDB AUTO_INCREMENT=7429111 ;SQL执行计划:
mysql>explain UPDATE test SET is_deleted = 1 WHERE group_id = 1332577 and test_id = 4212859所以第一个事务先根据group_id索引,已经锁住primary id,然后再根据test_id索引,锁定primary id;第二个事务先根据test_id索引,已经锁住primary id,然后再根据group_id索引,去锁primary id;所以这样并发更新就可能出现死索引。
MySQL官方也已经确认了此bug:https://bugs.mysql.com/bug.php?id=77209
解决方法有两种:
第一、添加test_id+group_id的组合索引,这样就可以避免掉index merge;
第二、将优化器的index merge优化关闭;
建议选择第一种方法来避免此问题的发生。
update时,如果where条件里面涉及多个字段,区分度都比较高且字段都分别建了索引的话,mysql会多个索引各走一遍,然后结果取个交集;
单条记录更新不会引发问题; 多条记录并发更新时,如果索引行数有重叠,因加锁顺序可能不同,互相等待可能会导致死锁,为什么加锁顺序会不同呢?我们的sql中where条件的顺序是一定的,那么加锁顺序也应该一定,为什么会有加锁顺序不同情况。情况是这样的,因为我们使用的是两个单值索引,where条件中是复合条件,那么mysql会使用index merge进行优化,优化过程是mysql会先用索引1进行扫表,在用索引2进行扫表,然后求交集形成一个合并索引。这个使用索引扫表的过程和我们本身的sql使用索引的顺序可能存在互斥,所以造成了死锁。更多问题说明及解决方案请参见
另外一个mysql死锁的场景
在事务中用for循环更新一张表,这张表中有主键和二级索引,更新就是以二级索引为条件,这时候,因为for循环里面执行的循序不一定,所以有可能导致死锁
原文:https://blog.csdn.net/zheng0518/article/details/54695605 ;
mysql 官网参考
[1 Jun 2015 11:31] Andrii Nikitin
Description:On some conditions UPDATE query uses index merge when both indexes expect to retrieve 1 row.This behavior increases chances for deadlock.(Corresponding SELECT doesn't show index merge)How to repeat:drop table if exists a;CREATE TABLE `a` ( `ID` int AUTO_INCREMENT PRIMARY KEY, `NAME` varchar(21), `STATUS` int, KEY `NAME` (`NAME`), KEY `STATUS` (`STATUS`)) engine = innodb;set @N=0;insert into a(ID,NAME,STATUS)select @N:=@N+1, @N%1600000, floor(rand()*4) from information_schema.global_variables a, information_schema.global_variables b, information_schema.global_variables c LIMIT 1600000;update a set status=5 where rand() < 0.005 limit 1;explain UPDATE a SET STATUS = 2 WHERE NAME = '1000000' AND STATUS = 5;+-------------------------+--------------+------------------------------------------------------| type key | key_len rows | Extra +-------------------------+--------------+------------------------------------------------------| index_merge NAME,STATUS | 24,5 1 | Using intersect(NAME,STATUS); Using where; Using temp+-------------------------+--------------+------------------------------------------------------Suggested fix:Do not use index merge when single index is good enoughTry to avoid using index merge in UPDATE to not provoke deadlocks
[25 Feb 2016 18:38] Paul Dubois
Noted in 5.6.30, 5.7.12, 5.8.0 changelogs.For some queries, an Index Merge access plan was choosen over a rangescan when the cost for the range scan was the same or less.