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: <hasta_l3_at_hotmail.com>
Date: 8 Dec 2006 12:39:48 -0800
Message-ID: <1165605864.315327.216950@73g2000cwn.googlegroups.com>


> but they wrote:
>
> > > > 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>
>
> that is
>
> a) don't look at the lock table
> b) why does a delete from child lock parent
>
> It seems unambiguous to me - no lock table is involved and they say an
> operation on child locks parent - which it doesn't
>

Well, the OP noticed - as documented in note 223303.1 - that a delete on a child table takes a Row-SS lock on the parent table. Granted, the lock does not prevent DML on the parent table, but it still qualifies
as a table lock, isn't it ?

At any rate, I find the bigger picture more interesting. As I get it, the OP wanted to manually lock in exclusive mode some configuration (or definition or type) tables, to prevent concurrent changes by two users - not a completely stupid idea, IMO.

And then, he noticed that the routine OLTP work was blocked when deleting rows of tables that reference the manually locked configuration
tables, when the reference is (rightfully) enforced by an indexed foreign
key.

An interesting gotcha, dont you think ?

> so, I guess unless they clarify - we'll never really know :)

Well, I dont think the OP will dare to post anymore. He was told to read a few hundred pages before coming back...

(not saying that the advice is bad - in general)

Best regards, Tom

Received on Fri Dec 08 2006 - 14:39:48 CST

Original text of this message

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