引言

本文继续前一篇文章 MySQL insert 语句加锁分析（上）分析 insert 语句在遇到冲突键时的加锁流程。

其中【遇到冲突键】的场景根据冲突数据的状态可以分为以下四种：

与已有数据冲突；
与未提交插入冲突；
与未提交删除冲突；
与已提交未 purge 删除冲突。

由于个人能力有限，以下内容可能有误，欢迎批评指正。

准备工作

测试数据

数据库版本：5.7.24

事务隔离级别：RR

测试数据

mysql> show create table t_lock G
*************************** 1. row ***************************
       Table: tt
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=10 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> insert into t_lock(id, a, b, c)  values(1, 1, 1, 1),(5, 5, 5, 5),(9, 9, 9, 9);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

其中：

测试表中有三个索引，包括主键索引、二级唯一索引、二级非唯一索引。

查看数据

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)

下面测试并分析在什么场景下 insert 需要加锁。

场景

参考小孩子4919 大佬的文章两条一样的INSERT语句竟然引发了死锁？，insert 语句正常情况下使用隐式锁，因此不会创建锁结构，但是特殊场景下需要加锁，具体包括以下三种场景：

待插入记录的下一条记录上已经被其他事务加了间隙锁时，发生插入意向锁等待；
遇到冲突键时，加锁类型与具体场景有关；
外键检查时，由于不推荐使用外键，因此本文不做介绍。

其中【遇到冲突键】的场景根据冲突数据的状态又可以分为以下四种：

与已有数据冲突；
与未提交插入冲突；
与未提交删除冲突；
与已提交未 purge 删除冲突。

显然，如果表中没有唯一键，加锁场景只有一种，就是存在间隙锁导致插入意向锁等待。

下面对比测试 insert 语句在不同场景下加锁的差异，调试过程中在lock_rec_lock函数设置断点。

由于篇幅原因，全文拆分为两篇文章，本文是第二篇文章，分析第二种场景，也就是考虑唯一索引的场景。

与已有数据冲突

测试

主键冲突，二级唯一键不冲突

mysql> insert into t_lock(id,a,b,c) values(5,3,3,3);
ERROR 1062 (23000): Duplicate entry '5' for key 'PRIMARY'

事务信息

其中：

测试显示主键冲突时持有 S 型 next-key lock id=5；
测试显示二级唯一键冲突时持有 S 型 next-key lock a=5，因此加锁规则相同。

堆栈信息

调用加锁函数lock_rec_lock一次。

其中：

row_ins_duplicate_error_in_clust函数调用row_ins_set_shared_rec_lock函数加锁；
row_ins_set_shared_rec_lock函数入参type=0表示 next-key lock；
lock_rec_lock函数入参impl=false表示显式锁。

row_ins_set_shared_rec_lock函数返回后，继续执行。

其中：

调用row_ins_dupl_error_with_rec函数判断是否与已有数据冲突；
判断数据冲突，将err从DB_SUCCESS_LOCKED_REC修改为DB_DUPLICATE_KEY，表示判重前加锁成功，判重后数据冲突。

发现数据冲突后抛出异常后进行处理。

其中：

对于 insert duplicate / replace into 语句，跳过异常继续插入其他索引；
对于 insert 语句，继续向外层抛出异常并处理。

分析

不同于非唯一索引，唯一索引在插入索引记录之前需要进行唯一性检查，以主键索引为例，堆栈如下所示。

