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

最新推荐文章于 2024-05-29 17:02:37 发布

weixin_33736832

最新推荐文章于 2024-05-29 17:02:37 发布

阅读量617

点赞数

原文链接：https://my.oschina.net/maojindaoGG/blog/2980357

版权

2019独角兽企业重金招聘Python工程师标准>>>

问题：

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

### 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.

转载于:https://my.oschina.net/maojindaoGG/blog/2980357