CODEWITHSUNDEEP
oracle-databaselocking
rt15
rt15

Reputation: 85

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

Initial situation:

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

Upvotes: 0

Views: 15608

Answers (2)

rt15
rt15

Reputation: 85

I added the LOCKED_MODE explanation in you query:

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.

Here is the result:

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 is a table lock so it prevents any DDL operation (That seems to be said in the doc). This lock is used on both parent and child. I suppose that the lock is added on parent to at least prevent it from being deleted so we would lost the pending update on the child table.

That said, I still have no solution. Suppose that the parent table is a manufacturer. There is a child car table and we are inserting plenty of new cars in that table on the fly. There is a foreign key from car to manufacturer. Now there is a new product that we want to manage: "bicycles". So we want to create a bicycle table similar to car. But we cannot create the table as we are performing insertions in car. Seems a very simple use case... How to support it?

===== Edit: There might be no solution. Here is a guy with the same issue.

Upvotes: 0

Maheswaran Ravisankar
Maheswaran Ravisankar

Reputation: 17920

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;

Upvotes: 2

Related Questions