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
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@(protected)> To: "Oracle-L@(protected) Org (E-mail)" <oracle-l@(protected)> 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?
