请教各位,如何阅读数据库发生死锁时生成的trace文件,下面把一个例子粘贴。现在对各个参数的意思还很模糊,希望老师和各位解释解释。
*** 2010-09-17 08:59:56.397
user session for deadlock lock 0x45fb406d0
pid=115 serial=2072 audsid=11010901 user: 57/ZXJ
O/S info: user: , term: , ospid: 1234, machine: com1
program:
Current SQL Statement:
delete from order_info where yjtm=:1 and cjdm=:2
user session for deadlock lock 0x45fc19090
pid=115 serial=2072 audsid=11010901 user: 57/ZXJ
O/S info: user: , term: , ospid: 1234, machine: com1
program:
Current SQL Statement:
delete from order_info where yjtm=:1 and cjdm=:2
Global blockers dump start:---------------------------------
DUMP LOCAL BLOCKER/HOLDER: block level 5 res [0x300013][0x596c],[TX]
----------resource 0x0x433d04c18----------------------
resname : [0x300013][0x596c],[TX]
Local node : 0
dir_node : 1
master_node : 1
hv idx : 83
hv last r.inc : 6
current inc : 8
hv status : 0
hv master : 1
open options : dd
Held mode : KJUSERNL
Cvt mode : KJUSEREX
Next Cvt mode : KJUSERNL
msg_seq : 0x1
res_seq : 5
grant_bits : KJUSERNL
grant mode : KJUSERNL KJUSERCR KJUSERCW KJUSERPR KJUSERPW KJUSEREX
count : 1 0 0 0 0 0
val_state : KJUSERVS_NOVALUE
valblk : 0x00000000000000000000000000000000 .
access_node : 1
vbreq_state : 0
state : x8
resp : 0x433d04c18
On Scan_q? : N
Total accesses: 42
Imm. accesses: 38
Granted_locks : 0
Cvting_locks : 1
value_block: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
GRANTED_Q :
CONVERT_Q:
lp 0x45fc19090 gl KJUSERNL rl KJUSEREX rp 0x433d04c18 [0x300013][0x596c],[TX]
master 1 gl owner 0x45f784138 possible pid 6772 xid 73000-0001-00000019 bast 0 rseq 5 mseq 0 history 0x1495149a
convert opt KJUSERGETVALUE
----------enqueue 0x0x45fc19090------------------------
lock version : 13545
Owner node : 0
grant_level : KJUSERNL
req_level : KJUSEREX
bast_level : KJUSERNL
notify_func : (nil)
resp : 0x433d04c18
procp : 0x45f963878
pid : 6772
proc version : 3
oprocp : (nil)
opid : 0
group lock owner : 0x45f784138
possible pid : 6772
xid : 73000-0001-00000019
dd_time : 1.0 secs
dd_count : 0
timeout : 0.0 secs
On_timer_q? : N
On_dd_q? : Y
lock_state : OPENING CONVERTING
Open Options : KJUSERDEADLOCK
Convert options : KJUSERGETVALUE
History : 0x1495149a
Msg_Seq : 0x0
res_seq : 5
valblk : 0x00000000000000000000000000000000 .
DUMP LOCAL BLOCKER: initiate state dump for DEADLOCK
possible owner[115.6772] on resource TX-00300013-0000596C
Submitting asynchronized dump request [28]
Global blockers dump end:-----------------------------------
ENQUEUE DUMP REQUEST: from 1.15472 on [0x60026][0x2b86a],[TX] for reason 3 mtype 0
DUMP LOCAL BLOCKER/HOLDER: block level 5 res [0x60026][0x2b86a],[TX]
----------resource 0x0x43377a588----------------------
resname : [0x60026][0x2b86a],[TX]
Local node : 0
dir_node : 0
master_node : 0
hv idx : 100
hv last r.inc : 8
current inc : 8
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 : 0
vbreq_state : 0
state : x0
resp : 0x43377a588
On Scan_q? : N
Total accesses: 41
Imm. accesses: 38
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 0x45fb406d0 gl KJUSEREX rp 0x43377a588 [0x60026][0x2b86a],[TX]
master 0 gl owner 0x45f784138 possible pid 6772 xid 73000-0001-00000019 bast 0 rseq 5 mseq 0 history 0x4977d495
open opt KJUSERDEADLOCK
CONVERT_Q:
lp 0x45fb43928 gl KJUSERNL rl KJUSEREX rp 0x43377a588 [0x60026][0x2b86a],[TX]
master 0 owner 1 bast 1 rseq 599 mseq 0x1 history 0x77d497ad
convert opt KJUSERGETVALUE
lp 0x45aae8098 gl KJUSERNL rl KJUSEREX rp 0x43377a588 [0x60026][0x2b86a],[TX]
master 0 gl owner 0x459794928 possible pid 3713 xid 71000-0001-0000000E bast 0 rseq 5 mseq 0 history 0x1495149a
convert opt KJUSERGETVALUE
----------enqueue 0x0x45fb406d0------------------------
lock version : 2775
Owner node : 0
grant_level : KJUSEREX
req_level : KJUSEREX
bast_level : KJUSERNL
notify_func : (nil)
resp : 0x43377a588
procp : 0x45f9442a0
pid : 1193
proc version : 0
oprocp : (nil)
opid : 0
group lock owner : 0x45f784138
possible pid : 6772
xid : 73000-0001-00000019
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 : 0x4977d495
Msg_Seq : 0x0
res_seq : 5
valblk : 0x00000000000000000000000000000000 .
DUMP LOCAL BLOCKER: initiate state dump for DEADLOCK
possible owner[115.6772] on resource TX-00060026-0002B86A
Submitting asynchronized dump request [28]
----------enqueue 0x0x45fb43928------------------------
lock version : 691
Owner node : 1
grant_level : KJUSERNL
req_level : KJUSEREX
bast_level : KJUSERNL
notify_func : 0xd46798
resp : 0x43377a588
procp : 0x45f949c10
pid : 0
proc version : 0
oprocp : (nil)
opid : 0
group lock owner : (nil)
xid : 0000-0000-00000000
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 : KJUSERNO_XID
Convert options : KJUSERGETVALUE
History : 0x77d497ad
Msg_Seq : 0x1
res_seq : 599
valblk : 0x00000000000000000000000000000000 .
ENQUEUE DUMP REQUEST: from 1.15472 on [0x300013][0x596c],[TX] for reason 3 mtype 0
DUMP LOCAL BLOCKER/HOLDER: block level 5 res [0x300013][0x596c],[TX]
----------resource 0x0x433d04c18----------------------
resname : [0x300013][0x596c],[TX]
Local node : 0
dir_node : 1
master_node : 1
hv idx : 83
hv last r.inc : 6
current inc : 8
hv status : 0
hv master : 1
open options : dd
Held mode : KJUSERNL
Cvt mode : KJUSEREX
Next Cvt mode : KJUSERNL
msg_seq : 0x1
res_seq : 5
grant_bits : KJUSERNL
grant mode : KJUSERNL KJUSERCR KJUSERCW KJUSERPR KJUSERPW KJUSEREX
count : 1 0 0 0 0 0
val_state : KJUSERVS_NOVALUE
valblk : 0x00000000000000000000000000000000 .
access_node : 1
vbreq_state : 0
state : x8
resp : 0x433d04c18
On Scan_q? : N
Total accesses: 43
Imm. accesses: 39
Granted_locks : 0
Cvting_locks : 1
value_block: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
GRANTED_Q :
CONVERT_Q:
lp 0x45fc19090 gl KJUSERNL rl KJUSEREX rp 0x433d04c18 [0x300013][0x596c],[TX]
master 1 gl owner 0x45f784138 possible pid 6772 xid 73000-0001-00000019 bast 0 rseq 5 mseq 0 history 0x1495149a
convert opt KJUSERGETVALUE
----------enqueue 0x0x45fc19090------------------------
lock version : 13545
Owner node : 0
grant_level : KJUSERNL
req_level : KJUSEREX
bast_level : KJUSERNL
notify_func : (nil)
resp : 0x433d04c18
procp : 0x45f963878
pid : 6772
proc version : 3
oprocp : (nil)
opid : 0
group lock owner : 0x45f784138
possible pid : 6772
xid : 73000-0001-00000019
dd_time : 1.0 secs
dd_count : 0
timeout : 0.0 secs
On_timer_q? : N
On_dd_q? : Y
lock_state : OPENING CONVERTING
Open Options : KJUSERDEADLOCK
Convert options : KJUSERGETVALUE
History : 0x1495149a
Msg_Seq : 0x0
res_seq : 5
valblk : 0x00000000000000000000000000000000 .
DUMP LOCAL BLOCKER: initiate state dump for DEADLOCK
possible owner[115.6772] on resource TX-00300013-0000596C
Submitting asynchronized dump request [28]
user session for deadlock lock 0x45fc19090
pid=115 serial=2072 audsid=11010901 user: 57/ZXJ
O/S info: user: , term: , ospid: 1234, machine: com1
program:
Current SQL Statement:
delete from order_info where yjtm=:1 and cjdm=:2
ENQUEUE DUMP REQUEST: from 1.15472 on [0x60026][0x2b86a],[TX] for reason 3 mtype 0
DUMP LOCAL BLOCKER/HOLDER: block level 5 res [0x60026][0x2b86a],[TX]
----------resource 0x0x43377a588----------------------
resname : [0x60026][0x2b86a],[TX]
Local node : 0
dir_node : 0
master_node : 0
hv idx : 100
hv last r.inc : 8
current inc : 8
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 : 0
vbreq_state : 0
state : x0
resp : 0x43377a588
On Scan_q? : N
Total accesses: 52
Imm. accesses: 49
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 0x45fb406d0 gl KJUSEREX rp 0x43377a588 [0x60026][0x2b86a],[TX]
master 0 gl owner 0x45f784138 possible pid 6772 xid 73000-0001-00000019 bast 0 rseq 5 mseq 0 history 0x4977d495
open opt KJUSERDEADLOCK
CONVERT_Q:
lp 0x45aae8098 gl KJUSERNL rl KJUSEREX rp 0x43377a588 [0x60026][0x2b86a],[TX]
master 0 gl owner 0x459794928 possible pid 3713 xid 71000-0001-0000000E bast 0 rseq 5 mseq 0 history 0x1495149a
convert opt KJUSERGETVALUE
lp 0x45fb43928 gl KJUSERNL rl KJUSEREX rp 0x43377a588 [0x60026][0x2b86a],[TX]
master 0 owner 1 bast 1 rseq 600 mseq 0x1 history 0x7ad497ad
convert opt KJUSERGETVALUE
----------enqueue 0x0x45fb406d0------------------------
lock version : 2775
Owner node : 0
grant_level : KJUSEREX
req_level : KJUSEREX
bast_level : KJUSERNL
notify_func : (nil)
resp : 0x43377a588
procp : 0x45f9442a0
pid : 1193
proc version : 0
oprocp : (nil)
opid : 0
group lock owner : 0x45f784138
possible pid : 6772
xid : 73000-0001-00000019
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 : 0x4977d495
Msg_Seq : 0x0
res_seq : 5
valblk : 0x00000000000000000000000000000000 .
DUMP LOCAL BLOCKER: initiate state dump for DEADLOCK
possible owner[115.6772] on resource TX-00060026-0002B86A
Submitting asynchronized dump request [28]
----------enqueue 0x0x45aae8098------------------------
lock version : 923
Owner node : 0
grant_level : KJUSERNL
req_level : KJUSEREX
bast_level : KJUSERNL
notify_func : (nil)
resp : 0x43377a588
procp : 0x45f96cd88
pid : 3713
proc version : 10
oprocp : (nil)
opid : 0
group lock owner : 0x459794928
possible pid : 3713
xid : 71000-0001-0000000E
dd_time : 26.0 secs
dd_count : 0
timeout : 0.0 secs
On_timer_q? : N
On_dd_q? : Y
lock_state : OPENING CONVERTING
Open Options : KJUSERDEADLOCK
Convert options : KJUSERGETVALUE
History : 0x1495149a
Msg_Seq : 0x0
res_seq : 5
valblk : 0x00000000000000000000000000000000 .
DUMP LOCAL BLOCKER: initiate state dump for DEADLOCK
possible owner[113.3713] on resource TX-00060026-0002B86A
Submitting asynchronized dump request [28]
user session for deadlock lock 0x45fb406d0
pid=115 serial=2072 audsid=11010901 user: 57/ZXJ
O/S info: user: , term: , ospid: 1234, machine: com1
program:
Current SQL Statement:
delete from order_info where yjtm=:1 and cjdm=:2
Global blockers dump start:---------------------------------
DUMP LOCAL BLOCKER/HOLDER: block level 5 res [0x300013][0x596c],[TX]
----------resource 0x0x433d04c18----------------------
resname : [0x300013][0x596c],[TX]
Local node : 0
dir_node : 1
master_node : 1
hv idx : 83
hv last r.inc : 6
current inc : 8
hv status : 0
hv master : 1
open options : dd
Held mode : KJUSERNL
Cvt mode : KJUSEREX
Next Cvt mode : KJUSERNL
msg_seq : 0x1
res_seq : 5
grant_bits : KJUSERNL
grant mode : KJUSERNL KJUSERCR KJUSERCW KJUSERPR KJUSERPW KJUSEREX
count : 1 0 0 0 0 0
val_state : KJUSERVS_NOVALUE
valblk : 0x00000000000000000000000000000000 .
access_node : 1
vbreq_state : 0
state : x8
resp : 0x433d04c18
On Scan_q? : N
Total accesses: 49
Imm. accesses: 43
Granted_locks : 0
Cvting_locks : 1
value_block: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
GRANTED_Q :
CONVERT_Q:
lp 0x45fc19090 gl KJUSERNL rl KJUSEREX rp 0x433d04c18 [0x300013][0x596c],[TX]
master 1 gl owner 0x45f784138 possible pid 6772 xid 73000-0001-00000019 bast 0 rseq 5 mseq 0 history 0x1495149a
convert opt KJUSERGETVALUE
----------enqueue 0x0x45fc19090------------------------
lock version : 13545
Owner node : 0
grant_level : KJUSERNL
req_level : KJUSEREX
bast_level : KJUSERNL
notify_func : (nil)
resp : 0x433d04c18
procp : 0x45f963878
pid : 6772
proc version : 3
oprocp : (nil)
opid : 0
group lock owner : 0x45f784138
possible pid : 6772
xid : 73000-0001-00000019
dd_time : 56.0 secs
dd_count : 1
timeout : 0.0 secs
On_timer_q? : N
On_dd_q? : Y
lock_state : OPENING CONVERTING
Open Options : KJUSERDEADLOCK
Convert options : KJUSERGETVALUE
History : 0x1495149a
Msg_Seq : 0x0
res_seq : 5
valblk : 0x00000000000000000000000000000000 .
DUMP LOCAL BLOCKER: initiate state dump for DEADLOCK
possible owner[115.6772] on resource TX-00300013-0000596C
Submitting asynchronized dump request [28]
DUMP LOCAL BLOCKER/HOLDER: block level 5 res [0x60026][0x2b86a],[TX]
----------resource 0x0x43377a588----------------------
resname : [0x60026][0x2b86a],[TX]
Local node : 0
dir_node : 0
master_node : 0
hv idx : 100
hv last r.inc : 8
current inc : 8
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 : 0
vbreq_state : 0
state : x0
resp : 0x43377a588
On Scan_q? : N
Total accesses: 54
Imm. accesses: 50
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 0x45fb406d0 gl KJUSEREX rp 0x43377a588 [0x60026][0x2b86a],[TX]
master 0 gl owner 0x45f784138 possible pid 6772 xid 73000-0001-00000019 bast 0 rseq 5 mseq 0 history 0x4977d495
open opt KJUSERDEADLOCK
CONVERT_Q:
lp 0x45aae8098 gl KJUSERNL rl KJUSEREX rp 0x43377a588 [0x60026][0x2b86a],[TX]
master 0 gl owner 0x459794928 possible pid 3713 xid 71000-0001-0000000E bast 0 rseq 5 mseq 0 history 0x1495149a
convert opt KJUSERGETVALUE
lp 0x45fb43928 gl KJUSERNL rl KJUSEREX rp 0x43377a588 [0x60026][0x2b86a],[TX]
master 0 owner 1 bast 1 rseq 600 mseq 0x1 history 0x7ad497ad
convert opt KJUSERGETVALUE
----------enqueue 0x0x45fb406d0------------------------
lock version : 2775
Owner node : 0
grant_level : KJUSEREX
req_level : KJUSEREX
bast_level : KJUSERNL
notify_func : (nil)
resp : 0x43377a588
procp : 0x45f9442a0
pid : 1193
proc version : 0
oprocp : (nil)
opid : 0
group lock owner : 0x45f784138
possible pid : 6772
xid : 73000-0001-00000019
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 : 0x4977d495
Msg_Seq : 0x0
res_seq : 5
valblk : 0x00000000000000000000000000000000 .
DUMP LOCAL BLOCKER: initiate state dump for DEADLOCK
possible owner[115.6772] on resource TX-00060026-0002B86A
Submitting asynchronized dump request [28]
----------enqueue 0x0x45aae8098------------------------
lock version : 923
Owner node : 0
grant_level : KJUSERNL
req_level : KJUSEREX
bast_level : KJUSERNL
notify_func : (nil)
resp : 0x43377a588
procp : 0x45f96cd88
pid : 3713
proc version : 10
oprocp : (nil)
opid : 0
group lock owner : 0x459794928
possible pid : 3713
xid : 71000-0001-0000000E
dd_time : 26.0 secs
dd_count : 0
timeout : 0.0 secs
On_timer_q? : N
On_dd_q? : Y
lock_state : OPENING CONVERTING
Open Options : KJUSERDEADLOCK
Convert options : KJUSERGETVALUE
History : 0x1495149a
Msg_Seq : 0x0
res_seq : 5
valblk : 0x00000000000000000000000000000000 .
DUMP LOCAL BLOCKER: initiate state dump for DEADLOCK
possible owner[113.3713] on resource TX-00060026-0002B86A
Submitting asynchronized dump request [28]
Global blockers dump end:-----------------------------------
Global Wait-For-Graph(WFG) at ddTS[0.5f] :
BLOCKED 0x45fc19090 5 wq 2 cvtops x1 [0x300013][0x596c],[TX] [73000-0001-00000019] 0
BLOCKER 0x4201e97a8 5 wq 1 cvtops x8 [0x300013][0x596c],[TX] [F9000-0002-0000005D] 1
BLOCKED 0x45fe1c850 5 wq 2 cvtops x1 [0x60026][0x2b86a],[TX] [F9000-0002-0000005D] 1
BLOCKER 0x45fb406d0 5 wq 1 cvtops x8 [0x60026][0x2b86a],[TX] [73000-0001-00000019] 0
* Cancel deadlock victim lockp x5fc19090
|
|