RE: Unindexed FK Cause Deadlock or Only Share Lock?
- To: <Ethan.Post@xxxxxx>, <jonathan@xxxxxxxxxxxxxxxxxx>, "Oracle-L@Freelists. Org (E-mail)" <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 30 Jun 2005 13:59:13 -0700
Ethan, I don't think the cardinality makes any difference (somebody please
correct me if I'm wrong). When you update/delete from the parent table of a FK
relationship with no index on the FK in the child table, the *entire* child
table is locked. Also, in your example, the index with 2 distinct values could
prove to be very useful if they are unevenly distributed, for example if you
have 10 "N"s and 1000000 "Y"s, the index would be very efficient for finding
the "N"s.
Regards,
Brandon
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Post, Ethan
Sent: Thursday, June 30, 2005 1:37 PM
To: jonathan@xxxxxxxxxxxxxxxxxx; Oracle-L@Freelists. Org (E-mail)
Subject: RE: Unindexed FK Cause Deadlock or Only Share Lock?
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
Privileged/Confidential Information may be contained in this message or
attachments hereto. Please advise immediately if you or your employer do not
consent to Internet email for messages of this kind. Opinions, conclusions and
other information in this message that do not relate to the official business
of this company shall be understood as neither given nor endorsed by it.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
