Re: How to avoid waiting for locks during delete

From: André Hartmann <andrehartmann_at_hotmail.com>
Date: Wed, 13 Aug 2003 16:59:55 +0200
Message-ID: <3f3a526e$1@olaf.komtel.net>

Hi,

  unfortunate to say you missed my point. The index thing is interesting to know. But its not the issue here. Maybe its my fault because I was generalizing my sample statements. Consider this example and you will have to admit i get the same "waiting for release" behaviour even if i deploy your foreign key index:

Session 1:

select * from b where foreign_key_to_A = 177 for update nowait; update b set Name='New Name' where foreign_key_to_A = 177; //no commit, commit will follow 7 hours later because much more work to do int his transaction ! result: rows with foreign_key_to_A = 177 are now locked in B for the next 7 hours.

Session 2:
delete from a where key = 177;
//oops, this will wait for 7 hours until Session 1 is done! ... no "NOWAIT" option. My application will just look as if it is hung.

Dont get me wrong: I am not complaining about the lock. The lock is fine ! I dont want session 2 to delete while session 1 is still at changing records! But I want a notification in session2 that there is this lock, a notification like i get when i use NOWAIT in a SELECT statement.

Okay ?

"Ryan Gaffuri" <rgaffuri_at_cox.net> schrieb im Newsbeitrag news:1efdad5b.0308120809.7d82dcd7_at_posting.google.com...
> "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 Wed Aug 13 2003 - 09:59:55 CDT