oracle 死锁日志位置,高手请进，帮我分析一下日志（死锁）

数据库应用引起死锁，很频繁，是一条基于查询的插入sql语句引起的，从Global Wait-For-Graph(WFG)上看是锁类型是 3(共享锁)

看不懂相关的 ****_lmd0_2277812.trc 文件，不知道是什么资源引起阻塞，请高人帮我分析一下，谢谢啦~~~

user session for deadlock lock 7000003f23b1c30

pid=70 serial=1146 audsid=78030182 user: 41/SGYD

O/S info: user: weblogic, term: , ospid: 1234, machine: sgyxapp01

program:

Current SQL Statement:

INSERT INTO DF_MONEY_FILES (USER_NO, GROUP_NO, MON, MON_SN, SN, WRITE_SECT_NO, POWER_NO, TRANS_GROUP, PRICE_CODE, TS_FLAG, ELEC_TYPE_CODE, TRADE_TYPE_CODE, MS_MODE, ADD_TAXFLAG, STATUS, USER_TYPE1, LINE_CODE, TRANS_NO, RELA_USER_NO, BUSINESS_PLACE_CODE, HAVE_EXT, USER_MS_TYPE) SELECT B.USER_NO, B.GROUP_NO, C.MON, C.MON_SN, C.SN, C.WRITE_SECT_NO, NVL(B.POWER_NO,B.POWER_GROUP_NO), B.TRANS_GROUP, NVL(B.PRICE_CODE,:B1 ), NVL(B.TS_FLAG, '0'), B.ELEC_TYPE_CODE, B.TRADE_TYPE_CODE, B.MS_MODE, NVL(B.ADD_TAXFLAG, '0'), 'I', C.USER_TYPE1, (SELECT MAX(D.LINE_CODE) FROM POWER_FILE D WHERE D.USER_NO = B.USER_NO AND (D.POWER_NO = B.POWER_NO OR D.POWER_GROUP_NO = B.POWER_GROUP_NO)), NVL((SELECT MAX(D.TRANS_NO) FROM POWER_FILE D WHERE D.USER_NO = B.USER_NO AND (D.POWER_NO = B.POWER_NO OR D.POWER_GROUP_NO = B.POWER_GROUP_NO)),B.TRANS_NO), C.RELA_USER_NO, C.BUSINESS_PLACE_CODE, '0', B.USER_MS_TYPE FROM JL_POINT_FILE B, DF_TMP_USER_SELECT C WHERE B.USER_NO = C.USER_NO

user session for deadlock lock 7000003f23b16d8

pid=77 serial=18542 audsid=78030184 user: 41/SGYD

O/S info: user: weblogic, term: , ospid: 1234, machine: sgyxapp01

program:

Current SQL Statement:

INSERT INTO DF_MONEY_FILES (USER_NO, GROUP_NO, MON, MON_SN, SN, WRITE_SECT_NO, POWER_NO, TRANS_GROUP, PRICE_CODE, TS_FLAG, ELEC_TYPE_CODE, TRADE_TYPE_CODE, MS_MODE, ADD_TAXFLAG, STATUS, USER_TYPE1, LINE_CODE, TRANS_NO, RELA_USER_NO, BUSINESS_PLACE_CODE, HAVE_EXT, USER_MS_TYPE) SELECT B.USER_NO, B.GROUP_NO, C.MON, C.MON_SN, C.SN, C.WRITE_SECT_NO, NVL(B.POWER_NO,B.POWER_GROUP_NO), B.TRANS_GROUP, NVL(B.PRICE_CODE,:B1 ), NVL(B.TS_FLAG, '0'), B.ELEC_TYPE_CODE, B.TRADE_TYPE_CODE, B.MS_MODE, NVL(B.ADD_TAXFLAG, '0'), 'I', C.USER_TYPE1, (SELECT MAX(D.LINE_CODE) FROM POWER_FILE D WHERE D.USER_NO = B.USER_NO AND (D.POWER_NO = B.POWER_NO OR D.POWER_GROUP_NO = B.POWER_GROUP_NO)), NVL((SELECT MAX(D.TRANS_NO) FROM POWER_FILE D WHERE D.USER_NO = B.USER_NO AND (D.POWER_NO = B.POWER_NO OR D.POWER_GROUP_NO = B.POWER_GROUP_NO)),B.TRANS_NO), C.RELA_USER_NO, C.BUSINESS_PLACE_CODE, '0', B.USER_MS_TYPE FROM JL_POINT_FILE B, DF_TMP_USER_SELECT C WHERE B.USER_NO = C.USER_NO

