[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;
--通过以上信息应该可以定位问题语句,以及在存储过程的位置.
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-2117246/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/267265/viewspace-2117246/