Re: How to avoid waiting for locks during delete

"André Hartmann" <andrehartmann_at_hotmail.com> wrote in message news:<3f38ae0c$1_at_olaf.komtel.net>...
> 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
> .
>

Yes it did. The only rows being locked now are the ROWS being deleted. However, you are trying to delete from the WHOLE child table, so therefore you are being blocked by the delete of the ROWS in question locked when you delete the parent table.

Basically your saying:

Delete From Parent and get all its child records. Do NOT Commit.
Transaction is in doubt.

Now User 2 comes along:

Delete all from Child table:
However, the child records you want to delete are locked. It doesnt have an 'image' to delete.

Yes, I would prefer a NO WAIT option on delete, but what you can do before deleting ALL from child table is check v$locked_object and do a join to DBA_OBJECTS to see if that table has a lock on it. If not proceed. If so, dont do it.

You should not be doing a DELETE FROM CHILD TABLE unconditionally and you should probably issue a 'commit' in the application when you do a delete from the parent table cascade.

This is bad design. Oracle is ONLY locking the records in question with foreign keys.

> "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.
Received on Tue Aug 12 2003 - 11:09:54 CDT