h2.오라클 Lock

• 래치 : SGA에 공유돼 있는 갖가지 자료 구조를 보호할 목적으로 사용하는 가qu운 Lock
• 버퍼 Lock : 버퍼 블록에 대한 액세스를 직렬화
• 라이브러리 캐시Lock : 라이브러리 캐시 오브젝트에 대한 핸들을 보호
• 라이브러리 캐시 Pin : 라이브러리 캐시 오브젝트의 실제 내용이 담긴 힙(Heap)을 보호
• DML 테이블 Lock : Enqueue Lock으로 구현함
• DML 로우 Lock : 로우 단위 Lock과 트랜잭션 Lock을 조합해서 구현함

h4.Enqueue Lock
Enqueue는 공유 리소스에 대한 액세스를 관리하는 Lock 매커니즘 이다. Enqueue Lock은 래치와 달리 순서가 보장되는 큐(Queue)구조를 사용한다. 따라서 대기자 큐(Queue)에 가장 먼저 Lock요청을 등록한 세션이 가장 먼저 Lock을 획득한다.
Enqueue Lock으로 관리되는 공유 리소스에 대해 Lock을 획득하려면 먼저 'Enqueue 리소스'를 할당 받양 한다.(v$resource를 통해 확인 가능)
Enqueue리소스는 소유자(Owner), 대기자(Waiter)의 목록을 관리할 수 있는 구조체{}{}를 말한다. 각 Enqueue 리소스에 고유한 식별자가 부여되면, 식별자는 <Type-ID1-ID2>로 구성된다. Type은 'TX', 'TM', 'TS'처럼 2개의 문자열로 이루어지며, ID1, ID2에는 Lock 종류에 따라 다른 정보를 갖는다.

• TM Lock 식별자는 다음과 같은 정보를 포함한다.
• TYPE : TM
• ID1 : 오브젝트 ID
• ID2 : 0

• TX Lock 식별자는 다음과 같은 정보를 포함한다.
• TYPE : TX
• ID1 : UNDO 세그먼트 번호 + 트랜잭션 슬롯 번호
• ID2 : 트랜잭션 슬롯 Sequence 번호

• 오라클은 Enqueue 리소스 구조체를 통합관리하는 리소스 테이블(일종의 Array){}{}를 갖고 있으며, 리소스 테이블에서 관리되는 각 리소스를 찾을 때는 해싱 알고리즘을 사용한다. 해싱을 위한 해시 키로는 리소스 식별자가 사용된다. 각 해시 버킷에는 Linked List로 연결된 해시 체인을 가지며, 여기에 리소스 구조체가 연결된다.
• Enqueue 방식으로 관리되는 특정 리소스(테이블, 트랜잭션)에 대해 Lock을 획득하려면, 먼저 리소스 테이블에서 해당 리소스 구조체를 찾는다.
• 리소스 구조체를 찾지 못하면, 새로운 리소스 구조체를 할당 받아 해시 체인 연결 리스트에 연결한다.
• 그런후, 리소스 구조체의 소유자 목록에 자신을 등록 한다.
• 오화되지 않는 모드로 먼저 Lock을 획득한(소유자 목록에 등록된)세션이 있다면 Lock 요청을 대기자 목록에 등록하고 대기해야 한다.
• 소유자가 Exclusive 모드 일 때는 한순간에 하나의 세션만 Lock을 획득할 수 있지만, Shared 모드일 때는 여러 세션이 동시에 Lock을 획득할 수 있다. 즉, 여러 세션이 동시에 소유자 목록에 등록 될 수 있다.
• 소유자 목록에 Shared 또는 Exclusive 모드 Lock이 등록된 상태에서 Exclusive 모드로 Lock을 획득하려는 세션은 대기자 목록에서 대기해야 하며, 하나의 리소스 구조체 대기자 목록에 동시에 여러 세션이 등록된 상태로 대기할 수도 있다.
• Enqueue Lock 매커니즘
  1. A 세션이 Shared 모드로 Lock을 획득
  2. B 세션이 Shared 모드로 Lock을 획득하려고 한다. 먼저 Lock을 소유한 A 세션과 호환되므로 정상적으로 Lock을 획득한다. 이제 소유자 목록에는 두 개 세션이 달려 있다.
  3. C 세션이 Exclusive 모드로 Lock을 획득하려고 한다. Shared 모드와 Exclusive 모드 간에 호환성이 없으므로 대기자 목록에 자신을 등록하고 대기한다.
  4. 소유자 목록에 Shared 모드로 달려 있던 A,B 두 세션이 모두 Lock을 해제하면 C세션이 Exclusive 모드로 소유자 목록에 등록된다.
  5. A세션이 Exclusive 모드로 다시 Lock을 획득하려고 하면, Exclusive 모드와 호환 되지 않으므로 대기자 목록에 자신을 등록하고 대기한다.
  6. B 세션이 다시 Shared 모드로 Lock을 획득하려고 할 때도 Exclusive 모드와 호환되지 않으므로 대기자 목록에 자신을 등록하고 대기한다.
  7. Enqueue Lock은 순서가 보장되므로 C 세션이 Lock을 해제하면 A 세션이 가장 먼저 Exclusive 모드로 Lock을 획득한다.

