oracle lock 이란?

Latch가 SGA영역을 보호 한다면 Lock은 데이터 베이스를 보호 한다고 보면 된다.  Lock은 Latch와 다르게 복잡한 메카니즘을 통하여 관리가 된다.

또, Lock은 관리 방법에 따라서 일반 Lock과 Enqueue Lock으로 분류를 할 수 있다.

enqueue의 구조는 버퍼 캐쉬 아키텍처와 유사하다.

enqueue hash chains latch (1: m) hash bucket (1:1) enqueue hash cahin 

여기서 enqueue hash chains latch의 개수는 기본적으로 CPU_COUNT와 동일하고 _ENQUEUE_HASH_CHAIN_LATCHES 파라메터로

수정이 가능하다.

Enqueue에 대해서 좀더 구조를 확인 하고 싶으면 다음 명령을 이용하여 Dump를 수행 할 수 있다.

alter session set events ’immediate trace name enqueues level 3’;

Enqueue Lock은 Enqueue라는 구조체로 관리 되는 lock이다. 이 구조체는 shared pool 영역에 저장이 된다.

Enqueue 구조체에 의해서 관리되는 lock은 우리가 일반적으로 아는 Object 관련 lock이라고 생각하면 된다. 예를 들면 TX,TM,UL, CF,US……

등등의 lock이 Enqueue 구조체로 관리되는 enqueue lock 이다.

enqueue lock 획득을 시도한 프로세스가 획득에 실패 할 경우 Enqueue 구조체 안에 waiter list에 자신을 등록한다.

기존에 획득했던 프로세스가 lock을 해제 할 경우 이 waiter list안의 프로세스를 깨워 준다. waiter list에 등록된 프로세스는 3초뒤에 다시 깨어나서 deadlock이 발생했는지 체크 후 다시 대기 상태로 돌아간다. 이는 latch와는 다르게 lock은 deadlock이 발생 할 가능성이 있기 때문이다.

일반적인 lock 구조 즉 Owner list / Waiter list를 자체 관리하는 방식으로는 row cache lock , library cache lock , library cache pin ,

buffer lock 등이 있다. 일반 lock도 deadlock이 발생 할 가능성이 있으므로 타임아웃 시간을 가진 후 깨어나서 deadlock을 체크 하게 되는데

각각의 lock 마다 시간이 틀리다. rowcache lock은 60초 까지 기다리며 buffer lock은 기본적으로 1초 지만 연속적인 buffer lock 획득을 위한 경우 3초이다. library cache lock과 library cache pin은 3초를 기다린다.

Enqueue lock 경합이 발생하면 v$lock 뷰의 TYPE , ID1 , ID2값을 확인하면 된다. 이 값은 해당 시점에 v$session_wait뷰에서도 enqueue wait을 하는 세션의 P1=name|mode , P2=ID1 , P2=ID2 를 확인해도 알수 있다.

그럼 일반 lock 경합은 어떻게 확인이 가능할까 ? v$session_wait 뷰에서의 대기 현상을 모니터링 해야 한다.

V$LOCK 뷰의 컬럼들을 확인해 보면 다음과 같다.

ADDR    :  Address of lock state object
KADDR   :  Address of lock
SID     :  Identifier for session holding or acquiring the lock
TYPE    :  Type of user of system lock
           System lock의 종류는 밑에 다시 정리 하겠슴
ID1     :  Lock identifier #1 (depend on type)
ID2     :  Lock identifier #2 (depend on type)
LMODE   :  Lock mode in which the session holds the lock
           0 – none / 1 – null / 2 – row-S(SS) / 3 – row-X(SX) / 4 – share(S)
           5 – S/Row-X(SSX) / 6 – exclusive(X)
REQUEST :  Lock mode in which the process requests the lock
           0 – none / 1 – null / 2 – row-S(SS) / 3 – row-X(SX) / 4 – share(S)
           5 – S/Row-X(SSX) / 6 – exclusive(X)
CTIME   :  Time since current mode was granted
BLOCK   :  A value of the either 0 or 1. Depending on whether or not lock in question is the blocker

System Lock의 종류는 다음과 같다.

BL :  Buffer hash table instance
NA..NZ : Library cache pin instance (A..Z = namespace)
CF : Control file schema global enqueue
PF : Password File
CI : Cross-instance function invocation instance
PI, PS : Parallel operation
CU : Cursor bind
PR : Process startup
DF : datafile instance
QA..QZ : Row cache instance (A..Z = cache)
DL : Direct loader parallel index create
RT : Redo thread global enqueue
DM : Mount/startup db primary/secondary instance
SC : System change number instance
DR : Distributed recovery process
SM : SMON
DX : Distributed transaction entry
SN : Sequence number instance
FS : File set
SQ : Sequence number enqueue
HW : Space management operations on a specific segment
SS : Sort segment
IN : Instance number
ST : Space transaction enqueue
IR : Instance recovery serialization global enqueue
SV : Sequence number value
IS : Instance state
TA : Generic enqueue
IV : Library cache invalidation instance
TS : Temporary segment enqueue (ID2=0)
JQ : Job queue
TS : New block allocation enqueue (ID2=1)
KK : Thread kick
TT : Temporary table enqueue
LA .. LP : Library cache lock instance lock (A..P = namespace)
UN : User name
MM : Mount definition global enqueue
US : Undo segment DDL
MR : Media recovery
WL : Being-written redo log instance
 
