插入记录导致唯一索引冲突，on duplicate key update 更新主键字段值的加锁情况分析。
作者：操盛春，爱可生技术专家，公众号『一树一溪』作者，专注于研究 MySQL 和 OceanBase 源码。
爱可生开源社区出品，原创内容未经授权不得随意使用，转载请联系小编并注明来源。
本文基于 MySQL 8.0.32 源码，存储引擎为 InnoDB。
1. 准备工作
创建测试表：
CREATE TABLE `t4` (
`id` int unsigned NOT NULL AUTO_INCREMENT,
`i1` int DEFAULT '0',
`i2` int DEFAULT '0',
PRIMARY KEY (`id`) USING BTREE,
UNIQUE KEY `uniq_i1` (`i1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;
插入测试数据：
INSERT INTO `t4` (`id`, `i1`, `i2`) VALUES
(1, 11, 21), (2, 12, 22), (3, 13, 23),
(4, 14, 24), (5, 15, 25), (6, 16, 26);
2. 可重复读
把事务隔离级别设置为 REPEATABLE-READ（如已设置，忽略此步骤）：
SET transaction_isolation = 'REPEATABLE-READ';
-- 确认设置成功
SHOW VARIABLES like 'transaction_isolation';
+-----------------------+-----------------+
| Variable_name | Value |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
+-----------------------+-----------------+
执行以下 insert 语句（唯一索引冲突，更新主键字段值）：
begin;
insert into t4 (id, i1, i2) values (7, 12, 220)
on duplicate key update id = values(id), i2 = values(i2);
查看加锁情况：
select
engine_transaction_id, object_name, index_name,
lock_type, lock_mode, lock_status, lock_data
from performance_schema.data_locks
where object_name = 't4'
and lock_type = 'RECORD'\G
***************************[ 1. row ]***************************
engine_transaction_id | 250933
object_name | t4
index_name | uniq_i1
lock_type | RECORD
lock_mode | X
lock_status | GRANTED
lock_data | 12, 2
***************************[ 2. row ]***************************
engine_transaction_id | 250933
object_name | t4
index_name | uniq_i1
lock_type | RECORD
lock_mode | X
lock_status | GRANTED
lock_data | 13, 3
***************************[ 3. row ]***************************
engine_transaction_id | 250933
object_name | t4
index_name | PRIMARY
lock_type | RECORD
lock_mode | X,REC_NOT_GAP
lock_status | GRANTED
lock_data | 2
***************************[ 4. row ]***************************
engine_transaction_id | 250933
object_name | t4
index_name | PRIMARY
lock_type | RECORD
lock_mode | X
lock_status | GRANTED
lock_data | supremum pseudo-record
***************************[ 5. row ]***************************
engine_transaction_id | 250933
object_name | t4
index_name | PRIMARY
lock_type | RECORD
lock_mode | X,GAP
lock_status | GRANTED
lock_data | 7
***************************[ 6. row ]***************************
engine_transaction_id | 250933
object_name | t4
index_name | uniq_i1
lock_type | RECORD
lock_mode | X,GAP
lock_status | GRANTED
lock_data | 12, 7
insert 语句插入记录，导致唯一索引冲突，更新主键字段值，这个过程加锁有点多，加锁逻辑也比较复杂。
上面查询出来的加锁情况，不是按照加锁先后面顺序展示的。接下来，我们按照加锁的先后顺序，来看看这些记录的加锁逻辑。
1. row、4. row、3. row 这三条记录的的加锁逻辑，和上一期唯一索引冲突，不更新主键字段值的加锁逻辑相同。为了方便阅读，这里把上一期的内容拷贝过来了。
1. row，lock_data = 12,2、lock_mode = X 表示对唯一索引 uniq_i1 中 <i1 = 12, id = 2> 的记录加了排他 Next-Key 锁。
insert 语句执行过程中，先插入记录到主键索引，再逐个插入记录到二级索引。
对于示例 SQL，先插入 <id = 7, i1 = 12, i2 = 220> 的记录到主键索引，因为主键字段值（id = 7）和主键索引中已有记录不冲突，插入成功。
再插入 <i1 = 12, id = 7> 的记录到唯一索引 uniq_i1，插入记录之前，需要先找到插入记录的目标位置。
找到的目标位置是 uniq_i1 中 <i1 = 12, id = 2> 的记录后面。insert 语句发现即将插入的新记录和这条记录的 i1 字段值都是 12。
对于唯一索引，需要进行两项检查，以确认新记录是否和已有记录冲突：
- 第 1 项，检查新记录中是否有哪个字段值为 NULL。
- 第 2 项，检查已发现可能冲突的记录是否已经被标记删除。
进行以上两项检查之前，insert 语句需要对表中可能冲突的记录加锁。
因为唯一索引允许插入唯一字段值为 NULL 的多条记录。为了防止其它事务往可能冲突的记录间隙插入记录，检查过程中，会对可能冲突的记录加 Next-Key 锁。
这和对主键索引中可能冲突的记录的加锁逻辑不同。
示例 SQL 包含 on duplicate key update 子句，如果确认新记录和已有记录冲突，会用这个子句中各字段值更新冲突记录，检查过程中没有加共享锁，而是直接加了排他锁。
以上就是对 uniq_i1 中 <i1 = 12, id = 2> 的记录加排他 Next-Key 锁的原因。
4. row，lock_data = supremum pseudo-record、lock_mode = X 表示对主键索引某个数据页中 supremum 记录加了排他 Next-Key 锁。
supremum 记录和 insert 语句想要插入的记录，似乎隔着十万八千里，怎么还给它加了锁呢？
这个过程有点曲折，我们一步步来看。
insert 语句插入 <id = 7, i1 = 12, i2 = 220> 的记录到主键索引成功之后，接着插入 <i1 = 12, id = 7> 的记录到唯一索引 uniq_i1 中，发现并确认新记录和 uniq_i1 中已有 <i1 = 12, id = 2> 的记录冲突。
新记录和 uniq_i1 中已有记录冲突，插入操作无法继续进行下去了，刚刚插入到主键索引的记录会被删除。
InnoDB 执行 insert 语句之前，会创建一个保存点，删除刚刚插入到主键索引的记录，就是通过回滚到这个保存点实现的。
回滚过程中，删除刚刚插入到主键索引中 <id = 7> 的记录之前，会把这条记录上的隐式锁转换为显式锁，锁模式为 LOCK_X，精确模式为 LOCK_REC_NOT_GAP。
按照 data_locks 表中 lock_mode 字段的显示格式是 X,REC_NOT_GAP。
隐式锁转换为显式锁之后，继续删除 <id = 7> 的记录。
但是，问题又来了，这条记录上现在有显式锁，删除这条记录之后，它上面的锁怎么办呢？
那就是只能顺延了，让它的下一条记录，也就是 supremum 记录继承它的锁。
下一条记录继承锁的时候，只会继承锁模式（LOCK_X），不会继承精确模式（LOCK_REC_NOT_GAP），然后加上自己的精确模式（LOCK_GAP）。
按照 data_lock 表中 lock_mode 字段的显示格式是 X,GAP。
然而，我们从 data_locks 表中查询出来的加锁情况，supremum 记录的 lock_mode 是 X，这又是为什么呢？
这是因为 InnoDB 对 supremum 记录做了特殊处理，所有事务对 supremum 记录加锁，不管原来的精确模式是什么，都会被改为 LOCK_ORDINARY（值为 0），也就是对 supreum 记录加的锁都会变成 Next-Key 锁。
3. row，lock_data = 2、lock_mode = X,REC_NOT_GAP 表示对主键索引中 <id = 2> 的记录加了排他普通记录锁。
回滚操作删除刚刚插入到主键索引中 <id = 7> 的记录之后，insert 语句接下来执行 on duplicate key update 子句的操作，用这个子句中指定的各字段值更新 uniq_i1 的冲突记录对应的表中记录，也就是 <id = 2> 的记录。
更新之前，需要先根据 uniq_i1 的冲突记录中保存的主键字段值，回表读取完整的主键索引记录，也就是读取主键索引中 <id = 2> 的完整记录。
读取记录过程中，需要对主键索引中 <id = 2> 的记录加锁。
按照主键字段值回表查询一条记录，加普通记录锁就可以满足要求。读取记录之后，接下来要更新记录，所以直接加了排他锁。
5. row，lock_data = 7、lock_mode = X,GAP 表示对主键索引中 <id = 7> 的记录加了排他间隙锁。
前面插入 <i1 = 12, id = 7> 的记录到唯一索引 uniq_i1，发现和 uniq_i1 中已有 <i1 = 12, id = 2> 的记录冲突，通过回滚到保存点的操作删除了刚刚插入到主键索引中 <id = 7, i1 = 12, i2 = 220> 的记录。
然后又回表查询了 <id = 2> 的完整记录，准备执行 on duplicate key update 子句的更新操作。
执行更新操作过程中，因为主键字段（id）的值发生了变化（从 2 变为 7），需要先删除主键索引中 <id = 2> 的记录，再插入 <id = 7> 的记录。
插入记录，本来不会显式加锁，示例 SQL 却对新插入的 <id = 7> 的记录加了排他间隙锁。
这是因为 <id = 7> 的记录，插入到主键索引中 <id = 6> 的记录后面，supremum 记录前面，而 supremum 记录被加了排他 Next-Key 锁。
<id = 7> 的记录插入到 supremum 记录前面的间隙，就把这个间隙一分为二了。
为了避免其它事务插入记录到刚刚分裂出来的 <id = 7> 的记录前面的间隙，也需要锁住这个间隙，所以会对 <id = 7> 的记录加间隙锁。
因为是插入操作，所以加了排他锁，综合起来，insert 语句就对新插入的 <id = 7> 的记录加了排他间隙锁。
2. row，lock_data = 13,3、lock_mode = X 表示对唯一索引 uniq_i1 中 <i1 = 13, id = 3> 的记录加了排他 Next-Key 锁。
第二次插入 <id = 7, i1 = 12, i2 = 220> 的记录到主键索引成功之后，接下来插入 <i1 = 12, id = 7> 的记录到唯一索引 uniq_i1 中。
找到插入记录的目标位置（<i1 = 12, id = 2> 的记录后面），insert 语句还是发现这条记录可能和即将插入的新记录冲突。
会再一次进行两项检查，以确认是否真的存在冲突，这个过程中依然要对唯一索引 uniq_i1 中 <i1 = 12, id = 2> 的记录加排他 Next-Key 锁。
加锁过程中，发现之前已经加过同样的锁了，不会重复加锁。然后，发现唯一索引中 <i1 = 12, id = 2> 的记录已经被标记删除，和即将插入的 <i1 = 12, id = 7> 的记录不冲突。
因为唯一索引中允许存在唯一字段值为 NULL 的多条记录，所以，这条记录和新插入记录不冲突，还需要继续检查下一条记录是否和即将插入的记录冲突。
唯一索引 uniq_i1 中 <i1 = 12, id = 2> 的记录的下一条记录，就是 <i1 = 13, id = 3> 的记录。
insert 语句也会对这条记录进行两项检查，检查过程中，会对这条记录加排他 Next-Key 锁。
这就是 insert 语句对唯一索引 uniq_i1 中 <i1 = 13, id = 3> 的记录加排他 Next-Key 锁的原因。
6. row，lock_data = 12,7、lock_mode = X,GAP 表示对唯一索引 uniq_i1 中 <i1 = 12, id = 7> 的记录加了排他间隙锁。
前面对唯一索引 uniq_i1 中 <i1 = 13, id = 3> 的记录进行两项检查之后，发现和即将插入的 <i1 = 12, id = 7> 的记录不冲突，可以继续插入这条记录到唯一索引 uniq_i1 中。
插入记录之前，依然要找到插入记录的目标位置：位于 <i1 = 11, id = 1> 的记录后面，<i1 = 13, id = 3> 的记录前面。
因为 <i1 = 13, id = 3> 的记录被加了排他 Next-Key 锁。插入 <i1 = 12, id = 7> 的记录到这条记录前面，就把它前面的间隙一分为二了。
为了避免其它事务插入记录到刚刚分裂出来的 <i1 = 12, id = 7> 的记录前面的间隙，也需要锁住这个间隙，所以会对 <id = 12, id = 7> 的记录加间隙锁。
因为是插入操作，所以加了排他锁，综合起来，insert 语句就对新插入的 <i1 = 12, id = 7> 的记录加了排他间隙锁。
3. 读已提交
把事务隔离级别设置为 READ-COMMITTED（如已设置，忽略此步骤）：
SET transaction_isolation = 'READ-COMMITTED';
-- 确认设置成功
SHOW VARIABLES like 'transaction_isolation';
+-----------------------+----------------+
| Variable_name | Value |
+-----------------------+----------------+
| transaction_isolation | READ-COMMITTED |
+-----------------------+----------------+
执行以下 insert 语句（唯一索引冲突，更新主键字段值）：
begin;
insert into t4 (id, i1, i2) values (7, 12, 220)
on duplicate key update id = values(id), i2 = values(i2);
查看加锁情况：
select
engine_transaction_id, object_name, index_name,
lock_type, lock_mode, lock_status, lock_data
from performance_schema.data_locks
where object_name = 't4'
and lock_type = 'RECORD'\G
***************************[ 1. row ]***************************
engine_transaction_id | 250935
object_name | t4
index_name | uniq_i1
lock_type | RECORD
lock_mode | X
lock_status | GRANTED
lock_data | 12, 2
***************************[ 2. row ]***************************
engine_transaction_id | 250935
object_name | t4
index_name | uniq_i1
lock_type | RECORD
lock_mode | X
lock_status | GRANTED
lock_data | 13, 3
***************************[ 3. row ]***************************
engine_transaction_id | 250935
object_name | t4
index_name | PRIMARY
lock_type | RECORD
lock_mode | X,REC_NOT_GAP
lock_status | GRANTED
lock_data | 2
***************************[ 4. row ]***************************
engine_transaction_id | 250935
object_name | t4
index_name | uniq_i1
lock_type | RECORD
lock_mode | X,GAP
lock_status | GRANTED
lock_data | 12, 7
和可重复读隔离级别一样，上面查询出来的加锁情况，不是按照加锁先后面顺序展示的。接下来，我们按照加锁的先后顺序，来看看这些记录的加锁逻辑。
1. row，lock_data = 12,2、lock_mode = X，表示对唯一索引 uniq_i1 中 <i1 = 12, id = 2> 的记录加了排他 Next-Key 锁。
这条记录的加锁逻辑和可重复读隔离级别下一样，这里就不赘述了。
有一点需要说明的是，读已提交隔离级别本来不应该加 Next-Key 锁，这里却加了 Next-Key 锁。
这是因为唯一索引中允许存在唯一字段值为 NULL 的多条记录，确认新记录和表中已有记录是否冲突的过程中，为了避免其它事务插入唯一字段值为 NULL 的记录，所以这里加了 Next-Key 锁。
对于示例 SQL，虽然可能存在冲突的是唯一字段（i1）值等于 12 的记录，但是 InnoDB 没有针对唯一索引字段值是 NULL 或不是 NULL 做不同处理，而是简单粗暴的在这个场景下都加 Next-Key 锁。
以下三条记录的加锁逻辑，和可重复读隔离级别下一样，不赘述了。
3. row，lock_data = 2、lock_mode = X,REC_NOT_GAP 表示对主键索引中 <id = 2> 的记录加了排他普通记录锁。
2. row，lock_data = 13,3、lock_mode = X 表示对唯一索引 uniq_i1 中 <i1 = 13, id = 3> 的记录加了排他 Next-Key 锁。
4. row，lock_data = 12,7、lock_mode = X,GAP 表示对唯一索引 uni1_i1 中 <i1 = 12, id = 7> 的记录加了排他间隙锁。
4. 总结
没有需要总结的内容了。
有一个问题，可重复读隔离级别下，insert 语句对刚刚插入到主键索引中 <id = 7> 的记录加了排他间隙锁，为什么不是加排他 Next-Key 锁？
换句话说，就是为什么只锁定这条记录前面的间隙，而不锁定这条记录本身。
欢迎评论区留言交流~
