11

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?

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
ceebreenk
  • 1,031
  • 2
  • 14
  • 29

1 Answers1

17

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 +/- 231-1. In Firebird 3 the transaction id has been changed to an unsigned 48 bit integer (so the limit there is 248), 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).

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
  • I'd want to add, that how exactly one proceeds with backup-restore cycle is application specific and better be checked with application documentation and vendors beforehand. There are at least two considerations: 1) security: who would become `database owner` after the Restoring. It might affect application normal procedures later, especially those changing meta-data (upgrading program and database to newer versions). 2) reliability: what file do you recreate database into? recreating "in place" upon the old database file is somewhat risky and can end up with data being lost. Newer FB would – Arioch 'The May 21 '19 at 14:20
  • mostly prohibit this dangerous option. Anyway, it would be good to recreate the database into some NEW DIFFERENT file. But then you would have to know the way to tell your application how the new file is named and where it was put. – Arioch 'The May 21 '19 at 14:21