Re: Drop Foreign Key and Insert causing deadlock

From: Mark Bobak <Mark.Bobak_at_proquest.com>
Date: Tue, 25 Feb 2014 18:21:08 +0000
Message-ID: <CF32491C.41833%Mark.Bobak_at_ProQuest.com>

Totally agree with Tim and Mark, just because you can, doesn't mean you should.

Just to clarify, the deadlock, when it occurs, is ORA-04020, not ORA-00060, correct?

-Mark

--

Mark J. Bobak, Lead Oracle DBA

ProQuest | 789 E. Eisenhower Pkwy | Ann Arbor, MI 48108-3218 USA | +1 734 997 4059

www.proquest.com<http://www.proquest.com/>

ProQuest... Start here. 2013 InformationWeek 500 Top Innovator

From: "Mark W. Farnham" <mwf_at_rsiz.com<mailto:mwf_at_rsiz.com>> Reply-To: "mwf_at_rsiz.com<mailto:mwf_at_rsiz.com>" <mwf_at_rsiz.com<mailto:mwf_at_rsiz.com>> Date: Tuesday, February 25, 2014 at 12:57 PM To: Vasu <vasudevanr_at_gmail.com<mailto:vasudevanr_at_gmail.com>>, "oracle-l_at_freelists.org<mailto:oracle-l_at_freelists.org>" <oracle-l_at_freelists.org<mailto:oracle-l_at_freelists.org>> Subject: RE: Drop Foreign Key and Insert causing deadlock

1. I don't think so, other than not intentionally tossing locks in each other's way
2. 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.

Clue me in if I've got that wrong.

mwf

From: oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org> [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Vasu Sent: Tuesday, February 25, 2014 12:28 PM To: oracle-l_at_freelists.org<mailto:oracle-l_at_freelists.org> Subject: Drop Foreign Key and Insert causing deadlock

Hi ,

I run into deadlock when one session is dropping Foreign key from a table while other session is inserting into the same table.

Is there any safe trick to avoid deadlock ?! Im already using DDL_LOCK_TIMEOUT in the DDL session.

To reproduce the problem , I run 2 sessions : Session-1 runs a INSERT loop loading TEST table , commiting every 20 records, session-2 , performs ALTER TABLE... add constraint novalidate and DROP CONSTRAINT in a loop.

Session-2 is able to add and drop the constraints 100s of times, then drop constraint times out a few times (expected behaviour from ddl_lock_timeout) but then fails with a deadlock sporadically , which I want to eliminate.

Oracle Ver is 11.2.0.3 .

Thanks,
Vasu

--

http://www.freelists.org/webpage/oracle-l Received on Tue Feb 25 2014 - 19:21:08 CET