Lock 경합 진단: Waiter·Blocker·TX/TM Event·ITL·Deadlock

핵심 요약

Lock 경합 진단의 핵심은 Event 이름을 암기하는 것이 아니라 다음 네 가지를 하나의 증거 사슬로 연결하는 것입니다.

Waiter
→ 현재 실제로 대기 중인가?
→ 어떤 Resource를 어떤 Mode로 요청하는가?

Immediate Blocker
→ 같은 Resource를 어떤 Mode로 보유하는가?

Final Blocker
→ Wait Chain 끝에서 진행을 막는 Session은 누구인가?

Root Cause
→ 같은 Row, 미확정 Unique Key, Bitmap Fragment, ITL, Index Block Split,
   TM Lock Mode, Unindexed Foreign Key, DDL, 긴 Transaction 중 무엇인가?

enq: TX - row lock contention은 같은 Row 변경뿐 아니라 미확정 Unique Key와 Bitmap Index Fragment 경합 등에서도 나타날 수 있습니다. enq: TM - contention도 “Table 전체가 Row Lock으로 잠겼다”는 뜻이 아니라 Object에 대한 비호환 TM Lock 요청이 대기 중임을 뜻합니다.

Deadlock은 오래 기다리는 Blocking과 다릅니다. Blocking은 선행 Transaction이 정상적으로 COMMIT 또는 ROLLBACK하면 진행할 수 있지만, Deadlock은 서로의 Lock을 순환해서 기다리는 Cycle입니다. Oracle은 Cycle을 감지하면 관련 Statement 하나를 Statement-level Rollback하여 충돌 Lock 일부를 해제하고 ORA-00060을 반환합니다. 같은 Transaction에서 그보다 앞서 완료한 DML과 Lock은 남을 수 있으므로 Application은 업무 단위의 Rollback 정책을 명시해야 합니다.

학습 목표

• Blocking·Wait Chain·Deadlock의 구조적 차이를 설명한다.
• Waiter·Immediate Blocker·Final Blocker를 올바르게 구분한다.
• GV$SESSION, GV$TRANSACTION, GV$LOCK, V$LOCKED_OBJECT의 역할을 구분한다.
• TYPE·ID1·ID2, LMODE, REQUEST로 Lock Resource와 Mode를 연결한다.
• TX 경합을 같은 Row·Unique Key·Bitmap·ITL·Index 구조 변경으로 분류한다.
• TM 경합을 Object·Mode·DDL·Unindexed Foreign Key 관점에서 분류한다.
• ROW_WAIT_*의 유효 조건과 한계를 이해한다.
• Deadlock Trace와 Transaction의 전체 SQL 순서를 함께 해석한다.
• Session 종료와 근본 원인 개선을 분리한다.

1. Blocking·Wait Chain·Deadlock

1.1 Blocking

Session A가 Resource 보유
→ Session B가 비호환 Mode 요청
→ B 대기
→ A가 COMMIT·ROLLBACK
→ B 진행 가능

대기 시간이 길어도 구조가 단방향이면 Deadlock이 아닙니다. 핵심은 “얼마나 오래 기다렸는가”가 아니라 “대기 관계가 Cycle인가”입니다.

1.2 Wait Chain

Session C가 B를 차단
→ Session B가 A를 차단
→ Session A 대기

A의 Immediate Blocker는 B이고, Chain 끝의 Final Blocker는 C입니다. 중간 Blocker만 종료하거나 재시도하면 근본 Blocker가 남을 수 있습니다.

1.3 Deadlock

Session A: Row 100 보유 → Row 200 요청
Session B: Row 200 보유 → Row 100 요청

A → B → A

Cycle 안의 Session들은 상대방이 먼저 진행하기를 기다리므로 시간 경과만으로 풀리지 않습니다. Oracle은 Deadlock을 감지한 Transaction의 관련 Statement 하나를 Rollback하고 ORA-00060을 반환합니다. 이전 Statement에서 획득한 Lock과 Transaction 자체는 유지될 수 있습니다.

