Re: How to avoid waiting for locks during delete

From: Billy Verreynne <vslabs_at_onwe.co.za>
Date: 14 Aug 2003 06:32:40 -0700
Message-ID: <1a75df45.0308140532.748c7975@posting.google.com>

"André Hartmann" <andrehartmann_at_hotmail.com> wrote

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

That can be solved in many different ways.. and is not an issue related to transaction processing & locking. Features such as workflow and workspaces comes to mind.

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

Partitioning. I replace the stale data in a partition with the fresh data in a table (in fact Oracle simply swaps ownership of the data around).

The basics:

- create new table with fresh data
- index it & analyze it
- issue an alter table exchange partition to swap the fresh

  data into the partitioned table and swap the stale data out   into the new table

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

Oracle Workspace.

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

I disagree. The business process in the form of an Oracle transaction has failed. Failure does not imply success. Usually.. ;-)

--
Billy

Received on Thu Aug 14 2003 - 08:32:40 CDT