[Oracle] 오라클 Lock(1) enq: TX - row lock contention

[Oracle] 오라클 Lock(1) enq: TX - row lock contention

 

안녕하세요. 갓대희 입니다. 이번 포스팅은 [ [Oracle] Lock 첫번째 TX - row lock contention ] 입니다. : ) 

 - 오라클 모니터링을 하다보면 Active 세션 중 다음과 같은 락발생 현황을 종종 볼 수 있다. 이와 관련된 내용을 알아 보도록 하자.

 

0.Oracle Lock

 - 사실 오라클 락은 매우 다양한 종류가 있지만, 이번엔 간단히  자주 볼수있는 Lock의 종류중 하나인 TX Lock에 대해 알아 보려 한다.  TX Lock과 관련된 내용을 천천히 알아보자.

 

1) Enqueue Lock

 - 테이블, 트랜젝션, 테이블스페이스, 시퀀스, Temp 세그먼트 등의 공유 리소스에 대한 액세스를 관리하는 Lock 메커니즘 이다. Queue 구조를 사용하기 때문에 순서가 보장되지만 이로 인해 무한 대기에 빠지는, 우리가 은히 겪는 Lock 현상이 발생하게 된다.

 - 대기큐자 에 가장 먼저 Lock 요청을 등록한 세션이 가장 먼저 Lock을 획득 한다.

 

1. TX Lock

1) Enq: TX-row lock contention

 - 로우 레벨 락(row level lock)에 의한 경합 발생 상황이다.
 - 트랜젝션이 첫 번째 변경을 시작할 때 Undo 세그먼트 헤더에 위치한 트랜젝션 테이블로부터 슬롯을 하나 할당받아 는다.
 - TX 락은 트랜잭션이 첫 번째 변경을 시작할 때 획득하며, 커밋 또는 롤백할 때 해제 한다.
 - 다른 세션의 트랜잭션이 완료될 때까지 기다릴 수 있는 큐잉 매커니즘으로 사용 한다.

 

※참고

대기 이벤트 이름	Lock 모드	원인
enq: TX - row lock contention	Exclusive(6)	DML 로우 Lock
	Shared(4)	무결성 제약 위배 가능성 또는 비트맵 인덱스 엔트리 갱신 등
enq: TX - allocate ITL entry	Shared(4)	ITL 부족
enq: TX - index contention	Shared(4)	인덱스 분할
enq: TX - contention	Shared(4)	읽기 전용 테이블스페이스, PREPARED TxN(2PC), Free Lists 등등

 

2. TX-row lock 경합이 발생하는 Case
 - 여러 세션이 동일 로우를 변경하는 경우
 - 여러 세션이 동일 Unique Key 충돌을 일으키는 경우
 - 여러 세션이 비트맵 인덱스 충돌을 일으키는 경우 

3. Rock 조회 방법
 - TX Lock 경합 상황 모니터링 : v$lock 뷰를 통해 조회할 수 있다.
 - V$TRANSACTION : 활동 중인 모든 트랜잭션에 대한 Entry 정보 조회 가능.
 - V$SESSION : 로그인 User의 세션 조회 가능.

 

EX) 현재 세션 중 Active 상태 조회

SELECT 	SID
        , SERIAL#
        , MODULE
        , PROGRAM 
        , STATUS 
        , SQL_ID 
        , EVENT 
FROM    V$SESSION
WHERE   TYPE <> 'BACKGROUND' AND STATUS='ACTIVE';

 

 - V$LOCK : 보유 중인 모든 enqueue 락에 대한 엔트리를 포함하고 있다.

EX) LOCK TYPE이 TX이 USER 및 Lmode 조회

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 = '유저이름'

ㆍ REQUEST => 0 : 락을 보유, 작업이 끝나지 않은 상태, 4, 6 :  락으로 인해 대기중인 상태, 작업이 끝나기 전에는 같은 로우를 시도하려는 유져들
  ※참고 mode(4) : Unique Key 충돌을 일으키는 경우, mode(6) : 여러 세션이 동일 로우를 변경하는 경우

ㆍ 현재 34번 세션이 65번 세션의 진행을 블로킹 중인 것을 볼 수 있다..(block = 1)
ㆍ 정상적인 대기가 아니라고 판단시 Request4, sid 65번은 Kill 대상이다.

 

EX) 락을 좀더 자세히 확인할 수 있는 쿼리

