Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when tryin

本文探讨了多线程并发更新数据库时出现死锁的问题,并分析了其原因在于MySQL使用index merge优化导致的不同加锁顺序。提供了两种解决方案:一是添加组合索引,二是禁用index merge优化。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

多线程在更新数据库时报死锁问题

死锁信息

*** (1) TRANSACTION:
TRANSACTION 29285454235, ACTIVE 0.001 sec fetching rows
mysql tables in use 3, locked 3
LOCK WAIT 6 lock struct(s), heap size 1184, 4 row lock(s)
MySQL thread id 6641616, OS thread handle 0x2b165c4b1700, query id 28190427937 10.103.180.86 test_ebs Searching rows for update
UPDATE test SET is_deleted = 1 WHERE group_id = 1332577 and test_id = 4580605
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 132 page no 37122 n bits 352 index `PRIMARY` of table `testdriver`.`test` trx id 29285454235 lock_mode X locks rec but not gap waiting
Record lock, heap no 179 PHYSICAL RECORD: n_fields 8; compact format; info bits 0
*** (2) TRANSACTION:
TRANSACTION 29285454237, ACTIVE 0.001 sec fetching rows, thread declared inside InnoDB 4980
mysql tables in use 3, locked 3
5 lock struct(s), heap size 1184, 3 row lock(s)
MySQL thread id 6639213, OS thread handle 0x2b1694cc2700, query id 28190427939 10.103.180.113 test_ebs Searching rows for update
UPDATE test SET is_deleted = 1 WHERE group_id = 1332577 and test_id = 4212859
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 132 page no 37122 n bits 352 index `PRIMARY` of table `testdriver`.`test` trx id 29285454237 lock_mode X locks rec but not gap
Record lock, heap no 179 PHYSICAL RECORD: n_fields 8; compact format;



表结构:
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循环里面执行的循序不一定,所以有可能导致死锁

这个问题的错误信息是"java.sql.SQLException: Deadlock found when trying to get lock; try restarting transaction"。这个错误通常发生在数据库中的并发操作过程中,当两个或更多的事务相互等待对方所持有的资源时发生死锁死锁是一个常见的数据库问题,它会导致某些事务无法继续执行并最终失败。 根据引用的数据库报错日志显示,错误信息中提到了"Lock wait timeout exceeded",这意味着事务在等待锁的过程中超出了设置的等待时间。这种情况下,通常的解决方法是重启事务或终止事务。 引用提供了一个临时解决方案,即通过手动终止未能提交事务的线程来解决问题。这可以通过使用"kill"命令来终止线程的执行。需要注意的是,终止线程可能会导致未提交的更改丢失,所以在执行此操作之前应仔细考虑。 综上所述,针对这个错误,可以尝试以下几个解决方案: 1. 重新启动事务:根据错误信息中的建议,可以尝试重新启动事务来解决死锁问题。 2. 终止未提交事务的线程:如果有急需处理的业务数据,可以考虑使用"kill"命令终止未能提交事务的线程。 3. 优化数据库设计:死锁问题通常与数据库设计和查询有关。可以通过优化数据库设计、索引和查询来减少死锁的发生。 需要注意的是,不同的情况可能需要不同的解决方法,因此在实际应用中,需要对具体的数据库和代码进行分析来确定最佳的解决方案。<span class="em">1</span><span class="em">2</span><span class="em">3</span> #### 引用[.reference_title] - *1* *3* [生产异常 Cause: com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Lock wait timeout ...](https://blog.csdn.net/h952520296/article/details/127268930)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc_result","utm_medium":"distribute.pc_search_result.none-task-cask-2~all~insert_cask~default-1-null.142^v92^chatsearchT3_1"}}] [.reference_item style="max-width: 50%"] - *2* [解决生产异常 Cause com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException Lock wait timeout ...](https://blog.csdn.net/zhiyikeji/article/details/124766592)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc_result","utm_medium":"distribute.pc_search_result.none-task-cask-2~all~insert_cask~default-1-null.142^v92^chatsearchT3_1"}}] [.reference_item style="max-width: 50%"] [ .reference_list ]
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值