IBM Books

Administration Guide

Recovering from Problems During Two-Phase Commit

Recovering from error situations is a normal task associated with application programming, system administration, database administration and system operation. Distributing databases over several remote servers increases the potential for error situations resulting from network or communication failures. To ensure data integrity, the database manager provides the two-phase commit process which is illustrated in "Understanding the Two-Phase Commit Process" as points (10), (11) and (12). The following explain how the database manager handles errors during this two-phase commit process:

Manual Recovery of Indoubt Transactions

If, for some reason, you cannot wait for the transaction manager to automatically resolve the indoubt transaction, there are some actions you can take to manually resolve the states of indoubt transactions. This manual process is sometimes referred to as "making a heuristic decision".

The LIST INDOUBT TRANSACTIONS command (and a related set of APIs) allows you to query, commit, and roll back indoubt transactions. In addition, it also allows you to "forget" transactions that have been heuristically committed or rolled back by removing the log records and releasing the log space. For information about the command and APIs, see the Command Reference and the API Reference manuals.

You should use this command (or APIs) with extreme caution and as a last resort. The best solution is to wait for the transaction manager to drive the resynchronization process. You could experience data integrity problems if you manually commit or roll back a transaction in one of the participating databases, and the opposite action is taken for another of the databases. Recovering from data integrity problems requires you to understand the application logic, the data changed or rolled back, and then to perform a point-in-time recovery of the database, or manually undo/redo the database changes.

If you cannot wait for the transaction manager to initiate the resynchronization process and you must release the resources tied up by an indoubt transaction, then heuristic operations are necessary. This situation could occur if the transaction manager will not be available for an extended period of time to do the resync, and the indoubt transaction is tying up resources that are urgently needed. An indoubt transaction ties up the resources that were associated with this transaction before the transaction manager or participating database became unavailable. These resources include things such as the locks on tables and indexes, log space, and storage taken up by the transaction. Each indoubt transaction also decreases (by one) the maximum number of concurrent transactions that can be handled by the database.

There are no foolproof ways to perform heuristic operations. You can use the following steps as a guideline:

  1. Connect to the database for which you require all transactions to be complete.
  2. Use the LIST INDOUBT TRANSACTIONS command to display the indoubt transactions. The xid represents the global transaction ID and is identical in other databases participating in this transaction, including the transaction manager database.
  3. For each indoubt transaction, use your knowledge about the application and the tm_database configuration parameter to determine the transaction manager database as well as the other participating databases.
  4. Connect to the transaction manager database.
Note:The LIST INDOUBT TRANSACTIONS command includes "type" information to show you the role of the database in each indoubt transaction:
Indicates the indoubt transaction is using the database as a transaction manager database.
Indicates the indoubt transaction is using the database as a resource manager, meaning that it is one of the databases participating in the transaction, but is not the transaction manager database.

Resynchronizing Indoubt Transactions if AUTORESTART=OFF

For configuration considerations in the DB2 Universal Database two-phase commit environment, refer to "Other Configuration Considerations in Any Environment".

In particular, if the autorestart database configuration parameter is OFF and there are indoubt transactions in either the TM or RM databases, the RESTART DATABASE command is required in order to start up the resynchronization process. If issuing the RESTART DATABASE command from the command line processor, use different sessions. If you restart a different database from the same session, the connection established by the previous restart database command will be dropped. The database will need to be restarted again if the last connection to it is dropped. Issue DB2 TERMINATE to drop the connection after there are no indoubt transactions listed by the DB2 LIST INDOUBT TRANSACTIONS command.

[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]

[ DB2 List of Books | Search the DB2 Books ]