행위

락 처리 절차

DB CAFE

thumb_up 추천메뉴 바로가기

1 락 처리 절차[편집]

1.1 락 모니터링[편집]

1.1.1 락 모니터링 뷰 생성[편집]

-- DROP VIEW RTIS_DBA.VW_DBA_LOCKS;
CREATE OR REPLACE FORCE VIEW VW_DBA_LOCKS
(
    LVL,
    LCK_HOLD,
    LCK_WAIT,
    USERNAME,
    OSUSER,
    STATUS,
    TYPE,
    LOCK_TM,
    HOLD,
    OBJECT,
    ID1,
    ID2,
    REQUEST,
    EVENT,
    MACHINE,
    MODULE,
    PROGRAM,
    SQL_ID,
    BLOCKING_SESSION,
    CTIME,
    OBJ#,
    TYPE_NM,
    ID1_TAG,
    ID2_TAG,
    SID,
    SERIAL#,
    CMD_KILL
)
BEQUEATH DEFINER
AS
               SELECT /*+ ordered */
                      LEVEL
                          lvl,
                      NVL (s.blocking_session, s.sid)
                          lck_hold,
                      CASE WHEN s.blocking_session IS NOT NULL THEN s.sid END
                          lck_wait,
                      s.username,
                      s.osuser,
                      s.status,
                      l.TYPE
                          TYPE,
                      fn_sec_to_time (l.ctime)
                          lock_tm,
                      DECODE (lmode,
                              0, 'NONE',
                              1, 'NULL',
                              2, 'row-S(SS)',
                              3, 'row-X(SX)',
                              4, 'share(S)',
                              5, 'S/Row-X(SSX)',
                              6, 'exclusive(X)')
                          HOLD,
                      o.name
                          object --, decode(l.id2, 0, null, trunc(l.id1 / power(2,16))) undo#
                                --, decode(l.id2, 0, null, bitand(l.id1, to_number('ffff', 'xxxx')) + 0 )  slot#
                                ,
                      l.id1,
                      l.id2,
                      DECODE (l.request,
                              0, 'NONE',
                              1, 'NULL',
                              2, 'row-S(SS)',
                              3, 'row-X(SX)',
                              4, 'share(S)',
                              5, 'S/Row-X(SSX)',
                              6, 'exclusive(X)')
                          REQUEST,
                      s.event,
                      s.machine,
                      s.module,
                      s.program,
                      s.sql_id,
                      s.blocking_session,
                      l.ctime,
                      o.obj#,
                      t.name
                          TYPE_NM,
                      t.id1_tag,
                      t.id2_tag,
                      s.sid,
                      s.serial#,
                      'ALTER SYTEM KILL SESSION '''
                      || TO_CHAR (S.SID)
                      || ','
                      || TO_CHAR (S.SERIAL#)
                      || ''';'
                          CMD_KILL
                 FROM v$lock l,
                      v$session s,
                      sys.obj$ o,
                      v$lock_type t
                WHERE     1 = 1
                      AND s.TYPE != 'BACKGROUND'
                      --  and s.username = 'EP_APP'
                      --  and s.sid = 4179
                      AND l.sid = s.sid
                      AND l.TYPE IN ('TM', 'TX', 'UL')
                      AND o.obj#(+) = DECODE (L.ID2, 0, L.ID1, -1)
                      AND t.TYPE = l.TYPE
           CONNECT BY     PRIOR s.sid = s.blocking_session
                      AND PRIOR l.id1 = l.id1
                      AND PRIOR l.id2 = l.id2
           START WITH s.blocking_session IS NULL
    ORDER SIBLINGS BY lock_tm DESC, lck_hold
--order by lck_hold, l.ctime desc, l.sid;;
;

COMMENT ON TABLE RTIS_DBA.VW_DBA_LOCKS IS 'DBA_락_확인';

COMMENT ON COLUMN RTIS_DBA.VW_DBA_LOCKS.LVL IS '레벨';

1.2 lock 확인[편집]

1.2.1 lock 걸린 SERIAL_NO,SESSION_ID, OBJECT[편집]

SELECT DISTINCT t1.session_id AS session_id
               ,t2.serial# AS serial_no
               ,t1.os_user_name AS os_user_name
               ,t1.oracle_username AS oracle_username
               ,t2.status AS status
               ,t3.object_name
               ,DECODE( locked_mode
                       ,2, 'ROW SHARE'
                       ,3, 'ROW EXCLUSIVE'
                       ,4, 'SHARE'
                       ,5, 'SHARE ROW EXCLUSIVE'
                       ,6, 'EXCLUSIVE'
                       ,'UNKNOWN'
                      ) lock_mode
           FROM v$locked_object t1
              , v$session t2
              , dba_objects t3
          WHERE t1.session_id = t2.SID
            AND t1.object_id = t3.object_id;

1.2.2 락 걸린 세션 과 락을 잡은 세션[편집]

  • RAC 환경
SELECT blocking_session, blocked_session, script
    FROM (SELECT DISTINCT s1.username
                 || '@'         || s1.machine       || ' ( INST='
                 || s1.inst_id  || ' SID='          || s1.sid
                 || ' ET='      || s1.last_call_et  || 'sn. STATUS='
                 || s1.status   || ' EVENT='        || s1.event
                 || ' ACTION= ' || s1.action        || ' PROGRAM='
                 || s1.program  || ' MODULE='       || s1.module
                 || ')'                                              blocking_session
               ,    s2.username                 
                 || '@'         || s2.machine       || ' ( INST='                 
                 || s2.inst_id  || ' SID='          || s2.sid              
                 || ' ET='      || s2.last_call_et  || 'sn. STATUS='                 
                 || s2.status   || ' EVENT='        || s2.event                 
                 || ' ACTION= ' || s2.action        || ' PROGRAM='                 
                 || s2.program  || ' MODULE='       || s2.module                 
                 || ')'                                              blocked_session
               , DECODE (s1.TYPE
                         , 'USER',    'alter system kill session ''' || s1.sid  || ','   || s1.serial#  || ',@'  || s1.inst_id  || ''' immediate;'
                         , NULL)                                           script
               , COUNT (*) OVER (PARTITION BY s1.inst_id, s1.sid)    blocked_cnt
            FROM gv$lock   l1
               , gv$session s1
               , gv$lock   l2
               , gv$session s2
           WHERE s1.sid = l1.sid
             AND s2.sid = l2.sid
             AND s1.inst_id = l1.inst_id
             AND s2.inst_id = l2.inst_id
             AND l1.block > 0
             AND l2.request > 0
             AND l1.id1 = l2.id1
             AND l1.id2 = l2.id2)