user session for deadlock lock 7000003f37aa070

pid=77 serial=18542 audsid=78030184 user: 41/SGYD

O/S info: user: weblogic, term: , ospid: 1234, machine: sgyxapp01

program:

Current SQL Statement:

INSERT INTO DF_MONEY_FILES (USER_NO, GROUP_NO, MON, MON_SN, SN, WRITE_SECT_NO, POWER_NO, TRANS_GROUP, PRICE_CODE, TS_FLAG, ELEC_TYPE_CODE, TRADE_TYPE_CODE, MS_MODE, ADD_TAXFLAG, STATUS, USER_TYPE1, LINE_CODE, TRANS_NO, RELA_USER_NO, BUSINESS_PLACE_CODE, HAVE_EXT, USER_MS_TYPE) SELECT B.USER_NO, B.GROUP_NO, C.MON, C.MON_SN, C.SN, C.WRITE_SECT_NO, NVL(B.POWER_NO,B.POWER_GROUP_NO), B.TRANS_GROUP, NVL(B.PRICE_CODE,:B1 ), NVL(B.TS_FLAG, '0'), B.ELEC_TYPE_CODE, B.TRADE_TYPE_CODE, B.MS_MODE, NVL(B.ADD_TAXFLAG, '0'), 'I', C.USER_TYPE1, (SELECT MAX(D.LINE_CODE) FROM POWER_FILE D WHERE D.USER_NO = B.USER_NO AND (D.POWER_NO = B.POWER_NO OR D.POWER_GROUP_NO = B.POWER_GROUP_NO)), NVL((SELECT MAX(D.TRANS_NO) FROM POWER_FILE D WHERE D.USER_NO = B.USER_NO AND (D.POWER_NO = B.POWER_NO OR D.POWER_GROUP_NO = B.POWER_GROUP_NO)),B.TRANS_NO), C.RELA_USER_NO, C.BUSINESS_PLACE_CODE, '0', B.USER_MS_TYPE FROM JL_POINT_FILE B, DF_TMP_USER_SELECT C WHERE B.USER_NO = C.USER_NO

user session for deadlock lock 7000003f37a9c80

pid=70 serial=1146 audsid=78030182 user: 41/SGYD

O/S info: user: weblogic, term: , ospid: 1234, machine: sgyxapp01

program:

Current SQL Statement:

INSERT INTO DF_MONEY_FILES (USER_NO, GROUP_NO, MON, MON_SN, SN, WRITE_SECT_NO, POWER_NO, TRANS_GROUP, PRICE_CODE, TS_FLAG, ELEC_TYPE_CODE, TRADE_TYPE_CODE, MS_MODE, ADD_TAXFLAG, STATUS, USER_TYPE1, LINE_CODE, TRANS_NO, RELA_USER_NO, BUSINESS_PLACE_CODE, HAVE_EXT, USER_MS_TYPE) SELECT B.USER_NO, B.GROUP_NO, C.MON, C.MON_SN, C.SN, C.WRITE_SECT_NO, NVL(B.POWER_NO,B.POWER_GROUP_NO), B.TRANS_GROUP, NVL(B.PRICE_CODE,:B1 ), NVL(B.TS_FLAG, '0'), B.ELEC_TYPE_CODE, B.TRADE_TYPE_CODE, B.MS_MODE, NVL(B.ADD_TAXFLAG, '0'), 'I', C.USER_TYPE1, (SELECT MAX(D.LINE_CODE) FROM POWER_FILE D WHERE D.USER_NO = B.USER_NO AND (D.POWER_NO = B.POWER_NO OR D.POWER_GROUP_NO = B.POWER_GROUP_NO)), NVL((SELECT MAX(D.TRANS_NO) FROM POWER_FILE D WHERE D.USER_NO = B.USER_NO AND (D.POWER_NO = B.POWER_NO OR D.POWER_GROUP_NO = B.POWER_GROUP_NO)),B.TRANS_NO), C.RELA_USER_NO, C.BUSINESS_PLACE_CODE, '0', B.USER_MS_TYPE FROM JL_POINT_FILE B, DF_TMP_USER_SELECT C WHERE B.USER_NO = C.USER_NO

Global blockers dump start:---------------------------------

DUMP LOCAL BLOCKER/HOLDER: block level 3 res [0x130024][0x77844],[TX]

----------resource 0x7000003b21ed860----------------------

resname       : [0x130024][0x77844],[TX]

Local node    : 1

dir_node      : 1

master_node   : 1

hv idx        : 71

hv last r.inc : 34

current inc   : 40

hv status     : 0

hv master     : 0

open options  : dd

grant_bits    : KJUSERNL KJUSEREX

grant mode    : KJUSERNL  KJUSERCR  KJUSERCW  KJUSERPR  KJUSERPW  KJUSEREX

count         : 2         0         0         0         0         1

val_state     : KJUSERVS_NOVALUE

valblk        : 0x00000000000000000000000000000000 .

access_node   : 1

vbreq_state   : 0

state         : x0

resp          : 7000003b21ed860

On Scan_q?    : N

Total accesses: 3609

Imm.  accesses: 3052

Granted_locks : 1

Cvting_locks  : 2

value_block:  00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00

GRANTED_Q :

lp 7000003f23b16d8 gl KJUSEREX rp 7000003b21ed860 [0x130024][0x77844],[TX]

master 1 gl owner 7000003f49f9330 possible pid 684170 xid 2004-004D-000023BB bast 0 rseq 362 mseq 0 history 0x49a51495

open opt KJUSERDEADLOCK

CONVERT_Q:

lp 7000003f23b1c30 gl KJUSERNL rl KJUSERPR rp 7000003b21ed860 [0x130024][0x77844],[TX]

master 1 gl owner 7000003f49e11e0 possible pid 737392 xid 2004-0046-00003FE5 bast 0 rseq 362 mseq 0 history 0x1495149a

convert opt KJUSERGETVALUE

lp 7000003f3382570 gl KJUSERNL rl KJUSERPR rp 7000003b21ed860 [0x130024][0x77844],[TX]

master 1 owner 0  bast 1 rseq 3422 mseq 0x1 history 0x77d497ad

convert opt KJUSERGETVALUE

----------enqueue 0x7000003f23b16d8------------------------

lock version     : 2677

Owner node       : 1

grant_level      : KJUSEREX

req_level        : KJUSEREX

bast_level       : KJUSERNL

notify_func      : 0

resp             : 7000003b21ed860

procp            : 7000003f30fff00

pid              : 737392

proc version     : 54

oprocp           : 0

opid             : 0

group lock owner : 7000003f49f9330

possible pid     : 684170

xid              : 2004-004D-000023BB

dd_time          : 0.0 secs

dd_count         : 0

timeout          : 0.0 secs

On_timer_q?      : N

On_dd_q?         : N

lock_state       : GRANTED

Open Options     : KJUSERDEADLOCK

Convert options  : KJUSERNOQUEUE

History          : 0x49a51495

Msg_Seq          : 0x0

