RE: Unindexed FK Cause Deadlock or Only Share Lock?
- From: "Post, Ethan" <Ethan.Post@xxxxxx>
- To: <jonathan@xxxxxxxxxxxxxxxxxx>, "Oracle-L@Freelists. Org (E-mail)" <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 30 Jun 2005 15:36:32 -0500
I was hoping one of the more brilliant scienticians from the other side
of the pond would chime in. If I may beg you to waste a little more time
with my humble request. What happens when there is an index, and let's
assume the index only contains 2 distinct values over zillions of
records. Have we eliminated the deadlock scenario below entirely? I am
all for indexing my FK's but was not aware such a otherwise useless
index (as described in previous sentence) could be so useful?
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Jonathan Lewis
Sent: Thursday, June 30, 2005 3:21 PM
To: Oracle-L@Freelists. Org (E-mail)
Subject: 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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
