Re: How to avoid waiting for locks during delete

From: <ctcgag_at_hotmail.com>
Date: 13 Aug 2003 22:36:14 GMT
Message-ID: <20030813183614.047$Xh@newsreader.com>

"André Hartmann" <andrehartmann_at_hotmail.com> wrote:
> >
> > 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.

Personally, I prefer the computer have an infinite amount of patience, and I just hit ctrl^C if I tire of it.

>
> >
> > 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.

Gosh. Oracle's transactions were not intended to by like a library book check-out system. Usually you use a staging area, or add a status column to the tables, for something like that.

But if the data is just being imported and analyzed, you should never run into the problem. The new data cannot be seen, and thus cannot be deleted, by anyone else. Only if this analysis involves updating or deleting pre-existing rows should it cause a problem.

> Only at the end it is clear if
> the whole import is accepted or not.. so I cannot divide it into
> simpler/smaller transactions.

Why can't you? Once the analysis has proven successful (in a set of smaller transactions), do:

update blah1 set embargoed_by=null where embargoed_by='Inspector_12';
update blah2 set embargoed_by=null where embargoed_by='Inspector_12';
update blah3 set embargoed_by=null where embargoed_by='Inspector_12';

commit;

Hopefully that won't take 3 days to accomplish!

Of course, you'd have to change the non-staging queries to all have "embargoed is null" in the where clause, or build views that accomplish that automatically.

Xho

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

Received on Wed Aug 13 2003 - 17:36:14 CDT