VERIFIED SOLUTION i

Resolve Portrait Dialogue: ORA-01591: lock held by in-doubt distributed transaction string

Product Feature: Reports

Operating System: Not stated

Database: Not stated

Configuration: Not stated
 

Issue

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

The Portrait Dialogue Server error logs show below entry in it:

====================================================================

Exception: ORA-01591: lock held by in-doubt distributed transaction 99.7.59257 
Time: 12/1/2015 11:58:32 PM 
Module: C:\PROGRA~2\PST\MILLIO~1\DIALOG~1\MHDialogServer.dll 
Version: 6.1.0.349 
Filedate: 9/14/2015 2:26:44 PM 
Instancename: PRDTMB 
Username: internal 
ProcessID: 10648 
ThreadID: 11924 
Exceptiontype: EMHSQLException 
Unit: MHSQLAccessLayer.pas 
Procedure: MHSQLAccessLayer.TMHSQLAccessLayer.DoExecuteSQLRaw 
Line: 345 
SQLStatement: UPDATE dlg_opr_execution_task 
SET doeta_is_started = 'F', doeta_start_datetime = NULL 
WHERE doeta_id = :doeta_id 
AND doeta_is_started = 'T' 
AND doeta_dol_id is null 

[1B869D85] MHSQLAccessLayer.TMHSQLAccessLayer.DoExecuteSQLRaw (Line 345, "MHSQLAccessLayer.pas") 
[1B86A557] MHSQLAccessLayer.TMHSQLAccessLayer.ExecuteSQL (Line 494, "MHSQLAccessLayer.pas") 
[1B19244F] MHDialogAccessLayer.TMHDialogAccessLayer.DoResetPreparingTask (Line 1660, "MHDialogAccessLayer.pas") 
[1B19287D] MHDialogAccessLayer.TMHDialogAccessLayer.ResetHangingExecutionTasks (Line 1725, "MHDialogAccessLayer.pas") 
[1BA29037] MHInternalServiceSupport.TMHInternalServiceSupport.ResetHangingDlgExecutionTasks (Line 496, "MHInternalServiceSupport.pas") 

====================================================================

Cause

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 Mechanism got an error somewhere.

Resolution

UPDATED: March 29, 2017
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

Let's assume that we have a scenario like the below image. 

 
User-added image
The following distributed transaction executed by a person which 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';

Note: There are many Forums online. that can explain this locking issue and its resolution more presizely