Firebird Documentation IndexGfix - Database Housekeeping → Limbo Transaction Management
Firebird Home Firebird Home Prev: Set Database Page BuffersFirebird Documentation IndexUp: Gfix - Database HousekeepingNext: Cache Manager

Limbo Transaction Management

Listing Limbo Transactions
Committing Or Rolling Back
Automatic Two-phase Recovery

Limbo transactions can occur when an application is updating two (or more) databases at the same time, in the same transaction. At COMMIT time, Firebird will prepare each database for the COMMIT and then COMMIT each database separately.

In the event of a network outage, for example, it is possible for part of the transaction to have been committed on one database but the data on the other database(s) may not have been committed. Because Firebird cannot tell if these transactions (technically sub-transactions) should be committed or rolled back, they are flagged as being in limbo.

Gfix offers a number of commands to allow the management of these limbo transactions.

Note

The following examples of limbo transactions are based on Firebird 1.5 and have kindly been provided by Paul Vinkenoog. Because of the limitation of my setup, I am unable to create limbo transactions in my current location.

In the spirit of consistency, however, I have renamed Paul's servers and database locations to match the remainder of this document.

Listing Limbo Transactions

The gfix command -l[ist] will display details of transactions that are in limbo. If there is no output, then there are no transactions in limbo and no further work need be done. The command is:

gfix -l[ist] database_name

An example of listing limbo transactions is shown below. This command is run against the local database on the server named linux where a multi-database transaction had been run connected to databases linux@my_employee and remote:testlimbo. Both of these database names are aliases.

linux> gfix -list my_employee
Transaction 67 is in limbo.
 Multidatabase transaction:
 Host Site: linux
 Transaction 67
has been prepared.
 Remote Site: remote
 Database path: /opt/firebird/examples/testlimbo.fdb

If the command is run against the remote database then nothing will be listed because that database does not have any limbo transactions - the transaction that went into limbo, when the network failed, for example, was initiated on the local database.

You may also supply the -p[rompt] option to the command and you will be prompted to COMMIT or ROLLBACK each detected limbo transaction. In this case, the command would be:

gfix -l[ist] -p[rompt] database_name

An example of this is shown below.

linux> gfix -list -prompt my_employee
Transaction 67 is in limbo.
 Multidatabase transaction:
 Host Site: linux
 Transaction 67
has been prepared.
 Remote Site: remote
 Database path: /opt/firebird/examples/testlimbo.fdb
Commit, rollback or neither (c, r, or n)?

Committing Or Rolling Back

When a limbo transaction has been detected, the DBA has the option of committing or rolling back one or more of the transactions reported as being in limbo.

When more than one transaction is listed, the DBA can either commit or roll back all transactions in limbo, or a specific transaction number.

The following commands show the -c[ommit] option being used, but the -r[ollback] option applies as well, it all depends on what the DBA is trying to achieve.

To commit every limbo transaction on the database, the following command would be used:

gfix -commit all database_name

If the DBA wanted to commit a single transaction, then the command would change to the following:

gfix -commit TXN database_name

Where TXN is the transaction number to be committed.

When either of these options are user, there is no feedback from gfix to advise you that the commit actually worked. You would need to rerun the gfix -list command to make sure that all, or the selected, limbo transactions had indeed gone.

You cannot commit or rollback a transaction that is not in limbo. If you try , the following will occur:

linux> gfix -commit 388 my_employee
failed to reconnect to a transaction in database my_employee
transaction is not in limbo
-transaction 388 is active
unknown ISC error 0

When committing or rolling back all limbo transactions, the -p[rompt] option can be specified. It is, however, not permitted when processing a single transaction. An example of using the -p[rompt] option has been shown above under listing limbo transactions.

Automatic Two-phase Recovery

Gfix can be used to perform automatic two-phase recovery. The command for this is -t[wo_phase] and, like -c[ommit] and -r[ollback] above, requires either 'all' or a transaction number.

The output of the -l[ist] command shows what will happen to each listed transaction in the event that the DBA runs the -t[wo_phase] command.

The command also takes the -p[rompt] option, as above, when used to process all transaction.

The command line to carry out automatic two-phase recovery is:

gfix -t[wo_phase] TXN database_name or

gfix -t[wo_phase] all database_name

As above, TXN is a single transaction number from the list of limbo transactions.

Note

Paul has noted that when using the -c[ommit], -r[ollback] or -t[wo_phase] options, the output is exactly the same and appears to show that these three are all just synonyms for the -l[ist] -p[rompt] pair of options. This occurred whether or not Paul used the transaction number, 67, or 'all' in the command line.

Prev: Set Database Page BuffersFirebird Documentation IndexUp: Gfix - Database HousekeepingNext: Cache Manager
Firebird Documentation IndexGfix - Database Housekeeping → Limbo Transaction Management