Subject Re: [IBDI] Transactions
Author Ann W. Harrison
At 02:23 PM 7/9/2001 -0700, Peter Morris wrote:

>Someone once said that you must backup/restore IB on a regular basis (I
>believe it was a lady on the FireBird Development team, Anne maybe ?).

That was no lady. That was me, Ann.

>Apparently you are limited to a total number of transactions,

Right. The transaction id is held in a long word so you get 2,147,483,647
transactions before the system starts trying to index backward into the
transaction array with unhappy results. As it happens you'll never get
to that limit because the number of transaction pages is also limited.

>this number is reset when you restore,

Yes. Restore actually creates a new database, recreates the metadata
from the old database, and populates the new database. So all internal
indicators (transaction id, table format version, etc.) are reset.

>this number is proportionate to the page size of
>your database.

That's true, although it will not be the limiting factor
in Firebird 1.0. There the limit will be the range of the
transaction id. We could double that by handling the TID
as an unsigned quantity. Probably should. When we start
making ODS changes, perhaps we should double the size of
the TID... or maybe not.

However, and for now (from my previous message):

There is a bug in InterBase 5.6, 6.01, and the 9.4
Firebird beta that causes the lookup of a transaction
inventory page to fail if there are more than 32767
transaction pages. That makes the maximum safe
transaction id for a database with:

1024 byte pages 131,596,287
2048 byte pages 265,814,015
4096 byte pages 534,249,471
8192 byte pages 1,071,120,383
16384 byte pages 2,147,483,587

Although those are large numbers, one particular
database exceeded 131 million transactions in six

1) don't use a 1024 byte page size.
2) do check your next transaction number from
time to time.
3) if you see the next transaction number
approaching the limit, backup and restore
the database.

>Would you mind informing me of the exact details please ?

Transaction states are stored in an array that is spread across
database pages. Each page adds 20 bytes of overhead. Each transaction
occupies two bits. As older versions of Firebird and all versions of
InterBase can address only 32767 pages of transaction ids, there is a
limit on the number of transactions that can be started and the number
depends on the page size, as above. Larger page -> more transactions
per page -> more transactions in 32768 pages.



We have answers.