Enq: TM - contention

EXEM Knowledge Base

Jump to: navigation, 찾기

목차

[편집] Basic Info

DML이 수행되는 동안, DML과 관련된 객체에 대한 변경을 방지하기 위해 DML을 수행하는 프로세스는 반드시 해당 테이블에 대해 TM 락을 획득해야 한다. TM 락을 획득하는 과정에서 경합이 발생하면 enq: TM - contention 이벤트를 대기하게 된다. 오라클의 Concept 매뉴얼과 Reference 매뉴얼에서는 락의 분류를 다음과 같이 설명하고 있다.

  • DML 락 : Data lock. DML수행시 데이터를 보호하기 위한 락. 로우 락(TX)은 특정 로우를 보호하고, 테이블 락(TM)은 전체 테이블을 보호한다. dba_dml_locks 뷰를 통해 관찰 가능하다.
  • DDL 락 : Data dictionary lock. User/Table/View/Procedure등의 정의를 보호한다. dba_ddl_locks 뷰를 통해 관찰 가능하다.
  • Internal locks and latches

위의 설명은 DML 락과 DDL 락이라는 별개의 락이 존재하는 것처럼 혼란을 줄 소지가 있다. 실제로 DML 락과 DDL 락은 락을 적절히 분류하기 위해 부여한 이름일 뿐이다. 이 점에 유의하기 바란다.

