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: Tue, 12 Aug 2003 10:55:54 +0200
Message-ID: <3f38ab9e$1@olaf.komtel.net>


Okay,

  let me try to illustrate what i mean.

  Lets say in my current application which works on a particular data model you have tables A and B and C where C and C have a foreign key reference to table A like i described previously. In different modules of the application, delete operations are performed on tables B and C and in another module delete operations on table A are performed (which also lead to deletes on B and C because of the CASCADE connection).

  If I understand you right, it means that when I delete from A i have to actually know that there is a table B and a table C which have a CASCADING reference... and I have to lock the affected rows in B and C "manually" prior to my delete on A. Is that correct ? If yes, I find it bad: in the module of the application that deletes from A i am not aware what other tables have a CASCADING reference to A and I dotn want to be aware of it: I just want to take advantage of the CASCADING meachanism to delete all dependent records in other tables automatically.

  Furthermore, lets say in half a year due to a new feature requirement from my customer I have a new table D introduced into the data model which also has a CASCADE reference to A. Now, in order to have my delete on A to bestill okay, it has to additionally lock rows in D which is bad because the business logic in the module that deletes from A has not really changed: I want to delete a record from A and everything attached to it! Through the CASCADE option Oracle provides a means to me to get this job done without knowing what other tables actually attach records to the records in A by a foreign cascading key constraint.

  However due to the locking problem that has occurred I cannot be sure of my delete on A still performing fine: my application is multi-user and someone might be performing a delete on B, C, D which could block my delete on A... but I dont get notified.. I am just stuck. That seems to be incosistent because when I do a SELECT ... FOR UPDATE there is a way around getting stuck: the NOWAIT option.

  I dont think my example is so far fetched. It is real. It occurs in two projects that I maintain for real world customers. It arises from the following facts that I am affected by:

  Since I find this a very common scenario, I am/was of good hope that there would be a solution to this which obeys:

  So, is it a little clearer now ? You email sounded a bit rude. I find the data model well designed and it is not a mess. Our applications handle dozens of GB of data in this datamodel with an acceptable degree of both consistency and performance.

André Hartmann
:)

"Sybrand Bakker" <gooiditweg_at_sybrandb.demon.nl> schrieb im Newsbeitrag news:tgp7jvs7c5ipmld1daffoto2aem9fmagng_at_4ax.com...
> On Fri, 8 Aug 2003 17:49:23 +0200, "André Hartmann"
> <andrehartmann_at_hotmail.com> wrote:
>
> >
> > * If you suggest to lock everything explicitely, that means that when
> >issue my delete command i actually have to know all the cascading foreign
> >key relations that need to be traced. but i dont (want) to know that. my
> >data model will develop and get more enhanced in the future... each time
i
> >introduce a new foreign key constraint to a base table that is to delete
> >cascadingly, i would have to adjust the code around my delete-command to
> >make additional locks... thats also unacceptable because its a source of
> >errors and iconsistencies.
>
> I don't know what you are talking about. In a properly designed
> datamodel that doesn't happen. PERIOD.
> If you think otherwise, you have to come up with evidence, and likely
> you will show a MESS
>
>
> Sybrand Bakker, Senior Oracle DBA
>
> To reply remove -verwijderdit from my e-mail address
Received on Tue Aug 12 2003 - 03:55:54 CDT

Original text of this message

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