SELECT ... FOR UPDATE 会锁住非目标行，根本原因是索引未覆盖查询条件导致全表扫描，触发间隙锁或临键锁；应建合适索引、用覆盖索引、考虑乐观锁或唯一索引+ON DUPLICATE KEY UPDATE。
为什么 SELECT ... FOR UPDATE 会锁住非目标行
在并发更新场景中，即使你只查 WHERE status = 'pending'，MySQL 也可能锁住大量无关记录。根本原因不是 SQL 写得不对，而是索引没覆盖查询条件 + 扫描方式导致的间隙锁（Gap Lock）或临键锁（Next-Key Lock）。比如 status 字段没有索引，InnoDB 只能走聚簇索引全表扫描，所有被扫描过的间隙都会被锁住——哪怕某行 status = 'done'，它也在锁范围内。
实操建议：
- 确认执行计划：用
EXPLAIN SELECT ... FOR UPDATE看是否走了索引、是否出现type: ALL或key: NULL - 给
WHERE条件字段建索引，优先组合索引：如高频查询是WHERE status = ? AND created_at > ?，就建(status, created_at)联合索引 - 避免在
WHERE中对索引字段做函数操作，例如WHERE DATE(created_at) = '2024-01-01'会让索引失效
如何用覆盖索引减少锁范围
覆盖索引能让 InnoDB 在二级索引层完成整个查询，无需回表访问聚簇索引。这意味着锁只加在二级索引记录上，且只锁命中的索引条目（精确匹配时甚至不锁间隙），大幅降低锁粒度。
实操建议：
- 把
SELECT中的所有字段都包含进索引定义里，例如查询SELECT id, user_id FROM orders WHERE status = 'processing'，可建索引INDEX idx_status_cover (status, id, user_id) - 注意索引顺序：等值查询字段（
status）放最左，然后是排序/范围字段，最后是 SELECT 的其他字段 - 别盲目加太多字段——索引变宽会增加写开销和内存占用，优先覆盖高频、高并发的查询路径
乐观锁替代方案：为什么 UPDATE ... WHERE version = ? 更适合高并发
当业务允许“重试”时，用应用层控制版本号比数据库行锁更轻量。它不依赖索引结构，也不触发间隙锁，失败后只需重新读取再提交，吞吐量通常更高。
实操建议：
- 在表中加
version INT DEFAULT 0字段，并为它建普通索引（非必须，但能加速WHERE判断） - 更新语句必须带版本检查：
UPDATE orders SET status = 'done', version = version + 1 WHERE id = ? AND version = ? - 检查
ROW_COUNT()返回值：为 0 表示被其他事务抢先更新，需重试；为 1 表示成功 - 注意避免长事务导致版本号长时间未更新，引发频繁重试
唯一索引 + INSERT ... ON DUPLICATE KEY UPDATE 防止重复插入锁竞争
在抢购、发券等场景中，多个请求同时插入相同业务单号，若只靠主键或唯一索引判断存在性再更新，中间存在时间窗口，极易引发死锁或重复数据。而 ON DUPLICATE KEY UPDATE 是原子操作，InnoDB 仅对冲突的唯一索引值加锁，锁范围极小。
实操建议：
- 确保用于判重的字段有唯一索引（不能只是普通索引），例如
UNIQUE KEY uk_order_no (order_no) - 写法要统一：所有插入逻辑都走
INSERT ... ON DUPLICATE KEY UPDATE，不要混用SELECT + INSERT/UPDATE - 如果需要返回影响行数判断是插入还是更新，用
mysql_affected_rows()：1 表示插入，2 表示更新（含自增主键变化）
FOR UPDATE 或一个漏掉的唯一约束。 