// 索引中插入记录
row_ins_index_entry_low
  // 将 cursor 移动到最后一个小于等于插入值的位置，称为当前记录
  btr_cur_search_to_nth_level(PAGE_CUR_LE)
  // 唯一性检查
  row_ins_duplicate_error_in_clust
    // 如果当前记录索引中相同值的列数大于等于索引的唯一键值数量，可能存在唯一键冲突
    if (cursor->low_match >= n_unique)
      // 给当前记录加 S 锁，可能会发生锁等待
      row_ins_set_shared_rec_lock(LOCK_S)
        // 隐式锁检查，与插入意向锁无关
        lock_clust_rec_read_check_and_lock
          // 将隐式锁转换成显式锁
          lock_rec_convert_impl_to_expl
          // 如果上面的隐式锁转化成功，此处加S锁将会等待，直到活跃事务释放锁
          lock_rec_lock
      // 加锁完成后，最终判断是否唯一键冲突，其中 rec 表示当前记录，entry 表示新插入的值
      if (row_ins_dupl_error_with_rec(rec, entry)
        // 如果唯一键冲突，返回报错
        err = DB_DUPLICATE_KEY;
  // 先进行乐观插入，如果乐观插入失败，后面会进行悲观插入
  btr_cur_optimistic_insert

其中：

对于 insert 语句，search mode = PAGE_CUR_LE，因此将游标移动到最后一个小于等于插入值的位置；
两次判断唯一键是否冲突，其中：

第一次判断冲突后加锁，冲突条件是当前记录索引中相同值的列数大于等于索引的唯一键值数量；
第二次最终判断冲突后返回报错，冲突条件是索引值相同且不包括 NULL 且未标记删除，表明先加锁后判断是否冲突。

主键索引与二级唯一索引唯一性检查时加锁主要有三点区别：

如果唯一键冲突，主键索引加锁一次，二级唯一索引循环加锁；
如果唯一键冲突，主键索引的 lock_type 与事务隔离级别有关，二级唯一索引的 lock_type 统一为 next-key lock；
如果唯一键不冲突，且 SQL 是 insert duplicate，主键索引不加锁，二级唯一索引加 X 型 gap lock。

索引插入操作中在实际插入之前唯一性检查相关流程图如下所示。

其中主键索引插入时加锁规则见下图，分别判断 lock_type 与 lock_mode，其中 lock_type 与事务隔离级别有关。

二级索引插入时加锁规则见下图，其中 lock_type 与事务隔离级别无关。

因此对于 RR 下 insert 语句，当主键冲突或二级唯一键冲突时，加锁类型都是 S 型 next-key lock。

判重逻辑见下图，其中有三种场景认为不冲突，一种场景认为冲突。

其中：

主键索引与二级索引的判重条件不同，原因是主键索引不允许为空，而唯一索引允许为空；
因此主键相同且未标记删除时认为数据冲突。

最后是唯一键冲突后的异常处理，堆栈如下所示。

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

其中：

不同与主键索引，二级索引唯一键冲突回滚时需要先删除新插入的主键；
回滚操作中删除主键时，5.7.24 版本中直接删除，而 5.7.26 版本中会先将该记录上的隐式锁转换成显式锁，具体将在下一篇文章中分析。

与未提交插入冲突

测试

操作流程

time	session 1	session 2
1	begin; insert into t_lock(id,a,b,c) values(3,3,3,3);
2		begin; insert into t_lock(id,a,b,c) values(3,4,3,3); blocking

其中：

主键冲突，二级唯一键不冲突

事务信息

其中：

事务 1 的隐式锁被转换成显式锁；
事务 2 由于主键冲突等待 S 型 next-key lock。测试显示唯一键冲突时加锁规则相同。

堆栈信息

首先将已有的隐式锁转换成显式锁。

然后自身加锁时发生锁等待。

分析

前文提到，唯一性检查时先加锁后判断是否重复。

  // 唯一性检查
  row_ins_duplicate_error_in_clust
    // 如果当前记录索引中相同值的列数大于等于索引的唯一键值数量，可能存在唯一键冲突
    if (cursor->low_match >= n_unique)
      // 给当前记录加 S 锁，可能会发生锁等待
      row_ins_set_shared_rec_lock(LOCK_S)
        // 隐式锁检查，与插入意向锁无关
        lock_clust_rec_read_check_and_lock
          // 将隐式锁转换成显式锁
          lock_rec_convert_impl_to_expl
          // 如果上面的隐式锁转化成功，此处加S锁将会等待，直到活跃事务释放锁
          lock_rec_lock

其中：

insert 操作插入成功后申请隐式锁，加锁类型是 X 型 record lock；
判重前加锁时将活跃事务持有隐式锁，因此将其转换成显式锁，并等待 S 型 next-key lock。

与未提交删除冲突

测试

操作流程

time	session 1	session 2
1	begin; delete from t_lock where id=5;
2		begin; insert into t_lock values(5,6,5,5); blocking

其中：

两个线程先后删除与插入同一条数据，其中主键冲突。

时刻 1 事务信息

其中：

delete 语句查询数据期间加锁，加锁类型是 id = 5 record lock；
delete 语句查询数据期间不加锁，其中主键索引由于已加锁因此不需要加锁，二级索引使用隐式锁。

时刻 2 事务信息

其中：

info bits 32 表示数据已标记删除，按照唯一键判重的逻辑，理论上不冲突；
事务 2 insert 等待 S 型 next-key lock，现象和与未提交插入冲突时相同；
主键加锁时发生锁冲突，因此二级索引的隐式锁还没有被转换成显式锁。

当前数据库版本 5.7.24，但是对于 5.7.33 版本，当与未提及删除冲突时加锁类型不同。

其中：

事务 2 insert 等待 S 型 record lock，而不是 S 型 next-key lock；
原因是 5.7.26 版本中针对唯一性检查加锁类型进行了优化，具体与前文中回滚操作中删除主键时先将该记录上的隐式锁转换成显式锁有关，具体将在下一篇文章中分析。

堆栈信息

首先同样将已有的隐式锁转换成显式锁。

然后同样自身加锁时发生锁等待。

但是按照唯一键判重的逻辑，理论上不冲突。

其中：

手动调用判重函数row_ins_dupl_error_with_rec，显示 insert 与已标记删除的记录不冲突；
发生锁等待的原因是 delete 事务未提交，因此持有的隐式锁未释放，从而被转换成显式锁后导致锁冲突。

那么，如果 delete 事务已提交，还会发生锁等待吗？下面进行测试。

与已提交未 purge 删除冲突

测试

操作流程

time	session 1	session 2
1	begin; delete from t_lock where id=5; commit;
2		begin; insert into t_lock values(5,6,5,5); non-blocking

其中：

测试数据库使用 5.7.33-debug；
测试开始前执行set global innodb_purge_stop_now=1;语句停止 purge 线程；
两个线程先后删除与插入同一条数据，其中主键冲突，其中事务 1 删除操作提交，但是没有被 purge；
事务提交后持有的锁释放，按照唯一键判重的逻辑，唯一键冲突时对于标记删除的记录，认为不冲突，因此没有发生锁等待。

时刻 2 事务信息

其中：

事务 2 insert 等待 S 型 record lock，原因是数据库版本 >= 5.7.26；
纯 insert 时事务显示没有锁，两者不同的原因是 delete 已提交未 purge 时数据查询会发现重复记录，因此需要加锁，但是判重时认为不冲突。

知识点

插入操作执行流程

参考文章 MySQL · 源码分析 · 一条insert语句的执行过程，insert 语句的执行堆栈如下所示。

mysql_parse
  mysql_execute_command
    mysql_insert // 执行 insert
      write_record
        handler::ha_write_row // 调用存储引擎的接口
          ha_innobase::write_row  // innodb 存储引擎
            row_mysql_convert_row_to_innobase // 将记录格式从 MySQL 转换成 InnoDB
            row_ins_step // 插入记录
              while (node->index != NULL) // 遍历所有索引，向每个索引中插入记录
                row_ins_index_entry_step // 向索引中插入记录
                  row_ins_clust_index_entry // 插入主键索引
                    row_ins_clust_index_entry_low
                
                      btr_cur_search_to_nth_level // 将 cursor 移动到索引上待插入的位置
                      row_ins_duplicate_error_in_clust // 唯一性检查
                        row_ins_set_shared_rec_lock // 加锁
                        row_ins_dupl_error_with_rec // 判重
                
                      btr_cur_optimistic_insert // 乐观插入
                        lock_rec_insert_check_and_lock // 插入意向锁冲突检查
                        page_cur_tuple_insert // 真正插入
                      btr_cur_pessimistic_insert // 悲观插入
                
                  row_ins_sec_index_entry // 插入二级索引
                dict_table_get_next_index // 获取下一个索引

其中：

对于唯一索引，插入前先进行唯一性检查，期间先加锁后检查；
唯一性检查通过后插入前先进行插入意向锁冲突检查；
没有唯一键冲突，且没有插入意向锁冲突时最终插入，插入的位置是Inserts a record next to page cursor。

插入操作游标定位

参考操盛春大佬的文章 MySQL 自适应哈希索引（一）构造，对于 select、insert、update、delete，定位记录时，搜索条件和叶子结点中的记录比较，会产生两个边界，左边为下界，右边为上界。

定位记录时产生的下界、上界示意图如下所示。定位记录过程中，下界和上界会不断向目标记录靠近，最终下界或上界的其中一个会指向目标记录，且上下界的距离差为 1。

page_cur_search_with_match函数用于在叶子节点中定位记录，主要使用二分法实现。

  if (mode <= PAGE_CUR_GE) {
    page_cur_position(up_rec, block, cursor);
  } else {
    page_cur_position(low_rec, block, cursor);
  }

其中：

函数执行结束时 low_rec 和 up_rec 是临近的两个 record，称为下界、上界，其中low_rec <= tuple <= up_rec，根据 search mode 决定返回 low_rec 或 up_rec 作为 cusor rec；
low_rec 和 up_rec 中保存当前记录与查询记录匹配的字段数量，分别对应btr_cur_t结构体成员 low_match 与 up_match。

因此对于 insert 语句，定位要插入的位置时search mode = PAGE_CUR_LE：

返回最后一条小于等于插入值的记录 low_rec，新记录将插入到 low_rec 的右侧；
low_match 保存 cusor rec 或 cusor rec 上一条记录的相同字段数量，up_match 保存 cusor rec 下一条记录的相同字段数量。

定位小于等于 3 的记录时，二叉查询的过程见下图，最终返回最后一个小于等于 3 的记录，cursor 指向插入记录之前的记录。

因此，插入操作中的两步加锁操作【插入意向锁冲突检查】与【唯一性检查】时加锁规则不同。

插入意向锁冲突检查，判断 cusor rec 下一行记录上是否持有间隙锁。

//  storage/innobase/lock/lock0lock.cc/lock_rec_insert_check_and_lock

  // 获取下一条记录
  const rec_t*  next_rec = page_rec_get_next_const(rec);
  
  // 间隙锁
  const ulint type_mode = LOCK_X | LOCK_GAP | LOCK_INSERT_INTENTION;

  // 判断是否与插入意向锁冲突，也就是检查是否有间隙锁
  const lock_t* wait_for = lock_rec_other_has_conflicting(
        type_mode, block, heap_no, trx);

唯一性检查，对于唯一索引，如果有左完全匹配或者右完全匹配记录，加锁后判重。

//  storage/innobase/row/lock0ins.cc/row_ins_duplicate_error_in_clust
  
  n_unique = dict_index_get_n_unique(index);

  if (!index->allow_duplicates /* allow_duplicates表示允许重复行而不报错 */
      && n_uniq /* 唯一索引 */
     /* 通过二分法搜索记录，有左完全匹配或者右完全匹配记录 */
      && (cursor->up_match >= n_uniq || cursor->low_match >= n_uniq)) {

      /* Note that the following may return also
      DB_LOCK_WAIT */

      // 主键判重的主函数
      // 主键冲突时加锁
      err = row_ins_duplicate_error_in_clust(
        flags, cursor, entry, thr, &mtr);
    }
  }

