I'm much more surprised when you're *not* running into a deadlock than the demonstrated off-chance when you are.
DDL commands aren't intended to mix with DML, by design. That's why DML enqueues are obtained by INSERT, UPDATE, DELETE, MERGE, and SELECT ... FOR UPDATE on the objects being modified, and that's why these enqueues (i.e. TYPE='TM' and ID1 = obj#) are globally visible in GV$LOCK. DDL commands check GV$LOCK to find out if a transaction is in progress on the object to be altered, and either enqueues behind it or fails if so.
If you want to eliminate these deadlocks, then eliminate the DDL and DML coinciding. DDL should not be part of the functionality of an application.
There are famous old vaudeville routines <http://en.wikipedia.org/wiki/Smith_%26_Dale> that go like this...
* Patient: "Doctor! Doctor! It hurts when I do this!" (/waves arms and hands vigorously //and comically/) * Doctor: "Then don't do that!"As another old saying says, "just because you *can* do something doesn't mean that you *should*."
On 2/25/2014 10:27 AM, Vasu wrote:
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
