Re: How to avoid waiting for locks during delete

"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

-- 
-------------------- http://NewsReader.Com/ --------------------
Usenet Newsgroup Service              New Rate! $9.95/Month 50GB