Unindexed FK Cause Deadlock or Only Share Lock? 2005-06-30 - By Allen, Brandon
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 (See ORA-00069.ora-code.com)) 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@(protected)]
Sent: Thursday, June 30, 2005 2:07 PM
To: Allen, Brandon; jonathan@(protected); Oracle-L@(protected) 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@(protected)]
Sent: Thursday, June 30, 2005 3:59 PM
To: Post, Ethan; jonathan@(protected); Oracle-L@(protected) 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
|
|