시험 포인트
“ORA-00060이 발생하면 Transaction 전체가 자동 Rollback된다”는 설명은 틀립니다. 오류를 받은 Application이 부분 처리 상태를 확인하고 업무 단위 전체를 Rollback하거나 명확한 보상 절차를 수행해야 합니다.

2. 진단 원칙: 상태 → Session → Transaction → Resource → Object

진단은 다음 순서를 지키면 Event 이름에 끌려가는 오류를 줄일 수 있습니다.

1. 실제 WAITING 상태인가?
2. Waiter와 Blocker의 SID·SERIAL#·INST_ID는 무엇인가?
3. Blocker Transaction은 언제 시작됐는가?
4. 같은 TYPE·ID1·ID2 Resource에서 무엇을 보유·요청하는가?
5. 관련 Object·Block·Row 또는 Index·Constraint는 무엇인가?
6. Application의 Transaction 경계와 접근 순서는 어떠한가?

V$SESSION.EVENT는 Session이 현재 대기 중이면 현재 Event를, 대기 중이 아니면 마지막 Event를 보여 줄 수 있습니다. 따라서 Event 값만 보지 말고 STATE, WAIT_CLASS, BLOCKING_SESSION_STATUS를 함께 봅니다.

Oracle 버전과 Priority Transactions 설정에 따라 Row Lock 관련 Event 이름이 세분될 수 있습니다. 운영 환경에서는 V$EVENT_NAME과 P1TEXT·P2TEXT·P3TEXT를 확인하고, 최종 판단은 Lock Resource와 Transaction 증거로 수행합니다.

3. 현재 Waiter·Immediate Blocker·Final Blocker 찾기

단일 Instance에서는 V$SESSION, RAC에서는 GV$SESSION을 사용합니다.

SELECT inst_id,
       sid,
       serial#,
       username,
       status,
       event,
       state,
       wait_class,
       wait_time_micro,
       blocking_session_status,
       blocking_instance,
       blocking_session,
       final_blocking_session_status,
       final_blocking_instance,
       final_blocking_session,
       sql_id,
       prev_sql_id,
       module,
       action,
       client_identifier,
       row_wait_obj#,
       row_wait_file#,
       row_wait_block#,
       row_wait_row#
FROM   gv$session
WHERE  blocking_session_status = 'VALID'
    OR final_blocking_session_status = 'VALID'
    OR (state = 'WAITING' AND wait_class = 'Application')
ORDER BY final_blocking_instance,
         final_blocking_session,
         inst_id,
         sid;

BLOCKING_SESSION과 FINAL_BLOCKING_SESSION 값은 각각의 Status가 VALID일 때 해석합니다. Cyclical Wait Chain에서는 Oracle이 Cycle 안의 한 Session을 Final Blocker로 선택할 수 있으므로 Final Blocker 값만으로 Deadlock 여부를 단정하지 않고 Deadlock Trace 또는 V$WAIT_CHAINS.CHAIN_IS_CYCLE 같은 추가 증거를 확인합니다.

Idle Blocker

Blocker가 DML 후 사용자 입력, 외부 API, Message Queue, Application 연산을 기다리면 다음 상태가 가능합니다.

STATUS = 'INACTIVE'
SQL_ID = NULL 또는 현재 원인 DML과 무관
Transaction은 계속 열려 있음
Lock은 COMMIT·ROLLBACK까지 유지

이때 PREV_SQL_ID, TADDR, Transaction 시작 시각, Module·Action·Client Identifier, Connection Pool 반환 여부를 함께 확인합니다.

4. Active Transaction과 Undo 사용량 확인

SELECT s.inst_id,
       s.sid,
       s.serial#,
       s.username,
       s.status,
       s.sql_id,
       s.prev_sql_id,
       s.module,
       s.action,
       t.start_date,
       t.xidusn,
       t.xidslot,
       t.xidsqn,
       t.used_ublk,
       t.used_urec
FROM   gv$session s
JOIN   gv$transaction t
  ON   t.inst_id = s.inst_id
 AND   t.addr    = s.taddr
ORDER BY t.start_date;

확인 질문은 다음과 같습니다.

