現場報有一個功能走不下去,後台日志報錯:java.sql.SQLException: ORA-01591: 鎖被未決分布式事務處理 657.7.39336 持有。
解決方案:
rollback force '657.7.39336';--執行可能會比較慢
執行完成後,查詢DBA_2PC_PENDING,
select * from DBA_2PC_PENDING s where s.local_tran_id='657.7.39336';
657.7.39336 SP4GD.a6dfea73.657.7.39336forced rollback
no 2015-6-17 5:28:05 2015-6-17 10:44:33 2015-6-17 5:28:05 oracle UNKNOWN SCDB02 LCA_ZC 14456764049772
或者
delete from sys.pending_trans$ where local_tran_id = '657.7.39336';
delete from sys.pending_sessions$ where local_tran_id = '657.7.39336';
delete from sys.pending_sub_sessions$ where local_tran_id ='657.7.39336';
commit;
Commit force '657.7.39336'
exec dbms_transaction.purge_lost_db_entry('657.7.39336');
DBA_2PC_PENDING describes
distributed transactions awaiting recovery.描述等待恢復的分布式事務。
LOCAL_TRAN_ID String of form: n.n.n; n is a number
GLOBAL_TRAN_ID Globally unique transaction ID
STATE Collecting, prepared, committed, forced commit, or forced rollback
MIXED YES indicates part of the transaction committed and part rolled back
ADVICE C for commit, R for rollback, else NULL
TRAN_COMMENT Text for commit work comment text
FAIL_TIME Value of SYSDATE when the row was inserted (transaction or system recovery)
FORCE_TIME Time of manual force decision (null if not forced locally)
RETRY_TIME Time automatic recovery (RECO) last tried to recover the transaction
OS_USER Operating system-specific name for the end-user
OS_TERMINAL Operating system-specific name for the end-user terminal
HOST Name of the host machine for the end-user
DB_USER Oracle user name of the end-user at the topmost database
COMMIT# Global commit number for committed transactions
這個錯誤是什麼意思呢?
[oracle@standby ~]$ oerr ora 01591
01591, 00000, "lock held by in-doubt distributed transaction %s"
// *Cause: Trying to access resource that is locked by a dead two-phase commit
// transaction that is in prepared state.
// *Action: DBA should query the pending_trans$ and related tables, and attempt
// to repair network connection(s) to coordinator and commit point.
// If timely repair is not possible, DBA should contact DBA at commit
// point if known or end user for correct outcome, or use heuristic
// default if given to issue a heuristic commit or abort command to
// finalize the local portion of the distributed transaction.
下面簡單介紹一下分布式事務。
分布式事務,簡單來說,是指一個事務在本地和遠程執行,本地需要等待確認遠程的事務結束後,進行下一步本地的操作。如通過dblink update遠程數據庫的一行記錄,如果在執行過程中網絡異常,或者其他事件導致本地數據庫無法得知遠程數據庫的執行情況,此時就會發生in doublt的報錯。此時需要dba介入,且需要分多種情況進行處理。
分布式事務的Two-Phase Commit機制,會經歷3個階段:
1.PREPARE PHASE:
1.1 決定哪個數據庫為commit point site。(注,參數文件中commit_point_strength值高的那個數據庫為commit point site)
1.2 全局協調者(Global Coordinator)要求所有的點(除commit point site外)做好commit或者rollback的准備。此時,對分布式事務的表加鎖。
1.3 所有分布式事務的節點將它的scn告知全局協調者。
1.4 全局協調者取各個點的最大的scn作為分布式事務的scn。
至此,所有的點都完成了准備工作,我們開始進入COMMIT PHASE階段,此時除commit point site點外所有點的事務均為in doubt狀態,直到COMMIT PHASE階段結束。
2.COMMIT PHASE:
2.1 Global Coordinator將最大scn傳到commit point site,要求其commit。
2.2 commit point嘗試commit或者rollback。分布式事務鎖釋放。
2.3 commit point通知Global Coordinator已經commit。
2.4 Global Coordinator通知分布式事務的所有點進行commit。