The 8.1.7 doco says:
"Figure 25-8 illustrates the locking mechanisms used
by Oracle when no index is defined on the foreign key
and when rows are being updated or deleted in the
parent table. Inserts into the parent table do not
require any locks on the child table. "
thus updates and deletes are not distinguished.
And this behaviour can be proved:
Session 1:
SQL> create table par ( x number primary key );
Table created.
SQL> insert into par values (1);
1 row created.
SQL> insert into par values (2);
1 row created.
SQL> create table chi ( c number, x number);
Table created.
SQL> alter table chi add constraint chi_fk
2 foreign key ( x) references par(x);
Table altered.
SQL> insert into chi values (999,2);
1 row created.
SQL> insert into chi values (998,2);
1 row created.
SQL> commit;
Commit complete.
Then either of the two following changes:
SQL> delete from par where x = 1;
SQL> update par set x =1 where x = 1;
locks the following statement in Session 2:
insert into chi values (997,2);
hth
connor
- Rachel Carmichael <wisernet100_at_yahoo.com> wrote:
> oh man I could have used this information a few
> weeks ago, during
> database design......
>
>
> --- "Magaliff, Bill" <Bill.Magaliff_at_lendware.com>
> wrote:
> > Hope you find this useful . . . this info enabled
> me to drop appx 200
> > indexes that had solely been created to avoid
> supposed parent-table
> > locking
> > . . .
> >
> > I'd been trying to determine locking patters on
> parent-child tables
> > with and
> > without foreign key indexes. Oracle docs (notably
> 11828.1, which
> > does say
> > for Kernel version 7) specifies a shared lock on
> parent table when
> > updating
> > child table with no index on foreign key. My
> testing on 8.1.7
> > disproved
> > this - index or no index, there was never a lock
> on the parent table.
> >
> >
> > Having not seen any doc regaring versions 8+, I
> opened a TAR to
> > clarify.
> > Wanted to share the outcome:
> >
> > bill
> >
> > *************************************************
> >
> > Oracle rep says:
> > We no longer will acquire a share lock on the
> parent table
> >
> > The only difference that the FK index makes is
> that if a delete is
> > done on
> > the parent table, no FK index results
> > in a share lock being acquired on the child table.
>
> >
> > An index means that no such lock is acquired.
> > (I confirmed this)
> >
> > I said:
> > "from a performance standpoint this should not
> matter, right? (as
> > long as
> > none would
> > try to modify the child table while the dml on the
> parent is
> > happening, and
> > thus have to
> > wait for the share lock to be released). And from
> a r/i perspective,
> > the FK
> > definition should
> > prevent the deletion of a parent value referenced
> in the child table,
> > index
> > or no index.
> > So as long as I manage who performs DDL on my
> tables (as a good dba
> > should)
> > I should safely be able to drop any indexes on FK
> columns are not
> > used in
> > where-clauses of joins
> > and reduce my overhead."
> >
> > Oracle rep says:
> > "Yes, your assessment is correct. Provided you can
> manage DDL
> > operation, you
> > can safely
> > drop any indexes on FK columns that are not used
> in 'where' clauses
> > of
> > joins."
> >
> >
> >
> >
> >
> >
> >
> > --
> > Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> > --
> > Author: Magaliff, Bill
> > INET: Bill.Magaliff_at_lendware.com
> >
> > Fat City Network Services -- (858) 538-5051
> FAX: (858) 538-5051
> > San Diego, California -- Public Internet
> access / Mailing
> > Lists
> >
>
> > To REMOVE yourself from this mailing list, send an
> E-Mail message
> > to: ListGuru_at_fatcity.com (note EXACT spelling of
> 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB
> ORACLE-L
> > (or the name of mailing list you want to be
> removed from). You may
> > also send the HELP command for other information
> (like subscribing).
>
>
> __________________________________________________
> Do you Yahoo!?
> Yahoo! News - Today's headlines
> http://news.yahoo.com
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> --
> Author: Rachel Carmichael
> INET: wisernet100_at_yahoo.com
>
> Fat City Network Services -- (858) 538-5051 FAX:
> (858) 538-5051
> San Diego, California -- Public Internet
> access / Mailing Lists
>
> To REMOVE yourself from this mailing list, send an
> E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of
> 'ListGuru') and in
> the message BODY, include a line containing: UNSUB
> ORACLE-L
> (or the name of mailing list you want to be removed
> from). You may
> also send the HELP command for other information
> (like subscribing).
Connor McDonald
http://www.oracledba.co.uk
http://www.oaktable.net
"Remember amateurs built the ark - Professionals built the Titanic"
Do You Yahoo!?
Everything you'll ever need on one web page
from News and Sport to Email and Music Charts
http://uk.my.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: =?iso-8859-1?q?Connor=20McDonald?=
INET: hamcdc_at_yahoo.co.uk
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Received on Sat Sep 14 2002 - 14:43:19 CDT