Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: lock held by in-doubt distributed transaction

RE: lock held by in-doubt distributed transaction

From: <Tony.Adolph_at_o2.com>
Date: Thu, 10 Feb 2005 16:29:34 +0100
Message-ID: <OF5211A1F1.E164E2A2-ONC1256FA4.00543547-C1256FA4.0054FC9B@viaginterkom.de> 





For all / any that are interested Oracle Support fixed my problem by 
inserting dummy rows into pending_trans$ and pending_sessions$
They recommend the following note, but none of the scenarios here require 
what they advocated in our case:  Note: 126069.1 Manually Resolving 
In-Doubt Transactions



but anyway, here's what happened:



SQL> SELECT KTUXEUSN, KTUXESLT, KTUXESQN, /* Transaction ID */
  2  KTUXESTA Status,


  3  KTUXECFL Flags


  4  FROM x$ktuxe WHERE ktuxesta!='INACTIVE' AND ktuxeusn= 4;



  KTUXEUSN   KTUXESLT   KTUXESQN STATUS           FLAGS  


---------- ---------- ---------- ---------------- ------------------------ 
 
         4         18       4935 PREPARED         SCO|COL|REV|DEAD  





SQL> insert into pending_trans$


  2  ( 


LOCAL_TRAN_ID,GLOBAL_TRAN_FMT,GLOBAL_ORACLE_ID,STATE,STATUS,SESSION_VECTOR,RECO_VECTOR,TYPE#,FAIL_TIME,RECO_TIME)

  3  values( '4.18.4935',306206, /* */ 'XXXXXXX.12345.1.2.3', /* These 
values can be used without any */


  4  'prepared','P', /* modification. Most of the values are */
  5  hextoraw( '00000001' ), /* constant. */
  6  hextoraw( '00000000' ), /* */


  7  0, sysdate, sysdate );



1 row created.



SQL> insert into pending_sessions$ values('4.18.4935',1,
  2  hextoraw('05004F003A1500000104'),'C',0,30258592,'',146);



1 row created.



commit;



SQL> select * from pending_sessions$



LOCAL_TRAN_ID          SESSION_ID  


---------------------- ----------  




BRANCH_ID  





I      TYPE# PARENT_DBID  


- ---------- ----------------  




PARENT_DB  





 DB_USERID  




4.18.4935                       1  
05004F003A1500000104  
C          0 30258592  
  
       146  
  





SQL> select * from pending_trans$;



LOCAL_TRAN_ID          GLOBAL_TRAN_FMT  


---------------------- ---------------  


GLOBAL_ORACLE_ID  





GLOBAL_FOREIGN_ID  





TRAN_COMMENT  





STATE            S H SESSION_ RECO_VEC      TYPE# FAIL_TIME  


---------------- - - -------- -------- ---------- ------------------  
HEURISTIC_TIME     RECO_TIME          TOP_DB_USER  
------------------ ------------------ ------------------------------  


TOP_OS_USER  





TOP_OS_HOST  





TOP_OS_TERMINAL  




GLOBAL_COMMIT#       SPARE1 SPARE2                             SPARE3  
---------------- ---------- ------------------------------ ----------  


SPARE4  




4.18.4935                       306206  


XXXXXXX.12345.1.2.3  

  
  

prepared         P   00000001 00000000          0 10-FEB-05  
                   10-FEB-05  
  




SQL> rollback force '4.18.4935';



Rollback complete.



SQL> execute DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('4.18.4935');
BEGIN DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('4.18.4935'); END;


*


ERROR at line 1:

ORA-30019: Illegal rollback Segment operation in Automatic Undo mode
ORA-06512: at "SYS.DBMS_TRANSACTION", line 65
ORA-06512: at "SYS.DBMS_TRANSACTION", line 85
ORA-06512: at line 1 






SQL> select count(*) from tracking.tracked_lacosa_backend_request;
select count(*) from tracking.tracked_lacosa_backend_request

                              *




ERROR at line 1:


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




SQL> alter session set "_smu_debug_mode" = 4;



Session altered.



SQL> execute DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('4.18.4935');



PL/SQL procedure successfully completed.



SQL> select count(*) from tracking.tracked_lacosa_backend_request;
select count(*) from tracking.tracked_lacosa_backend_request

                              *




ERROR at line 1:


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




----*************  Notice here the transaction id changed ************* 





SQL> SELECT KTUXEUSN, KTUXESLT, KTUXESQN, /* Transaction ID */
  2  KTUXESTA Status,


  3  KTUXECFL Flags


  4  FROM x$ktuxe WHERE ktuxesta!='INACTIVE' AND ktuxeusn= 10;



  KTUXEUSN   KTUXESLT   KTUXESQN STATUS           FLAGS  


---------- ---------- ---------- ---------------- ------------------------ 
 
        10         15       4693 PREPARED         SCO|COL|REV|DEAD  





SQL> insert into pending_trans$ ( 


LOCAL_TRAN_ID,GLOBAL_TRAN_FMT,GLOBAL_ORACLE_ID,

  2  STATE,STATUS,SESSION_VECTOR,RECO_VECTOR,TYPE#,FAIL_TIME,RECO_TIME)

  3  values( '10.15.4693',


  4  306206, /* */


  5  'XXXXXXX.12345.1.2.3', /* These values can be used without any */
  6  'prepared','P', /* modification. Most of the values are */
  7  hextoraw( '00000001' ), /* constant. */
  8  hextoraw( '00000000' ), /* */


  9  0, sysdate, sysdate );



1 row created.



SQL> insert into pending_sessions$ values('10.15.4693',1,
  2  hextoraw('05004F003A1500000104'),'C',0,30258592,'',146);



1 row created.



SQL> commit;



Commit complete.



SQL> rollback force '10.15.4693';



Rollback complete.



SQL> execute DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('10.15.4693');



PL/SQL procedure successfully completed.



SQL> select count(*) from tracking.tracked_lacosa_backend_request;



  COUNT(*)  





     98697  



Yee Ha!



Tony






"Powell, Mark D" <mark.powell_at_eds.com> 


02/09/2005 02:13 PM



To


"'Tony.Adolph_at_o2.com'" <Tony.Adolph_at_o2.com>, oracle-l_at_freelists.org
cc



Subject


RE: lock held by in-doubt distributed transaction








Is tracking.tracked_lacosa_request a local table or remote?  If remote did 
you check the dba_pending* views on the remote system.  The bug I was 
thinking of showed entries in the pending views but your problem does not 
so it is likely a different bug.
 


I checked metalink for the ORA-01591error and got around 47 hits.  I 
glanced at a few but I did not see a perfect match.  I think you will need 
to open an iTAR and get help directly from Oracle.  Please let the board 
know how this is resolved.
 


HTH -- Mark D Powell --
 


-----Original Message-----


From: Tony.Adolph_at_o2.com [mailto:Tony.Adolph_at_o2.com]
Sent: Wednesday, February 09, 2005 3:37 AM
To: mark.powell_at_eds.com


Cc: oracle-l_at_freelists.org; oracle-l-bounce_at_freelists.org
Subject: RE: lock held by in-doubt distributed transaction




Hi Mark, 



Thanks for the feedback. I tried... 



SQL> alter session set "_smu_debug_mode" = 4; 



Session altered. 



SQL> exec dbms_transaction.purge_lost_db_entry('4.18.4935'); 
BEGIN dbms_transaction.purge_lost_db_entry('4.18.4935'); END; 



* 


ERROR at line 1: 

ORA-06510: PL/SQL: unhandled user-defined exception 
ORA-06512: at "SYS.DBMS_TRANSACTION", line 94 
ORA-06512: at line 1 





:-( 



Tony 




"Powell, Mark D" <mark.powell_at_eds.com> 


Sent by: oracle-l-bounce_at_freelists.org 


02/08/2005 06:48 PM 



Please respond to


mark.powell_at_eds.com





To


oracle-l_at_freelists.org 


cc



Subject


RE: lock held by in-doubt distributed transaction










I believe this is a bug.  The first solution I saw was support telling
people to convert their systems to using regular rollback segments but I
believe that you can issue ' alter session set "_smu_debug_mode" = 4; ' to
get around the problem.  I do not have the bug number so before you alter
your session you may want to check metalink on the hidden parameter.
HTH -- Mark D Powell --




-----Original Message-----


From: oracle-l-bounce_at_freelists.org


[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Tony.Adolph_at_o2.com
Sent: Tuesday, February 08, 2005 11:55 AM
To: oracle-l_at_freelists.org


Cc: Maxim.Demenko_at_o2.com


Subject: lock held by in-doubt distributed transaction




Hi folks,


One of our developers has just reported this error after a simple select:



select COUNT(*) FROM tracking.tracked_lacosa_request

                             *




ERROR at line 1:


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



So I looked for the in doubt transaction as follows:



select * from dba_2pc_neighbors;



and



select * from dba_2pc_pending;



but both returned no rows.



I tried the same query and also get the same error.  I bounced the 
database (immediate) and still get the error and all the time with the 
same transaction id 4.18.4935.  I got the developers to bounce their jboss 



but no joy. 



I tried dbms_transaction.purge_lost_db_entry:



SQL> exec dbms_transaction.purge_lost_db_entry('4.18.4935');
BEGIN dbms_transaction.purge_lost_db_entry('4.18.4935'); END;



*


ERROR at line 1:

ORA-30019: Illegal rollback Segment operation in Automatic Undo mode
ORA-06512: at "SYS.DBMS_TRANSACTION", line 65
ORA-06512: at "SYS.DBMS_TRANSACTION", line 85
ORA-06512: at line 1





Any ideas folks?



Cheers


Tony


--
http://www.freelists.org/webpage/oracle-l


--
http://www.freelists.org/webpage/oracle-l



--
http://www.freelists.org/webpage/oracle-l


Received on Thu Feb 10 2005 - 10:29:46 CST












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US