一次调用执行两次insert into_MySQL RC级别下并发insert锁超时问题 - 现象分析和解释...

作者：网易数据库团队

DDB（网易杭研自研的MySQL数据库中间件产品）团队小伙伴发现了一个问题，觉得比较奇怪。于是找到我们，希望解释下。过程中除解释了问题的现象，也通过代码了解了更多的InnoDB DML执行逻辑，还发现了MySQL/InnoDB官方在二级唯一索引冲突检查时加锁行为的反复。本系列打算用三四篇文章来聊聊这个事情。这是第一篇，说清问题并提供解释。

---

问题描述

表dt包含了一个主键，一个复合唯一索引和一个普通索引，存在9条记录。表结构和记录如下：

CREATE TABLE `dt` (
  `ID` int(10) NOT NULL,
  `COUPON_ID` varchar(60) NOT NULL,
  `OPERATION_TYPE` decimal(2,0) NOT NULL,
  `REMAIN_AMOUNT` decimal(8,2) NOT NULL,
  `OPERATION_AMOUNT` decimal(8,2) NOT NULL,
  `OPERATION_DESC` varchar(200) DEFAULT NULL,
  `OPERATION_IP` varchar(30) DEFAULT NULL,
  `OPERATION_ID` varchar(60) DEFAULT NULL,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `detail7_1` (`COUPON_ID`,`OPERATION_DESC`),
  KEY `detail7_2` (`OPERATION_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
insert into dt values(1,1,0,10000.00,10000.00,1,1,1);
insert into dt values(2,1,0,10000.00,10000.00,2,2,2);
insert into dt values(3,1,0,10000.00,10000.00,3,3,3);
insert into dt values(4,1,0,10000.00,10000.00,4,4,4);
insert into dt values(5,1,0,10000.00,10000.00,5,5,5);
insert into dt values(6,1,0,10000.00,10000.00,6,6,6);
insert into dt values(7,1,0,10000.00,10000.00,7,7,7);
insert into dt values(8,1,0,10000.00,10000.00,8,8,8);
insert into dt values(9,1,0,10000.00,10000.00,9,9,9);

分别在2个session起2个事务。事务隔离级别均为RC。

session1-ddb>show session variables like "%isolation%";
+-----------------------+----------------+
| Variable_name         | Value          |
+-----------------------+----------------+
| transaction_isolation | READ-COMMITTED |
| tx_isolation          | READ-COMMITTED |
+-----------------------+----------------+
2 rows in set (0.00 sec)

session2-ddb>show session variables like "%isolation%";
+-----------------------+----------------+
| Variable_name         | Value          |
+-----------------------+----------------+
| transaction_isolation | READ-COMMITTED |
| tx_isolation          | READ-COMMITTED |
+-----------------------+----------------+
2 rows in set (0.01 sec)

session1执行事务t1，删除一条记录后又重新插入该记录r16：

session1-ddb>begin;
Query OK, 0 rows affected (0.00 sec)

session1-ddb>delete from dt where COUPON_ID='1' and OPERATION_DESC='6';
Query OK, 1 row affected (0.01 sec)

session1-ddb>insert into dt values(6,1,0,10000.00,10000.00,6,6,6);
Query OK, 1 row affected (0.00 sec)

t1在未commit情况下，session2执行事务t2，删除与r16相邻的左边记录r15，在重新插入时失败：

session2-ddb>begin;
Query OK, 0 rows affected (0.00 sec)

session2-ddb>delete from dt where COUPON_ID='1' and OPERATION_DESC='5';
Query OK, 1 row affected (0.00 sec)

session2-ddb>insert into dt values(5,1,0,10000.00,10000.00,5,5,5);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

从上面的结果看，在MySQL的RC隔离级别下，并发插入相邻的记录竟然锁超时了。

问题背景

有同学会问，为什么要构造删除一条记录重新插入这样的案例呢？我简单解释下，这是DDB团队开发的另一款产品NDC（类似于阿里的DTS）中的一个行为。在将一个数据库实例中的数据迁移到另一个数据库实例上时，需要分为2步，第一步是迁移全量数据，第二步是回放全量迁移期间产生的增量数据。基于多种因素考虑，全量迁移的数据并非来自与一致性快照，举个例子，比如记录r1开始全量时值为a，在全量迁移期间，它可能被改为b，再被改为c。那么NDC在第一步获取的r1的值可能是a或b或c。取决于update操作和select操作的先后顺序。假设select时值已经是c。第二步需要回放全量期间的MDL操作，对于记录r1，就是要先将记录修改为b，再改为c。虽然这样会有些重复性操作，但却是正确而高效的数据迁移方案。由于r1已经是c了，在ROW格式下，回放将记录从a修改为b的操作显然会失败。为此，不管采用什么样的sql，本质上都需要先删除当前值为c的记录r1，再重新插入置为b的记录r1。为了提高增量回放速度，往往是多线程并发执行，因此就有了上文所说的情况。

问题分析

好了，继续回到问题本身。相信会提出疑问：在执行insert时，由于RC隔离级别下一般情况下没有next-key lock的存在，应该能够进行并行insert才对，为什么并行插入r15和r16两条相邻记录会锁超时呢？ 如果没有前面的delete操作，是不是就没问题了呢？ 我们先来验证后一个问题，结果如下：

session1-ddb>begin;
Query OK, 0 rows affected (0.00 sec)

session1-ddb>insert into dt values(11,1,0,10000.00,10000.00,11,11,11);
Query OK, 1 row affected (0.00 sec)

session2-ddb>begin;
Query OK, 0 rows affected (0.00 sec)

session2-ddb>insert into dt values(10,1,0,10000.00,10000.00,10,10,10);
Query OK, 1 row affected (0.00 sec)

事实证明如果只是单纯地执行插入，确实可以并发。所以似乎跟delete操作有关，InnoDB因为delete-marked记录引发的死锁或锁超时问题很多，看起来我们又遇到了一例。

其实关于这个问题，姜承尧老师的文章http://www.innomysql.com/26186-2/，该文对insert操作的并发性做了比较详细地解释。这里我直接给结论，感兴趣的同学可以看文中的分析过程。

在RC隔离级别下，要让insert操作达到完全的并发执行，需要有个前提条件：
所操作的表没有唯一索引；若存在唯一索引，插入的数据不会引发唯一性冲突。（补充说明下，这里的唯一性索引不包含聚集索引。原因后面再分析。）对于insert操作来说，若发生唯一约束冲突，则需要对冲突的唯一索引加next-key共享读锁。而且还需要对该唯一索引的下一条记录也加next-key共享读锁。

GDB调试

根据该前提条件，符合上文2个案例的结果。而且，我们通过GDB调试，确实是由于在插入r15时，获取了已删的r15锁后，又去获取r16锁时由于该锁被事务t1持有，所以就引发了锁超时。我们对mysqld加了lock_rec_lock断点调试r15的insert操作（insert into dt values(5,1,0,10000.00,10000.00,5,5,5);），该记录的插入过程如下：

1、进行唯一性约束检查，查看是否存在主键值为5的记录：

通过上图可以知道，检查时加的锁模式为1026，即非gap的共享读锁。显然由于我们先删了r15，所以，找到了处于delete-marked状态的旧的r15。 通过heap_no=6, index=0x1b180f88，我们可以知道0x1b180f88代表主键索引。r15在数据页中的heap_no为6。

2、插入主键值为5的记录：

从调用栈信息可以发现，InnoDB并不是插入一条新的记录，而是直接在标为delete-marked的旧记录上执行了update操作，所以加锁时heap_no为6。由于是DML操作，加锁模式为排他写锁。

3、处理完主键索引后，接下来处理唯一性索引。仍然是先进行唯一性检查：

通过index=0x1a0cb488可以知道，已经不是在处理主键索引了。通过row_ins_scan_sec_index_for_duplicate函数可以知道，这是唯一性索引。表中只出在一个唯一性索引，就是UNIQUE KEY `detail7_1` (`COUPON_ID`,`OPERATION_DESC`)。我们还可以知道在该索引中，r15记录对应的heap_no也是6。通过mode=2可以发现，确实加了next-key的共享读锁。也就是对detail7_1索引上表为delete-marked的r15记录加了next-key共享读锁。

4、看看接下来发生了什么：

有趣的事情发生了，又对detail7_1索引上heap_no为7的记录（即r16）加next-key的共享读锁。由于r16这条记录被事务t1已经加了写排它锁，所以显然无法得到满足。通过查询事务和锁的信息也能发现同样的问题：

从图中可得事务t2等待的记录就是r16，而事务t1已经对该记录加了排它锁。

好，从上面这些分析可以知道确实如文章所述，确实是因为在有唯一性约束冲突下，需要获取下一条记录的锁导致。

代码确认

我们在根据gdb打印的调用栈来从源码上确认下，下面是row_ins_scan_sec_index_for_duplicate进行二级索引唯一性冲突检查的逻辑：

注意，这是一个while循环，图中仅展示循环的上半部分，可以发现其调用row_ins_set_shared_rec_lock来共享锁，锁类型lock_type为LOCK_ORDINARY即next-key锁。next-key共享读锁，反映在lock_rec_lock的入参mode上即为2（LOCK_ORDINARY（0）+ LOCK_S（2））。也符合预期。再来看循环的下半部分：

若在唯一性冲突查询时找到了相同的记录（cmp_dtuple_rec()返回0），则通过row_ins_dupl_error_with_rec()函数判断是否是真的冲突了。其判断依据为找到的记录是否为delete-marked，或者该唯一索引是否包含了NULL字段，若不满足任意条件，则意味着发生唯一性冲突。返回duplicate_key错误。但若记录已被标为delete-marked的，代码下滑到while (btr_pcur_move_to_next(&pcur, mtr))，继续对游标上的下一条记录加next-key共享读锁判断唯一性冲突，而正是这一步导致了对r16加锁超时。

问题深究和假设

但姜总的文章中并没有详细说明为什么还要继续加锁取游标上的下一条记录，或许在大神眼里，认为这是显而易见，不值一提。我们不妨假设原因是：

找到了delete-marked记录后，还需要继续的原因是可能还会有更多的delete-marked，甚至最后可能还会出现一条非delete-marked状态的相同记录。这个是很容易理解的，如果在不同的事务中对r16这条记录进行了多次删除和再插入操作，就可能出现所设想的情况。

可是，这个假设有一点解释不通。因为既然是这个理由，那么为什么在对主键索引进行唯一性冲突查询时就不需要通过while循环取下一条记录呢？针对这个疑问，隐约想到主键索引和二级索引在进行insert或update时，行为是不一样的。主键索引倾向于进行inplace更新，而二级索引一般情况下是重写一条新记录。那么大胆再做出这样的假设：

对于主键索引，最多存在一条主键相同的记录，该记录或者是delete-marked状态，或者是普通状态。因为对记录进行不涉及主键字段的update时总是inplace的，不存在delete+insert情况；insert时如果发现了主键相同的delete-marked记录，则直接复用该记录，即insert转为inplace update。而对于二级索引，update时总是执行delete+insert，insert时也不会复用delete-marked状态的记录。

---

本篇详细描述了问题，并引用现有的资料进行了解释。但还没有彻底说明为什么，只是提出一个假设。在下一篇中，主要从代码层来证明这个假设。

原文链接：MySQL RC级别下并发insert锁超时问题 - 现象分析和解释