Re: [Firebird-devel] Max transaction duration

[<psc...@in...>]

On 12 Dec 2002 at 8:25, Jason Chapman (JAC2) wrote:

> Just my thoughts.

How about my thoughts....

> 
> The main reason for this feature is to cope with software that hasn't
> been written in a sympathetic way towards sensible transaction length,
> so implementing in the client seems weaker than having a _database_
> parameter.

The software should be fixed, a maximum transaction duration, cures the symptom, 
and not the overall disease, a badly designed app, which probably has other 
problems as well.
 
> Also, units of time do give users the most consistent interface (i.e.
> it would become known that 24 hours and your app will be rolling back
> all over the place), but from a true protection perspective it would
> be better to do this for the difference between transaction numbers
> e.g. next TX - theTX >= 500,000.   Could you imagine someone realising
> that the date on the server is a day out, adjusting it & all users get
> their TX's rolled back :-).

There are a couple of issues, any overall general transaction life routine, will either 
need too long a period that a transaction can live, or it's going to kill something 
important.  For example if the year end sales report runs for 17 hours, then you 
need to set the timeout to at least 18 hours.   This means that ANY transaction can 
live for 18 hours, if you have 25,000 normal transactions an hour, your still sunk.  

Next, whether caused by a sales report, or a duffus playing around, the performance 
effect is the same, that long running transaction, is going to gum up the works.  
Ultimately the application needs to be changed to use more shorter transactions.

> Also I think that it should kill the connection and not just the
> transaction as then the offending application will cease, as opposed
> to one element of it ceasing to work (the client will probably not
> know the tx has been rolled back & I review plenty of code where
> unusual exceptions are just swallowed). Also it should put an entry in
> the log file.
>
> Finally, this should really be considered a bug in the client
> application and to add to the debugging the ultimate would be stats on
> Open TX's with connections and IP addresses etc.

I think a better solution, is when you create a transaction, you can identify the 
application, the engine then has a duration, but it doesn't kill the transaction or the 
connection, but it does write an entry in it's log that transaction xxxx owned by 
application yyy exceeded the transaction limit of zzzzzz ms.   This way when 
transactions are causing problems by running too long, the DBA can notify the 
proper person and the application can be fixed.