Re: Delete of child-entry locks father-table

From: <hasta_l3_at_hotmail.com>
Date: 4 Dec 2006 23:02:58 -0800
Message-ID: <1165302178.305565.75420@80g2000cwy.googlegroups.com>

thomas.kyte_at_oracle.com wrote :

> 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>
>
> It does NOT.
>
> There is no lock placed on the parent table that would prevent any
> other transaction from modifying the parent table or reading it.

Dear Tom,

As I get it, the OP is actually asking why one cannot concurrently delete from a child table and do a LOCK TABLE of the parent (when using indexed foreign keys)

Session 1 (10.2)

---

create table p ( x int primary key );
insert into p values ( 1 );

create table c ( x references p );
create index cp_idx on c( x );
insert into c values ( 1 );
commit;

delete from c where 1=0;
  -> 0 rows deleted

Session 2

---

lock table p in exclusive mode;
  -> hanging !

Session 1

---

rollback;

Session 2

---

  -> table (P) locked

Session 1

---

delete from c where 1=0;
  -> hanging !

Congratulations for your books, BTW !

• Raoul

Received on Tue Dec 05 2006 - 01:02:58 CST