Manually Committing an In-Doubt Transaction: Example - Oracle 10g

We illustrates a failure during the commit of a distributed transaction. In this failure case, the prepare phase completes. During the commit phase, however, the commit confirmation of the commit point site never reaches the global coordinator, even though the commit point site committed the transaction. Inventory data is locked and cannot be accessed because the in- doubt transaction is critical to other transactions. Further, the locks must be held until the in-doubt transaction either commits or rolls back.

Example of an In-Doubt Distributed Transaction

You can manually force the local portion of the in-doubt transaction by following the steps detailed in the following sections:

Step 1: Record User Feedback
Step 2: Query DBA_2PC_PENDING
Step 3: Query DBA_2PC_NEIGHBORS on Local Node
Step 4: Querying Data Dictionary Views on All Nodes
Step 5: Commit the In-Doubt Transaction
Step 6: Check for Mixed Outcome Using DBA_2PC_PENDING

Step 1: Record User Feedback

The users of the local database system that conflict with the locks of the in-doubt transaction receive the following error message:

In this case, 1.21.17 is the local transaction ID of the in-doubt distributed transaction. You should request and record this ID number from users that report problems to identify which in-doubt transactions should be forced.

Step 2: Query DBA _2PC _PENDING

After connecting with SQL*Plus to warehouse, query the local DBA_2PC_PENDING data dictionary view to gain information about the in-doubt transaction:

CONNECT SYS/password@warehouse.acme.com SELECT * FROM DBA_2PC_PENDING WHERE LOCAL_TRAN_ID = '1.21.17'; The database returns the following information: Column Name Value ---------------------- ----------------------------- LOCAL_TRAN_ID 1.21.17 GLOBAL_TRAN_ID SALES.ACME.COM.55d1c563.1.93.29 STATE prepared MIXED no ADVICE TRAN_COMMENT Sales/New Order/Trans_type 10B FAIL_TIME 31-MAY-91 FORCE_TIME RETRY_TIME 31-MAY-91 OS_USER SWILLIAMS OS_TERMINAL TWA139: HOST system1 DB_USER SWILLIAMS COMMIT#

Determining the Global Transaction ID

The global transaction ID is the common transaction ID that is the same on every node for a distributed transaction. It is of the form:

where:

• global _database _name is the database name of the global coordinator.
• hhhhhhhh is the internal database identifier of the global coordinator (in hexadecimal).
• local _transaction _id is the corresponding local transaction ID assigned on the global coordinator.

Note that the last portion of the global transaction ID and the local transaction ID match at the global coordinator. In the example, you can tell that warehouse is not the global coordinator because these numbers do not match:

LOCAL_TRAN_ID 1.21.17 GLOBAL_TRAN_ID ... 1.93.29

Determining the State of the Transaction

The transaction on this node is in a prepared state:

Therefore, warehouse waits for its coordinator to send either a commit or a rollback request.

Looking for Comments or Advice

The transaction comment or advice can include information about this transaction. If so, use this comment to your advantage. In this example, the origin and transaction type is in the transaction comment:

It could also be provided as a transaction name with a SET TRANSACTION ... NAME statement. This information can reveal something that helps you decide whether to commit or rollback the local portion of the transaction. If useful comments do not accompany an in-doubt transaction, you must complete some extra administrative work to trace the session tree and find a node that has resolved the transaction.

Step 3: Query DBA_2PC_NEIGHBORS on Local Node

The purpose of this step is to climb the session tree so that you find coordinators, eventually reaching the global coordinator. Along the way, you may find a coordinator that has resolved the transaction. If not, you can eventually work your way to the commit point site, which will always have resolved the in-doubt transaction. To trace the session tree, query the DBA _2PC _NEIGHBORS view on each node.

In this case, you query this view on the warehouse database:

CONNECT SYS/password@warehouse.acme.com SELECT * FROM DBA_2PC_NEIGHBORS WHERE LOCAL_TRAN_ID = '1.21.17' ORDER BY SESS#, IN_OUT;

Obtaining Database Role and Database Link Information

The DBA _2PC _NEIGHBORS view provides information about connections associated with an in-doubt transaction. Information for each connection is different, based on whether the connection is inbound (IN_OUT = in) or outbound (IN_OUT = out):

In this example, the IN_OUT column reveals that the warehouse database is a server for the sales client, as specified in the DATABASE column:

