v$lock视图
SID：Identifier for session holding or acquiring the lock
TYPE：Type of user or system lock
 TM - DML enqueue
 TX - Transaction enqueue
 UL - User supplied
LMODE：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: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)
用sysdba方式先登录
C:\Documents and Settings\Administrator>sqlplus / as sysdba
SQL*Plus: Release 10.2.0.3.0 - Production on 星期一 3月 15 10:19:02 2010
Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
此时未有WANGLIN用户登录
SQL> select sid,username from v$session where username='WANGLIN';
未选定行
登录第一个WANGLIN用户
C:\Documents and Settings\Administrator>sqlplus wanglin/wanglin
SQL*Plus: Release 10.2.0.3.0 - Production on 星期一 3月 15 10:22:02 2010
Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
查询第一个WANGLIN登录的SID
SQL> select sid,username from v$session where username='WANGLIN';
       SID USERNAME
---------- ------------------------------
       159 WANGLIN
继续登录第二个WANGLIN用户，并查询SID
SQL> select sid,username from v$session where username='WANGLIN';
       SID USERNAME
---------- ------------------------------
       152 WANGLIN
       159 WANGLIN
查询两个session是否申请了lock了
SQL> select sid,type,lmode,request from v$lock where sid in(152,159) order by sid;
未选定行
第一个WANGLIN用户更新一行。
SQL> select * from test;
        ID NAME
---------- ----------
         1 1111
         2 lin
         3 wanglin
SQL> update test set name='fffff' where id=3;
已更新 1 行。
不要提交查询v$lock视图
SQL> select sid,type,lmode,request from v$lock where sid in(152,159) order by sid;
       SID TY      LMODE    REQUEST
---------- -- ---------- ----------
       159 TX          6          0
       159 TM          3          0
这个时候在WANGLIN session会话里也更新这一行（回话2里要hang住）
SQL> select * from test;
        ID NAME
---------- ----------
         1 1111
         2 lin
         3 wanglin
SQL> update test set name='xxxxx' where id=3;
再查询下v$lock视图
SQL> select sid,type,lmode,request from v$lock where sid in(152,159) order by sid;
       SID TY      LMODE    REQUEST
---------- -- ---------- ----------
       152 TM          3          0
       152 TX          0          6
       159 TX          6          0
       159 TM          3          0
这里可以看的出会话二在等待前一个会话释放TX锁。查询下session里是否有这个等待事件。
SQL> select sid,event from v$session where sid=152;
       SID EVENT
---------- ----------------------------------------------------------------
       152 enq: TX - row lock contention
可以判断hang住的原因就是在等待TX锁的释放。
这时回话一commit后会话二就正常了。
SQL> select sid,event from v$session where sid=152;
       SID EVENT
---------- ----------------------------------------------------------------
       152 SQL*Net message from client
SQL> select sid,type,lmode,request from v$lock where sid in(152,159) order by sid;
       SID TY      LMODE    REQUEST
---------- -- ---------- ----------
       152 TX          6          0
       152 TM          3          0