h4.TX Lock(트랜잭션 Lock)
트랜잭션을 시작하려면 먼저 Undo 세그먼트 헤더에 위치한 트랜잭션 테이블로부터 슬롯을 하나 할당 받아야 한다. 이 트랜잭션이 변경을 가한 블록에 대한 Consistent버전을 얻으려는 다른 트랜잭션은, 트랜잭션 슬롯에 기록된 상태정보를 확인하고, 필요하다면 CR블록을 생성{}{}해서 읽는다. 그렇게 함으로써, 레코드가 갱신 중이더라도 읽기 작업에 대해서는 블로킹 없이 작업을 진행 할 수 있다.
하지만 변경중인 레코드를 동시에 변경{}{}하려는 트랜잭션에 대해서는 액세스를 직렬화해야 하며, 그 목적으로 사용하는 Lock 매커니즘이 트랜잭션 Lock{}{}이다. TX Lock은 트랜잭션이 첫 번째 변경을 시작할 때 얻고, 커밋 또는 롤백할 때 해제한다. TX Lock도 Enqueue Lock으로 구현되어 있다.

• TX Lock을 위한 Enqueue 리소스 구조체
  • TYPE : TX
  • ID1 : UNDO 세그먼트 + 트랜잭션 슬롯번호
  • ID2 : 트랜잭션 슬롯 Sequence번호

Enqueue 식별자를 갖는 리소스 구조체를 Enqueue 리소스 테이블 해시 체인에 연결하고, 소유자 목록에 트랜잭션을 등록함으로써 Lock을 획득한다.

• TX Lock 매커니즘

1. TX1 트랜잭션은 UNDO 세그먼트에서 트랜잭션 슬롯을 할당 받고, Enqueue 리소스를 통해 TX Lock을 설정한다. 이 상태에서 r1 부터 r5까지 5개 레코드를 변경하고, 아직 커밋은 하지 않았다.
2. TX2 트랜잭션도 트랜잭션 테이블에서 하나의 슬롯을 할당 받고, Enqueue 리소스를 통해 TX Lock을 설정한 후 r6 레코드를 변경한다.
3. 이제 TX2가 r3 레코드를 액세스하려는 순간 호환되지 않는 모드로 Lock이 걸려 있음을 인지하고 TX1의 트랜잭션 슬롯 상태를 확인한다.
4. TX1이 아직 커밋되지 않는 Active 상태이므로 TX2는, TX1dl Lock을 설정한 Enqueue 리소스 구조체 대기자 목록에 자신을 등록하고, 대기 상태로 들어간다.
5. TX2는 대기하면서 3초마다 한번씩 TX1이 설정한 TX Lock의 상태를 확인한다. 교착 상태( DeadLock ) 발생 여부를 확인하기 위함이다.
6. TX1이 커밋 또는 롤백하면, TX1이 설정한 TX Lock의 대기자 목록에서 가장 우선 순위가 높은 TX2 트랜잭션을 깨워 트랜잭션을 재개 하도록 한다.
7. TX2는 r3 레코드를 변경한다.

• V$lock을 통해 TX 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

