Does using 'Commit Retaining' impair Firebird performance?

Question

The quoted extracts below appear to be contradictory on this point.

(They are both pretty old I think, the second one is from 2004 and the first mentions Borland so must be old as well, so perhaps they are outdated.)

The first seems to suggest that commit retaining keeps the transaction active and thus will stick the OIT.

The second, if I understand it implies that with a commit retaining, the existing TID is marked committed and the transaction is kept alive but with a new TID and therefore doesn't stick the OIT. This second extract relates to Interbase, I don't know if that explains the seeming contradiction.

Firebird Documentation Extract:

With Firebird (and InterBase), Commit Retaining causes transactions to remain interesting indefinitely. Garbage collection effectively ceases on the “standard” Borland RAD tools database application and any other applications that make use of Commit Retaining.

Embarcadero Blog post extract

Read committed, read-write:

This transaction can run forever with no negative impact on performance if you do a commit retaining from time to time.

Answer 1

When you use commit retaining (either using the API or with COMMIT RETAIN) with Firebird, the started transaction is not really ended, it just gets associated with the set of visible transactions from a new transaction that has been started internally, while also keeping the old one(s) active.

This means that the Oldest Interesting and Oldest Active transactions don't move and that back versions accumulate that can't be garbage collected until the transaction is really committed. This means that eventually queries will need to scan a longer chain of record versions, which can have a performance impact.

I assume there are some optimizations possible, for example the original transaction might possibly be marked as committed if there are no cursors open that were started in the transaction (one of the features of commit retaining is that cursors aren't closed on transaction commit, which - if I'm not mistaken - requires the old transaction context to remain available). This might be something that InterBase has done for read committed transactions.

This can be seen by yourself by starting an isql session and do some inserts in combination with commit retaining: if you check gstat -h in combination, you will notice that the Oldest Interesting and Oldest Active transactions don't move until you really commit.