ON DUPLICATE KEY UPDATE 引起的死锁问题，求助一下

最近生产上遇到了一个死锁问题，看日志应该是和使用了 ON DUPLICATE KEY UPDATE 语法有关系。查阅了一些资料，这个语法的确在并发高时容易引起死锁问题，但主要都是 gap 锁和插入意图锁引起的。但我实际遇到的是和唯一索引、主键索引有关，在测试环境也无法复现，有没有大佬帮助一下。 以下是相关环境信息和日志信息， tb 是主要更新的表，tb1 和 tb2 是用于关联查询出插入内容

Mysql 5.7 RC 事务隔离级别

CREATE TABLE `tb` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  `bid` varchar(38) NOT NULL,
  `sid` varchar(20) NOT NULL,
  `oid` varchar(20) NOT NULL,
  `emark` char(2) NOT NULL DEFAULT '00',
  `amark` char(2) NOT NULL DEFAULT '00',
  `bmark` char(2) NOT NULL DEFAULT '00',
  `cmark` char(2) NOT NULL DEFAULT '00',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_bid_sid_oid` (`bid`,`sid`,`oid`),
  KEY `idx_oid` (`oid`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

CREATE TABLE `tb1` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `sid` varchar(20) NOT NULL,
  `bid` varchar(38) NOT NULL,
  `oid` varchar(20) DEFAULT NULL,
  `status` char(2) NOT NULL DEFAULT '1',
  `update_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_bid_sid` (`bid`,`sid`),
  KEY `idx_sid` (`sid`),
  KEY `idx_oid` (`oid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

CREATE TABLE `tb2` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `bid` varchar(38) NOT NULL,
  `expire_time` datetime DEFAULT NULL,
  `status` char(2) NOT NULL,
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_name_list_batch_id` (`batch_id`),
  KEY `idx_name_list_expire_time` (`expire_time`),
  KEY `index_name_list_market_no` (`market_case_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 

InnoDB: *** (1) TRANSACTION:

TRANSACTION 7367657071, ACTIVE 1 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 3180164, OS thread handle 140056880891648, query id 2058483539
tb update

insert into tb
    ( 
    bid, sid, oid, emark, amark, bmark, cmark, update_time
   )
    values ('444444', '555555', '666666', '00', '00', '00', '00', now())
    ON DUPLICATE KEY UPDATE
    amark = values(amark),
    update_time = now()

2023-12-28T19:00:05.105996+08:00 3180165 [Note] InnoDB: *** (1) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 270 page no 6338 n bits 352 index idx_bid_sid_oid of table db.tb trx id 7367657071 lock_mode X waiting Record lock, heap no 13 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 25; hex 4f4243574830315a3032363631323032333132323241303536; asc 444444;;
1: len 10; hex 30313939373333303331; asc 555555;;
2: len 6; hex 363733353839; asc 666666;;
3: len 8; hex 00000000000910e7; asc ;;

2023-12-28T19:00:05.106368+08:00 3180165 [Note]
InnoDB: *** (2) TRANSACTION:

TRANSACTION 7367656999, ACTIVE 2 sec inserting, thread declared inside InnoDB 236 mysql tables in use 3, locked 1 465 lock struct(s), heap size 57552, 38900 row lock(s), undo log entries 19415
MySQL thread id 3180165, OS thread handle 140056037168896, query id 2058483278
user Sending data

insert into tb
      ( 
    bid, sid, oid, emark, amark, bmark, update_time
   )
      select tb1.bid, tb1.sid, tb1.oid, '10', '00', '00', now()
      from tb1 left join tb2 
      on tb1.bid = tb2.bid
      where tb2.expire_time < '2023-12-31 19:00:03.963' and tb2.expire_time > now()
        and tb1.status IN
         (  
          '2'
         , 
          '3'
         ) 
       
      ON DUPLICATE KEY UPDATE
      emark = values(emark),
      update_time = now()

2023-12-28T19:00:05.106420+08:00 3180165 [Note] InnoDB: *** (2) HOLDS THE LOCK(S):

RECORD LOCKS space id 270 page no 6338 n bits 352 index idx_bid_sid_oid of table db.tb trx id 7367656999 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0 0: len 8; hex 73757072656d756d; asc supremum;;
Record lock, heap no 13 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 25; hex 4f4243574830315a3032363631323032333132323241303536; asc 444444;;
1: len 10; hex 30313939373333303331; asc 555555;;
2: len 6; hex 363733353839; asc 666666;;
3: len 8; hex 00000000000910e7; asc ;;

2023-12-28T19:00:05.139592+08:00 3180165 [Note] InnoDB: *** (2) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 270 page no 7273 n bits 88 index PRIMARY of table db.tb trx id 7367656999 lock_mode X locks rec but not gap waiting
Record lock, heap no 14 PHYSICAL RECORD: n_fields 12; compact format; info bits 0
0: len 8; hex 00000000000aaf6c; asc l;;
1: len 6; hex 0001b725866f; asc % o;;
2: len 7; hex e20000118b0110; asc ;;
3: len 25; hex 4f4243574830315a3032363631323032333132323241303536; asc 444444;;
4: len 10; hex 30313939373333303331; asc 555555;;
5: len 6; hex 363733353839; asc 666666;;
6: len 2; hex 3030; asc 00;;
7: len 2; hex 3030; asc 00;;
8: len 2; hex 3030; asc 00;;
9: len 2; hex 3030; asc 00;;
10: len 5; hex 99b1f93004; asc 0 ;;
11: len 5; hex 99b1f93004; asc 0 ;;

2023-12-28T19:00:05.140247+08:00 3180165 [Note] InnoDB: *** WE ROLL BACK TRANSACTION (1)