mysql 自增字段 deadlock_Mysql 批量操作出现DeadLock

处理完一批数据需要插入到表中，在插入之前需要根据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