【MYSQL死锁问题】Deadlock found when trying to get lock;

问题:

在多访问的情况下,一个删除计划的操作会出现死锁现象,报错如下:

### 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使用索引的顺序可能存在互斥,所以造成了死锁。

更多问题说明及解决方案请参见https://bugs.mysql.com/bug.php?id=77209

另外一个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 enough
Try 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 range
scan when the cost for the range scan was the same or less.

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

扫码关注云+社区

领取腾讯云代金券