SQLP 튜닝 연습장

테마

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

매시 정각부터 2분 동안 고객센터의 고객 정보 저장이 멈춘다

기초SELECT한빛유통517 / 700

상황 ​

한빛유통 쇼핑몰은 매시 정각에 「구매 적립금 지급」 배치를 돌린다. 직전 한 시간에 결제가 끝난 주문마다 고객 등급으로 적립금을 계산해 포인트 시스템에 보내고, 주문에 지급 표시를 한다. 지난달 '지급하는 사이에 취소된 주문에도 적립금이 나갔다'는 사고가 나서 개발자가 커서에 FOR UPDATE를 붙였다. 그 뒤로 고객센터의 「고객 정보 수정」과 쇼핑몰의 「배송지 변경」이 매시 정각부터 2분 남짓 저장 버튼에서 멈춘다. 개발자의 말이다. "배치는 고객 테이블을 한 줄도 안 고쳐요."

테이블과 인덱스 ​

  • T_ORD 주문: 100,000행 · 896블록 · PK_T_ORD (ORD_SEQ) · IX_T_ORD_PAY (PAY_DT)(이 문제 설정) · 지급 표시 PNT_YN(문제 설정, 기본 'N') · 한 시간에 결제가 끝나는 주문은 400건 안팎
  • M_CUS 고객: 2,000행 · 30블록 · PK_M_CUS (CUS_ID) · CUS_GD (A 1% · B 9% · C 90%) · 연락처 TEL_NO와 배송지 ADDR(문제 설정) · 고객센터와 쇼핑몰은 연락처나 배송지를 고칠 때 고객 행을 UPDATE한다
  • T_PNT_HIS 적립금 지급 이력(이 문제의 파생 테이블) · 포인트 시스템은 같은 주문 번호로 두 번 온 지급 요청을 한 번만 처리한다
  • 배치는 주문마다 포인트 시스템을 부르고(건당 0.3초 안팎) 끝에 한 번 COMMIT한다 · 통계는 어젯밤에 수집했다

원본 SQL ​

sql
-- 「구매 적립금 지급」 배치 (매시 정각) · 직전 한 시간에 결제가 끝난 주문
DECLARE
  CURSOR c IS
    SELECT o.ORD_SEQ, o.CUS_ID, o.PAY_AMT, c.CUS_GD
      FROM T_ORD o, M_CUS c
     WHERE c.CUS_ID  = o.CUS_ID
       AND o.PAY_DT >= :from_dt AND o.PAY_DT < :to_dt
       AND o.ORD_ST  = 'COMPLETE'
       AND o.PNT_YN  = 'N'
       FOR UPDATE;                       -- 지급하는 사이 주문이 취소되지 않게 (지난달 추가)
  v_pnt NUMBER;
BEGIN
  FOR r IN c LOOP
    v_pnt := ROUND(r.PAY_AMT * CASE r.CUS_GD WHEN 'A' THEN 0.03 WHEN 'B' THEN 0.02 ELSE 0.01 END);
    PKG_PNT.SEND(r.CUS_ID, r.ORD_SEQ, v_pnt);                 -- 포인트 시스템 (건당 0.3초)
    INSERT INTO T_PNT_HIS (ORD_SEQ, CUS_ID, PNT_AMT, REG_DT) VALUES (r.ORD_SEQ, r.CUS_ID, v_pnt, SYSDATE);
    UPDATE T_ORD SET PNT_YN = 'Y' WHERE ORD_SEQ = r.ORD_SEQ;
  END LOOP;
  COMMIT;
END;

실행계획 학습용 예시 ​

xplan
[커서 · ALLSTATS LAST · 14:00 배치 (직전 한 시간에 결제가 끝난 주문 418건)]
-------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |              |      1 |        |    418 |00:00:00.02 |     876 |
|   1 |  FOR UPDATE                           |              |      1 |        |    418 |00:00:00.02 |     876 |
|*  2 |   HASH JOIN                           |              |      1 |    410 |    418 |00:00:00.01 |      38 |
|*  3 |    TABLE ACCESS BY INDEX ROWID BATCHED| T_ORD        |      1 |    410 |    418 |00:00:00.01 |       8 |
|*  4 |     INDEX RANGE SCAN                  | IX_T_ORD_PAY |      1 |    436 |    436 |00:00:00.01 |       3 |
|   5 |    TABLE ACCESS FULL                  | M_CUS        |      1 |   2000 |   2000 |00:00:00.01 |      30 |
-------------------------------------------------------------------------------------------------------------
   2 - access("C"."CUS_ID"="O"."CUS_ID")
   3 - filter(("O"."ORD_ST"='COMPLETE' AND "O"."PNT_YN"='N'))
   4 - access("O"."PAY_DT">=:FROM_DT AND "O"."PAY_DT"<:TO_DT)

[시간표 · 14:00 배치]
시각        적립금 배치 (SID 47)                                   고객 정보 화면
14:00:00    커서 OPEN · 주문 418행과 그 주문의 고객 401명 행을 잠금
14:00:01 ~  주문마다 포인트 시스템 호출 · 지급 이력 INSERT · PNT_YN 고침
14:00:40                                                           고객센터 (SID 133) CUS_000731 연락처 저장 → 기다림 (SID 47)
14:01:15                                                           쇼핑몰 (SID 152 · 160) 배송지 변경 → 기다림 (SID 47)
14:02:06    COMMIT                                                 모두 풀림

[V$SESSION · 14:01:30]
SID   EVENT                            SECS   BLOCKING_SESSION   하고 있는 문장
47    TCP Socket (KGAS)                   0                      PKG_PNT.SEND (포인트 시스템 응답 대기)
133   enq: TX - row lock contention      50   47                 UPDATE M_CUS SET TEL_NO = :1, ADDR = :2 WHERE CUS_ID = :3
152   enq: TX - row lock contention      15   47                 UPDATE M_CUS SET ADDR = :1 WHERE CUS_ID = :2
160   enq: TX - row lock contention      12   47                 (같은 문장)

[V$LOCK · 14:01:30 (TM 은 객체 이름으로)]
SID   TYPE  대상                    LMODE  REQUEST  BLOCK
47    TM    T_ORD                     3      0        0
47    TM    M_CUS                     3      0        0      ← 배치는 M_CUS 를 한 행도 고치지 않는다
47    TM    T_PNT_HIS                 3      0        0
47    TX    배치의 트랜잭션             6      0        1
133   TM    M_CUS                     3      0        0
133   TX    배치의 트랜잭션             0      6        0

[고객 정보 저장 · 지난주 애플리케이션 로그]
  매시 00 ~ 02분   평균 38초 · 그 밖의 시간 0.02초 · 기다린 고객은 모두 직전 한 시간에 결제한 고객

문제 ​

  1. 실행계획과 V$LOCK을 근거로 배치가 고치지도 않는 고객 행을 왜 잠그는지 설명하시오.
  2. 지급하는 동안 주문은 그대로 잠그면서 고객 화면은 기다리지 않도록 커서를 고치시오. 고친 뒤 FOR UPDATE 줄의 Buffers가 어떻게 바뀌는지도 쓰시오.
원본 SQL을 고쳐 쓰세요. 인덱스 DDL과 힌트도 쓸 수 있습니다.
무엇이 문제였고 왜 이렇게 고쳤는지 쓰세요.0자