Amar,
That's the whole point Jared was trying to make: your
locking issue is because you *don't* have indexes on
your foreign keys.
You might want to review the following notes:
11828.1 " FOREIGN KEYS, INDEXES AND PARENT TABLE
LOCKING"
33453.1 "(V7) REFERENTIAL INTEGRITY AND LOCKING"
38373.1 "TECH: Example Table Locks During Referential
Integrity Enforcement"
61552.1 "DIAGNOSING DATABASE HANGING ISSUES"
15476.1 "Detecting and Resolving Locking Conflicts"
HTH,
-- Anita
--- Amar Kumar Padhi <[EMAIL PROTECTED]> wrote:
> No indexes on foreign key.
> still the same result.
>
> rgds
> amar
>
> -----Original Message-----
> Sent: Monday, June 11, 2001 2:05 PM
> To: Multiple recipients of list ORACLE-L
> overhead?...soluti
>
>
> Jared has a point. Is there an index on the foreign
> key?
>
> Another minor detail: You should of course never
> update a primary key
> column, of
> course :-)
>
> Amar Kumar Padhi wrote:
>
> > Jared,
> > I tried again and got the same result.
> > on oracle 8.1.7 (RBO).
> >
> > session1 : delete from emp where deptno = 10;
> >
> > session2 : delete from dept where deptno = 30;
> > session 2 hanged and waited till transaction in
> session one was committed.
> >
> > the max_trans on both tab is set to 255, incase
> this raises doubt.
> > are you getting a different result? Is the FK
> present on the detail table
> > you are trying with?
> >
> > rgds
> > amar
> >
> > -----Original Message-----
> > Sent: Monday, June 11, 2001 10:25 AM
> > To: Multiple recipients of list ORACLE-L
> > overhead?...soluti
> >
> > On Sunday 10 June 2001 21:50, Amar Kumar Padhi
> wrote:
> > > - locks
> > > Oracle generates internal locks on dict tables
> to maintain the data
> > > integrity.
> > > e.g.: I deleted a record from detail table in
> one session. In another
> > > session I tried to delete a master record not
> related to the deleted
> > detail
> > > record. The 2nd session hanged. There were
> row-exclusive locks present
> on
> > > the detail table and an exclusive lock present
> on an internal table (I
> > > couldn't access). The same is the case with
> update.
> > >
> > > Oracle locks both the master & detail table
> records when the master
> record
> > > is deleted and the foreign key is created with
> on delete cascade option.
> > >
> > > Bottomline, Oracle doesn't allow altering of
> master table primary key
> when
> > > active transactions exists on detail table.
> Oracle will allow update of
> > > columns other than the primary key.
> >
> > You should probably test this scenario, as it
> works fine for me.
> >
> > Delete the a child detail row.
> > Delete an unrelated parent row, with delete
> cascade on the child table.
> >
> > Works fine.
> >
> > Possibly are missing an index? Some of these
> rules have changed I know,
> > but I'll let you look up the details.
> >
> > Bottom line, it does work.
> >
> > Jared
> > --
> > Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> > --
> > Author: Jared Still
> > INET: [EMAIL PROTECTED]
> >
> > 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: [EMAIL PROTECTED] (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).
> > --
> > Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> > --
> > Author: Amar Kumar Padhi
> > INET: [EMAIL PROTECTED]
> >
> > 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: [EMAIL PROTECTED] (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).
>
> --
> Venlig hilsen
>
> Mogens Nørgaard
>
> Technical Director
> Miracle A/S, Denmark
> Web: http://MiracleAS.dk
> Mobile: +45 2527 7100
>
>
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> --
> Author: Mogens =?iso-8859-1?Q?N=F8rgaard?=
> INET: [EMAIL PROTECTED]
>
> 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: [EMAIL PROTECTED] (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).
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> --
> Author: Amar Kumar Padhi
> INET: [EMAIL PROTECTED]
>
> 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: [EMAIL PROTECTED] (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!?
Spot the hottest trends in music, movies, and more.
http://buzz.yahoo.com/
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: A. Bardeen
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
