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

On Jul 16, 3:24 am, André Hartmann <andre.hartm..._at_hotmail.de> wrote:

> The T1 transaction ... 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.

Another idea is doing all the necessary activity of "T1" outside a transaction in some temporary area, preparing all the data necessary to effect the updates related to "T1." The idea would be to do your 2 hrs of work first, save the results, and then, hopefully within some much shorter time, attempt to commit to the database, possibly after first doing some checks that no subsequent, conflicting activity has occured in the interim. (This check and the update could themselves be wrapped in a --hopefully shorter -- transaction).

The long running nature of T1, esp. as it seems to vary a lot and also to be subject to the whim of human interaction, looks to be source of a lot of the trouble.

Finally, I'd echo Martin T's comments about it being strange that these conflicts occur frequently, which is indicative of quirks in your organizational ("business") process and not anything database related.

More info = more help BTW, and do let us know how you sovled it.

	Cheers,
	John Hinsdale