Home » Server Options » Replication » Lockheld in by In-doubt transaction (Oracle 10.2.0.3)
Lockheld in by In-doubt transaction [message #466447] Mon, 19 July 2010 13:05 Go to next message
paranidharan
Messages: 6
Registered: July 2010
Location: Chennai
Junior Member
In my web application i am getting an error @ a particular page. I am using JTA user transaction to mark the transaction boundaries. On commit i am confronting the error "lock held in by In-Doubt transaction. I queried pending_trans$,dba_2pc_pending and force commit the transaction.Also i purged the transaction using Purge_Lost_db_entity. Still after doing all these i am facing the same problem with the new transaction no. What is the solution to this.. Can anyone help me out in this...
Re: Lockheld in by In-doubt transaction [message #466453 is a reply to message #466447] Mon, 19 July 2010 14:25 Go to previous messageGo to next message
babuknb
Messages: 1734
Registered: December 2005
Location: NJ
Senior Member

For In doubt transaction check here
Re: Lockheld in by In-doubt transaction [message #466455 is a reply to message #466453] Mon, 19 July 2010 14:29 Go to previous messageGo to next message
babuknb
Messages: 1734
Registered: December 2005
Location: NJ
Senior Member

>>Still after doing all these i am facing the same problem with the new transaction no

Post your error message.

Have you tried COMMIT FORCE?

Babu
Re: Lockheld in by In-doubt transaction [message #466494 is a reply to message #466447] Mon, 19 July 2010 23:04 Go to previous messageGo to next message
paranidharan
Messages: 6
Registered: July 2010
Location: Chennai
Junior Member
tis is the error message.
ORA-01591: Lock held by in-doubt distributed transaction <local transaction ID> .
I did commit force <local transaction Id>.
I have restarted the DB & app server. not yet resolved
Re: Lockheld in by In-doubt transaction [message #466556 is a reply to message #466494] Tue, 20 July 2010 02:21 Go to previous messageGo to next message
babuknb
Messages: 1734
Registered: December 2005
Location: NJ
Senior Member

Could you please post output of the below query

SELECT * FROM deferror;

Babu
Re: Lockheld in by In-doubt transaction [message #466566 is a reply to message #466556] Tue, 20 July 2010 03:01 Go to previous messageGo to next message
paranidharan
Messages: 6
Registered: July 2010
Location: Chennai
Junior Member
no rows returned..
Re: Lockheld in by In-doubt transaction [message #466581 is a reply to message #466566] Tue, 20 July 2010 03:58 Go to previous messageGo to next message
babuknb
Messages: 1734
Registered: December 2005
Location: NJ
Senior Member

Fine. Then check is there any locks during commit force

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

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.


Babu
Re: Lockheld in by In-doubt transaction [message #466585 is a reply to message #466581] Tue, 20 July 2010 04:07 Go to previous messageGo to next message
paranidharan
Messages: 6
Registered: July 2010
Location: Chennai
Junior Member
I did follow the below steps:

SELECT * FROM PENDING_TRANS$

SELECT
LOCAL_TRAN_ID, GLOBAL_TRAN_ID, STATE, MIXED, HOST, COMMIT#
FROM
DBA_2PC_PENDING
WHERE
LOCAL _TRAN_ID = '??.';

SELECT LOCAL_TRAN_ID, IN_OUT, DATABASE, INTERFACE
FROM DBA_2PC_NEIGHBORS;

COMMIT FORCE 'local transactionID', 'SCN';

DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY (local transactionID); OR
DBMS_TRANSACTION.PURGE_MIXED (local transactionID);

SELECT s.inst_id,
s.sid,
s.serial#,
p.spid,
s.username,
s.program
FROM gv$session s
JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
WHERE s.type != 'BACKGROUND'
and s.program='JDBC Thin Client'

ALTER SYSTEM KILL SESSION '102,10' IMMEDIATE;

Database shutdown and restart doesnt work..

Re: Lockheld in by In-doubt transaction [message #466587 is a reply to message #466585] Tue, 20 July 2010 04:15 Go to previous messageGo to next message
babuknb
Messages: 1734
Registered: December 2005
Location: NJ
Senior Member

Also post output the above query's

I hope you don't have network issues between mater & materialized view site. While doing COMMIT FORCE check is there any resource locks from any other objects

Babu
Re: Lockheld in by In-doubt transaction [message #466594 is a reply to message #466587] Tue, 20 July 2010 04:35 Go to previous messageGo to next message
paranidharan
Messages: 6
Registered: July 2010
Location: Chennai
Junior Member
I dont understand y u need the output of the queries. There are entries in pending_trans$,dba_2pc_pending,dba_pc_neighbors. I dont have any other locks on that particular resource.Commit is successful only
Re: Lockheld in by In-doubt transaction [message #466662 is a reply to message #466594] Tue, 20 July 2010 08:03 Go to previous messageGo to next message
babuknb
Messages: 1734
Registered: December 2005
Location: NJ
Senior Member

Paranidharan -

>>Commit is successful only

If COMMIT statement successfully completed then why are you getting ORA-01591 error message?

Further I would like to recommend to check "How to Troubleshoot Distributed Transactions [ID 100664.1]"

Babu
Re: Lockheld in by In-doubt transaction [message #466712 is a reply to message #466447] Tue, 20 July 2010 13:27 Go to previous message
paranidharan
Messages: 6
Registered: July 2010
Location: Chennai
Junior Member
I dont understand y i m getting this error again & again.. I m googling for the past 3 days. still i dont get the required info.
Previous Topic: GoldenGate Environment Variable
Next Topic: Materialized view refresh
Goto Forum:
  


Current Time: Thu Nov 20 21:30:28 CST 2014

Total time taken to generate the page: 0.08992 seconds