使用V$LOCK 检查阻塞
查询阻塞的示例：
1.官方V$lock view 的explain：
V$LOCK
This view lists the locks currently held by the Oracle Database and outstanding requests for a lock or latch.
Column Datatype Description
ADDR RAW(4 | 8) Address of lock state object
KADDR RAW(4 | 8) Address of lock
SID NUMBER Identifier for session holding or acquiring the lock
TYPE VARCHAR2(2) Type of user or system lock
The locks on the user types are obtained by user applications. Any process that is blocking others is likely to be holding one of these locks. The user type locks are:
TM - DML enqueue
TX - Transaction enqueue
UL - User supplied
The locks on the system types are held for extremely short periods of time. The system type locks are listed in Table 6-1.
ID1 NUMBER Lock identifier #1 (depends on type)
ID2 NUMBER Lock identifier #2 (depends on type)
LMODE NUMBER Lock mode in which the session holds the lock:
0 - none
1 - null (NULL)
2 - row-S (SS)
3 - row-X (SX)
4 - share (S)
5 - S/Row-X (SSX)
6 - exclusive (X)
REQUEST NUMBER Lock mode in which the process requests the lock:
0 - none
1 - null (NULL)
2 - row-S (SS)
3 - row-X (SX)
4 - share (S)
5 - S/Row-X (SSX)
6 - exclusive (X)
CTIME NUMBER Time since current mode was granted
BLOCK NUMBER A value of either 0 or 1, depending on whether or not the lock in question is the blocker.
2.示例
--执行如下statement：
SQL> UPDATE test_zhp_061901 SET c1=111;
3 rows updated
--另一个session中执行：
SQL> UPDATE test_zhp_061901 SET c1=222;
(一直等待，说明该session被阻塞)
--查询那个session阻塞了那个session:
SQL> SELECT a.sid,b.sid FROM v$lock a ,v$lock b
2 WHERE b.id1=a.id1 AND a.id2=b.id2
3 AND a.sid<>b.sid
4 AND b.request<>0
5 /
SID SID
---------- ----------
4048 4017
说明session 4048阻塞了session 4017
--分别查看两个session holding 什么锁：
SQL> select * from v$lock where sid=4048;
ADDR KADDR SID TYPE ID1 ID2 LMODE REQUEST CTIME BLOCK
---------------- ---------------- ---------- ---- ---------- ---------- ---------- ---------- ---------- ----------
C00000040681F928 C00000040681F950 4048 TM 233121 0 3 0 112 2
C000000403E025A0 C000000403E02728 4048 TX 2031641 689835 6 0 112 1
session 4048 持有对象233121上的TM锁，锁的模式是3( row-X (SX) ),
同时session 4048 持有TX锁，锁的模式是6( exclusive (X) ),REQUEST为0表示该session没有请求锁，
也就是说该session持有锁(表明该session是阻塞者：阻塞其他session的session)
SQL> select * from v$lock where sid=4017;
ADDR KADDR SID TYPE ID1 ID2 LMODE REQUEST CTIME BLOCK
---------------- ---------------- ---------- ---- ---------- ---------- ---------- ---------- ---------- ----------
C00000041C0D53C0 C00000041C0D53E0 4017 TX 2031641 689835 0 6 118 0
C00000040681FF28 C00000040681FF50 4017 TM 233121 0 3 0 118 2
session 4017 持有对象233121上的TM锁，锁的模式是3( row-X (SX) ),
同时session 4017没有持有TX锁，锁的类型是0( none ) 表面该session 等待持有TX
锁，也就是说该session被阻塞，同时可以看到TX锁对应的REQUEST=6 表明该
SESSION正在请求一个模式为6( exclusive (X) )的锁，也就是说该SESSION是
被阻塞者(被其他SESSION阻塞的SESSION)
SQL> select object_id,object_name,object_type from all_objects where object_id=233121;
OBJECT_ID OBJECT_NAME OBJECT_TYPE
---------- ------------------------------ -------------------
233121 TEST_ZHP_061901 TABLE
SQL>
