Oracle FOR UPDATE SKIP LOCKED出现无关锁导致查询结果异常

代码与环境信息 #

Repository代码 #

@Repository
public interface MyTableRepository extends JpaRepository<MyTableEntity, Long> {

    @Query(value = """
        SELECT rowid, field1, field2, status
        FROM my_table 
        WHERE field2 = :value AND ROWNUM <= 10000
        FOR UPDATE OF STATUS SKIP LOCKED
        """,
        nativeQuery = true)
    List<MyTableEntity> getEntities(String value);

}

实体类代码 #

@Data
@Entity
public class MyTableEntity {

    private String rowid;

    @Id
    private Long field1;
    private String field2;
    private String status;

}

DAO方法 #

@Transactional
public List<MyTableEntity> getTransactions(String value) {
    List<MyTableEntity> entities= myTableRepository.getEntities(value);
    log.info("Count: {}", entities.size());
    return entities;
}

数据库表结构 #

CREATE TABLE MY_TABLE
(    
    "FIELD1" NUMBER(15,0), 
    "FIELD2" CHAR(6 BYTE), 
    "STATUS" VARCHAR2(20 BYTE)
);
CREATE UNIQUE INDEX MY_TABLE_IDX1 ON MY_TABLE ("FIELD1");
ALTER TABLE MY_TABLE MODIFY ("FIELD1" NOT NULL ENABLE);

表数据分布 #

SELECT count(1), field2 FROM my_table group by field2 order by 2

30424   value1
21171   value2
10890   value3
16690   value4
11198   value5

现象 #

预期：无论单线程还是多线程并行查询不同field2值，getEntities均返回10000条数据（SQL含ROWNUM <= 10000）。

• 单线程/2个并行线程查询：结果正常，均返回10000条
• 3个并行线程查询：结果正常
• 4个并行线程查询：部分线程返回数不足10000（如value2返回9857，value1返回9973）
• 5个并行线程查询：多数线程返回数不足10000，仅value3仍返回10000
• 移除FOR UPDATE OF STATUS SKIP LOCKED后，所有场景结果正常

疑问 #

为何条件完全正交的独立查询会出现结果依赖？一个查询的锁为何会影响另一个条件完全不同的查询的数据可选性？

核心原因 #

问题根源在于缺少FIELD2字段的索引，导致所有查询都执行全表扫描，结合FOR UPDATE OF STATUS SKIP LOCKED和ROWNUM <=10000的执行逻辑，在多线程并行场景下触发了非预期的行过滤：

1. 全表扫描的资源竞争
   没有FIELD2索引时，所有查询都会遍历整个表的行。不同线程的查询虽针对不同field2值，但全表扫描过程中会共享表的访问资源，导致行的扫描顺序、可用性被打乱。

2. ROWNUM与SKIP LOCKED的交互逻辑
   Oracle中ROWNUM <=10000会在筛选出符合WHERE条件的行后立即生效，而SKIP LOCKED是在尝试对选中行加锁时跳过已锁定的行。在全表扫描场景下，优化器可能基于统计信息选择“提前终止扫描”的策略（认为扫描前N行就能凑够10000条符合条件的行），但多线程并行时，部分符合条件的行可能因其他线程的全表扫描操作（即使针对其他field2值）导致扫描顺序异常或临时锁定，最终无法凑够10000条加锁成功的行。

解决方案 #

在FIELD2字段上建立索引，让每个查询仅扫描符合自身field2条件的行，彻底避免全表扫描带来的资源竞争：

CREATE INDEX MY_TABLE_FIELD2_IDX ON MY_TABLE ("FIELD2");

建立索引后，查询会直接通过索引定位目标行，不会扫描其他field2值的行，无论单线程还是多线程场景，都能稳定返回10000条数据。

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