MySQL死锁(Deadlock found when trying to get lock； try restarting transaction)_deadlock found when trying to get lock; try restar-CSDN博客

本文链接：https://blog.csdn.net/xhy1999/article/details/121422535

前言：最近一个SpringBoot项目在一段时间内出现了大量的死锁,花了些时间,最总解决了问题,在此记录一下。

写在前面：本次问题的核心是在事务检索和操作时没有使用索引,数据库使用了表锁,导致操作时间过长,从而使锁占用时间过长,最终导致死锁。

环境：

数据库：MySQL 5.7
存储引擎：InnoDB

(1).定位问题

进入数据库命令行，执行以下指令，查看最近一次死锁的信息：

show engine innodb status;

MySQL默认只能看到最近一次的死锁,如果想要把死锁信息保存到MySQL的错误日志中,请将以下配置添加到my.cnf
[mysqld]
innodb_print_all_deadlocks = 1
或
[mysqld]
innodb_print_all_deadlocks = on

或者执行以下语句

mysql> SET GLOBAL innodb_print_all_deadlocks = 1;
或
mysql> SET GLOBAL innodb_print_all_deadlocks = ‘ON’;

指令执行后,我们会看到以下信息

------------------------
LATEST DETECTED DEADLOCK
------------------------
2021-11-18 11:18:44 0x13dc
// 第一个事务
*** (1) TRANSACTION:
TRANSACTION 41652703, ACTIVE 45 sec fetching rows
mysql tables in use 1, locked 1
LOCK WAIT 557 lock struct(s), heap size 57552, 49714 row lock(s)
MySQL thread id 3979, OS thread handle 10496, query id 79146570 localhost 127.0.0.1 root updating
UPDATE mealrecordsreal  SET card_consume=0,
card_balance=3333.8000,
subsidy_consume=10,
subsidy_balance=338.0000,
status='1'  WHERE card_id='1570000000000000000'

// 这里是在等待主键索引 (和部分网上的情况不一样)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 117 page no 1161 n bits 176 index PRIMARY of table `onecard`.`mealrecordsreal` trx id 41652703 lock_mode X waiting
Record lock, heap no 86 PHYSICAL RECORD: n_fields 32; compact format; info bits 0

// 第二个事务
*** (2) TRANSACTION:
TRANSACTION 41652698, ACTIVE 68 sec starting index read, thread declared inside InnoDB 5000
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 596
MySQL thread id 3981, OS thread handle 5084, query id 79147777 localhost 127.0.0.1 root updating
UPDATE mealrecordsreal SET clock_id=999,
...[此处省略]
subsidy_consume=2.0000,
subsidy_balance=353.5000  WHERE card_id='1570000000000000000'
// 持有的锁
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 117 page no 1161 n bits 176 index PRIMARY of table `onecard`.`mealrecordsreal` trx id 41652698 lock_mode X locks rec but not gap
Record lock, heap no 86 PHYSICAL RECORD: n_fields 32; compact format; info bits 0
// 这里也是在等待主键索引
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 117 page no 9 n bits 160 index PRIMARY of table `onecard`.`mealrecordsreal` trx id 41652698 lock_mode X waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 32; compact format; info bits 0
// 回滚第一个事务
*** WE ROLL BACK TRANSACTION (1)

与SpringBoot的异常日志对比后,就找到了出现问题的地方

this.saveOrUpdateBatch(request.getData());

出问题时,批量操作的数据量大概在2000条左右,于是我把每次操作的数据量限制在100条,死锁就没有出现了

个人猜想(如果错误,请大家指出):

1.第一次请求时,请求超时,返回500
2.后续的请求都抛出了死锁异常
请求是使用Nginx转发到SpringBoot项目,可能是由于服务端与客户端断开连接,但事务仍然没有执行完成,导致锁没有释放
但可以确定的是,肯定和超时有关

于是,我将批量操作的语句全都分开,并记录下执行时间,结果发现select语句需要20-40ms,update语句需要120-150ms,insert语句需要20ms,这个速度肯定是有问题的,而对100条记录来说,花费的总时间是20s左右,那么2000条数据必然超过了MySQL默认的锁超时时间50s