怎么解决MySQL唯一索引范围查询锁冲突？-mysql教程-PHP中文网

mysql在rr级别下范围扫描必加next-key锁（记录锁+间隙锁）以防止幻读，如id between 10 and 20可能锁住(9,25)区间；唯一等值查询且字段为主键或唯一索引时才只加记录锁。

怎么解决mysql唯一索引范围查询锁冲突？

WHERE条件走范围扫描必然加间隙锁

MySQL在可重复读（RR）隔离级别下，只要SELECT FOR UPDATE或UPDATE语句走的是范围扫描（比如BETWEEN、>=、IN配合非唯一索引），InnoDB就会加next-key锁——即记录锁 + 间隙锁。哪怕你只查id BETWEEN 10 AND 20，实际锁住的可能是(9, 25)这个区间，导致插入id = 21被阻塞。

这不是bug，是RR级别下防止幻读的机制。想绕过？别指望LOCK IN SHARE MODE能豁免——它和FOR UPDATE一样触发间隙锁，区别只在锁类型（S锁 vs X锁），不改变锁粒度。

用EXPLAIN确认是否真走了范围扫描：如果type是range或index，基本已中招
唯一等值查询（WHERE id = 15）且id是主键或唯一索引时，才只加record lock
哪怕写成WHERE id >= 15 AND id ，优化器也可能不识别为等值，仍走range

把BETWEEN拆成IN列表必须满足三个前提

把UPDATE t SET x=1 WHERE created_at BETWEEN '2024-01-01' AND '2024-01-31'改成先查ID再UPDATE ... WHERE id IN (...)，确实能规避间隙锁，但前提是：

查出的ID数量可控（建议≤ 500），否则MySQL可能放弃索引，退化为全表扫描
两次操作之间不能有其他事务删改这些ID对应的数据，否则出现更新丢失或幻读；需应用层做幂等或重试
IN里的每个值都必须真实存在且命中唯一索引（如主键），否则仍可能触发间隙锁

示例安全写法：

MySQL

编写正确的MySQL查询，避免字符集、索引和锁方面的常见陷阱。

下载

SELECT id FROM orders WHERE status = 'pending' AND created_at >= '2024-01-01' AND created_at 拿到结果后，拼成<code>UPDATE orders SET status = 'processing' WHERE id IN (101,102,105,...)</code>——此时每条都是确定存在的主键值，只锁行，不锁间隙。

<h3>联合索引设计直接影响间隙锁“杀伤半径”</h3>
<p>间隙锁范围由扫描路径决定，不是由WHERE条件字面意思决定的。比如表上有<code>INDEX(status, created_at)</code>，但你只查<code>WHERE created_at BETWEEN ...</code>，那该索引根本用不上，InnoDB可能扫全表或走其他低效索引，锁住更大范围。</p>

确保范围条件字段出现在联合索引最左前缀位置，例如查status和created_at，索引应建为(status, created_at, id)
如果业务常按created_at范围查，又不想锁太宽，可考虑加id到索引末尾，让覆盖扫描更精准，缩小gap区间
避免在索引字段上用函数：WHERE DATE(created_at) = '2024-01-01'会失效索引，必须写成WHERE created_at >= '2024-01-01' AND created_at

INSERT ON DUPLICATE KEY UPDATE不是万能解药

它能解决“插入时唯一冲突”的问题，但对范围查询锁冲突完全无关——因为INSERT ... ON DUPLICATE KEY UPDATE本身不涉及范围扫描，只依赖唯一索引快速定位单行。但它有个关键限制：

必须确保ON DUPLICATE KEY所依据的字段（如email）已建UNIQUE KEY或PRIMARY KEY，否则语句会报错或退化为全表扫描加锁
多个唯一索引并存时（比如同时有UNIQUE(email)和UNIQUE(phone)），并发插入可能因加锁顺序不一致引发死锁
它不解决SELECT FOR UPDATE类语句的间隙锁问题，这两类场景压根不在同一技术路径上

真正容易被忽略的点是：你以为加了唯一索引就万事大吉，但没验证过执行计划是否真的用了它——EXPLAIN里type不是const或eq_ref，那所有锁优化都是空谈。