大量数据插入mysql,出现死锁。
(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
------------------------
2022-06-11 16:06:41 0x7f389405f700
// 第一个事务
*** (1) TRANSACTION:
TRANSACTION 115239597, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 5 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 2
MySQL thread id 142, OS thread handle 139880516249344, query id 14053439 localhost 127.0.0.1 root update
insert into asset_metrics_temp
( id,
org_id,
mac_addr,
asset_status,
input_v,
// 这里是在等待主键索引 (和部分网上的情况不一样)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 166 page no 4 n bits 584 index idx_mac_addr of table `cuta`.`asset_metrics_temp` trx id 115239597 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 333 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
0: len 16; hex 30303132344230303143363933414242; asc 00124B001C693ABB;;
1: len 8; hex 1c0fcddf2922a073; asc )" s;;
// 第二个事务
*** (2) TRANSACTION:
TRANSACTION 115239598, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
5 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 2
MySQL thread id 144, OS thread handle 139880978315008, query id 14053442 localhost 127.0.0.1 root update
insert into asset_metrics_temp
( id,
org_id,
mac_addr,
asset_status,
input_v,
// 持有的锁
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 166 page no 4 n bits 584 index idx_mac_addr of table `cuta`.`asset_metrics_temp` trx id 115239598 lock_mode X
Record lock, heap no 333 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
0: len 16; hex 30303132344230303143363933414242; asc 00124B001C693ABB;;
1: len 8; hex 1c0fcddf2922a073; asc )" s;;
// 这里也是在等待主引*
** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 166 page no 4 n bits 584 index idx_mac_addr of table `cuta`.`asset_metrics_temp` trx id 115239598 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 333 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
0: len 16; hex 30303132344230303143363933414242; asc 00124B001C693ABB;;
1: len 8; hex 1c0fcddf2922a073; asc )" s;;
// 回滚第二个事务*
** WE ROLL BACK TRANSACTION (2)
------------
发现因为 idx_mac_addr 这个索引导致的死锁,因为这个表一边存,一边删,数据量不多,所以把这个非主键索引去掉
在linux里进入mysql命令行,执行:
DROP INDEX idx_mac_addr ON `asset_metrics_temp`;
观察不再死锁
后来数据量增加又出现了死锁的情况,对删除数据与插入数据的代码 加了同步锁 synchronized ,继续观察一下