ORA-01591: LOCK HELD BY IN-DOUBT DISTRIBUTED TRANSACTION STRING

This can be encountered in an environment that use two-phase commit, managed distributed transaction, and where the client is having a network problem with the database server. The setup was Oracle Weblogic 10.3.3 with Oracle’s Thin XA Driver, and Oracle 11g Database Server.

The first thing to do is to check the result of these queries:
Check the following table:

select * from dba_2pc_pending;
select * from dba_2pc_neighbors;
select * from pending_trans$;
select * from pending_sessions$;
select * from pending_sub_sessions$;

From the above queries keep all the value of LOCAL_TRAN_ID in each table and try to force commit or rollback.

ALTER SYSTEM DISABLE DISTRIBUTED RECOVERY;
COMMIT FORCE 'LOCAL_TRAN_ID';

or

ROLLBACK FORCE 'LOCAL_TRAN_ID';

Then try to purge it using these commands, first check the value of _smu_debug_mode

SHOW PARAMETER _smu_debug_mode;

Keep the value, then change it to 4

ALTER SYSTEM SET "_smu_debug_mode" = 4;
COMMIT;

Purge the previously committed or rollback-ed transaction

EXEC DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('LOCAL_TRAN_ID');
COMMIT;

Repeat each steps for each LOCAL_TRAN_ID, turn on recovery for distributed transaction and restore _smu_debug_mode value when finish

ALTER SYSTEM ENABLE DISTRIBUTED RECOVERY;
ALTER SYSTEM SET _smu_debug_mode=original-value

However, when this solution didn’t fix the problem, or when you failed to purge the transaction. This could happen because DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY is not able to find appropriate record in several tables, to do so inserting several dummy data must be done. First check whether the troubled LOCAL_TRAN_ID is in x$ktuxe.

SELECT KTUXEUSN, KTUXESLT, KTUXESQN, /* Transaction ID */ KTUXESTA Status, KTUXECFL Flags FROM x$ktuxe WHERE ktuxesta!='INACTIVE';

create dummy data to the pending_trans$ and pending_sessions$ table if in both tables there are no rows with specified LOCAL_TRAN_ID

insert to pending_trans$ you only need to specify LOCAL_TRANS_ID the rest of the values is dummy.

INSERT INTO PENDING_TRANS$ (LOCAL_TRAN_ID, GLOBAL_TRAN_FMT, GLOBAL_ORACLE_ID, STATE, STATUS, SESSION_VECTOR, RECO_VECTOR,TYPE#, FAIL_TIME,RECO_TIME) VALUES('11.5.4', 306206, 'xxxxxxx.12345.1.2.3', 'prepared','p', HEXTORAW('00000001'), HEXTORAW('00000000'), 0, SYSDATE, SYSDATE);
COMMIT;

insert to pending_sessions$ you only need to specify LOCAL_TRANS_ID the rest of the values is dummy.

INSERT INTO PENDING_SESSIONS$ values('11.5.4',1 , hextoraw('05004F003A1500000104'),'C',0,30258592,'',146);
COMMIT;

crosscheck if the data is exist:

select * from pending_trans$;
select * from pending_sessions$;

rollback the transasction and pruge it for each LOCAL_TRAN_ID:

ROLLBACK FORCE 'LOCAL_TRANS_ID';
ALTER SYSTEM SET "_smu_debug_mode" = 4;
EXEC DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('LOCAL_TRAN_ID');
COMMIT;

crosscheck these tables (must be empty):

select * from dba_2pc_pending;
select * from dba_2pc_neighbors;
select * from pending_trans$;
select * from pending_sessions$;
select * from pending_sub_sessions$;

Advertisements