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

数据库 MySQL
online ddl 不会导致死锁,两者最大的区别是 pt-osc 执行 DDL 时产生的主从延迟小,原因是 online ddl 是在主库执行完成后从库开始执行,因此天然存在延迟。

引言

本文介绍一个在 pt-osc 执行期间发生的死锁案例,其实之前的文章 并发 replace into 导致 supremum X 锁与插入意向锁形成死锁 中也分析过相关案例,但由于理解不到位导致根因分析并不全面,因此本文进一步分析该类型的死锁,包括死锁发生的原因与优化方法。

现象

时间:2024-03-13 20:48:29

数据库版本:MySQL 5.7.21

现象:pt-osc 执行 DDL 期间多次发生死锁

分析

死锁日志

------------------------
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)

其中:

  • REPLACE INTO _po_main_new,表明是 pt-osc 的 SQL,其中 SQL 截断;
  • eclp_po1_rw,显示是业务账号,表明对应 pt-osc 增量数据同步阶段;
  • index po_no,加锁索引;
  • info bits 0,表明没有发生标记删除。结合显示 ACTIVE 0 sec inserting,因此判断对应 insert,不是 update;
  • 日志显示间隙锁与插入意向锁冲突导致死锁。

表结构

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,
  ...

其中:

  • index po_no,二级唯一索引

复现

测试数据

数据库版本版本:5.7.24

事务隔离级别:RR

测试表结构

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)

流程

三个事务并发向同一个间隙插入数据。

时刻

session 1

session 2

session 3

1

begin;

replace into t_lock values(2,2,2,2);



2


begin;

replace into t_lock values(3,3,3,3);

blocked


3



begin;

replace into t_lock values(4,4,4,4);

blocked

4

rollback; / commit;



5


Query OK, 1 row affected

Deadlock found

其中:

  • replace 对应 insert;
  • 与 insert 不同之处在于事务 1 提交以后也会触发死锁,原因是事务 2 与 3 持有的间隙锁与事务 1 的回滚操作无关;
  • 研发反馈不存在回滚操作,因此判断提交操作触发死锁。

由于死锁日志与前文相同,因此这里不再展示,但是为了分析死锁,下面分析锁等待的原因。

锁信息

时刻 3 查看事务 1 与 2 的锁信息

图片图片

其中:

  • 事务 2 与 3 的锁信息相同,因此仅展示事务 1 与 2;
  • 事务 1 与 2 均已持有间隙锁,但是事务 2 发生插入意向锁等待;

时刻 3 查看事务 2 与 3 的锁等待信息

图片图片

其中:

  • 显示有三组锁等待,右图中展示事务间锁等待的关系,其中箭头指向表示事务等待的锁;
  • 事务 1 同时阻塞事务 2 与 3;
  • 事务 3 同时等待事务 1 与 2,显然事务 3 指向事务 2 是死锁检测时环中的第一条边,下一条边将从事务 2 指回事务 3;
  • 事务 1 提交或回滚后,事务 2 与 3 同时持有间隙锁,且均等待插入意向锁,因此导致死锁。

原理

pt-osc

online ddl 不会导致死锁,两者最大的区别是 pt-osc 执行 DDL 时产生的主从延迟小,原因是 online ddl 是在主库执行完成后从库开始执行,因此天然存在延迟。

为解决这个问题,pt-osc 支持主从“同时”执行,缺点是全量数据和增量数据可能发生乱序。为解决这个问题,将全量数据中 insert 转换成 insert ignore,将增量数据中 insert / update 转换成 replace into,从而实现数据的最终一致性,缺点是改写 SQL 后加锁变复杂,可能导致死锁。

replace 语句加锁流程

参考【许海波】大佬的文章 REPLACE语句死锁与官方修复剖析,5.7.19 版本中 replace 语句加锁流程见下图。

图片图片

整理加锁规则见下表。

索引类型

是否冲突

加锁类型

主键索引


不加锁



next-key lock

二级唯一键


gap lock



next-key lock

其中:

  • 二级唯一键没有冲突时加 gap lock 最不合理;
  • 5.7.26 中针对该问题进行了修复。

