Does using “commit commit” use Firebird performance degradation?

Question

The above excerpts below seem contradictory at this point.

(Both of them are quite old, I think the second is from 2004, and the first mentions of Borland should also be old, so maybe they are out of date.)

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

Secondly, if I understand, this means that with commit commit, the existing TID is marked as perfect, and the transaction is kept alive, but with the new TID and therefore does not put the OIT. This second passage refers to Interbase, I do not know if this explains the apparent contradiction.

Firebird Documentation Extract:

With Firebird (and InterBase), Commit Retaining invokes transactions to remain interesting endlessly. Garbage collection effectively stops on the “standard” Borland RAD application database application and any other applications that use commit commit.

Embarcadero Blog Post

Read, read-write:

This transaction can be performed forever without negative impact on if you commit from time to time.

Answer 1

When you use commit commit (either using the API or using COMMIT RETAIN ) with Firebird, the initial transaction does not actually end, it is simply associated with a set of visible transactions from a new transaction that was launched within the organization, while maintaining old active.

This means that the oldest interesting and oldest Active transactions are not moved, and backups are accumulated that cannot be garbage collected until the transaction is actually completed. This means that ultimately, queries will need to crawl a longer chain of record versions that may have an impact on performance.

I suppose that some optimizations are possible, for example, the initial transaction can be marked as committed if there are no open cursors that were launched in the transaction (one of the features of commit commit is that cursors do not close to transaction commit, which, if I I'm not mistaken, it requires that the old transaction context remain available). This may be something that InterBase did for read transactions.

You can see this for yourself by starting an isql session and making a few inserts in conjunction with a commit commit: if you check gstat -h in combination, you will notice that the oldest interesting and oldest active transactions are not moving until you really commit.