mysql批量insert死锁

Rcfee

已于 2023-12-01 18:29:02 修改

阅读量2.6k

点赞数

分类专栏：数据库文章标签： mysql 数据库 mysql批量insert死锁

于 2022-09-28 18:30:12 首次发布

本文链接：https://blog.csdn.net/xiazaizhuanyong1231/article/details/127094326

版权

数据库专栏收录该内容

2 篇文章 0 订阅

订阅专栏

场景：

清结算流程，用户为维度多线程进行订单、金额的清结算，在批量insert资产历史(每个用户每个币种只有一条数据)时mysql deadlock。

dump日志：

Transactions deadlock detected, dumping detailed information.
2022-09-27T18:11:13.600331+08:00 72980645 [Note] InnoDB:
*** TRANSACTION:



TRANSACTION 2864287581, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
21 lock struct(s), heap size 1136, 26 row lock(s), undo log entries 19
MySQL thread id 72980645, OS thread handle 140596170843904, query id 13810651742 192.168.8.31 root update
INSERT INTO td_fund_asset_history (id, record_no, record_date, record_time, user_id, account_id, total_market_value, assets_type, total_income, holding_income, yesterday_income, remark )
values
  
(null, '1574371280161431552', '2022-09-27 00:00:00.0', '2022-09-27 18:11:13.598', '200048459', '200048459', 120001.1800, 'USD', 1.18000, 1.18000, 1.18000, '首次保存' )
,
(null, '1574688535153467392', '2022-09-27 00:00:00.0', '2022-09-27 18:11:13.598', '200048459', '200048459', 29999.9900, 'HKD', 0.00000, 0.00000, 0.00000, '首次保存' )
2022-09-27T18:11:13.600371+08:00 72980645 [Note] InnoDB: *** HOLDS THE LOCK:



RECORD LOCKS space id 5026 page no 5087 n bits 688 index uk_record_no of table `za_invest_trade_00`.`td_fund_asset_history` trx id 2864287581 lock_mode X locks rec but not gap
Record lock, heap no 456 PHYSICAL RECORD: n_fields 3; compact format; info bits 32
0: len 3; hex 8fcd3b; asc   ;;;
1: len 8; hex 95da6ae131b3e000; asc   j 1   ;;
2: len 8; hex 80000000000857cd; asc       W ;;



Record lock, heap no 466 PHYSICAL RECORD: n_fields 3; compact format; info bits 32
0: len 3; hex 8fcd3b; asc   ;;;
1: len 8; hex 95d94a5682996000; asc   JV  ` ;;
2: len 8; hex 80000000000857cc; asc       W ;;



***  WAITING FOR THIS LOCK TO BE GRANTED, WHICH CONFLICTS WITH THE LOCK HELD BY TRANSACTION 2864287583:
RECORD LOCKS space id 5026 page no 5087 n bits 688 index uk_record_no of table `za_invest_trade_00`.`td_fund_asset_history` trx id 2864287581 lock mode S waiting
Record lock, heap no 470 PHYSICAL RECORD: n_fields 3; compact format; info bits 32
0: len 3; hex 8fcd3b; asc   ;;;
1: len 8; hex 95d94a5688196000; asc   JV  ` ;;
2: len 8; hex 80000000000857ce; asc       W ;;



2022-09-27T18:11:13.600877+08:00 72980645 [Note] InnoDB:
*** TRANSACTION:



TRANSACTION 2864287583, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 21 lock struct(s), heap size 1136, 23 row lock(s), undo log entries 17
MySQL thread id 72980643, OS thread handle 140596055435008, query id 13810651731 192.168.8.31 root update
INSERT INTO td_fund_asset_history (id, record_no, record_date, record_time, user_id, account_id, total_market_value, assets_type, total_income, holding_income, yesterday_income, remark )
values
  
(null, '1574371280253706240', '2022-09-27 00:00:00.0', '2022-09-27 18:11:13.596', '200048460', '200048460', 60000.3900, 'USD', 0.39000, 0.39000, 0.39000, '首次保存' )
,
(null, '1574688535166050304', '2022-09-27 00:00:00.0', '2022-09-27 18:11:13.596', '200048460', '200048460', 59999.9800, 'HKD', 0.00000, 0.00000, 0.00000, '首次保存' )
2022-09-27T18:11:13.600912+08:00 72980645 [Note] InnoDB: *** HOLDS THE LOCK:



RECORD LOCKS space id 5026 page no 5087 n bits 688 index uk_record_no of table `za_invest_trade_00`.`td_fund_asset_history` trx id 2864287583 lock_mode X locks rec but not gap
Record lock, heap no 470 PHYSICAL RECORD: n_fields 3; compact format; info bits 32
0: len 3; hex 8fcd3b; asc   ;;;
1: len 8; hex 95d94a5688196000; asc   JV  ` ;;
2: len 8; hex 80000000000857ce; asc       W ;;



***  WAITING FOR THIS LOCK TO BE GRANTED, WHICH CONFLICTS WITH THE LOCK HELD BY TRANSACTION 2864287581:
RECORD LOCKS space id 5026 page no 5087 n bits 688 index uk_record_no of table `za_invest_trade_00`.`td_fund_asset_history` trx id 2864287583 lock mode S waiting
Record lock, heap no 456 PHYSICAL RECORD: n_fields 3; compact format; info bits 32
0: len 3; hex 8fcd3b; asc   ;;;
1: len 8; hex 95da6ae131b3e000; asc   j 1   ;;
2: len 8; hex 80000000000857cd; asc       W ;;



*** WE ROLL BACK TRANSACTION 2864287583

日志分析：

MySQL thread id 72980645：线程id，后面跟随此次线程执行的sql；

[Note] InnoDB: *** HOLDS THE LOCK：HOLDS代表该线程持有以下锁；

RECORD LOCKS：持有的是记录锁/行锁，记录锁永远都是加在一行数据上的；

lock_mode X locks rec but not gap：锁类型为X锁，但并不是间隙锁；

n_fields 3：表示锁到的索引，有关系的字段为三个，以下就是被锁的数据行的这个索引相关的字段值；

WAITING FOR THIS LOCK：等待的锁信息。

简化、抓取关键日志：

事务 2864287581
uk_record_no：record_date、record_no、id
Record lock, heap no 456 PHYSICAL RECORD: n_fields 3; compact format; info bits 32
0: len 3; hex 8fcd3b; asc ;;;
1: len 8; hex 95da6ae131b3e000; asc j 1 ;;
2: len 8; hex 80000000000857cd; asc W ;;

wait lock
0: len 3; hex 8fcd3b; asc ;;;
1: len 8; hex 95d94a5688196000; asc JV ` ;;
2: len 8; hex 80000000000857ce; asc W ;;

事务 2864287583
Record lock, heap no 470 PHYSICAL RECORD: n_fields 3; compact format; info bits 32
0: len 3; hex 8fcd3b; asc ;;;
1: len 8; hex 95d94a5688196000; asc JV ` ;;
2: len 8; hex 80000000000857ce; asc W ;;

wait lock
0: len 3; hex 8fcd3b; asc ;;;
1: len 8; hex 95da6ae131b3e000; asc j 1 ;;
2: len 8; hex 80000000000857cd; asc W ;;