Re: Inserted, uncommitted child records make deletion of parent hang

On Jul 15, 4:16 am, "André Hartmann" <andrehartm..._at_hotmail.com> wrote:

> T1: Inserts several child records for the one-and-only parent. No commit
> yet!
> T2: Tries to delete the parent record. Hangs because the insert from T1 is
> pending (not committed yet). When T2 selects from the child table it sees no
> data, because the data inserted by T1 are not committed yet.
> T1: commit! The lock is released, T2 continues with the delete.
>
> What is not good about this scenario is that T2 hangs until T1 commits or
> rolls back. ... the
> second session (handled by an end user application) just hangs, it means my
> application hangs and the users complain about that!

André,
Oracle is doing its best to preserve the integrity of your database. I wouldn't fight it - it'll create problems that it will solve. I'd do whatever you can to get "T1" to finish faster, or failing that, to separate out the deletion processing from the app.

You didn't mention what kind of process T1 is, specifically how long it is
taking to complete its transaction; but if users are complaining it must
be something that takes a while (batch job? human-driven app?) If a batch
job you could have it commit more often instead of being one big transaction.

If there is "contention" for deletions of your "parent" table, other option
is to do the delete in a separate, asynchronous thread of activty. There
are issues with that, however, if the end user needs immediate confirmation that his
delete actually went through. A more cumbersome but reliable way is to isolate
the actual database deletion entirely, setting a "deleted" flat, then running
an entirely separate purge job later. A big pain but definitely works well to
remove contention.

Just some thoughts ...

Cheers,
John Hinsdale Received on Sun Jul 15 2007 - 14:54:47 CDT