MySQL RC隔离级别下多线程先查后删查询仅返首行原因咨询

1. RC隔离级别的读模式差异 #

MySQL在RC（读已提交）隔离级别下，默认支持两种读模式：

• 普通无锁SELECT属于快照读（一致性非锁定读）：读取的是SQL执行瞬间的行历史快照版本，不会加行锁，也不会等待其他事务持有的行锁释放，仅能读取到执行瞬间已经提交的行数据。
• 加FOR UPDATE的SELECT、UPDATE、DELETE属于当前读：读取行的最新已提交版本，同时会对匹配的行加排他锁，遇到被其他事务加锁的行时，会等待锁释放后再做读取判断。

2. 并发修改导致的快照读行匹配异常 #

你提到多线程处理的ID范围无交集，但查询WHERE条件除ID范围外还包含多个业务状态字段，触发问题的核心逻辑如下：

• 当你执行无锁的查询SQL时，快照读会按顺序扫描符合ID范围的行，如果扫描过程中刚好有其他并发事务修改了某行的业务状态、或者删除了该行，且修改/删除事务在你的查询SQL扫描到这行之前完成提交，那么快照读读取到的行版本就会不匹配查询条件，直接被排除出结果集。
• 因为锁竞争和事务提交的时机是随机的，所以每次测试时未返回的行都不一致。

3. 仅返回批次首行的共性原因 #

如果你的查询没有覆盖所有查询条件的联合索引，InnoDB会按照ID从小到大的顺序扫描聚簇索引：
批次首行的ID最小，扫描顺序最靠前，通常还没来得及被其他并发事务修改/加锁，所以能正常匹配查询条件返回；后续的行在扫描时，要么已经被其他事务修改为不匹配条件的状态、要么已经被删除，最终就会出现仅返回首行的现象。

4. 加FOR UPDATE后恢复正常的原因 #

添加FOR UPDATE后查询变为当前读：
执行查询时会先对所有匹配WHERE条件的行加排他锁，阻塞其他并发事务对这些行的修改/删除操作，同时遇到被其他事务加锁的行时会等待锁释放，读取最新的有效版本判断是否匹配条件，因此可以拿到完整的符合条件的ID列表。

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