Re: How to avoid waiting for locks during delete

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:

• I have a mukti-user environment
• tables are linked through foreign key constraints with the ON DELETE CASCADE option
• dedicated delete operatoon frequently occur on alltables involved

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

• modular programming, especially information hiding (when working on A i dont want to know who is attached to A)
• database consistency (my maintaining the foreign key constraints with their ON DELETE CASCADE
• notification on a lock being detected as in NOWAIT rather than the connection just hanging and waiting until the lock is eventually released.

  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