Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: How to avoid waiting for locks during delete

Re: How to avoid waiting for locks during delete

From: André Hartmann <andrehartmann_at_hotmail.com>
Date: Thu, 14 Aug 2003 12:10:38 +0200
Message-ID: <3f3b6022@olaf.komtel.net>

"Billy Verreynne" <vslabs_at_onwe.co.za> schrieb im Newsbeitrag news:1a75df45.0308140108.3ddc3f52_at_posting.google.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.
>
> Why do we have transactions? Multiple people changing the same data
> and we need to have data integrity. ACID.

  Yes, of course! I am not denying that. If I didnt bother about integrity, I would just remove my foreign key constraints and get rid of the lock between parent and child table. But of course I want to have my integrity expressed and obeyed in the schema.

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

  Why is it dangerous ? If someone else is working on some object that will be affected by my work as well, I rather want to wait until that person is finished or even get the chance to talk to that person. In my particular case: If someone updates aircrfat engines on an aircraft that I want to delete, obviously we have a human conflict here: I want to delete the aircraft but someone is still working on it. So is my intention to delete it correct ? I rather talk to the person who manipulates the engines what he is doing there and tell him the aircraft is about to go anyway and if he doesnt want to continue work on some other aicraft or he tells me i must not delete. I take this as a means of conflict resolution which emerges out of many people working on the same thing. The application would detect that conflict (using oracle) and let the user decide how to handle it.

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

Okay, I have a table of around 100 million rows, around 10 columns, 500 bytes per row (average), only INTs and VARCHARs and CHARs in the table. Two indexes on the table for rapid select. Even a delete takes 20 seconds already (Oracle Enterprise Server 9i, Windows 2000, 500MB RAM, 1GHz Athlon processor). So I would be very curious how you can not only delete but even replace (delete + insert) 100 million rows in just a second.

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

The cause of the problem is that two people work on the same thing. It is a requirement by our customer that the application maintains integrity and informs the user about such conflicts.

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

Yes, it must be done. But not parallel to some other operation that wants to change the same data. My degree of freedom is TIME here. If I had the chance to let Oracle detect the collision, I can suggest to the user to defer the concurrent operation wich is acceptable to our customer.

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

YES! See above.

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

  I admit I am not familiar with Oracle Workflow and Partitioning. It's probably a valuable hint, I will dig into it and see what it can contribute :)

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

  Oh, as stated above, I would be really curious to see an example how you do that. my boundary conditions are: It must be done within a transaction so that in the end i can decide: commit or rollback! So, no DDL statements please (temporary dropping of indices, dropping/truncating/recreating tables etc...)... :)

  Thank you :)

>
> --
> Billy
Received on Thu Aug 14 2003 - 05:10:38 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US