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

By: Borland Staff

Abstract: Discussion involving the Multigenerational Database Engine

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.


Server Response from: ETNASC04