• • 현재 150세션이 145번 세션의 진행을 블로킹 하고 있다.(block=1)
  • 145번 세션은 150번 세션이 Exclusive ahem (lmode=6)로 획득한 TX Lock을 Exclusive 모드(request=6)로 요청한 채 대기하고 있다.
  • 현재 경합이 발생한 TX Lock식별자는 <TX-655401-1601>이고, id1과 id2 값을 이용해 TX Lock을 소유한 트랜잭션의 UNDO 세그먼트와 트랜잭션 슬롯번호, 시퀀스 번호까지 식별해 준다.
• 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

관찰된 대기 이벤트명에 따라 TX Lock을 아래와 같이 구분할 수 있다.

• 이벤트명이 enq: TX - row lock contention 일 때는 Lock 모드에 따라 그 발생원인을 판단해야 한다. Lock 모드는 이벤트 발생 시 함께 기록되는 p1 파라미터를 통해 확인할 수 있다.
• Lock 타입 - TM, TX, TS, SQ, HW, HV 등

Chr(bitand(:p1, -16777216)/16777215) || chr(bitand(:p1, 16711680/65536)

TM : DML
TX : Transaction
TS : Temp Segment
SQ : Sequence Number
HW : highwarter mark 공간을 할당 할 때 사용한다. 이 Lock은 segment의 high warter mark가 이동될 때 획득하며 heavy insert하는 동안에 전형적으로 나타난다.
HV : HW와 비슷하다 단지 parallel direct path INSERT가 일어 났을 때 나타난다.

• 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 )

• P2, p3 파라미터를 통해 UNDO 세그먼트, 트랜잭션 슬롯번호, 그리고 Wrap 시퀀스 번호를 식별해 낼 수 있다.

1. undo 세그먼트 번호

trunc(:p2/power(2,16))

1. 트랜잭션 테이블 슬롯 번호

 bitand(:p2, to_number(';ffff', 'xxxx')) + 0

• TX Lock발생원인 6가지
• DML 로우 Lock
• 무결성 제약 위배 가능성
• 비트맵 인덱스 엔트리 갱신
• ITL 슬롯 부족
• 인덱스 분할
• 기타

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

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

• Dept 테이블 deptno 컬럼에 PK 인덱스가 생성돼 있는 상황에서 두 트랜잭션이 다음과 같이 진행하면, enq:TX - row lock contention 대기 이벤트가 Shared 모드로 발생
  1. 트랜잭션 TX1이 dept 테이블에 deptno = 40인 레코드를 입력
  2. 트랜잭션 TX2도 dept 테이블에 deptno = 40인 레코드를 입력하면, TX1이 커밋 또는 롤백할 때까지 Shared 모드로 enq:TX - row lock contention 대기 이벤트가 발생
  3. TX1이 커밋하면 TX2는 ORA-00001에러를 만나게 된다.
  4. TX1이 롤백하면 TX2는 정상적으로 입력이 완료
• Dept와 emp 테이블이 1:M 관계고, deptno 컬럼으로 dept, deptno를 참조하도록 emp 테이블에 FK 제약이 설정돼 있다고 가정하자. 이런 상황에서 두 트랜잭션이 아래와 같이 진행하면, 마찬가지고 enq:TX - row lock contention 대기 이벤트가 Shared 모드로 발생
  1. 트랜잭션 TX1이 deptno 테이블에 deptno = 40인 레코드를 지운다.
  2. 트랜잭션 TX2가 emp 테이블에 deptno=40인 레코드를 입력하면, TX이 커밋 또는 롤백할 때까지 Shared 모드로 enq:TX - row lock contention 대기 이벤트가 발생
  3. TX1이 커밋하면 TX2는 ORA-02291 에러를 만나게 된다.
  4. TX1이 롤백하면 TX2는 정상적으로 입력이 완료 된다.
• 비트맵 인덱스 엔트리에 대한 갱신을 수행할 때도 Shared 모드로 enq:TX - row lock contention 이벤트가 발생할 수 있다. 비트맵 인덱스는 구조상 하나의 엔트리가 여러 개 레코드와 매핑되므로 하나의 엔트리에 Lock을 설정하면 매핑되는 레코드 전체에 Lock이 설정되므로, 비트맵 인덱스 엔트리를 두 개 이상 트랜잭션이 동시에 갱신할 때 이 이벤트가 자주 발생{}{}한다.

