> I have a number of beginner questions, if you don't mind:
>
>> The oldest transaction is a transaction in a state other than committed.
>> Can be rolled back and the engine wasn't able to undo stuff and do a
>> commit or it's in limbo in case of a distributed transaction across two
>> ore more databases.
>
> I only use one database, so there shouldn't be any distributed
> transaction issues.

Ok.

> I am not sure what you mean by "can be rolled back and the engine wasn't
> able to undo stuff" - how could that possibly happen? I was under the
> impression that if I roll back a transaction, all of its changes return
> to the state at the beginning of that transaction. Do you really mean
> that a rollback might fail??

No. A rollback reverts back. Period.

What I meant is, that basically a commit is better for transaction
statistics, so what the engine tries with a rollback is to undo stuff
and then calls commit. The engine isn't able to do that e.g. when
working on a larger number of records, e.g. by deleteing records and
then calling rollback.

>> Then there is also a gap of ~ 2200 between the oldest active and the
>> next transaction, thus you have a transaction, which is still active
>> resp. not hard committed or rolled back.
>
> That is quite possible. While I try to properly close all transactions,
> there are simply too many places where they occur (and too many
> connections to the same database file, using different access objects)
> to be sure that I caught them all.

By "close all transaction" I get the impression that you are starting a
client transaction explicitely?

Also, watch out AutoCommit transactions, which basically do a commit
retaining behind the scene when commiting. Although a commit retain
makes changes to concurrent connections/transactions visible, the
transaction is still active in sense of the oldest active transaction,
thus this value can't move forward.

>> A sweep, if the oldest transaction didn't get stuck due to a failed two
>> phase commit, should make the oldest transaction move forward.
>>
>> As you have set the sweep interval to 0, automatic sweep is disabled,
>> thus you should run a scheduled sweep at low load, e.g. in the night.
>
> I will need to study this sweeping. It's a completely new issue for me,
> as I never did it before.

Basically a sweep visits all records in all tables, removes oldish back
record versions and tries to move transaction statistic counters forward.

>> Hope this helps.
>
> I think it does. First of all I am going to read up on sweeping, to
> learn what it is about. Then I'll try it on that slow database and see
> what happens.

--
With regards,

Thomas Steinmaurer
Upscene Productions
http://www.upscene.com
http://blog.upscene.com/thomas/

Download LogManager Series, FB TraceManager today!
Continuous Database Monitoring Solutions supporting
Firebird, InterBase, Advantage Database, MS SQL Server
and NexusDB!