Lock held by distributed in doubt transactions for some transaction id

Friday, August 13, 2010



If you will get an error like this "locked held by distributed in doubt transactions for some transaction id". Please follow the following steps to recover from locking.

  • Try to find out the locked transaction id from the server error logs.
  • Execute select * from dba_2pc_pending in DB console. You should be able to see the particular locked transaction Id under "local_tran_id" column, and "state" for this should be PREPARED in dba_2pc_pending table.
  • Login to the database as sysdba. Execute the following command - sqlplus './as sysdba' rollback force "locked transaction id" Now if you will check dba_2pc_pending table again, the "state" should be changed to FORCED ROLLBACK for the transaction id.
  • Now you should not get the above error any more. You can also execute commit force "locked transaction id" to avoid this error.

0 comments:

Post a Comment

Followers

 

2009 ·Techy Freak by TNB