h4.TX Lock : ITL 슬롯 부족
블록에 레코드를 추가/갱신/삭제하려면, ITL 슬롯을 먼저 할당 받고 그 곳에 트랜잭션 ID를 먼저 기록{}{}해야 한다. 비어 있는 ITL 슬롯이 없다면{}{}, ITL 슬롯을 사용중인 트랜잭션 중 하나가 커밋 또는 롤백할 때까지 기다려야 하며, 이때 Shared 모드 enq:TX - allocate ITL entry 대기 이벤트가 발생한다{}{}. 한 블록을 동시에 갱신할 수 있는 트랜잭션 개수는 ITL 슬롯에 의해 결정되고 슬롯당 24 바이트 공간을 차지 한다. ITL 슬롯 개수는 INITRANS 파라미터로 설정한다.

Create table t(....) INITRANS 5 MAXTRANS 255 PCTFREE 30;

• PCTFREE는 원래 컬럼UPDATE를 위해 예약된 공간이나 INITRANS에 의해 미리 할당된 ITL슬롯이 모두 사용 중일 때 새로운 트랜잭션이 ITL 슬롯을 요청하면 이 공간을 활용한다.
• 최대한 생성할 수 있는 ITL 슬롯 개수는 MAXTRANS에 의해 결정된다.
• ITL 슬롯 부족에 의한 대기 현장은 다음 둘 중에 하나에 해당한다.
  • 동시에 블록을 갱신하려는 트랜잭션 개수가 MAXTRANS 값을 초과
  • PCTFREE를 0으로 지정했거나 PCTFREE 예약 공간을 모두 사용한 상태에서 새로운 트랜잭션을 위한 ITL 슬롯이 부족

• 테이블에 INSERT{*}{+}할때는 ITL 슬롯이 부족하더라도 굳이 그 블록에 INSERT 하려고 대기 하지 않는다.* *새 블록을 할당해 그곳에 INSERT{+}{*}하면 되기 때문이며, ORACLE 9i 부터 이렇게 동작하기 시작하였다. (freelist가 아니라 bitmap으로 관리하기 시작하면서부터 가능)
• 인덱스에 값을 삽입할 때는 정렬 상태를 유지해야 하므로 여전히 ITL 경합이 발생{}{}한다. Update, delete 일 때는 테이블, 인덱스를 불문하고 ITL경합{}{}이 나타날 수 있다.
• INITRANS, MAXTRANS 설정과 관련하여 9i부터는 테이블에 INITRANS 를 3보다 작게 설정하더라도 오라클이 기본적으로 3개의 ITL 슬롯을 할당{}{}한다. 10g에서는 MAXTRANS를 위해 사용자가 지정한 값이 무시되면 항상 255개로 고정된다. PCTFREE에 의해 예약된 공간이 updated 의해 모두 사용되고 나면 여전히 ITL 경합이 발생{}{}할 수 있다.
• ITL 경합에 의한 대기 현상이 자주 발생하는 SEGMENT에 대해서는 INITRANS를 늘려 주어야 하며, 그런 세그먼트 목록은 v$segstat(10g이상)을 통해 확인 가능

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

• INITRANS 값을 변경하더라도 기존에 할당된 블록의 ITL 슬롯 개수에는 변함이 없고, 새로 할당된 블록에만 적용{}{}된다. 따라서 기존 블록에서 ITL경합이 빈번하게 발생한다면, 테이블 또는 인덱스 전체를 재생성{}{}해야 한다.

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

• 현재 5번과 9번 리프 블록이 꽉 차있는 상태에서 5번 블록에 새로운 값을 입력하려면 트랜잭션은 인덱스 분할을 실시해야 한다.

• 인덱스 분할이 완료된 후 모습이다. 5번과 6번 블록 사이에 10번 블록이 삽입되었고, 5번 블록에 있던 레코드 절반이 10번 블록으로 이동하였다.

• 9번 블록에 새로운 값을 추가하려는 트랜잭션이 생겼다. 9번 블록도 꽉 찬 상태이므로 먼저 입력한 공간을 확보해야 한다. 맨 우측에 값을 추가하려는 것이므로 레코드가 이동할 필요는 없으며, 새 블록을 추가해 주기만 하면 된다.

