如何阅读数据库发生死锁时生成的trace文件
请教各位，如何阅读数据库发生死锁时生成的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 ,
----------resource 0x0x433d04c18----------------------
resname : ,
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 : KJUSERNLKJUSERCRKJUSERCWKJUSERPRKJUSERPWKJUSEREX
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 ,
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 on resource TX-00300013-0000596C
Submitting asynchronized dump request
Global blockers dump end:-----------------------------------
ENQUEUE DUMP REQUEST: from 1.15472 on , for reason 3 mtype 0
DUMP LOCAL BLOCKER/HOLDER: block level 5 res ,
----------resource 0x0x43377a588----------------------
resname : ,
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 : KJUSERNLKJUSERCRKJUSERCWKJUSERPRKJUSERPWKJUSEREX
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 ,
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 ,
master 0 owner 1bast 1 rseq 599 mseq 0x1 history 0x77d497ad
convert opt KJUSERGETVALUE
lp 0x45aae8098 gl KJUSERNL rl KJUSEREX rp 0x43377a588 ,
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 on resource TX-00060026-0002B86A
Submitting asynchronized dump request
----------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 , for reason 3 mtype 0
DUMP LOCAL BLOCKER/HOLDER: block level 5 res ,
----------resource 0x0x433d04c18----------------------
resname : ,
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 : KJUSERNLKJUSERCRKJUSERCWKJUSERPRKJUSERPWKJUSEREX
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 ,
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 on resource TX-00300013-0000596C
Submitting asynchronized dump request
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 , for reason 3 mtype 0
DUMP LOCAL BLOCKER/HOLDER: block level 5 res ,
----------resource 0x0x43377a588----------------------
resname : ,
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 : KJUSERNLKJUSERCRKJUSERCWKJUSERPRKJUSERPWKJUSEREX
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 ,
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 ,
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 ,
master 0 owner 1bast 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 on resource TX-00060026-0002B86A
Submitting asynchronized dump request
----------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 on resource TX-00060026-0002B86A
Submitting asynchronized dump request
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 ,
----------resource 0x0x433d04c18----------------------
resname : ,
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 : KJUSERNLKJUSERCRKJUSERCWKJUSERPRKJUSERPWKJUSEREX
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 ,
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 on resource TX-00300013-0000596C
Submitting asynchronized dump request
DUMP LOCAL BLOCKER/HOLDER: block level 5 res ,
----------resource 0x0x43377a588----------------------
resname : ,
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 : KJUSERNLKJUSERCRKJUSERCWKJUSERPRKJUSERPWKJUSEREX
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 ,
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 ,
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 ,
master 0 owner 1bast 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 on resource TX-00060026-0002B86A
Submitting asynchronized dump request
----------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 on resource TX-00060026-0002B86A
Submitting asynchronized dump request
Global blockers dump end:-----------------------------------
Global Wait-For-Graph(WFG) at ddTS :
BLOCKED 0x45fc19090 5 wq 2 cvtops x1 , 0
BLOCKER 0x4201e97a8 5 wq 1 cvtops x8 , 1
BLOCKED 0x45fe1c850 5 wq 2 cvtops x1 , 1
BLOCKER 0x45fb406d0 5 wq 1 cvtops x8 , 0
* Cancel deadlock victim lockp x5fc19090
这是两个节点的rac数据库，从下面
Global blockers dump end:-----------------------------------
Global Wait-For-Graph(WFG) at ddTS :
BLOCKED 0x45fc19090 5 wq 2 cvtops x1 , 0
BLOCKER 0x4201e97a8 5 wq 1 cvtops x8 , 1
BLOCKED 0x45fe1c850 5 wq 2 cvtops x1 , 1
BLOCKER 0x45fb406d0 5 wq 1 cvtops x8 , 0
知道，发生死锁的sql是分别部署在两个节点上。
呵呵。。。找到了别人介绍的：
Global Wait-For-Graph(WFG) at ddTS :
BLOCKED 0x45fc19090 5 wq 2 cvtops x1 , 0
BLOCKER 0x4201e97a8 5 wq 1 cvtops x8 , 1
BLOCKED 0x45fe1c850 5 wq 2 cvtops x1 , 1
BLOCKER 0x45fb406d0 5 wq 1 cvtops x8 , 0
WFG(Wait-For_Graph,等待图）
其中第一列列出了阻塞者（BLOCKER）和被阻塞者（BLOCKED），第二列是锁的地址指针，第三列是锁的模式，第四列是资源的ID及名字，第五列的含义是<pid|did|txn_id>,可能是PID,也可能是进程事物号，最后一列是rac节点的ID，0代表一号节点，1代表二号节点。
锁模式的取值及含义说明如下：
0：NULL
1:并发读（SS)
2：并发写（SX）
3：保护读(S）
4：保护写（SSX)
5：排他访问（X）
处理死锁的方法：
1、找到trace文件，找到里面的“WFG”
2、找到产生死锁的sql语句、争用的资源、事务的信息
3、将这些找出来以后，让开发者去解决（一半是因为逻辑业务形成的死锁）
作为，DBA，必须给开发人员找到这些内容。 有时间给你做一个trace的例子分析一下。
:),谢谢老师，期待中。 同样期待老师的分析！ 期待老师的分析！
页:
[1]