Re: Drop Foreign Key and Insert causing deadlock

From: Vasu <vasudevanr_at_gmail.com>
Date: Tue, 25 Feb 2014 13:58:21 -0600
Message-ID: <CA+O6cLL4z6j54ZPRwqY1Qpzp30zg0siY-p=eJ08DCrCejgDxbw_at_mail.gmail.com>

The archival job works on a reference partitioned table using exchange-partition method, that require the above discussed step.

Looks like a not-so-great work-around would be , doing a CTAS of child table and then dropping the partition .

Appreciate your interest.

On Tue, Feb 25, 2014 at 1:32 PM, Mark W. Farnham <mwf_at_rsiz.com> wrote:

> 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:
>
> 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
>
>
>
>

-- 
-Vasu

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

Received on Tue Feb 25 2014 - 20:58:21 CET