看起来好像Oracle FOR UPDATE应用了不相关的锁

我有一段Java代码，它处理JPA存储库，并使用普通的单表nativeQuery select。这是repo：

@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.getEntity(value);
        log.info("Count: {}", entities.size());
        return entities;
    }

型
MY_TABLE的结构如下：

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);

型
包含不同field 2值的记录数量

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

30424   value1
21171   value2
10890   value3
16690   value4
11198   value5

型
我预计，如果调用repo方法getEntity，它将在任何情况下响应一个大小为10000（AND ROWNUM <= 10000）的列表-如果它在单个线程或5个并行线程中被调用，每个线程都有一个field 2可能的值。
在单线程情况下，一切都很好，并且可以预测-运行field 2值value1：

2023-11-06 13:14:48,198 INFO  [     test-value1-1] [InputStorage.java:58]   Count: 10000

型
当它在field 2值value1,value2的两个并行线程中运行时，它仍然可以：

2023-11-06 13:13:23,505 INFO  [     test-value2-1] [InputStorage.java:58]   Count: 10000
2023-11-06 13:13:23,505 INFO  [     test-value1-1] [InputStorage.java:58]   Count: 10000

型
对于三个并行线程（对于field 2值value 1，value 2，value 3），它仍然是可以的。当我在4个并行线程中运行field 2值value 1，value 2，value 3，value 4时，灾难发生了：

2023-11-06 13:16:20,961 INFO  [     test-value2-1] [InputStorage.java:58]   Count: 9857
2023-11-06 13:16:20,962 INFO  [     test-value4-1] [InputStorage.java:58]   Count: 10000
2023-11-06 13:16:20,962 INFO  [     test-value3-1] [InputStorage.java:58]   Count: 10000
2023-11-06 13:16:21,181 INFO  [     test-value1-1] [InputStorage.java:58]   Count: 9973

型
而在5个线程中运行value 1，value 2，value 3，value 4，value 5会更糟糕：

2023-11-06 13:20:54,355 INFO  [     test-value1-1] [InputStorage.java:58]   Count: 9401
2023-11-06 13:20:54,355 INFO  [     test-value2-1] [InputStorage.java:58]   Count: 9542
2023-11-06 13:20:54,497 INFO  [     test-value4-1] [InputStorage.java:58]   Count: 9976
2023-11-06 13:20:54,505 INFO  [     test-value5-1] [InputStorage.java:58]   Count: 9909
2023-11-06 13:20:54,552 INFO  [     test-value3-1] [InputStorage.java:58]   Count: 10000

型
当然，如果我从仓库中的SQL语句中删除FOR UPDATE OF STATUS SKIP LOCKED，在所有（单线程或多线程）情况下都可以。
问题是：纯独立查询的结果（独立的，因为它们包含严格正交的条件）是如何以如此奇怪和不稳定的方式依赖的？一个具有某些查询条件的选择应用的锁如何影响（通过锁定？）另一个具有完全不同条件的选择的数据的可选择性？

我在这里使用@ translation只是为了完整性，因为所有这些都是我对所选实体进行更新的应用程序代码的简化。
我使用的是Oracle Database 23 c Free，Release 23.0.0.0.0 -Oracle Database 23 c Free-Release Version 23.2.0.0.0*

该文件说：
Oracle数据库使用排队机制来获取行锁。如果事务需要行锁，并且该行尚未锁定，则该事务将获取该行数据块中的锁。事务本身在块标题的相关事务列表（ITL）部分中有一个条目。此事务修改的每一行都指向存储在ITL中的事务ID的副本。因此，同一块中由单个事务修改的100行需要100个行锁，但所有100行都引用单个事务ID。
和
当事务结束时，事务ID保留在数据块标题的ITL部分。如果新事务想要修改行，则它使用事务ID来确定锁是否处于活动状态。
如this old blog entry中所述，如果感兴趣的事务列表（ITL）已满：
当我们启动多个并发的消费者程序时，一个等待事件立即变得明显和压倒性：

enq：TX -分配ITL条目

因此，很明显，我所有问题和头痛的根源是数据块头部的ITL条目不足。因此，阻塞发生在块级别，而不是行级别。这解释了为什么即使游标尚未识别出100个候选行，也不会对未锁定的行进行锁定。
我不确定这是不是真的但是...
由于INITRANS默认为1，如果同一块中有多行具有不同的值，则为第一个值选择更新的第一行将占用该ITL插槽;该值的后续行将在同一事务中，因此将共享该ITL插槽;但是由不同事务（即具有不同值）选择的同一块中的行，将看到 block 被锁定，并且由于SKIP LOCKED子句的存在，它会悄悄地跳过该行--它跳过了所有的TX锁，而不仅仅是行TX锁。
正如该博客总结的那样：
然而，重要的是要理解skip locked子句意味着Oracle将跳过任何时候遇到TX锁，包括在块级别发生的TX锁。问题是，如果您遇到非行级别的锁定，skip locked仍然会跳过锁定的资源并继续前进。不会报告任何错误！这可能会导致不可预知的结果。这个故事的寓意是，跳过锁定并不一定意味着跳过锁定的行，它意味着跳过任何锁定的资源。
这就给了，作为他们问题的修复，建议alter table ... move nologging initrans 110.你不需要切换到nologging，你可以只做：

alter table my_table move initrans 5

字符串
或者具有更高的数字以处理更多的并发事务。

看起来好像Oracle FOR UPDATE应用了不相关的锁

1条答案

相关问题

热门标签

最新问答