v$enqueue_lock 아라는 뷰도 있는데 이 뷰의 컬럼들도 v$lock 과 같다. 그러나 v$enqueue_lock 뷰는 블로킹이 발생한 경우 대기 세션에 대한 정보만 조회 된다. 즉 큐에서 대기 중인 세션의 정보만 보여준다. 블로킹 세션에 대한 정보는 dba_waiters뷰에서 조회해 보아도 된다.

또 v$locked_object 뷰는 현재 시스템에서 획득중인 TM락에 대한 정보를 제공해 주므로 dba_objects와 조인해서 object 정보 확인이 가능하다.

그럼 enqueue lock이 아닌경우는 어디서 확인을 해야 하는 것인가 ? library cache lock 같은 경우는 dba_ddl_locks나 dba_kgllock , 또는 x$kgllk 뷰를 통해서 관찰이 가능하고 library cache pin 같은 경우는 dba_kgllock 뷰나 x$kglpn 뷰를 통해서 가능하다.

일반적인 lock인경우 v$sesion_wait뷰에서 P1값을 가지고 확인해 볼 수 있는 쿼리이다.

SELECT  chr(bitand(P1,-16777216)/16777215)||chr(bitand(P1,16711680)/65535) “Name” , bitand(P1,65535) “Mode” FROM dual ;

————————————————————————————————–

 Subject:  HOW TO FIND THE SESSION HOLDING A LIBRARY CACHE LOCK 
  Doc ID:  Note:122793.1 Type:  TROUBLESHOOTING 
  Last Revision Date:  25-JUL-2005 Status:  PUBLISHED

PURPOSE
——-

 In some situations it may happen your session is ‘hanging’ and is awaiting for 
 a ‘Library cache lock’. This document describes how to find the session that 
 in fact has the lock you are waiting for.
 

SCOPE & APPLICATION
——————-

Support analysts, dba’s, ..

