Re: How to avoid waiting for locks during delete

From: <ctcgag_at_hotmail.com>
Date: 14 Aug 2003 17:05:25 GMT
Message-ID: <20030814130525.510$KT@newsreader.com>

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

Aircraft are physical objects, with mass and dimensions. How does one "delete" an aircraft? I hope to god I never ride on an aircraft that has been "deleted" from your system. Someone may have unscrewed the bolts that hold the wings on, and committed. Then someone else came and "deleted" the aircraft. Now, no one knows to tighten the bolts back up, because they officially no longer exist.

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

Do people at your company just wander around doing whatever they feel like, as long as it's allowed by Oracle's transaction system? Again, I hope to God I am never on one of your aircraft.

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

Oracle's job is to identify microconflicts, not macroconflicts.

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

Then make your *application* maintain integrity and inform the user about such conflicts. It isn't Oracle's job to do so. Since you don't want your application to know that the conflicting tables even exist, then good luck with notifying the user about them.

>

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

How do you defer an update until after the things to be updated are deleted? You seem to be assuming the delete will always come in a millisecond after the update, but there is no reason it couldn't be the other way around.

Xho

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

Received on Thu Aug 14 2003 - 12:05:25 CDT