在使用v$session视图在查询会话的行锁的等待事件时，视图中提供了会话等待的对象号(ROW_WAIT_OBJ#)、文件号(ROW_WAIT_FILE#)、块号(ROW_WAIT_BLOCK#)和行号(ROW_WAIT_ROW#)但是如何使用这些信息定位出会话等待的是哪一行呢？答案就是使用DBMS_ROWID
打开两个会话同时更新同一条数据
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
#session 1
zx@ORCL>
select
distinct
sid
from
v$mystat;
SID
----------
22
zx@ORCL>
zx@ORCL>
update
zx
set
name
=
'zx'
where
id=1;
1 row updated.
#session 2
zx@ORCL>
select
distinct
sid
from
v$mystat;
SID
----------
145
zx@ORCL>
update
zx
set
name
=
'zx'
where
id=1;
|
此时session2会被session1阻塞，查询v$session会话145在等待enq: TX - row lock contention
|
1
2
3
4
5
6
|
zx@ORCL>col event
for
a40
zx@ORCL>
select
SID,EVENT,ROW_WAIT_OBJ#,ROW_WAIT_FILE#,ROW_WAIT_BLOCK#,ROW_WAIT_ROW#
from
v$session
where
sid=145;
SID EVENT ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW#
---------- ---------------------------------------- ------------- -------------- --------------- -------------
145 enq: TX - row lock contention 99754 18 15571 7
|
查询v$lock确认会话145在请求会话22的TX锁
|
1
2
3
4
5
6
7
8
9
10
|
zx@ORCL>
select
sid,type,id1,id2,lmode,request
from
v$lock
where
sid=145
or
sid=22
order
by
1;
SID TYPE ID1 ID2 LMODE REQUEST
---------- ------ ---------- ---------- ---------- ----------
22 AE 100 0 4 0
22 TM 99754 0 3 0
22 TX 4390915 581 6 0
145 TM 99754 0 3 0
145 TX 4390915 581 0 6
145 AE 100 0 4 0
|
使用如下语句查询会话145等待哪个表的哪个行
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
zx@ORCL>col owner
for
a10
zx@ORCL>col object_name
for
a10
zx@ORCL>col rowid
for
a30
zx@ORCL>
select
b.owner,b.object_name,dbms_rowid.rowid_create(1,s.ROW_WAIT_OBJ#,s.ROW_WAIT_FILE#,s.ROW_WAIT_BLOCK#,ROW_WAIT_ROW#)
"rowid"
from
v$session s,dba_objects b
where
s.ROW_WAIT_OBJ#=b.object_id
and
s.si
d=145;
OWNER OBJECT_NAM rowid
---------- ---------- ------------------------------
ZX ZX AAAYWqAASAAADzTAAH
--使用上面查询出的rowid查看数据，即为session2等待的行
zx@ORCL>
select
*
from
zx.zx
where
rowid=
'AAAYWqAASAAADzTAAH'
;
ID
NAME
---------- ------------------------------
1 ZX
|
官方文档：http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_rowid.htm#ARPLS053
使用下面语句查找会话之间的阻塞关系
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
|
SELECT
(
'节点'
|| a.inst_id ||
' session '
|| a.sid ||
','
|| a_s.serial# ||
'阻塞了节点'
|| b.inst_id ||
' session '
|| b.sid ||
','
|| b_s.serial#) blockinfo,
a.inst_id,
a_s.sid,
a_s.schemaname,
a_s.module,
a_s.status,
a_s.event,
a.type lock_type,
a.id1,
a.id2,
decode(a.lmode,
0,
'none'
,
1,
NULL
,
2,
'row-S(SS)'
,
3,
'row-X(SX)'
,
4,
'share(S)'
,
5,
'S/Row-X(SSX)'
,
6,
'exclusive(X)'
) lock_mode,
a.ctime time_hold,
'后为被阻塞信息'
remark_flag,
b.inst_id blocked_inst_id,
b.sid blocked_sid,
b.type blocked_lock_type,
decode(b.request,
0,
'none'
,
1,
NULL
,
2,
'row-S(SS)'
,
3,
'row-X(SX)'
,
4,
'share(S)'
,
5,
'S/Row-X(SSX)'
,
6,
'exclusive(X)'
) blocked_lock_request,
b.ctime time_wait,
b_s.schemaname blocked_schemaname,
b_s.module blocked_module,
b_s.status blocked_status,
b_s.sql_id blocked_sql_id,
b_s.event,
obj.owner blocked_owner,
obj.object_name blocked_name,
obj.object_type blocked_object_type,
CASE
WHEN
b_s.row_wait_obj# <> -1
THEN
dbms_rowid.rowid_create(1,
obj.data_object_id,
b_s.row_wait_file#,
b_s.row_wait_block#,
b_s.row_wait_row#)
ELSE
'-1'
END
blocked_rowid,
--被阻塞数据的rowid
decode(obj.object_type,
'TABLE'
,
'select * from '
|| obj.owner ||
'.'
|| obj.object_name ||
' where rowid='
''
||
dbms_rowid.rowid_create(1,
obj.data_object_id,
b_s.row_wait_file#,
b_s.row_wait_block#,
b_s.row_wait_row#) ||
''
''
,
NULL
) blocked_data_querysql
FROM
gv$lock a,
gv$lock b,
gv$session a_s,
gv$session b_s,
dba_objects obj
WHERE
a.id1 = b.id1
AND
a.id2 = b.id2
AND
a.block > 0
--阻塞了其他人
AND
b.request > 0
--AND ((a.INST_ID=b.INST_ID AND a.SID<>b.SID) OR (a.INST_ID<>b.INST_ID ))
AND
a.sid = a_s.sid
AND
a.inst_id = a_s.inst_id
AND
b.sid = b_s.sid
AND
b.inst_id = b_s.inst_id
AND
b_s.row_wait_obj# = obj.object_id(+)
ORDER
BY
a.inst_id, a.sid;
|
本文转自hbxztc 51CTO博客，原文链接：http://blog.51cto.com/hbxztc/1896136，如需转载请自行联系原作者
