Drop Foreign Key and Insert causing deadlock

From: Vasu <vasudevanr_at_gmail.com>
Date: Tue, 25 Feb 2014 11:27:38 -0600
Message-ID: <CA+O6cLKMU56oyXipEUW8JD0CnsLU1c+W6ph0193g8dP_FQcH_A_at_mail.gmail.com>



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 - 18:27:38 CET

Original text of this message