IN_OUT in DATABASE SALES.ACME.COM

The connection to warehouse was established through a database link from the swilliams account, as shown by the DBUSER _OWNER column:

DBUSER_OWNER SWILLIAMS

Determining the Commit Point Site

Additionally, the INTERFACE column tells whether the local node or a subordinate node is the commit point site:

Neither warehouse nor any of its descendants is the commit point site, as shown by the INTERFACE column.

Step 4: Querying Data Dictionary Views on All Nodes

At this point, you can contact the administrator at the located nodes and ask each person to repeat Steps 2 and 3 using the global transaction ID. For example, the following results are returned when Steps 2 and 3 are performed at sales and hq.

Checking the Status of Pending Transactions at sales

At this stage, the sales administrator queries the DBA _2PC _PENDING data dictionary view:

SQL> CONNECT SYS/password@sales.acme.com SQL> SELECT * FROM DBA_2PC_PENDING> WHERE GLOBAL_TRAN_ID = 'SALES.ACME.COM.55d1c563.1.93.29';

Determining the Coordinators and Commit Point Site at sales

Next, the sales administrator queries DBA _2PC _NEIGHBORS to determine the global and local coordinators as well as the commit point site:

SELECT * FROM DBA_2PC_NEIGHBORS WHERE GLOBAL_TRAN_ID = 'SALES.ACME.COM.55d1c563.1.93.29' ORDER BY SESS#, IN_OUT;

This query returns three rows:

• The connection to warehouse
• The connection to hq
• The connection established by the user

Reformatted information corresponding to the rows for the warehouse connection appears below:

Reformatted information corresponding to the rows for the hq connection appears below:

The information from the previous queries reveal the following:

• sales is the global coordinator because the local transaction ID and global transaction ID match.
• Two outbound connections are established from this node, but no inbound connections. sales is not the server of another node.
• hq or one of its servers is the commit point site.

Checking the Status of Pending Transactions at HQ

At this stage, the hq administrator queries the DBA _2PC _PENDING data dictionary view:

SELECT * FROM DBA_2PC_PENDING@hq.acme.com WHERE GLOBAL_TRAN_ID = 'SALES.ACME.COM.55d1c563.1.93.29';

At this point, you have found a node that resolved the transaction. As the view reveals, it has been committed and assigned a commit ID number:

Therefore, you can force the in-doubt transaction to commit at your local database. It is a good idea to contact any other administrators you know that could also benefit from your investigation.

Step 5: Commit the In-Doubt Transaction

You contact the administrator of the sales database, who manually commits the in-doubt transaction using the global ID:

SQL> CONNECT SYS/password@sales.acme.com SQL> COMMIT FORCE 'SALES.ACME.COM.55d1c563.1.93.29';

As administrator of the warehouse database, you manually commit the in-doubt transaction using the global ID:

SQL> CONNECT SYS/password@warehouse.acme.com SQL> COMMIT FORCE 'SALES.ACME.COM.55d1c563.1.93.29';

Step 6: Check for Mixed Outcome Using DBA _2PC _PENDING

After you manually force a transaction to commit or roll back, the corresponding row in the pending transaction table remains. The state of the transaction is changed depending on how you forced the transaction.

Every Oracle Database has a pending transaction table. This is a special table that stores information about distributed transactions as they proceed through the two-phase commit phases. You can query the pending transaction table of a database through the DBA _2PC _PENDING data dictionary view.

Also of particular interest in the pending transaction table is the mixed outcome flag as indicated in DBA _2PC _PENDING.MIXED. You can make the wrong choice if a pending transaction is forced to commit or roll back. For example, the local administrator rolls back the transaction, but the other nodes commit it. Incorrect decisions are detected automatically, and the damage flag for the corresponding pending transaction record is set (MIXED=yes).

The RECO (Recoverer) background process uses the information in the pending transaction table to finalize the status of in-doubt transactions. You can also use the information in the pending transaction table to manually override the automatic recovery procedures for pending distributed transactions.

All transactions automatically resolved by RECO are removed from the pending transaction table. Additionally, all information about in-doubt transactions correctly resolved by an administrator (as checked when RECO reestablishes communication) are automatically removed from the pending transaction table.

However, all rows resolved by an administrator that result in a mixed outcome across nodes remain in the pending transaction table of all involved nodes until they are manually deleted using DBMS _TRANSACTIONS.PURGE _MIXED.