Re: Unindexed FK Cause Deadlock or Only Share Lock?


If you attempt to delete a parent row,
or update the key value of a parent
row in a parent/child relationship where
the child DOES NOT HAVE an index on the foreign key, then Oracle will
attempt to acquire a mode 4 lock
on the child table (or mode 5 if it
has previously modified the child
table).

If any other sessions are currently
modifying the child table, your session
will have to wait before it can acquire
its mode 4/5 as the other sessions will
be holding mode 3 and therefore will
be blocking you.

Anyone who tries to start a new transaction
on the child table (and therefore need to
acquire a mode 3) will be blocked behind
your request for a mode 4.


To engineer a deadlock:
   session A
       delete child row C1
       acquires mode 3 on child table

   session B
       delete child row C2
       acquires mode 3 on child table

   session A
       attempts to delete parent of C1
       attempts to convert mode 3 to mode 5
       blocked by session B holding mode 3
           therefore starts to wait

   session B
       attempts to delete parent of C2
       attempts to convert mode 3 to mode 5
       blocked by session A in the converters queue
           (viz: holding 3, and waiting to convert to 5)
           therefore start to wait

   Three seconds or less later, session a gets
   an ORA-00060 Deadlock detected.



Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/appearances.html
Public Appearances - schedule updated June 22nd 2005






----- Original Message ----- From: "Post, Ethan" <Ethan.Post@xxxxxx>
To: "Oracle-L@Freelists. Org (E-mail)" <oracle-l@xxxxxxxxxxxxx>
Sent: Thursday, June 30, 2005 7:32 PM
Subject: Unindexed FK Cause Deadlock or Only Share Lock?



I am trying to find out if a missing index on a foreign key would cause
a deadlock. I always thought is would only cause a share lock and hold
up other DML, not deadlock it.

This asktom link http://tinyurl.com/djgco (search for deadlock and read
comments/responses) seems to suggest you only get a share lock.

However I was sent these links also...some don't seem to support the
idea of deadlocks but others do. Anyone know the answer here? Anyone
think of a quick test case which demonstrates this?


--
http://www.freelists.org/webpage/oracle-l

Other related posts: