Oracle数据库数据恢复、性能优化

找回密码
注册
搜索
热搜: 活动 交友 discuz
Oracle数据库数据恢复、性能优化»论坛 › Oracle › Oracle数据库管理 › 如何查XA transaction 的session
发新帖
hwqhx

91

积分

0

好友

3

主题

如何查XA transaction 的session

1#
发表于 2012-4-28 10:44:38 | 查看: 7659| 回复: 2
如何查到XA transaction 的session 以及 语句？
3#
发表于 2012-4-28 14:42:56
ODM TEST:

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production



SQL> conn maclean/maclean
Connected.
SQL>


SQL> select * from employees where EMPLOYEE_ID=100;

EMPLOYEE_ID FIRST_NAME           LAST_NAME                 EMAIL                     PHONE_NUMBER         HIRE_DATE JOB_ID         SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
----------- -------------------- ------------------------- ------------------------- -------------------- --------- ---------- ---------- -------------- ---------- -------------
        100                      FDSFS                     ZC                                             28-APR-12 ST_MAN


启动一个XA transaction


SQL> REM Session 1 starts a transaction and does some work.
SQL> DECLARE
  2    rc  PLS_INTEGER;
  3    oer PLS_INTEGER;
  4    xae EXCEPTION;
  5  BEGIN
  6    rc  := DBMS_XA.XA_START(DBMS_XA_XID(123), DBMS_XA.TMNOFLAGS);
  7  
  8    IF rc!=DBMS_XA.XA_OK THEN
  9      oer := DBMS_XA.XA_GETLASTOER();
10      DBMS_OUTPUT.PUT_LINE('ORA-' || oer || ' occurred, XA_START failed');
11      RAISE xae;
12    ELSE DBMS_OUTPUT.PUT_LINE('XA_START(new xid=123)     OK');
13    END IF;
14  
15    UPDATE employees SET salary=salary*1.1 WHERE employee_id = 100;
16    rc  := DBMS_XA.XA_END(DBMS_XA_XID(123), DBMS_XA.TMSUSPEND);
17  
18    IF rc!=DBMS_XA.XA_OK THEN
19      oer := DBMS_XA.XA_GETLASTOER();
20      DBMS_OUTPUT.PUT_LINE('ORA-' || oer || ' occurred, XA_END failed');
21      RAISE xae;
22    ELSE DBMS_OUTPUT.PUT_LINE('XA_END(suspend xid=123)   OK');
23    END IF;
24  
25    EXCEPTION
26      WHEN OTHERS THEN
27        DBMS_OUTPUT.PUT_LINE
28         ('XA error('||rc||') occurred, rolling back the transaction ...');
29        rc := DBMS_XA.XA_END(DBMS_XA_XID(123), DBMS_XA.TMSUCCESS);
30        rc := DBMS_XA.XA_ROLLBACK(DBMS_XA_XID(123));
31  
32        IF rc != DBMS_XA.XA_OK THEN
33          oer := DBMS_XA.XA_GETLASTOER();
34          DBMS_OUTPUT.PUT_LINE('XA-'||rc||', ORA-' || oer ||
35           ' XA_ROLLBACK does not return XA_OK');
36          raise_application_error(-20001, 'ORA-'||oer||
37           ' error in rolling back a failed transaction');
38        END IF;
39  
40        raise_application_error(-20002, 'ORA-'||oer||
41         ' error in transaction processing, transaction rolled back');
42  END;
43  /

PL/SQL procedure successfully completed.

SQL> SHOW ERRORS
No errors.
SQL> DISCONNECT
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> conn maclean/maclean
Connected.
SQL> REM Session 2 resumes the transaction and does some work.
SQL> DECLARE
  2    rc  PLS_INTEGER;
  3    oer PLS_INTEGER;
  4    s   NUMBER;
  5    xae EXCEPTION;
  6  BEGIN
  7    rc  := DBMS_XA.XA_START(DBMS_XA_XID(123), DBMS_XA.TMRESUME);
  8  
  9    IF rc!=DBMS_XA.XA_OK THEN