其中：

判断上下界是否完全匹配记录的条件是up_match >= n_uniq || low_match >= n_uniq，其中出现 n_uniq 字段；
对于主键索引，n_uniq 表示主键索引的列数，对于二级索引，n_uniq 表示二级索引的列数，注意不包括其中保存的主键。

唯一性检查加锁时出现两个分支，其中加锁行与加锁类型均不同。

//  storage/innobase/row/lock0ins.cc/row_ins_duplicate_error_in_clust

  if (cursor->low_match >= n_unique)
    
    rec = btr_cur_get_rec(cursor);
    lock_type =
      trx->isolation_level <= TRX_ISO_READ_COMMITTED
      ? LOCK_REC_NOT_GAP : LOCK_ORDINARY;
    row_ins_set_shared_rec_lock(lock_type, rec)

  if (cursor->up_match >= n_unique)
    
    rec = page_rec_get_next(btr_cur_get_rec(cursor));
    row_ins_set_shared_rec_lock(lock_type, LOCK_REC_NOT_GAP)

其中：

如果下界是重复记录，给 cursor rec 加锁，lock_type = next-key lock，判断原因是可能存在多条重复记录；
如果上界是重复记录，给 cursor rec 下一行加锁， lock_type = record lock，判断原因是只有一条重复记录，测试中没有走过这个分支。

