本次测试目的为：根据v$session找到产生持锁会话，视具体情况，kill掉持锁会话，解决死锁问题，并初步理解v$session各列的概念

 

SQL> select distinct sid from v$mystat;  --确认一个会话的sid

       SID
----------
       988

SQL> update t1 set a=1; --更新一个记录不提交,会产生一个lock

已更新 1 行。

 

 

SQL> select distinct sid from v$mystat;--确认另一个会话的sid

       SID
----------
       976

SQL> update t1 set a=2;  --更新前一个会话的同一个表的数据，这样会产生死锁

 

 

 select 

sid,

serial#,

username,

command,--会话正在执行的命令，这里是数字，详细请查阅官方手册

taddr,--事务状态对象的地址，不是事务的地址，两个概念

lockwait,--会话等待锁的地址（是这个会话要等待的锁的地址，对应v$lock的kaddr）

status,--会话各种状态，有active,inactive,unknown其它

server,--专用或共享服务器模式

schemaname,--会话所有用户名称

osuser,--客户端（连接的应用）所在操作系统用户名称

blocking_session --阻塞这个会话的会话sid,非常有用，直接根据它去找持锁会话

from v$session

  SID    SERIAL# USERNAME      COMMAND TADDR    LOCKWAIT STATUS   SERVER    SCHEMANAME                     OSUSER                         BLOCKING_SESSION
----- ---------- ---------- ---------- -------- -------- -------- --------- ------------------------------ ------------------------------ ----------------
  968         75 PD                  3                   INACTIVE DEDICATED PD                             BvwW
  975        301 SYS                 3                   ACTIVE   DEDICATED SYS                            oracle
  976        416 PD                  6 3CC341C0 3DC347AC ACTIVE   DEDICATED PD                             oracle                                      988
  985          3                     0                   ACTIVE   DEDICATED SYS                            oracle
  986          3                     0                   ACTIVE   DEDICATED SYS                            oracle
  988        154 PD                  0 3CC346E4          INACTIVE DEDICATED PD                             oracle
  990          1                     0                   ACTIVE   DEDICATED SYS                            oracle
  991          1                     0                   ACTIVE   DEDICATED SYS                            oracle
  992          1                     0                   ACTIVE   DEDICATED SYS                            oracle
  993          1                     0                   ACTIVE   DEDICATED SYS                            oracle
  994          1                     0                   ACTIVE   DEDICATED SYS                            oracle
  995          1                     0                   ACTIVE   DEDICATED SYS                            oracle
  996          1                     0                   ACTIVE   DEDICATED SYS                            oracle
  997          1                     0                   ACTIVE   DEDICATED SYS                            oracle
  998          1                     0                   ACTIVE   DEDICATED SYS                            oracle
  999          1                     0                   ACTIVE   DEDICATED SYS                            oracle
 1000          1                     0                   ACTIVE   DEDICATED SYS                            oracle

 

 

 

SQL> select * from v$lock order by sid;--查看目前以上两个持锁及等待锁会话的lock信息

ADDR     KADDR      SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
-------- -------- ----- -- ---------- ---------- ---------- ---------- ---------- ----------
3DC34798 3DC347AC   976 TX      65576       1744          0          6        108          0
3D1C6BC4 3D1C6BDC   976 TM      56527          0          3          0        108          0
3D1C6B18 3D1C6B30   988 TM      56527          0          3          0        114          0
3CC346E4 3CC34708   988 TX      65576       1744          6          0        114          1
3DC349C0 3DC349D4   994 TS          3          1          3          0      88278          0
3DC347F4 3DC34808   995 RS         25          1          2          0      88285          0
3DC346E0 3DC346F4   995 XR          4          0          1          0      88290          0
3DC3473C 3DC34750   995 CF          0          0          2          0      88290          0
3DC348AC 3DC348C0   996 RT          1          0          6          0      88285          0
3DC34B30 3DC34B44   997 MR          4          0          4          0      88282          0
3DC34B8C 3DC34BA0   997 MR          5          0          4          0      88282          0
3DC34BE8 3DC34BFC   997 MR          6          0          4          0      88282          0
3DC34A78 3DC34A8C   997 MR          2          0          4          0      88282          0
3DC34CA0 3DC34CB4   997 MR          8          0          4          0      88282          0
3DC34CFC 3DC34D10   997 MR        201          0          4          0      88282          0
3DC34A1C 3DC34A30   997 MR          1          0          4          0      88282          0
3DC34AD4 3DC34AE8   997 MR          3          0          4          0      88282          0
3DC34C44 3DC34C58   997 MR          7          0          4          0      88282          0