10      oer := DBMS_XA.XA_GETLASTOER();
11      DBMS_OUTPUT.PUT_LINE('ORA-' || oer || ' occurred, xa_start failed');
12      RAISE xae;
13    ELSE DBMS_OUTPUT.PUT_LINE('XA_START(resume xid=123)  OK');
14    END IF;
15  
16    SELECT salary INTO s FROM employees WHERE employee_id = 100;
17    DBMS_OUTPUT.PUT_LINE('employee_id = 100, salary = ' || s);
18    rc  := DBMS_XA.XA_END(DBMS_XA_XID(123), DBMS_XA.TMSUCCESS);
19  
20    IF rc!=DBMS_XA.XA_OK THEN
21      oer := DBMS_XA.XA_GETLASTOER();
22      DBMS_OUTPUT.PUT_LINE('ORA-' || oer || ' occurred, XA_END failed');
23      RAISE xae;
24    ELSE DBMS_OUTPUT.PUT_LINE('XA_END(detach xid=123)    OK');
25    END IF;
26  
27    EXCEPTION
28      WHEN OTHERS THEN
29        DBMS_OUTPUT.PUT_LINE
30         ('XA error('||rc||') occurred, rolling back the transaction ...');
31        rc := DBMS_XA.XA_END(DBMS_XA_XID(123), DBMS_XA.TMSUCCESS);
32        rc := DBMS_XA.XA_ROLLBACK(DBMS_XA_XID(123));
33  
34        IF rc != DBMS_XA.XA_OK THEN
35          oer := DBMS_XA.XA_GETLASTOER();
36          DBMS_OUTPUT.PUT_LINE('XA-'||rc||', ORA-' || oer ||
37           ' XA_ROLLBACK does not return XA_OK');
38          raise_application_error(-20001, 'ORA-'||oer||
39           ' error in rolling back a failed transaction');
40        END IF;
41  
42        raise_application_error(-20002, 'ORA-'||oer||
43         ' error in transaction processing, transaction rolled back');
44  END;
45  /

PL/SQL procedure successfully completed.

SQL> SHOW ERRORS
No errors.
SQL> DISCONNECT




SQL> select * from v$lock where type in ('TM','TX');

no rows selected


SQL> connect / as sysdba

Connected.
SQL> SQL> set pagesize 1000
SQL> prompt +++ In memory transaction +++
+++ In memory transaction +++
SQL> select /*+ ORDERED */
  2  '----------------------------------------'||'
  3  Curent Time : '|| substr(to_char(sysdate,' HH24.MI.SS'),1,22) ||'
  4  '||'TX start_time: '||t.KTCXBSTM||'
  5  '||'FORMATID: '||g.K2GTIFMT ||'
  6  '||'GTXID: '||g.K2GTITID_EXT ||'
  7  '||'Branch: '||g.K2GTIBID ||'
  8  Local_Tran_Id ='||substr(t.KXIDUSN||'.'||t.kXIDSLT||'.'||t.kXIDSQN,1,15)||'
  9  '||'KTUXESTA='|| x.KTUXESTA ||'
10  '||'KTUXEDFL='|| x.KTUXECFL ||'
11  Lock_Info: ID1: ' || ((t.kXIDUSN*64*1024)+ t.kXIDSLT)
12  ||' ID2: '|| t.kXIDSQN
13  XA_transaction_INFO
14  from x$k2gte g, x$ktcxb t, x$ktuxe x
15  where g.K2GTDXCB =t.ktcxbxba and
16  x.KTUXEUSN = t.KXIDUSN(+) and
17  x.KTUXESLT = t.kXIDSLT(+) and
18  x.KTUXESQN =t.kXIDSQN(+);

XA_TRANSACTION_INFO
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------
Curent Time :  02.40.42
TX start_time: 04/28/12 02:40:03
FORMATID: 203348753
GTXID: 0000007B
Branch: 00000000000000000000000000000001
Local_Tran_Id =8.17.241
KTUXESTA=ACTIVE
KTUXEDFL=NONE
Lock_Info: ID1: 524305 ID2: 241


