Lock 충돌 해결 In RAC

이 문서는 RAC 환경에서 lock 충돌의 예와 처리 방법을 설명하고 있다.

 Distribution Lock은 RAC를 사용하는데 있어서 가장 복잡한 이슈 중에 하나이다. 이와 같은 복
잡성은 OPS Time을 줄여주지 못한다. 대부분의 이슈는 x$와 gv$를 조회하여 해결 될 수 있다. 그
러나, 대부분의 복잡한 문제는 System Statedump 또는 oradebug를 사용해야 해결 할 수 있다.

++++++++++++++++++
+++ 기본 정의
+++++++++++++++++++

distributed : 인스턴스 A에 존재하는 세션이 다른 인스턴스 B에서 잡고 있는 row에 대한 lock을
              잡으려고 하며 대기하는 것.

local       : 인스턴스 A에 존재하는 세션이 같은 인스턴스 A에 다른 세션에서 잡고 있는 row에
              대한 lock을 잡으려고 하며 대기하는 것.

+++++++++++++++++++++++++++
+++ locking resolution
+++++++++++++++++++++++++++

 시스템이 HALT 그리고, 모든 작업이 중지된 이후에 인지하며, 모든 노드의 cpu idle는 0%이면 
시스템이 Hang 처럼 보인다. Hang은 발생 가능 하지만, 대부분 RAC를 다룰 때 distributed locki
ng problem의 문제일 수 있다.

【STEP 1】

DBA로 인스턴스에 접속하여 distributed locking problem 여부를 확인한다.

다음 sql은 distributed 와 lock의 모든 유형의 lock을 보여준다.

SQL> SELECT lh.inst_id Locking_Inst, lh.sid Locking_Sid,
            lw.inst_id Waiting_Inst, lw.sid Waiter_Sid,
     decode ( lh.type, 'MR', 'Media_recovery',
                       'RT', 'Redo_thread',
                       'UN', 'User_name',
                       'TX', 'Transaction',
                       'TM', 'Dml',
                       'UL', 'PLSQL User_lock',
                       'DX', 'Distrted_Transaxion',
                       'CF', 'Control_file',
                       'IS', 'Instance_state',
                       'FS', 'File_set',
                       'IR', 'Instance_recovery',
                       'ST', 'Diskspace Transaction',
                       'IV', 'Libcache_invalidation',
                       'LS', 'LogStaartORswitch',
                       'RW', 'Row_wait',
                       'SQ', 'Sequence_no',
                       'TE', 'Extend_table',
                       'TT', 'Temp_table',
                       'Nothing-' ) Waiter_Lock_Type,
     decode ( lw.request,  0, 'None',
                           1, 'NoLock',
                           2, 'Row-Share',
                           3, 'Row-Exclusive',
                           4, 'Share-Table',
                           5, 'Share-Row-Exclusive',
                           6, 'Exclusive',
                           'Nothing-' ) Waiter_Mode_Req
      FROM gv$lock lw, gv$lock lh
     WHERE lh.id1=lw.id1
       AND lh.id2=lw.id2
       AND lh.request=0
       AND lw.lmode=0
       AND (lh.id1,lh.id2) in (
                                 SELECT id1,id2 FROM gv$lock WHERE request=0
                                 INTERSECT
                                 SELECT id1,id2 FROM gv$lock WHERE lmode=0 );


  참고 : lmode=0 대기하는 세션, request=0 락을 잡은 세션, intersect는 교집합을 구하는 것으
         로 락은 잡은 세션과 잡으려고 하는 세션이 동일한 id1,id2를 가진 경우

테스트는 4 node sun cluster in 9.2 이며, 인스턴스 수는 1~4 이다.

LOCKING_INST LOCKING_SID WAITING_INST WAITER_SID WAITER_LOCK_TYPE      WAITER_MODE_REQ
------------ ----------- ------------ ---------- --------------------- ---------------
           1          41            2         37 Transaction Exclusive
           4          22            1         41 Transaction Exclusive

 인스턴스 4번의 22번 세션은 lock을 잡고 있으며 인스턴스 1번의 41 세션이 대기 중이며 인스턴
스 1번의 41에 의해서 인스턴스 2번의 37번이 대기 중이다.

4.22 --------> 1.41 --------> 2.37

【STEP 1】

locking session 세션의 username과 object를 알아본다.

STA == ACT ========> waiting
STA == INA ========> locked(잡고 있다.)

SQL> SELECT a.inst_id,
            a.session_id,
            b.serial#,
            b.status,
            a.oracle_username,
            a.os_user_name,
            a.process, c.name
       FROM sys.obj$ c,
            gv$session b,
            gv$locked_object a
      WHERE a.session_id=b.sid
        AND c.obj#=a.object_id;
    

INST_ID  SID  SER#   STA  ORACLE_USERNAME  OS_USER_NAME  Os Proc  Locked Object
-------- ---- ------ ---- ---------------- ------------- -------- --------------
       1   41   9123 ACT  RECOVER          oracle          8279   LOCK_TEST
       1   41     59 INA  RECOVER          oracle          8279   LOCK_TEST
       1   41   4316 INA  RECOVER          oracle          8279   LOCK_TEST
       2   37    952 ACT  RECOVER          oracle          9189   LOCK_TEST
       2   37    216 INA  RECOVER          oracle          9189   LOCK_TEST
       2   37    188 INA  RECOVER          oracle          9189   LOCK_TEST
       4   22     15 INA  RECOVER          oracle         15149   LOCK_TEST
       4   22     32 INA  RECOVER          oracle         15149   LOCK_TEST
       4   22   2703 INA  RECOVER          oracle         15149   LOCK_TEST

 이 시점에 가장 안쪽의 lock을 대기하는 kill 할 것이다.(인스턴스 2의 37 세션) 문제 해결은 
간단하다. 그러나, 어떤 쿼리가 문제를 유발 했는지 알고자 한다면 다음 쿼리를 이용한다.

SQL> select s.inst_id, substr(u.username,1,12) user_name, s.sql_text
       from gv$sql s,
            gv$session u
      where s.hash_value = u.sql_hash_value
        and s.inst_id = u.inst_id
        and lower(sql_text) not like '%from v$sql s, v$session u%'
        and sid in (37, 41, 22);


INST_ USER_NAME SQL_TEXT
----- --------- --------------------------------------------------------------------------
    1 RECOVER   select OBJECT_NAME from lock_test where OBJECT_NAME =:"SYS_B_0" for update
    2 RECOVER   select OBJECT_NAME from lock_test where OBJECT_NAME =:"SYS_B_0" for update