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

filts · 发表于 2015-11-8 08:22:37

http://blog.iyunv.com/langcai1981/article/details/
　　今天在查找一个普通表时，出现了上图所示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 a25
COLUMN DATABASE format a22
COLUMN global_name format a22
SELECT * 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,
   MIXED
FROM DBA_2PC_PENDING;
LOCAL_TRAN_ID       1.92.66874          prepared
1 为回滚段号
SELECT KTUXEUSN,
   KTUXESLT,
   KTUXESQN, /* Transaction ID */
   KTUXESTA STATUS,
   KTUXECFL Flags
FROM x$ktuxe
WHERE 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,
   MIXED
FROM DBA_2PC_PENDING;
查询无记录
SELECT local_tran_id, state
FROM dba_2pc_pending
WHERE local_tran_id = ' 1.92.66874 ';  -- 为空
SELECT KTUXEUSN,
   KTUXESLT,
   KTUXESQN, /* Transaction ID */
   KTUXESTA STATUS,
   KTUXECFL Flags
FROM x$ktuxe
WHERE 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.username
FROM v$transaction t, v$session s, v$sqlarea a
WHERE s.taddr = t.addr
AND a.address = s.prev_sql_addr
AND t.xidusn = 1
AND t.xidslot = 25
AND t.xidsqn = 589367;
如果 v$session 和 v$sqlarea 已经无法查到，那么我们还可以关联一些 dba_hist_* 试图进行查询。

还有一个找到一个解决办法:  网址为：  http://space.itpub.net/10173379/viewspace-626171

1.找到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;

　　

版权声明：本文为博主原创文章，未经博主允许不得转载。

账号		自动登录	找回密码
密码			立即注册

Centos6.5×64安装配置openmeetings3.0.3详

大疆运维招人啦，

C++ :try 语句块和异常处理

C++的多态

Red Hat RHCE 8 (EX294) Cert Guide

Java/C++ 区别：看完这一篇，就够用！

别再用过时库了！这 13 个顶级 C++ 库才是

[经验分享] ORA-01591:lock held by in-doubt distributed transacation 解析

浏览过的版块

扫码加入运维网微信交流群