• 인덱스 분할이 진행되는 동안 그 블록에 새로운 값을 입력하려는 또 다른 트랜잭션이 생길 수 있다는 점이다. 그러면 두 번째 트랜잭션은 선행 트랜잭션이 인덱스 분할을 완료할 때까지 대기해야 하며, Shared 모드에서 enq:TX - index contention 이벤트를 만나게 된다.
• TX Lock은 선행 트랜잭션이 커밋 또는 롤백할 때 비로소 해제되는데, 만약 인덱스 분할을 진행한 트랜잭션이 커밋하지 않은 채 계속 다른 갱신 작업을 진행한다면 TX Lock을 대기하던 트래잭션은 어떻게 될까? (이 문제를 해결하려면 autonomous 트랜잭션을 사용한다.)
  1. TX1 트랜잭션이 인덱스에 로우를 삽입 하려는 순간 빈 공간을 차지 못했다. 인덱스 분할이 필요하다.
  2. TX1 트랜잭션은 autonomous 트랜잭션 TX2를 생성해 인덱스 분할을 진행토록 한다.
  3. 인덱스 분할이 진행중인 블록에 TX3 트랜잭션이 로우를 삽입하려고 한다. 이 트랜잭션은 enq:TX - index contention 이벤트를 만나게 되고, TX2 트랜잭션이 커밋 할 때까지 대기 한다.
  4. 인덱스 분할이 완료되면 TX2 트랜잭션은 커밋한다. Autonomous 트랜잭션이므로 TX1이 커밋되지 않은 상태로 계속 트랜잭션을 진행할 수 있다.
  5. TX3트랜잭션도 작업을 재개 한다.
• 인덱스 분할을 최소화 하는 방안으로, PCTFREE를 증가시키면 된다고 흔히 알려 졌다. 그런데 이 방법은 대개 효과가 없거나 일시적이라고 할 수 있다. (데이터가 없는 상태에서 인덱스를 만들면서 PCTFREE를 크게 설정한 경우는) PCTFREE 설정은 인덱스를 처음 생성하거나 재생성하는 시점에만 적용{}{}되기 때문에이다.
• 인덱스를 생성하는 시점에, 나중에 발생할 INSERT를 위해 공간을 남겨두는 것이므로 인덱스 분할을 최소화할 목적으로 제공되나 공간을 남겨두더라도 언젠가 다시 채워지므로, 인덱스를 주기적으로 재생성 하지 않는 한 근본적인 해결책이 되지는 못한다.
• 테이블에서의 PCTFREE 공간이 나중에 발생할 update를 위해 남겨두는 공간인데 반해, 인덱스의 PCTFREE는 insert를 위해 남겨두는 공간{}{}이다. 인덱스에는 update 개념이 없기 때문에 (delete & insert 방식) 테이블처럼 update를 위해 공간을 남겨둔다면 영영 사용되지 않는 죽은 공간이 될 것이다.
• 참고로, 우측 맨 끝으로만 값이 입력되는 Right Growing (순차적으로 증가하는 일련번호 컬럼에 인덱스를 생성하는 경우) 인덱스라면, PCTFREE를 0으로 설정하는 것이 인덱스 크기를 줄이는 데에 도움{}{}이 된다.

h4.TX Lock : 기타 트랜잭션 Lock
Shared 모드 enq:TX - contention 대기 이벤트는 분산 트랜잭션에서 2-Phase 커밋을 위한 PREPARED TX Lock을 대기할 때 발생한다. 앞에서 열거한 중요한 TX Lock 이외의 트랜잭션 대기 상황을 여기에 모두 포함한 것 같다.
h4.TX Lock : DML 로우 Lock
DML Lock은 다중 사용자에 의해 동시에 액세스되는 사용자데이터의 무결성을 보호해 준다. DML수행중에 호환되지 않는 다른 DML 또는 DDL 오퍼레이션의 수행을 방지 시켜주는 것이다.
그중 로우 Lock은 두개의 동시 트랜잭션이 같은 로우를 변경하는 것을 방지 한다. 하나의 로우를 변경하려면 로우 Lock을 먼저 획득해야 한다.