HOW TO FIND THE SESSION HOLDING A A LIBRARY CACHE LOCK
——————————————————
 
 Common situations:

 * a DML operation that is hanging because the table which is accessed is currently 
   undergoing changes (ALTER TABLE). This may take quite a long time depending on 
   the size of the table and the type of the modification 
   (e.g. ALTER TABLE x MODIFY (col1 CHAR(200) on thousands of records).

* The compilation of package will hang on Library Cache Lock and Library Cache Pin 
  if some users are executing any Procedure/Function defined in the same package.

 In the first situation the V$LOCK view will show that the session doing the 
 ‘ALTER TABLE’ has an exclusive DML enqueue lock on the table object (LMODE=6, 
 TYPE=TM and ID1 is the OBJECT_ID of the table). The waiting session however does 
 not show up in V$LOCK yet so in an environment with a lot of concurrent sessions 
 the V$LOCK information is insufficient to track down the culprit blocking your 
 operation.

METHOD 1: SYSTEMSTATE ANALYSIS
——————————

 One way of finding the session blocking you is to analyze the system state dump.
 Using the systemstate event one can create a tracefile containing detailed 
 information on every Oracle process. This information includes all the resources
 held & requested by a specific process.

 Whilst an operation is hanging, open a new session and launch the following
 statement:

 ALTER SESSION SET EVENTS ‘IMMEDIATE TRACE NAME SYSTEMSTATE LEVEL 10’;

 Oracle will now create a systemstate tracefile in your USER_DUMP_DEST directory.
 Get the PID (ProcessID) of the ‘hanging’ session from the V$PROCESS by matching 
 PADDR from V$SESSION with ADDR from V$PROCESS:

 SELECT PID FROM V$PROCESS WHERE ADDR=
        (SELECT PADDR FROM V$SESSION WHERE SID=sid_of_hanging_session);

 The systemstate dump contains a separate section with information for each 
 process. Open the tracefile and do a search for ‘PROCESS pid_from_select_stmt’.
 In the process section look up the wait event by doing a search on ‘waiting for’.

 Example output:

 PROCESS 8:
   —————————————-
   SO: 50050b08, type: 1, owner: 0, flag: INIT/-/-/0x00
   (process) Oracle pid=8, calls cur/top: 5007bf6c/5007bf6c, flag: (0) –
             int error: 0, call error: 0, sess error: 0, txn error 0
   (post info) last post received: 82 0 4
               last post received-location: kslpsr
               last process to post me: 5004ff08 1 2
               last post sent: 0 0 13
               last post sent-location: ksasnd
               last process posted by me: 5004ff08 1 2
     (latch info) wait_event=0 bits=0
     Process Group: DEFAULT, pseudo proc: 50058ac4
     O/S info: user: daemon, term: pts/1, ospid: 15161
     OSD pid info: 15161
     —————————————-
     SO: 5005f294, type: 3, owner: 50050b08, flag: INIT/-/-/0x00
     (session) trans: 0, creator: 50050b08, flag: (41) USR/- BSY/-/-/-/-/-
               DID: 0001-0008-00000002, short-term DID: 0000-0000-00000000
               txn branch: 0
               oct: 6, prv: 0, user: 41/LC
     O/S info: user: daemon, term: pts/1, ospid: 15160, machine: goblin.forgotten.realms
               program: sqlplus@goblin.forgotten.realms (TNS V1-V3)
     application name: SQL*Plus, hash value=3669949024
     waiting for ‘library cache lock’ blocking sess=0x0 seq=253 wait_time=0
!>>              handle address=5023ef9c, lock address=5019cad4, 10*mode+namespace=15

 Using the ‘handle address’ you can look up the process that is keeping a lock
 on your resource by doing a search on the address within the same tracefile.

 Example output:

 PROCESS 9:
   —————————————-
   SO: 50050e08, type: 1, owner: 0, flag: INIT/-/-/0x00
   (process) Oracle pid=9, calls cur/top: 5007bbac/5007bbfc, flag: (0) –
             int error: 0, call error: 0, sess error: 0, txn error 0
 
   <cut> ….
  
        —————————————-
         SO: 5019d5e4, type: 34, owner: 5015f65c, flag: INIT/-/-/0x00
!>>      LIBRARY OBJECT PIN: pin=5019d5e4 handle=5023ef9c mode=X lock=0
         user=5005fad4 session=5005fad4 count=1 mask=0511 savepoint=118218 flags=[00]
  
 From the output we can see that the Oracle process with PID 9 has an exclusive
 lock on the object we are trying to access. Using V$PROCESS and V$SESSION we can
 retrieve the sid,user,terminal,program,… for this process. The actual statement
 that was launched by this session is also listed in the tracefile (statements and
 other library cache objects are preceded by ‘name=’).

METHOD 2: EXAMINE THE X$KGLLK TABLE
———————————–

 The X$KGLLK table (accessible only as SYS/INTERNAL) contains all the 
 library object locks (both held & requested) for all sessions and
 is more complete than the V$LOCK view although the column names don’t
 always reveal their meaning.

 You can examine the locks requested (and held) by the waiting session 
 by looking up the session address (SADDR) in V$SESSION and doing the 
 following select:

 select * from x$kgllk where KGLLKSES = ‘saddr_from_v$session’

 This will show you all the library locks held by this session where
 KGLNAOBJ contains the first 80 characters of the name of the object.
 The value in KGLLKHDL corresponds with the ‘handle address’ of the
 object in METHOD 1.

 You will see that at least one lock for the session has KGLLKREQ > 0 
 which means this is a REQUEST for a lock (thus, the session is waiting). 
 If we now match the KGLLKHDL with the handles of other sessions in 
 X$KGLLK that should give us the address of the blocking session since
 KGLLKREQ=0 for this session, meaning it HAS the lock.
 
 SELECT * FROM X$KGLLK LOCK_A 
 WHERE KGLLKREQ = 0
   AND EXISTS (SELECT LOCK_B.KGLLKHDL FROM X$KGLLK LOCK_B
               WHERE KGLLKSES = ‘saddr_from_v$session’ /* BLOCKED SESSION */
               AND LOCK_A.KGLLKHDL = LOCK_B.KGLLKHDL
               AND KGLLKREQ > 0);

 If we look a bit further we can then again match KGLLKSES with SADDR 
 in v$session to find further information on the blocking session:

 SELECT SID,USERNAME,TERMINAL,PROGRAM FROM V$SESSION
 WHERE SADDR in 
  (SELECT KGLLKSES FROM X$KGLLK LOCK_A 
   WHERE KGLLKREQ = 0
     AND EXISTS (SELECT LOCK_B.KGLLKHDL FROM X$KGLLK LOCK_B
                 WHERE KGLLKSES = ‘saddr_from_v$session’ /* BLOCKED SESSION */
                 AND LOCK_A.KGLLKHDL = LOCK_B.KGLLKHDL
                 AND KGLLKREQ > 0)
  );

 In the same way we can also find all the blocked sessions:

 SELECT SID,USERNAME,TERMINAL,PROGRAM FROM V$SESSION
 WHERE SADDR in 
  (SELECT KGLLKSES FROM X$KGLLK LOCK_A 
   WHERE KGLLKREQ > 0
     AND EXISTS (SELECT LOCK_B.KGLLKHDL FROM X$KGLLK LOCK_B
                 WHERE KGLLKSES = ‘saddr_from_v$session’ /* BLOCKING SESSION */
                 AND LOCK_A.KGLLKHDL = LOCK_B.KGLLKHDL
                 AND KGLLKREQ = 0)
  );