Oracle Ref Partitioning: Deadlock due to child table row migration

Question

My ordering application uses Oracle 11g Database. This DB has a primary table ORDERS and multiple child tables like ORDER_DETAILS, PLAN etc.

ORDERS table is LIST partitioned on STATUS column and all other tables are referenced partitioned with ORDERID as a foreign key.

At peak load, when order status is changed and ORDERS table row is moved from one partition to another, Oracle performs row migration for all the child tables referenced partitioned by ORDERS table. Due to many tables that depend on ORDERS table, large number of row movements happen causing a deadlock in one of the child table.

My question is, how to resolve a deadlock caused in the ORACLE's internal row migration step?

Here is an example setup:

ORDERS table:

CREATE TABLE ORDERS (
       orderID   NUMBER PRIMARY KEY,
       description    VARCHAR2(30),
       status   VARCHAR2(30))
  PARTITION BY LIST (status) (  
       PARTITION A VALUES ('COMPLETED'), 
       PARTITION B VALUES ('ACTIVE'), 
       PARTITION C VALUES ('SUBMITTED'))

Child table: PLAN

CREATE TABLE PLAN (
       planID   NUMBER PRIMARY KEY,
       orderID    NUMBER,
       description   VARCHAR2(30),
       CONSTRAINT FKC64393AD1EC7235 FOREIGN KEY (orderID) 
       REFERENCES ORDERS (orderID) ON DELETE CASCADE
 )
 PARTITION BY REFERENCE (FKC64393AD1EC7235);

There are many more child table where ORDERS is there parent table.

Under heavy load, when ORDER status is changed which causes row movement between partition, following deadlock error is printed in the log ORA-00060: deadlock detected while waiting for resource

In the Oracle trace log, I see following SQL causing deadlock

update /*+ opt_param('_and_pruning_enabled', 'false') */ "TEST"."PLAN" partition (dataobj_to_partition( "TEST"."ORDERS" , :1)) move to partition (dataobj_to_partition( "TEST"."ORDERS" , :1)) set "ORDERID" = "ORDERID" where "ORDERID" = :1

Now this SQL is internally generated by Oracle to perform row migration for child PLAN table.

To resolve the issue I tried following changes:

1. I have confirmed that OrderID column (Foreign Key column) in the PLAN table has index on it.
2. Tried increasing PCTFREE parameter on the table.

But havnt got success yet. How do I handle deadlock for this scenario?

------------------------- UPDATE -------------------------

As per suggestions suggested by Wernfried and Gandolf989, I verified if all my foreign keys have indexes on them by running query given in the Gandolf989 answer.

Result was "No Rows Found". So it means, all the indexes seems to be in place. But while analyzing I realized, if I check an explain plan for a simple query like below, I see FULL table scan on the PLAN table even after having an index on ORDERID column.

 UPDATE PLAN SET DESCRIPTION = 'ABC' WHERE orderid= '234';

Following is the index statement originally run on this table.

 CREATE INDEX IDX_PLAN_ORDERID ON  PLAN(ORDERID);

So for some reason, ORACLE is not taking Index into consideration while running update query on PLAN table. Am I missing something?

Answer 1

That could be caused by unindexed foreign keys. Tom Kyte has a query that you can use to find them. It could be as simple as adding a few indexes on the child tables.

Unindexed Foreign Keys