Drop Foreign Key and Insert causing deadlock
- From: Vasu <vasudevanr@xxxxxxxxx>
- To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 25 Feb 2014 11:27:38 -0600
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
Other related posts:
