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

"John K. Hinsdale" <hin_at_alma.com> schrieb im Newsbeitrag news:1184529287.329282.15550_at_g4g2000hsf.googlegroups.com... On Jul 15, 4:16 am, "André Hartmann" <andrehartm..._at_hotmail.com> wrote:

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

  The application is user-driven, not a batch-job or similar. The T1 transaction typically starts with a lengthy process which is triggered by user interaction but does not take any user interaction which under way. It may take anything between 5 minutes and 2 hours. At the end of this process the user has to provide some input regarding the data that have been established in the beginning. Only after that has been done, the transaction can be committed.   If I have T1 not impose any locks in the database, the danger is that by concurrency towards the end of T1 the results are already unjustified because of changes in the database.

> 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

  Initially I would have said yes, the user expects immediate success or failure reported in his/her T2 session. But maybe there is an option here... to discuss that with the users and see what kind of behaviour they would still be happy with.

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

  "Deleted" flag? True, that would probably work. Obviously thats a bit of a refactoring of the data model and access routines implied here, but we might have to go through something like that anyway.

  Thank's very much for your supporting ideas! André
Berlin, Germany
:) Received on Mon Jul 16 2007 - 02:24:37 CDT