mysql批量insert死锁

场景：

清结算流程，用户为维度多线程进行订单、金额的清结算，在批量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 ;;

 从以上日志可以看出，

事务2864287581获取了80000000000857cd这行记录的锁，等待80000000000857ce这行记录的锁；

事务2864287583获取了80000000000857ce这行记录的锁，等待80000000000857cd这行记录的锁；

形成了交替等待的场景，形成了死锁。

但是，为什么多线程批量insert时，会出现这种情况呢？

后续：

针对单字段的唯一索引，或主键，是不会加gap锁的，原因是不会有两条及以上同UK的数据;

联合键的唯一索引，是无法确定数据的唯一性的，所以会加gap锁，除非明确了UK键中的所有字段值；

针对批量insert时，会加X锁并且两边加上开区间的间隙锁，这也是为了防止幻读；

所以当事务1的数据分别在[1,5]、[6, 7]区间插入，事务2在[6, 7]、[1, 5]的区间插入，事务1成功在[1,5]插入，事务2成功在[6, 7]插入，这时就会发生事务1等[6, 7]区间的锁释放，事务2等[1, 5]区间的锁释放，就会导致这个问题了。

解决方案：批量插入改为单条插入，就不会发生锁互相等待的情况了