고객 정보를 열어 두기만 했는데 등급 재산정 배치가 50분을 기다렸다

기초UPDATE한빛유통520 / 700

상황

한빛유통 고객센터의 상담 프로그램(설치형 클라이언트)은 상담원이 고객을 조회할 때 개인정보 접근 기록을 남긴다. 작년 개인정보 점검 뒤 M_CUS에 컬럼 둘을 더하고, 조회할 때마다 그 고객 행을 UPDATE하게 바꿨다. COMMIT은 상담원이 「저장」 버튼을 누를 때만 한다. 7월 1일 12:00에 돈 「고객 등급 재산정」 배치가 12:51에야 끝났다(평소 3초). 그 사이 다른 상담원 둘의 고객 조회 화면도 멈췄다. 상담원들은 점심시간이라 아무것도 하지 않았다고 한다.

테이블과 인덱스

M_CUS 고객: 2,000행 · 30블록 · PK_M_CUS (CUS_ID) · 작년에 더한 LST_INQ_DT(마지막 조회 시각), LST_INQ_EMP(조회한 상담원)
상담 프로그램은 상담원 한 명이 DB 세션 하나를 계속 쥔다(자동 COMMIT 꺼짐).
등급 재산정 배치는 매일 12:00에 고객 2,000명의 CUS_GD를 한 문장으로 고치고 COMMIT한다.
개인정보 점검 요구 원문: "개인정보 조회 이력(조회자, 조회 시각, 대상)을 1년 보관할 것"

원본 SQL

sql

-- 상담 프로그램: 고객 조회 (상담원이 고객을 열 때마다)
SELECT CUS_ID, CUS_NM, RGN_ID, GND_TP, BTH_YMD, CUS_GD
  FROM M_CUS
 WHERE CUS_ID = :cus_id;

UPDATE M_CUS
   SET LST_INQ_DT = SYSDATE, LST_INQ_EMP = :emp_id     -- 개인정보 접근 기록 (작년 점검 조치)
 WHERE CUS_ID = :cus_id;
-- COMMIT 은 「저장」 버튼에서만 (조회만 하고 넘어가면 다음 저장 때 함께)

-- 12:00 「고객 등급 재산정」 배치 (평소 3초)
MERGE INTO M_CUS c
USING (SELECT CUS_ID,
              CASE WHEN SUM(ORD_AMT) >= 5000000 THEN 'A'
                   WHEN SUM(ORD_AMT) >= 1000000 THEN 'B' ELSE 'C' END AS NEW_GD
         FROM T_ORD
        WHERE ORD_DT >= ADD_MONTHS(TRUNC(SYSDATE), -12)
        GROUP BY CUS_ID) s
   ON (c.CUS_ID = s.CUS_ID)
 WHEN MATCHED THEN UPDATE SET c.CUS_GD = s.NEW_GD;
COMMIT;

실행계획 학습용 예시 · 블로킹 트리 쿼리는 교재 12화 §5

xplan

[블로킹 트리 — 7월 1일 12:20:05]
SID_TREE   PROGRAM            STATUS     EVENT                            SECS    BLOCKER   SQL_ID 의 문장 / PREV_SQL_ID 의 문장
77         CSCLIENT.exe       INACTIVE   SQL*Net message from client      1,207             (없음) / UPDATE M_CUS SET LST_INQ_DT = SYSDATE …
  203      JDBC Thin Client   ACTIVE     enq: TX - row lock contention    1,203   77        MERGE INTO M_CUS c USING …
    18     CSCLIENT.exe       ACTIVE     enq: TX - row lock contention      640   203       UPDATE M_CUS SET LST_INQ_DT = SYSDATE … (CUS_000412)
    41     CSCLIENT.exe       ACTIVE     enq: TX - row lock contention      312   203       UPDATE M_CUS SET LST_INQ_DT = SYSDATE … (CUS_000377)

[V$LOCK — 같은 시각]
SID   TYPE  대상                  LMODE  REQUEST  BLOCK
77    TM    M_CUS                   3      0        0
77    TX    SID 77 의 트랜잭션        6      0        1
203   TM    M_CUS                   3      0        0
203   TX    배치의 트랜잭션           6      0        1      ← 이미 고친 고객 1,286명의 행
203   TX    SID 77 의 트랜잭션        0      6        0      ← CUS_001287 에서 멈췄다
18    TX    배치의 트랜잭션           0      6        0
41    TX    배치의 트랜잭션           0      6        0

[V$TRANSACTION ⋈ V$SESSION — SID 77]
  START_TIME 11:59:58 · USED_UREC 1 (행 하나) · 상담원 7번 자리
  11:59:58 고객 CUS_001287 조회 · 그 뒤 실행한 SQL 0개 (화면을 열어 둔 채 점심)

[12:51:40]  상담원 7번이 돌아와 다른 고객을 조회하고 「저장」 → COMMIT → 12:51:43 배치 끝 → 18 · 41 진행

문제

블로킹 트리와 V$LOCK, V$TRANSACTION을 근거로 배치가 무엇을 50분 동안 기다렸는지 설명하시오. 막은 세션이 아무 SQL도 실행하지 않는데 락을 쥐고 있던 이유도 쓰시오.
개인정보 점검 요구를 지키면서 조회 화면이 행 락을 남기지 않도록 고치시오.

개선 SQL원본 SQL을 고쳐 쓰세요. 인덱스 DDL과 힌트도 쓸 수 있습니다.

개선 사유무엇이 문제였고 왜 이렇게 고쳤는지 쓰세요.0자

모범답안

모범 답안 SQL

sql