SELECT 	A.SID, A.SERIAL#,A.USERNAME,A.PROCESS,B.OBJECT_NAME,
        DECODE(C.LMODE,2,'RS',3,'RX',4,'S',5,'SRX',8,'X','NO') "TABLE LOCK",
        DECODE(A.COMMAND,2,'INSERT',3,'SELECT',6,'UPDATE',7,'DELETE',12,'DROPTABLE',26,'LOCK
        TABLE','UNKNOWN') "SQL",
        DECODE(A.LOCKWAIT, NULL,'NO WAIT','WAIT') "STATUS"
FROM    V$SESSION A,DBA_OBJECTS B, V$LOCK C
WHERE   A.SID=C.SID AND B.OBJECT_ID=C.ID1
AND     C.TYPE='TM';

 

EX) 간단하게 블록킹 및 WAITNG SID 조회

SELECT  'USERNAME : ' || (SELECT USERNAME FROM V$SESSION WHERE SID = A.SID) || ', SID : ' || A.SID AS BLOCKING,
        ' IS BLOCKING ',
        'USERNAME : ' || (SELECT USERNAME FROM V$SESSION WHERE SID = B.SID) || ', SID : ' || B.SID AS BLOCKED
FROM    V$LOCK A,
        V$LOCK B
WHERE 	A.ID1 = B.ID1
AND 	A.ID2 = B.ID2
AND 	A.BLOCK = 1
AND 	B.REQUEST > 0;

 

 - V$SESSION_WAIT : TX Lock의 발생 원인 조회

EX)

SELECT  SID, SEQ#, EVENT, STATE, SECONDS_IN_WAIT, P1, P2, P3
FROM    V$SESSION_WAIT
WHERE   EVENT LIKE 'enq: TX%';

 

 - V$TRANSACTION : TRANSACTION 조회

EX) 

SELECT  XIDUSN,
        XIDSLOT,
        XIDSQN,
        TO_CHAR(XIDUSN, 'XXXX') || TO_CHAR(XIDSLOT, 'XXXX') || TO_CHAR(XIDSQN, 'XXXX')
        TO_HEX_XID,
        XID AS 트랜잭션ID
FROM    V$TRANSACTION;

 

 - SID별 실행 쿼리 추출

SELECT  A.USERNAME
        , A.PROGRAM
        , A.MACHINE
        , A.MODULE
        , B.SPID
        , A.SID
        , A.SERIAL#
        , A.STATUS
        , C.SQL_TEXT
FROM    V$SESSION A, V$PROCESS B, V$SQL C
WHERE   A.SID IN (SELECT    SID
                  FROM      V$LOCK
                  WHERE     TYPE = 'TX'
        ) -- sid 입력
AND     B.ADDR = A.PADDR
AND     A.SQL_HASH_VALUE = C.HASH_VALUE(+)
AND     A.SQL_ADDRESS = C.ADDRESS(+);

 

2) 락 발생 원인 강제 종료

 - 위에서 여러 쿼리를 통해 락 발생 원인, 실제 쿼리 등을 확인하여 락의 원인이 밝혀졌다면, TX-Row Lock 이벤트 세션 강제 종료 시킨다.

 - 나와 같은 경우는 바로 위의 예제에서 SID : 34, SERIAL# : 43을 죽일 예정이다.

 - Alter system kill session ‘해당SID,시리얼#‘ ; 

Alter system kill session 'SID,시리얼#'; 

 

 - EX) 락을 좀더 자세히 확인할 수 있는 쿼리를 통해 락 해소 확인

SELECT 	A.SID, A.SERIAL#,A.USERNAME,A.PROCESS,B.OBJECT_NAME,
        DECODE(C.LMODE,2,'RS',3,'RX',4,'S',5,'SRX',8,'X','NO') "TABLE LOCK",
        DECODE(A.COMMAND,2,'INSERT',3,'SELECT',6,'UPDATE',7,'DELETE',12,'DROPTABLE',26,'LOCK
        TABLE','UNKNOWN') "SQL",
        DECODE(A.LOCKWAIT, NULL,'NO WAIT','WAIT') "STATUS"
FROM    V$SESSION A,DBA_OBJECTS B, V$LOCK C
WHERE   A.SID=C.SID AND B.OBJECT_ID=C.ID1
AND     C.TYPE='TM';

1) Kill Session 이전 결과

2) Kill Session 이후 결과

 

 - SID 34로 인해 SID 65번이 대기 중이 였기 때문에 Kill 이전엔 Insert Wait 상태였지만, SID 65의 강제 종료 이후 commit 처리가 되지 않았지만 WAIT현상이 해소된 것을 볼 수 있다.

 - 물론 이렇게 실제 운영상황에서는 한건의 락 상황이 아닌 대량의 락 발생이 발생했을 것이며, 그런 경우엔어쩔수 없이 락 원인이 되는 세션을 순차적으로 계속 Kill 해줘야 할 것 같다.