Re: How to avoid waiting for locks during delete

ctcgag_at_hotmail.com wrote in message news:<20030813180917.352$w3_at_newsreader.com>...
> "André Hartmann" <andrehartmann_at_hotmail.com> wrote:
> > 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:
>
> Are you sure the problem persists in your actual situation once the
> index is there?
>
> >
> > 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.
>
> OK, but that seems to be the least of your problems. What if session 2
> got in ahead of session 1? Now session 1 wants to update data that is
> not longer there. How can this possibly be acceptable? Presumably session
> 1 isn't just updating data for the fun of it. It seems to me that you
> shouldn't delete data until you are damn sure no one else needs it, and
> if you are damn sure no one else needs it, you'd never encounter this
> locking problem in the first place, unless people indiscriminantly lock
> data they don't need.
>
>
> > 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!
>
> Why not? What's the point of letting session 1 change records if those
> records will just be deleted immediately after session 1 commits?
> (Not that this way would be easier to implement, mind you...)
>
> If I'm going to shred documents, I don't have others go through correcting
> all the spelling mistakes first.
>
> > 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 ?
>
> Well, I guess it's Okay to want it, but it isn't there.
> How about having the application time out the call after a certain
> period?
>
> Xho

ignore my post abotu the straight update not locking. it was early and i was being stupid.

the only way to know in advance would be to query v$locked_object and join to dba_objects to get the object_Names that are locked. If you have a transaction that takes 7 hours AND you have a business reason to need those rows unlocked you may want to consider committing sooner. It depends on your application logic.

you could also implement some logic as follows. When a long running batch runs take a lock out using DBMS_LOCK, then you can check to see if that lock is taken out before you delete a specific table. If you have to do this alot, you may want to consider it over hitting the v$views since hitting those repeatedly can cause performance issues.

basics are that you have to write your own logic for this. Oracle wont do it for you. Good side is that its not alot of logic.

You may not have control over business process here(im find myself in that situation all the time) however, its probably best to re-evaluate why you need that 7 hour transaction? And why another user might want to delete a record that that long running transaction is using. We dont always get a say in things like that. I understand. Its often... this is bad, fix it. Received on Fri Aug 15 2003 - 07:57:20 CDT