MySQL中REPEATABLE_READ下SELECT FOR UPDATE锁未互斥的问题及解决
现有两张表：site (id, name, url)、page (id, code, content, path, site_id)，其中site表存在id=1的记录，page表为空。需要在REPEATABLE_READ隔离级别的并发事务中实现“若page表中不存在指定记录则插入”的逻辑，因此使用SELECT ... FOR UPDATE语句在验证阶段加锁，避免重复记录。
事务执行步骤：
- TRX 1：
START TRANSACTION; select * from `page` where path = 'path' and site_id = 1 limit 1 for update;
- TRX 2：
START TRANSACTION; select * from `page` where path = 'path' and site_id = 1 limit 1 for update;
根据锁兼容规则，LOCK_MODE = X的锁请求与已持有的X锁互斥，第二个事务应等待第一个事务完成，但实际两个事务同时获取了锁，锁信息如下：
| ENGINE | ENGINE_TRANSACTION_ID | OBJECT_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
|---|---|---|---|---|---|---|
| INNODB | 6180 | page | TABLE | IX | GRANTED | NULL |
| INNODB | 6180 | page | RECORD | X | GRANTED | supremum pseudo-record |
| INNODB | 6178 | page | TABLE | IX | GRANTED | NULL |
| INNODB | 6178 | page | RECORD | X | GRANTED | supremum pseudo-record |
请问为何会出现这种情况，该如何解决？
问题出在空表+无匹配记录时的InnoDB锁机制：
当SELECT ... FOR UPDATE查询没有命中任何实际记录时，InnoDB不会锁定不存在的记录，而是会锁定查询范围对应的间隙（或临键锁）。在page表为空的场景下，查询path='path' and site_id=1的范围覆盖了整个表，最终锁定的是聚簇索引末尾的supremum伪记录（这是InnoDB为维护索引结构存在的虚拟记录）。
而间隙锁（包括针对supremum伪记录的锁）的核心作用是阻止其他事务向该间隙插入数据，但不同事务之间的间隙锁是互相兼容的——多个事务可以同时持有同一间隙的X级间隙锁，不会互相阻塞。这就导致两个事务都能顺利获取锁，后续都可能执行插入操作，最终产生重复记录。
方案1：使用唯一索引+原子插入语句（推荐）
给page表的site_id和path字段创建唯一联合索引：
CREATE UNIQUE INDEX idx_page_site_path ON page(site_id, path);
然后直接使用原子性的插入语句替代“先查后插”的逻辑，由数据库层面保证唯一性：
- 若只需忽略重复插入：
INSERT IGNORE INTO page(code, content, path, site_id) VALUES('xxx', 'xxx', 'path', 1);
- 若需要在重复时更新字段：
INSERT INTO page(code, content, path, site_id) VALUES('xxx', 'xxx', 'path', 1) ON DUPLICATE KEY UPDATE code = VALUES(code), content = VALUES(content);
这种方式不需要手动加锁，由数据库引擎保证并发场景下的原子性，是最可靠的实现方式。
方案2：调整锁策略（适合必须保留先查后插逻辑的场景）
如果一定要保留“先查询验证，再插入”的逻辑，需要确保查询能精准锁定目标间隙，避免多个事务同时获取兼容的间隙锁：
- 先创建
(site_id, path)的联合索引（非唯一也可以，但唯一索引更稳妥）； - 调整查询语句，确保锁定的是
site_id=1, path='path'对应的精准间隙，而不是整个表的supremum记录。不过这种方式依然不如方案1可靠，并发场景下仍可能出现漏锁情况，优先推荐方案1。
内容的提问来源于stack exchange，提问作者Vasyabylba
