오라클의 Lock

오라클은 공유리소스와 사용자 데이터를 보호할 목적으로 여러가지 장치를 준비하고 있다.

그 중, 동시 접근하여 정합성을 망가뜨릴 수 있는 위험으로부터 보호해주는 Lock이라것이 있다. (Java의 syncronized와도 같다.)

Lock은 어떤 부분을 동시에 접근못하고 동시간에 오직 한 녀석만이 사용하게 하는 것을 보장하는 개념이다.

단지, 그 영역의 범위를 어디까지 하나로, 여러가지 개념들이 태어나게 되는 것이다. 

이 장에서 볼것은 DML Lock에 대해 알아보려 한다.

DML Lock은 지금까지 수차 이야기한 데이터의 무결성을 보호해주기 위한 장치이다. 

다음과 같은 내용을 알아보겠다.

1. Enqueue Lock
2. TX Lock
3. TM Lock
4. 커밋

DML Lock을 이해하기 위해, 1번부터 살펴보겠다..

(1) Enquenue Lock

이름에서도 알 수 있듯, Enqueue Lock은 큐구조를 사용하여 요청을 적재한다.

Enqueue에 의해 보호되는 리소스는 다음과 같다. (v$resource에서 현재 할당된 리소스를 확인할 수 있다.)

• 테이블
• 트랜잭션
• 테이블스페이스
• 시퀀스
• 임시세그먼트
• 등등...

Enqueue리소스는 소유자, 대기자 목록을 관리할 수 있는 구조체로 되어 있다.

식별자는 <TYPE-ID1-ID2>로, 그 내용은 다음과 같다.

TM Lock의 식별자

TM-오브젝트ID-0

TX Lock의 식별자

TX-Undo세그번트번호+트랜잭션슬롯번호-트랜잭션슬롯Sequence번호

Enqueue 리소스구조체는 리소스 테이블에 통합관리 되며, 리소스를 찾을 때는 해싱알고리즘을 사용한다.

아래 그림은 리소스 구조체를 표현한 것이다.

각 리소스의 고유식별는 소유자 목록과 대기자 목록을 갖는다.

소유자 목록에 소유자가 존재하면, 리소스를 얻지못하고 대기자 목록에 등록한다.

소유자 목록의 소유자가 작업이 끝나 리소스의 소유권을 대기자목록 1순위에게 넘기면, 비로소 소유자목록에 등록되어 소유하게 된다.

단, 리소스를 점유하려는 세션에는  Shared모드와 Exclusive모드가 있어, Shared모드일경우는 공유가 가능하다.

이를테면, 현재 소유자가 Shared모드이고 나도 Shared모드라면 소유자목록에 자신을 등록, 해당 리소스의 Lock을 얻을 수 있다.

그러나, 현재소유자 혹은 내가 Exclusive모드라면 공유하지 못한다..

다음의 경우들을 살펴 보자.

대상세션 : Shared모드

1. 테이블T의 락을 얻기 위해, 리소스 테이블을 뒤지기 시작.
2. 테이블T의 고유식별자 발견. 
3. 소유자 목록 체크 → 비어있다
   
4. 자신을 소유자 목록에 등록. 해당 리소스의 Lock을 취득.

1. 테이블T의 락을 얻기 위해, 리소스 테이블을 뒤지기 시작.
2. 테이블T의 고유식별자 발견. 
3. 소유자 목록 체크 → 누군가 있다 → Shared모드의 세션이 소유중
   
4. 자신을 소유자 목록에 등록. 해당 리소스의 Lock을 취득.

1. 테이블T의 락을 얻기 위해 리소스 테이블을 뒤지기 시작.
2. 테이블T의 고유식별자 발견. 
3. 소유자 목록 체크 → 누군가 있다 → Exclusive모드의 세션이 소유중
   
4. 자신을 대기자 목록에 등록. 대기한다.

대상세션 : Exclusive모드

1. 테이블T의 락을 얻기 위해, 리소스 테이블을 뒤지기 시작.
2. 테이블T의 고유식별자 발견. 
3. 소유자 목록 체크 → 비어있다
   
4. 자신을 소유자 목록에 등록. 해당 리소스의 Lock을 취득.

1. 테이블T의 락을 얻기 위해, 리소스 테이블을 뒤지기 시작.
2. 테이블T의 고유식별자 발견. 
3. 소유자 목록 체크 → 누군가 있다
   
4. 자신을 대기자 목록에 등록. 대기한다.
   

(2) TX Lock(트랜잭션 Lock)

우리는 문장읽기수준 일관성이라는 부분에서 타 트랜잭션이 읽기를 수행하고 있어도 블로킹없이 읽기를 진행할 수 있음을 공부했다.

그러나, 변경 중인 레코드를 동시에 변경하려는 트랜잭션에 대해서는 블로킹작업이 필요하다. (액세스 직렬화)

