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

转自：http://www.cnblogs.com/rootq/articles/1147450.html