[20160608]自治事务引起死锁.txt

[20160608]自治事务引起死锁.txt

--链接http://www.itpub.net/thread-2060966-2-1.html上的讨论,很久以前也遇到过
--(http://blog.itpub.net/267265/viewspace-721262/),时间有点久远,自己重复测试看看:

1.环境:
SCOTT@test> @ &r/ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- ----------------------------------------------------------------
x86_64/Linux 2.4.xx            10.2.0.4.0     Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi

create table t (id number,name varchar2(10));
create unique index i_t_i on t(id);

CREATE  PROCEDURE test1( l_id number,l_name varchar2,flag VARCHAR2) AS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
    commit ;
    IF flag = 'INSERT' THEN
        insert into t values(l_id,l_name);
    END IF;
    IF flag = 'UPDATE' THEN
        update t set id=l_id,name=l_name where id=l_id;
    END IF;
    IF flag = 'DELETE' THEN
        delete from t where id=l_id;
    END IF;
    commit;
    dbms_output.put_line (flag);
END;
/

insert into t values(1,'a');
insert into t values(2,'b');
commit;

2.测试:
打开会话1,执行如下:
SCOTT@test> @ &r/spid
       SID    SERIAL# SPID       PID  P_SERIAL# C50
---------- ---------- ------ ------- ---------- --------------------------------------------------
       152      19213 17197       17        209 alter system kill session '152,19213' immediate;

SCOTT@test> set SERVEROUT on
SCOTT@test> update t set name='aaa' where id=1;
1 row updated.

SCOTT@test> exec test1(1,'A','UPDATE');
BEGIN test1(1,'A','UPDATE'); END;

*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
ORA-06512: at "SCOTT.TEST1", line 9
ORA-06512: at line 1

--检查跟踪文件:
DEADLOCK DETECTED ( ORA-00060 )
[Transaction Deadlock]
The following deadlock is not an ORACLE error. It is a
deadlock due to user error in the design of an application
or from issuing incorrect ad-hoc SQL. The following
information may aid in determining the deadlock:
Deadlock graph:
                       ---------Blocker(s)--------  ---------Waiter(s)---------
Resource Name          process session holds waits  process session holds waits
TX-0004001b-00000553        17     152     X             17     152           X
session 152: DID 0001-0011-0000F28B session 152: DID 0001-0011-0000F28B
Rows waited on:
Session 152: obj - rowid = 00013558 - AAATVYAAEAAAAGmAAA
  (dictionary objn - 79192, file - 4, block - 422, slot - 0)
Information on the OTHER waiting sessions:
End of information on OTHER waiting sessions.
Current SQL statement for this session:
UPDATE T SET ID=:B1 ,NAME=:B2 WHERE ID=:B1
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
0x76e908a8         9  procedure SCOTT.TEST1
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
0x74d27968         1  anonymous block
===================================================
PROCESS STATE
-------------
Process global information:
     process: 0x7ce63c10, call: 0x7cfab610, xact: 0x7b3209d0, curses: 0x7cf796f8, usrses: 0x7cf796f8
  ----------------------------------------
  SO: 0x7ce63c10, type: 2, owner: (nil), flag: INIT/-/-/0x00
  (process) Oracle pid=17, calls cur/top: 0x7cfab610/0x7cfa89b0, flag: (0) -
            int error: 0, call error: 0, sess error: 0, txn error 0
  (post info) last post received: 109 0 4
              last post received-location: kslpsr
              last process to post me: 7ce5f4e8 1 6
              last post sent: 0 0 24
              last post sent-location: ksasnd
              last process posted by me: 7ce5f4e8 1 6
    (latch info) wait_event=0 bits=0
    Process Group: DEFAULT, pseudo proc: 0x7cea6ac8
    O/S info: user: oracle, term: pts/4, ospid: 17197
    OSD pid info: Unix process pid: 17197, image: oracle@icaredg (TNS V1-V3)

--提示很明确,甚至行号也指出来了.看~~~

--没有看到zergduan提到的“Autonomous Transaction Frames”信息.估计是版本问题,11g才会出现.

select * from x$kglob where kglnaobj='TEST1' and kglnaown='SCOTT';

Record View
As of: 2016/6/8 9:34:56

ADDR:       00002B8ADBEB0AC8
INDX:       6215
INST_ID:    1
KGLHDADR:   0000000076E908A8
KGLHDPAR:   0000000076E908A8
~~~~~~~~~~~~~~~~~~~~~~~~~~~~
KGLHDCLT:   23
KGLNAOWN:   SCOTT
KGLNAOBJ:   TEST1
KGLFNOBJ:   TEST1
KGLNADLK:  
KGLNAHSH:   1942837727
KGLNAHSV:   df25314504e786643e240bf673cd59df
KGLNATIM:   2016/6/8 8:54:35
KGLNAPTM:  
KGLHDNSP:   1
KGLHDLMD:   1
KGLHDPMD:   0
KGLHDFLG:   33554432
KGLHDOBJ:   0000000077BDEE38
KGLHDLDC:   1
KGLHDIVC:   0
KGLHDEXC:   1
KGLHDLKC:   1
KGLHDKMK:   0
KGLHDDMK:   29
KGLHDAMK:   0
KGLOBFLG:   5
KGLOBSTA:   1
KGLOBTYP:   7
KGLOBHMK:   0
KGLOBHS0:   1675
KGLOBHS1:   0
KGLOBHS2:   36864
KGLOBHS3:   0
KGLOBHS4:   4096
KGLOBHS5:   0
KGLOBHS6:   0
KGLOBHS7:   0
KGLOBHD0:   0000000074C89A10
KGLOBHD1:   00
KGLOBHD2:   0000000077BDF150
KGLOBHD3:   00
KGLOBHD4:   000000006CFD22B8
KGLOBHD5:   00
KGLOBHD6:   00
KGLOBHD7:   00
KGLOBPC0:   0
KGLOBPC6:   0
KGLOBTP0:   00
KGLOBT00:   0
KGLOBT01:   0
KGLOBT02:   0
KGLOBT03:  
KGLOBT04:   0
KGLOBT05:   0
KGLOBT35:   0
KGLOBT06:   0
KGLOBT07:   0
KGLOBT08:   0
KGLOBT09:   0
KGLOBT10:   0
KGLOBT11:   0
KGLOBT12:   0
KGLOBT13:   0
KGLOBT14:   0
KGLOBT15:   0
KGLOBT16:   0
KGLOBT17:   0
KGLOBT18:   0
KGLOBT19:   0
KGLOBT20:   0
KGLOBT21:   0
KGLOBT22:   0
KGLOBT23:   0
KGLOBT24:   0
KGLOBT25:   0
KGLOBT26:   0
KGLOBT28:   0
KGLOBT29:   0
KGLOBT30:   0
KGLOBT31:   0
KGLOBT27:   0
KGLOBT32:   0
KGLOBT33:   0
KGLOBWAP:   0
KGLOBWCC:   0
KGLOBWCL:   0
KGLOBWUI:   0
KGLOBWDW:   0
KGLOBT42:   0
KGLOBT43:   0
KGLOBT44:   0
KGLOBT45:   0
KGLOBT46:   0
KGLOBT47:   0
KGLOBT49:   0
KGLOBT50:   0
KGLOBTL0:   0
KGLOBTL1:   0
KGLOBTS0:  
KGLOBTS1:  
KGLOBTN0:  
KGLOBTN1:  
KGLOBTN2:  
KGLOBTN3:  
KGLOBTN4:  
KGLOBTN5:  
KGLOBTS2:  
KGLOBTS3:  
KGLOBTS5:  
KGLOBTT0:  
KGLOBCCE:  
KGLOBCCEH:  0
KGLOBCLA:  
KGLOBCLC:   0
KGLOBCCC:   0
KGLOBTS4:  
KGLOBCBCA: 
KGLOBT48:   0
KGLOBDSO:   0
KGLOBDEX:   0
KGLOBDPX:   0
KGLOBDLD:   0
KGLOBDIV:   0
KGLOBDPS:   0
KGLOBDDR:   0
KGLOBDDW:   0
KGLOBDBF:   0
KGLOBDRO:   0
KGLOBDCP:   0
KGLOBDEL:   0
KGLOBDFT:   0
KGLOBDEF:   0
KGLOBDUI:   0
KGLOBDCL:   0
KGLOBDAP:   0
KGLOBDCC:   0
KGLOBDPL:   0
KGLOBDJV:   0

SYS@test> @ &r/sharepool/shp4 0000000074D27968 1
old  17:  WHERE kglobt03 = '&1'  or kglhdpar='&1' or kglhdadr='&1' or KGLNAHSH= &2
new  17:  WHERE kglobt03 = '0000000074D27968'  or kglhdpar='0000000074D27968' or kglhdadr='0000000074D27968' or KGLNAHSH= 1
TEXT           KGLHDADR         KGLHDPAR         C40                                      KGLOBHD0         KGLOBHD6           KGLOBHS0   KGLOBHS6   KGLOBT16   N0_6_16        N20   KGLNAHSH KGLOBT03        KGLOBT09
-------------- ---------------- ---------------- ---------------------------------------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ----------
子游标句柄地址 0000000074D27968 0000000076D7F590 BEGIN test1(1,'A','UPDATE'); END;        0000000076DCEA10 000000006F03D0C8       3664      12400       1804     17868      17868 4157711603 8y01d1rvx397m          0

--shp4.sqlj脚本如下:
$ cat shp4.sql
column N0_6_16 format 99999999
SELECT DECODE (kglhdadr,
               kglhdpar, '父游标句柄地址',
               '子游标句柄地址')
          text,
       kglhdadr,
       kglhdpar,
       substr(kglnaobj,1,40) c40,
       kglobhd0,
       kglobhd6,
       kglobhs0,kglobhs6,kglobt16,
       kglobhs0+kglobhs6+kglobt16 N0_6_16,
           kglobhs0+kglobhs1+kglobhs2+kglobhs3+kglobhs4+kglobhs5+kglobhs6+kglobt16 N20,
           kglnahsh,
           kglobt03 ,
           kglobt09
  FROM x$kglob
WHERE kglobt03 = '&1'  or kglhdpar='&1' or kglhdadr='&1' or KGLNAHSH= &2;

--通过以上信息应该可以定位问题语句,以及在存储过程的位置.