테마
12화 · 파고들기
필요할 때 찾아보는 문서다. 처음 읽을 때는
1_사건.md만으로 충분하다. 이 문서의 숫자는 모두 실측값이다. 한빛유통은ora-hanorder(11화 종료 상태)의 복사본에서, 신규 이커머스 플랫폼은ora-newshop의ORA_SQL_TEST복사본에서 쟀다. 실습 스크립트 번호도 함께 적는다. 여러 세션이 동시에 움직이는 실험은 실행할 때마다 시간이 흔들린다. 그래서 판정은 누가 누구를 막는가, LMODE / REQUEST, 받은 번호로 하고, 시간은 범위로 적었다.
| 찾는 것 | 바로 가기 |
|---|---|
| 래치 · 뮤텍스 · 버퍼 Lock · Enqueue: 줄을 세우는 것은 무엇인가 | §1 줄의 종류 |
TX 락: USN · SLOT · WRAP · V$LOCK의 ID1 / ID2 풀기 | §2 TX |
| TM 6모드 호환성 매트릭스 · DML이 거는 모드 (실측) | §3 TM |
V$LOCK 읽는 법 · 이 화에서 잰 REQUEST 지도 | §4 V$LOCK |
| 블로킹 트리 쿼리 · 장애 대응 첫 5분 | §5 블로킹 트리 |
| N1: 겹침 · 빈 번호 · 기다림 세 칸 | §6 채번 |
| 데드락: ORA-00060 · 트레이스 찾기 · 꺼내기 · 읽기 | §7 데드락 |
| 무인덱스 FK: 이 버전에서 잰 것과 공식문서의 설명 | §8 FK |
| DDL의 암시적 COMMIT · DDL이 줄 맨 앞에 서면 | §9 DDL |
| ITL 슬롯 부족 · 인덱스 맨 오른쪽 블록 | §10 블록 안의 줄 |
| 오라클에는 락 에스컬레이션이 없다 | §11 에스컬레이션 |
| Free에서 못 잰 것 | §12 한계 |
| 측정값 전부 | §13 측정 |
| 원천 좌표 | §14 좌표 |
1. 줄의 종류 — Latch · Mutex · Buffer Lock · Enqueue
여러 세션이 같은 것을 동시에 건드리지 못하게 하는 장치는 넷이다. 무엇을 지키는가, 얼마나 쥐는가, 못 잡으면 어떻게 하는가로 갈린다.
| 지키는 것 | 쥐는 시간 | 못 잡으면 | 줄(순서)이 있나 | 대표 대기 이벤트 | 이 교재 | |
|---|---|---|---|---|---|---|
| 래치 (latch) | SGA의 공유 구조 (해시 체인 · 리스트) | 아주 짧다 (구조를 따라가거나 고치는 순간) | 그 자리에서 다시 시도(spin) → 그래도 안 되면 잠깐 잔다 | 없다 | latch: cache buffers chains · latch: shared pool | 07화 탕비실 집게 |
| 뮤텍스 (mutex) | 라이브러리 캐시의 객체 하나 (커서 · 핸들) | 아주 짧다 | spin → 잔다 | 없다 | library cache: mutex X · cursor: pin S | 11화 |
| 버퍼 Lock (버퍼 핀) | 버퍼 캐시의 블록 본문 | 블록을 읽거나 고치는 동안 | 기다린다 | 없다 | buffer busy waits | 07화 · 이 화 §10 |
| Enqueue (락) | 데이터와 객체: 행 · 테이블 · 시퀀스 캐시 · … | 트랜잭션 끝까지 갈 수 있다 | 줄을 선다(요청한 순서대로) | 있다 (FIFO) | enq: TX - row lock contention · enq: TM - contention | 이 화 |
- enqueue는 이름 그대로 큐다. 자기 모드로 줄에 이름을 올리고, 앞사람과 호환되지 않으면 기다린다. 앞에 선 사람을 건너뛰지 못한다. 앞사람과는 부딪치지 않는 모드라도 그렇다(§9 실측).
- enqueue 종류는 두 글자 이름으로 부른다. 이 화의 주인공은 TX(트랜잭션)와 TM(DML 테이블 락)이다. 그 밖에 시퀀스 캐시를 채울 때의 SQ, 사용자 락 UL 등이 있다. 인스턴스 전체의 enqueue 요청과 대기 누계는
V$ENQUEUE_STATISTICS에 있다.
실측 — 장부 (실습/03 · 04 끝의 증가분, 인스턴스 전체)
| 실습 | eq_type · req_reason | 대기 횟수 |
|---|---|---|
03 N1 다섯 판 | TX · row lock contention | 7 |
04 블록 안의 줄 | TX · allocate ITL entry | 2 |
04 블록 안의 줄 | TX · index contention | 291~306 |
💡 줄을 서는가를 보면 셋이 갈린다. 07화의 래치는 줄이 없어 spin했다. 오늘의 enqueue는 이름을 적고 순서대로 기다렸다. 래치 대기가 많으면 구조를 덜 건드리게 고치고(논리적 읽기를 줄인다. 07화 참고), enqueue 대기가 많으면 누가 무엇을 얼마나 오래 쥐는가를 고친다.
2. TX 락 — 트랜잭션의 이름표
트랜잭션이 처음으로 데이터를 바꾸는 순간, 언두 세그먼트 하나의 머리에 있는 트랜잭션 테이블의 칸(slot) 하나를 받는다. 그 칸의 주소가 트랜잭션의 이름이다.
| 이름의 조각 | 뜻 | V$TRANSACTION | V$LOCK (type = 'TX') |
|---|---|---|---|
| USN | 언두 세그먼트 번호 | XIDUSN | TRUNC(ID1 / 65536) |
| SLOT | 그 세그먼트 헤더의 트랜잭션 테이블 칸 번호 | XIDSLOT | MOD(ID1, 65536) |
| WRAP | 그 칸을 몇 번째로 쓰는가 (칸은 재사용된다) | XIDSQN | ID2 |
트랜잭션은 시작할 때 자기 TX를 모드 6(X)으로 쥐고, COMMIT하거나 ROLLBACK할 때 놓는다. 그 트랜잭션이 잠근 행을 원할 때, 같은 키를 넣으려 할 때, 블록의 칸이 모자랄 때처럼 누군가 그 트랜잭션이 끝나기를 기다려야 하면 그 TX를 요청하고 줄을 선다.
줄은 행이나 테이블이 아니라 앞사람의 트랜잭션에 선다. 그래서 기다리는 시간은 앞사람이 무엇을 잠갔는지가 아니라 언제 끝내는지로 정해진다.
실측 — 풀어서 맞춰 본다 (실습/01 [C])
ID1 ID2 USN SLOT WRAP XIDUSN XIDSLOT XIDSQN M TRACE_NAME
458778 752 7 26 752 7 26 752 Y TX-0007001A-000002F0458778 = 7 × 65536 + 26이다. 데드락 트레이스에는 같은 이름이 16진수로 TX-<ID1 8자리>-<ID2 8자리> 모양으로 찍힌다(§7).
- 같은 세션이라도 COMMIT하고 다시 시작하면 이름이 바뀐다(
실습/01[C2]:usn 6 / slot 27 / wrap 838→usn 1 / slot 14 / wrap 761). - 칸 안에 무엇이 적히는지(상태 · 언두 주소 · 커밋 SCN), 행의 lock byte와 블록의 ITL이 이 이름을 어떻게 가리키는지는 13화에서 다룬다.
3. TM 락 — 6모드와 호환성 매트릭스
TM은 테이블(객체) 단위의 enqueue다. V$LOCK의 ID1이 객체 번호(OBJECT_ID)다. 목적은 하나, 누가 이 테이블의 행을 고치는 동안 테이블째 바꾸는 일(DDL)이 끼어들지 못하게 하는 것이다.
| 모드 | 약어 | 이름 | LOCK TABLE ... IN ___ MODE |
|---|---|---|---|
| 0 | — | none | |
| 1 | N | null | |
| 2 | RS (SS) | row share | ROW SHARE |
| 3 | RX (SX) | row exclusive | ROW EXCLUSIVE |
| 4 | S | share | SHARE |
| 5 | SRX (SSX) | share row exclusive | SHARE ROW EXCLUSIVE |
| 6 | X | exclusive | EXCLUSIVE |
실측 — 호환성 매트릭스 (실습/01 [D])
행은 내 트랜잭션이 먼저 쥔 모드다(lmode는 V$LOCK에서 읽은 값). 열은 다른 트랜잭션(자율 트랜잭션)이 NOWAIT로 요청한 모드다. O = 잡았다 · X = ORA-00054 (기다려야 한다)
held \ asked RS RX S SRX X
RS ROW SHARE lmode=2 : O O O O X
RX ROW EXCLUSIVE lmode=3 : O O X X X
S SHARE lmode=4 : O X O X X
SRX SHARE ROW EXCL lmode=5 : O X X X X
X EXCLUSIVE lmode=6 : X X X X X외우는 법: RX(3)끼리는 서로 막지 않는다. 그래서 여럿이 같은 테이블의 다른 행을 동시에 고칠 수 있다. S(4)는 RX(3)와 부딪친다. 읽기 전용으로 테이블을 붙잡으면 고치는 사람이 막힌다. X(6)는 모두와 부딪친다.
실측 — DML이 거는 락 (실습/01 [A])
| 문장 | TM | TX |
|---|---|---|
INSERT · UPDATE · DELETE · SELECT ... FOR UPDATE | 3 (그 테이블) | 6 (자기 트랜잭션) |
SELECT | 없음 | 없음 |
읽기는 아무것도 잠그지 않는다. 그래서
MAX+1은 아무도 막지 않는다. 막지 않으니까 겹친다(§6).⚠️ 성능고도화 I권(p.142)은
SELECT ... FOR UPDATE가 RS(2)로 테이블 락을 잡는다고 적는다. 이 컨테이너에서 잰 값은 3이다(실습/01[A4]). 결론(DML과 서로 막지 않는다)은 같다. RS와 RX는 어느 조합이든 호환되기 때문이다. 모드 숫자는 버전마다 재서 적는다.
4. V$LOCK 읽는 법 — 그리고 실측 REQUEST 지도
| 열 | 뜻 |
|---|---|
TYPE | TX · TM · … |
ID1 · ID2 | TX: USN·SLOT / WRAP (§2) · TM: 객체 번호 / 0 |
LMODE | 지금 쥔 모드 (0이면 아직 못 쥐었다) |
REQUEST | 지금 원하는 모드 (0이 아니면 줄을 서 있다) |
BLOCK | 1 = 이 락 때문에 누군가 기다리고 있다 |
CTIME | 지금 모드로 쥐었거나 기다린 지 몇 초 |
ID1 · ID2가 같은 줄끼리 짝지어 읽는다. LMODE 6 · BLOCK 1인 줄이 막은 쪽, 같은 이름에 REQUEST > 0인 줄이 막힌 쪽이다.
이 화에서 잰 REQUEST — "교과서 값을 단정하지 않고" 잰 결과
| 상황 | 막힌 쪽이 요청한 것 | 대기 이벤트 | 실습 |
|---|---|---|---|
남이 잠근 행을 원한다 (SELECT ... FOR UPDATE · UPDATE) | TX 6 | enq: TX - row lock contention | 03 [R3] · [R5] |
| 남이 COMMIT 전에 넣은 같은 키를 넣으려 한다 (PK) | TX 4 | enq: TX - row lock contention | 03 [R2] |
| 블록의 ITL 칸이 모자란다 | TX 4 | enq: TX - allocate ITL entry | 04 [I1] |
| 무인덱스 FK의 부모를 지우는데 자식 쪽에 진행 중 트랜잭션이 있다 | TX 4 (자식 TM 요청은 없었다) | enq: TX - row lock contention | 11 [K1] · [K1b] |
| 지워지는 중인 부모를 가리키는 자식을 넣는다 | TX 4 | enq: TX - row lock contention | 11 [K2] |
TRUNCATE (ddl_lock_timeout > 0) · LOCK TABLE ... EXCLUSIVE | TM 6 | enq: TM - contention | 05 [D2] · [D3] |
CREATE INDEX (ONLINE 아님) | TM 4 | enq: TM - contention | 05 [D4] |
| DDL 뒤에 온 평범한 DML | TM 3 (앞의 DDL 요청 뒤에 줄) | enq: TM - contention | 05 [D2] · [D3] · [D4] |
이벤트 이름이
row lock contention이어도 REQUEST가 4면 행 락이 아니다. 같은 키, ITL, FK처럼 "저 트랜잭션이 끝나야 결과를 알 수 있는" 기다림이 4로 찍혔다.
5. 블로킹 트리 — 장애 대응 첫 5분
쿼리 (운영에서 읽기만 한다)
sql
SELECT LPAD(' ', 2 * (LEVEL - 1)) || s.sid AS sid_tree,
s.username, s.program, s.event, s.seconds_in_wait AS secs, s.blocking_session AS blocker,
s.sql_id, s.prev_sql_id
FROM v$session s
WHERE s.blocking_session IS NOT NULL
OR s.sid IN (SELECT blocking_session FROM v$session WHERE blocking_session IS NOT NULL)
START WITH s.blocking_session IS NULL
CONNECT BY PRIOR s.sid = s.blocking_session
ORDER SIBLINGS BY s.sid;ora-hanorder에서 핫픽스 장면을 재현하고 돌린 결과다. 뿌리 하나(막은 쪽) 밑에 막힌 둘이 들여 써진다.
SID_TREE USERNA EVENT SECS BLOCKER SQL_ID PREV_SQL_ID
53 SQLP PL/SQL lock timer 0 75p70p63f6rcr 2sz3fk2bg66gk
21 SQLP enq: TX - row lock contention 2 53 c65u3ajz0tz8z 2sz3fk2bg66gk
216 SQLP enq: TX - row lock contention 2 53 c65u3ajz0tz8z 2sz3fk2bg66gk- 막힌 쪽의
SQL_ID를 보면 무엇을 하려다 막혔는지 알 수 있다. 뿌리의PREV_SQL_ID로는 무엇을 한 뒤 지금 무엇을 하는 중인지 본다. - 뿌리의 이벤트가
SQL*Net message from client면 트랜잭션을 연 채 쉬고 있는 세션이다(화면을 열어 둔 채 자리를 비운 사용자, 커넥션 풀에 반납되지 않은 트랜잭션). BLOCKING_SESSION은 바로 앞사람 하나만 가리킨다. 그래서 트리로 이어 붙여야 뿌리가 보인다.
첫 5분의 순서 (12화 선배의 세 마디)
| # | 동작 | 왜 |
|---|---|---|
| 1 | 블로킹 트리를 찍는다 · 시각을 적는다 | V$는 장애가 풀리면 사라진다. 찍은 시각이 증거의 시각이다 |
| 2 | 뿌리가 무엇을 하는 세션인지, 보통 얼마나 걸리는지 주인에게 묻는다 | 기다리면 풀리는 줄인지, 끝나지 않는 줄인지 |
| 3 | 죽이기 전에 되감기 비용을 묻는다 | 세션을 죽이면 그 트랜잭션이 한 일이 전부 되감기고, 되감는 동안에도 락은 그대로다 |
| 4 | 결정을 사용자 쪽에 알린다 (예상 시각 포함) | 탐지 → 인지 → 공지 |
| 5 | 데드락 · 에러를 받은 사용자가 있으면 캡처를 지우지 말라고 한다 | 지나간 장애의 거의 유일한 흔적 (§7) |
6. 채번 — 겹침 · 빈 번호 · 기다림
셋을 다 주는 방법은 없다. 무엇을 포기해도 되는지는 번호를 쓰는 업무가 정한다.
실측 — 다섯 가지 방법 (실습/02 혼자 · 실습/03 세 명 동시 · 복사본)
| 방법 | 번호 하나 Buffers | 세 명 동시: 기다림 | 겹침 | 빈 번호 | 그 밖에 |
|---|---|---|---|---|---|
rev00 NVL(MAX(STLM_SEQ),0)+1 | 568 (TABLE ACCESS FULL · 10만 행) | 없다 (0~1cs) | 있다: 100001 × 3 | 없다 | 취소된 맨 끝 번호를 다시 준다 (02 [C]) |
| rev00 + PK | 568 | 있다: TX 4로 기다렸다가 ORA-00001 × 2 (220~221cs) | 없다 | 없다 | 줄 끝에서 에러 |
핫픽스 채번 테이블 FOR UPDATE | 5 (INDEX UNIQUE SCAN) | 있다: TX 6 · 앞사람이 COMMIT할 때까지 (316~321cs) | 없다 | 없다 | 기다림 = 번호를 받은 트랜잭션의 길이 |
| 채번 테이블 + 자율 트랜잭션 | (재지 않음) | 번호 받는 순간뿐. 옆 사람이 NOWAIT로도 받는다 (02 [F]) | 없다 | 있다: 내가 ROLLBACK해도 LAST_NO는 이미 올라가 있다 | 번호마다 채번 테이블 한 행을 고치고 COMMIT |
| rev01 시퀀스 | 0 (캐시를 채우는 20번에 한 번 4) | 없다 (0cs) | 없다 | 있다: ROLLBACK (03 [R4] · 100001) · 캐시 소실 (91 · 18개) | CACHE 20 (기본값) |
왜 MAX+1은 겹치는가
COMMIT 전의 행은 다른 세션에 보이지 않는다(읽기 일관성). 배치가 100001을 넣고 아직 COMMIT하지 않았으면, 화면이 읽는 MAX는 여전히 100000이다. 그리고 SELECT는 아무것도 잠그지 않는다. 기다리게 할 장치가 없으니 겹친다. 번호를 받는 곳이 하나뿐이면 이 문제는 없다(강도현이 그때 세운 근거).
왜 핫픽스는 줄을 세우는가
SELECT ... FOR UPDATE는 그 행을 잠그고, 행 락은 트랜잭션이 끝날 때 풀린다. 번호를 받는 함수가 COMMIT하지 않으니 부른 쪽의 트랜잭션이 끝날 때까지 채번 테이블의 한 행이 잠긴다. 그 트랜잭션이 10분짜리 배치면, 번호를 원하는 모든 세션이 10분을 선다.
채번은 업무 트랜잭션에서 떼어 낸다. 시퀀스든 자율 트랜잭션이든 번호를 받는 일이 업무가 COMMIT하는 시점에 묶이지 않게 한다.
빈 번호는 막아야 하는가 — 요구를 원문으로 확인한다
| 강도현의 근거 (2019.03 커밋) | "시퀀스는 쓰지 않음 — 결번이 생긴다 (2018 감사 지적)" |
| 감사 지적 원문 | "정산번호 결번 발생 시 결번 사유를 확인할 수 있는 기록이 없음" |
| 정산팀 · 회계팀 확인 (12화) | 정산번호는 순서에 의미가 없는 식별 번호다. 결번 사유("취소 · 장애로 쓰이지 않은 번호")를 업무 규정에 적으면 된다. 겹침은 안 된다 |
요구는 결번 금지가 아니라 결번의 사유를 댈 수 있을 것이었다. 그 요구 하나를 위해 모두를 줄 세우는
FOR UPDATE는 정당하지 않다. 진짜로 결번이 금지된 번호(법정 연번이 요구되는 서류 등)라면 결론이 바뀐다. 그때는 줄을 짧게 하는 쪽으로 설계한다(번호를 트랜잭션 맨 끝에서 받거나, 번호를 받는 트랜잭션을 짧게 한다).
시퀀스의 빈 번호가 생기는 두 경우 (실측)
| 경우 | 실측 |
|---|---|
| 번호를 받은 트랜잭션이 ROLLBACK | 100001을 받은 배치가 ROLLBACK → 남은 번호 100002 · 100003 (03 [R4]) · 100003 ROLLBACK → 다음 100004 (02 [D]) |
| 캐시가 사라진다 (공유 풀 비우기 · 인스턴스 재시작) | CACHE 20에서 1 · 2를 쓰고 ALTER SYSTEM FLUSH SHARED_POOL → 다음 번호 21 (3~20의 18개 소실) · LAST_NUMBER 21 (91, SYSDBA) |
NOCACHE로 하면 두 번째 경우는 줄지만 번호마다 딕셔너리(SEQ$)를 고친다. 그 비용은 이 화에서 재지 않았다.
7. 데드락 — ORA-00060과 트레이스
무엇이 일어나나 (실측 · 실습/03 [R5])
SCR1 EXEC 0 UPDATE ... WHERE STLM_SEQ = 50000 ← 정산 50000 번을 고친다 (COMMIT 전)
BATCH TAKE 0 N1_HOTFIX_C12 ← 배치가 번호를 받는다 (채번 테이블 행 락)
SCR1 이 번호를 받으려 한다 → BATCH 를 기다린다
BATCH 가 50000 번에 닿는다 → SCR1 을 기다린다 (원이 닫힌다)
SCR1 ERR 328 ORA-00060: deadlock detected while waiting for resource| 사실 | 실측 |
|---|---|
| 누가 에러를 받나 | 먼저 기다리기 시작한 쪽(SCR1). 두 번 실행 모두 같았다 |
| 언제 | 기다리기 시작한 지 약 3초 (324~328cs) |
| 무엇이 되돌려지나 | 그 문장 하나(SELECT ... FOR UPDATE). 트랜잭션은 살아 있다 |
| 그래서 | SCR1이 앞에 고친 50000번 행의 락은 그대로다. BATCH는 여전히 SCR1을 기다린다. SCR1이 ROLLBACK해야 BATCH가 간다 |
데드락은 줄을 풀어 주지 않는다. 원 하나를 끊어 줄 뿐이다. 에러를 받은 애플리케이션이 ROLLBACK하지 않으면 남은 쪽은 계속 기다린다.
트레이스 찾기 · 꺼내기 · 읽기
데드락이 나면 오라클은 alert 로그에 한 줄, 그리고 에러를 받은 세션의 트레이스 파일 하나를 남긴다. 트레이스는 DB 서버(컨테이너) 안에 있다.
① 어느 파일인가: SQL로 찾는다 (sqlp 계정으로 된다)
sql
SELECT TO_CHAR(originating_timestamp, 'HH24:MI:SS') AS alert_time,
REGEXP_SUBSTR(message_text, '/opt/oracle/[^ ]+\.trc') AS trace_file
FROM v$diag_alert_ext
WHERE message_text LIKE '%ORA-00060%'
ORDER BY originating_timestamp DESC FETCH FIRST 1 ROWS ONLY;
-- 트레이스 디렉터리 : SELECT value FROM v$diag_info WHERE name = 'Diag Trace';② Deadlock graph만 보고, 파일째 꺼낸다. Git Bash는 컨테이너 안의 /opt/... 경로를 윈도 경로로 바꿔 버리므로 MSYS_NO_PATHCONV=1 환경 변수를 붙인다.
bash
T=/opt/oracle/diag/rdbms/free/FREE/trace/FREE_ora_785.trc # ①에서 나온 이름
MSYS_NO_PATHCONV=1 docker exec ora-hanorder bash -c "sed -n '/Deadlock graph/,/End of information for waiting sessions/p' $T"
MSYS_NO_PATHCONV=1 docker cp ora-hanorder:$T .PowerShell은 경로를 바꾸지 않으므로 MSYS_NO_PATHCONV 없이 같은 명령을 쓴다.
③ 읽는다. 실측 트레이스(1,660,663바이트 · 28,925줄) 중 앞쪽 스무 줄이다. 나머지는 프로세스 상태 덤프다.
Deadlock graph:
------------Blocker(s)----------- ------------Waiter(s)------------
Resource Name process session holds waits serial process session holds waits serial
TX-0002000F-00000350-469D09C8-00000000 87 221 X 46523 84 22 X 9825
TX-0005001F-00000338-469D09C8-00000000 84 22 X 9825 87 221 X 46523
Session 221:
Holds resource TX-0002000F-00000350-469D09C8-00000000 acquired 3 seconds ago.
current SQL: SELECT LAST_NO + 1 FROM T_STLM_NUM_C12 WHERE NUM_TP = 'STLM' FOR UPDATE
Session 22:
Holds resource TX-0005001F-00000338-469D09C8-00000000 acquired 3 seconds ago.
current SQL: UPDATE T_ORD_STLM_C12 SET STLM_AMT = STLM_AMT WHERE STLM_SEQ = 50000| 조각 | 읽는 법 | 이 트레이스 |
|---|---|---|
TX-0002000F | ID1의 16진수. 앞 4자리가 USN, 뒤 4자리가 SLOT | USN 2 · SLOT 15 |
-00000350 | ID2 16진수 = WRAP | 848 |
-469D09C8 | 이 버전에서 붙는 꼬리로, PDB의 CON_UID 값이다 (V$PDBS와 대조해 확인. 설치마다 다르다) | FREEPDB1 = 1,184,696,776 |
-00000000 | (이 화의 실험에서는 늘 0. 뜻은 확인하지 못했다) | |
holds X · waits X | 쥔 모드 · 원하는 모드. X = 6 | 행 락 기다림 = TX 6 (§4와 같다) |
한 줄이 자원 하나다. Blocker 열의 세션이 쥐고, Waiter 열의 세션이 기다린다. 두 줄이 서로를 가리키면 원이다. 같은 시각 V$LOCK에서 SCR1(221)의 TX는 usn 2 / slot 15 / wrap 848, BATCH(22)의 TX는 usn 5 / slot 31 / wrap 824(0x1F = 31 · 0x338 = 824)였다. 트레이스와 같다.
데드락을 줄이는 법
| 방법 | 오늘의 경우 |
|---|---|
| 같은 자원들을 같은 순서로 잡는다 | 배치는 번호 → 정산 행, 화면은 정산 행 → 번호. 순서가 반대였다 |
| 잡는 시간을 짧게 해서 원이 생길 틈을 줄인다 | 배치가 번호 락을 10분 쥐었다 → rev01로 번호 락 자체를 없앴다 |
| 에러를 받으면 ROLLBACK하고 다시 시도하게 짠다 | 화면 프레임워크는 ROLLBACK했다(그래서 배치가 이어 갔다) |
8. 무인덱스 FK — 이 버전에서 잰 것
FK 지도 — 자식의 FK 컬럼이 인덱스의 맨 앞 컬럼인가 (실습/10)
| 시스템 | FK | 자식 FK 컬럼에 인덱스 없음 |
|---|---|---|
한빛유통 주문 시스템 (ora-hanorder) | 0 (계정 전체 1개는 SCOTT 견본 EMP → DEPT) | — |
신규 이커머스 플랫폼 ORA_SQL_TEST (ora-newshop) | 7 | 3: M_CUS.RGN_ID · T_ITM_EVL.CUS_ID · T_ORD.CUS_ID |
나머지 넷은 PK의 맨 앞 컬럼이 FK 컬럼이라 저절로 덮여 있다(C_BAS_CD · M_ITM_PRC_HIS · T_ITM_EVL.ITM_ID · T_ORD_DET).
sql
-- 자식 FK 컬럼이 어떤 인덱스의 맨 앞 컬럼인가 (FK 가 한 컬럼일 때)
SELECT c.table_name AS child, c.constraint_name AS fk, cc.column_name AS fk_col,
(SELECT MIN(ic.index_name) FROM user_ind_columns ic
WHERE ic.table_name = c.table_name AND ic.column_position = 1 AND ic.column_name = cc.column_name) AS covering_index
FROM user_constraints c JOIN user_cons_columns cc ON cc.constraint_name = c.constraint_name AND cc.position = 1
WHERE c.constraint_type = 'R';여러 컬럼짜리 FK라면 인덱스의 앞쪽 컬럼들이 FK 컬럼 전부를(순서는 상관없이) 덮어야 한다.
문서에 적힌 것
오라클 공식문서 cncpt(23ai 판) 「Locks and Unindexed Foreign Keys」의 설명이다(12화 때 확인). 요지만 옮기고, 문서 예시의 employees는 "자식 테이블"로 바꿨다.
The database acquires a full table lock on the child table when no index exists on the foreign key column of the child table, and a session modifies a primary key in the parent table (for example, deletes a row or modifies primary key attributes) …
This lock enables other sessions to query but not update the child table.
(It) is held for the duration of the statement, not the transaction.
모드 이름은 적혀 있지 않지만 "조회는 되고 갱신은 안 되는" 테이블 락이면 S(4)다. 장애 사례집들이 "자식 테이블 전체가 멈춘다"고 쓰는 근거가 이것이다. 자식 쪽에 진행 중인 DML(TM 3)이 있으면 부모 DELETE가 기다리고, 그 뒤로 오는 자식 DML은 그 요청 뒤에 줄을 선다는 것이다(§9의 DDL과 같은 모양).
같은 문서에는 인덱스가 있으면 자식 테이블에 full table lock을 걸지 않는다는 설명과 함께, "외래 키에는 거의 언제나 인덱스를 둔다"는 권고가 있다.
이 컨테이너(Oracle Database 26ai Free 23.26.2)에서 잰 것 (실습/11 · 복사본 M_CUS_C12 → T_ORD_C12)
| 판 | 상황 | 관리자(부모 DELETE)의 락 | 결과 |
|---|---|---|---|
| K1 인덱스 없음 | 주문 화면 둘이 주문을 넣는 중(COMMIT 전) → 관리자가 주문 없는 고객을 지운다 | M_CUS_C12 TM 3 · 주문 화면의 TX를 REQUEST 4 · 자식 T_ORD_C12에는 TM이 없다 | 관리자가 주문 화면을 하나씩 기다렸다. 하나가 COMMIT해도 다른 하나를 계속 기다렸다. 둘 다 끝나자 DELETE 완료(260~265cs). 그 사이 새 주문 INSERT는 막히지 않았다(0cs) |
| K1b 인덱스 없음 | 주문 화면이 다른 고객 주문의 금액만 고치는 중 → 관리자가 지운다 | 같은 모양 (TX REQUEST 4) | 관리자가 그 화면을 기다렸다(116~120cs) |
| K2 인덱스 없음 | 관리자가 먼저 지워 두고(COMMIT 전) 주문이 들어온다 | 지운 뒤 관리자의 락: M_CUS_C12 TM 3 뿐 | 다른 고객의 주문은 통과(0cs). 지워지는 그 고객의 주문은 관리자의 TX를 REQUEST 4로 기다렸다(120~136cs). FK의 정상 동작 |
| K3 인덱스 있음 | K1과 같은 상황 | M_CUS_C12 TM 3 · 자식 T_ORD_C12에도 TM 3 | 아무도 기다리지 않았다 (0~1cs) |
| K4 FK 없음 | 주문이 있는 고객을 지운다 | — | 에러도 대기도 없이 지워진다 · 주인 없는 주문 33건 (FK가 켜져 있으면 ORA-02292) |
혼자 잰 것(실습/10): 주문 없는 고객 한 명을 지울 때의 session logical reads는 인덱스가 없으면 31, 있으면 8이다. 인덱스가 없으면 자식 테이블(25블록)을 통째로 훑어 이 고객의 주문이 정말 없는지 확인한다.
덧붙이면, 자식에 INSERT한 주문 화면은 부모 M_CUS_C12에도 TM 3을 쥐었다(K1 · K2의 V$LOCK). 막지 않는 모드다.
정리(이 버전 기준). 문서에 나온 "자식 테이블 full table lock"은
V$LOCK에 한 번도 보이지 않았다. 요청(REQUEST)으로도, 그 요청 뒤의 줄로도 나타나지 않았다. 무인덱스 FK의 부모 DELETE는 자식 테이블에 테이블 락을 걸며 기다리지 않았다. 대신 자식 테이블을 훑으며 만나는 진행 중 트랜잭션을 TX 모드 4로 하나씩 기다렸다. 자식 쪽 DML은 멈추지 않았다. 멈춘 것은 부모 쪽이다. 부모를 지우는 세션, 그리고 그 세션이 쥔 부모 행을 원하는 세션들이 멈췄다. 고객 수천 명을 정리하는 배치라면, 자식 트랜잭션을 하나씩 기다리는 동안 이미 지운 고객 수천 명의 행 락을 쥐고 있게 된다. 인덱스를 만들면 아무도 기다리지 않았다. 결론(외래 키에는 인덱스)은 문서와 같고, 기다림의 모양만 다르다. 문서의 설명과 이 측정이 왜 어긋나는지, 언제부터 이렇게 동작하는지는 이 컨테이너로 확인할 수 없다. 다른 버전에서는 다시 재야 한다. 그래서 재발방지책에는 "문서에 그렇게 써 있다"를 근거로 쓰지 않는다.
FK를 걸었을 때와 안 걸었을 때
| FK 없음 (한빛유통) | FK + 무인덱스 | FK + 인덱스 | |
|---|---|---|---|
| 고아 | 생긴다 (33건) | 막는다 (ORA-02292) | 막는다 |
| 부모 DELETE가 자식 트랜잭션을 기다리나 | 아니다 | 기다린다 (TX 4, 하나씩) | 아니다 |
| 부모 DELETE의 읽기 | — | 31 | 8 |
| 대가 | 정합성은 사람이 지킨다 | 부모 쪽 대기 | 인덱스 하나의 적재 비용 (→ 14화) |
9. DDL — 암시적 COMMIT과 줄 맨 앞
DDL은 앞의 일을 COMMIT한다 — 실패해도 (실습/01 [E])
INSERT하고 → 아래 문장을 실행하고 → ROLLBACK했을 때 INSERT가 남았나:
| # | 사이에 한 것 | 남았나 |
|---|---|---|
| E1 | TRUNCATE TABLE (다른 테이블) | 남았다 |
| E2 | CREATE TABLE: ORA-00955로 실패 (이름이 이미 있음) | 남았다 |
| E3 | CREATE TABLE: ORA-00902로 실패 (자료형 오타) | 남았다 |
| E4 | PL/SQL 블록 안의 EXECUTE IMMEDIATE 'TRUNCATE ...' | 남았다 |
| E5 | GRANT | 남았다 |
| E6 | ALTER SESSION | 지워졌다 (COMMIT하지 않는다) |
배치 중간에 DDL이 한 줄이라도 있으면, 그 배치는 한 트랜잭션이 아니다. DDL 앞까지의 일은 이미 확정됐다. DDL이 실패해도 마찬가지다.
DDL이 줄 맨 앞에 서면 (실습/05)
등장인물: SCR (INSERT하고 COMMIT 전) · DDL (배치) · SCR2 (뒤에 온 화면)
| 판 | DDL | DDL의 요청 | 결과 |
|---|---|---|---|
| D1 | TRUNCATE (기본 ddl_lock_timeout = 0) | — | 기다리지 않고 바로 ORA-00054 (0cs). DDL은 기본값이 못 잡으면 포기다 |
| D2 | TRUNCATE (ddl_lock_timeout = 30) | TM 6 | DDL은 SCR을 기다리고, SCR2의 INSERT(TM 3)는 DDL 뒤에 줄을 섰다. SCR과는 부딪치지 않는 모드인데도 그랬다. SCR이 끝나자 DDL(242~243cs), 그다음 SCR2(121~124cs) |
| D3 | LOCK TABLE ... IN EXCLUSIVE MODE (기본값이 기다린다) | TM 6 | D2와 같은 줄. SCR이 끝난 뒤에도 SCR2는 계속 기다렸다. 이번에는 DDL이 X를 쥐고 있기 때문이다(트랜잭션 끝까지) |
| D4 | CREATE INDEX (ONLINE 아님) | TM 4 | 같은 줄 |
enqueue는 끼어들기를 허락하지 않는다. 줄 맨 앞에 모두와 부딪치는 요청이 서면, 그 뒤의 아무와도 안 부딪치는 요청까지 전부 선다. 그래서 바쁜 테이블에 DDL을 걸 때는
ddl_lock_timeout을 길게 주는 편이 더 위험할 수 있다. 기다리는 동안 그 테이블의 모든 DML이 뒤에 서기 때문이다.
10. 블록 안의 줄 — ITL과 인덱스 분할
ITL 슬롯 부족 (실습/04 [I1] · [I2]) ← 05화가 넘긴 질문
블록을 고치는 트랜잭션은 블록 머리의 ITL(Interested Transaction List) 칸 하나에 자기 이름표를 적는다. 칸이 모자라고 블록에 칸을 늘릴 빈자리도 없으면, 행이 달라도 기다린다.
| 실측 | |
|---|---|
| 실험 블록 | PCTFREE 0 · INITRANS 1로 꽉 채운 블록 하나 (272행). ITL 칸은 기본 2개 |
| 세 사람이 행 1 · 2 · 3을 고친다 | 셋째만 막혔다. enq: TX - allocate ITL entry, 앞사람(칸을 쥔 둘 중 하나, 실행마다 다르다)의 TX를 REQUEST 4 · 앞사람들이 끝날 때까지 118~121cs |
ALTER TABLE ... MOVE INITRANS 3 뒤 같은 실험 | 아무도 막히지 않았다 (0cs) |
대가: INITRANS를 올리면 블록마다 그만큼 칸이 미리 잡혀 행을 덜 담는다. 칸 하나가 블록에서 무엇을 차지하는지, 칸에 무엇이 적히는지는 13화에서 본다(블록 덤프로 ITL의 Flag를 읽는다).
인덱스 맨 오른쪽 블록 (실습/04 [X1]) ← 07화가 넘긴 buffer busy waits
순번 키(시퀀스)로 넣으면 새 키는 늘 인덱스의 맨 오른쪽 리프 블록으로 간다. 여럿이 동시에 넣으면 그 블록 하나에 몰린다.
| 세 사람 × 2만 건 (한 건마다 COMMIT) · 일꾼 한 명 기준 | 실측 (세 번 실행 · 일꾼 아홉 명의 범위) |
|---|---|
buffer busy waits | 4,164~5,977번 · 6~20cs |
enq: TX - index contention (리프 블록이 나뉘는 동안 기다림) | 80~111번 · 1~15cs |
leaf node splits (그중 90-10 splits) | 45~67 (33~45) |
| 걸린 시간 | 136~158cs |
CPU 2개짜리 Free에 세 명이라 시간으로는 작다. 이 컨테이너로 잴 수 있는 것은 이벤트가 생긴다는 것까지다. 맨 오른쪽 블록을 나누는 방법(역순 키 인덱스 · 해시 파티션 인덱스 · 키 앞에 분산 값)과 그 대가는 14화에서 적재와 함께 본다.
11. 락 에스컬레이션이 없다
어떤 DBMS는 행 락이 많아지면 메모리를 아끼려고 테이블 락으로 올린다(escalation). 오라클은 그러지 않는다.
실측 (실습/01 [B])
| 같은 트랜잭션에서 | V$LOCK의 내 줄 |
|---|---|
| 1행 UPDATE 뒤 | 2 (TM 3 · TX 6) |
| 이어서 100,000행 UPDATE 뒤 | 2 (TM 3 · TX 6) |
오라클의 행 락은 락 관리자의 메모리에 행마다 적히지 않는다. 그 행이 사는 블록 안에 적힌다. 행 머리의 lock byte가 블록의 ITL 칸을 가리키고, ITL 칸에 트랜잭션의 이름표(§2)가 있다. 행이 10만 개여도 락 관리자가 아는 것은 트랜잭션 하나다. 그래서 올릴 이유가 없다.
대가는 다른 곳에서 치른다. 행을 잠근 블록마다 ITL 칸이 필요하고(§10), 그 표시는 나중에 누군가 지워야 한다(블록 클린아웃 → 13화).
12. Free에서 못 잰 것
| 못 잰 것 | 왜 | 대신 한 것 |
|---|---|---|
| 수백 세션이 한 줄에 서는 규모 · 운영의 "세션 수십 개가 매달린 트리"의 숫자 | Free는 CPU 2개 (cpu_count = 2). 일꾼 셋이면 줄의 모양은 같다 | 세 세션으로 모양(누가 누구를 막는지, LMODE / REQUEST)을 쟀다 |
| 인덱스 맨 오른쪽 블록 경합의 실제 크기 | 같은 이유 | 이벤트가 생기는 것까지 (§10) |
NOCACHE 시퀀스 · enq: SQ - contention의 비용 | 재지 않았다 | — |
| 무인덱스 FK 동작이 바뀐 버전 | 이 컨테이너는 한 버전뿐 | 이 버전에서 잰 것을 그대로 적었다 (§8) |
대규모 EE 환경의 동시성 경합은 별권에서 다룬다.
13. 이 화의 측정값 전부
한빛유통 (ora-hanorder · 11화 종료 상태 · 복사본)
| 무엇 | 값 | 출처 |
|---|---|---|
| 주문 시스템 FK | 0 (계정 전체 1: EMP → DEPT) | 00 [전제] |
T_ORD_STLM · MAX(STLM_SEQ) · T_STLM_NUM.LAST_NO | 100,000 · 100,000 · 0 | 00 [전제] |
| rev00 번호 하나 | TABLE ACCESS FULL · Buffers 568 | 02 [A1] |
| 핫픽스 번호 하나 | INDEX UNIQUE SCAN · Buffers 5 | 02 [A2] |
| rev01 번호 하나 | 첫 번째 Buffers 4 · 두 번째 0 | 02 [A3] |
| rev00 겹침 (혼자 · 자율 트랜잭션) | mine 100001 / neighbor 100001 | 02 [B] |
| rev00 다시 씀 | 취소된 100001 → 다음 100001 | 02 [C] |
| rev01 빈 번호 | 100003 ROLLBACK → 다음 100004 | 02 [D] |
| 핫픽스 옆 사람 NOWAIT | ORA-00054 (TX) | 02 [E] |
| 자율 트랜잭션 채번 | 옆 사람 NOWAIT 통과(100002) · 내 ROLLBACK 뒤 LAST_NO 100001 = 빈 번호 | 02 [F] |
| [R1] rev00 세 명 | 셋 다 100001 · 기다림 0~1cs · 정산 결과 100001 × 3 | 03 |
| [R2] rev00 + PK | 화면 둘 TX REQUEST 4 · 220~221cs 뒤 ORA-00001 × 2 · 들어간 것 1건 | 03 |
| [R3] 핫픽스 | 셋 다 TM 3 · 화면 둘 TX REQUEST 6 · 배치 BLOCK 1 · 기다림 316~321cs · 번호 100001 · 100002 · 100003 | 03 |
| [R3] TX 이름표 대조 | V$LOCK usn 5 / slot 17 / wrap 824 = V$TRANSACTION 5 · 17 · 824 (TX-00050011-00000338) | 03 (1회차) |
| [R4] rev01 | 100001 · 100002 · 100003 · 기다림 0 · 배치 ROLLBACK 뒤 100002 · 100003만 | 03 |
| [R5] 데드락 | SCR1 ORA-00060 (324~328cs) · 그 뒤에도 BATCH는 SCR1에게 막힘 | 03 |
| 데드락 트레이스 | 1,660,663바이트 · 28,925줄 · TX-0002000F-00000350 / TX-0005001F-00000338 · holds X waits X · 469D09C8 = CON_UID 1,184,696,776 | 03 1회차 |
| enqueue 장부 | TX row lock contention 7회 | 03 |
| DML 락 | INSERT · UPDATE · DELETE · SELECT FOR UPDATE = TM 3 + TX 6 · SELECT = 없음 | 01 [A] |
| 락 에스컬레이션 | 1행 2줄 → 10만 행 2줄 | 01 [B] |
| TX 풀기 | ID1 458,778 · ID2 752 → 7 / 26 / 752 = V$TRANSACTION · TX-0007001A-000002F0 | 01 [C] |
| TM 매트릭스 | §3 표 | 01 [D] |
| DDL 암시적 COMMIT | E1 ~ E6 = 1 · 2 · 3 · 4 · 5 · 5 | 01 [E] |
| ITL | 272행 블록 · 셋째 allocate ITL entry TX REQUEST 4 · 118~121cs · INITRANS 3 뒤 0 | 04 |
| 인덱스 맨 오른쪽 | 일꾼별 buffer busy waits 4,164~5,977 · index contention 80~111 · 136~158cs | 04 |
| DDL 줄 | D1 ORA-00054 0~1cs · D2 TM 6 / 뒤의 TM 3 · DDL 242~243cs · SCR2 121~124cs · D3 TM 6 · D4 TM 4 | 05 |
| 시퀀스 캐시 소실 | 1 · 2 → FLUSH → 21 (18개) · LAST_NUMBER 21 | 91 (SYSDBA) |
신규 이커머스 플랫폼 (ora-newshop · ORA_SQL_TEST · 복사본)
| 무엇 | 값 | 출처 |
|---|---|---|
| FK · 그중 자식 컬럼 무인덱스 | 7 · 3 | 10 [지도] |
| 부모 한 행 DELETE의 logical reads | 인덱스 없음 31 · 있음 8 | 10 [혼자] |
| 주문 있는 고객 DELETE | ORA-02292 | 10 [혼자] |
| K1 | 관리자 TX REQUEST 4 · 자식 TM 없음 · 260~265cs · 그 사이 새 주문 0cs | 11 |
| K1b | 금액만 고치는 화면에게도 막힘 · 116~120cs | 11 |
| K2 | 다른 고객 주문 0cs · 지워지는 고객 주문 TX REQUEST 4 · 120~136cs | 11 |
| K3 (인덱스) | 대기 없음 0~1cs · 관리자 자식 TM 3 | 11 |
| K4 (FK 없음) | 고아 33건 | 11 |
측정 환경
Oracle AI Database 26ai Free Release 23.26.2.0.0 · cpu_count 2 · db_block_size 8192 · statistics_level TYPICAL · ddl_lock_timeout 0 · job_queue_processes 1 (그래서 동시 세션은 잡이 아니라 docker exec로 띄운다. 07화와 같다).
14. 원천 좌표
전체 지도 →
00_자료준비/06_원서와_공식문서_지도.mdPDF 페이지 = 책 페이지 − 2 ·성능고도화I_목차색인.md(공개 사이트에는 없음)
성능고도화 I권 02장 — 트랜잭션과 Lock (주 원천)
| 주제 | 책 페이지 |
|---|---|
| 트랜잭션 동시성 제어 · 트랜잭션이란 · ACID | 101 · 103 · 105 |
| 비관적 vs. 낙관적 동시성 제어 | 115 |
| 동시성 구현 사례: 일련번호 채번 동시성 높이기 | 122 |
| 오라클 Lock: Enqueue Lock | 126 |
| TX Lock (트랜잭션 Lock) | 130 |
| TX Lock ► ITL 슬롯 부족 · ► 인덱스 분할 · ► 기타 · ► DML 로우 Lock | 136 · 138 · 141 · 142 |
| TM Lock ► DML 테이블 Lock (무인덱스 FK 포함) | 143 |
| Lock을 푸는 열쇠, 커밋 | 150 |
성능고도화 I권 Ch1 — 함께 볼 곳
| 주제 | 책 페이지 |
|---|---|
| 버퍼 Lock · 버퍼 Lock의 필요성 (07화) | 30 · 32 |
| Undo 세그먼트 트랜잭션 테이블 슬롯 · Lock Byte (→ 13화) | 41 · 46 |
| 래치와 대기 이벤트 개념 명확화 | 92 |
오라클 공식문서
| 주제 | 매뉴얼 | 장 제목 |
|---|---|---|
| 락의 종류 · DML이 거는 락 · Locks and Foreign Keys · 데드락 | cncpt | Data Concurrency and Consistency |
LOCK TABLE의 모드 | sqlrf | LOCK TABLE |
V$LOCK · V$TRANSACTION · V$SESSION.BLOCKING_SESSION · V$ENQUEUE_STATISTICS · V$DIAG_ALERT_EXT | refrn | Dynamic Performance Views |
DDL_LOCK_TIMEOUT | refrn | Initialization Parameters |
| enqueue 대기 진단 | tgdba | Instance Tuning Using Performance Views (Wait Events) |
| 시퀀스 · 캐시 | admin | Managing Views, Sequences, and Synonyms |
| 트레이스 파일 · alert 로그 · ADR | admin | Managing Diagnostic Data |
| 동시성을 고려한 애플리케이션 설계 | adfns | (트랜잭션 · 락 관련 장) |
이 화에서 꼭 확인할 뷰
sql
V$SESSION -- blocking_session · event · seconds_in_wait · sql_id · prev_sql_id · taddr
V$LOCK -- type · id1 · id2 · lmode · request · block · ctime
V$TRANSACTION -- xidusn · xidslot · xidsqn (TX 의 이름표)
V$ENQUEUE_STATISTICS -- eq_type · req_reason · total_req# · total_wait# · cum_wait_time
V$DIAG_ALERT_EXT -- alert 로그를 SQL 로 (ORA-00060 · 트레이스 파일 이름)
V$DIAG_INFO -- 'Diag Trace' (트레이스 디렉터리)
USER_CONSTRAINTS · USER_CONS_COLUMNS · USER_IND_COLUMNS -- FK 와 그 컬럼을 덮는 인덱스다음 화가 여기서 가져가는 것
| 화 | 가져가는 것 |
|---|---|
| 13 | TX의 이름표가 가리키는 언두 세그먼트 헤더의 칸 · 블록의 ITL과 행의 lock byte · 블록 클린아웃 (§2 · §10 · §11) |
| 13 | 포스트모템 1호의 재발방지 2번: 주간 보정 배치를 N건마다 COMMIT(오과장이 다음 주 월요일부터 적용) |
| 14 | 신규 플랫폼 FK 인덱스 셋 · 인덱스 맨 오른쪽 블록 경합을 나누는 방법. 둘 다 적재 비용과 함께 |
| 16 · 17 | V$는 장애가 끝나면 사라진다. 지나간 시간을 보는 도구 (AWR · ASH) |
| 18 | 「SQL 코딩 가이드」 후보 조문: 채번은 업무 트랜잭션에서 떼어 낸다 (근거 1회 · 12화) |