A server machine running Oracle Database software crashes
A network connection between two or more Oracle Databases involved in distributed processing is disconnected
An unhandled software error occurs
See Also:"In-Doubt Transactions" for a conceptual overview of in-doubt transactions
You can manually force the commit or rollback of a local, in-doubt distributed transaction. Because this operation can generate consistency problems, perform it only when specific conditions exist.
This section contains the following topics:
ORA-02050: transaction ID rolled back, some remote dbs may be in-doubt ORA-02053: transaction ID committed, some remote dbs may be in-doubt ORA-02054: transaction ID in-doubt
A robust application should save information about a transaction if it receives any of the preceding errors. This information can be used later if manual distributed transaction recovery is desired.
No action is required by the administrator of any node that has one or more in-doubt distributed transactions due to a network or system failure. The automatic recovery features of the database transparently complete any in-doubt transaction so that the same outcome occurs on all nodes of a session tree (that is, all commit or all roll back) after the network or system failure is resolved.
In extended outages, however, you can force the commit or rollback of a transaction to release any locked data. Applications must account for such possibilities.
The in-doubt transaction locks data that is required by other transactions. This situation occurs when the
ORA-01591 error message interferes with user transactions.
An in-doubt transaction prevents the extents of a undo segment from being used by other transactions. The first portion of the local transaction ID of an in-doubt distributed transaction corresponds to the ID of the undo segment, as listed by the data dictionary view
The failure preventing the two-phase commit phases to complete cannot be corrected in an acceptable time period. Examples of such cases include a telecommunication network that has been damaged or a damaged database that requires a long recovery time.
Normally, you should make a decision to locally force an in-doubt distributed transaction in consultation with administrators at other locations. A wrong decision can lead to database inconsistencies that can be difficult to trace and that you must manually correct.
If none of these conditions apply, always allow the automatic recovery features of the database to complete the transaction. If any of these conditions are met, however, consider a local override of the in-doubt transaction.
If you decide to force the transaction to complete, analyze available information with the following goals in mind.
DBA_2PC_PENDING view to find a node that has either committed or rolled back the transaction. If you can find a node that has already resolved the transaction, then you can follow the action taken at that node.
See if any information is given in the
TRAN_COMMENT column of
DBA_2PC_PENDING for the distributed transaction. Comments are included in the
COMMENT clause of the
COMMIT statement, or if transaction naming is used, the transaction name is placed in the
TRAN_COMMENT field when the transaction is committed.
COMMIT COMMENT 'Finance/Accts_pay/Trans_type 10B';
See Also:"Naming Transactions"
See if any information is given in the
ADVICE column of
DBA_2PC_PENDING for the distributed transaction. An application can prescribe advice about whether to force the commit or force the rollback of separate parts of a distributed transaction with the
ADVISE clause of the
ALTER SESSION statement.
The advice sent during the prepare phase to each node is the advice in effect at the time the most recent DML statement executed at that database in the current transaction.
For example, consider a distributed transaction that moves an employee record from the
emp table at one node to the
emp table at another node. The transaction can protect the record--even when administrators independently force the in-doubt transaction at each node--by including the following sequence of SQL statements:
ALTER SESSION ADVISE COMMIT; INSERT INTO emp@hq ... ; /*advice to commit at HQ */ ALTER SESSION ADVISE ROLLBACK; DELETE FROM emp@sales ... ; /*advice to roll back at SALES*/ ALTER SESSION ADVISE NOTHING;
If you manually force the in-doubt transaction following the given advice, the worst that can happen is that each node has a copy of the employee record; the record cannot disappear.