ORA-01591: Lock held by in-doubt distributed transacation Parsing

Today, when looking for a common table, there is a ORA-01591 error prompt, found the problem point on the internet,

ORA-01591 errors are described as follows:

Error:  ORA 1591                                                              Text:   lock held by in-doubt distributed transaction <num >                  -------------------------------------------------------------------------------Cause:  An attempt was made to access a resource locked by a dead two-phase           commit transaction that is in prepared state.                         Action: Match the transaction number in the message with the GLOBAL_TRAN_ID           column of the DBA_2PC_PENDING table to determine the database link and        the state of the transaction.                                                 Attempt to repair network connections to the coordinator and commit           point, if necessary.                                                          If timely repair is not possible, contact the database administrator          at the commit point, if known, to resolve the pending transaction.

The above description is a description of the error in MOS. Of course, there may be many reasons for this error, and most of the time it is caused by network reasons, however, the error message returned when querying a single table is caused by distributed transaction queries. It is due to the exception information that occurs suddenly when programmers distribute operations to two databases. Such a distributed transaction can be understood as a complete transaction. Multiple operations are distributed in more than two databases. Only these operations are completed, the transaction is completed, otherwise the transaction will fail. In other words, if the transaction fails and involves operating table A, A ORA-01591 error occurs when other sessions Access Table. As for the specific reason why distributed transactions fail, it is necessary to analyze them in detail, which may be due to program problems or network problems. As mentioned above, most of the time it is caused by abnormal network interruptions, this error may occur if the company's network is unstable and packet loss occurs during the operation. ORA-01591: Lock held by in-doubt distributed transacation 9.8.1.003433, this error local_tran_id = 9.8.1.003433, the system determines that it is a distributed transaction, then we will query the dba_2pc_pending view to find the relevant reasons

The query results are as follows;

