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

On Jul 15, 10:16 am, "André Hartmann" <andrehartm..._at_hotmail.com> wrote:
> Hi,
>
> I have a problem with a parent/child table (or record) scenario, which I
> would like to describe briefly.
>
> The parent table has a unique key, which the child table uses as an
> (indexed) foreign key with an ON DELETE CASCADE option. Initially, there is
> one parent record in the parent table and no child records so far. I have
> two sessions (transactions) acting on the tables as follows:
>
> 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. I understand, that the T2 delete is blocked by locks on the
> records inserted into the child table by T1, of course. What I would like to
> have is some kind of NOWAIT behaviour in the T2 session, so that this user
> can be notified about the situation. What happens right now is that the
> second session (handled by an end user application) just hangs, it means my
> application hangs and the users complain about that!
>
> (...)

What I find rather odd about your situation as you describe it, is that it seems that users are - on a regular basis (?) - inserting data that is obsoleted by someone else at the same time.

br,
Martin Received on Mon Jul 16 2007 - 08:47:51 CDT