OraFAQ Forum: Replication » Lockheld in by In-doubt transaction

Home » Server Options » Replication » Lockheld in by In-doubt transaction (Oracle 10.2.0.3)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Lockheld in by In-doubt transaction [message #466447]

Mon, 19 July 2010 13:05

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...

Report message to a moderator

Re: Lockheld in by In-doubt transaction [message #466453 is a reply to message #466447]

Mon, 19 July 2010 14:25

babuknb
Messages: 1736
Registered: December 2005
Location: NJ

Senior Member

For In doubt transaction check here

Report message to a moderator

Re: Lockheld in by In-doubt transaction [message #466455 is a reply to message #466453]

Mon, 19 July 2010 14:29

babuknb
Messages: 1736
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

Report message to a moderator

Re: Lockheld in by In-doubt transaction [message #466494 is a reply to message #466447]

Mon, 19 July 2010 23:04

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

Report message to a moderator

Re: Lockheld in by In-doubt transaction [message #466556 is a reply to message #466494]

Tue, 20 July 2010 02:21

babuknb
Messages: 1736
Registered: December 2005
Location: NJ

Senior Member

Could you please post output of the below query

SELECT * FROM deferror;

Babu

Report message to a moderator

Re: Lockheld in by In-doubt transaction [message #466566 is a reply to message #466556]

Tue, 20 July 2010 03:01

paranidharan
Messages: 6
Registered: July 2010
Location: Chennai

Junior Member

no rows returned..

Report message to a moderator

Re: Lockheld in by In-doubt transaction [message #466581 is a reply to message #466566]

Tue, 20 July 2010 03:58

babuknb
Messages: 1736
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

Report message to a moderator

Re: Lockheld in by In-doubt transaction [message #466585 is a reply to message #466581]

Tue, 20 July 2010 04:07

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..

Report message to a moderator

Re: Lockheld in by In-doubt transaction [message #466587 is a reply to message #466585]

Tue, 20 July 2010 04:15

babuknb
Messages: 1736
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

Report message to a moderator

Re: Lockheld in by In-doubt transaction [message #466594 is a reply to message #466587]

Tue, 20 July 2010 04:35

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

Report message to a moderator

Re: Lockheld in by In-doubt transaction [message #466662 is a reply to message #466594]

Tue, 20 July 2010 08:03

babuknb
Messages: 1736
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

Report message to a moderator

Re: Lockheld in by In-doubt transaction [message #466712 is a reply to message #466447]

Tue, 20 July 2010 13:27

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.

Report message to a moderator

Previous Topic:	GoldenGate Environment Variable
Next Topic:	Materialized view refresh

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Feb 06 14:02:08 CST 2025