[2) 트랜잭션과 Lock]5. 오라클 Lock(2) TX Lock(트랜잭션 락)

Yu River·2022년 6월 12일

SQLP oracle 오라클성능고도화

오라클성능고도화1 정리요약

목록 보기

17/55

TX Lock

1) TX Lock(=트랜잭션 Lock)

DML 로우 Lock은 로우 단위 Lock과 트랜잭션 Lock을 조합해서 구현한다.
트랜잭션이 첫 번째 변경을 시작할 때 얻고, 커밋 또는 롤백할 때 해제된다.
변경 중인 레코드(또는 기타 리소스)를 동시에 변경하려는 트랜잭션이 있으면 트랜잭션 Lock(TX Lock)을 사용하여 액세스를 직렬화한다.
Enqueue Lock으로 구현된다.
✅ Enqueue 리소스에서의 TX Lock
- Enqueue 리소스는 소유자(Owner), 대기자(Waiter) 목록을 관리할 수 있는 구조체이다.
- 각 Enqueue 리소스에 고유한 식별자(Type-ID1-ID2)를 부여한다.
- Enqueue 리소스 식별자에서 TX Lock의 Type은 TX이다.
- Enqueue 리소스 식별자에서 TX Lock의 ID1은 'Undo 세그먼트 번호 << 16 | + 트랜젝션 슬롯번호' 이다.
- Enqueue 리소스 식별자에서 TX Lock의 ID2는 '트랜잭션 슬롯 Sequence 번호' 이다.
- 이 식별자를 갖는 리소스 구조체를 Enqueue 리소스 테이블 해시 체인에 연결 후 소유자 목록에 트랜젝션을 등록하여 Lock을 획득한다.
오라클은 레코드가 갱신중이라도 읽기 작업에 대해서는 블로킹 없이 작업을 진행할 수 있도록 구현한다.

✅ 오라클에서는 어떻게 갱신중인 레코드에 블로킹 없이 읽기 작업을 수행할 수 있을까요?

오라클에서 트랜잭션을 시작하려면 먼저 Undo 세그먼트 헤더에 위치한 트랜젝션 테이블로부터 슬롯을 하나 할당받아야 한다.

이 트랜잭션이 변경을 가한 블록에서 다른 트랜잭션이 Consistent 버전을 얻으려면 트랜잭션 슬롯에 기록된 상태 정보를 확인한 후 필요시 CR 블록을 생성해서 읽는다.

2) TX Lock 메커니즘 (트랜잭션 Lock 매커니즘)

1. TX1 트랜젝션은 Undo 세그먼트에서 트랜잭션 슬롯을 할당받고, Enqueue 리소스를 통해 TX Lock을 설정한다.

2. 이 상태에서 r1부터 r5까지 5개 레코드를 변경하고, 아직 커밋은 하지 않은 상태이다.

3. TX2 트랜젝션도 트랜잭션 테이블에서 하나의 슬롯을 할당받고, Enqueue 리소스를 통해 TX Lock을 설정후 r6 레코드를 변경했다.

4. TX2가 r3 레코드를 액세스하려는 순간 호환되지 않는 모드로 Lock이 걸려 있음을 인지하고 TX1의 트랜잭션 슬롯 상태를 확인한다.

5. TX1이 아직 커밋되지 않은 Active 상태이다.

6. TX2는 "TX1이 Lock을 설정한 Enqueue 리소스 구조체" 대기자 목록에 자신을 등록하고 대기상태로 들어간다.

7. TX2는 대기하면서 3초마다 한번씩 TX1이 설정한 TX Lock의 상태를 확인한다.

교착상태(Deadlock) 발생 여부를 확인하기 위함이다.

8. TX1이 커밋 또는 롤백하면 TX1이 설정한 TX Lock의 대기자 목록에서 가장 우선순위가 높은 TX2 트랜잭션을 깨워 트랜잭션을 재개한다.

9. TX2는 r3 레코드를 변경할 수 있게된다.

3) TX Lock 경합 상황 모니터링

v$lock 뷰를 통해 조회할 수 있다.
- 하지만 경합의 발생 원인은 v$lock 뷰를 통해서는 알 수 없다.

[예시] TX Lock 경합

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

현재 150번 세션이 145번 세션의 진행을 블로킹한다.
150번 세션이 Exclusive 모드(lmode = 6)로 요청한 채 대기중이다.
여기서 id1과 id2 값을 이용해 TX Lock을 소유한 트랜젝션의 Undo 세그먼트와 트랜젝션 슬롯번호, 시퀀스 번호까지 식별이 가능하다.
- 경합이 발생한 TX Lock 식별자는 < TX-655401-1601 > 이다.