res_seq          : 362

valblk           : 0x00000000000000000000000000000000 .

DUMP LOCAL BLOCKER: initiate state dump for DEADLOCK

possible owner[77.684170] on resource TX-00130024-00077844

Submitting asynchronized dump request [28]

DUMP LOCAL BLOCKER/HOLDER: block level 3 res [0xe0007][0x8a91f],[TX]

----------resource 0x7000003881c25d8----------------------

resname       : [0xe0007][0x8a91f],[TX]

Local node    : 1

dir_node      : 1

master_node   : 1

hv idx        : 5

hv last r.inc : 34

current inc   : 40

hv status     : 0

hv master     : 0

open options  : dd

grant_bits    : KJUSERNL KJUSEREX

grant mode    : KJUSERNL  KJUSERCR  KJUSERCW  KJUSERPR  KJUSERPW  KJUSEREX

count         : 1         0         0         0         0         1

val_state     : KJUSERVS_NOVALUE

valblk        : 0x00000000000000000000000000000000 .

access_node   : 1

vbreq_state   : 0

state         : x0

resp          : 7000003881c25d8

On Scan_q?    : N

Total accesses: 313107

Imm.  accesses: 278032

Granted_locks : 1

Cvting_locks  : 1

value_block:  00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00

GRANTED_Q :

lp 7000003f37a9c80 gl KJUSEREX rp 7000003881c25d8 [0xe0007][0x8a91f],[TX]

master 1 gl owner 7000003f49e11e0 possible pid 737392 xid 2004-0046-00003FE5 bast 0 rseq 33619 mseq 0 history 0x49a51495

open opt KJUSERDEADLOCK

CONVERT_Q:

lp 7000003f37aa070 gl KJUSERNL rl KJUSERPR rp 7000003881c25d8 [0xe0007][0x8a91f],[TX]

master 1 gl owner 7000003f49f9330 possible pid 684170 xid 2004-004D-000023BB bast 0 rseq 33619 mseq 0 history 0x49a5149a

convert opt KJUSERGETVALUE

----------enqueue 0x7000003f37a9c80------------------------

lock version     : 3319

Owner node       : 1

grant_level      : KJUSEREX

req_level        : KJUSEREX

bast_level       : KJUSERNL

notify_func      : 0

resp             : 7000003881c25d8

procp            : 7000003f311b938

pid              : 684170

proc version     : 11

oprocp           : 0

opid             : 0

group lock owner : 7000003f49e11e0

possible pid     : 737392

xid              : 2004-0046-00003FE5

dd_time          : 0.0 secs

dd_count         : 0

timeout          : 0.0 secs

On_timer_q?      : N

On_dd_q?         : N

lock_state       : GRANTED

Open Options     : KJUSERDEADLOCK

Convert options  : KJUSERNOQUEUE

History          : 0x49a51495

Msg_Seq          : 0x0

res_seq          : 33619

valblk           : 0x00000000000000000000000000000000 .

DUMP LOCAL BLOCKER: initiate state dump for DEADLOCK

possible owner[70.737392] on resource TX-000E0007-0008A91F

Submitting asynchronized dump request [28]

Global blockers dump end:-----------------------------------

Global Wait-For-Graph(WFG) at ddTS[0.226] :

BLOCKED 7000003f23b1c30 3 wq 2 cvtops x1 [0x130024][0x77844],[TX] [2004-0046-00003FE5] 1

BLOCKER 7000003f23b16d8 3 wq 1 cvtops x8 [0x130024][0x77844],[TX] [2004-004D-000023BB] 1

BLOCKED 7000003f37aa070 3 wq 2 cvtops x1 [0xe0007][0x8a91f],[TX] [2004-004D-000023BB] 1

BLOCKER 7000003f37a9c80 3 wq 1 cvtops x8 [0xe0007][0x8a91f],[TX] [2004-0046-00003FE5] 1