Manually Resolving In-Doubt Transactions: Different Scenarios (Doc ID 126069.1)

Last updated on JUNE 13, 2023

Applies to:

Purpose

This note is intended to serve as an additional aid for both Analysts
and Customers when studying or being confronted with in-doubt transactions.

Scope

This note provides examples of failing distributed transactions and how to
resolve them manually. Failures at different stages of the two phase-commit
are identified and studied, together with guidelines to troubleshoot them.

Scripts are included at the bottom to setup the environment that will help
simulate failing distributed transactions. Please feel free to adjust them
to your personal needs.

In reality, you should only need to resolve an in-doubt transaction in the
following cases:
- the in-doubt transaction has locks on critical data or rollback segments
- the cause of the machine, network or software failure cannot be repaired
quickly

The RECO background process (Distributed Recovery process) of an
Oracle instance automatically resolves failures involving distributed
transactions, when the machine, network, or software problem is resolved.
Until RECO can resolve the transaction, the data is locked for both reads
and writes. Oracle blocks reads because it cannot determine which version
of the data to display for a query.

Please note that the information here only relates to Oracle distributed
transactions. The transaction is done entirely within Oracle or in other
words Oracle coordinates the transaction. There are transaction monitors
that can be used to coordinate the transactions,instead of Oracle. One
example of those is Tuxedo, where the X/A interface is used.
There could be also a situation where a non-Oracle database could be
part of a distributed transaction through a means of Transparent Gateway.
Both of those are outside the scope of this article.

The examples given here are limited to distributed transactions between
Two nodes as this is the most common encountered environment.

If you are not familiar with the two phase-commit, and handling distributed
transactions within Oracle, then you will find useful to consult the
additional documentation included at the end of this note. These materials
provide background information and terminology related to Oracle Distributed
Systems.

Details