-- 조회 이력: 조회마다 한 행씩 쌓기만 한다 (요구 원문: 조회자 · 조회 시각 · 대상을 1년 보관)
CREATE TABLE T_CUS_INQ_LOG (
  INQ_DT  DATE          NOT NULL,
  EMP_ID  VARCHAR2(20)  NOT NULL,
  CUS_ID  VARCHAR2(40)  NOT NULL
);

CREATE OR REPLACE PROCEDURE PRC_CUS_INQ_LOG(p_emp VARCHAR2, p_cus VARCHAR2) IS
  PRAGMA AUTONOMOUS_TRANSACTION;               -- 상담원이 고치던 내용과 섞이지 않게 따로 커밋
BEGIN
  INSERT INTO T_CUS_INQ_LOG (INQ_DT, EMP_ID, CUS_ID) VALUES (SYSDATE, p_emp, p_cus);
  COMMIT;
END;

-- 상담 프로그램: 고객 조회 — 고객 행은 고치지 않는다
SELECT CUS_ID, CUS_NM, RGN_ID, GND_TP, BTH_YMD, CUS_GD
  FROM M_CUS
 WHERE CUS_ID = :cus_id;
BEGIN PRC_CUS_INQ_LOG(:emp_id, :cus_id); END;

개선 후 실행계획 학습용 예시 · 블로킹 트리 쿼리는 교재 12화 §5

xplan

[고친 뒤 — 같은 점심시간 (학습용 예시)]
  고객 조회 하나   M_CUS 를 읽기만 한다 · 이력 INSERT 1행 · 자율 트랜잭션 COMMIT · 쥐는 락 없음
  12:00 배치       3초에 끝 (기다림 0) · 다른 상담원의 조회도 기다리지 않는다
  조회 이력        조회마다 한 행 · 하루 약 4,000행 · 1년 보관 뒤 월 단위로 지운다

모범 답안 서술

블로킹 트리의 뿌리는 SID 77, 상담 프로그램(CSCLIENT.exe)이다. STATUS는 INACTIVE이고 이벤트는 SQL*Net message from client, 곧 아무 SQL도 실행하지 않고 클라이언트의 다음 요청을 기다리는 중이다. 그런데 V$TRANSACTION에는 11:59:58에 시작한 트랜잭션이 행 하나(USED_UREC 1)를 고친 채 열려 있고, PREV_SQL_ID는 UPDATE M_CUS SET LST_INQ_DT …다. 상담원이 고객 CUS_001287을 조회하자 화면이 접근 기록을 남기려고 그 고객 행을 UPDATE했고, COMMIT은 「저장」 버튼에서만 하므로 상담원이 화면을 열어 둔 채 점심을 먹으러 간 50분 동안 행 락(TX 6)을 쥐고 있었다. 12:00 등급 재산정 배치(SID 203)는 고객 2,000명을 한 문장으로 고치다가 CUS_001287에서 77의 TX를 6으로 기다렸다. 그동안 배치는 이미 고친 고객 1,286명의 행 락을 쥐고 있어서, 그중 한 고객을 조회한 다른 상담원(18, 41)의 화면도 접근 기록 UPDATE에서 배치를 기다렸다. 사슬은 상담원 77 ← 배치 203 ← 상담원 18, 41이다. 12:51:40에 상담원이 돌아와 저장하자(COMMIT) 3초 만에 모두 풀렸다. 고친 판은 조회 화면이 고객 행을 고치지 않게 한다. 접근 기록은 조회 이력 테이블에 한 행씩 INSERT하고, 자율 트랜잭션 프로시저로 화면의 트랜잭션과 따로 바로 COMMIT한다. 이력 INSERT는 새 행만 잠그므로 누구와도 부딪치지 않고, 그 락도 몇 ms 뒤에 사라진다. 따로 커밋하므로 상담원이 고치던 다른 내용이 조회 때 함께 커밋되는 일도 없다. 개인정보 점검 요구 원문은 "조회 이력(조회자, 조회 시각, 대상)을 1년 보관"이다. 마스터 행의 LST_INQ_DT를 덮어쓰는 방식은 마지막 한 번만 남기므로 애초에 요구를 지키지 못했다. 이력 테이블은 조회마다 한 행을 남기므로 요구에도 맞는다.

해설

뿌리가 INACTIVE인 블로킹은 '트랜잭션을 연 채 쉬는 세션'이다. 화면을 열어 둔 채 자리를 비운 사용자, COMMIT을 빠뜨린 채 커넥션 풀에 돌아간 커넥션, 수동 UPDATE 뒤 COMMIT하지 않은 개발 도구가 흔한 뿌리다. V$TRANSACTION.START_TIME과 뿌리의 PREV_SQL_ID로 언제 무엇을 했는지 찾는다(교재 12화 §5).
조회 화면은 락을 남기지 않는다. 읽기만 하는 화면에 끼워 넣은 UPDATE(조회수, 마지막 조회 시각, 읽음 표시)는 그 화면을 쓰기 화면으로 바꾼다. 꼭 고쳐야 한다면 바로 COMMIT하는 짧은 트랜잭션으로 떼어 낸다.
고친 즉시 COMMIT하는 방법(UPDATE 바로 뒤 COMMIT)도 락은 없앤다. 하지만 같은 세션에서 상담원이 고치던 다른 내용까지 그 순간 커밋되고, 이력은 여전히 마지막 한 번만 남는다.
장애 중이라면 뿌리 77의 트랜잭션은 행 하나짜리라 되감는 비용이 거의 없다. 상담원에게 연락이 닿지 않으면 세션을 끊는 것이 빠르다. 재발을 막으려면 프로파일의 유휴 시간 제한이나, 최근 버전의 '다른 세션을 막는 유휴 세션' 제한(MAX_IDLE_BLOCKER_TIME)을 함께 검토한다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · 블로킹 트리 쿼리는 교재 12화 §5 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · 블로킹 트리 쿼리는 교재 12화 §5 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황