해당식별자를 갖는 리소스 구조체를 Enqueue리소스 테이블의 해쉬체인에 연결.

해당 트랜잭션을 소유자목록에 등록함으로써 Lock을 획득하게 된다. (.※식별자와 Enqueue리소스 테이블에 대해서는 윗절 참조)

그리고, 해당 트랜잭션의 일련의 작업이 수행된다

이것이, TX Lock이다.

TX Lock의 메카니즘에 대해 살펴보자.

?

　      TX1　　　　　　　　　　　　　　　　　　　　         TX2

1. Undo세그먼트에서 트랜잭션 슬롯을 할당
   R1～R5에대한 TX Lock설정.(소유자목록에 등록)
   
2. R1～R5 Update작업실시                                R6에대한 TX Lock설정
   
3. Update완료 (커밋전)                                     R6의 Update작업실시
4.                                                                 Update완료 (커밋전)
5.                                                                 R3에대한 TX Lock을 얻기위해 시도.
6.                                                                 TX1이 소유자에 등록되있다.
7.                                                                 대기자목록에 등록하고 대기. 
   
8.                                                                 3초마다 TX1의 TX Lock상태 감시 (교착상태인경우, 에라가 발생. 롤백됨.)
9. 커밋 (혹은, 롤백)
   
10. 대기자목록에서 우선순위1인 트랜잭션을
    재개하도록 함.
11.                                                                 R3의 TX Lock설정(소유자목록에 등록)
12.                                                                 R3의 Update작업실시
13.                                                                 Update완료
14.                                                                 커밋.

해당자원에 대해 소유상태와 대기상태를 알아보려면, v$lock뷰를 조회해본다.

SQL>  select sid, type, id1, id2, lmode, request, block
  2         , to_char(trunc(id1/power(2,16))) USN
  3         , bitand(id1, to_number('ffff', 'xxxx')) + 0 SLOT
  4         , id2 SQN
  5   from v$lock
  6   where TYPE = 'TX' ;

  SID TY      ID1   ID2   LMODE   REQUEST   BLOCK USN    SLOT     SQN
----- -- -------- ----- -------- ----------- ------ --- ------- -------
  145 TX   655401  1601       0         6         0   10      41    1601
  150 TX   655401  1601       6         0         1   10      41    1601

• LMODE : 세션모드 (6=exclusive mode)
• REQUEST : 요청세션모드
• BLOCK : 1이 소유자, (0 : 대기자)
  

발생원인을 알아보기 위해서는 v$session_wait뷰를 참조한다.

SQL> select sid, seq#, event, state, seconds_in_wait, p1, p2, p3
  2  from v$session_wait
  3  where event like 'enq: TX%' ;

SID SEQ# EVENT                             STATE   SECONDS_IN_WAIT         P1     P2    P3
--- ---- ---------------------------------- ------- --------------- ---------- ------ -----
158  137 enq: TX - row lock contention  WAITING             108 1415053318 589824  7754

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

지금부터, 위의 6가지 발생원인에 대해 자세히 알아보기로 하자.

(3) TX Lock >> 무결성 제약 위배 가능성 또는 비트맵 인덱스 엔트리 갱신

로우 Lock경합은 다음과 같은 경우 발생한다.

1. Update시
2. Delete시
3. Insert시 (단, 테이블에 Unique인덱스가 정의된 경우만)

부서에는 1명이상의 사원이 있을 수 있고, 사원은 1개 부서에만 소속. (Dept테이블, Emp테이블)

부서번호(deptno)로 FK제약 설정.

1. TX1이 부서 레코드(deptno=40)를 삭제. (커밋전)
   
2. TX2가 사원 레코드를 삽입.(deptno=40)
   
3. TX2는 대기. (Shared 모드, enq: TX - row lock contention)
4. TX1이 커밋한 경우 → TX2는 ORA-02291 에러발생. (무결성 제약조건이 위배-부모 키가 없음)
5. TX1이 롤백한 경우 → TX2는 정상적으로 삽입 완료.

비트맵 인덱스의 엔트리에 대한 갱신시.

사원테이블의 성별(sex)컬럼에 비트맵인덱스가 설정.

※비트맵 인덱스는 하나의 엔트리가 여러개의 레코드와 맵핑되어, 하나가 락이 걸리면 관련된 레코드가 락이 걸리게 됨.

1. TX1이 사원번호 2010의 레코드를 삽입. (커밋전)
   
2. TX2가 사원번호 2009의 레코드를 갱신.
   
3. TX2는 대기. (Shared 모드, enq: TX - row lock contention)
   
4. TX1이 커밋(혹은 롤백)한 경우 → TX2는 정상적으로 갱신 완료.

우리는 트랜잭션이 시작할때, ITL슬롯을 할당받아야 함을 지난강좌들을 통해 배웠다.

ITL슬롯을 할당받아 트랜잭션ID를 기록한 후 비로소 트랜잭션이 시작됨을 의미한다.

