关于mysql死锁的一个问题。

以下是我的表的实体类，id是自动增长，分别在statDate，advertiserId，“"orderId", "statDate"” 都建有索引！

@Entity
@Table(name = "A"
        , indexes = {@Index(name = "A_stat_date", columnList = "statDate"),
        @Index(name = "A_advertiserId", columnList = "advertiserId")}
        , uniqueConstraints={@UniqueConstraint(name = "A_order_Id_stat_date", columnNames = {"orderId", "statDate"})})
public class QianChuanWebSxtOrderDayReport {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private Long orderId; // 订单ID
    @JSONField(name = "ad_id")
    private Long adId; // 计划ID
    @JSONField(name = "advertiser_id")
    private Long advertiserId; // 广告主Id
    @JsonIgnore // 忽略JSON化
    private LocalDate statDate; // 所属的哪一天

}

执行以下语句会经常发生死锁，事务性的删除，先删除今日广告主的所有数据，然后再保存今日最新的数据

    @Override
    @Transactional
    public void saveAll(Long advertiserId, LocalDate date, List<QianChuanWebSxtOrderDayReport> reports) {
        repository.deleteByAdvertiserIdAndStatDate(advertiserId, date);
        repository.flush();
        repository.saveAll(reports);
    }

死锁问题，怎么样可以彻底解决掉。。

mysql：版本 5.7
事务隔离级别为：读已提交



```java
------------------------
2021-10-29 10:39:37 0x7f59f19ff700
*** (1) TRANSACTION:
TRANSACTION 782976863, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 6 lock struct(s), heap size 1136, 18 row lock(s), undo log entries 9
MySQL thread id 4449027, OS thread handle 140158230841088, query id 18386593495 172.16.16.29 root update
insert into A (ad_id, advertiser_id, click_cnt, convert_cnt, create_order_amount, create_order_count, dy_comment, dy_follow, dy_like, dy_share, live_fans_club_join_cnt, live_pay_order_cost_per_order, live_watch_one_minute_count, luban_live_click_product_cnt, luban_live_comment_cnt, luban_live_enter_cnt, luban_live_gift_amount, luban_live_gift_cnt, luban_live_share_cnt, luban_live_slidecart_click_cnt, order_id, pay_order_amount, pay_order_count, play25feed_break, play50feed_break, play75feed_break, play_duration3s, play_over, prepay_order_amount, prepay_order_count, show_cnt, stat_cost, stat_date, total_play) values (1714915200240647, 1710487443496968, 0, 0, 0.0, 0, 0, 0, 0, 0, 0, null, 0, 0, 0, 0, 0.0, 0, 0, 0, 7024292577708916743, 0.0, 0, 0, 0, 0, 1, 0, 0.0, 0, 11, 4.35, '2021-10-29', 11)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1546 page no 42 n bits 408 index A_order_Id_stat_date of table `A` trx id 782976863 lock mode S waiting
Record lock, heap no 317 PHYSICAL RECORD: n_fields 3; compact format; info bits 32
 0: len 8; hex e17b4d964b02001e; asc  {M K   ;;
 1: len 3; hex 8fcb5d; asc   ];;
 2: len 8; hex 800000000003116d; asc        m;;

*** (2) TRANSACTION:
TRANSACTION 782976860, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
6 lock struct(s), heap size 1136, 21 row lock(s), undo log entries 12
MySQL thread id 4455151, OS thread handle 140024282609408, query id 18386593514 172.16.16.3 root update
insert into A (ad_id, advertiser_id, click_cnt, convert_cnt, create_order_amount, create_order_count, dy_comment, dy_follow, dy_like, dy_share, live_fans_club_join_cnt, live_pay_order_cost_per_order, live_watch_one_minute_count, luban_live_click_product_cnt, luban_live_comment_cnt, luban_live_enter_cnt, luban_live_gift_amount, luban_live_gift_cnt, luban_live_share_cnt, luban_live_slidecart_click_cnt, order_id, pay_order_amount, pay_order_count, play25feed_break, play50feed_break, play75feed_break, play_duration3s, play_over, prepay_order_amount, prepay_order_count, show_cnt, stat_cost, stat_date, total_play) values (1714880521646135, 1711766364093518, 26, 3, 328.97, 3, 0, 0, 5, 0, 0, null, 0, 0, 0, 0, 0.0, 0, 0, 0, 7024150326106177544, 328.97, 3, 146, 91, 65, 181, 36, 0.0, 0, 377, 117.75, '2021-10-29', 408)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 1546 page no 42 n bits 408 index A_order_Id_stat_date of table `A` trx id 782976860 lock_mode X locks rec but not gap
Record lock, heap no 236 PHYSICAL RECORD: n_fields 3; compact format; info bits 32
 0: len 8; hex e17a053aa881c007; asc  z :    ;;
 1: len 3; hex 8fcb5d; asc   ];;
 2: len 8; hex 8000000000031167; asc        g;;

Record lock, heap no 299 PHYSICAL RECORD: n_fields 3; compact format; info bits 32
 0: len 8; hex e17a8982bb40800d; asc  z   @  ;;
 1: len 3; hex 8fcb5d; asc   ];;
 2: len 8; hex 8000000000031168; asc        h;;

Record lock, heap no 309 PHYSICAL RECORD: n_fields 3; compact format; info bits 32
 0: len 8; hex e17acb818c80c008; asc  z      ;;
 1: len 3; hex 8fcb5d; asc   ];;
 2: len 8; hex 8000000000031169; asc        i;;

Record lock, heap no 317 PHYSICAL RECORD: n_fields 3; compact format; info bits 32
 0: len 8; hex e17b4d964b02001e; asc  {M K   ;;
 1: len 3; hex 8fcb5d; asc   ];;
 2: len 8; hex 800000000003116d; asc        m;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1546 page no 42 n bits 408 index A_order_Id_stat_date of table `A` trx id 782976860 lock mode S waiting
Record lock, heap no 310 PHYSICAL RECORD: n_fields 3; compact format; info bits 32
 0: len 8; hex e17acfd69341401f; asc  z   A@ ;;
 1: len 3; hex 8fcb5d; asc   ];;
 2: len 8; hex 8000000000031180; asc         ;;

*** WE ROLL BACK TRANSACTION (1)

```

像你这种情况，id设置为自增会出现一种问题，在同一个事务下，你删除的数据是被锁住了的，假设数据被删除后，又重新新增了数据，而此时自增的id等于你删除的那些数据其中的id，这时就会出现事务异常。
你这个需求可以不需要在同一个事务下，删除和新增数据互不影响

是不是访问很高啊，执行速度太慢，又有大量访问的话，久了就会死锁。走定时任务，全部异步，一次小批量处理试试。