Re: How to avoid waiting for locks during delete

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

Why do we have transactions? Multiple people changing the same data and we need to have data integrity. ACID.

If a person can decide "hmm.. I cannot update right now.. hmm.. maybe I should wait and try again in 6 hour's time.." is just DANGEROUS.

What if he forgets to do the update? What if he does the update, but forgot what exactly to update, and then go and update the wrong client account with a discount?

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

Exactly. It is an APPLICATION problem. A DESIGN problem. It has *nothing* to do with not having a DELETE NOWAIT in Oracle.

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

There are many ways to skin this cat WITHOUT impacting on the users. Just the basic concept of needing to run multi-day transactions on production data send shivers down my spine and have me reaching for my lead pipe to beat the holy crap out of something or someone.

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

I can and do remove 10's of GB of old data from a production table (VLT) and replace that with 10's of GB of new data.. in seconds in Oracle. And this is done WITH indexes and WITH analyzer stats on the new data. And yeah.. 14GB in _less_ than a second on a VLT of over 500 million rows the last time I timed this processing step.

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

Come on. That addresses the SYMPTOM of the problem and not the CAUSE of the problem.

Fact. The data MUST change. That is after all WHY the user wants to update/delete it. It MUST be done.

Fact. After this silly no-can-do-yet message, the data HAS NOT CHANGED. You call that solving the problem?

> That is a common human conflict resolution: the database can only
> indicate conflicts (locks) but seldomly solve them.

Oracle does solve them. The transaction will either succeed or not. No maybe-that-should-be-retried-again-in-30-minutes-to-succeed. Even better, Oracle does it in such a way to ENSURE the integrity of your data. The ACID test.

To solve the "human interface problem" does not mean having the human decide whether or not changing business data at that point in time due to TECHNICAL db considerations. How can correct business decisions be made when you are not sure whether Joe Soap has done his job and updated/delete/changed the data he should have?

There are many features in Oracle that addresses this problem. Workflow. Replication. Partitioning. AQ. Etc.

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

Whether data is locked or not locked should not have ANY consideration in the client. What are you going to check next? Check if there's enough space for a next extent in a tablespace? Check if the maxextents are not set too low? That you are causing chained rows?

IMO André, this is a design issue and transgressing a fundemental concept of how transactions must be used within a RDBMS.

If we can replace 10's or even 100's of GB in an Oracle table within _seconds_.. that tells me that you are doing something seriously wrong by locking GB's of data for days on end with a single transaction.

--
Billy