ORA-01591: lock held by in-doubt distributed transaction

ORA-01591: lock held by in-doubt distributed transaction:

This error is encountered by many DBA's and cause problem by locking the distributed transaction process, and not letting the query go through, because the Two - Phase Commit Mecanism got an error somewhere.

Lets see what is a distributed transaction first:

What Are Distributed Transactions?

A distributed transaction includes one or more statements that, individually or as a group, update data on two or more distinct nodes of a distributed database.

Note: If all statements of a transaction reference only a single remote node, then the transaction is remote, not distributed


Lets assume that we have a scenerio like the below image.

The following distributed transaction executed by scott updates the local sales database, the remote hq database, and the remote maint database:

 

UPDATE scott.dept@hq.us.acme.com
  SET loc = 'REDWOOD SHORES'
  WHERE deptno = 10;
UPDATE scott.emp
  SET deptno = 11
  WHERE deptno = 10;
UPDATE scott.bldg@maint.us.acme.com
  SET room = 1225
  WHERE room = 1163;
COMMIT;

There are two types of permissible operations in distributed transactions:

• DML and DDL Transactions
• Transaction Control Statements

The following list describes DML and DDL operations supported in a distributed transaction:

• CREATE TABLE AS SELECT
• DELETE
• INSERT (default and direct load)
• LOCK TABLE
• SELECT
• SELECT FOR UPDATE

The following list describes supported transaction control statements:

• COMMIT
• ROLLBACK
• SAVEPOINT

 The distributed transaction process is verified by the Two Phase Commit Mechanism.

Unlike a transaction on a local database, a distributed transaction involves altering data on multiple databases. Consequently, distributed transaction processing is more complicated, because Oracle must coordinate the committing or rolling back of the changes in a transaction as a self-contained unit. In other words, the entire transaction commits, or the entire transaction rolls back.

In-Doubt Transactions

The two-phase commit mechanism ensures that all nodes either commit or perform a rollback together. What happens if any of the three phases fails because of a system or network error? The transaction becomes in-doubt.

Distributed transactions can become in-doubt in the following ways:

• A server machine running Oracle software crashes
• A network connection between two or more Oracle databases involved in distributed processing is disconnected
• An unhandled software error occurs

The RECO process automatically resolves in-doubt 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.

Manual Resolution of In-Doubt Transactions

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.

Resolution of in-doubt transactions can be complicated. The procedure requires that you do the following:

• Identify the transaction identification number for the in-doubt transaction.
• Query the DBA_2PC_PENDING and DBA_2PC_NEIGHBORS views to determine whether the databases involved in the transaction have committed.
• If necessary, force a commit using the COMMIT FORCE statement or a rollback using the ROLLBACK FORCE statement.

Here are some codes to help you through the process:

This one brings in-doubt transactions:

select * from DBA_2PC_PENDING where state='prepared'

 This one prepares the rollback script for the transactions:

select 'rollback force '''||local_tran_id||''';' from DBA_2PC_PENDING where state='prepared'

 At last run the rollback with the transaction id:

rollback force '29.34.42726';