4) TX Lock TX Lock의 발생 원인 조회

v$session_wait 뷰 또는 이벤트 트레이스(레벨 8)을 통해 확인한다.
p2, p3 파라미터를 통해 Undo 세그먼트, 트랜젝션 슬롯번호, 그리고 Wrap 시퀀스 번호를 식별한다.

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

Undo 세그먼트 번호는 trunc(:p2/power(2,16))이다.
트랜젝션 테이블 슬롯번호는 bitand(:p2, to_number('ffff', 'xxxx')) + 0 이다.
트랜젝션 슬롯 Wrap 시퀀스는 p3이다.

5) 대기 이벤트명에 따른 TX Lock의 구분

enq: TX - row lock contention일 때는 Lock 모드에 따라 그 발생원인을 판단해야 한다.
- Lock Mode는 이벤트 발생시 함께 기록되는 p1 파라미터를 통해 확인할 수 있다.
Lock 모드는 앞 시리즈인 TM Lock에서 설명한 내용과 비슷합니다.

-- Lock 타입 - TM, TX, TS, SQ, HW, HV 등
  chr(bitand(:p1, -16777216)/16777215) || chr(bitand(:p1, 16711680/65536)

-- Lock 모드
  decode(to_char(bitand(:p1, 65536)), 0, 'None'
                                    , 1, 'Null'
                                    , 2, 'RS'   -- Row-Shared
                                    , 3, 'RX'   -- Row-Exclusive
                                    , 4, 'S'    -- Shared
                                    , 5, 'SRX'  -- Shared-Row-Exclusive
                                    , 6, 'X'    -- Exclusive

6) 대표적 TX Lock 발생원인

1. ⭐️ DML 로우 Lock (가장 중요)

다중 사용자에 의해 동시에 액세스되는 사용자 데이터의 무결성을 보호한다.
DML 수행 중에 호환되지 않는 다른 DML 또는 DDL 오퍼레이션의 수행을 방지한다.
두 개의 트랜젝션이 동시에 같은 로우를 변경하는 것을 방지한다.

오라클에서는 로우 Lock을 로우 단위 Lock과 TX Lock을 조합해서 구현한다.

로우를 갱신하려면 Undo 세그먼트에서 트랜젝션 슬롯을 할당받고
Enqueue 리소스를 통해 TX Lock을 획득한다.
- TX Lock은 트랜잭션을 시작할 때 한 번만 획득한다.
insert, update, delete, merge 문장을 통해 갱신하는 각 로우마다 Exclusive 모드로 로우 단위 Lock을 획득한다.

<1> 로우 단위 Lock (액세스 관점)

오라클은 데이터 액세스시 로우 단위 Lock과 다중 버전 읽기 일관성 메커니즘을 이용함으로써 읽기 작업(select for update 문이 아닌)에 대해 Lock에 의한 대기 현상이 발생하지 않도록 구현한다.
한 트랜잭션이 로우 정보를 갱신할 때는 우선 블록 헤더 ITL과 로우 헤더에 Lock Byte를 설정하고 블록 헤더 ITL 슬롯에 트랜잭션 ID를 기록한다.
이후 이 레코드를 액세스하려는 다른 트랜잭션이 로우 헤더에 설정된 Lock Byte를 통해 ITL 슬롯을 찾고 ITL 슬롯이 가리키는 Undo 세그먼트 헤더의 트랜잭션 슬롯에서 로우를 갱신중인 트랜잭션 상태를 확인하고 해당 레코드에 액세스 가능 여부를 결정한다.
- TX1 트랜잭션이 갱신을 진행중일 때 이 레코드를 읽으려는 TX2 트랜잭션은 TX1 트랜잭션의 상태를 확인하고 CR 블록을 생성해서 읽기 작업을 완료한다.

<2> TX Lock (갱신 관점)

Enqueue 리소스를 통해 TX Lock을 설정하는 것이다.
Lock이 설정된 레코드를 갱신하고자 할 때 Enqueue 리소스에서 대기한다.
- TX1이 갱신중인 레코드를 같이 갱신하려는 TX2 트랜잭션은 TX1 트랜잭션이 완료될 때까지 대기한다.
DML 로우 Lock에 의한 TX Lock 때문에 블로킹된 세션에서 발생되는 대기 이벤트
- Exclusive 모드의 enq: TX - row lock contention 대기 이벤트가 지속적으로 나타난다.

2. 무결성 제약 위배 가능성

로우 Lock 경합은 일반적으로 update, delete 시에 발생한다.
테이블에 Unique 인덱스가 정의되어 있는 경우 insert에 의한 로우 Lock 경합이 발생할 수 있.
- 두 개 이상 트랜잭션이 같은 값을 입력하려 할 때, 선행 트랜젝션이 아직 진행 중이라면 값의 중복 여부가 확정되지 않았으므로 후행 트랜잭션은 진행을 멈추고 대기한다.

[예시1] dept 테이블의 deptno 컬럼에 PK 인덱스가 잡혀 있는 상황

TX1이 dept 테이블에 deptno = 40인 레코드를 입력한다.
TX2도 dept 테이블에 deptno = 40인 레코드를 입력하면, TX1이 커밋 또는 롤백할 때까지 Shared 모드로 enq: TX - row lock contention 대기 이벤트가 발생한다.
TX1이 커밋하면 TX2는 ORA-00001 에러
- "ORA-00001: 무결성 제약 조건(PK_DEPT)에 위배됩니다."
TX1이 롤백하면 TX2는 정상적으로 입력이 완료된다.

[예시2] dept와 emp 테이블이 1:M 관계이며 deptno 컬럼으로 dept.deptno를 참조하도록 emp 테이블에 FK가 설정되어있는 상황

TX1이 dept 테이블에 deptno = 40인 레코드를 지운다.
TX2가 emp 테이블에 deptno = 40인 레코드를 입력하면, TX1이 커밋 또는 롤백할 때까지 Shared 모드로 enq: TX - row lock contention 대기 이벤트가 발생한다.
TX1이 커밋하면 TX2는 ORA-02291 에러
- "ORA-02291: 무결성 제약조건(FK_EMP_DEPT)이 위배되었습니다- 부모 키가 없습니다"
TX1이 롤백하면 TX2는 정상적으로 입력이 완료된다.

3. 비트맵 인덱스 엔트리 갱신

Shared 모드로 enq: TX - row lock contention 대기 이벤트가 발생한다.
비트맵 인덱스의 구조상 하나의 엔트리가 여러 개 레코드와 매핑된다.
하나의 엔트리에 Lock을 설정하면 매핑되는 레코드 전체에 Lock이 설정된다.
비트맵 인덱스 엔트리를 두 개 이상 트랜잭션이 동시에 갱신할 때 이 이벤트가 자주 발생한다.

4. ITL(Interested Transaction List) 슬롯 부족

ITL(Interested Transaction List) 슬롯은 한 블록을 동시에 갱신할 수 있는 트랜잭션의 갯수 결정한다.
ITL 슬롯당 24 바이트 공간을 차지한다.
INITRANS 파라미터로 기본 할당 ITL 슬롯 갯수를 설정한다.
- 9i부터는 3보다 작게 설정하더라도 오라클이 기본적으로 3개의 ITL 슬롯을 할당한다.
최대한 생성할 수 있는 ITL 슬롯의 갯수는 MAXTRANS 파라미터에 의해 결정한다.
- 10g부터는 MAXTRANS를 위해 사용자가 지정한 값은 무시되며 항상 255개로 고정된다.
미리 할당된 ITL 슬롯이 모두 사용중일 때 새로운 트랜젝션이 ITL 슬롯을 요청하면 PCTFREE 설정에 의해 비워둔 공간을 활용한다.
블록에 레코드를 추가/갱신/삭제하려면 ITL 슬롯을 먼저 할당받고 그곳에 트랜잭션 ID를 기록한다.
비어 있는 ITL 슬롯이 없다면 ITL 슬롯을 사용중인 트랜잭션 하나가 커밋 또는 롤백할 때까지 기다린다.
- Shared 모드 enq: TX - allocate ITL entry 대기 이벤트 발생
테이블에 insert 할 때는 ITL 슬롯이 부족한 경우 새 블록을 할당해 그곳에 insert 하면 되기 때문에 대기할 필요가 없다.(9i부터 이와 같이 동작)
update, delete일 때는 테이블, 인덱스를 불문하고 ITL 경합이 나타날 수 있다.
테이블 insert에서는 경합이 발생하지 않지만, index 값 삽입할 때는 여전히 ITL 경합이 발생한다.
동시에 블록을 갱신하려는 트랜젝션의 갯수가 MAXTRANS 값을 초과한 경우 ITL 슬롯 부족에 의한 대기현상이 발생한다.
PCTFREE를 0으로 지정했거나 PCTFREE 예약 공간을 모두 사용한 상태여서, 새로운 트랜젝션을 위한 ITL 슬롯이 부족한 경우 발생한다.
✅ PCTFREE에 대한 고찰
- 테이블에서의 PCTFREE 공간은 나중에 발생할 update를 위해 남겨두는 공간이며 인덱스에서의 PCTFREE는 insert를 위해 남겨두는 공간이다.
- PCTFREE 설정은 인덱스를 처음 생성하거나 재생성하는 시점에만 적용되며 공간을 남겨두더라도 언젠가 다시 채워질 것이다.
- 인덱스 분할을 최소화하기 위해 PCTFREE를 증가시키는 것은 효과가 없거나 일시적인 효과만 있는 셈이다.
- 즉 , 인덱스를 주기적으로 재생성해야 의미가 있으며 우측 맨 끝으로만 값이 입력되는 Right Growing 인덱스인 경우 PCTFREE를 0으로 설정하는 것이 인덱스 크기를 줄이는 데 도움이 된다.
  ex) 순차적으로 증가하는 일련번호 컬럼에 인덱스를 생성한 경우
