SQLP 튜닝 연습장

테마

튜닝 연습Lock과 동시성 제어
LOCK-002

FK를 켠 다음 날, 개인정보 파기 배치가 주문 화면을 기다린다

기초DDL이커머스497 / 700

이 문제는 교재 18화 졸업 과제와 같은 이커머스 데이터베이스를 씁니다. 『튜너의 1년』을 순서대로 읽고 있다면 졸업 과제를 먼저 풀고 오세요. 아래 「테이블과 인덱스」 칸에 과제의 답 일부(제약 조건, 인덱스, 통계의 실제 상태)가 나옵니다.

상황 ​

18화 진단에서 신규 이커머스 플랫폼의 FK 39개가 전부 꺼져 있고 고아 데이터도 있다는 것이 드러났다. 그래서 고아를 정리하고, 7월 9일에 첫 단계로 orders → customers FK 하나를 켰다. 다음 날 11:00에 개인정보 파기 배치(가입 5년이 지나도록 주문이 없는 고객을 지운다)가 돌기 시작했는데, 평소 2초면 끝나던 배치가 4분째 끝나지 않는다. 그 사이 고객 등급 갱신 배치도 멈췄다. 주문 화면은 정상이다.

테이블과 인덱스 ​

  • customers(부모): 5,230행 · 51블록 · PK customer_id
  • orders(자식): 34,908행 · 193블록 · FK_ORDERS_CUSTOMER_ID (customer_id → customers) ENABLED VALIDATED · customer_id에 인덱스 없음(PK order_id만 있다)
  • 나머지 FK 38개는 아직 꺼져 있다(DISABLED).
  • 주문 화면은 주문을 INSERT한 뒤 결제창이 닫힐 때 COMMIT한다(보통 1~3분).
  • 파기 배치는 대상 고객을 한 명씩 DELETE하고 끝에 COMMIT한다(오늘 대상 140명).

원본 SQL ​

sql
-- 7월 9일 반영 — 고아를 정리하고 FK 하나를 켰다
ALTER TABLE orders ENABLE VALIDATE CONSTRAINT FK_ORDERS_CUSTOMER_ID;

-- 매일 11:00 개인정보 파기 배치 — 가입 5년 · 주문 없는 고객
BEGIN
  FOR r IN (SELECT c.customer_id
              FROM customers c
             WHERE c.join_date < ADD_MONTHS(SYSDATE, -60)
               AND NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id)) LOOP
    DELETE FROM customers WHERE customer_id = r.customer_id;
  END LOOP;
  COMMIT;
END;

실행계획 학습용 예시 · 기다림의 모양은 교재 12화 실측(26ai Free) ​

xplan
[시간표 — 7월 10일 · FK 를 켠 다음 날]
시각       주문 화면 A (SID 142)                파기 배치 B (SID 77)                          등급 갱신 C (SID 95)
11:00:00   INSERT INTO orders (고객 3,118)
           결제창 — COMMIT 전
11:00:01                                        고객 52 명 DELETE (한 명 2 ~ 3 ms)
11:00:01                                        53 번째 고객 4,410 DELETE → 기다림 ①
11:00:40                                                                                      UPDATE customers SET grade … (전 고객)
                                                                                              → B 가 지운 고객 행에서 기다림 ②
11:02:30   COMMIT (결제 완료)                   → 다음 고객 · 또 다른 주문 화면 D 를 만나 또 기다림
11:04:10                                        140 명 끝 · COMMIT                            → 이제야 진행

[V$SESSION — 11:01:30]
SID   EVENT                            SECS   BLOCKING_SESSION   하고 있는 문장
142   SQL*Net message from client        90                      (결제창 — 트랜잭션을 연 채)
77    enq: TX - row lock contention      89   142                DELETE FROM CUSTOMERS WHERE CUSTOMER_ID = :B1
95    enq: TX - row lock contention      50    77                UPDATE CUSTOMERS SET GRADE = …

[V$LOCK — 11:01:30 (TM 은 객체 이름으로)]
SID   TYPE  대상                  LMODE  REQUEST  BLOCK
142   TM    ORDERS                  3      0        0
142   TM    CUSTOMERS               3      0        0      ← 자식에 넣은 세션은 부모에도 TM 3 을 쥔다
142   TX    A 의 트랜잭션            6      0        1
77    TM    CUSTOMERS               3      0        0
77    TX    A 의 트랜잭션            0      4        0      ← 행 락(6)이 아니라 4 — 'A 가 끝나야 결과를 안다'
77    TX    B 의 트랜잭션            6      0        1
95    TM    CUSTOMERS               3      0        0
95    TX    B 의 트랜잭션            0      6        0
      (B 는 ORDERS 에 TM 을 쥐지도 요청하지도 않았다)

[고객 한 명 DELETE — 혼자 돌렸을 때 session logical reads]
  FK 꺼짐 약 6 · FK 켬 (인덱스 없음) 약 200 — orders 193 블록을 통째로 훑는다

[같은 장면을 문서(Concepts 「Locks and Unindexed Foreign Keys」)와 이전 버전 사례는 이렇게 적는다 — 이 교재의 컨테이너에서는 재현되지 않았다]
  B 가 자식 ORDERS 에 테이블 락 S(4) 를 요청 → A 의 TM 3 과 충돌 → B 는 enq: TM - contention
  그 뒤에 오는 주문 INSERT (TM 3 요청) 는 B 의 요청 뒤에 줄을 선다 → 주문 화면 전체가 멈춘다

문제 ​

  1. 시간표를 보고 누가 누구를 기다리는지(사슬)와, 고객 한 명을 지우는 데 왜 orders를 통째로 훑는지 설명하시오.
  2. DDL로 고치시오. 그 DDL을 낮에 돌려도 되는지도 쓰시오.
  3. "FK를 다시 끄면 된다"는 의견에 답하시오.
원본 SQL을 고쳐 쓰세요. 인덱스 DDL과 힌트도 쓸 수 있습니다.
무엇이 문제였고 왜 이렇게 고쳤는지 쓰세요.0자