<< Set database page buffers | Firebird Database Housekeeping Utility | Cache manager >>

Limbo transaction management

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.

back to top of page

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)?

back to top of page

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.

back to top of page

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 transactions.

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.

See also:
Transactions in limbo
Two-phase commit

back to top of page
<< Set database page buffers | Firebird Database Housekeeping Utility | Cache manager >>