Re: How to avoid waiting for locks during delete

"André Hartmann" <andrehartmann_at_hotmail.com> wrote in message news:<3f3a526e$1_at_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.

you will need to build logic to hit the data dictionary before doing your delete. This will cause a performance hit if you do it alot.

go to v$locked_object and do a join to DBA_OBJECTS. this will tell you what object is locked. Wont tell you the row level locks though.

for update should ONLY be used to serialize transaction control and for very short updates.

Second off, in that example it is a waste to use a cursor with for update. A straight update is better. The update will update the before image if there is a delete. Delete will still take place.

Its always better to use straight sql then to use pl/sql with sql inside of it.
FOR UPDATE is inappropriate in this place. Received on Fri Aug 15 2003 - 06:14:53 CDT