Oracle问题- ORA-00054:资源繁忙-同时创建外键

Question

初步情况：

• 表PARENT_TABLE，其列PK_COL上有主键。
• 带有外键的表CHILD_TABLE1 ( PARENT_TABLE(PK_COL) )。

我在事务中将一行插入到CHILD_TABLE1中，而不提交。

然后，我尝试在另一个会话中创建一个与CHILD_TABLE1对称的表CHILD_TABLE1。但是当我创建外键时会引发ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired，因为CHILD_TABLE1中正在进行插入。

我不明白为什么Oracle会阻止外键的创建:没有在PARENT_TABLE上执行任何修改。

请帮帮忙。

在平方加以下繁殖：

set autocommit off

create table PARENT_TABLE(PK_COL varchar(10));
alter table PARENT_TABLE add constraint PK_CONSTRAINT primary key (PK_COL);
insert into PARENT_TABLE values ('foo');
commit;

create table CHILD_TABLE1(CHILD_PK_COL varchar(10), FK_COL varchar(10));
alter table CHILD_TABLE1 add constraint CHILD_TABLE1_CONSTRAINT foreign key (FK_COL) references PARENT_TABLE(PK_COL);
create index CHILD_TABLE1_INDEX on CHILD_TABLE1(FK_COL);
insert into CHILD_TABLE1 values ('bar', 'foo');

在另一个控制台中：

alter session set ddl_lock_timeout=10;
create table CHILD_TABLE2(CHILD_PK_COL varchar(10), FK_COL varchar(10));
alter table CHILD_TABLE2 add constraint CHILD_TABLE2_CONSTRAINT foreign key (FK_COL) references PARENT_TABLE(PK_COL);

有趣的是:随着CHILD_TABLE2_CONSTRAINT的创建，执行正在被挂起.

Answer 1

您不是在修改父表中的某些内容。但实际上，您正在尝试将其主键引用到您的子表中。在与表建立关系或任何DDL之前，它必须没有锁。

因此，在创建此约束之前，Oracle确实会检查引用表(PARENT_TABLE)上的现有锁。表上的锁(此上下文中的表级别锁)实际上是坚持ACID属性的原因。

理解其重要性的一个最佳示例是ON DELETE CASCADE，这意味着如果删除父表中的记录，那么子表中的相应记录将自动删除。

因此，当在引用父表的子表上存在未提交的insert/update/delete时。不能为父级创建其他引用约束。只是为了避免僵局或混乱。

当您的子表中有一个未提交的插入时，要更清晰一些。你的父表上也有锁。因此，其他所有其他提到它的DDL都将等待。

您可以使用此查询检查相同的内容。

SELECT c.owner,
  c.object_name,
  c.object_type,
  b.sid,
  b.serial#,
  b.status,
  b.osuser,
  b.machine
FROM v$locked_object a ,
  v$session b,
  dba_objects c
WHERE b.sid     = a.session_id
AND a.object_id = c.object_id;

Answer 2

我在查询中添加了LOCKED_MODE解释：

DECODE(a.LOCKED_MODE, 0,'NONE', 1,'NULL', 2,'ROW SHARE (RS/SS)', 3,'ROW EXCLUSIVE (RX/SX)', 4,'SHARE (S)', 5,'SHARE ROW EXCLUSIVE (SRX/SSX)', 6,'EXCLUSIVE (X)', NULL) LOCK_MODE.

结果如下：

OBJECT_NAME                    OBJECT_TYPE         LOCK_MODE                         SID    SERIAL# STATUS
------------------------------ ------------------- ----------------------------- ---------- ---------- --------
PARENT_TABLE                   TABLE               ROW EXCLUSIVE (RX/SX)              71          8694 INACTIVE
CHILD_TABLE1                   TABLE               ROW EXCLUSIVE (RX/SX)              71          8694 INACTIVE

RX/SX是一个表锁，因此它可以防止任何DDL操作(在文档中似乎是这样说的)。此锁同时用于父锁和子锁。我假设锁是添加在父表上的，以便至少防止它被删除，这样我们就会丢失子表上挂起的更新。

尽管如此，我仍然没有解决办法。假设父表是制造商。有一个儿童汽车表，我们正在那张桌子上插入大量的新车。从汽车到制造商有一个外键。现在有一个新的产品，我们想管理：“自行车”。因此，我们希望创建一个类似于car的表。但是我们不能创建该表，因为我们正在执行car中的插入操作。似乎是一个非常简单的用例..。如何支持它？

=====编辑:也许没有解决办法。这是一个有着同样问题的人。