- ITL 경합에 의한 대기 현상이 자주 발생하는 세그먼트(테이블, 인덱스, 파티션)에 대해서는 INITRANS를 늘려주어야 한다.
  INITRANS 값을 변경하더라도 기존에 할당된 블록의 ITL 슬롯의 갯수에는 변함이 없고, 새로 할당되는 블록에만 적용된다.
따라서 기존 블록에서 ITL 경합이 빈번하게 발생한다면 테이블 또는 인덱스 전체를 재생성해야 한다.

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

세그먼트 목록은 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;

5. 인덱스 분할

테이블의 경우 레코드 간 정렬 상태를 유지하지 않으므로 입력할 공간이 부족할 때 새로운 블록을 할당받아 입력한다.
인덱스의 경우 정렬된 상태를 유지해야 하므로 아무 블록에나 값을 입력할 수 없기 때문에 값을 입력할 위치에 빈 공간이 없으면 인텍스 분할을 실시해 새 값을 입력할 공간을 확보하는 과정에서 Lock 경합이 발생한다.

[예시]

5번과 9번 리프 블록이 꽉 차 있는 상태에서 5번 블록에 새로운 값을 입력하려는 트랜잭션은 먼저 인덱스 분할을 실시한다.
5번과 6번 블록 사이에 10번 블록이 삽입되었고, 5번 블록에 있던 레코드 절반이 10번 블록으로 이동한다.
9번 블록에 새로운 값을 추가하려는 트랜잭션이 발생하면 9번 블록도 꽉 찬 상태이므로 먼저 입력할 공간을 확보해야 한다.