二级索引循环加锁

主键与二级唯一键插入时都可以将插入转为原地更新从而复用 delete-mark 的记录，但是也有区别：

主键，当主键索引值相同时就可以原地更新；
二级唯一键，只有当二级唯一索引 + 主键索引值完全相同时才可以原地更新。

对于二级唯一键，唯一性检查用的是二级唯一索引，而判断是否可以原地更新用的是二级唯一索引 + 主键索引。

因此，理论上可能同时存在多个二级唯一索引相同但主键不同的 delete-mark 记录。所以为了保证唯一性，插入二级唯一键时需要给所有被 delete-mark 的 rec_t 以及第一个 sk 不相同的 rec_t 都加上 next-key lock。

引用阿里云的内核月报中提供的一个场景，注意仅限理论上，本人暂未找到复现的思路。

比如只包含 pk, sk 的一个 table。其中 pk 指主键，sk 指二级唯一键。

已经存在的二级索引记录 <1, 1>, <4, 2>, <10(delete-mark), 3>, <10(d), 8>, <10(d), 11>, <10(d), 21>, <15, 9> 需要插入二级索引<10, 6>, 那么就需要给<10, 3>, <10, 8>,<10,11>,<10,21>, <15, 9> 都加上next-key lock。

注意: 这里 <15, 9>也需要加上 next-key lock, 为的是保证像 <10, 100> 这样的 record 也不允许插入的. 但是如果这里 <15, 9> 是 <15000, 9> 那么这里被锁住的 gap 区间就非常非常大了。

