Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.misc -> Inserted, uncommitted child records make deletion of parent hang
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
:)
//-----------------------------------------------------------------------------------------------
//-----------------------------------------------------------------------------------------------Es gibt nur 10 Arten von Menschen:
//-----------------------------------------------------------------------------------------------Received on Sun Jul 15 2007 - 03:16:00 CDT
![]() |
![]() |