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:
ORA-01591:lock held by in-doubt distributed transaction 1.21.17In 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:
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:
global_database_name.hhhhhhhh.local_transaction_idwhere:
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:
Determining the State of the Transaction
The transaction on this node is in a prepared state:
STATE preparedTherefore, 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:
TRAN_COMMENT Sales/New Order/Trans_type 10BIt 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:
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:
The connection to warehouse was established through a database link from the swilliams account, as shown by the DBUSER _OWNER column:
Determining the Commit Point Site
Additionally, the INTERFACE column tells whether the local node or a subordinate node is the commit point site:
INTERFACE NNeither 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:
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:
This query returns three rows:
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:
Checking the Status of Pending Transactions at HQ
At this stage, the hq administrator queries the DBA _2PC _PENDING data dictionary view:
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:
STATE COMMIT COMMIT# 129314Therefore, 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:
As administrator of the warehouse database, you manually commit the in-doubt transaction using the global ID:
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.
|
|
Oracle 10g Related Tutorials |
|
---|---|
Oracle 9i Tutorial | Oracle 8i Tutorial |
Informatica Tutorial | Oracle 11g Tutorial |
Oracle 10g Related Interview Questions |
|
---|---|
Oracle 10g Interview Questions | Oracle 9i Interview Questions |
Oracle 8i Interview Questions | Informatica Interview Questions |
PL/SQL Interview Questions | Oracle 11g Interview Questions |
SQL Interview Questions | Oracle apps Interview Questions |
Sybase Interview Questions | Oracle Apps ERP Interview Questions |
Oracle 7.3 Interview Questions | Oracle Access Manager Interview Questions |
Oracle Application Framework Interview Questions | Oracle Apps DBA Interview Questions |
Oracle 10g Related Practice Tests |
|
---|---|
Oracle 10g Practice Tests | Oracle 9i Practice Tests |
Oracle 8i Practice Tests | Informatica Practice Tests |
PL/SQL Practice Tests | Oracle 11g Practice Tests |
SQL Practice Tests | Oracle apps Practice Tests |
Sybase Practice Tests | Oracle Apps ERP Practice Tests |
Oracle 7.3 Practice Tests |
All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.