ORA-01591: lock held by in-doubt…

总结：from 老白<wbr><a href="http://www.oraclefans.cn/forum/showblog.jsp?rootid=7153">http://www.oraclefans.cn/forum/showblog.jsp?rootid=7153</a></wbr>

1、对于ORA-1591,一般来说是由于分布式事务失败引起的。强制提交或者回退分布式事务就可以解决问题

2、有些时候分布式事务自动的回退会失败，这样就会导致1591相关的表被锁住

3、这个时候分为两种情况，首先通过

<wbr><wbr><wbr><wbr> SELECT KTUXEUSN, KTUXESLT, KTUXESQN,<br><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr> KTUXESTA Status,<br><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr> KTUXECFL Flags<br><wbr><wbr><wbr> FROM x$ktuxe<br><wbr><wbr><wbr> WHERE ktuxesta!='INACTIVE'<br><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr> AND ktuxeusn in =108;</wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>

<wbr>确认事务的状态，如果显示是：<br><wbr> KTUXEUSN<wbr><wbr> KTUXESLT<wbr><wbr> KTUXESQN STATUS<wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr> FLAGS<wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><br> ---------- ---------- ---------- ---------------- ------------------------<wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><br><wbr><wbr><wbr><wbr><wbr><wbr> 108<wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr> 28<wbr><wbr><wbr><wbr><wbr> 46269 PREPARED<wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr> SCO|COL|REV|DEAD<wbr><br> 说明事务需要回退或者提交。</wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>

此时如果DBA_2PC_PENDING中有该记录，但是状态不是PREPARED，那么就

<wbr>UPDATE PENDING_TRANS$ SET<wbr><wbr><wbr><wbr><wbr><wbr><wbr> STATE='prepared',<br><wbr><wbr><wbr><wbr><wbr><wbr><wbr> STATUS ='P'<br> where local_tran_id='73.11.124822';<br> commit;<br></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>

否则：

alter system disable distributed recovery;
insert into pending_trans$ (
<wbr><wbr><wbr><wbr><wbr><wbr><wbr> LOCAL_TRAN_ID,<br><wbr><wbr><wbr><wbr><wbr><wbr><wbr> GLOBAL_TRAN_FMT,<br><wbr><wbr><wbr><wbr><wbr><wbr><wbr> GLOBAL_ORACLE_ID,<br><wbr><wbr><wbr><wbr><wbr><wbr><wbr> STATE,<br><wbr><wbr><wbr><wbr><wbr><wbr><wbr> STATUS,<br><wbr><wbr><wbr><wbr><wbr><wbr><wbr> SESSION_VECTOR,<br><wbr><wbr><wbr><wbr><wbr><wbr><wbr> RECO_VECTOR,<br><wbr><wbr><wbr><wbr><wbr><wbr><wbr> TYPE#,<br><wbr><wbr><wbr><wbr><wbr><wbr><wbr> FAIL_TIME,<br><wbr><wbr><wbr><wbr><wbr><wbr><wbr> RECO_TIME)<br><wbr><wbr><wbr> values( '108.28.46269',<br><wbr><wbr><wbr><wbr><wbr><wbr><wbr> 306206,<wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><br><wbr><wbr><wbr><wbr><wbr><wbr><wbr> 'XXXXXXX.12345.1.2.3',<wbr><wbr><br><wbr><wbr><wbr><wbr><wbr><wbr><wbr> 'prepared','P',<wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><br><wbr><wbr><wbr><wbr><wbr><wbr><wbr> hextoraw( '00000001' ),<wbr><br><wbr><wbr><wbr><wbr><wbr><wbr><wbr> hextoraw( '00000000' ),<wbr><br><wbr><wbr><wbr><wbr><wbr><wbr><wbr> 0, sysdate, sysdate );</wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>

<wbr><wbr><wbr> insert into pending_sessions$<br><wbr><wbr><wbr> values( '108.28.46269',<br><wbr><wbr><wbr><wbr><wbr><wbr><wbr> 1, hextoraw('05004F003A1500000104'),<br><wbr><wbr><wbr><wbr><wbr><wbr><wbr> 'C', 0, 30258592, '',<br><wbr><wbr><wbr><wbr><wbr><wbr><wbr> 146<br><wbr><wbr><wbr><wbr><wbr> );</wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>

<wbr><wbr><wbr> commit;</wbr></wbr></wbr>

插入相关记录。

然后进行ROLLBACK FORCE <wbr>'TX-ID'或者COMMIT<wbr> FORCE 'TX-ID'</wbr></wbr>

如果ROLLBACK 不成功，可以尝试COMMIT