Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Delete of child-entry locks father-table

Re: Delete of child-entry locks father-table

From: HerbertMue <HerbertMueller3_at_gmx.at>
Date: 4 Dec 2006 03:36:53 -0800
Message-ID: <1165232213.342208.238580@j72g2000cwa.googlegroups.com>

hasta_l3_at_hotmail.com schrieb:

> HerbertMue wrote :
>
> > Thanks for specifying in detail.
> >
> > But even now I do have the following question (don't look at the lock
> > table and the "other bad code"). I only want to get answered this
> > principal (fundamental, basic) question.
> > <Question>
> > Why does a DELETE of a child(slave)-entry causes a
> > lock of the father (master, parent) table???
> > </Question>
>
> Dear,
>
> Note that you are hitting a bug in Oracle's 9.2 documentation,
> adressed in metalink note 223303.1
>
> <Excerpt>
>
> Starting in version 9.2.0, a Row-SS lock is taken on the parent table
> for any DML issued against the child table. This will occur with or
> without an index on the foreign key column of the child table.
>
> ...
>
> the lock is held until the transaction is committed or rolled back.
>
> This change in behaviour was introduced in version 9.2.0 to address
> problems with parallel DML hanging in certain situations. This problem
> with PDML was addressed in Bug 1657607.
>
> This new locking behaviour is an expected behaviour for the Oracle
> code. As only shared locks are involved, it does not prevent DML from
> being issued against either the child or parent tables. It will prevent
> operations that require an exclusive table level lock. However, as it
> generally considered to be bad design to have an application
> implementing exclusive table locks, the impact of the change should be
> minimal.
>
> </Excerpt>
>
> --- Raoul

Thanks for your detailled and helpfully comment. Received on Mon Dec 04 2006 - 05:36:53 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US