v$session之小測試（一）_與v$lock

 本次測試目的為：根據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