在分析修复实现前首先介绍该修复带来的新问题,具体是 5.7.26 中新增的锁。

不同版本 insert 加锁对比

参考以下两篇文章:

  • 【操盛春】大佬的文章 答读者问:唯一索引冲突,为什么主键的 supremum 记录会加 next-key 锁?
  • 【高鹏】大佬的文章 MySQL:新版本RR模式下特殊的锁行为一列

其中都提到了一个现象,插入二级唯一键时如果发生唯一键冲突,内部回滚删除主键时会给回滚记录加锁,并在回滚完成后将锁继承到下一行,加锁类型是 gap lock。注意如果下一行是 sup 伪列,加锁类型是 next-key lock,从而导致大于当前最大主键的新插入的主键值均无法插入,出现大面积堵塞。

其中的重点是主键删除过程中的以下两步操作:

  • 将隐式锁转换成显式锁
  • 发生锁继承

文中提到这个现象是在 5.7.29+ 版本中发现,5.7.22 中没有该现象。

因此下面测试对比 5.7.24 与 5.7.33 两个版本中 insert 发生唯一键冲突时的加锁规则,其中使用两个事务依次模拟主键冲突与二级唯一键冲突。

由于 insert 没有唯一键冲突时使用隐式锁,可以认为不加锁,因此仅测试唯一键冲突的场景。

测试数据

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);

结果

图片图片

其中:

  • 主键冲突时,5.7.24 next-key lock,5.7.33 record lock。

二级唯一键冲突

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

结果

图片图片

其中:

  • 二级唯一键冲突时,5.7.24 next-key lock,5.7.33 也是 next-key lock;
  • 5.7.33 新增主键索引上的锁,具体是 X 型 next-key lock,且加锁到右边界 supremum,因此大于等于 12 的主键值都无法插入;
  • 5.7.33 主键索引上新增锁的原因是主键回滚,期间先将隐式锁转换成显式锁,然后发生锁继承;
  • 5.7.24 同样主键回滚,但是主键索引上没有锁的原因是使用隐式锁,没有转换成显式锁,因此锁继承时不加锁;
  • 因此判断高版本中针对二级唯一键冲突检查加锁没有优化,针对主键冲突检查加锁粒度减小,针对主键回滚加锁粒度增大。

但是通常主键值自增,因此二级索引加锁粒度变大可能导致死锁增多。

代码分析

下面分析 5.7.26 中修改唯一键冲突时加锁规则对应的 commit,具体是 Bug #25966845 INSERT ON DUPLICATE KEY GENERATE A DEADLOCK。

相关描述如下所示。

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.
...

其中:

  • 问题,insert duplicate 语句在死锁回滚事务时没有保证串行,导致回滚期间其他锁等待事务可以更新数据;
  • 修复,将 delete 持有的隐式锁转换成显式锁,并随后发生锁继承,从而阻塞其他事务在回滚期间更新数据。

release notes 中显示 5.7.26 中该 commit 用于修复 bug,但是暂未根据 commit 找到对应要修复的 bug。

Two sessions concurrently executing an INSERT ... ON DUPLICATE KEY UPDATE operation generated a deadlock. During partial rollback of a tuple, another session could update it. The fix for this bug reverts fixes for Bug #11758237, Bug #17604730, and Bug #20040791. (Bug #25966845)

下面分析该 commit 中的代码实现。

最明显的是新增row_convert_impl_to_expl_if_needed函数,相关代码如下所示。

首先是row_convert_impl_to_expl_if_needed函数的定义。

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);
}

其中:

  • RR 事务隔离级别下调用lock_rec_convert_active_impl_to_expl函数将隐式锁转换成显式锁;
  • RC 事务隔离级别下不转换。

然后是row_convert_impl_to_expl_if_needed函数的调用。

图片图片

其中:

  • 删除主键与二级索引时都调用row_convert_impl_to_expl_if_needed函数,包括 insert、insert duplicate、replace。

到目前为止,锁的粒度在增大,但实际上并非始终如此,具体在唯一性检查时锁的粒度有减小。

