Firebird Transaction Count Exceeded

Question

We have an implementation running a Firebird database where we get this error:

"Implementation Limit Exceeded - Transactions count exceeded. Perform a backup and restore to make the database operable again."

We know how to fix this by making the database read only, performing backup & restore and making it read write again, however we're not too sure what is causing this. I have a feeling that the transactions is limited to a billion(?).

Can anyone confirm that? And what is the correct way to prevent this?

Answer 1

Firebird has a monotonically increasing transaction counter in the form of a signed 32 bit integer (for 2.5 and earlier). So the number of transactions is limited to +/- 2³¹-1. In Firebird 3 the transaction id has been changed to an unsigned 48 bit integer (so the limit there is 2⁴⁸), with room to expand to 64 bit integers in the future AFAIK.

The transaction counter is reset when performing a backup and restore using gbak. This can be done at any time, but when the limit is actually reached it requires marking the database read-only, because in a read-only database the 'last' transaction id of the database is used for new transactions instead of allocating a new transaction id.

Firebird is a MVCC (Multi Version Concurrency Control) database, which means it maintains multiple versions of a record. These record versions are marked with the id of the transaction that created that version. With a backup and restore only the latest versions are backed up and on restore those record versions are written with a low transaction id (probably 1).

Just resetting transaction counter is not possible (or at least: has a lot of complications) due to visibility of record versions for other transactions based on the isolation level, time that the transaction started etc. For example a transaction with repeatable read can only see record versions created by transactions that were committed at the time the transaction started. Record versions created by active transactions, or by transactions that were committed after transaction start are invisible.

There is no way to prevent this, except for doing regular backup and an actual restore before the transaction limit is reached (as that will reset the transaction id as well).