ORA-01591:lock held by in-doubt distributed transacation 解析

今天在尋找一個普通表時，出現了所示ORA-01591的錯誤提示，在網上找了下問題點，

ORA-01591 錯誤問題說明如下：

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.

以上說明是mos關於該錯誤的一個描述，當然，引起該錯誤的原因可能有很多很多，很多時候是由於網路原因造成的，但是單表查詢所報的錯誤提示是由於分散式交易查詢時引起，是由於程式員在將操作分布到兩個資料庫時，突然發生的異常資訊。如此分散式交易，其實可以這樣理解，就是一個完整的事務，其中包含的多個操作分布在兩個以上的資料庫中，只有這些操作都全部完成了，該事務才算完成，不然該事務都將失敗。換句話說，如果該事務失敗了，其中涉及到動作表A，那麼當其他session訪問到表A時將出現ORA-01591錯誤。至於具體說為什麼分散式交易會失敗，那麼就要具體分析了，可能是程式本身的問題或者網路問題等等，正如上面所說的很多時候是由於網路異常中斷導致的，公司網路不穩定，在操作過程中丟包就有可能發生此錯誤。ORA-01591:lock held by in-doubt distributed transacation 9.8.1.003433，此錯誤的local_tran_id=9.8.1.003433，系統判斷是分布事務，那麼我們就查詢dba_2pc_pending視圖來找查相關原因了

查詢結果如下；

LOCAL_TRAN_ID          STATE            FAIL_TIME    OS_USER    DB_USER---------------------- ---------------- ------------ ---------- ----------
9.8.1.003433           prepared         2012-06-06              MIS
根據分布事務的狀態（state）不同，我們需要採取不同的方法進行處理，稍後進行一個簡單的總結，這裡我繼續描述如何處理該問題。總結網址為： http://www.killdb.com/2011/10/11/ora-01591-lock-held-by-in-doubt-distributed-transaction.html通過如下兩個小步驟進行處理：rollback force '19.3.5343485';execute dbms_transaction.purge_lost_db_entry('19.3.5343485');
關於分散式交易處理的簡單總結
++++++ 常規處理步驟 ++++++ 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:SS'),STATE, MIXED FROM DBA_2PC_PENDING;SELECT LOCAL_TRAN_ID, IN_OUT,INTERFACE, DATABASE FROM DBA_2PC_NEIGHBORS; 其中有如下五種state：  collecting   -- execute DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('1.10.255'）； prepared   -- rollback 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');  ++++++ 如果遇到ORA-30019錯誤，可以採取如下方式：++++++ ALTER SESSION SET "_smu_debug_mode" = 4;EXECUTE DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('1.10.255'); ====== 情況1 在dba_2pc_pending表中還有事務記錄，但是實際已經不存在該事務了 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 返回為0 如果當狀態為prepared，且事務表中也不存在相關資訊，那麼我們只能手工進行清理： ++++++ 使用如下方式進行手工處理：++++++ 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; ====== 情況2  在dba_2pc_pending表中無法查到分散式交易資訊，但是實際上卻是存在該分散式交易的 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 dba_2pc_pendingWHERE local_tran_id = ' 1.92.66874 ';  -- 為空白 SELECT KTUXEUSN,       KTUXESLT,       KTUXESQN, /* Transaction ID */       KTUXESTA STATUS,       KTUXECFL FlagsFROM x$ktuxeWHERE ktuxesta != 'INACTIVE'AND ktuxeusn = 1; 查詢有記錄 ====== 此種情況下，我們無法手工進行ROLLBACK或commit ====== ++++++ 我們用如下的方式手工清理：++++++ 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 (' 1.92.66874 ', 306206, ' XXXXXXX.12345.1.2.3 ', ' prepared ', ' P ', hextoraw(' 00000001 '), hextoraw(' 00000000 '), 0, sysdate, sysdate); INSERT INTO pending_sessions $VALUES (' 1.92.66874 ', 1, hextoraw(' 05004F003A1500000104 '), ' C ', 0, 30258592, '', 146); commit ;  commit   force ' 1.92.66874 ' ; ++++++ 此時如果commit force還是出現報錯，需要繼續執行：++++++ 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. commit ;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' ) ====== 另外我們還可以通過如下SQL來捕獲到導致分散式交易失敗的SQL：====== ++++++ 擷取local_tran_id ++++++ SELECT a.sql_text, s.osuser, s.usernameFROM v$transaction t, v$session s, v$sqlarea aWHERE s.taddr = t.addrAND a.address = s.prev_sql_addrAND t.xidusn = 1AND t.xidslot = 25AND t.xidsqn = 589367;  如果 v$session 和 v$sqlarea 已經無法查到，那麼我們還可以關聯一些 dba_hist_* 試圖進行查詢。還有一個找到一個解決辦法:  網址為：  http://space.itpub.net/10173379/viewspace-6261711.找到TRAN_ID  Select 'Rollback force '''||LOCAL_TRAN_ID||'''' from sys.pending_trans$;2.將其COMMIT或ROLLBACK:  rollback force LOCAL_TRAN_ID;  commit force LOCAL_TRAN_ID;  3.如果執行失敗,則強行從資料字典中刪除該交易記錄: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.執行失敗,還一種情況可以嘗試下重起一下Oracle服務,這種情況我遇到過一次，重新啟動後該鎖自動解除（因為對刪除資料字典比較擔心,所以胡亂試了一次重啟資料庫,前提是執行了commit force LOCAL_TRAN_ID;失敗之後重新啟動）5.產生語句動作陳述式的程式begin   for r in (select local_tran_id                from dba_2pc_pending t               where t.state = 'collecting') 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;