RE: Unindexed FK Cause Deadlock or Only Share Lock?

Not sure if you caught this in my earlier email, but the concurrency is improved, i.e. the locking is relaxed, in later versions of Oracle so in 9i (and presumably in 10g, but I haven't checked), it us much less of a problem - see below from Metalink note 15476.1:

"When indexes are not present on child table foreign keys columns, Oracle requires, on top of the previous locking situation: a) in 8.1.7, 'mode 4 Share' locks on the child table when updating/deleting from the parent table. The lock mode even becomes a 'mode 5 S/Row-X (SSX)' lock when deleting from the parent table with a 'delete cascade' foreign key constraint.Those locks can't be disabled (ORA-00069) and are held during the full transaction time. b) in 9.0.1, Oracle only need those additional locks during the execution time of the UPDATE or DELETE. Those locks are downgraded to 'mode 3 Row-X (SX)' locks when the execution is finished. It is thus an improvement compared to Oracle 8.1.7. c) in 9.2.0, the downgraded 'mode 3 Row-X (SX)' locks are no longer required except when deleting from a parent table with a 'delete cascade' constraint."

Also, here's another good section from the Concepts guide too - be sure to read the section titled "Concurrency Control, Indexes, and Foreign Keys": http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96524/c22integ.htm#2161

-----Original Message-----
From: Post, Ethan [mailto:Ethan.Post_at_ps.net] Sent: Thursday, June 30, 2005 2:07 PM
To: Allen, Brandon; jonathan_at_jlcomp.demon.co.uk; Oracle-L_at_Freelists. Org
(E-mail)

Subject: RE: Unindexed FK Cause Deadlock or Only Share Lock?

Sorry, I should have mentioned it is an even 50/50 distribution.

My point was you have a mechanism within Oracle which avoids any type of data inconsistencies while avoiding the deadlock by using the index. Not understanding the mechanics of this I have to ask if there should not be some way Oracle could "simulate" this type of operation without requiring the index? I tried to come up with an example in which the requirement to scan the index blocks would be a large operation just like scanning the table blocks.

This is probably the type of question I will "ask" Tom at Hotsos 2006 then stare back blankly and pretend I understand when he answers me.

My guess is that this could be coded somehow with oracle.exe but isn't really required since we should all be indexing FK's.

-----Original Message-----
From: Allen, Brandon [mailto:Brandon.Allen_at_OneNeck.com] Sent: Thursday, June 30, 2005 3:59 PM
To: Post, Ethan; jonathan_at_jlcomp.demon.co.uk; Oracle-L_at_Freelists. Org
(E-mail)

Subject: RE: Unindexed FK Cause Deadlock or Only Share Lock?

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

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.

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