Re: How to avoid waiting for locks during delete

Darn. A VI programmer has a terrible time posting from GOOGLE/IE. Pressing <escape> destroys all the edits 8^(

"André Hartmann" <andrehartmann_at_hotmail.com> wrote in message news:<3f38ab9e$1_at_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 ?

Yes and that is independent of ORACLE. You have the same OR WORSE conditions in other DBM systems.

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

It is one thing for the CODE to not be aware of the CASCADE DELETE, and it is quite a different matter for the PROGRAMMER/DESIGNER to not be aware of it. It almost sounds like you wish the DB to magically do the right thing. The trouble is, in order to do the right thing, the DB must somehow know what action each session will make: commit or rollback.
>
> 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.

Right but your real complaint was concerning a locking condition that I do not think is realistic in a well written application. (more on this below)
>
> 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.

The only reason you might get stuck is if you issue the DELETE B where... and then wait before issuing the COMMIT.

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

and from some factors you left out:
there is a significant time delay between issuing the DELETE and the COMMIT.
delete operations are allowed on child tables independently from parent tables.

> 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
> :)
>

Or so this is all just a theoretical discussion? If you don't have a performance issue, I do not see what your problem really is.

But may I suggest a different point of view that may help. In many commercial DB applications there are auditting requirements. As a result the applications do not usually issue DELETE commands. Instead the records are marked in some manner. Yes this means the application queries are slightly more complex (one extra phrase in the where clause is sometimes all that's needed). So the DELETE actually becomes an update operation so all your techniques using NO WAIT apply.

And trust me, it is not better in other DB's. I'm coding a similar operation in another DBMS right now which requires me to handle locks explicitly and no cascaded DELETE.

But like I said earlier, the code can be very modular with information hidden away from it, but there cannot be anything be hiddden from the application designer. It's your job to engineer a solution. There are choices and trade offs. The DBMS cannot solve all the design problems for you. As the song says:

"you can't always get what you want,
 but if you try sometimes,
 you just might find
 you get what you need"

 HTH (if not you then hopefully others)
   Ed Received on Tue Aug 12 2003 - 15:02:38 CDT