when i am running the procedure i am getting this error
ORA 01591 lock held by in-doubt distributed transaction
And i commited the transaction manually, but again it is comming.
Is there any problem in the code.
here i am attaching the procedure
Printable View
when i am running the procedure i am getting this error
ORA 01591 lock held by in-doubt distributed transaction
And i commited the transaction manually, but again it is comming.
Is there any problem in the code.
here i am attaching the procedure
Check DBA_2PC_PENDING and dba_2pc_neighbors for distributed transaction id's.
If you find rows there, do:
;)Code:ROLLBACK WORK FORCE '{transaction id.}';
Use this if ROLLBACK WORK FORCE '{transaction id.}'; does not work
SQL> alter system set undo_suppress_errors=true sid='XXXXX';
SQL> set transaction use rollback segment system;
Transaction set.
SQL> exec dbms_transaction.purge_lost_db_entry('74.42.1112345');
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.
Thanks for Both of them,
I will try it :)
Quote:
Originally Posted by BV1963
my god - what a load of rubbish, you cannot set transation like that!! all you are doing is supressing the error it gives
gee whizz
Hi davey,
when i got the error first
i followed the steps which i got from the metalink.
select LOCAL_TRAN_ID, TRAN_COMMENT,STATE,STATUS from pending_trans$;
SELECT LOCAL_TRAN_ID, GLOBAL_TRAN_ID, STATE, MIXED, HOST, COMMIT#
FROM DBA_2PC_PENDING
WHERE LOCAL_TRAN_ID ='10.25.36897';
COMMIT FORCE '10.25.36897', '10146005246693';
after commiting the transaction i am able to insert.
but the same error is comming again...So what steps i can take for stoping it :)
I think it is the problem with the aplication ?
Quote:
Originally Posted by davey23uk
It does remove pending transaction even when commit or rollback force do not work .try it for yourself before calling it 'load of rubbish"
Under certain conditions, it may not be possible for an automatic recovery to execute without errors. As a result, recovery cannot identify it. (A possible symptom for this situation is when the ORA-02062 error is raised.) In this case, the DBA may use the DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY procedure to clean up any entries whose state is not "prepared."
Perhaps changing the process would stop this error from occurring. This just looks like a kludge to keep bad code running.Quote:
Originally Posted by BV1963
needs some alteration in the code :)Quote:
Originally Posted by gandolf989
please let me know why the transaction is pending,how to find where it is blocking.How to check which query is performing when the state of transaction
is prepared