[Tibero]세션 락 Session Lock / 실행 쿼리 상태 조회

현재 실행중인 쿼리중 lock 발생여부 확인 쿼리

 

1. LOCK발생 여부 확인

SELECT bs.user_name "Blocking User"
              ,ws.user_name "Waiting User"
              ,bs.sess_id "Blocking Sid"
              ,ws.sess_id "Waiting Sid"
              ,wk.type "Lock Type"
             ,DECODE(hk.lmode, 0, '[0]', 1, '[1]Row-S(RS)', 2, '[2]Row-X(RX)', 3, '[3]Shared(S)', 4, '[4]S/Row-S(SRX)', 5, '[5]Exclusive(X)', 6, '[6]PIN', TO_CHAR (hk.lmode) )  "Holding mode"
             ,DECODE(wk.lmode, 0, '[0]', 1, '[1]Row-S(RS)', 2, '[2]Row-X(RX)', 3, '[3]Shared(S)', 4, '[4]S/Row-S(SRX)', 5, '[5]Exclusive(X)', 6, '[6]PIN', TO_CHAR (wk.lmode) )  "Request mode"
            ,NVL(bs.sql_id, bs.prev_sql_id) || '/' || NVL2(bs.sql_id, bs.sql_child_number, bs.prev_child_number) "SQL_ID"
           ,bs.logon_time
           ,wk.status
   FROM vt_wlock hk, 
              vt_session bs,
              vt_wlock wk,
             vt_session ws
  WHERE wk.status in( 'WAITER','CONVERTER')
    AND hk.status = 'OWNER'
    AND hk.lmode > decode(wk.status,'WAITER',1,0)
    AND wk.type = hk.type
    AND wk.id1 = hk.id1
    AND wk.id2 = hk.id2
    AND wk.sess_id = ws.sess_id
    AND hk.sess_id = bs.sess_id
    AND bs.sess_id != ws.sess_id
  ORDER BY 1,3

 

 

 

2. LOCK발생 SQL_ID 로 실행되고 있는 쿼리 확인

 

 SELECT * 
 FROM V$SQL
WHERE SQL_ID = '9g6pyx7qz035v/8980'  <--Lock 발생된 SQL_ID