ロックしているセッションとロックされているセッションを特定する

基本的にV$SESSIONをみればロックで待たされているセッションと、ロックしているセッションがわかります。BLOCKING_SESSION_STATUS=’VALID’の条件で、現在待たされているセッションを特定しす。

ロック対象セッションの特定SQL

select
 ws.SID          -- 待たされているセッションID
,ws.SERIAL#  
,ws.STATUS
,ws.LAST_CALL_ET -- 今の状態になってからの経過時間
,ls.SID          -- ロックしているセッションID
,ls.SERIAL#
,ls.STATUS
,ls.LAST_CALL_ET -- 今の状態になってからの経過時間
from v$session ws inner join v$session ls
on ws.BLOCKING_SESSION = ls.SID
and ws.BLOCKING_SESSION_STATUS='VALID'
;

ロック対象セッションの特定SQL（ロック対象テーブルも表示）

実際現場ではRAC環境である事や、その他の情報もあわせて見たいので、これにdba_objectsや、v$locked_objectを結合して表示させています。

select
----待たされている側のsession情報
 ws.INST_ID
,ws.SID
,ws.SERIAL#
,ws.USERNAME 
,ws.LOCKWAIT         -- ロックのアドレス。ロックがない場合はNULL
,ws.STATUS
,ws.LAST_CALL_ET     -- 今の状態になってからの経過時間
,ws.SQL_ID           -- 待たされているSQL
,ws.EVENT
-- ロックが発生しているオブジェクト
,o.OBJECT_ID
,o.OWNER
,o.OBJECT_NAME
,ws.ROW_WAIT_BLOCK#  -- ロック対象行の存在するブロック
,ws.row_wait_row#    -- ロック対象オブジェクトの対象行
--- 待ちの原因となっているセッション
,ls.INST_ID
,ls.SID
,ls.SERIAL#
,ls.USERNAME
,ls.SQL_ID
,ls.BLOCKING_SESSION_STATUS    -- 当セッションをブロックしているセッションの有無
,ls.STATUS
,ls.LAST_CALL_ET       -- 今の状態になってからの経過時間
,decode(k.LOCKED_MODE
        ,0, 'ロック要求中'     -- まだ取得できていない
        ,1, 'NULL' 
        ,2, '行共有(SS)'       -- 行共有ロック
        ,3, '行排他(SX)'       -- 行排他表ロック
        ,4, '共有(S)'          -- 共有表ロック
        ,5, '共有行排他(SRX)'  -- 共有行排他表ロック
        ,6, '排他(X)'          -- 排他表ロック
        ,'???' ) locked_mode_dec
,ls.PROGRAM
,ls.MACHINE
,ls.EVENT
from gv$session ws        --待たされているセッション
inner join gv$session ls  --ロックしているセッション（トランザクション発生中）
on  ws.BLOCKING_INSTANCE = ls.INST_ID
and ws.BLOCKING_SESSION  = ls.SID
inner join gv$locked_object k 
on ls.SID = k.SESSION_ID
left outer join dba_objects o 
on ws.ROW_WAIT_OBJ# = o.OBJECT_ID
where ws.BLOCKING_SESSION_STATUS ='VALID'
;