Re: How to avoid waiting for locks during delete

"Billy Verreynne" <vslabs_at_onwe.co.za> schrieb im Newsbeitrag news:1a75df45.0308122105.6f1d9cec_at_posting.google.com...
> "André Hartmann" <andrehartmann_at_hotmail.com> wrote:
>
> > What I am sort of unhappy with is that in some circumstances (SELECT
..
> > FOR UPDATE NOWAIT) Oracle is able to tell you when there is a LOCK and
> > prevent you from waiting for an uncertain amount of time but instead
> > returning an error message... and in some other circumstances (cascading
> > delete) Oracle does NOT tell you that but you are stuck with a wait,
which
> > is nasty.
>
> Point understood André. However, why is it nasty?
>
> If you know that the data is locked with the DELETE/INSERT/UPDATE, how
> will this make it better? You cannot force the transaction through.
> All you can do is tell the user "sorry, no go - try again". How is
> that better than having to wait?

YES !!! That is a 1000 times better ! Imagine the whole thing from the user's point of view: It is better to have the application tell you that some operation cannot be carried out right now that keep quiet and just display the hour-glass for an uncertain amount of time.

>
> Is the problem here not rather _how_ users use the system and/or how
> the front-end is coded? What do you do in the case of a user causing a
> lock for 1 hours, 5 hours or even 10 hours?

Actually the transactions of our applications are measured in DAYS! They can run up to 3 days because huge amounts of data (dozens of GB) are being imported and complicatedly analyzed. Only at the end it is clear if the whole import is accepted or not.. so I cannot divide it into simpler/smaller transactions. Also I cannot truncate tables or disable indexes (which would make importing faster) because those are DDL statements which terminate my transaction and slow down other users who work on the "old" data during the hours/days the new data are being imported and analyzed.

> No amount of NOWAIT and "sorry - please retry" will solve the problem
> of users locking data for hours on an end.

Yes, it will solve the problem because the user will be notified! He/she can then decide what to do: maybe call the one who owns the lock and tell "hey, do you really need to keep this lock, it blocks my work..". That is a common human conflict resolution: the database can only indicate conflicts (locks) but seldomly solve them. In the case we are discussing here I am not asking oracle to solve my locks: I am only asking to detect them and tell me that they are there. :)

>
> --
> Billy
Received on Wed Aug 13 2003 - 09:53:59 CDT