ORDER BY blocked_cnt DESC;
  • 락 가해자 0 , 락 피해자 6
SELECT S.USERNAME,
       L.SID,
       L.ID1,
       TRUNC(L.ID1 / POWER(2, 16)) RBS,
       BITAND(L.ID1, TO_NUMBER('ffff', 'xxxx')) + 0 SLOT,
       L.ID2 SEQ,
       L.LMODE,
       L.REQUEST
FROM   V$LOCK    L,
       V$SESSION S
WHERE  L.SID      = S.SID
AND    L.TYPE     = 'TX'
AND    S.USERNAME = USER;

@ REQUEST 0 : 락을 보유 
@ REQUEST 6 : 락으로 인해 대기중인 상태

1.2.3 락 경합 사항 보기[편집]

 - V$LOCK 셀프조인 모니터링

SELECT (SELECT USERNAME
          FROM V$SESSION
         WHERE SID = A.SID) BLOCKER,
       A.SID,
       ' is blocking ',
       (SELECT USERNAME
          FROM V$SESSION
         WHERE SID = B.SID) BLOCKER,
       B.SID
FROM   V$LOCK A,
         V$LOCK B
WHERE  A.ID1 = B.ID1
AND    A.ID2 = B.ID2
AND    A.BLOCK   = 1 -- Lock을 잡고 있는 세션
AND    B.REQUEST > 0 ; -- Lock으로 인해 대기하는 세션

2. session_id를 *SESSION_ID*에 넣어 락 sql 찾기

-- lock 걸린 sql 

SELECT   b.username username
        ,c.SID AS session_id
        ,c.owner object_owner
        ,c.OBJECT OBJECT
        ,b.lockwait
        ,a.sql_text SQL
        ,piece
    FROM v$sqltext a
       , v$session b
       , v$access c
   WHERE a.address = b.sql_address
     AND a.hash_value = b.sql_hash_value
     AND b.SID = c.SID
     AND c.owner != 'SYS'
     and c.SID = '&SESSION_ID'
ORDER BY b.username, c.SID, c.owner, c.OBJECT, piece;

1.3 세션 kill/discount/OS process kill[편집]

  • SESSION_ID*,*SERIAL_NO* 부분에 위 1번 쿼리에서 확인한 session_id,serial_no을 사용하여 lock 걸린 session을 kill 한다.
  1. lock 걸린 SESSION kill
alter system kill session 'SESSION_ID ,SERIAL_NO[,@1]' ; --@1안 rac일경우 1번 인스턴스
  1. lock SESSION discount
alter system discount session 'SESSION_ID ,SERIAL_NO[,@1]' immediate; --@1안 rac일경우 1번 인스턴스
  1. OS Process Kill
3번의 alter system kill session을 사용하여도 죽지 않는 경우 아래 쿼리를 사용하여 PROCESS ID를 확인하여 unix에서 직접 PROCESS를 kill한다.
  * 주의!!! 실수하기 쉽고 위험한 방법으로 lock걸린 내용을 정확히 알지 못할 경우 kill하지 말것!!!

-- lock 걸린 PROCESS ID 찾기 

SELECT DISTINCT s.username "ORACLE USER"
               ,p.pid "PROCESS ID"
               ,s.SID "SESSION ID"
               ,s.serial#
               ,osuser "OS USER"
               ,p.spid "PROC SPID"
               ,s.process "SESS SPID"
               ,s.lockwait "LOCK WAIT"
           FROM v$process p, v$session s, v$access a
          WHERE a.SID = s.SID
            AND p.addr = s.paddr
            AND s.username != 'SYS'
-- unix에 로그인 하여 process kill

kill -9 프로세스아이디

1.4 락 걸린 테이블 찾아서 kill[편집]

-- 1) 헤당 테이블 찾기  
SELECT * FROM v$lock
  WHERE id1 = (SELECT object_id 
                 FROM all_objects 
                WHERE owner ='오너명'
                  AND object_name ='테이블명'
               )
-- 2) 죽이기 
SELECT * FROM v$session 
 WHERE sid = 찾은 sid값
원본 주소 "http://dbcafe.co.kr/wiki/index.php?title=락_처리_절차&oldid=5267"