MySQL 5.7中SELECT...LIMIT...FOR UPDATE锁行为问题及优化咨询

问题根源 #

InnoDB的行锁基于索引扫描范围，而非仅锁定查询返回的行：

• 当执行SELECT ... LIMIT ... FOR UPDATE时，若查询无合适索引触发全表扫描，或索引扫描覆盖了超出返回结果的行，这些被扫描到的行都会被加锁。
• 测试2中事务2的LIMIT 1,1对应的行恰好被事务1的扫描过程锁定，因此出现等待；测试1中id=2不在事务1的锁定范围内（或主键索引精准锁定单一行），所以可立即返回。

针对业务场景（从vouchers表查询status=1的凭证，高并发下锁等待崩溃），给出以下MySQL 5.7可用的解决办法：

• 添加精准联合索引
  创建包含查询条件和排序字段的联合索引：

  CREATE INDEX idx_status_id ON vouchers(status, id);
  

  该索引让查询仅扫描status=1的行，且按id有序遍历，LIMIT只会锁定最终返回的行，避免扫描额外行导致的锁范围扩大。

• 分两步查询加锁
  先无锁获取目标主键ID，再通过主键精准锁定：

  1. 无锁查询ID：

     SELECT id FROM vouchers WHERE status=1 LIMIT 0,?;
     

  2. 事务内根据ID加锁查询：

     SELECT * FROM vouchers WHERE id IN (/* 第一步获取的ID列表 */) FOR UPDATE;
     

  此方式避免了扫描阶段的大范围加锁，仅锁定业务需要的行。需将两步放在同一事务内，防止数据被其他事务修改。

• 调整事务隔离级别为READ COMMITTED
  默认的REPEATABLE READ隔离级别会触发间隙锁防止幻读，导致锁范围扩大。改为READ COMMITTED后，普通查询不再添加间隙锁：

  -- 会话级别临时调整
  SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
  

  若需全局生效，可修改my.cnf配置文件：

  transaction-isolation = READ-COMMITTED
  

• 缩短事务持有锁时间
  减少单次LIMIT的行数，避免一次性锁定过多行；事务仅包含必要的查询和锁定逻辑，执行完成后立即提交，不要持有锁处理其他非核心业务。

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