深入剖析MySQL并发Insert唯一索引时遇到的超时与死锁问题，并提供应用层重试机制等优化方案，提升数据库并发性能。
原文标题：多并发Insert唯一索引引发的超时和死锁问题
原文作者：牧羊人的方向
冷月清谈：
怜星夜思：
2、文章中提到应用层增加重试机制可以解决超时和死锁问题，那么重试机制应该如何设计，才能保证最终数据的一致性，并且避免无限重试导致系统雪崩？
3、除了文章中提到的重试机制和避免长事务，还有哪些其他的数据库或架构层面的优化手段，可以有效缓解高并发Insert唯一索引带来的性能瓶颈和锁冲突？
原文内容
在高并发数据库应用中，“先查询判断是否存在，再决定执行更新或插入”是一种极其常见的业务模式，通常被称为“upsert”逻辑。然而，在数据库的并发控制机制下，这种看似简单的逻辑隐藏着复杂的锁竞争风险，极易引发锁等待或死锁问题。
1、业务场景
1）环境配置
数据库运行版本为MySQL 8.0.25版本，存储引擎为InnoDB、事务的隔离级别为READ-COMMITTED。
2）业务表结构
create table t1(
id int autoincrement,
c1 int not null default 0,
c2 varchar(20),
primary key(id),
unique index uniq_index1(c1)
);
-
主键（Primary Key）：id，自增整数。InnoDB的表是索引组织表（IOT），数据行本身存储在聚簇索引（Clustered Index）中，即主键索引。因此，对行的访问和锁定必然涉及主键索引。
-
唯一索引（Unique Index）：uniq_index1(c1)。业务逻辑围绕c1=xx这个条件展开，而c1上的唯一索引是查询和保证数据唯一性的关键路径。唯一索引的存在会影响SELECT … FOR UPDATE和INSERT的加锁行为。
在索引设计上InnoDB存储引擎的二级索引（包括唯一索引）在叶子节点存储的是索引列的值+对应行的主键值。
3）业务模型
业务上同一个session有两个事务：事务1先select…for update查询唯一索引，如果查到记录则直接update，如果没有查到记录，则insert表唯一键值；事务2删除事务1中操作的记录。单个会话中的执行逻辑如下：
--事务1 BEGIN; 开启事务。 SELECT c1,c2 FROM t1 WHERE c1=xx FOR UPDATE; --目的是以排他方式锁定c1=xx的记录。分支逻辑：
IF 查询到记录：则执行UPDATE操作。 UPDATE t1 SET c2 = ‘新值’ WHERE c1=xx; 的操作 。该UPDATE会在SELECT … FOR UPDATE已持有的锁基础上，对同一行再次进行锁定。
ELSE 未查到记录：则执行INSERT操作，语句为 INSERT INTO t1(c1, c2) VALUES (xx, ‘aa’);。
COMMIT; 提交事务，释放所有锁。
–事务2
BEGIN;
DELETE FROM t1 WHERE c1=xx; 删除刚由事务1处理过的记录。
COMMIT;
2、MySQL中锁机制
-
记录锁（Record Lock）：行锁，锁定单个索引记录。例如，SELECT … WHERE c1 = xx FOR UPDATE如果找到了记录，就会在该记录的uniq_index1索引上施加一个记录锁。
-
间隙锁（Gap Lock）：间隙锁锁定一个索引记录之间的范围，或者第一个索引记录之前、最后一个索引记录之后的范围。它的主要目的是防止其他事务在这个“间隙”中插入新的记录，从而避免“幻读”。在READ-COMMITTED下的常规扫描中被禁用。
-
临键锁（Next-Key Lock）：临键锁是记录锁和间隙锁的结合体，它锁定一个索引记录以及该记录之前的间隙。在REPEATABLE-READ隔离级别下默认的锁定策略。
-
插入意向锁（Insert Intention Lock）：一种特殊类型的间隙锁，由INSERT操作在真正插入数据行之前设置 。它表示一个事务“意图”向某个索引间隙中插入数据。多个事务可以同时在同一个间隙上持有插入意向锁，只要它们插入的位置不同。但是，插入意向锁与间隙锁和临键锁是不兼容的。
-
Supremum Record：表示索引中大于任何实际存在的键值的虚拟位置，在每个索引页（B+树叶子节点）的末尾，表示"正无穷大"。当事务执行Insert时，InnoDB需要锁住当前索引页中键值到Supremum之间的间隙。
READ-COMMITTED与REPEATABLE-READ级别在锁行为上的关键差异在于对间隙锁（Gap Lock）的限制：
-
禁用搜索和索引扫描的间隙锁：在READ-COMMITTED级别下，间隙锁仅用于外键约束检查和重复键检查 。这意味着普通的SELECT … FOR UPDATE范围查询，不会阻止其他事务在查询范围内插入新数据。
-
非匹配行的记录锁提前释放：对于SELECT … FOR UPDATE或UPDATE语句，如果某行记录不满足WHERE条件，InnoDB会在评估完该行的WHERE条件后，立即释放该行上的记录锁 。这提高了并发度。
-
半一致性读（Semi-Consistent Read）：对于UPDATE语句，InnoDB会进行“半一致性”读取，如果一行被其他事务锁定，InnoDB会返回该行最新的已提交版本给MySQL服务器层，以判断其是否匹配WHERE条件。如果不匹配，则跳过该行，从而避免不必要的锁等待。
根据业务模型，SELECT c1, c2 FROM t1 WHERE c1 = xx FOR UPDATE 是事务的起点。由于c1列上有唯一索引uniq_index1，在READ-COMMITTED下，其行为因查询条件和索引类型而异。
-
当c1=xx的记录存在时： InnoDB会直接在uniq_index1上找到对应的索引记录，并对其施加一个排他记录锁（X Record Lock）。由于是在READ-COMMITTED级别下对唯一索引的唯一值查询，不会产生间隙锁 。同时，它也会在主键索引（聚簇索引）的相应记录上加锁，因为数据行本身存储于此。查询视图data_locks如下，能看到uniq_index1和primary主键两条锁记录X,REC_NOT_GAP
mysql> select * from performance_schema.data_locks where object_name='lock_tb1';
+--------+------------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+-------------+-----------------------+-----------+---------------+-------------+-----------+
| ENGINE | ENGINE_LOCK_ID | ENGINE_TRANSACTION_ID | THREAD_ID | EVENT_ID | OBJECT_SCHEMA | OBJECT_NAME | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+--------+------------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+-------------+-----------------------+-----------+---------------+-------------+-----------+
| INNODB | 139896888152000:2125:139896145164080 | 3542042 | 82 | 33 | tango | lock_tb1 | NULL | NULL | NULL | 139896145164080 | TABLE | IX | GRANTED | NULL |
| INNODB | 139896888152000:1063:5:3:139896145161088 | 3542042 | 82 | 33 | tango | lock_tb1 | NULL | NULL | uniq_index1 | 139896145161088 | RECORD | X,REC_NOT_GAP | GRANTED | '1002', 2 |
| INNODB | 139896888152000:1063:4:3:139896145161432 | 3542042 | 82 | 33 | tango | lock_tb1 | NULL | NULL | PRIMARY | 139896145161432 | RECORD | X,REC_NOT_GAP | GRANTED | 2 |
+--------+------------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+-------------+-----------------------+-----------+---------------+-------------+-----------+
3 rows in set (0.00 sec)
-
当c1=xx的记录不存在时：在这种情况下，SELECT … FOR UPDATE 查询返回一个空集。因为没有找到任何记录，所以该语句不会施加任何记录锁或间隙锁。事务会继续执行，进入INSERT分支。
1）普通INSERT成功
在插入的新行上设置一个排他记录锁（X Record Lock）。这个锁是索引记录锁，不是临键锁，因此它不会阻止其他事务在这个新插入行之前的间隙中插入数据 。
2）INSERT遇到重复键错误（唯一键冲突）
当INSERT操作因为唯一键（主键或唯一索引）冲突而失败时，该事务会在导致冲突的已存在的索引记录上获取一个共享锁（S锁） 。这个S锁的目的是为了让当前事务能够判断记录的状态。但如果该记录已被其他事务以X模式锁定，获取S锁的过程可能需要等待。
3）INSERT … ON DUPLICATE KEY UPDATE
当发生重复时，它会对重复的主键值加排他记录锁（X Record Lock），或对重复的唯一键值加排他临键锁（X Next-Key Lock）
DELETE操作类似于UPDATE，会在要删除的每一行上加排他锁（X锁）。同时会根据唯一索引找到的主键值，在主键索引对应记录上加X锁。在READ-COMMITTED下，对于使用唯一索引的等值删除，它锁定具体的记录。与REPEATABLE-READ的区别是不会有任何间隙锁。
3、并发场景下的锁等待分析
锁等待超时发生在事务A持有了事务B所需的锁，事务B等待该锁释放，但等待时间超过了innodb_lock_wait_timeout（默认50秒）的设置，导致事务B被回滚。
当表t1中已存在一条c1=xx的记录时，如果事务A先访问资源，事务B后访问，在事务A未释放资源过程中，会触发锁等待。资源等待关系如下所示：
会话A (持有锁) 会话B (等待锁)| |
|— X锁 on c1=10 —|—> 尝试获取X锁，被阻塞
| |
事务处理中… |
| |
COMMIT (释放锁) |—> 如果在此之前超时（50秒），则B回滚
|—> 如果在此之前A提交，则B获得锁继续
-
时刻T0：会话A（Session A）开启事务，执行 SELECT … FOR UPDATE WHERE c1=xx。成功获取到该行在唯一索引和主键上的排他记录锁（X锁）。
-
时刻T1：会话B（Session B）开启事务，几乎同时执行 SELECT … FOR UPDATE WHERE c1=xx。
-
冲突发生：会话B尝试在c1=xx的记录上加X锁，但发现该锁已被会话A以X模式持有。X锁与X锁互斥，因此会话B被阻塞，进入锁等待状态。
-
等待超时：如果会话A的事务持有锁的时间过长，超过50秒仍未提交，则会话B的等待将触发innodb_lock_wait_timeout。会话B的事务会被数据库自动回滚，并返回类似“Lock wait timeout exceeded; try restarting transaction”的错误。
该场景是长时间锁等待引起。会话A通过SELECT … FOR UPDATE获取了c1=xx记录的排他锁，并且长时间持有该锁未提交。会话B在尝试获取同一个锁时，被迫进入等待状态。当等待时间达到innodb_lock_wait_timeout的上限时，B的事务被InnoDB强制终止，以防止资源被无限期占用。查看data_locks视图能看到锁等待现象：
mysql> select * from performance_schema.data_locks where object_name='lock_tb1';
+--------+------------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+-------------+-----------------------+-----------+---------------+-------------+-----------+
| ENGINE | ENGINE_LOCK_ID | ENGINE_TRANSACTION_ID | THREAD_ID | EVENT_ID | OBJECT_SCHEMA | OBJECT_NAME | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+--------+------------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+-------------+-----------------------+-----------+---------------+-------------+-----------+
| INNODB | 139896888154568:2125:139896145182416 | 3542079 | 94 | 10 | tango | lock_tb1 | NULL | NULL | NULL | 139896145182416 | TABLE | IX | GRANTED | NULL |
| INNODB | 139896888154568:1063:5:3:139896145179424 | 3542079 | 94 | 10 | tango | lock_tb1 | NULL | NULL | uniq_index1 | 139896145179424 | RECORD | X,REC_NOT_GAP | WAITING | '1002', 2 |
| INNODB | 139896888152000:2125:139896145164080 | 3542042 | 82 | 33 | tango | lock_tb1 | NULL | NULL | NULL | 139896145164080 | TABLE | IX | GRANTED | NULL |
| INNODB | 139896888152000:1063:5:3:139896145161088 | 3542042 | 82 | 33 | tango | lock_tb1 | NULL | NULL | uniq_index1 | 139896145161088 | RECORD | X,REC_NOT_GAP | GRANTED | '1002', 2 |
| INNODB | 139896888152000:1063:4:3:139896145161432 | 3542042 | 82 | 33 | tango | lock_tb1 | NULL | NULL | PRIMARY | 139896145161432 | RECORD | X,REC_NOT_GAP | GRANTED | 2 |
+--------+------------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+-------------+-----------------------+-----------+---------------+-------------+-----------+
5 rows in set (0.00 sec)
当表t1中不存在一条c1=xx的记录时，资源等待关系如下所示：
会话A 会话B
| |
|--- SELECT ... FOR UPDATE (无记录)|
|--- INSERT (获得 X_Insert锁) -----|--- SELECT ... FOR UPDATE (无记录)
| |--- INSERT (尝试获取S锁进行重复检查)
| | |
| | |---> 等待会话A的 X_Insert锁释放
| 事务A处理中... | | (S锁与X锁互斥)
|--- COMMIT (释放X_Insert锁) ------|-----|---> 如果等待超时(50秒)，B回滚
|---> 如果A先提交，B的INSERT因重复键错误而立即失败
-
时刻 T0: 会话A执行 SELECT … FOR UPDATE WHERE c1=xx。由于记录不存在，在READ-COMMITTED下，没有加锁。
-
时刻 T1: 会话A执行 INSERT INTO t1(c1,c2) VALUES (xx, ‘aa’)。此时插入成功，会话A在新增的c1=xx记录上获得了排他记录锁（X_Insert）。
-
时刻 T2: 几乎与T1同时，会话B也执行 SELECT … FOR UPDATE WHERE c1=xx。由于此时记录已被会话A插入并锁定，假设会话B的SELECT … FOR UPDATE在会话A INSERT提交之前就执行了。在READ-COMMITTED下，会话B的SELECT看不到未提交的数据，因此它也会判断“记录不存在”。此时，两个会话都认为记录不存在。
-
时刻 T3: 会话B执行 INSERT INTO t1(c1,c2) VALUES (xx, ‘bb’)。
-
冲突发生: 会话B的INSERT操作会触发重复键检查。在检查过程中，它发现唯一索引c1=xx的位置上，已经存在一个未提交的、由会话A插入的记录。此时，会话B的INSERT操作会尝试在这条未提交的记录上获取一个共享锁（S锁），以完成重复性判断 。
-
锁不兼容: 然而，这条未提交的记录已经被会话A以排他锁（X_Insert）锁定。S锁与X锁不兼容。因此，会话B的INSERT操作被阻塞，等待会话A释放X锁。
-
等待超时: 如果会话A的事务长时间未提交（>50秒），则会话B的INSERT操作等待超时，整个事务B被回滚。
查看data_locks视图能看到锁等待现象：
mysql> select * from performance_schema.data_locks where object_name='lock_tb1';
+--------+------------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+-------------+-----------------------+-----------+---------------+-------------+-----------+
| ENGINE | ENGINE_LOCK_ID | ENGINE_TRANSACTION_ID | THREAD_ID | EVENT_ID | OBJECT_SCHEMA | OBJECT_NAME | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+--------+------------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+-------------+-----------------------+-----------+---------------+-------------+-----------+
| INNODB | 139896888154568:2125:139896145182416 | 3543447 | 151 | 12 | tango | lock_tb1 | NULL | NULL | NULL | 139896145182416 | TABLE | IX | GRANTED | NULL |
| INNODB | 139896888154568:1063:5:4:139896145179424 | 3543447 | 151 | 13 | tango | lock_tb1 | NULL | NULL | uniq_index1 | 139896145179424 | RECORD | S | WAITING | '1003', 3 |
| INNODB | 139896888152000:2125:139896145164080 | 3543385 | 152 | 14 | tango | lock_tb1 | NULL | NULL | NULL | 139896145164080 | TABLE | IX | GRANTED | NULL |
| INNODB | 139896888152000:1063:5:4:139896145161088 | 3543385 | 151 | 13 | tango | lock_tb1 | NULL | NULL | uniq_index1 | 139896145161088 | RECORD | X,REC_NOT_GAP | GRANTED | '1003', 3 |
+--------+------------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+-------------+-----------------------+-----------+---------------+-------------+-----------+
4 rows in set (0.00 sec)
会话2中存在一条waiting状态的等待uniq_index1的S-lock。
4、并发Insert死锁场景分析
在向表t1的唯一索引中插入记录时，会存在三种锁：
-
用于唯一性检查的S 锁，多个事务可以同时持有 S 锁，检查期间短暂持有，检查完释放或升级；
-
插入时候持有X锁，在事务提交前一直持有；
-
插入意向锁（Insert Intention Lock），表示打算在某个间隙插入记录。
在该业务场景下存在一个delete的事务，假设表t1中存在c1=10的记录，会话A正在进行delete操作，但是没有提交事务，唯一索引 uk 上c1=10的记录被 S1的DELETE操作加上了X锁（排他锁），记录处于"删除标记"状态（但物理上尚未删除）。由于唯一索引（二级索引）叶子节点行记录数据包括Key+主键字段，同一个数据页下可能有多条记录。DELETE事务走唯一索引扫描时，先定位到符合条件的最左边记录，再逐个遍历直到符合条件的右边界，遍历过程中会对当前正在检查的行加行锁。Insert唯一索引时，首先也需要遍历相应的区间检查是否满足唯一性约束。此时，当DELETE提交时，在多并发insert场景下可能会触发死锁问题，具体时序如下：
时间线 会话S1 (DELETE) 会话S2 (INSERT) 会话S3 (INSERT) ----- --------------------- --------------------- --------------------- T1 BEGIN; T2 DELETE FROM t1 WHERE c1=10; └─ 获得 c1=10 的 X 锁T3 BEGIN;
T4 SELECT WHERE C1=11 FOR UPDATE;无记录
T4 INSERT INTO t1 VALUES(11, …);
├─ 请求 c1=11 的 S 锁（唯一性检查）
└─ 等待 S1 的 X 锁释放（阻塞）T5 BEGIN;
T6 SELECT WHERE C1=11 FOR UPDATE;无记录
T6 INSERT INTO t1 VALUES(11, …);
├─ 请求 c1=11 的 S 锁
└─ 等待 S1 的 X 锁释放（阻塞）T7 COMMIT; – 立即提交
├─ 释放 c1=10 的 X 锁
└─ 记录被物理删除T8 S2 被唤醒，尝试获取锁
├─ 获取 c1=10 的 S 锁成功（唯一性检查）
├─ 检查通过，释放 S 锁
├─ 获取插入意向锁
└─ 插入新记录，获得隐式 X 锁
T9 S3 同时被唤醒
├─ 也尝试获取 c1=10 的 S 锁
└─ 此时可能发生竞争
死锁等待资源图如下：
T8: S2 获取 S 锁（检查唯一性）→ 成功 T8: S2 释放 S 锁 → 准备插入 T8: S3 同时获取 S 锁（检查唯一性）→ 成功 T9: S2 尝试获取 X 锁插入 → 需要等待所有 S 锁释放（包括S3的） T9: S3 尝试获取 X 锁插入 → 需要等待所有 S 锁释放（包括S2的）
结果：S2等待S3，S3等待S2 → 死锁
5、优化建议
针对上述多并发Insert唯一键值的业务场景，本质上是通过锁资源实现乐观锁机制，以减少锁资源持有的时间，提升业务并发。当出现超时或死锁问题时，在应用层面增加重试机制，应用捕获超时和死锁的错误码（如ERROR 1213），进行事务重试。重试策略包括等待随机时间、设置最大重试次数、记录重试日志便于排查问题等。
补充：MySQL社区对社区有一个bug（Bug （Changes in MySQL 8.0.28 (2022-01-18）））在READ-COMMITTED隔离级别下，若二级索引未匹配查询条件，锁仍会持有到事务结束，导致并发性能下降。
1、BUG描述：With index_condition_pushdown=ON and transaction_isolation=‘READ-COMMITTED’, locks on secondary indexes were not released until the transaction was either committed or rolled back, even though the secondary indexes were unmatched. (Bug , Bug #32554667)
2、BUG说明：在READ-COMMITTED隔离级别下，若二级索引未匹配查询条件，锁仍会持有到事务结束，导致并发性能下降。BUG修复优化了锁释放机制，使未匹配条件的二级索引锁在扫描完成后立即释放，而非等待事务提交或回滚。但是锁的释放从之前的固定顺序变成了提前释放，但若多个事务交叉操作同一数据，可能因锁释放顺序不一致导致死锁。
该bug有部分数据库厂商已经修复，但是可能会因为提前释放锁资源而增加死锁发生的频率，在使用上还是需要应用层增加重试机制兜底优化。