Re: Drop Foreign Key and Insert causing deadlock

From: Vasu <vasudevanr_at_gmail.com>
Date: Tue, 25 Feb 2014 12:50:28 -0600
Message-ID: <CA+O6cLJLUC4_=oyMqySquHUCBGs1yQrkKtNfx16FWtCFC1-AVg_at_mail.gmail.com>

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

--
http://www.freelists.org/webpage/oracle-l

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