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

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

One of the application support guy asked me for help. He received the following error.

Error starting at line 3 in command:

update scv_cv_publish_xml set status = 'A' where status = 'V' and overworking_status='A'

Error report:
SQL Error: ORA-01591: lock held by in-doubt distributed transaction 63.8.92859
ORA-01591 – “lock held by in-doubt distributed transaction %s”
Cause: Trying to access resource that is locked by a dead two-phase commit transaction that is in prepared state.
Action: DBA should query the pending_trans$ and related tables, and attempt to repair network connection(s) to coordinator and commit point. If timely repair is not possible, DBA should contact DBA at commit point if known or end user for correct outcome, or use heuristic default if given to issue a heuristic commit or abort command to finalize the local portion of the distributed transaction.

Solution

1. Login to database and check the transaction.

SQL> select local_tran_id, state from dba_2pc_pending where local_tran_id='63.8.92859';

LOCAL_TRAN_ID  STATE
-------------  ---------
3.8.92859      prepared

SQL> select a.sql_text, s.osuser, s.username from v$transaction t, v$session s, v$sqlarea a where s.taddr = t.addr and a.address = s.prev_sql_addr and t.xidusn = 63 and t.xidslot = 8 and t.xidsqn = 92859;
no rows selected

2. How To Resolve DBA_2PC_PENDING Entries [ID 401302.1].

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

KTUXEUSN KTUXESLT KTUXESQN STATUS   FLAGS
-------- -------- -------- -------- ------------------------
63       8        92859    PREPARED SCO|COL|REV|DEAD|EXTDTX

SQL> rollback force '63.8.92859';
Rollback complete.
SQL> commit;
Commit complete.
SQL> select local_tran_id, state from dba_2pc_pending where local_tran_id='63.8.92859';

LOCAL_TRAN_ID  STATE
-------------  --------------- 
63.8.92859     forced rollback


3. Check with application team, if still get error then follow the following steps

SQL> delete from sys.pending_trans$ where local_tran_id='63.8.92859';
1 row deleted.
sys@avac> delete from sys.pending_sessions$ where local_tran_id ='63.8.92859';
1 row deleted.
sys@avac> delete from sys.pending_sub_sessions$ where local_tran_id = '63.8.92859';
0 rows deleted.
sys@avac> commit;
Commit complete.
sys@avac> select local_tran_id, state from dba_2pc_pending where local_tran_id='63.8.92859';
no rows selected

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.