빈 ITL슬롯이 존재하지 않는경우 다른 트랜잭션이 끝나기를(커밋 혹은 롤백) 기다려야함도 알고 있다.

이것이 Shared모드 enq:TX-allocate ITL entry 대기 이벤트이다.

ITL슬롯은 다음과 같은 특성을 갖는다.

1. ITL슬롯당 24Byte 차지.
2. 블록에 할당하는 ITL슬롯갯수 → INITRANS파라미터로 설정.
   create table emp ( .... ) INITRANS 5 MAXZTRANS 255 PCTFREE 30;
   ♣PCTFREE : Update를 위해 예약된 공간으로 INITRANS로 할당된 공간이 모두 사용중이면 이 공간을 사용한다.
3. [9i부터] insert시는, 대기없이 새 블록을 찾아 insert를 실시하기에 ITL슬롯경합이 없다. (인덱스의 경우는 제외)
4. [9i부터] INITRANS의 값은 3이하의 경우 오라클이 강제적으로 3으로 셋팅.
5. [9i부터] MAXZTRANS의 값은 무시되어 오라클이 강제적으로 255로 셋팅.
   ITL슬롯 경합이 발생하지 않으리라 생각되지만, PCTFREE가 전부 사용되어버리면 거기서 경합발생(Update)
   

ITL경합이 빈번한 세그먼트에 대해서는 INITRANS를 늘려주는것이 좋다.

경합상태는 다음의 v$segstat(10g부터)에서 확인할 수 있다.

select ts#, obj#, dataobj#, sum(value) itl_waits from v$segstat where statistic_name = 'ITL waits'
group by ts#, obj#, dataobj# having sum(value) > 0
order by sum(value) desc

주의할 점이, INITRANS를 변경하더라도 기존에 할당된 블록에는 반영이 않된다. (새 블록에만 적용)

필요하다면, 기존의 블록에 반영하는 작업을 다음과 같이 해줄수 있다.

alter table t move INITRANS 5; 　　　　　→ 인덱스가 모두 unusable 되므로 주의!
alter index t_idx rebuild INITRANS 5;

(5) TX Lock >> 인덱스 분할

테이블은 정렬상태를 유지할 필요가 없기에 입력공간이 부족하면 다른 곳의 새 블록에 입력하면 된다.

그러나, 인덱스는 정렬상태를 유지해야 하므로 순서를 의식한 끼워넣기가 빈번하게 발생할 수 있다.

이때, 인덱스분할을 행하고, 이 과정에서 Lock경합이 발생한다.

이것이 Shared 모드 enq:TX - index contention 대기이벤트이다.

다음 그림을 보자.

1. 트랜잭션이 5번위치(블록)에 데이터 삽입을 시도 (현재 5번은 공간이 없다.)
   
2. 결국, 5번의 크기를 늘려야 하므로 5번과 6번사이에 블록을 추가 (10번블록)
3. 5번블록의 데이터절반을 10블록으로 옮긴다. (결과적으로 5번위치가 2배커진 효과다)
   

위의 그림에서,

1. 트랜잭션이 9번위치(블록)에 데이터 삽입을 시도 (현재 9번은 공간이 없다.)
   
2. 단순히, 9번블록 다음에 블록을 추가 (11번블록)

1. ?WAIT(default) : LGWR가 로그버퍼를 파일에 기록, 완료 메세지를 받을 때까지 대기, log file sync대기 이벤트 발생.(동기커밋)
2. NOWAIT : LGWR의 완료메세지를 기다리지 않고 바로 다음 트랜잭션을 진행하므로, log file sync대기 이벤트 발생않음.(비동기커밋)
3. IMMEDIATE(default) : 커밋명령을 받을 때마다 LGWR가 로그버퍼를 파일에 기록.
4. BATCH : 세션 내부에 트랜잭션 데이터를 일정량 버퍼링한후 일괄 처리.

SQL> COMMIT WRITE IMMEDIATE WAIT;               - 68

SQL> COMMIT WRITE IMMEDIATE NOWAIT;           - 9

SQL> COMMIT WRITE BATCH WAIT;                    - 66       

SQL> COMMIT WRITE BATCH NOWAIT;                - 6

위의 수치는 임의의 루프를 돌려 테스트했을때 상대적 수치다.

Nowait에 대해서는 상당한 개선효과 가 보인다. IMMEDIATE와 BATCH를 비교해 보면 이들 사이의 효과는 미비해보인다.

참고적으로, 비동기식 커밋 옵션을 사용하면, 커밋 직후 인스턴스에 문제가 생기거나 할 때 정상적으로 회복시키기가 어려운 문제점도 있다. (동기식처럼 확실히 Redo/Undo작업이 완료되었으리라는 보장이 없다.)

데이터 중요도에 따라 결정하기를.

<2장 5절 끝>