RE: Drop Foreign Key and Insert causing deadlock
Date: Tue, 25 Feb 2014 14:32:32 -0500
Message-ID: <16af01cf3260$550d7bf0$ff2873d0$_at_rsiz.com>
At this point I would like an explanation of what the archival job is supposed to accomplish. I don't quite understand why archiving would require dropping and adding a constaint.
Oh - are you also purging as part of the archive job?
Please explain the whole job and what you mean by archiving.
Depending on what your job needs to do you may be able to accomplish this with some form of partition swapping or synonyms and a two-table dance so you current app doesn't know the difference.
If you did have control of the app you might be able to pause dml by queuing on a sentinel table's row to force exactly one of these things to happen at a time.
But I still don't know how to mix a loops of ddl and dml arbitrarily firing against each other.
mwf
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
On Behalf Of Vasu
Sent: Tuesday, February 25, 2014 1:50 PM
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 .
On Tue, Feb 25, 2014 at 11:57 AM, Mark W. Farnham <mwf_at_rsiz.com> wrote:
- 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-lReceived on Tue Feb 25 2014 - 20:32:32 CET