맨 우측에 값을 추가하는 것이므로 레코드를 이동할 필요 없이 새 블록만 추가하면 된다.

9번 블록 뒤쪽에 11번 블록이 추가된다.
인덱스 분할이 진행되는 동안 그 블록에 새로운 값을 입력하려는 또 다른 트랜잭션이 생긴다면?
- 두 번째 트랜잭션은 선행 트랜젝션이 인덱스 분할을 완료할 때까지 대기한다.
- Shared 모드에서 enq: TX - index contention 대기 이벤트가 발생한다.
  ✅ 인덱스 분할을 진행한 선행 트랜잭션이 커밋하지 않은 채 계속 다른 갱신 작업을 진행한다면?
  - 대기하던 트랜잭션은 계속 대기하게 된다.
  - 인덱스 분할 작업을 따로 autonomous 트랜잭션으로 구현하여 동시성 문제를 해결한다.
  - [순서]
    1. TX1이 인덱스에 로우를 삽입하려는 순간 빈 공간을 찾지 못하게 되어 인덱스 분할이 필요하다.
    2. TX1은 autonomous 트랜잭션 TX2를 생성해 인덱스 분할을 진행한다.
    3. 인덱스 분할이 진행중인 블록에 TX3 트랜잭션이 로우를 삽입하려 하는 과정에서
    enq: TX - index contention 이벤트를 만나, TX2 트랜잭션이 커밋할 때까지 대기한다.
    4. 인덱스 분할이 완료되면 TX2 트랜잭션은 커밋하고, autonomous 트랜잭션이므로 TX1은 커밋되지 않은 상태로 계속 트랜젝션을 진행한다.
    5. TX3 트랜젝션도 작업을 재개한다.

