Oracle解决异布lock的方法!
通常由于网络的不稳定或则数据库的bug,在使用dblink时产生了异步lock,下面就谈谈异步lock的解决方法:
1)查询 dba_2pc_pending ,确定异步lock当前的状态。
select local_tran_id, global_tran_id, state, mix, host, commit# from dba_2pc_pending;
LOCAL_TRAN_ID|GLOBAL_TRAN_ID |STATE |MIX|HOST |COMMIT#
-------------|----------------------|---------|---|----------|-------
1.10.255 |V817REP.BE.ORACLE.COM.|committed|no |BE-ORACLE-|202241
|89f6eafb.1.10.255 | | |NT\bel449 |
此时通过state=committed, 表示此session已提交,只是在提交后,接受不到global session的transaction信息了,所以产生异步lock,此时对一般不造成table的lock。
通过调用 execute DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('1.10.255'); 可解决此问题。
当然state还有以下几种状态:
collecting:在收集数据过程中,产生异常
解决方法: execute DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('1.10.255');
prepared: 在接受到异步commit/rollback指令前, 产生异常
解决方法: rollback force tran_id/commit force tran_id; -- 可根据异步transaction的状况决定使用方法。
forced rollback:在使用rollback force出现
解决方法: execute DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('1.10.255');
forced commit:在使用commit force出现
解决方法: execute DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('1.10.255');
** NOTE1: If using Oracle 9i or later and DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY fails with
ORA-30019: Illegal rollback Segment operation in Automatic Undo mode, use the following workaround
SQL> alter session set "_smu_debug_mode" = 4;
SQL>execute DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('local_tran_id');
测试 :
模拟的是分布式事务在2pc提交过程产生in-doubt 的问题解决方式
环境:orcl(ORCL01.TEST.COM),solo(ORCL02.TEST.COM) version 10.2.0.3
15:45:29 sys@SOLO> drop public database link solo_link;
数据库链接已删除。
15:45:57 sys@SOLO> create public database link solo_link connect to scott identified by scott using 'solo';
数据库链接已创建。
15:46:18 sys@SOLO> update emp@solo_link set sal=sal*2 ;
15:46:38 sys@SOLO> commit;
如果这个时候solo出现网络故障。orcl执行commit 被挂起,这个时候如果网络恢复则问题会自动解决。
而这时如果却执行了一个shutdown abort
再启动之后,这个时候查询scott.emp表会报错:
ERROR at line 1:
ORA-01591: lock held by in-doubt distributed transaction 5.32.251
这个时候查询dba_2pc_pending数据字典会看到5.32.251 的state是prepared
并且同过查询dba_2pc_neighbors知道该事务对应的database是pu_link.test.com对应的数据库
SQL> col local_tran_id format a13
SQL> col global_tran_id format a30
SQL> col state format a8
SQL> col mixed format a3
SQL> col host format a10
SQL> col commit# format a10
SQL> select local_tran_id, global_tran_id, state, mixed, host, commit#
2 from dba_2pc_pending;
LOCAL_TRAN_ID GLOBAL_TRAN_ID STATE MIX HOST COMMIT#
-------------------- ------------------------------------------ -------- --- ---------- ----------
5.32.251 ORCL.TEST.COM.8705ca3e.5.32.251 prepared no dg1 498537
SQL> col local_tran_id format a13
SQL> col in_out format a6
SQL> col database format a25
SQL> col dbuser_owner format a15
SQL> col interface format a3
SQL> select local_tran_id, in_out, database, dbuser_owner, interface
2 from dba_2pc_neighbors;
LOCAL_TRAN_ID IN_OUT DATABASE DBUSER_OWNER INT
-------------------- --------- ------------------------- ------------------ ---
5.32.251 in SYS N
5.32.251 out PU_LINK.TEST.COM SYS C
这时候就需要使用手动提交或回滚 commit或者rollback
根据state列的值prepared我们知道,orcl是prepared阶段,则solo肯定不能到commit阶段.
为了事务的一致性最好 rollback force '5.32.251';
select local_tran_id, global_tran_id, state, mixed, host, commit#
2 from dba_2pc_pending;
LOCAL_TRAN_ID GLOBAL_TRAN_ID STATE MIX HOST COMMIT#
------------- ------------------------------------------- ----------- --- ---------- ----------
5.32.251 ORCL01.TEST.COM.8705ca3e.5.32. forced rollback no dg1
251
DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('5.32.251');
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/92530/viewspace-610987/,如需转载,请注明出处,否则将追究法律责任。