MySQL 并发 replace into 导致 insert intention 与 gap lock 形成死锁

------------------------
LATEST DETECTED DEADLOCK
------------------------
2024-03-13 20:48:29 0x7ff6fb2f8700
*** (1) TRANSACTION:
TRANSACTION 385752159, ACTIVE 0 sec inserting
mysql tables in use 2, locked 2
LOCK WAIT 4 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 2
  MySQL thread id 17811400, OS thread handle 140698772580096, query id 25019811085 x.x.x.x eclp_po1_rw update
REPLACE INTO `eclp_po1`.`_po_main_new` (`id`, `parent_no`, `po_no`, `unit_flag`, `unit_rule`, `bill_of_lading`, `dept_no`, `dept_id`, `dept_name`, `seller_id`, `seller_no`, `seller_name`, `org_id`, `org_no`, `org_name`, `distribution_id`, `distribution_no`, `distribution_name`, `warehouse_id`, `warehouse_no`, `warehouse_name`, `out_warehouse_no`, `out_warehouse_name`, `target_warehouse_id`, `expect_arrival_time`, `po_type`, `po_status`, `po_cancel_status`, `po_sign`, `out_source_no`, `out_po_no`, `out_seller_no`, `difference_remark`, `contacts`, `contacts_address`, `supplier_id`, `supplier_no`, `supplier_name`, `supplier_contacts`, `approval_time`, `approval_user`, `po_dl_result`, `po_dl_message`, `receive_level`, `isv_replenish_type`, `temperature`, `temperature_value`, `create_time`, `products_code`, `receive_box_number`, `products_name`, `update_time`, `create_user`, `update_user`, `yn`, `ts`,
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 245 page no 234753 n bits 384 index po_no of table `eclp_po1`.`_po_main_new` trx id 385752159 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 313 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 16; hex 45504c34343138303834393836363939; asc xxx;;
 1: len 8; hex 80000404aa62df4b; asc      b K;;

*** (2) TRANSACTION:
TRANSACTION 385752158, ACTIVE 0 sec inserting
mysql tables in use 2, locked 2
5 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 2
MySQL thread id 17811470, OS thread handle 140698752878336, query id 25019811042 x.x.x.x eclp_po1_rw update
REPLACE INTO `eclp_po1`.`_po_main_new` (`id`, `parent_no`, `po_no`, `unit_flag`, `unit_rule`, `bill_of_lading`, `dept_no`, `dept_id`, `dept_name`, `seller_id`, `seller_no`, `seller_name`, `org_id`, `org_no`, `org_name`, `distribution_id`, `distribution_no`, `distribution_name`, `warehouse_id`, `warehouse_no`, `warehouse_name`, `out_warehouse_no`, `out_warehouse_name`, `target_warehouse_id`, `expect_arrival_time`, `po_type`, `po_status`, `po_cancel_status`, `po_sign`, `out_source_no`, `out_po_no`, `out_seller_no`, `difference_remark`, `contacts`, `contacts_address`, `supplier_id`, `supplier_no`, `supplier_name`, `supplier_contacts`, `approval_time`, `approval_user`, `po_dl_result`, `po_dl_message`, `receive_level`, `isv_replenish_type`, `temperature`, `temperature_value`, `create_time`, `products_code`, `receive_box_number`, `products_name`, `update_time`, `create_user`, `update_user`, `yn`, `ts`,
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 245 page no 234753 n bits 384 index po_no of table `eclp_po1`.`_po_main_new` trx id 385752158 lock_mode X locks gap before rec
Record lock, heap no 313 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 16; hex 45504c34343138303834393836363939; asc xxx;;
 1: len 8; hex 80000404aa62df4b; asc      b K;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 245 page no 234753 n bits 384 index po_no of table `eclp_po1`.`_po_main_new` trx id 385752158 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 313 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 16; hex 45504c34343138303834393836363939; asc xxx;;
 1: len 8; hex 80000404aa62df4b; asc      b K;;

*** WE ROLL BACK TRANSACTION (1)

mysql> show create table eclp_po1.po_main \G
*************************** 1. row ***************************
       Table: po_main
Create Table: CREATE TABLE `po_main` (
  `id` bigint(20) NOT NULL COMMENT '主键',
  `parent_no` varchar(50) DEFAULT NULL COMMENT '采购父单号',
  `po_no` varchar(50) NOT NULL COMMENT '采购单号（ECLP采购单号）',
  ...
  PRIMARY KEY (`id`),
  UNIQUE KEY `po_no` (`po_no`) USING BTREE,
  ...

mysql> show create table t_lock \G
*************************** 1. row ***************************
       Table: t_lock
Create Table: CREATE TABLE `t_lock` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` int(11) DEFAULT '0',
  `b` int(11) DEFAULT '0',
  `c` int(11) DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_a` (`a`),
  KEY `idx_b` (`b`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> select * from t_lock;
+----+------+------+------+
| id | a    | b    | c    |
+----+------+------+------+
|  1 |    1 |    1 |    1 |
|  5 |    5 |    5 |    5 |
|  9 |    9 |    9 |    9 |
+----+------+------+------+
3 rows in set (0.00 sec)

drop table `e`;
CREATE TABLE `e` (
  `id` int NOT NULL AUTO_INCREMENT,
  `c`  int DEFAULT NULL,
  `d`  int DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_c` (`c`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4;

insert into e(c,d) values(10,10),(20,20);

insert into e(id,c,d) values(11,11,11);

inser into e(c,d) values(10,10);

Bug #25966845 INSERT ON DUPLICATE KEY GENERATE A DEADLOCK
PROBLEM
-------

When we do a partial rollback of the tuple due to "on duplicate
key update" clause we were not maintaining serilizability,
so another connection waiting on the row could update it and
cause wrong results.

FIX
---

1) During partial rollback ,while deleting the tuple convert implicit
   lock on the tuple to explicit lock ,so that no connection can
   get hold of the tuple during delete. This lock is later inherited
   by the next record.
...

void
row_convert_impl_to_expl_if_needed(
/*===============================*/
 btr_cur_t* cursor, /*!< in: cursor to record */
 undo_node_t* node) /*!< in: undo node */
{
 ulint*  offsets = NULL;
 
  // IODKU 表示 insert duplicate
 /* In case of partial rollback implicit lock on the
 record is released in the middle of transaction, which
 can break the serializability of IODKU and REPLACE
 statements. Normal rollback is not affected by this
 becasue we release the locks after the rollback. So
 to prevent any other transaction modifying the record
 in between the partial rollback we convert the implicit
 lock on the record to explict. When the record is actually
 deleted this lock be inherited by the next record.  */

  // 判读事务隔离级别
 if (!node->partial
     || (node->trx == NULL)
     || node->trx->isolation_level < TRX_ISO_REPEATABLE_READ){
  return;
 }
  
  ...

  // 将隐式锁转换成显式锁
  lock_rec_convert_active_impl_to_expl(block, rec, index,
                offsets,node->trx,heap_no);
}

// 判断是否报错，唯一键冲突时 err = DB_DUPLICATE_KEY
if (err != DB_SUCCESS)
  // 处理异常
  row_mysql_handle_errors
    // 插入记录导致唯一索引冲突，需要回滚
    trx_rollback_to_savepoint
      // 回滚 insert 操作
      row_undo_ins
        // 删除主键索引
        row_undo_ins_remove_clust_rec
          // 5.7.24 中没有该函数，5.7.26 中新函数
          row_convert_impl_to_expl_if_needed
            // 把主键索引记录上的隐式锁转换为显式锁
            lock_rec_convert_impl_to_expl
          // 先进行乐观删除，如果乐观删除失败，后面会进行悲观删除
          btr_cur_optimistic_delete
            // 锁继承
            lock_rec_inherit_to_gap
       // 真正删除
       page_cur_delete_rec