✅ autonomous 트랜잭션이란?

자율 트랜잭션 (autonomous transaction) 이라고도 한다.

트랜잭션 내 트랜잭션을 구현하는 것이다.

메인 트랜잭션에는 영향을 끼치지 않고 서브 트랜잭션 내 커밋이 독립적으로 진행될 수 있다.

다만 메인 트랜잭션이 원자성(All OR Nothing)이 깨지는 부작용이 있다.

2) US Lock

enque 리소스 식별자에서 Type은 'US' ID1은 'Undo 세그먼트 번호(USN)' 로 표시된다.

3) HW Lock

enque 리소스 식별자에서 Type은 'HW' ID1은 'UTablespace#' ID2는 'DBA of Undo Segment Header'로 표시된다.

Yu River

도광양회(韜光養晦) ‘빛을 감추고 어둠속에서 힘을 기른다’

이전 포스트

[2) 트랜잭션과 Lock]5. 오라클 Lock(1)Latch,TM Lock

다음 포스트

[2) 트랜잭션과 Lock]5. 오라클 Lock(2) TX Lock(트랜잭션 락)

오라클성능고도화1 정리요약

TX Lock

1) TX Lock(=트랜잭션 Lock)

✅ Enqueue 리소스에서의 TX Lock

✅ 오라클에서는 어떻게 갱신중인 레코드에 블로킹 없이 읽기 작업을 수행할 수 있을까요?

2) TX Lock 메커니즘 (트랜잭션 Lock 매커니즘)

1. TX1 트랜젝션은 Undo 세그먼트에서 트랜잭션 슬롯을 할당받고, Enqueue 리소스를 통해 TX Lock을 설정한다.

2. 이 상태에서 r1부터 r5까지 5개 레코드를 변경하고, 아직 커밋은 하지 않은 상태이다.

3. TX2 트랜젝션도 트랜잭션 테이블에서 하나의 슬롯을 할당받고, Enqueue 리소스를 통해 TX Lock을 설정후 r6 레코드를 변경했다.

4. TX2가 r3 레코드를 액세스하려는 순간 호환되지 않는 모드로 Lock이 걸려 있음을 인지하고 TX1의 트랜잭션 슬롯 상태를 확인한다.

5. TX1이 아직 커밋되지 않은 Active 상태이다.

6. TX2는 "TX1이 Lock을 설정한 Enqueue 리소스 구조체" 대기자 목록에 자신을 등록하고 대기상태로 들어간다.

7. TX2는 대기하면서 3초마다 한번씩 TX1이 설정한 TX Lock의 상태를 확인한다.

8. TX1이 커밋 또는 롤백하면 TX1이 설정한 TX Lock의 대기자 목록에서 가장 우선순위가 높은 TX2 트랜잭션을 깨워 트랜잭션을 재개한다.

9. TX2는 r3 레코드를 변경할 수 있게된다.

3) TX Lock 경합 상황 모니터링

[예시] TX Lock 경합

4) TX Lock TX Lock의 발생 원인 조회

5) 대기 이벤트명에 따른 TX Lock의 구분

6) 대표적 TX Lock 발생원인

1. ⭐️ DML 로우 Lock (가장 중요)

오라클에서는 로우 Lock을 로우 단위 Lock과 TX Lock을 조합해서 구현한다.

<1> 로우 단위 Lock (액세스 관점)

<2> TX Lock (갱신 관점)

2. 무결성 제약 위배 가능성

[예시1] dept 테이블의 deptno 컬럼에 PK 인덱스가 잡혀 있는 상황

[예시2] dept와 emp 테이블이 1:M 관계이며 deptno 컬럼으로 dept.deptno를 참조하도록 emp 테이블에 FK가 설정되어있는 상황

3. 비트맵 인덱스 엔트리 갱신

4. ITL(Interested Transaction List) 슬롯 부족

✅ PCTFREE에 대한 고찰

5. 인덱스 분할

[예시]

✅ 인덱스 분할을 진행한 선행 트랜잭션이 커밋하지 않은 채 계속 다른 갱신 작업을 진행한다면?

✅ autonomous 트랜잭션이란?

2) US Lock

3) HW Lock

[2) 트랜잭션과 Lock]5. 오라클 Lock(1)Latch,TM Lock

[2) 트랜잭션과 Lock]5. 오라클 Lock(3)Enqueue Lock,commit

0개의 댓글