Local_tran_id state fail_time OS _user db_user ----------------------------------------------------------------------
9.8.1.003433           prepared         2012-06-06              MIS
Depending on the state of the distributed transaction, we need to take different methods for processing. Let's make a brief summary later. Here I will continue to describe how to handle this problem. Summary URL: Force '19. 3.5343485 '; execute dbms_transaction.purge_lost_db_entry ('19. 3.5343485 ');
Summary of Distributed Transaction Processing
+++ General processing steps +++ 1. identify the ID of the transaction: column global_tran_id format a25column database format a22column global_name format a22select * From global_name; select local_tran_id, global_tran_id, to_char (fail_time, 'dd-mon-yyyy hh24: MI: SS '), State, mixed from dba_2pc_pending; select local_tran_id, in_out, interface, database from dba_2pc_neighbors; 2. purge the transaction: Execute DBMS _ Transaction. purge_lost_db_entry ('<transaction_id>'); Commit; 3. confirm that the transaction has been purged: Select local_tran_id, global_tran_id, to_char (fail_time, 'dd-mon-yyyy hh24: MI: s'), State, mixed from dba_2pc_pending; select local_tran_id, in_out, interface, database from dba_2pc_neighbors; there are five states: collecting -- execute dbms_transaction.purge_lost_db_entry ('1. 10.255 '); prepared -- rollb Ack force tran_id/commit force tran_id; execute dbms_transaction.purge_lost_db_entry ('1. 10.255 '); committed -- execute dbms_transaction.purge_lost_db_entry ('1. 10.255 '); Forced commit -- execute dbms_transaction.purge_lost_db_entry ('1. 10.255 '); Forced rollback -- execute dbms_transaction.purge_lost_db_entry ('1. 10.255 '); ++ if a ORA-30019 error occurs, you can take the following method: ++ alter session set "_ smu_debug_mode" = 4; Execute dbms_transaction.purge_lost_db_entry ('1. 10.255 '); ======== case 1 There are still transaction records in the dba_2pc_pending table, but the actual transaction does not exist. Select local_tran_id, global_tran_id, to_char (fail_time, 'dd-mon-yyyy hh24: MI: ss'), State, mixedfrom dba_2pc_pending; local_tran_id 1.92.66874 prepared 1: Select ktuxeusn, ktuxeslt, ktuxesqn, /* transaction ID */ktuxesta status, ktuxecfl flagsfrom x $ ktuxewhere ktuxesta! = 'Inactive' and ktuxeusn = 1 The returned value is 0. If the status is prepared and there is no relevant information in the transaction table, we can only perform manual cleaning: ++ uses the following method for manual processing: ++ SET transaction use rollback segment system; delete from sys. pending_trans $ where local_tran_id = '1. 92.66874 '; delete from sys. pending_sessions $ where local_tran_id = '1. 92.66874 '; delete from sys. pending_sub_sessions $ where local_tran_id = '1. 92.66874 '; commit; ======= case 2 the distributed file cannot be found in the dba_2pc_pending table Transaction information, but it is actually the select local_tran_id, global_tran_id, to_char (fail_time, 'dd-mon-yyyy hh24: MI: ss'), State, mixedfrom dba_2pc_pending; select local_tran_id, statefrom into local_tran_id = 'hangzhou'; -- null select region, ktuxeslt, region,/* transaction ID */transaction status, ktuxecfl flagsfrom x $ ktuxewhere ktuxesta! = 'Inactive' and ktuxeusn = 1; query records ====== in this case, we cannot manually perform rollback or commit ======+++. We use the following method to manually clean up: +++ ++ alter system disable distributed recovery; insert into pending_trans $ (local_tran_id, global_tran_fmt, global_oracle_id, state, status, session_vector, reco_vector, type #, fail_time, reco_time) values ('hangzhou', 306206, 'hangzhou ', 'prepared', 'P', hextoraw ('2017 '), Hextoraw ('20140901'), 0, sysdate, sysdate); insert into pending_sessions $ values ('1.92.66874', 1, hextoraw ('05004f003a1500000104 '), 'C ', 0, 30258592, '', 146); Commit force '1.92.66874 '; ++ if the commit force still reports an error, continue to execute: ++ 1. delete from pending_trans $ where local_tran_id = '1. 92.66874 '; 2. delete from pending_sessions $ where local_tran_id = '1. 92.66874 '; 3. comm It; 4. alter system enable distributed recovery; 5. alter session set "_ smu_debug_mode" = 4; 6. exec dbms_transaction.purge_lost_db_entry ('1. 92.66874 ') ====== In addition, we can use the following SQL to capture the SQL statement that causes the distributed transaction to fail: ========+++ obtain local_tran_id ++++ select. SQL _text, S. osuser, S. usernamefrom v $ transaction T, V $ session S, V $ sqlarea awhere S. taddr = T. addrand. address = S. prev_ SQL _addrand T. xidusn = 1and T. xidslo T = 25and T. xidsqn = 589367; if v $ session and V $ sqlarea cannot be found, we can also associate some dba_hist _ * To try to query. Another solution is: http://space.itpub.net/10173#/viewspace-6261711.find tran_id select 'rollback force' | local_tran_id | ''' from sys. pending_trans $; 2. commit or rollback: rollback force local_tran_id; Commit force local_tran_id; 3. if the execution fails, the transaction record is forcibly deleted from the data dictionary: Set transaction use rollback segment system; delete from dba_2pc_pending where local_tran_id = local_tran_id; delete from pending_sessions $ where local_tran_id = local_tran_id; delete from pending_sub_sessions $ where local_tran_id = local_tran_id; commit; 4. execution failed. In another case, You can restart the Oracle service. In this case, the lock is automatically removed after the restart (because you are worried about deleting the data dictionary, therefore, I tried to restart the database once, provided that commit force local_tran_id is executed; restart after failure) 5. the program that generates the statement operation statement begin for R in (select local_tran_id from dba_2pc_pending t where T. state = 'collect') loop -- dbms_output.put_line ('commit force' '| r. local_tran_id | '''; '); dbms_output.put_line ('delete from dba_2pc_pending where local_tran_id = ''' | r. local_tran_id | '''; '); dbms_output.put_line ('delete from pending_sessions $ where local_tran_id = ''' | r. local_tran_id | '''; '); dbms_output.put_line ('delete from pending_sub_sessions $ where local_tran_id = ''' | r. local_tran_id | '''; '); End loop; dbms_output.put_line ('commit;'); end;