DML 락은 실제로는 TM 락과 일치한다. DML 락은 DBA_DML_LOCKS 뷰를 통해 관찰가능한데, 이 뷰는 V$LOCK 뷰에서 락 유형이 TM인 것만을 추출해서 보여주는 역할을 한다. 데이터베이스에서 허용 가능한 TM 락의 개수는 DML_LOCKS 파라미터로 지정할 수 있다. 만일 DML_LOCKS 파라미터 값을 0으로 주면 테이블에 대해 TM 락이 획득되지 않는다. 이 경우 오라클은 테이블 정의가 보호되는 것을 보장하기 위해 이미 존재하는 테이블에 대한 DD[을 원천적으로 허용하지 않는다. 따라서 TM 락을 획득하지 않고도 아무런 걱정없이 테이블의 특정 로우를 변경하는 것이 허용된다. OPS와 같은 환경에서 TM 락을 글로벌하게 획득하는 데서 오는 오버헤드를 줄이기 위해 DML_LOCKS 값을 0으로 변경하기도 한다.

DDL 락은 실제로는 library cache lock과 일치한다. DDL락은 DBA_DDL_LOCKS 뷰를 통해 관찰 가능한데, 이 뷰는 실제로는 X$KGLLK 뷰를 적절히 가공해서 보여주는 역할을 한다. DDL 락은 DBA_DDL_LOCKS 뷰 외에도 X$KGLLK, DBA_KGLLOCK와 같은 뷰를 통해 관찰 가능하다.

일반적인 DML 문은 테이블에 대해 TM 락Sub-Exclusive(SX)모드로 획득한다. Sub-Exclusive 모드간에는 상호호환성이 있기 때문에 여러 세션이 동일 테이블에 대해 동시에 DML을 수행하는 것이 가능하다. DML을 수행한 세션은 테이블에 대해서는 TM 락을 Sub-Exclusive 모드로 획득하고, 변경된 데이터에 대해서는 TX 락을 Exclusive 모드로 획득한다.

아래와 같이 V$LOCK 뷰를 통해 DML을 수행한 세션이 어떤 락을 어떤 모드로 획득하는지 관찰할 수 있다.

SQL> update test set id = 1 where rownum = 1;
...
SQL> exec print_table('select * from v$lock where sid = 148');

ADDR                           : C0000000ED2663D0
KADDR                         : C0000000ED2663F8
SID                              : 148
TYPE                            : TM
ID1                              : 54679
ID2                              : 0
LMODE                         : 3
REQUEST                      : 0
CTIME                          : 67
BLOCK                         : 0
-----------------
ADDR                           : C0000000ED2DB7F8
KADDR                         : C0000000ED2DB980
SID                              : 148
TYPE                            : TX
ID1                              : 4718594
ID2                              : 439
LMODE                         : 6
REQUEST                      : 0
CTIME                          : 67
BLOCK                         : 0

오브젝트 아이디 54679(ID1)에 해당하는 오브젝트에 대해 TM 락Sub-Exclusive(3) 모드로 획득중임을 확인할 수 있다. TM 락의 ID1은 테이블의 오브젝트 아이디(Object ID)에 해당한다. 따라서 DBA_OBJECTS 뷰와 조인하면 어떤 테이블에 대한 TM 락인지 판단할 수 있다. Sub-Exclusive 모드간에서 호환성이 있기 때문에 DML간에는 TM 락을 둘러싼 경합이 발생하지 않는다. 따라서 enq: TM - contention 대기도 발생하지 않는다. TM Lock 경합이 발생하는 경우는 보통 다음과 같다.

  • 인덱스가 없는 Foreign key의 부모키를 변경하는 경우
  • DML과 DDL 간의 TM 락 경합
  • Lock table .. 에 의한 TM 락 경합
  • Direct load 작업에 의한 TM 락 경합

[편집] Parameter & Wait Time

[편집] Wait Parameters

  • P1 : Enqueue 정보
  • P2 : Object#
  • P3 : Table/Partition 정보

[편집] Wait Time

enqueue 대기이벤트와 동일하다. 최대 3초까지 기다린다. 만일 TM 락을 획득하기 못하면 획득할 때까지 대기한다.

[편집] Check Point & Solution

[편집] 인덱스가 없는 Foreign Key

오라클 9i 이전에는 인덱스가 없는 foreign key 컬럼이 TM 락 경합의 주된 원인이었다. 오라클 9i 이전 버전들에서는 자식 테이블의 foreign key 컬럼에 인덱스가 없는 상태에서 부모 테이블의 Key가 변경되면, 자식 테이블에 대해 Shared 모드나 Shared-Sub-Exclusive 모드로 TM 락을 획득해야 했다. 획득된 TM 락은 부모 Key를 변경한 트랜잭션이 종료(commit 또는 rollback)될 때까지 유지되기 때문에 이로 인한 경합이 심각한 성능저하 현상을 일으키곤 한다. 하지만, 다행히도 오라클 9i부터는 알고리즘이 개선되어 더 이상 이와 같은 경합은 발생하지 않는다. 오라클 9i 이전버전을 사용하고 있다면, foreign key 컬럼에 반드시 인덱스를 생성하는 습관을 가질 필요가 있다.

[편집] 부적절한 DDL로 인한 TM 락 경합

트랜잭션이 진행중인 테이블에 대해서는 기본적으로 DDL이 불가능하다. 따라서 이 경우에는 경합으로 인한 성능 문제는 발생하지 않는다. 아래 예를 보자. 세션 A:

SQL> update test set id = 1 where rownum = 1;

세션 B:

SQL> alter table test add ID2 number;
alter table test add ID2 number
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified

Update가 이루어지고, 아직 커밋이 되지 않은 테이블에 대해서는 DDL을 수행하는 것이 불가능하다. 반면, DDL이 수행중인 테이블에 대해서 DML을 수행하는 경우에는 TM 락 경합이 발생할 수 있다. 특정 프로세스가 테이블에 대해서 인덱스를 생성하는 경우, 인덱스가 생성되는 동안에는 테이블에 대해서 TM 락을 Shared 모드로 획득하게 된다.

SQL> create index big_objects_idx_test 
on big_objects(owner,object_name,object_id);
<-- 인덱스가 생성되고 있는 동안 TM 락이 어떤 모드로 획득되는지 관찰한다.
SQL> exec print_table('select * from v$lock where sid = 148');
...
ADDR                          : C0000000ED2663D0
KADDR                        : C0000000ED2663F8
SID                             : 148
TYPE                           : TM
ID1                             : 52318
ID2                             : 0
LMODE                        : 4       <-- Shared 모드
REQUEST                     : 0
CTIME                         : 3
BLOCK                        : 0
...

TM 락의 ID1을 이용하면 BIG_OBJECTS 라는 테이블명을 얻을 수 있다. LMODE의 값은 4이며, Shared 모드를 의미한다. 즉, create index 문은 big_objects 테이블에 대해서 Shared 모드로 TM 락을 획득하게 된다. 문제는, 이 테이블에 대해 DML을 수행하는 세션은 Sub-Exclusive 모드로 TM 락을 획득해야 한다는 것이다. Shared 모드와 Sub-Exclusive 모드는 호환성이 없기 때문에 DML을 수행하는 세션은 DDL 수행이 완료될 때까지 대기하게 되고, 이때 enq: TM - contention 대기이벤트가 발생하게 된다. DDL로 인한 TM 락 경합을 줄일 수 있는 방법으로는 다음과 같은 것들이 있다.

  • 데이터 량이 많은 테이블에 대한 부적절한 DDL을 수행하게 되면, 해당 테이블을 접근하는 모든 DML 세션이 대기상태에 빠지게 되고, 장애상황으로까지 발전할 수 있다. 적절한 관리를 통해 원천적으로 방지하는 것이 좋다.
  • DDL 수행 시 가능하면 온라인(Online) 옵션을 사용한다. 오라클이 버전업될 수록 온라인으로 수행 가능한 DDL은 점점 증가하는 추세다. 대부분의 일반적인 DDL에서 온라인 옵션을 사용하는 것이 가능하다. 가령, create index 명령을 온라인 옵션으로 수행할 경우 테이블에 대해 Shared 모드가 아닌 Sub-Shared(SS) 모드로 TM 락을 획득한다. Sub-Shared 모드는 Sub-Exclusive 모드와 호환성이 있기 때문에 인덱스를 생성하는 도중에 DML을 수행하는 것이 가능하다. 즉 enq: TM - contention 대기가 발생하지 않는다. 아래 예를 보자.

세션A:(SID=148)

SQL> create index big_objects_idx_test111 on 
big_objects(secondary,generated,temporary,status,timestamp,last_ddl_time) 
online;   <-- Online 옵션으로 생성

세션B:

SQL> exec print_table('select * from v$lock where sid = 148');
...
ADDR                          : C0000000ED2663D0
KADDR                        : C0000000ED2663F8
SID                             : 148
TYPE                           : TM
ID1                             : 52318
ID2                             : 0
LMODE                        : 2    <-- Sub-Shared 모드
REQUEST                     : 0
CTIME                         : 1
BLOCK                        : 0
...
  • Parallel DDL을 사용해 DDL의 수행속도를 극대화한다. 대용량 데이터에 대한 DDL 수행 시 Parallel 옵션을 적절히 사용하면 DDL 자체의 성능을 극대화할 수 있다. 더불어 Nologging 옵션을 함께 사용해도 좋다. DDL의 수행속도가 향상되면, 그와 비례해서 TM 락에 의한 대기시간도 줄어들게 된다.

[편집] Lock table .. 을 이용해 의도적으로 TM 락을 획득하는 경우

Lock table ... 문을 이용해 의도적으로 TM 락을 획득하는 경우에 TM 락 경합이 발생할 수 있다. 세션A:(SID = 148)

SQL> update test set id = 1 where rownum = 1;

세션B:(SID = 150) SQL> lock table test in exclusive mode;

.. Wait ...

위와 같이 세션 A에서 update에 의해 TM 락 이 Sub-Exclusive 모드로 획득된 상태에서, 세션 B 가 lock table 명령을 이용해 TM 락을 Exclusive하게 획득하려고 시도하는 경우 경합이 발생하게 되고 enq: TM - contention 이벤트를 대기하게 된다.

TM 락에 의한 경합이 발생하는 경우, 락 홀더(Lock Holder) 세션에서 수행한 SQL 문을 수집하는 것이 대단히 중요하다. TM 락의 경우 발생할 수 있는 경우가 매우 다양하기 때문에 SQL 문이 없으면 정확한 판단이 어려운 경우가 많다. 문제가 유발된 SQL문이 Lock table … 구문이고, 이로 인해 TM 락 경합이 광범위하게 이루어진다면, 어플리케이션의 적절한 수정으로 해결할 수 있다. 동기화를 위해서 테이블 전체에 락을 거는 것보다는 DMBS_LOCK 패키지를 사용하거나 select ... for update 등을 사용해서 락의 범위를 줄이는 방법을 고려해야 한다.

[편집] Direct/Parallel Load 작업을 수행한 경우

INSERT /*+ APPEND */ INTO ... 나 SQL*Loader의 direct path load와 같은 일부 기능들은 해당 테이블에 대해 TM 락을 Exclusive하게 획득한다. Direct load 작업은 SGA를 경유하지 않고 데이터파일에 직접 쓰기를 수행하므로 작업이 수행되는 동안 테이블에 어떠한 변화도 허용해서는 안 된다. 따라서 TM 락을 Exclusive하게 획득함으로써 테이블에 대한 어떠한 변경도 허용되지 않는다는 것을 보장받아야만 작업이 가능하다. Direct load 작업이 수행되는 동안은 테이블에 대한 어떠한 DDL이나 DML도 허락되지 않는다. 따라서 트랜잭션이 많은 시점에 Direct load 작업을 수행할 때는 TM 락 경합으로 인한 문제가 발생할 소지가 있는지를 확인해야 한다. SQL*Loader를 parallel 모드로 수행하는 경우에는 테이블에 대해 TM 락을 Shared 모드로 획득한다. 따라서 이 경우에도 다른 세션에서의 DDL이나 DML은 허락되지 않는다.

[편집] Event Tip

[편집] Analysis Case