你大概习惯了日常检视v$lock视图,了解实例中的锁定了阻塞情况,v$lock是一个非常有用的视图,每个dba都爱用他,我们来看看下面的例子:
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
|
SQL>
drop
table
youyus;
Table
dropped
SQL>
create
table
youyus
as
select
rownum t1
from
dual
connect
by
level
<=100;
Table
created
/*首先清理环境*/
session 134:
SQL>
update
youyus
set
t1=t1+1
where
t1>50;
50
rows
updated
/*session 134 更新了表中t1>50的行*/
session 131:
SQL>
update
youyus
set
t1=t1+1;
/*session 131 试图更新表中所有记录,但因为表中部分行仍被锁定着,故处于等待状态*/
SQL>
select
*
from
v$lock
where
sid
in
(134,131)
and
type
in
(
'TM'
,
'TX'
)
order
by
SID,TYPE;
ADDR KADDR SID TYPE ID1 ID2 LMODE REQUEST CTIME BLOCK
-------- -------- ---------- ---- ---------- ---------- ---------- ---------- ---------- ----------
0DD76430 0DD76460 131 TM 73547 0 3 0 802 0
2DE665B8 2DE665F8 131 TX 196619 878 6 0 802 0
2EF45F00 2EF45F2C 131 TX 262166 693 0 6 802 0
0DD76430 0DD76460 134 TM 73547 0 3 0 813 0
2DE36D04 2DE36D44 134 TX 262166 693 6 0 813 1
|
本文转自maclean_007 51CTO博客,原文链接:http://blog.51cto.com/maclean/1276684