Oracle lock issue – ORA-00054: resource busy – while creating a foreign key

lockingoracle

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

You are not modifying something in the parent table. But you're actually, trying to refer its primary key in your child table. Before establishing a relationship or any DDL with table, it has to be free of locks.

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 the ACID 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.

To be more crisp, when you have an uncommitted insert in your child table. There's a lock over your parent table as well. So all other further DDLs referring it will be made wait.

You can use this query to check the same.

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;
Related Topic