A distributed transaction changes data in more than one database as one unit. Its participants must reach the same outcome: all commit, or all roll back. A failure during the commit protocol can leave a participant holding locks while it waits to learn that outcome.
Oracle DBA Lesson 14 Part 2 — Why Is a Distributed Transaction in Doubt?
This lesson explains preparation, interrupted communication, automatic recovery by RECO, and the evidence an administrator gathers. External transaction managers and XA recovery have additional responsibilities outside this example.
One stock transfer across two databases
A customer transfers one item between warehouses:
| Point in the example | Database A | Database B |
|---|---|---|
| Before the transaction | 10 items | 4 items |
| Changes being made, still uncommitted | 9 items | 5 items |
| Successful shared commit | 9 items committed | 5 items committed |
| Shared rollback | 10 items retained | 4 items retained |
Total stock remains 14 items after either valid outcome. If A commits its reduction but B rolls back its increase, the databases hold 9 and 4: the transfer has become inconsistent.
Intermediate values describe the transaction's work. They do not imply that an unrelated session can read unfinished changes.
Prepare is a promise to finish the shared outcome
Oracle coordinates distributed commit with its two-phase commit protocol. The global coordinator starts the coordination. The commit point site is where the commit is first recorded. Oracle selects it using the participants' commit point strengths. The coordinator and the commit point site can be different databases.
In this two-database example, A has both roles. B is the other participant.
- A asks B to prepare.
- B saves the transaction information required to recover its prepared work and retains the necessary locks. Physical online redo log files on disk provide recoverable records of that work.
- B reports that it is prepared. It promises to complete commit or rollback when the distributed protocol supplies the outcome.
- A commits at the commit point site. The coordinator communicates that outcome to B.
- B commits its local portion, releases its transaction locks, and reports completion. The protocol can then remove completed coordination records.
The commit point site does not enter the prepared state. Here, prepared applies to B. Oracle also has read-only and abort responses during preparation. They extend the mechanism beyond this particular failure case.
The disk label explains why prepared information remains recoverable after failure. This lesson introduces no SGA or PGA component and no process-memory placement. The shared outcome comes from the distributed protocol and its recoverable records.
See distributed transactions concepts.
The interruption creates uncertainty at B
Suppose A records its commit, then communication fails before the commit message reaches B. A has committed its local part. B remains prepared and cannot yet learn the recorded outcome.
The transaction is in doubt at B. Its necessary locks remain held until the outcome is resolved. Affected reads and writes can be blocked. Oracle may report ORA-01591: lock held by in-doubt distributed transaction. The identifier in that error identifies the local pending transaction.
Part 1 followed an abandoned, uncommitted local update whose unfinished work could be rolled back. Here, another database may already have recorded the distributed commit. Independent local choices can produce conflicting outcomes.
See distributed transactions concepts.
RECO resolves the pending distributed outcome
RECO, the recoverer background process, attempts to communicate with the other databases involved in an in-doubt transaction. It can use an existing connection or establish another one. Its retries occur at increasing intervals. One observation cannot supply an exact recovery deadline.
After the underlying communication or system failure is resolved, normal automatic recovery determines the recorded outcome and completes the local pending work accordingly. B learns the commit outcome, commits the increase to five, and releases its locks. A remains committed at nine. Both portions of the transfer now agree.
The traveling COMMIT marker represents decision information, not a data block or a second transaction. A retains its recorded commit while the information reaches B. RECO normally removes resolved entries from pending transaction tables. A mixed outcome or a permanently lost participant requires a separate incident procedure.
RECO addresses distributed transaction recovery. A missing physical data file requires a separate restore and media recovery workflow, commonly using RMAN.
See process architecture.
Inspect the pending transaction
SELECT local_tran_id, global_tran_id, state,
mixed, fail_time, retry_time
FROM dba_2pc_pending
ORDER BY fail_time;This query identifies distributed transactions awaiting recovery, their local state, and recovery timing. ORDER BY fail_time lists older pending-row timestamps first.
| Column | Interpretation |
|---|---|
LOCAL_TRAN_ID | Transaction identifier at the inspected database, normally in n.n.n form. Preserve database identity alongside it. |
GLOBAL_TRAN_ID | Common identifier used to correlate the same distributed transaction across participants. Local IDs can differ between databases. |
STATE | Protocol state recorded at this site. Compare records at the other sites. |
MIXED | YES indicates some portions committed and other portions rolled back. Investigate and reconcile that inconsistency. |
FAIL_TIME | Value of SYSDATE when the pending row was inserted during transaction or system recovery, rather than necessarily the exact network-failure time. |
RETRY_TIME | When automatic recovery last attempted to recover the transaction. |
| Field | Example value |
|---|---|
| Local transaction | 4.12.30 |
| Global transaction | DB_A.EXAMPLE.COM.1a2b3c4d.2.7.19 |
| State | prepared |
The local ID identifies B's work. The global ID connects it with A's transaction. B prepared and awaits the decision.
Interpret states through the mechanism
| State | Why it can appear | Useful next question |
|---|---|---|
collecting | A coordinator is collecting participant information before deciding whether preparation is possible. | Which participant or connection is preventing progress? |
prepared | The participant prepared, retains necessary locks, and awaits the decision. | Where is the recorded outcome, and can recovery reach it? |
committed | This site's local part committed while completion remains pending elsewhere. | Have other participants reached that outcome? |
forced commit / forced rollback | An administrator forced the local outcome. | Does that result agree with the other participants? |
An empty result is normal when no unresolved transactions are recorded in this view in the inspected scope. One empty view cannot establish another database's state or application-level reconciliation. Similarly, MIXED = NO cannot substitute for following the shared outcome to completion.
See DBA_2PC_PENDING and managing distributed transactions.
Gather evidence before considering a forced outcome
Record the database or container identity, the local and global transaction IDs, the state, the mixed flag, the pending and retry timestamps, the application errors, and the observed impact. Establish the failed communication path and compare records with administrators of the other participants. Coordinator and commit point evidence matter because the outcome may already be recorded elsewhere.
DBA_2PC_NEIGHBORS can help investigate incoming and outgoing relationships and the commit point role. Detailed session-tree tracing is beyond this introductory lesson.
Normal automatic recovery is the preferred path when the failure can be repaired. A prolonged outage involving critical blocked work may justify intervention under a coordinated incident procedure. Forcing a local outcome can conflict with another site's result. Removing a pending row cannot reconcile conflicting application data.
See managing distributed transactions.
Recap
A distributed transaction must reach one shared outcome: all commit, or all roll back. In this example, A is the global coordinator and the commit point site, and B is the other participant. A asks B to prepare. B saves the information required to recover its prepared work, retains the necessary locks, and promises to finish commit or rollback when the protocol supplies the outcome. The commit point site does not enter the prepared state. Online redo on disk keeps that prepared work recoverable. If A records the commit and communication fails before B receives it, the transaction is in doubt at B. The locks stay until the outcome is resolved, and Oracle may report ORA-01591. Part 1 followed an abandoned local update that could be rolled back. Here, A may already have committed, so a local rollback at B can leave the stock inconsistent: 9 items and 4 items, instead of 14 after either valid shared outcome.
RECO retries contact with the other databases, using an existing connection or a new one, at increasing intervals. After communication is restored, automatic recovery applies the recorded commit: B commits the increase to five and releases its locks, while A remains committed at nine. The COMMIT marker is decision information, not a data block or a second transaction. Read DBA_2PC_PENDING for the local and global IDs, state, mixed flag, fail time, and retry time. MIXED = YES means some portions committed and others rolled back. An empty view does not describe another database. Prefer automatic recovery, and collect both sites' evidence before any forced outcome. RECO does not replace a missing data file. That file needs a separate restore and media recovery, commonly with RMAN.
No comments:
Post a Comment