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] On Behalf Of Vasu
Sent: Tuesday, February 25, 2014 12:28 PM To: 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