• Row-level Lock과 TX Lock을 조합해서 로우 Lock을 구현{}{}하였다. 로우를 갱신하려면 UNDO 세그먼트에서 트랜잭션 슬롯을 먼저 할당 받고, Enqueue 리소스를 통해 TX Lock을 획득{}{}한다. 그런 후 insert, update, delete, merge 문장을 통해 갱신하는 각 로우마다 Exclusive 모드로 로우 단위 Lock을 획득한다. TX Lock은 트랜잭션을 시작할 때 한번만 획득한다.
• Row-level Lock은 TX1 트랜잭션이 로우 정보를 갱신할 때는, 블록 헤더 ITL 슬롯에 트랜잭션 ID를 기록하고, 로우헤더에 이를 가리키는 Lock Byte를 설정{}{}한다. 이 레코드를 액세스 하려는 다른 트랜잭션은, 로우 헤더에 설정한 Lock Byte를 통해 ITL슬롯을 찾고, ITL 슬롯이 가리키는 UNDO 세그먼트 헤더의 트랜잭션 슬롯에서 트랜잭션 상태 정보를 확인함으로써 해당 레코드에 대한 액세스 가능 여부를 결정
• TX1 트랜잭션이 진행중일 때, 이 레코드를 읽으려는 TX2 트랜잭션은 TX1 트랜잭션의 상태를 확인하고 CR 블록을 생성해서 읽기 작업을 완료 한다. 오라클은 로우 단위 Lock과 다중 버전 읽기 일관성 매커니즘을 이용함으로써 읽기 작업에 대해서는 절대 Lock에 의한(분산 트랜잭션 제외) 대기 현상이 발생하지 않도록 구현
• TX Lock은 TX1이 갱신 중인 레코드를 같이 갱신하려는 TX2트랜잭션은 TX1트랜잭션이 완료될 때까지 대기{}{}해야 한다. 이를 위해 TX Lock이 필요하다.
  • 로우 단위 Lock : 블록 헤더 ITL과 로우 헤더 Lock Byte 설정을 의미함. 이를 통해 로우를 갱신 중인 트랜잭션 상태를 확인하고 액세스 가능 여부를 결정
  • TX Lock : Enqueue 리소스를 통해 TX Lock을 설정하는 것을 의미함. Lock이 설정된 레코드를 갱신하고자 할 때 Enqueue 리소스에서 대기함
• DML 로우 Lock에 의한 TX Lock 때문에 블로킹된 세션을 관찰해 보면, Exclusive 모드의 enq:TX - row lock contention 대기 이벤트가 지속적으로 나타난다.

h4.TM Lock : DML 테이블 Lock
로우 Lock 획득 시, 해당 테이블에 대한 테이블 Lock도 동시에 획득한다. 그럼으로써 현재 트랜잭션이 갱신 중인 테이블에 대한 호환되지 않는 DDL 오퍼레이션을 방지{}{}한다. 테이블 구조를 변경하지 못하도록 막는 것이다.

• 테이블 Lock을 걸려ㅕ면 아래처럼 명시적으로 Lock Table 명령어를 이용할 수도 있다.
  • Lock table emp in row share mode
  • Lock table emp in row exclusive mode
  • Lock table emp in shared mode
  • Lock table emp in share row exclusive mode
  • Lock table emp in exclusive mode
• 오라클은 변경 작업에만 로우 Lock을 사용하므로, 로우 Lock은 항상 Exclusive 모드다. 하지만 테이블 Lock에는 여러가지 Lock 모드가 사용되며, Lock 모드간 호환성을 정리하면 다음과 같다.

• • RS : row share (SS : sub share)
  • RX : row exclusive (SX : sub exclusive)
  • S : share
  • SRX : share row exclusive (SSX : share/sub exclusive)
  • X : exclusive
