Oracle DBA · Lesson 14B

Oracle DBA Lesson 14B — Recognize an In-Doubt Distributed Transaction

A change that spans databases must finish the same way everywhere. When two-phase commit is interrupted, a participant can sit prepared without a local final decision. This lesson shows what “in doubt” means and how the recoverer brings the outcome back into agreement.

Watch on YouTube

Oracle DBA Lesson 14B — Recognize an In-Doubt Distributed Transaction

AI Ops Pro

One unit across databases

A distributed transaction changes data in more than one database as a single unit. Every participant must reach the same outcome: all commit, or all roll back. That requirement is what makes an interrupted exchange dangerous — partial success is not an acceptable end state.

Prepare first, then one durable decision

Two-phase commit asks participants to prepare, then coordinates a single commit decision. Preparation means a participant is ready to finish either way and has durable recovery information. Only after that exchange does the coordinator drive the final commit or rollback at each site.

What “in doubt” means

A failure during the decision exchange can leave a participant uncertain. That transaction is in doubt: it is prepared, but the final outcome is not known locally. While it stays unresolved, the prepared work can keep data locked. Reconnecting the network alone does not invent a commit decision — recovery information and communication with the other databases are both required.

RECO finishes the agreement

The recoverer (RECO) reconnects with the other databases and resolves the transaction consistently when communication and recovery information are available. Automatic resolution may commit or roll back; it does not always mean rollback. When resolution completes, the transaction’s locks are released. On many labs RECO is quiet; it matters when database links and commits span databases.

SELECT name, description
FROM   v$bgprocess
WHERE  name = 'RECO';

Inspect pending distributed work

When you suspect an in-doubt transaction, look for pending two-phase commit rows and their neighbors. These dictionary views show local and global identifiers, state, and related databases. Treat empty results as normal on a single-database lab with no distributed commits. Never force a decision from this page — understand state first, then follow your site’s recovery procedure.

SELECT local_tran_id, global_tran_id, state, mixed, advice
FROM   dba_2pc_pending;

SELECT local_tran_id, in_doubt, database, dbuser_owner, interface
FROM   dba_2pc_neighbors;

Illustrative checks only — no live result set is implied. Privileges and view availability depend on your role and release.

Gotchas

• In doubt = prepared without a known local final outcome — not “maybe we skipped prepare.”
• Network reconnect alone does not invent a commit decision.
• RECO’s automatic outcome can be commit or rollback; do not assume rollback.
• Locks can remain until resolution finishes.
• Ordinary single-session kill cleanup (PMON family) is a different path from distributed in-doubt recovery.
• Empty dba_2pc_pending on a simple lab is often expected.

Quick quiz

Choose one answer, then submit. You’ll see the correct answer and a short why.

1. What must be true of every participant in a distributed transaction?

Each site may commit independently as soon as its local redo is written All participants must reach the same outcome — commit everywhere or roll back everywhere Only the coordinator keeps locks; remote sites never prepare Participants vote after commit so RECO can undo mistakes

2. A transaction is “in doubt.” What does that mean?

The instance crashed and SMON has not opened the database yet A user session shows STATUS=KILLED while PMON cleans locks It prepared, but the final outcome is not known locally and may still hold locks Archive destinations failed, so redo groups cannot reuse

3. What does RECO do for an in-doubt distributed transaction?

Reconnects with the other databases and resolves consistently when communication and recovery information are available Always rolls back every prepared transaction as soon as the network returns Detects dead user processes and delegates lock cleanup to CLMN Applies redo at startup and reclaims temporary space after failed operations

4. Network returns and RECO is working. Is the outcome always rollback?

Yes — in-doubt always means rollback once RECO starts Yes — prepare is undone automatically when the link drops No — RECO always commits to finish faster No — resolution may commit or roll back; both sides must stay consistent

AIOps Pro

https://www.apps-dba.com/ https://x.com/AIOpsPro https://www.youtube.com/@AIOpsPro