Subject Re: [firebird-support] Awaiting Garbage Collector
Author Helen Borrie
At 08:09 a.m. 19/03/2015, Thomas Steinmaurer ts@... [firebird-support] wrote:

>Ask your software vendor to fix their client transaction management.
>Using auto commit as default transaction management mechanism is
>Firebird's enemy number 1. Not only due to OAT not moving forward, you
>may also reach the 32-bit transaction id limit, depending on your load,
>in weeks/months until a backup/restore cycle is mandatory.

It is not AutoCommit, per se, that prevents the OAT from moving forward, but another transaction setting, COMMIT WITH RETAIN. Assuming your software is written with Delphi or a derivative, the setting is called CommitRetaining. The hangup can be cleared if the software also periodically performs a hard COMMIT on the offending set. This isn't something you can fix yourself, if you don't have access to the source of the client code. Thomas' comments stand true, regarding AutoCommit eating up the transaction count.

There may be another flaw in the transaction management: if a set is being used for display and select but not for writing - such as in a drill-down scenario or a lookup - that runs throughout the user session in a read/write transaction with ReadCommitted visibility, then this will hold the OAT back as well. When such sets run in a read-only transaction, they do not cause the OAT to get stuck. Again, this is for the software vendor to fix.

Helen Borrie, Support Consultant, IBPhoenix (Pacific)
Author of "The Firebird Book" and "The Firebird Book Second Edition"