Re: Unindexed FK Cause Deadlock or Only Share Lock?
- From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
- To: "Oracle-L@Freelists. Org (E-mail)" <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 30 Jun 2005 23:33:52 +0100
Your question is a good question, and one which
I have been meaning to test for the last five years
or so.
Because an index entry MUST go in the right block,
Oracle can check for the existence of child rows of
the parent you are deleting (or updating) by looking
at the right block of the child index - if it has no
related child rows then your session can pin it in
exclusive mode in the buffer whilst modifying the
parent row. If it has child rows then your parent
update/delete must either wait or fail - depending on
whether or not the child row is an uncommitted row
from another transaction The answer to your specific
question about an index when there are only two
values with thousands of rows is therefore - Oracle
will find very rapidly that it can't change the parent,
so the number of rows for a value is irrelevant.
It's just bad luck that the index is otherwise inherently
a useless index. (But it sounds like a parent that
you shouldn't be able to update, so you could
try to disable table locks on the child to bypass
the issue).
But the thing I haven't tested is what happens if
there used to be thousands of leaf blocks that
ONCE HELD entries for a given value which have
been deleted but not cleaned out. It seems that your
session might have to pin thousands of blocks
in the buffer (to discover that there are no child
rows still in those thousands of blocks) prior to
modifying the parent - and that's not possible.
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: <jonathan@xxxxxxxxxxxxxxxxxx>; "Oracle-L@Freelists. Org (E-mail)"
<oracle-l@xxxxxxxxxxxxx>
Sent: Thursday, June 30, 2005 9:36 PM
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?
--
//www.freelists.org/webpage/oracle-l
Other related posts:
