Enq: TX - row lock contention
EXEM Knowledge Base
목차 |
[편집] Basic Info
enq: TX - row lock contention 이벤트는 로우 레벨 락(row level lock)에 의한 경합이 발생했다는 것을 의미한다. 다음과 같은 세 가지 경우에 로우 레벨 락 경합이 발생한다.
- 여러 세션이 동일 로우를 변경하는 경우
- 여러 세션이 동일 Unique Key 충돌을 일으키는 경우
- 여러 세션이 비트맵 인덱스 충돌을 일으키는 경우
[편집] 여러 세션이 동일 로우를 변경하는 경우(mode=6)
동일 로우 변경은 TX 락에 의한 경합이 발생하는 가장 보편적인 경우이다. 특정 프로세스가 특정 로우을 변경하기 위해 해당 로우를 방문했을 때, 현재 로우가 변경된 상태라면 ITL로부터 해당 로우를 변경한 트랜잭션을 확인하고, 자기 자신을 TX Enqueue 목록에 추가하고 enq: TX - row lock contention 이벤트를 대기한다. 이 대기는 해당 로우에 대해 TX 락을 보유한 프로세스가 락을 해제할 때까지 계속된다. 아래 예를 보자.
세션A:(SID = 150) SQL> update test set id = 1 where rownum = 1; 세션B:(SID = 148) SQL> update test set id = 1 where rownum = 1; ... Wait ...
세션 B는 세션 A가 이미 변경한 로우를 업데이트하기를 원하므로 대기상태에 빠지게 된다. 이 상태에서 TX 락의 대기현황을 V$LOCK 뷰를 통해 관찰하면 다음과 같다.
세션C:
SQL> exec print_table('select * from v$lock where type = TX');
ADDR : C0000000ED2DC938
KADDR : C0000000ED2DCAC0
SID : 150
TYPE : TX
ID1 : 1507368
ID2 : 7763
LMODE : 6 ? TX 락을 Exclusive 모드로 획득중
REQUEST : 0
CTIME : 235
BLOCK : 1
----------------------------------------------------
ADDR : C0000000EE0A78D8
KADDR : C0000000EE0A78F8
SID : 148
TYPE : TX
ID1 : 1507368
ID2 : 7763
LMODE : 0
REQUEST : 6 ? TX 락을 Exclusive 모드로 대기중
CTIME : 226
BLOCK : 0
테스트) 동일 로우 업데이트에 의한 TX 락 경합
위의 결과는 다음과 같이 해석할 수 있다. "세션A(SID=150)가 TX 락(ID1=1507368, ID2=7763)을 Exclusive 모드(LMODE=6)로 획득한 상태이며, 세션B(SID=148)이 동일한 ID1, ID2에 대해 TX 락을 Exclusive 모드(REQUEST=6)모드로 획득하기 위해 대기중이다". TX 락이 보호하는 자원은 "트랜잭션"으로, Part1의 “트랜잭션과 OWI”에서 설명한 바와 같이 트랜잭션은 USN+SLOT+SQN 정보로 표현된다. TX 락의 ID1 값은 usn + slot이며, ID2 값은 sqn에 해당한다. 따라서 V$LOCK 뷰를 통해 어떤 트랜잭션에서의 경합 현상인지 파악할 수 있다. enq: TX - row lock contention 이벤트의 P2, P3 값은 TX 락의 ID1, ID2 값과 매칭된다. V$SESSION_WAIT 뷰에서 세션B(SID=148)의 대기현상을 조회하면 다음과 같은 결과를 얻을 수 있다.
SQL> exec print_table('select * from v$session_wait where sid = 148');
SID : 148
SEQ# : 341
EVENT : enq: TX - row lock contention
P1TEXT : name|mode
P1 : 1415053318
P1RAW : 0000000054580006
P2TEXT : usn<<16 | slot
P2 : 1507368
P2RAW : 0000000000170028
P3TEXT : sequence
P3 : 7763
P3RAW : 0000000000001E53
WAIT_CLASS_ID : 4217450380
WAIT_CLASS# : 1
WAIT_CLASS : Application
WAIT_TIME : 0
SECONDS_IN_WAIT : 30
STATE : WAITING
-----------------
-----------------
여러 세션이 Unique Key 충돌을 일으키는 경우(mode=4) Unique Key 또는 Primary Key 충돌이 발생할 때도 TX 락 경합이 발생하게 된다. 프로세스 A가 Insert를 수행한 후, 프로세스 B가 Unique Key 충돌이 발생하게끔 Insert를 수행하면, 프로세스 B는 TX 락을 Shared 모드로 획득하기 위해 대기한다. 이때의 대기현상은 enq: TX - row lock contention 이벤트로 나타난다. 프로세스 B는 프로세스 A가 커밋하거나 롤백할 때까지 대기하게 된다. 프로세스 A에서 커밋이 이루어지면, ORA-0001 에러상황이 되며, 롤백이 이루어지면 프로세스 B의 Insert는 성공적으로 이루어지게 된다. 아래의 테스트 스크립트를 보자.
세션 A:(SID=144) Unique Key를 생성하고 값을 insert 한다.
SQL> create unique index test_idx on test(id);
SQL> insert into test values(1);
세션 B:(SID=148) 동일한 값을 insert 한다.
SQL> insert int test values(1);
... Wait ...
세션 C:
SQL> insert int test values(1);
... Wait ...
세션 C:
SQL> exec print_table('select * from v$lock where sid in (148,144)
and type = TX');
ADDR : C0000000ED2EE058
KADDR : C0000000ED2EE1E0
SID : 144
TYPE : TX
ID1 : 917514
ID2 : 9024
LMODE : 6 ? TX 락을 Exclusive하게 획득 중
REQUEST : 0
CTIME : 114
BLOCK : 1
--------------------
ADDR : C0000000ED2B6738
KADDR : C0000000ED2B68C0
SID : 148
TYPE : TX
ID1 : 3407878
ID2 : 1985
LMODE : 6 ? TX 락을 Exclusive하게 획득 중
REQUEST : 0
CTIME : 102
BLOCK : 0
-----------------
ADDR : C0000000EE0A78D8
KADDR : C0000000EE0A78F8
SID : 148
TYPE : TX
ID1 : 917514
ID2 : 9024
LMODE : 0
REQUEST : 4 ? Shared 모드로 TX 락을 획득하기 위해 대기
CTIME : 102
BLOCK : 0
테스트) Unqiue Key 충돌에 의한 TX 락 경합
V$LOCK 뷰의 결과를 유심히 볼 필요가 있다. 먼저 Insert를 수행한 144번 세션은 하나의 TX 락(ID1=917514, ID2=9024)을 Exclusive하게 획득하고 있다. 반면 나중에 Insert를 수행한 148번 세션은 이미 하나의 TX 락(ID1=3407878, ID2=1985)을 Exclusive하게 획득한 상태에서 144번 세션에 의해 획득된 TX 락을 Shared 모드로 획득하기 위해 대기하고 있다. 이 결과에서 오라클이 Unique Key를 보호하기 위해서 어떤 방법을 사용하는지 간접적으로 추론할 수 있다. 오라클은 테이블에 로우를 추가한 후 인덱스도 함께 추가하게 되는데 인덱스를 추가하는 과정에서 Unique 속성을 위반하는지 확인한다. 만일 동일 키값이 존재하고 선행 트랜잭션이 이미 종료된 상태라면 ORA-0001 에러가 나고, 아직 선행 트랜잭션이 종료되지 않은 상태라면 선행 트랜잭션이 획득한 TX 락을 Shared 모드로 획득하기 위해 대기하게 된다.
[편집] 여러 세션이 비트맵 인덱스(Bitmap Index) 충돌을 일으키는 경우(mode=4)
비트맵 인덱스 충돌에 의한 TX 락 경합을 이해하려면 비트맵 인덱스의 내부 구조에 대해 약간의 지식이 필요하다. 비트맵 인덱스의 리프 노드는 비트리 인덱스(B-tree Index) 방식으로 관리된다. 비트리 인덱스의 리프 노드는 정렬된 형태로 인덱스 엔트리 들을 저장하며 개개의 인덱스 엔트리는 하나의 ROWID를 가리킨다. 따라서 Unique Key 충돌을 제외하고는 인덱스 엔트리 간에는 경합이 발생하지 않는다. 반면 비트맵 인덱스의 리프 노드는 "Column의 값 + Start rowid + End rowid + Bitmap값"의 형태를 지닌다. 즉, 하나의 리프 노드가 넓은 범위의 ROWID를 관리한다. 테이블의 로우가 변경될 때마다 비트맵 인덱스에 해당하는 컬럼값에 대해 로우가 속한 리프 노드의 비트맵을 매번 새로 계산해주어야 한다. 따라서 동시에 두 세션이 같은 리프 노드에 대해 비트맵 연산을 수행할 경우, 순서를 보장하기 위해 TX 락을 획득해야 한다. 즉, 특정 세션이 TX 락을 Exclusive하게 획득한 후, 비트맵 연산을 수행하고 아직 커밋을 하지 않았다면, 다른 세션들은 선행한 트랜잭션에 대해 TX 락을 Shared 모드로 확보하기 위해 대기함으로써 비트맵 연산이 끝나기를 기다리게 된다. 하나의 리프 노드가 넓은 범위의 ROWID를 관리하기 때문에 TX 락 경합이 광범위하게 나타날 수 있다. 비트맵 인덱스 충돌에 의해 TX 락 경합이 발생할 경우 enq: TX - row lock contention 이벤트를 대기한다. 아래의 테스트 스크립트를 보자.
세션A:
SQL> -- Bitmap Index를 생성
create table tx_bitmap_test
(name1 char(1000), name2 char(1000), name3 char(1000));
create bitmap index tx_bitmap_test_idx
on tx_bitmap_test(name1, name2, name3);
세션B: (SID = 148)
SQL> insert into tx_bitmap_test values('a', 'b', 'c');
1 row created.
세션C: (SID = 159)
SQL> insert into tx_bitmap_test values('a', 'b', 'c');
.... Wait ....
두번째 Insert를 수행한 세션 C는 대기상태에 빠진다. 이 상태에서 V$LOCK 뷰를 통해 TX 락이 어떻게 획득되는지 살펴보자.
세션A:
SQL> exec print_table('select * from v$lock where sid in (148,159) and type = TX');
ADDR : C000000074756E40
KADDR : C000000074756FC8
SID : 148
TYPE : TX
ID1 : 4653100
ID2 : 440
LMODE : 6 ? TX 락을 Exclusive하게 획득중
REQUEST : 0
CTIME : 118
BLOCK : 1
-----------------
ADDR : C00000007478E760
KADDR : C00000007478E8E8
SID : 159
TYPE : TX
ID1 : 5046280
ID2 : 444
LMODE : 6 ? TX 락을 Exclusive하게 획득중
REQUEST : 0
CTIME : 102
BLOCK : 0
-------------------
ADDR : C0000000750D2080
KADDR : C0000000750D20A0
SID : 159
TYPE : TX
ID1 : 4653100
ID2 : 440
LMODE : 0
REQUEST : 4 ? Shared 모드로 TX 락을 획득하기 위해 대기
CTIME : 102
BLOCK : 0
-----------------
테스트) 비트맵 인덱스에 의한 TX 락 경합
V$LOCK 뷰의 조회결과를 분석해보면, 먼저 Insert를 수행하는 148번 세션은 하나의 TX 락(ID1=4653100, ID2=440)를 Exclusive하게 획득하고 있다. 반면 나중에 Insert를 수행한 159번 세션은 이미 하나의 TX 락(ID1=5046280, ID2=444)을 Exclusive하게 획득한 상태에서 148번 세션에 의해 획득된 TX 락을 Shared 모드(request=4)로 획득하기 위해 대기하고 있다. 이 결과는 Unique Key 충돌에 의한 대기현상과 완전히 동일하다. 대기현상만으로는 Unique Key 충돌과 Bitmap Index 충돌간의 차이를 구별할 수 없으며, 테이블에 대해 생성된 인덱스의 정확한 정보와 SQL문장을 같이 고려해야만 정확한 원인을 밝힐 수 있다.
[편집] Parameter & Wait Time
[편집] Wait Parameters
- P1 : Enqueue 정보
- P2 : usn<<16 | slot
- P3 : sequence
[편집] Wait Time
enqueue 대기이벤트와 동일하다. 최대 3초까지 기다린다. 만일 TX 락을 획득하기 못하면 획득할 때까지 대기한다.
[편집] Check Point & Solution
[편집] 트랜잭션 관리
동일 로우 변경에 따른 TX 락 경합은 철저하게 어플리케이션 이슈이다. 장시간 수행되는 Update나 Delete 명령은 트랜잭션이 적은 시간대에 수행하는 것이 좋다. 또는 Update나 Delete 명령 자체의 성능을 개선하는 것 또한 방법이 된다. 특히 대량의 데이터를 Update하는 것은 굳이 TX 락 경합 문제뿐만 아니라 수많은 성능 문제를 야기할 수 있다. 대량의 Update를 대신하는 한가지 방법은 다음과 같다.
1. 기존 테이블 old_table을 복사한 테이블 new_table을 생성하되, 변경 내용을 저장한다. 즉, “Create table new_table as selec id, name, register_date, decode(class,1,’A’,2,’B’)… from old_table”와 같은 명령을 이용한다. 2. 새로 생성한 new_table 테이블에 old_table과 동일하게 인덱스 등을 생성한다. 3. 기존 테이블 old_table을 Drop하고 새로운 테이블 new_table을 old_table로 rename한다.
위의 작업을 수행할 때 nologging과 parallel 옵션을 같이 사용하면 원하는 작업을 더욱 빨리, 그리고 리소스를 적게 사용하면서 실행할 수 있다.
[편집] Unique Key 관리
Unique Key 충돌에 의한 TX 락 경합은 철저하게 어플리케이션 이슈이다. Unique Key를 생성하기 위해 별도의 계산법을 쓴다거나 기존 테이블에서 최대값(MAX)을 추출하는 방법 등을 사용할 경우 Unique Key 충돌에 의한 TX 락 경합은 언제든지 발생할 수 있다. 최선의 해결책은 시퀀스(Sequence)를 사용해서 Unique Key를 생성하는 것이다.
[편집] Bitmap Index의 효율적 사용
비트맵 인덱스는 읽기작업은 빈번하고, 쓰기 작업은 드문 테이블에 대해 DSS성 Select 문의 성능을 극대화하기 위해 고안된 것이다. DML이 빈번한 테이블에 대해 비트맵 인덱스를 함부로 사용하는 것은 대단히 위험하다. 로우가 변경될 때마다 비트맵값을 계산해야하기 때문에 DML자체의 성능이 저하되며, 더구나 동시에 여러 세션이 DML을 수행하는 경우에는 과도한 TX 락 경합이 발생한다. 만일 DML이 빈번한 테이블에 대해 DSS성 SQL 문의 성능을 보장하고 싶다면 비트맵 인덱스보다는 Materialized View와 같은 기능을 사용하는 것이 바람직하다.
[편집] Event Tip
[편집] 오라클 트랜잭션 개요
오라클 트랜잭션을 참조하세요.
[편집] 블록 덤프의 이해와 로우 레벨 락
Block Dump와 Row level lock을 참조하세요.
[편집] Delayed Block Cleanout의 개념
Delayed block cleanout을 참조하세요
[편집] 트랜잭션과 언두 헤더 덤프
Undo Header Dump를 참조하세요.
[편집] Analysis Case
[편집] 1. 동일 Row 변경에 의한 성능저하 현상
동시 사용자가 많은 OLTP 환경에서는 TX 락 경합에 의한 성능 저하 현상이 발생하는 경우가 많다. Oracle DBMS의 성능진단/분석 툴인 MaxGauge(맥스게이지)를 활용하여, TX 락 경합으로 인한 성능저하 문제의 원인을 규명해 보고자 한다.
[편집] 성능저하구간의 확인
[Trend Analysis] 화면으로부터 14시05분 ~ 14시14분 사이에 Active Session 수가 급증하는 것을 확인할 수 있다.
■ Active Session수의 추이그래프
[편집] 대기이벤트의 검출 및 분석
문제 구간에서 Active Session 수가 급증한 원인을 분석하기 위해, [Stat/Event/Ratio] 화면에서 문제시점(14시 11분)에 대기현상을 조회하면, 아래 그림과 같이 enqueue 이벤트 대기가 가장 높은 비중을 차지하고 있다.
문제 구간의 Active Session 목록을 조회하면, 아래 그림과 같이 대부분의 세션이 Exclusive 모드의 TX 락 경합에 의해 enqueue 이벤트를 대기하고 있다.
[편집] 대기이벤트 발생원인의 조사
｢Lock Tree｣ 화면을 통해 TX 락 경합 현상을 겪는 세션들을 확인하면 아래 그림과 같다.
1178번이 홀더 세션이며, 홀더 세션이 Exclusive 모드로 획득한 TX 락을 획득하기 위해 많은 수의 세션들이 대기 중이다. 즉, 홀더 세션이 긴 시간 동안 TX 락을 해제하지 않음으로써, enqueue 이벤트 대기가 광범위하게 발생하고 있다.
[편집] 세션 및 SQL 분석을 통한 문제원인의 규명
[Session List] 화면을 통해 홀더 세션인 1178번 세션이 수행한 SQL 이력을 조회하면 아래 그림과 같다.
위의 그림을 통해 다음과 같은 사실을 확인할 수 있다.
홀더 세션은 <456130> SQL문을 수행한 후, <4618474> SQL문을 수행했다.
대기 세션들은 <4618474> SQL문을 수행하는 과정에서 TX 락 경합에 의한 enqueue 이벤트를 대기한다. 즉, 홀더 세션은 테이블의 특정 로우를 변경하는 작업을 수행한 후 커밋을 수행하지 않은 상태에서 다른 작업을 수행 중이며, 이로 인해 테이블의 특정 로우를 변경하고자 하는 다른 세션들이 모두 TX 락 경합에 의한 enqueue 이벤트 대기를 겪는다.
즉, 테이블의 특정 로우를 변경한 후, 적절한 시점에 커밋을 수행하지 않은 것이 enqueue 이벤트 대기가 발생한 근본적인 원인이다. 어플리케이션 로직을 개선하여, 테이블의 특정 로우를 변경한 후에는 반드시 커밋을 수행하도록 하면 이 문제를 해결할 수 있다.
[편집] 결 론
성능 저하 현상 (enqueue 이벤트 대기에 의한 Active Session 수 증가) ☞
테이블의 로우를 변경한 후 커밋을 수행하지 않아 TX 락 경합 유발 ☞
테이블 로우 변경 후 반드시 커밋을 수행하여 해결 필요
[편집] 2. Unique Key 충돌에 의한 TX Enqueue 발생
문제를 분석하고자 하는 인스턴스의 Active Session의 추이는 Enqueue 지표의 추이가 일치한다. 따라서, Active Session이 90개 전후로 유지된 것은 Enqueue 대기가 발생한 것임을 알 수 있다. Active Session 리스트를 통해서도 세션들이 TX Enqueue를 Share 모드로 대기하고 있음을 알 수 있다. INSERT INTO B (B_id, ...) 형태의 SQL문을 수행하고 있다.
자세히 Lock 대기상황을 살펴보면, Object Id가 같으므로 대기하는 세션 모두 동일 테이블에 작업 중이고, Share 모드로 대기중임을 알 수 있다.
세션들이 수행하고 있는 SQL문은 INSERT INTO B (B_id, ...) 형태로, B 테이블의 B_id는 Primary Key이며, 즉 Unique하다.
예를 들어, Emp 테이블의 Empno 칼럼에 Unique 인덱스가 생성되어 있다.
SID=21 insert into Emp (Empno) Values (:b1); SID=24 insert into Emp (Empno) Values (:b1);
동일한 값을 입력했을 경우, 24번 세션은 21번 세션이 commit/rollback 할 때까지 TX Enqueue를 Share 모드로 대기한다. Commit이 이루어지면, ORA-00001"unique constraint (%s.%s) violated" 발생한다. Rollback이 이루어지면, 24번의 insert는 성공적으로 이루어진다.
이와 같이 Unique한 컬럼에 동일한 값으로 INSERT를 수행할 경우, 먼저 수행한 세션이 트랜잭션을 종료할 때까지 TX Enqueue 대기가 발생한다. 이 경우, Sequence를 사용해서 Unique 컬럼에 INSERT를 수행하도록 변경해 주면 해결이 된다.
[편집] 해결방안
시퀀스를 사용하도록 INSERT 문을 다음과 같이 변경한다.
1. SEQUENCE를 사용하고자 하는 다음의 테이블이 있다.
CREATE TABLE MYTABLE (ID NUMBER, NAME VARCHAR2(20));
2. CREATE SEQUENCE 라는 문장을 사용하여 SEQ_ID라는 이름의 시퀀스를 만든다.
CREATE SEQUENCE SEQ_ID INCREMENT BY 1 START WITH 10000 cache 1000;
-- INCREMENT BY 1 : 증가 값은 1
-- START WITH 10000 : 10000부터 증가
3. 테이블에 데이터 입력시에는 NEXTVAL이라는 Pseudo-column을 이용하여 시퀀스를 사용한다.
INSERT INTO MYTABLE VALUES( SEQ_ID.NEXTVAL, '홍길동');
-- NEXTVAL : 현재 시퀀스 값의 다음 값을 반환 합니다.
[편집] 3. 동일 Row 변경에 의한 TX Enqueue