因此二级唯一键唯一性检查时需要循环加锁，如果遇到相同唯一键的多个删除操作，将持续向后加锁。

那么，为什么插入主键不需要循环加锁呢？

原因是主键与二级唯一键判定 delete-mark 是否可以 update-in-place 的规则不同。

对于二级唯一键，如果二级唯一键相同但主键不同，二级唯一索引不可以 update-in-place delete-mark record，因此可能存在多条二级唯一键相同的记录；
对于主键，如果主键相同，主键索引就可以 update-in-place delete-mark record，因此最多存在一条主键相同的记录。

插入操作加锁流程

参考文章 MySQL 死锁套路：一次诡异的批量插入死锁问题分析原创与 insert 加锁分析，插入操作的加锁流程图如下所示。

其中：

先对插入的间隙加插入意向锁，其中判断插入位置的下一条记录上是否有间隙锁；
如果没有间隙锁，表明可以插入，插入索引时判断当前索引是否是唯一键，否则发生插入意向锁等待；
如果是唯一键，表明需要进行唯一性冲突检查，其中先加锁后判重，否则插入成功；
如果存在相同键值，判断该键值是否已加锁；
如果有锁，表明该记录正在处理（新增、删除或更新），且事务还未提交，加 S 锁等待，其中先将存在的隐式锁转换显式锁；
如果没有锁，判断该记录是否被标记为删除；
如果标记为删除，说明事务已经提交，还没来得及 purge，这时加 S 锁等待，否则插入报错唯一键冲突；
注意插入成功后对索引记录加 X 型 record lock，默认是隐式锁。

重要函数

lock_rec_insert_check_and_lock，实现插入意向锁冲突检查
lock_rec_inherit_to_gap，实现锁继承
lock_update_delete，调用锁继承
lock_rec_inherit_to_gap_if_gap_lock，实现锁分裂
lock_update_insert，调用锁分裂
row_ins_duplicate_error_in_clust，实现唯一性检查
row_ins_dupl_error_with_rec，实现唯一键判重
lock_rec_convert_impl_to_expl，隐式锁转换成显式锁
row_convert_impl_to_expl_if_needed，5.7.26 中新增的函数

结论

insert 语句插入的位置是Inserts a record next to page cursor，而定位 cursor 时search mode = PAGE_CUR_LE，返回最后一个小于等于插入记录的位置。

insert 语句正常情况下使用隐式锁，在不考虑唯一索引的前提下，二级非唯一索引插入操作中可能持有的锁包括：

插入意向锁，可能发生锁等待；
间隙锁，可能发生锁分裂，注意回滚操作中不会发生锁继承；
记录锁，可能将隐式锁转换成显式锁，如果在插入成功以后有其他事务可能存在锁冲突。

如果考虑唯一索引，可能新增锁的原因是唯一性检查，原因是先加锁后判重，具体加锁类型与数据库版本有关。

对于 RR 下 insert 语句，当发生主键冲突或二级唯一键冲突时：

< 5.7.26，加锁类型都是 S 型 next-key lock；
<= 5.7.26，加锁类型都是 S 型 record lock。

其中唯一键冲突时，主键索引加锁一次，二级唯一索引循环加锁直到数据不冲突。

需要注意的是不同版本数据库的加锁规则不同：

回滚操作中删除主键时实现不同，5.7.24 版本中直接删除，而 5.7.26 版本中会先将该记录上的隐式锁转换成显式锁；
唯一性检查时加锁类型类型不同，5.7.24 是 S 型 next-key lock，5.7.26 是 S 型 record lock。

下面是遇到过的与 insert 有关的死锁案例。

案例	现象	分析
MySQL 并发 insert 导致间隙锁与插入意向锁形成死锁	三个事务 insert 唯一键冲突，其中一个回滚	同时申请插入意向锁，又均持有 S 型 gap lock
两条一样的INSERT语句竟然引发了死锁？	两个事务 insert 唯一键冲突，其中一个向间隙中插入另一条记录	等待中的锁阻塞新申请的锁
MySQL update 后 insert 导致死锁	两个事务均更新不存在的记录后插入	更新不存在的记录时持有 X 型 gap lock
MySQL 从一个死锁案例到锁分裂	两个事务分别更新存在与不存在的记录后插入	更新不存在的记录时持有 X 型 gap lock