Re: Unindexed FK Cause Deadlock or Only Share Lock?

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_at_ps.net> To: <jonathan_at_jlcomp.demon.co.uk>; "Oracle-L_at_Freelists. Org (E-mail)" <oracle-l_at_freelists.org> 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?

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