• Transaction이 언제 시작됐는가?
• Session은 Idle인데 Transaction만 열린 채 남아 있는가?
• USED_UBLK, USED_UREC가 커서 종료 후 Rollback 시간이 길 수 있는가?
• 현재 SQL보다 PREV_SQL_ID가 실제 Lock 획득 DML인가?
• Connection Pool이 미완료 Transaction을 재사용하거나 반환했는가?

Lock 보유시간은 개별 SQL의 실행시간이 아니라 Transaction 시작부터 COMMIT 또는 ROLLBACK까지의 경계에 크게 좌우됩니다.

5. GV$LOCK으로 같은 Resource의 Holder·Waiter 연결

Lock Resource는 기본적으로 다음 조합으로 식별합니다.

TYPE + ID1 + ID2

• Holder: LMODE > 0
• Waiter: REQUEST > 0
• CTIME: 현재 Mode가 부여된 뒤 경과한 초
• BLOCK: 다른 Lock 요청을 차단하는지 나타내는 표시

SELECT w.inst_id AS waiter_inst_id,
       w.sid     AS waiter_sid,
       h.inst_id AS holder_inst_id,
       h.sid     AS holder_sid,
       w.type,
       w.id1,
       w.id2,
       h.lmode   AS mode_held,
       w.request AS mode_requested,
       h.ctime   AS holder_mode_seconds,
       h.block   AS holder_block_flag
FROM   gv$lock w
JOIN   gv$lock h
  ON   h.type = w.type
 AND   h.id1  = w.id1
 AND   h.id2  = w.id2
WHERE  w.request > 0
  AND  h.lmode  > 0
  AND (h.inst_id <> w.inst_id OR h.sid <> w.sid)
ORDER BY w.type,
         w.id1,
         w.id2,
         w.inst_id,
         w.sid,
         h.inst_id,
         h.sid;

이 Query는 같은 Resource의 Blocker 후보를 보여 줍니다. 여러 Holder가 있는 Resource에서는 모든 Holder가 요청 Mode와 비호환인 것은 아닐 수 있으므로 다음을 함께 확인합니다.

1. GV$SESSION.BLOCKING_SESSION_STATUS='VALID'인지 확인
2. Holder의 LMODE와 Waiter의 REQUEST가 실제로 비호환인지 확인
3. RAC에서는 INST_ID와 BLOCKING_INSTANCE를 함께 확인
4. TX·TM의 ID1·ID2 의미를 Lock Type에 맞게 해석

Lock Mode 값은 일반적으로 다음처럼 읽습니다.

BLOCK=1은 Local Instance의 다른 Process를 차단함을 뜻합니다. RAC에서 BLOCK=2는 Local에서 차단된 Process가 보이지 않더라도 Remote Instance를 차단할 가능성이 있음을 뜻합니다.

6. ROW_WAIT_*로 현재 Row 위치 좁히기

ROW_WAIT_OBJ#, ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW#는 Session이 다른 Transaction의 Commit을 기다리고 있고 ROW_WAIT_OBJ# != -1인 경우에 Row 위치를 좁히는 데 사용할 수 있습니다.

SELECT s.sid,
       s.serial#,
       s.event,
       s.row_wait_obj#,
       s.row_wait_file#,
       s.row_wait_block#,
       s.row_wait_row#,
       dbms_rowid.rowid_create(
           rowid_type     => 1,
           object_number  => s.row_wait_obj#,
           relative_fno   => s.row_wait_file#,
           block_number   => s.row_wait_block#,
           row_number     => s.row_wait_row#
       ) AS waiting_rowid
FROM   v$session s
WHERE  s.sid = :waiter_sid
  AND  s.state = 'WAITING'
  AND  s.row_wait_obj# <> -1;

Object 매핑은 DATA_OBJECT_ID를 우선 확인합니다.

SELECT owner,
       object_name,
       subobject_name,
       object_type,
       object_id,
       data_object_id
FROM   dba_objects
WHERE  data_object_id = :row_wait_obj;

따라서 ROW_WAIT_*가 NULL 또는 -1이라고 해서 TX 경합이 아니라는 뜻은 아니며, 값이 있다고 해서 모든 TX 원인이 같은 Table Row 충돌이라는 뜻도 아닙니다.

7. TX 경합 원인 분류

7.1 같은 Row UPDATE·DELETE

Session A가 Row R 변경
→ Transaction 미종료
→ Session B가 같은 Row R 변경 요청
→ B가 A의 Commit·Rollback 대기

일반적으로 ROW_WAIT_*가 유효할 수 있고, Waiter는 선행 Transaction 종료 후 Row를 다시 확인해 DML을 계속하거나 조건 변화에 따라 결과가 달라질 수 있습니다.

7.2 미확정 Unique Key

두 Session이 동일한 PK 또는 Unique Key를 Insert하는 상황입니다.

-- Session A
INSERT INTO customer(customer_id, name)
VALUES (100, 'A');

-- Session B
INSERT INTO customer(customer_id, name)
VALUES (100, 'B');

Session B는 Session A의 결과가 확정되기 전까지 즉시 중복 오류를 결정할 수 없습니다.

Session A COMMIT
→ Key 100 확정
→ Session B는 ORA-00001을 받을 수 있음

Session A ROLLBACK
→ Key 100 제거
→ Session B Insert가 진행될 수 있음

이 경우 “현재 Table에 존재하는 같은 Row를 두 Session이 UPDATE했다”는 설명은 맞지 않습니다. Unique Index, 대기 중인 Key 값, 선행 Transaction 결과를 함께 확인합니다.

7.3 Bitmap Index Fragment 경합

Bitmap Index의 한 Entry는 여러 Rowid 범위를 표현할 수 있습니다. 서로 다른 Table Row를 변경하더라도 같은 Bitmap Fragment를 갱신하면 Transaction 간 경합이 발생할 수 있습니다. OLTP에서 Bitmap Index를 사용할 때는 낮은 Cardinality라는 장점뿐 아니라 동시 DML 경합 위험을 함께 평가합니다.

7.4 Prepared Distributed Transaction

In-Doubt 또는 Prepared 상태의 분산 Transaction이 Lock을 계속 보유할 수 있습니다. 일반 Session DML만 찾지 말고 분산 Transaction 상태와 복구 필요성을 확인합니다.

7.5 ITL Slot 부족

ITL(Interested Transaction List)은 Block Header에서 해당 Block의 Row를 변경하는 Transaction 정보를 관리합니다.

여러 Transaction이 같은 Block의 서로 다른 Row를 동시 변경
→ 사용 가능한 ITL Slot 소진
→ 새 ITL Slot을 만들 Free Space 부족
→ TX Mode 4 요청과 ITL 대기 가능

확인 항목:

• Table·Index Segment의 INITRANS
• PCTFREE와 Block Free Space
• 특정 Block·Partition에 DML이 집중되는 Key 구조
• Application 동시 DML 수
• V$SEGMENT_STATISTICS의 ITL waits
• GV$LOCK에서 TX Lock의 REQUEST=4가 보이는지

SELECT owner,
       object_name,
       subobject_name,
       object_type,
       value
FROM   v$segment_statistics
WHERE  statistic_name = 'ITL waits'
  AND  value > 0
ORDER BY value DESC;

ITL waits는 Instance 시작 이후 누적 통계이므로 현재 발생 중인 경합인지 판단하려면 Session Wait, 시간대별 증거와 함께 봅니다. INITRANS만 증가시키는 것으로 끝내지 말고 기존 Block에 반영되는 범위, Segment 재구성 필요성, Hot Block 분산을 검토합니다.

7.6 Index 구조 변경 경합

enq: TX - index contention은 한 Transaction이 Index Block Split 같은 구조 변경을 수행하는 동안 다른 Transaction이 완료를 기다릴 때 나타날 수 있습니다.

확인 항목:

• 단조 증가 Key가 오른쪽 끝 Leaf Block에 집중되는가?
• Insert 동시성이 특정 Index Partition·Leaf Block에 집중되는가?
• Index Block Split가 반복되는가?
• RAC에서 같은 Index Block의 gc 경합이 동반되는가?
• Reverse Key 또는 Hash Partitioned Index 적용 시 범위 Scan과 운영비용은 어떻게 달라지는가?

단순히 “Index가 있으므로 Row Lock 문제”라고 판단하지 않고, Index Block 구조와 DML 분포를 확인합니다.

8. TM 경합 원인 분류

TM Lock은 Table에 대한 DML Lock입니다. Transaction이 INSERT, UPDATE, DELETE, MERGE, SELECT ... FOR UPDATE, LOCK TABLE 등을 수행할 때 관련 Table에 TM Lock이 획득될 수 있습니다.

enq: TM - contention은 Object 수준에서 요청 Mode와 보유 Mode가 충돌해 대기하는 상황입니다. 대표 원인은 다음과 같습니다.

1. DML과 비호환 DDL
2. LOCK TABLE 또는 강한 Table Lock Mode
3. Unindexed Foreign Key와 부모 Primary/Unique Key 변경
4. MERGE·Direct-Path·Parallel DML 등 작업 특성
5. Partition·Subpartition 관련 Object Lock

8.1 Unindexed Foreign Key

DEPARTMENTS(department_id PK)
        ↓
EMPLOYEES(department_id FK)

자식 Foreign Key Column에 Index가 없고 다음 작업이 발생하면 Oracle은 참조 무결성을 확인하기 위해 Child Table에 Full Table Lock을 획득할 수 있습니다.

• 부모 Row 삭제
• 부모 Primary Key 속성 변경
• 부모 Table로 Row를 MERGE

이 Lock은 일반 Transaction의 모든 DML Lock과 달리 관련 Statement가 완료되면 해제될 수 있지만, 실행 중에는 Child Table의 동시 DML을 막아 TM 경합과 Deadlock 가능성을 높입니다.

반면 부모 Table에 단순 Insert하는 작업은 Child Table의 기존·신규 Row DML을 막는 Blocking Table Lock을 획득하는 경우에 해당하지 않습니다.

Foreign Key Index는 Constraint 선언 자체의 필수 문법은 아니지만, 다음 조건에서는 우선 검토합니다.

• 부모 Key 삭제·변경이 존재함
• 부모·자식 Table에 동시 DML이 많음
• Child Row를 Foreign Key로 자주 조회함
• TM 경합 또는 Deadlock이 반복됨

모든 Foreign Key에 무조건 Index를 추가하지 말고 부모 Key 변경 여부, Child DML 패턴, 기존 복합 Index의 선두 Column 포함 여부, Index 유지비용을 함께 평가합니다.

8.2 DDL·Manual Lock

DML Transaction은 충돌하는 DDL로부터 Object 정의를 보호하기 위해 TM Lock을 사용합니다. 반대로 DDL 또는 LOCK TABLE이 강한 Mode를 요청하면 진행 중인 DML과 경합할 수 있습니다. Object 이름과 LOCKED_MODE, 현재·이전 SQL, DDL 작업 시각을 함께 확인합니다.

9. V$LOCKED_OBJECT의 정확한 용도

SELECT lo.session_id AS sid,
       s.serial#,
       s.username,
       s.status,
       s.sql_id,
       s.prev_sql_id,
       o.owner,
       o.object_name,
       o.subobject_name,
       o.object_type,
       lo.locked_mode
FROM   v$locked_object lo
JOIN   v$session s
  ON   s.sid = lo.session_id
JOIN   dba_objects o
  ON   o.object_id = lo.object_id
ORDER BY lo.session_id,
         o.owner,
         o.object_name;

V$LOCKED_OBJECT는 Transaction이 보유한 TM-type DML Lock의 Object와 Mode를 보여 줍니다.

다음 용도로 사용하지 않습니다.

• 잠긴 모든 Row 목록 조회
• Waiter의 정확한 Row 위치 확인
• TX Lock의 ID1·ID2 해석 대체
• RAC 전체 관계를 Instance 정보 없이 단정

RAC에서는 각 Instance의 정보와 GV$SESSION, GV$LOCK을 함께 사용해 Session을 연결합니다.

10. Deadlock 진단

10.1 재현 구조

-- Session A
UPDATE accounts
SET    balance = balance - 100
WHERE  account_id = 1;

-- Session B
UPDATE accounts
SET    balance = balance - 100
WHERE  account_id = 2;

-- Session A
UPDATE accounts
SET    balance = balance + 100
WHERE  account_id = 2;

-- Session B
UPDATE accounts
SET    balance = balance + 100
WHERE  account_id = 1;

A는 Row 1 보유, Row 2 요청
B는 Row 2 보유, Row 1 요청
A → B → A

Oracle은 ORA-00060을 반환하고 진단 정보를 Trace에 기록합니다.

SELECT name,
       value
FROM   v$diag_info
WHERE  name IN ('Diag Trace', 'Default Trace File');

Deadlock Trace의 해석 순서:

1. Deadlock Graph에서 Resource와 Cycle 확인
2. Holder·Waiter와 Lock Mode 확인
3. 각 Session의 Current SQL 확인
4. Rowid·Object 정보가 제공되면 실제 Object와 연결
5. Transaction이 앞서 수행한 SQL 순서 확인
6. Trigger·Cascade·Foreign Key·MERGE 같은 숨은 DML 경로 확인
7. Application이 오류 후 부분 Transaction을 Commit할 위험 확인

Trace의 Current SQL만 보면 “왜 반대 순서가 만들어졌는지”를 놓칠 수 있습니다. Deadlock은 각 Transaction의 전체 접근 순서 문제이므로 이전 SQL과 업무 흐름을 함께 복원해야 합니다.

10.2 Deadlock 예방

모든 Transaction이 Table·Row를 같은 순서로 접근
→ Cycle 가능성 감소

Transaction을 짧게 유지
→ Lock 보유시간 감소

사용자 입력·외부 API 호출을 Transaction 밖으로 이동
→ Idle Blocker 감소

Foreign Key·Trigger·Cascade·MERGE 경로 점검
→ 숨은 Lock 순서 제거

Deadlock 오류 시 업무 단위 Rollback 후 제한적 재시도
→ 부분 Commit과 무한 재시도 방지

11. 긴급 해소와 근본 개선

Session 종료 전 최소 확인 항목:

• INST_ID, SID, SERIAL#
• Waiter와 Immediate·Final Blocker 관계
• Transaction 시작 시각
• SQL_ID, PREV_SQL_ID, Module·Action
• Undo 사용량과 예상 Rollback 부담
• 업무 중요도와 부분 처리 상태
• Application 재접속·자동 재시도 영향

Session Kill은 증상 해소 수단이며, Transaction 경계·접근 순서·Schema 구조가 그대로면 재발합니다.

12. 혼동하기 쉬운 판단

13. 실전 진단 절차

1. 문제 발생 시각, 업무 요청, 영향 Session 범위를 기록합니다.
2. GV$SESSION에서 STATE, Event, Waiter를 확인합니다.
3. BLOCKING_SESSION_STATUS와 FINAL_BLOCKING_SESSION_STATUS가 VALID인지 확인합니다.
4. Waiter·Immediate Blocker·Final Blocker의 INST_ID·SID·SERIAL#를 연결합니다.
5. Blocker의 SQL_ID, PREV_SQL_ID, Module·Action·Client Identifier를 확인합니다.
6. GV$TRANSACTION에서 시작 시각과 Undo 사용량을 확인합니다.
7. GV$LOCK에서 같은 TYPE·ID1·ID2의 Holder·Request Mode를 연결합니다.
8. TX라면 같은 Row·Unique Key·Bitmap·ITL·Index·분산 Transaction을 분류합니다.
9. TM이라면 Object·Lock Mode·DDL·Unindexed Foreign Key·MERGE 경로를 확인합니다.
10. ROW_WAIT_*가 유효하면 DATA_OBJECT_ID, File·Block·Row를 연결하되 적용 한계를 확인합니다.
11. Deadlock이면 Trace Graph와 각 Transaction의 전체 SQL 접근 순서를 복원합니다.
12. 긴급 해소 후 Transaction 경계·접근 순서·Index·Block 공간을 수정하고 동일 동시성으로 재검증합니다.