블록에 레코드를 추가/갱신/삭제하려면 ITL 슬롯을 먼저 할당받고 그곳에 트랜젝션 ID를 기록
비어 있는 ITL 슬롯이 없다면 ITL 슬롯을 사용중인 트랜젝션 하나가 커밋 또는 롤백할 때까지 기다려야 한다
Shared 모드 enq: TX - allocate ITL entry 대기 이벤트 발생
테이블에 insert 할 때는 ITL 슬롯이 부족한 경우 새 블록을 할당해 그곳에 insert 하면 되기 때문에 대기할 필요가 없다(9i부터 이와 같이 동작)
테이블 insert에서는 경합이 발생하지 않지만, index 값 삽입할 때는 여전히 ITL 경합이 발생
update, delete일 때는 테이블, 인덱스를 불문하고 ITL 경합이 나타날 수 있음
ITL 슬롯 부족에 의한 대기현상이 발생하는 경우
동시에 블록을 갱신하려는 트랜젝션의 갯수가 MAXTRANS 값을 초과한 경우
PCTFREE를 0으로 지정했거나 PCTFREE 예약 공간을 모두 사용한 상태여서, 새로운 트랜젝션을 위한 ITL 슬롯이 부족한 경우
ITL 경합에 의한 대기 현상이 자주 발생하는 세그먼트(테이블, 인덱스, 파티션)에 대해서는 INITRANS를 늘려주어야 한다.
세그먼트 목록은 v$segstat을 통해 확인할 수 있다(10g 이상)
select ts#, obj#, dataobj#, sum(value) itl_waits from v$segstat where statistic_name = 'ITL waits' groupby ts#, obj#, dataobj# having sum(value) > 0 order by sum(value) desc
INITRANS 값을 변경하더라도 기존에 할당된 블록의 ITL 슬롯의 갯수에는 변함이 없고, 새로 할당되는 블록에만 적용된다.
기존 블록에서 ITL 경합이 빈번하게 발생한다면 테이블 또는 인덱스 전체를 재생성
alter table t move INITRANS 5; -- 인덱스가 모두 unusable 되므로 주의! alter index t_idx rebuild INITRANS 5;
5) TX Lock ▶ 인덱스 분할
인덱스 분할이란?
테이블의 경우 레코드 간 정렬 상태를 유지하지 않으므로 입력할 공간이 부족할 때 새로운 블록을 할당받아 입력
인덱스의 경우 정렬된 상태를 유지해야 하므로 아무 블록에나 값을 입력할 수 없음
값을 입력할 위치에 빈 공간이 없으면 인텍스 분할(Split)을 실시해 새 값을 입력할 공간을 확보하며, 이 과정에서 Lock 경합이 발생할 수 있음
5번과 9번 리프 블록이 꽉 차 있음
5번 블록에 새로운 값을 입력하려는 트랜젝션은 먼저 인덱스 분할을 실시해야 함
인덱스 분할이 완료된 모습
5번과 6번 블록 사이에 10번 블록이 삽입되었고, 5번 블록에 있던 레코드 절반이 10번 블록으로 이동함
9번 블록에 새로운 값을 추가하려는 트랜젝션이 발생
9번 블록도 꽉 찬 상태이므로 먼저 입력할 공간을 확보해야 함
맨 우측에 값을 추가하는 것이므로 레코드를 이동할 필요 없이 새 블록만 추가하면 됨
인덱스 분할이 완료된 후 모습
9번 블록 뒤쪽에 11번 블록이 추가됨
인덱스 분할이 진행되는 동안 그 블록에 새로운 값을 입력하려는 또 다른 트랜젝션이 생긴다면?
두 번째 트랜젝션은 선행 트랜젝션이 인덱스 분할을 완료할 때까지 대기
Shared 모드에서 enq: TX - index contention 이벤트를 만나게 됨
의문 : 인덱스 분할을 진행한 선행 트랜젝션이 커밋하지 않은 채 계속 다른 갱신 작업을 진행한다면 대기하던 트랜젝션은 계속 대기해야 함
인덱스 분할 작업을 따로 autonomous 트랜젝션으로 구현하여 동시성 문제 해결
TX1 트랜젝션이 인덱스에 로우를 삽입하려는 순간 빈 공간을 찾지 못함. 인덱스 분할이 필요.
TX1 트랜젝션은 autonomous 트랜젝션 TX2를 생성해 인덱스 분할을 진행토록 함
인덱스 분할이 진행중인 블록에 TX3 트랜젝션이 로우를 삽입하려 하고, enq: TX = index contention 이벤트를 만나서, TX2 트랜젝션이 커밋할 때까지 대기
인덱스 분할이 완료되면 TX2 트랜젝션은 커밋하고, autonomous 트랜젝션이므로 TX1은 커밋되지 않은 상태로 계속 트랜젝션을 진행
TX3 트랜젝션도 작업을 재개
PCTFREE에 대한 고찰
테이블에서의 PCTFREE 공간은 나중에 발생할 update를 위해 남겨두는 공간
인덱스에서의 PCTFREE는 insert를 위해 남겨두는 공간
PCTFREE 설정은 인덱스를 처음 생성하거나 재생성하는 시점에만 적용됨
공간을 남겨두더라도 언젠가 다시 채워질 것임
인덱스 분할을 최소화하기 위해 PCTFREE를 증가시키는 것은 효과가 없거나 일시적인 효과만 있음
인덱스를 주기적으로 재생성해야 의미가 있음
우측 맨 끝으로만 값이 입력되는 Right Growing 인덱스인 경우 PCTFREE를 0으로 설정하는 것이 인덱스 크기를 줄이는 데 도움이 됨(예: 순차적으로 증가하는 일련번호 컬럼에 인덱스를 생성한 경우)
6) TX Lock ▶ 기타 트랜젝션 Lock
Shared 모드 enq: TX - contention 대기 이벤트
분산 트랜젝션에서 2-Phase 커밋을 위한 PREPARED TX Lock을 대기할 때 발생한다(오라클 매뉴얼)
앞에서 열거한 중요한 TX Lock 이외의 트랜젝션 대기 상황을 모두 여기에 포함한 것 같음(추측)
읽기 전용 테이블스페이스로 전환시 TX Lock 경합이 발견됨
USERS 테이블스페이스에 DML을 수행하는 트랜젝션이 아직 남아있는 상태에서 아래 명령을 수행시 Shared 모드로 TX Lock 대기
alter tablespace USERS read only;
7) TX Lock ▶ DML 로우 Lock
DML Lock은 다중 사용자에 의해 동시에 액세스되는 사용자 데이터의 무결성을 보호
DML 수행 중에 호환되지 않는 다른 DML 또는 DDL 오퍼레이션의 수행을 방지시켜 준다
선행 트랜젝션과 호환되지 않는 모드로 테이블 Lock을 설정하려는 후행 트랜젝션은 대기하거나 작업을 포기해야 한다
RS, RX 간에는 어떤 조합으로도 호환이 되므로 select for update나 DML문 수행시 이들간에 테이블 Lock에 의한 경합은 절대 발생하지 않는다
다만, 같은 로우를 갱신하려 할 때 로우 Lock에 의한 경합은 발생
TM Lock
오라클 테이블 Lock도 Enqueue로 구현(TM Enqueue)
테이블 Lock을 TM Lock이라고 부르기도 한다
TM Enqueue 리소스 구조체의 식별자TypeID1ID2
TM
오브젝트 ID
0
선행 트랜젝션이 TM Lock을 해제하기를 기다리는 트랜젝션에서 발생하는 대기 이벤트
enq: TM - contention 이벤트가 지속적으로 나타남
테이블 Lock에 대한 오해와 진실
테이블 전체에 Lock이 걸려서 다른 트랜젝션이 더는 레코드를 추가하거나 갱신하지 못하도록 막는다고 생각하기 쉬움
DML 수행시 항상 Table Lock이 함께 설정되므로 맞지 않음
Lock을 획득한 선행 트랜젝션이 해당 테이블에서 현재 어떤 작업을 수행중인지 알리는 일종의 푯말(Flag)으로 이해해야 함
여러 가지의 Lock 모드에 따라 후행 트랜젝션이 수행할 수 있는 작업의 범위가 결정됨
푯말에 기록된 Lock 모드와 후행 트랜젝션이 현재 하려는 작업 내용에 따라 진행 여부가 결정됨
진행할 수 없다면 기다릴지, 작업을 포기할지 진로를 결정(내부적으로 하드코딩 돼 있거나 사용자가 지정한 옵션에 따라 결정)해야 함
기다려야 한다면 TM Enqueue 리소스 대기자 목록에 Lock 요청을 등록하고 대기
예) DDL문을 이용해 테이블 구조를 변경하려는 세션
해당 테이블에 TM Lock이 설정돼 있는지 먼저 확인
TM Lock을 Row Exclusive(=SX) 모드로 설정한 트랜젝션이 하나라도 있다면 현재 테이블을 갱신중인 트랜젝션이 있다는 신호이므로 ORA-00054 메시지를 던지고 작업을 멈춤
DDL문이 먼저 수행중일 경우는 DML문을 수행하려는 세션이 TX Lock을 얻으려고 대기(enq: TM - contention 이벤트 발생)
대상 리소스가 사용중일 때 진로 선택
Lock을 얻고자 하는 리소스가 사용중일 때, 프로세스는 3가지 방법 중 하나를 선택 보통은 진로가 결정돼 있지만 사용자가 선택하는 경우도 있음(select for update 문 사용)
Lock이 해제될 때까지 기다린다.(select * from t for update)
일정 시간만 기다리다 포기한다.(select * from t for update wait 3)
포기할 때 ORA-30006: resource busy; acquire with WAIT timeout expired 메시지를 던짐
기다리지 않고 작업을 포기한다.(select * from t for update nowait)
작업을 포기할 때 ORA-00054: resource busy and acquire with NOWAIT specified 메시지를 던짐
DML문을 수행할 때 묵시적으로 테이블 Lock을 얻게 되고, 1번 기다리는 방법을 선택 Lock Table 명령을 이용해 명시적으로 테이블 Lock을 얻을 때도 기본적으로 기다리는 방법을 선택하지만, NOWAIT 옵션을 사용해 곧바로 작업을 포기하도록 사용자가 지정할 수 있음
DDL문을 수행할 때도 내부적으로 테이블 Lock을 얻음(이 경우는 NOWAIT 옵션이 자동으로 지정됨)
DML간에도 테이블 Lock을 이용해 동시성을 제어하는 경우 존재
병렬 DML 또는 Direct Path Insert 방식으로 작업을 수행하는 경우
사례)
1번 세션(SID=1057)에서 T 테이블에 Append 모드로 Insert를 수행
스크립트로 Lock 모니터링
1번 세션
SQL>insert /*+ append */ into t select * from t1; SQL>select l.session_id SID 2 , (case when lock_type = 'Transaction' then 'TX' 3 when lock_type = 'DML' then 'TM' end) TYPE 4 , mode_held 5 , mode_requested mode_reqd 6 , (case when lock_type = 'Transaction' then 7 to_char(trunc(lock_id1/power(2,16))) 8 when lock_type='DML' then 9 (select object_name from dba_objects 10 where object_id = l.lock_id1) 11 end) "USN/Table" 12 , (case when lock_type = 'Transaction' then 13 to_number(lock_id1) end) "SQN" 14 , (case when blocking_others = 'Blocking' then ' <<<<<' end) Blocking 15 from dba_lock l 16 where lock_type in ('Transaction', 'DML') 17 order by session_id, lock_type, lock_id1, lock_id2 ; SID TY MODE_HELD MODE_REQD USN/Table SQN BLOCKING ---------- -- ---------- ---------- ---------- ---------- ------ 1057 TM Exclusive None T 1057 TX Exclusive None 6 393231
DML문을 수행하니 TX Lock과 TM Lock을 동시에 획득함
일반적인 DML문에서는 테이블 Lock(=TM Lock)을 Row Exclusive(=SX) 모드로 설정하지만, 여기서는 Append 모드로 Insert를 수행했기 때문에 Exclusive 모드임
2번 세션(SID=1072)에서 T 테이블의 레코드 하나를 갱신하는 update문을 수행하고 Lock을 모니터링
2번 세션
SQL>update t 2 set c2 = 3 3 where c1 = 2 ; SID TY MODE_HELD MODE_REQD USN/Table SQN BLOCKING ---------- -- ---------- ---------- ---------- ---------- ------ 1057 TM Exclusive None T <<<<< 1057 TX Exclusive None 6 393231 1072 TM None Row-X (SX) T
일반적인 DML문을 사용했으므로 Row Exclusive(=SX) 모드로 테이블 Lock을 요청했음
Row Exclusive 모드 Lock은 Exclusive 모드와 호환성이 없으므로 2번 세션은 블로킹됨
로우 Lock이 아니라 테이블 Lock 때문에 블로킹된 점을 주목
로우 Lock 호환성을 확인하기 전에 테이블 Lock 호환성을 먼저 확인한다는 사실을 알 수 있음
v$session_wait 뷰를 통해 2번 세션(SID=1072)의 이벤트 상황을 조회(enq: TM - contention 대기 이벤트 발생)
SQL>select event, wait_time, seconds_in_wait, state 2 from v$session_wait 3 where sid = 1072 ; EVENT WAIT_TIME SECONDS_IN_WAIT STATE ------------------------------------------- --------------- ------------------- enq: TM - contention 0 358 WAITING
Exclusive 모드 테이블 Lock을 먼저 획득한 1번 세션(SID=1057)을 커밋하고 다시 Lock을 모니터링
댓글