RE: Drop Foreign Key and Insert causing deadlock
From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Tue, 25 Feb 2014 19:20:29 +0000
Message-ID: <CE70217733273F49A8A162EE074F64D901DDFF72_at_exmbx05.thus.corp>
From: oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org] on behalf of Vasu [vasudevanr_at_gmail.com] Sent: 25 February 2014 18:50
To: Mark W. Farnham
Cc: oracle-l_at_freelists.org
Subject: Re: Drop Foreign Key and Insert causing deadlock
Date: Tue, 25 Feb 2014 19:20:29 +0000
Message-ID: <CE70217733273F49A8A162EE074F64D901DDFF72_at_exmbx05.thus.corp>
Have you raised an SR and passed your demonstration - it's probably worth doing.
Why do you have to drop the constraint rather than disabling it ? Have you tried a cycle of "alter ... rely disable novalidate" / "alter ... enable novalidate" / "alter ... validate" When the ORA-00060 happens what type of locks are deadlocking ? Is the graph always the same ?
Have you considered simply not setting ddl timeouts and using a loop with a short dbms_sleep() to capture any locking errors and and retry after (say) 1/100the second ?
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
_at_jloracle
From: oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org] on behalf of Vasu [vasudevanr_at_gmail.com] Sent: 25 February 2014 18:50
To: Mark W. Farnham
Cc: oracle-l_at_freelists.org
Subject: Re: Drop Foreign Key and Insert causing deadlock
I have got ORA-04020, ORA-00060 (and also ORA-00054 which is timeout as expected).
Rest of the reply inline .
-- http://www.freelists.org/webpage/oracle-lReceived on Tue Feb 25 2014 - 20:20:29 CET