Oracle锁定多个插入的索引

Question

我有这个问题，还没有找到解决方案：

我有一个包含多个表的应用程序（像大多数应用程序一样）。

其中两个表是：

TB_POLICY
---------
id number(18) : PK
... some other columns
TB_REDEMPTION
-------------
id number(18) : PK
fk_policy number(18) : NOT NULL, FK
... some other columns

TB_POLICY是通过其某些字段的散列来划分的，TB_REDEMPTION是通过对fk_policy关系的引用来划分的。

表TB_POLICY包含约50000条记录，表TB_REDEMPTION包含约25000000条记录。

有一种用例，应用程序在单个事务中在TB_POLICY中插入单个记录，在TB_REDEMPTION中插入大量记录（约200-300条记录）。

当我在代码中标记一个断点时，在所有插入（在两个表中）之后，在提交事务之前，无法从另一个数据库连接（甚至直接从SQL*Plus）将另一条记录插入TB_REDEMPTION中——它只是等待！

我们分析了很多事情，发现PK_REDEMPTION（TB_REDEMPTION的主键索引）发生了锁。

我该如何防止这把锁？我在网上搜索了一下，在插入过程中没有找到任何关于索引锁的信息。

我要提到的是，这个应用程序是基于web的，有许多并发用户使用相同的用例，并且由一个用户锁定主键索引，阻止了其他用户的工作，并且对应用程序的性能影响非常糟糕。

还有一点，应用程序是由Spring/HHibernate开发的，所以事务管理是由Spring完成的，DML语句是由Hibernate创建的。我们使用的是Oracle 11g。

Answer 1

我已经解决了这个问题，TB_REDEMPTION有另一个外键FK_PAY，它是唯一的，但可以为null。

它被定义为：

alter table tb_redemption add constraint uk_rdm_pay unique(fk_pay);

但问题是，如果所有索引属性都为null，Oracle不会将记录添加到索引中。因此，当插入fk_pay等于null的记录时，Oracle锁定了表，而不是索引中的记录，这就是问题的根源。

我通过删除唯一约束并定义以下唯一索引来解决问题：

create unique index ux_rdm_pay on tb_redemption(fk_pay, case when fk_pay is null then id end);

因此，唯一索引要求关系的唯一性，并且索引的字段永远不会一起是null，因此表中的所有记录都将被索引，并防止表锁定。