Resolving indoubt transactions manually

An XA-compliant transaction manager (Transaction Processing Monitor) uses a two-phase commit process similar to that used by the Db2® transaction manager. The principal difference between the two environments is that the TP monitor provides the function of logging and controlling the transaction, instead of the Db2 transaction manager and the transaction manager database.

Errors similar to those that occur for the Db2 transaction manager can occur when using an XA-compliant transaction manager. Similar to the Db2 transaction manager, an XA-compliant transaction manager attempts to resynchronize indoubt transactions.

If you cannot wait for the transaction manager to automatically resolve indoubt transactions, you can manually resolve them. This manual process is sometimes referred to as making a heuristic decision.

About this task

The LIST INDOUBT TRANSACTIONS command (using the WITH PROMPTING option), or the related set of APIs (db2XaListIndTrans, sqlxphcm, sqlxhfrg, sqlxphrl), allows you to query, commit, and roll back indoubt transactions. In addition, it also allows you to forget transactions that were heuristically committed or rolled back, by removing the log records and releasing the log space.

Manually resolve indoubt transactions by using these commands (or related APIs) with extreme caution, and only as a last resort. The best strategy 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 against another participating database. Recovering from data integrity problems requires you to understand the application logic, to identify the data that was changed or rolled back, and then to perform a point-in-time recovery of the database, or manually undo or reapply the 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, heuristic operations are necessary. This situation could occur if the transaction manager will not be available for an extended period of time to perform the resynchronization, 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 resource managers became unavailable. For the database manager, these resources include 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. Moreover, an offline backup cannot be taken unless all indoubt transactions have been resolved.

The heuristic forget function is required in the following situations:
  • When a heuristically committed or rolled back transaction causes a log full condition, indicated in output from the LIST INDOUBT TRANSACTIONS command
  • When an offline backup is to be taken
The heuristic forget function releases the log space occupied by an indoubt transaction. The implication is that if a transaction manager eventually performs a resynchronization operation for this indoubt transaction, it could potentially make the wrong decision to commit or roll back other resource managers, because there is no log record for the transaction in this resource manager. In general a missing log record implies that the resource manager has rolled back the transaction.

Procedure

To resolve indoubt transactions manually:

  1. Connect to the database for which you require all transactions to be complete.
  2. List the indoubt transactions:
    1. For Db2 database servers, use the LIST INDOUBT TRANSACTIONS WITH PROMPTING command.
      The xid represents the global transaction ID, and is identical to the xid used by the transaction manager and by other resource managers participating in the transaction. The LIST INDOUBT TRANSACTIONS command returns the current state of the indoubt transactions at the start of the interactive session, but the output is not refreshed on the client if the status changes on the server. If the status change prevents you from being able to perform all of the actions that are listed as valid options, an error message is returned instructing you to quit the interactive session and start a new one.
    2. For host or System i® database servers, you can use one of the following:
      You can obtain indoubt information directly from the host or System i server. To obtain indoubt information directly from Db2 for z/OS®, invoke the DISPLAY THREAD TYPE(INDOUBT) command. Use the RECOVER command to make a heuristic decision. To obtain indoubt information directly from Db2 for IBM® i, invoke the wrkcmtdfn command.
      You can obtain indoubt information from the Db2 Connect server used to access the host or System i database server. You can obtain indoubt information from the Db2 Connect server used to access the host or System i database server.
  3. For each indoubt transaction, use the information shown about the application and the operating environment to determine the other participating resource managers.
  4. Determine the actions to take with each indoubt transaction:
    • If the transaction manager is available, and the indoubt transaction in a resource manager was caused by the resource manager not being available in the second commit phase, or for an earlier resynchronization process, do the following:
      1. Check the transaction manager's log to determine what action has been taken against the other resource managers.
      2. Take the same action against the database; that is, use the LIST INDOUBT TRANSACTIONS WITH PROMPTING command, to either heuristically commit or heuristically roll back the transaction.
    • If the transaction manager is not available, use the status of the transaction in the other participating resource managers to determine what action to take:
      • If at least one of the other resource managers has committed the transaction, heuristically commit the transaction in all the resource managers.
      • If at least one of the other resource managers has rolled back the transaction, heuristically roll back the transaction.
      • If the transaction is in the prepared (indoubt) state in all of the participating resource managers, heuristically roll back the transaction.
      • If one or more of the other resource managers is not available, heuristically roll back the transaction.

What to do next

To obtain indoubt transaction information from Db2, connect to the database and issue the LIST INDOUBT TRANSACTIONS WITH PROMPTING command, or call the db2XaListIndTrans API from a client application.