SQL>
SQL> prompt +++ Timed out, prepared XA transactions +++
+++ Timed out, prepared XA transactions +++
SQL> select global_tran_fmt, global_foreign_id, branch_id,state,
  2  tran.local_tran_id
  3  from sys.pending_trans$ tran, sys.pending_sessions$ sess
  4  where tran.local_tran_id = sess.local_tran_id
  5  and tran.state = 'prepared'
  6  and dbms_utility.is_bit_set(tran.session_vector, sess.session_id)=1;

no rows selected




Curent Time :  02.40.42
TX start_time: 04/28/12 02:40:03
FORMATID: 203348753
GTXID: 0000007B
Branch: 00000000000000000000000000000001
Local_Tran_Id =8.17.241
KTUXESTA=ACTIVE
KTUXEDFL=NONE
Lock_Info: ID1: 524305 ID2: 241

回复 只看该作者 道具 举报

2#
发表于 2012-4-28 14:36:46
1.
首先不建议 使用 XA Transaction 特性， 极少有事务处理需要用到 XA transaction

2.
10g 以后 V$LOCK 不在反应  XA Transaction 的相关信息 ， 可以利用以下脚本查询  XA Transaction

After upgrade to 10.2 v$lock table only shows a single TM lock entry for
a XA transaction waiting on a lock from another XA transaction. Note both
sessions are not detached, they are still attached XA sessions. The 9.2
v$lock shows 2 TM and 2 TX locks which makes it easily to figure out the
holder and blocker sesssions.

  1. connect / as sysdba

  3. set pagesize 1000
  4. prompt +++ In memory transaction +++
  5. select /*+ ORDERED */
  6. '----------------------------------------'||'
  7. Curent Time : '|| substr(to_char(sysdate,' HH24.MI.SS'),1,22) ||'
  8. '||'TX start_time: '||t.KTCXBSTM||'
  9. '||'FORMATID: '||g.K2GTIFMT ||'
  10. '||'GTXID: '||g.K2GTITID_EXT ||'
  11. '||'Branch: '||g.K2GTIBID ||'
  12. Local_Tran_Id ='||substr(t.KXIDUSN||'.'||t.kXIDSLT||'.'||t.kXIDSQN,1,15)||'
  13. '||'KTUXESTA='|| x.KTUXESTA ||'
  14. '||'KTUXEDFL='|| x.KTUXECFL ||'
  15. Lock_Info: ID1: ' || ((t.kXIDUSN*64*1024)+ t.kXIDSLT)
  16. ||' ID2: '|| t.kXIDSQN
  17. XA_transaction_INFO
  18. from x$k2gte g, x$ktcxb t, x$ktuxe x
  19. where g.K2GTDXCB =t.ktcxbxba and
  20. x.KTUXEUSN = t.KXIDUSN(+) and
  21. x.KTUXESLT = t.kXIDSLT(+) and
  22. x.KTUXESQN =t.kXIDSQN(+);

  24. prompt +++ Timed out, prepared XA transactions +++
  25. select global_tran_fmt, global_foreign_id, branch_id,state,
  26. tran.local_tran_id
  27. from sys.pending_trans$ tran, sys.pending_sessions$ sess
  28. where tran.local_tran_id = sess.local_tran_id
  29. and tran.state = 'prepared'
  30. and dbms_utility.is_bit_set(tran.session_vector, sess.session_id)=1;
复制代码

回复 只看该作者 道具 举报

返回列表
高级模式
B Color Image Link Quote Code Smilies
您需要登录后才可以回帖 登录 | 注册

QQ|手机版|Archiver|Oracle数据库数据恢复、性能优化

GMT+8, 2024-11-15 15:25 , Processed in 0.051025 second(s), 22 queries .

Powered by Discuz! X2.5

© 2001-2012 Comsenz Inc.

回顶部
TEL/電話+86 13764045638
Email service@parnassusdata.com
QQ 47079569