Inserted, uncommitted child records make deletion of parent hang

From: André Hartmann <andrehartmann_at_hotmail.com>
Date: Sun, 15 Jul 2007 10:16:00 +0200
Message-ID: <469a75a7$0$3836$9b4e6d93@newsspool4.arcor-online.net>

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!

I have tried to solve the problem by making T1 impose a lock on the parent record, and T2 also impose such a lock (SELECT ... WITH NOWAIT) such that T2 will realise the lock. But of course I am locking the entire parent record... T2 is now also blocked from changing some values of the parent record, and that is unnecessary overkill, isnt it?

I know there is no such thing as a "DELETE FROM child WHERE ... WITH NOWAIT" bit is there a way how I could get near to this kind of behaviour and make my application more user-friendly?

Thanks in advance, Your's
André
Berlin
Germany
:)

//-----------------------------------------------------------------------------------------------

// André Hartmann
// Technologiebereich WTDM (Web Technologien und Datenmanagement)
// PACE Aerospace Engineering and Information Technology GmbH
// Rotherstr. 20
// D-10245 Berlin
// phone: +49-(0)30-293 62 0
// fax: +49-(0)30-293 62 111
// mailto: Andre.Hartmann_at_pace.de
// http://www.pace.de

//-----------------------------------------------------------------------------------------------

Es gibt nur 10 Arten von Menschen:
Die einen verstehen binäre Zahlen, die anderen nicht.

//-----------------------------------------------------------------------------------------------

Received on Sun Jul 15 2007 - 03:16:00 CDT