Re: How to avoid waiting for locks during delete

Hi,

  I tried it out: I indexed the foreign key columns in my child table. But I still get the same behavious: When session 1 deletes on B without commit, session to is stuck with delete on A for as long as session 1 doesnt commit/rollback. The indexing of the foreign key colum(s) didnt change a thing. :(

Andre
.

"Ryan Gaffuri" <rgaffuri_at_cox.net> schrieb im Newsbeitrag news:1efdad5b.0308110844.2ea4e918_at_posting.google.com... > "André Hartmann" <andrehartmann_at_hotmail.com> wrote in message news:<3f339d88$1_at_olaf.komtel.net>...
> > Hi everyone,
> >
> > I have a problem with waiting for release of locked rows during a
> > cascading delete. I am aware of the NOWAIT option but it is not
available to
> > a DELETE statement. Consider this to see my problem:
> >
> > create table a (Id INT primary key, Name VARCHAR2(100));
> > create table b (Id INT primary key, MyA INT REFERENCES a(Id) ON DELETE
> > CASCADE);
> > insert into a values (1, 'hello');
> > insert into b values(5, 1);
> > commit;
> >
> > now session 1 does this:
> >
> > delete from b where Id = 5; //no commit!!!!
> >
> > now session 2 does this:
> >
> > delete from a;
> > //session 2 is stuck here and has to wait
> > //for session 1 to commit or rollback because of the cascading
> > // delete in table b. I would like to receive an error like when you
> > // do SELECT ... FOR UPDATE NOWAIT. How can i do that ??
> >
> > AH
> > :(

>
>
> you only run into this locking issue when you have unindexed foreign
> keys. go to asktom.oracle.com type in 'index foreign key' he has a
> script that tells you
> which foreign keys are unindexed.
>
> index them. now only the rows in 'question' will have this problem and
> not the whole table.