Re: Drop Foreign Key and Insert causing deadlock

From: Vasu <>
Date: Tue, 25 Feb 2014 12:50:28 -0600
Message-ID: <>

I have got ORA-04020, ORA-00060 (and also ORA-00054 which is timeout as expected).

Rest of the reply inline .

On Tue, Feb 25, 2014 at 11:57 AM, Mark W. Farnham <> wrote:

> a) I don't think so, other than not intentionally tossing locks in
> each other's way

// An archival job has to do this once a week ONLINE , but during testing this failed (=> It is likely to
repeat in PROD though it may be rare). To reproduce the error, we need to repeat this 100s of times
(and to demonstrate that using workaround like DDL_LOCK_TIMEOUT doesn't save the situation).

// While I can tweak the DDL code (because its still in Dev/Testing), I can't touch the App doing DML which is there for a long time.

// So, is there a way to foolproof the new code to avoid this scenario ?!  . That's the situation.

> b) I am at a loss to imagine a valid use case. I suppose from time to
> time you might have a situation where you need to do this once or twice
> (though I'm not making that claim), but doing this in a loop seems absurd.
> // Yes, I want to catch/avoid this.. even if this is once in a while.
> Hence the attempt.

> Clue me in if I've got that wrong.

// I expect the ddl_lock_timeout to work gracefully , either grab a successful DDL lock or timeout ,
but it fails once in a while with deadlock , So can I say DDL_LOCK_TIMEOUT is un-reliable.

> mwf

Received on Tue Feb 25 2014 - 19:50:28 CET

Original text of this message