1、create table test(id int,name varchar2(20));
insert into test values(1,‘aaa’);
insert into test values(2,‘bbb’);
commit;
session1:
SQL> select distinct sid from v$mystat;
SID
30
session2:
SQL> select distinct sid from v$mystat;
SID
21
session3:
SQL> SELECT * FROM V$LOCK where sid in (30,21);
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
44C3469C 44C346C8 30 AE 100 0 4 0 21331 0
44C353D0 44C353FC 21 AE 100 0 4 0 1740 0
session1:
update test set id=11 where id=1;
session3:
SQL> SELECT * FROM V$LOCK where sid in (30,21);
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
44C3469C 44C346C8 30 AE 100 0 4 0 21934 0
44C353D0 44C353FC 21 AE 100 0 4 0 2343 0
007FD2D4 007FD304 30 TM 80558 0 3 0 97 0
44270ED0 44270F10 30 TX 655380 2122 6 0 97 0
可以看到会话30持有锁时间97s,锁模式为3和6,拥有行级排它锁。
session2:
update test set id=22 where id=2;
session3:
SQL> SELECT * FROM V$LOCK where sid in (30,21);
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
44C3469C 44C346C8 30 AE 100 0 4 0 22687 0
44C353D0 44C353FC 21 AE 100 0 4 0 3096 0
007FD2D4 007FD304 30 TM 80558 0 3 0 850 0
007FD2D4 007FD304 21 TM 80558 0 3 0 20 0
44270ED0 44270F10 30 TX 655380 2122 6 0 850 0
44287C58 44287C98 21 TX 458772 2149 6 0 20 0
可以看到会话30持有锁时间850s,会话21持有锁时间20s,锁模式为3和6,拥有行级排它锁。
session3:
select * from v$Locked_object;
XIDUSN XIDSLOT XIDSQN OBJECT_ID SESSION_ID ORACLE_USERNAME OS_USER_NAME PROCESS LOCKED_MODE
7 20 2149 80558 21 PART oracle 28816 3
10 20 2122 80558 30 PART oracle 18752 3
可以看到会话21和30各有一个3级锁
session1:
update test set id=222 where id=2;
session3:
SQL> SELECT * FROM V$LOCK where sid in (30,21);
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
44C3469C 44C346C8 30 AE 100 0 4 0 23112 0
44C353D0 44C353FC 21 AE 100 0 4 0 3521 0
44C354B8 44C354E4 30 TX 458772 2149 0 6 33 0
007FD2D4 007FD304 30 TM 80558 0 3 0 1275 0
007FD2D4 007FD304 21 TM 80558 0 3 0 445 0
44270ED0 44270F10 30 TX 655380 2122 6 0 1275 0
44287C58 44287C98 21 TX 458772 2149 6 0 445 1
可以看到会话30多了一个0级锁,需要6,等待时间33s。(30是被锁者)
session2:
update test set id=111 where id=1;
session3:
SQL> SELECT * FROM V$LOCK where sid in (30,21);
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
44C3469C 44C346C8 30 AE 100 0 4 0 23315 0
44C353D0 44C353FC 21 AE 100 0 4 0 3724 0
44C354B8 44C354E4 30 TX 458772 2149 0 6 236 0
44C355A0 44C355CC 21 TX 655380 2122 0 6 3 0
007FD2D4 007FD304 30 TM 80558 0 3 0 1478 0
007FD2D4 007FD304 21 TM 80558 0 3 0 648 0
44270ED0 44270F10 30 TX 655380 2122 6 0 1478 1
44287C58 44287C98 21 TX 458772 2149 6 0 648 1
可以看到会话21多了一个0级锁,需要6,等待时间3s。(21是被锁者)
过一会session1报错:
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
session1:
SQL> commit;
session3:
select * from v$Locked_object;
XIDUSN XIDSLOT XIDSQN OBJECT_ID SESSION_ID ORACLE_USERNAME OS_USER_NAME PROCESS LOCKED_MODE
7 20 2149 80558 21 PART oracle 28816 3
可以看到现在的锁对象的会话是21.(21是持有者)
行级锁(tx锁,也叫事务锁)
表级锁(tm锁)
SELECT * FROM V$SESSION;–查看会话和锁的信息
SELECT * FROM V$SESSION_WAIT;–查看等待的会话信息
SELECT * FROM V$LOCK;–系统中所有锁
SELECT * FROM V$LOCKED_OBJECT;–系统中DML锁 有锁模式0~6
查看死锁:
select sid,serial#,username from v
s
e
s
s
i
o
n
w
h
e
r
e
s
i
d
i
n
(
s
e
l
e
c
t
b
l
o
c
k
i
n
g
s
e
s
s
i
o
n
f
r
o
m
v
session where sid in (select blocking_session from v
sessionwheresidin(selectblockingsessionfromvsession);