首先是主键唯一性检查前加锁,对比 5.7.24 与 5.7.26 代码中row_ins_duplicate_error_in_clust函数。

图片图片

其中:

  • 对于 RR,5.7.24,lock_type = next-key lock;
  • 5.7.26,lock_type = record lock,包括 insert、insert duplicate、replace。

因此主键索引加锁粒度减小,具体是从 next-key lock 减小为 record lock,也就是移除了 gap lock。

然后是二级唯一键唯一性检查前加锁,对比 5.7.24 与 5.7.26 代码中row_ins_sec_index_entry_low函数。

图片图片

其中:

  • 唯一键冲突时,加锁类型不变,保持 next-key lock,包括 insert、insert duplicate、replace;
  • 唯一键不冲突时,5.7.24 中 insert 不加锁,insert duplicate / replace 加 gap lock,5.7.26 中统一不加锁。

因此对于 insert 语句,唯一键冲突时,5.7.26 中未必锁减少,下面测试对于 replace 语句,加锁规则的变化。

不同版本 replace 加锁对比

下表中对比 insert 与 insert on duplicate 的加锁规则。

场景

唯一键不冲突

唯一键冲突

insert

隐式锁

S 型 next-key lock

insert on duplicate(5.7.26-)

X 型 gap lock

X 型 next-key lock

insert on duplicate(5.7.26+)

不加锁

X 型 next-key lock

其中:

  • 主要改动点是将唯一键不冲突时的间隙锁移除,下面测试验证。

操作流程

session 1

session 2

begin;

replace into t_lock values(2,2,2,2);



begin;

replace into t_lock values(3,3,3,3);

5.7.33 二级唯一键不冲突时不加锁。

图片图片

因此,5.7.33 没有冲突时不加锁,有冲突时加锁不变,且主键回滚后锁粒度增大。

总结

回顾 insert 二级唯一键冲突后的异常处理,堆栈如下所示。

// 判断是否报错,唯一键冲突时 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

其中:

  • 删除主键前先将要删除记录上的隐式锁转换成显式锁,作为过渡,用于下一步的锁转移;
  • 然后在真正删除前将要删除记录上的锁转移到下一行记录,加锁类型为 gap lock,其中如果下一行是 sup 伪列,加锁类型修改为 next-key lock。

总结下 5.7.26 中唯一性检查加锁规则的调整:

  • 主键索引,没有唯一键冲突时不加锁,有唯一键冲突时移除了 gap lock,不论隔离级别,全部加 record lock,而不是 next-key lock;
  • 二级唯一索引,没有唯一键冲突时不加锁,不论隔离级别,移除了 gap lock,有唯一键冲突时保持不变,不论隔离级别,依然是 next-key lock;
  • 删除主键索引与二级索引时都需要先将插入时的隐式锁转换成显式锁,因此在删除前锁继承时将产生 gap lock,从而阻塞并发插入。

到这里,可以提出一个问题,那就是加锁规则的调整可以保证数据唯一吗?

参考 #issue 68021 MySQL unique check 的问题,首先解释低版本中加 next-key lock 的原因。

原因是二级唯一索引插入记录时,分为两个阶段,包括唯一性检查与插入操作,因此通过在两阶段之间加锁保证操作的原子性,禁止其他事务在同一间隙中插入数据,从而避免检查没有冲突,但是插入时出现冲突。

文中介绍针对二级索引加 next-key lock 的问题,官方之前做过改动,具体将 next-key lock 改成 record lock,但是导致唯一性约束失效的严重 bug#73170,因此后来又将该 fix revert。

具体如下图所示,其中红色表示 record 已经被删除,蓝色表示未被删除。tuple 中第一个元素表示二级唯一键,第二个元素表示主键。

图片图片

其中:

  • 假如同时插入两个 record  (13000, 99)、( 13000, 120),如果将 next-key lock 改成 record lock;
  • 唯一性检查时分别对 (13000, 100)、(13000, 102)、(13000, 108) ... (13000, 112) 所有的二级索引加 S record lock,insert 时对 (13000, 100) 加 GAP | insert_intention lock;
  • 由于锁不冲突,因此这两个 record 都可以同时插入成功,就造成了unique key 约束失效了。

