TIP (Transaction Inventory Page)
The Transaction Inventory Page (TIP) is one of the ten page types defined in Firebird/InterBase®. Each and every user transaction is consecutively numbered, using the Firebird/InterBase® Transactions Inventory Page (TIP) (also known as the Transaction Information Page). These transaction numbers are used by the Firebird/InterBase® versioning engine to ensure that users always receive a consistent view of the database. It shows the status of each and every transaction in the database, and adheres to two main rules:
- Only those transactions are visible, whose
ID <= own ID.
- Only those transactions are visible, which were already committed at the time the own transaction was started.
Transactions are shown with one of the following four status values:
Table: Values in the Transaction Information Pages
|A||Transaction is active, or in process|
|C||Transaction was committed. The changes made by this transaction can be applied if necessary to show a consistent view of the database.|
|R||Transaction was rolled back. The changes made by this transaction should be ignored.|
|L||Limbo transaction. This transaction was part of an operation involving more than one database within an embedded SQL application.|
1C = first transaction committed,
2A = second transaction is active,
3C = third transaction is rolled back,
4L = Transaction is in limbo (i.e. when a transaction is dependent upon another transaction in another database = two-phase commit). This information is important for the garbage collection.
Special transactions IDs
Firebird/InterBase® tracks three special positions within the transaction history:
- The Oldest Interesting Transaction (OIT) is the earliest transaction in the database with a status other than committed. Every transaction prior to that one represents an unbroken chain of insertions and updates into the database.
- The Oldest Active Transaction (OAT) is the earliest transaction in the database that is currently active or open.
- The Next Transaction Number is the ID that is used for the next transaction that starts.
You can find these numbers in the IBExpert Database Statistics display within the Server Manager, or using the gstat
-h command in isql.
When you start a transaction, Firebird/InterBase® makes a copy of the TIP into the server memory cache assigned to your process, starting from the page holding the OIT and finishing with the page holding the OAT.
Whenever the database is backed up and restored, the transaction inventory is wiped out and the next transaction number is set to 1.
There is also a mechanism in the Firebird/InterBase® server TIP page, to allow a local TIP page for each user. The local TIP page is generated the minute a new user presses the Execute [F9] key. Please refer to TID (Transaction ID) for further information.
The advantage of such a system is that older records are held ready. The disadvantage for users, who execute, but need a considerable time before finally committing is that the local TIP becomes very large, as it always begins at the oldest active transaction, so that it is possible using this technique, for one transaction to hold everything up and slow the transaction processing for everyone. If a system becomes increasingly slow with time, it is almost always due to the fact that TIP pages are being filled further and further with transaction information, because the first transaction has not been committed. 99% of local TIPs are held in the RAM, until there are no further pages free.
- Note: If you are only doing a
SELECTin your transaction, you should always
COMMITto avoid creating an "interesting" transaction (transaction with a status code other than committed in the TIP).
All TIPs are of the page size defined when creating the database. 16,000 transactions fit, for example, onto a 4K page.
TIPs and Server Crashes
If a server crashes or hangs during user transactions, the Firebird/InterBase® server simply looks at the TIP, and rolls back all operations that were still active. This means that an Firebird/InterBase® server can be rapidly restarted. As soon as the operating system is up and running, Firebird/InterBase® is also up and running. Forced writes however influence the sequence in which is written:
- IBExpert Database Properties / Forced Writes - when committing Firebird/InterBase® saves all data sets to the hard drive and then to the TIP.
- Without forced writes the process is minimally quicker, but on a Windows platform, Windows decides what should be saved to file, where and when; and the data pages are saved to file last i.e. the TIP changes are written first and then the data sets, which could possibly lead to inconsistencies.
Therefore forced writes are extremely important when working on a Windows platform. Without forced writes, the computer needs to be extremely secure.