• 선행 트랜잭션과 호환되지 않는 모드로 테이블 Lock을 설정하려면 후행 트랜잭션은 대기하거나 작업을 포기해야 한다.
• Insert, update, delete, merge 문을 위해 로우 Lock을 설정하려면 해당 테이블에 RX(=SX) 모드 테이블 lock을 먼저 획득{}{}해야 한다. Select for update문을 위해 로우 Lock을 설정하려면 RS(=SS) 모드 테이블 Lock을 먼저 획득{}{}해야 한다. RS, RX 간에는 어떤 조합으로도 호환이 되므로 select for update나 DML문 수행 시 이들 간에 테이블 Lock에 의한 경합은 절대 발생하지 않는다. 다만, 같은 로우를 갱신하려 할 때 로우 Lock에 의한 경합은 발생{}{}한다.
• 오라클은 테이블 Lock도 Enqueue로 구현하였으며, 'TM Enqueue'라고 부른다. 이런 이유로 테이블 Lock을 'TM Lock'이라고ㅗ 부르기도 한다. TM Enqueue 리소스 구조체의 식별자는 아래와 같다.
  • TYPE : TM
  • ID1 : 오브젝트 ID
  • ID2 : 0
• 선행 트랜잭션이 TM Lock을 해제하기를 기다리는 트랜잭션에서 발생하는 대기 이벤트를 모니터링 해보면, enq : TM - contention 이벤트가 지속적으로 나타난다.
  {info:title=대상 리소스가 사용주일 때, 진로 선택}
  Lock을 얻고자 하는 리소스가 사용 중일 때, 프로세스는 아래 3가지 방법 중 하나를 택한다. 보통은 내부적으로 진로가 결정돼 있지만, select for update 문 처럼 사용자가 선택할 수 있는 경우도 있다.

• • Lock이 해제될 때까지 기다린다. (select * from t for update)
  • 일정 시간만 기다리다 포기한다. (select * from t for update wait 3)
  • 기다리지 않고 작업을 포기한다. (select * from t for update nowait)

DML문을 수행할 때 묵시적으로 테이블 Lock을 얻게 되는데 이때는 1번, 기다리는 방법을 선택한다. Lock Table 명령을 이용해 명시적으로 테이블 Lock을 얻을 때도 기본적으로 기다리는 방법을 택하지만, NOWAIT 옵션을 이용해 곧바로 작업을 포기하도록 사용자가 지정해 줄 수 있다.

Lock table emp in exclusive mode NOWAIT;

DDL 문을 수행할 때도 내부적으로 테이블 Lock을 얻는데, 이때는 NOWAIT 옵션이 자동으로 지정된다.

오라클은 2번 옵션(wait) 에 의해 작업을 포기할 때, 아래 메시지를 던진다.
ORA-30006 : resource busy; acquire with WAIT timeout expired
3번 옵션(nowait)에 의해 작업을 포기할 때는 아래 메시지를 던진다.
ORA-00054 : resource busy and acquire NOWAIT specified

-- Session 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

-- session 2
SQL>update t
  2  set c2=3
3  where c1 = 2; ? Hang 상태
 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

SCOTT@TUNING >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

SID TY MODE_HELD  MODE_REQD  USN/Table         SQN BLOCKING
---------- -- ---------- ---------- ---------- ---------- ---------
      1072 TM Row-X (SX) None       T
      1072 TX Exclusive  None       10             655388

SQL>update t
  2  set c2=3
3  where c1 = 2; ? Hang 상태
SID TY MODE_HELD  MODE_REQD  USN/Table         SQN BLOCKING
---------- -- ---------- ---------- ---------- ---------- --------------------
      1057 TM Row-X (SX) None       T
      1057 TX None       Exclusive  10             655388
      1072 TM Row-X (SX) None       T
      1072 TX Exclusive  None       10             655388  <<<<<

SQL>create table test1 (x int);
SQL>create table test2 (x int);
SQL>insert into test1 values(1);
SQL>insert into test2 values(1);
SQL>select usernmae, v$lock.sid, id1, id2
  2  , lmode, request, block, v$lock.type
  3  from v$lock, v$session
  4  where v$lock.sid = v$session.sid
  5  and v$session.username = 'SCOTT';   
USERNAME          SID        ID1        ID2      LMODE    REQUEST      BLOCK TY
---------- ---------- ---------- ---------- ---------- ---------- ---------- --
SCOTT            1057      61243          0          3          0          0 TM
SCOTT            1057      61244          0          3          0          0 TM
SCOTT            1057     589836      13209          6          0          0 TX
SQL> select object_name, object_id from user_objects;

OBJECT_NAME                     OBJECT_ID
------------------------------ ----------
TEST1                               61243
TEST2                               61244