Mysql 批量操作出现DeadLock

Question

处理完一批数据需要插入到表中，在插入之前需要根据primary key删除旧的数据，然后再插入，操作是批量操作。 id 是自增主键

Connection conn = Mysql.Connection;
conn.setAutocommit(false);
PrepareStament ps = conn.prepareStament("INSERT INTO A (id...) values (?...)");
PrepareStament delPs = conn.prepareStament("DELETE FROM A WHERE id=?");
for (int id:ids) {
ps.setInt(1,id);
delPs.setInt(1,id);
ps.addBatch();
delPs.addBatch();
}
delPs.executeBatch();
ps.executeBatch();
conn.commit();

这个方法在多线程环境下执行

日志里会有DeadLock，在网上看了好多资料还是很迷糊，项目好急，就过来请教了，谢谢大家。

*** (1) TRANSACTION:
TRANSACTION 12775897364, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1136, 6 row lock(s), undo log entries 1
MySQL thread id 2360865, OS thread handle 140506580461312, query id 19321604 180.110.120.147 wangyuchen update
INSERT INTO dealer.sta_dealer_series_data (dealer_id, brand_id,series_id, onsale_count, sold_count, onsale_low_price, onsale_high_price, all_low_price, all_high_price) VALUES (2050,9,145,0,1,0.0,0.0,23.58,23.58)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 7444 page no 4730 n bits 352 index PRIMARY of table `dealer`.`sta_dealer_series_data` trx id 12775897364 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 267 PHYSICAL RECORD: n_fields 12; compact format; info bits 32
 0: len 4; hex 80000806; asc     ;;
 1: len 4; hex 80000024; asc    $;;
 2: len 4; hex 800001b6; asc     ;;
 3: len 6; hex 0002f980b8e5; asc       ;;
 4: len 7; hex 2f0000815c04ef; asc /   \  ;;
 5: len 4; hex 80000000; asc     ;;
 6: len 4; hex 80000001; asc     ;;
 7: len 3; hex 800000; asc    ;;
 8: len 3; hex 800000; asc    ;;
 9: len 3; hex 80075a; asc   Z;;
 10: len 3; hex 80075a; asc   Z;;
 11: len 5; hex 999d8afa43; asc     C;;

*** (2) TRANSACTION:
TRANSACTION 12775897367, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1136, 5 row lock(s), undo log entries 1
MySQL thread id 2358010, OS thread handle 140505548138240, query id 19321764 180.110.120.147 wangyuchen update
INSERT INTO dealer.sta_dealer_series_data (dealer_id, brand_id,series_id, onsale_count, sold_count, onsale_low_price, onsale_high_price, all_low_price, all_high_price) VALUES (2054,35,407,0,2,0.0,0.0,3.5,8.28)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 7444 page no 4730 n bits 352 index PRIMARY of table `dealer`.`sta_dealer_series_data` trx id 12775897367 lock_mode X
Record lock, heap no 267 PHYSICAL RECORD: n_fields 12; compact format; info bits 32
 0: len 4; hex 80000806; asc     ;;
 1: len 4; hex 80000024; asc    $;;
 2: len 4; hex 800001b6; asc     ;;
 3: len 6; hex 0002f980b8e5; asc       ;;
 4: len 7; hex 2f0000815c04ef; asc /   \  ;;
 5: len 4; hex 80000000; asc     ;;
 6: len 4; hex 80000001; asc     ;;
 7: len 3; hex 800000; asc    ;;
 8: len 3; hex 800000; asc    ;;
 9: len 3; hex 80075a; asc   Z;;
 10: len 3; hex 80075a; asc   Z;;
 11: len 5; hex 999d8afa43; asc     C;;

Record lock, heap no 276 PHYSICAL RECORD: n_fields 12; compact format; info bits 32
 0: len 4; hex 80000806; asc     ;;
 1: len 4; hex 80000068; asc    h;;
 2: len 4; hex 800003f5; asc     ;;
 3: len 6; hex 0002f980b8c9; asc       ;;
 4: len 7; hex 400001002a2120; asc @   *! ;;
 5: len 4; hex 80000000; asc     ;;
 6: len 4; hex 80000001; asc     ;;
 7: len 3; hex 800000; asc    ;;
 8: len 3; hex 800000; asc    ;;
 9: len 3; hex 800c58; asc   X;;
 10: len 3; hex 800c58; asc   X;;
 11: len 5; hex 999d8afa43; asc     C;;

Record lock, heap no 277 PHYSICAL RECORD: n_fields 12; compact format; info bits 32

Answer 1

应该是Gap lock的问题，插入时，mysql会将新记录小于主键的上一条记录和大于主键的下一条记录之间，上gap lock确保不会幻读。

假设已有记录，主键为1,100，200,300
线程1：插入主键50记录（锁1-100）,删除主键150记录（等，因为100-200被线程2锁）
线程2：插入主键120记录（锁100-200）,删除主键20记录（等，因为1-100被线程1锁）

这样就死锁了

Answer 2

多线程并发必须按照主键顺序操作，这样顶多会有短暂锁等待，但不会死锁