7

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?

  • Try to make an index on column orderID and see if it helps. – Wernfried Domscheit Jul 8 '15 at 14:04
  • @Wernfried, thanks for the comments, I have updated my question with analysis of running missing index query. Pls check – Sauchin Jul 8 '15 at 22:16
  • 1
    Is orderid a number? Or a string? The name implies a number. The query you posted implies a string. What is the deadlock graph? A deadlock implies that A has a lock that B is waiting on and B has a lock that A is waiting on. You've shown us what one of the queries is waiting on but not what the other session is waiting on. Is it possible that other sessions are working with the same order at the same time? You may need the session that changes status to have exclusive access to the order. – Justin Cave Jun 13 '16 at 22:01
  • Is the query for updating the order status using a select for update clause on all relevant tables? This would block write access on the possible causes for the deadlock. If this is possible performance wise needed to be tested. – Grimaldi Feb 15 '17 at 16:11
  • 1
    If your plan.orderid is number, why are you forcing it as varchar2? try removing the single quotes around order id 234 in update. – Raj Mar 17 '17 at 19:59
0

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

  • I think such a conjecture combined with a link should be posted as comment but nor as an answer. – miracle173 Jul 8 '15 at 13:41
  • @Gandolf989, thanks for the comments, I have updated my question with analysis of running missing index query. Pls check – Sauchin Jul 8 '15 at 22:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.