Initial situation:
- A table PARENT_TABLE with a primary key on its column PK_COL.
- A table CHILD_TABLE1 with a foreign key on PARENT_TABLE(PK_COL).
I insert a line into CHILD_TABLE1 in a transaction and do not commit.
Then I try to create a table CHILD_TABLE2 symmetrical to CHILD_TABLE1 in another session.
But an ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
is raised when I create the foreign key, because of the ongoing insertion in CHILD_TABLE1.
I don't understand why Oracle is preventing the foreign key creation: there is no modification performed on PARENT_TABLE.
Please help.
To reproduce under sqlplus:
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');
In another console:
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);
Funny: with NOVALIDATE in CHILD_TABLE2_CONSTRAINT creation, the execution is hanging…
Best Answer
So, before creating this constraint, Oracle do check for existing locks over the referred table(
PARENT_TABLE
). A lock over a table(Table Level Lock,in this context) is actually for a reason to adhere to theACID
properties.One best example to understand its importance is
ON DELETE CASCADE
which means if a record in the parent table is deleted, then the corresponding records in the child table will automatically be deleted.So, when there's a uncommitted insert/update/delete over the child table referring a parent table. No other referential constraint can be created to the parent. Just to avoid a deadlock or chaos.
You can use this query to check the same.