因此,5.7.26 中针对二级唯一键冲突的场景,加锁规则不变,依然是 next-key lock,因此不会导致唯一性约束失效。

此外,主键唯一性检查时不需要 gap lock 的原因是理论上同一时间二级唯一键可能有重复,而主键不会有重复。

原因是二级索引与主键索引唯一性检查与记录复用判断的标准不同:

  • 二级索引删除后,再次插入相同唯一键时如果主键不同,原记录不可以复用。为了满足 MVCC 的需求,delete-marked record 不能马上删除,因此理论上可能有重复的唯一键;
  • 主键索引删除后,再次插入相同主键时可以直接服用原记录。为了满足 MVCC 的需求,delete-marked record 可以从 undo log 中查到,因此理论上不会有重复的主键。

结论

死锁的根本原因是 replace 语句在二级唯一键不冲突时申请 gap lock。

针对该问题,5.7.26 进行了优化,具体为:

  • 主键索引,没有唯一键冲突时不加锁,有唯一键冲突时移除了 gap lock,不论隔离级别,全部加 record lock,而不是 next-key lock;
  • 二级唯一索引,没有唯一键冲突时不加锁,不论隔离级别,移除了 gap lock,有唯一键冲突时保持不变,不论隔离级别,依然是 next-key lock;
  • 删除主键索引与二级索引时都需要先将插入时的隐式锁转换成显式锁,因此在删除前锁继承时将产生 gap lock,从而阻塞并发插入。

其中前两条减小了加锁粒度,最后一条增大了加锁粒度,commit 显示是为了修复 bug,但是具体 bug 暂未找到。

低版本中二级索引唯一性检查加 next-key lock 的原因是为了保证唯一性检查与插入操作两个阶段之间操作的原子性。

5.7.26 版本中同样唯一键冲突时,主键索引与二级索引加锁类型不同的原因是:

  • 主键索引,record lock,原因是理论上不会有重复的主键;
  • 二级索引,next-key lock,理论上可能有重复的二级唯一键。

参考教程

  • REPLACE语句死锁与官方修复剖析

https://zhuanlan.zhihu.com/p/527813412

  • 答读者问:唯一索引冲突,为什么主键的 supremum 记录会加 next-key 锁?
  • MySQL:新版本RR模式下特殊的锁行为一列
  • Bug #25966845 INSERT ON DUPLICATE KEY GENERATE A DEADLOCK

https://github.com/mysql/mysql-server/commit/066b6fdd433aa6673622341f1a2f0a3a20018043

  • #issue 68021 MySQL unique check 的问题
  • Replace into加锁的探究

https://www.jianshu.com/p/497fd78f0b91

  • 并发 replace into 导致 supremum X 锁与插入意向锁形成死锁
  • pt-online-schema-change的原理解析与应用说明

https://www.cnblogs.com/xinysu/p/6758170.html

责任编辑:武晓燕 来源: 丹柿小院
相关推荐

2023-07-06 08:06:47

LockCondition公平锁

2010-05-20 08:47:21

MySQL数据库

2017-05-03 16:26:24

MySQL并发死锁

2024-04-10 14:27:03

MySQL数据库

2024-04-02 11:22:01

死锁Java并发

2017-02-14 10:00:19

Java开发Lock

2021-03-26 10:40:16

MySQL锁等待死锁

2023-12-08 18:01:25

Java关键字

2018-01-04 10:08:08

2018-05-29 11:44:22

数据库MySQL死锁

2023-07-18 09:24:04

MySQL线程

2017-06-07 16:10:24

Mysql死锁死锁日志

2010-07-06 10:08:57

SQL Server

2023-06-14 08:34:18

Mybatis死锁框架

2024-01-15 08:57:13

MySQL高并发

2010-07-07 13:58:25

SQL Server死

2023-11-09 11:56:28

MySQL死锁

2023-10-11 22:24:00

DubboRedis服务器

2024-01-02 14:17:31

MySQLMDL LOCK语句

2009-08-04 17:57:41

C#线程同步死锁
点赞
收藏

51CTO技术栈公众号