MySQL 8.3.0执行SELECT ... FOR UPDATE时出现死锁问题求助

这不是MySQL的bug，是InnoDB默认的**Next-Key Locking（临键锁）**机制导致的，具体逻辑如下：

死锁触发原因 #

InnoDB在REPEATABLE READ（默认隔离级别）下，为防止幻读，执行SELECT ... FOR UPDATE这类加锁查询时，不仅会锁定匹配的行，还会锁定行所在的索引间隙（Gap Lock）。当表为空时，product_sku索引无任何现有值，此时两个会话的SELECT ... FOR UPDATE查询（分别针对'abc'和'def'）都会锁定整个索引的间隙范围（从负无穷到正无穷）。

后续插入操作需要申请插入意向锁（Insert Intention Lock），这是一种特殊间隙锁，用于标记插入到某间隙的意向：

• AA插入'abc'时，申请的插入意向锁与BB持有的全局间隙锁冲突，AA挂起等待
• BB插入'def'时，申请的插入意向锁与AA持有的全局间隙锁冲突，形成循环等待，触发死锁

可行解决方案 #

1. 给product_sku添加唯一索引（推荐） #

将product_sku设为唯一索引后，InnoDB会针对不存在的唯一键值使用**记录锁（Record Lock）**而非间隙锁——因为唯一索引可保证不会有重复值插入，无需通过间隙锁防止幻读。

修改表结构的SQL：

ALTER TABLE example ADD UNIQUE INDEX idx_unique_product_sku (product_sku);

修改后，两个会话针对不同product_sku的加锁查询只会锁定各自对应的虚拟记录，插入时不会产生锁冲突，既满足“阻止同一分组并发插入”的需求，又允许不同分组并行插入。

2. 降低事务隔离级别至READ COMMITTED #

该隔离级别下InnoDB不会使用间隙锁，仅使用记录锁，避免了间隙锁导致的锁范围过大问题。但此级别无法防止幻读，需根据业务场景判断是否可接受。

设置会话级隔离级别：

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

3. 调整业务逻辑（按需选择） #

如果业务允许，可以先尝试插入记录（利用唯一索引的唯一性约束阻止重复插入），再对已插入的行进行加锁操作，跳过前置的加锁查询步骤。

内容的提问来源于stack exchange，提问作者sowiq