Moving the oldest interesting (OIT) and oldest active transactions (OAT) along

Abstract: Moving the oldest interesting (OIT) and oldest active transactions (OAT) along

Problem: MOVING THE OLDEST INTERESTING (OIT) AND OLDEST ACTIVE TRANSACTIONS (OAT) ALONG. Solution: First let us define a transaction, what it's possible states are, the life cycle of a transaction, what exactly is the OIT and OAT, and then how they are set and moved along. DEFINITION A transaction is an atomic unit of work made up of one or more operations against the data in one or more databases. It can contain one or many operations that might INSERT, UPDATE, DELETE, or SELECT data. Or it might be work that changes the physical structure of the database itself. The scope of the transaction is defined by the user/programmer when they START a transaction and then end it with a COMMIT or ROLLBACK. POSSIBLE STATES A transaction can have one of four states; active, committed, rolled back, or limbo. LIFE CYCLE The life cycle of a transaction is first active, set that way by the execution of a isc_start_transaction() or an isc_start_multiple call(). Then the transaction can be either committed by a isc_commit_transaction() or an isc_commit_retaining() call, or rolled back by an isc_rollback_transaction() call. If the commit is happening for a transaction across multiple databases then the two-phase commit protocol is invoked. This first phase sets the transaction to limbo in each of the databases then the second phase races around the network to just switch the transaction bit to committed. If it fails anywhere in the two phases then the transaction is considered in limbo and the transaction bit is left set at the limbo state. DEFINITION OF OIT AND OAT The Oldest Interesting Transaction (OIT) is the first transaction in a state other than committed in the database's Transaction Inventory Pages (TIP). The TIP is a set of pages that log each transaction's information (transaction number and current state) in the database since the last time the database was created or last backed up and restored. The Oldest Active Transaction (OAT) is the first transaction marked as active in the TIP pages. The way to find out the values of the OIT and OAT is to run gstat -h locally against the database in question. The OIT is listed as the "Oldest Transaction" by gstat. MOVEMENT OF OIT AND OAT We have to refine the life cycle a bit first. To create a transaction the start transaction call will first read the header page of the database, pull off the Next Transaction number, increment it, and write the header page back to the database. It also reads the OIT value from the header page and starts reading the TIP pages from that transaction number forward up to the OAT. If the OIT is now marked as committed, then the process continues checking the transactions until it comes to the first transaction in a state other than committed and records that in the process's own transaction header block. The process then starts from the OIT and reads forward until it finds the first active transaction and records that in it's transaction header block also. If and only if the process starts another transaction, will the information from the process's transaction header block update the information on the header page when it is read to get the next transaction number. Of course if another process has already updated the header page with newer numbers, i.e. larger, then the information will not be written. There are only two non-committed and non-active transaction states; limbo and rolled back. The only way to change a limbo transaction to committed is for the user to run gfix on the database to resolve the limbo transaction by rolling back or committing it. The only way to change a rolled back transaction to committed is to sweep the database. The sweep can be executed by: A) the user running a gfix -s process B) programmatically attaching to the database with a database parameter block set to cause a sweep C) have the automatic sweep kicked off The automatic sweep interval is set by default to be 20,000. It can be changed by using the gfix -h command to set the interval to . If is zero then the automatic sweep is completely turned off and the user will have to use options A or B from above to sweep the database. Note, the automatic sweep is kicked off when the difference between the OAT and the OIT is greater then the sweep interval. The user's process that tried to start the transaction that exceed the sweep interval by one will sweep the entire database before actually starting the transaction they requested. As you can see, if you ever rollback a transaction, have an active transaction abnormally terminate, or always use processes that use only one transaction and then exit, then you will have to sweep the database to update the OIT and OAT values. Of course, sweeping the database also provides the added benefit of removing any delete records from the database.