Re: How to avoid waiting for locks during delete

"Sybrand Bakker" <gooiditweg_at_sybrandb.demon.nl> schrieb im Newsbeitrag news:5v9ijv0ihf8bsn0qke2hf77a8nvsd0npke_at_4ax.com...
> On Tue, 12 Aug 2003 10:55:54 +0200, "André Hartmann"
> <andrehartmann_at_hotmail.com> wrote:
>
> >. Our applications handle
> >dozens of GB of data in this datamodel with an acceptable degree of both
> >consistency and performance.
>
> There is no such thing as 'an acceptable degree of consistency'
> Either the database is consistent or it is inconsistent. Allowing

No, the world is not black and white. Sometimes you have to weigh for performance reasons weather tomaintain a particular consistency condition in the database or in your application. For example, you might not have a foreign key constraint / index in the database because that slows inserts/updates down. Ifg inserts / updates are the most common operations, you would leave out the contraints / indexes in the database and make your application such that it is always guaranteed. So, yes... in the database you can actually choose how much consistency you want to implement there or outside of it.

> consistency by leaving out mandatory constraints should be considered
> bad practice: sooner or later you will get hurt.
> Also I am not aware of any application where cascade delete is so
> liberally used as you do. Obviously you seem also to abide on 'Don't
> let one hand do what the other does'. I wouldn't call this modular
> programming, I would call this asking for trouble.

we have several teams working on sevaral issues here. One team deals with aircraft. another team deals with aircraft cabin interiour (all cabin interiour items are linked to their aircraft by a foreign key). another team handles structural items of an aircraft like wings, fuselage, wheels, engines. They are also stored and linked to the aircraft record in the dataabse by a foreign key. the aircraft team is only responsible for the aircraft records and has a whole lot to do with that. They dont even know what cabin interiour tables and items there are. So when they implement their "deleteAircraft()" procedure they do not want to know and in fact cannot know all tables in the data model that have a foreign key link to the aircraft table! and there is my problem: the deleteAircaft(177) method will be stuck (hour glass) if someone modifies for example one of the aircraft no. 177's engines. Its okay not to delete the aircraft then because someone is working on it but it is not okay to be stuck. I would expect to have the means (as a programmer) to write my deleteAircraft() method such that it throws for example an exception ("AircraftCurrentlyBuysTryAgainLater") to the user that indicates what the problem is.

> If your 'modules' all use one schema, all modules should be aware of
> all foreign key constraints there are. Dealing with a foreign key
> constraint in several modules, not changing others is unacceptable.

Have you ever worked with an OO-relational bridge like object spaces or so ? There, a data model is deduced from OO class model. Its a typical scenario the existance of which you deny.

> Also, so far, you didn't come up with any *proof* locks are really the
> culprit. You might just be suffering from inadequate access paths.
> If you experience waits, you should analyze what your session is
> waiting for, instead of going to crusade against the way Oracle

I am not on a crusade. I was asking for clues on this issue. can you stop being unobjective and discuss more target driven ? If this thread pisses you off, why do you participate ?

> handles locking.
>
>
> Sybrand Bakker, Senior Oracle DBA
>
> To reply remove -verwijderdit from my e-mail address
Received on Wed Aug 13 2003 - 10:12:10 CDT