입고 예정 배치는 매번 성공인데, 물류센터 화면에 없는 발주가 있다

중급PLSQL유통523 / 700

상황

본사 레거시 유통의 「입고 예정」 배치는 매일 08:00에 오늘 입고될 발주(거래처 리드타임 2일, 이틀 전에 낸 발주)를 발주완료(10)에서 입고중(11)으로 바꾼다. 물류센터 화면은 입고중인 발주만 보여 준다. 2022년에 이 배치가 발주 담당자의 수정 화면을 기다리느라 40분 멈춘 일이 있었고, 그 뒤 배치에 SKIP LOCKED를 넣었다. 지난달부터 물류센터가 "트럭이 왔는데 화면에 없는 발주"라며 입고를 일주일에 한두 번 돌려보낸다. 배치 로그는 매번 '성공'이다.

테이블과 인덱스

CF12 발주: 734행 · 10블록 · PK ORD_NO ('2023061002') · ORD_DT VARCHAR2(8) · ORD_PRGS_CD (CF16 공통코드: 10 발주완료 · 11 입고중 · 12 입고완료) · 하루 입고 예정 3~8건
CF13 발주상품: 7,102행 · 57블록 · PK (ORD_NO, ITM_NO)
발주 수정 화면(본사 발주 담당자)은 발주를 열 때 CF12 행을 FOR UPDATE로 잠그고, 수량을 고쳐 저장할 때 COMMIT한다. 아침 08:00 전후에 전날까지의 발주를 다듬는 일이 잦다.

원본 SQL

sql

-- 08:00 「입고 예정」 배치
DECLARE
  v_cnt PLS_INTEGER := 0;
BEGIN
  FOR r IN (SELECT ORD_NO
              FROM CF12
             WHERE ORD_DT = TO_CHAR(SYSDATE - 2, 'YYYYMMDD')
               AND ORD_PRGS_CD = '10'
               FOR UPDATE SKIP LOCKED) LOOP           -- 2022.04 수정 화면을 기다리지 않게
    UPDATE CF12 SET ORD_PRGS_CD = '11', MOD_DTT = SYSDATE WHERE ORD_NO = r.ORD_NO;
    v_cnt := v_cnt + 1;
  END LOOP;
  COMMIT;
  PKG_LOG.WRITE('입고 예정 ' || v_cnt || '건 성공');
END;

실행계획 학습용 예시

xplan

[시간표 — 6월 12일 · 6월 10일에 낸 발주 6건]
시각       발주 담당자 화면 (SID 66)                     입고 예정 배치 (SID 140)
07:52      발주 2023061002 열기 → CF12 행 잠금 (FOR UPDATE)
           수량 다듬는 중 — COMMIT 전
08:00:00                                                대상 6건 중 잠기지 않은 5건만 가져온다 (2023061002 는 에러 없이 건너뜀)
08:00:01                                                5건 → 11 · COMMIT · 로그 '입고 예정 5건 성공'
08:09      저장 · COMMIT (2023061002 는 여전히 10 발주완료)
10:40      트럭 도착 · 물류센터 화면에 2023061002 없음 → 입고 거부

[V$LOCK — 08:00:00]
SID   TYPE  대상                    LMODE  REQUEST  BLOCK
66    TM    CF12                      3      0        0
66    TX    SID 66 의 트랜잭션          6      0        0      ← 기다리는 사람이 없어 BLOCK 0
140   TM    CF12                      3      0        0
140   TX    SID 140 의 트랜잭션         6      0        0
      (배치는 아무것도 기다리지 않았다 · enq: TX 0)

[같은 대상 — 배치 전후 건수]
            ORD_PRGS_CD = '10'   ORD_PRGS_CD = '11'
08:00 전            6                    0
08:00 후            1                    5        ← 로그에는 '5건 성공'뿐, 몇 건이 남았는지는 없다

[지난 4주 — 입고를 돌려보낸 발주]  7건 · 모두 배치 시각에 발주 수정 화면이 열려 있던 발주

문제

시간표와 배치 전후 건수를 근거로 발주가 어떻게 빠졌는지, 배치 로그가 왜 '성공'인지 설명하시오.
한 건도 조용히 빠뜨리지 않으면서 수정 화면을 오래 기다리지도 않도록 배치를 고치시오.
SKIP LOCKED가 맞는 일과 맞지 않는 일을 구분해 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- 08:00 「입고 예정」 배치: 잠긴 건은 건너뛰되 남겨 두지 않는다 — 다시 세고, 다시 시도하고, 끝내 남으면 알린다
DECLARE
  v_done PLS_INTEGER := 0;
  v_left PLS_INTEGER;
BEGIN
  FOR k IN 1 .. 10 LOOP                                       -- 최대 10번 (1분 간격)
    FOR r IN (SELECT ORD_NO
                FROM CF12
               WHERE ORD_DT = TO_CHAR(SYSDATE - 2, 'YYYYMMDD')
                 AND ORD_PRGS_CD = '10'
                 FOR UPDATE SKIP LOCKED) LOOP
      UPDATE CF12 SET ORD_PRGS_CD = '11', MOD_DTT = SYSDATE WHERE ORD_NO = r.ORD_NO;
      v_done := v_done + 1;
    END LOOP;
    COMMIT;

    SELECT COUNT(*) INTO v_left                               -- 잠겨 있어 건너뛴 건이 남았나
      FROM CF12
     WHERE ORD_DT = TO_CHAR(SYSDATE - 2, 'YYYYMMDD')
       AND ORD_PRGS_CD = '10';
    EXIT WHEN v_left = 0;
    DBMS_SESSION.SLEEP(60);
  END LOOP;

  IF v_left > 0 THEN                                          -- 10분 뒤에도 남았으면 '성공'이 아니다
    PKG_LOG.WRITE('입고 예정 ' || v_done || '건 · 미처리 ' || v_left || '건 (수정 화면이 잡고 있음)');
    RAISE_APPLICATION_ERROR(-20010, '입고 예정 미처리 ' || v_left || '건');
  END IF;
  PKG_LOG.WRITE('입고 예정 ' || v_done || '건 · 미처리 0건');
END;

개선 후 실행계획 학습용 예시

xplan

[고친 판 — 같은 6월 12일 (학습용 예시)]
시각       발주 담당자 화면 (SID 66)            입고 예정 배치 (SID 140)
08:00:00                                       1회차: 5건 → 11 · COMMIT · 남은 건수 1
08:01 ~                                        2 ~ 9회차: 남은 건수 1 (기다리지 않고 1분마다 다시 센다)
08:09      저장 · COMMIT
08:09:00                                       10회차: 2023061002 → 11 · COMMIT · 남은 건수 0 · 로그 '6건 · 미처리 0건'
10:40      트럭 도착 · 물류센터 화면에 6건 모두 있다
  (10분 뒤에도 남으면 배치가 에러로 끝나 담당자에게 알림이 간다)

모범 답안 서술

07:52에 발주 담당자가 발주 2023061002를 수정 화면으로 열면서 그 행을 FOR UPDATE로 잠갔고, 08:09에 저장할 때까지 쥐고 있었다. 08:00 배치의 커서는 FOR UPDATE SKIP LOCKED라서 그 행을 에러 없이 건너뛰고 잠기지 않은 5건만 가져왔다. SKIP LOCKED는 잠긴 행을 기다리지도 않고 에러도 내지 않으며, 결과에서 그냥 뺀다. V$LOCK에서 배치가 아무것도 기다리지 않은 것(enq: TX 0, SID 66의 BLOCK 0)도 그 때문이다. 그래서 2023061002는 10(발주완료)에 남았고, 입고중만 보여 주는 물류센터 화면에 나오지 않아 10:40에 트럭이 돌아갔다. 배치 로그가 '성공'인 것은 배치가 처리한 건수만 셌기 때문이다. 대상이 6건이었는지, 몇 건이 남았는지는 세지 않았다. 배치 전후 건수를 비교하면 08:00 뒤에도 '10'이 1건 남아 있다. 지난 4주의 7건이 모두 그 시각에 수정 화면이 열려 있던 발주라는 것도 같은 원인을 가리킨다. 고친 판은 건너뛴 건을 남겨 두지 않는다. SKIP LOCKED로 잠기지 않은 건부터 처리하고 COMMIT한 뒤, 같은 조건으로 남은 건수를 다시 센다. 남았으면 1분 쉬고 다시 시도하고, 10번(10분) 뒤에도 남았으면 '성공'이 아니라 미처리 건수를 남기고 에러로 끝내 담당자가 알게 한다. 수정 화면을 기다리지는 않으면서 한 건도 조용히 빠뜨리지 않는다. 건마다 FOR UPDATE WAIT 5로 잠깐 기다리며 처리하고, 그래도 못 잡은 건을 모아 다시 시도하는 방법도 같은 효과를 낸다. SKIP LOCKED는 누가 처리해도 되고, 지금 못 한 건은 다음에 누군가 집는 일에 맞다. 작업 큐나 출고 대기열처럼 여러 작업자가 나눠 가지는 일이다. 이번 배치처럼 대상 전부를 정해진 시각까지 바꿔야 하는 마감이나 일괄 변경에는 맞지 않다. 건너뛴 건을 다시 집을 사람이 없기 때문이다. 근본 원인은 수정 화면이 발주를 열 때 잠그고 저장할 때까지 쥐는 데 있다. 화면을 저장하는 순간에만 짧게 잠그도록 바꾸면 배치가 건너뛸 일 자체가 드물어진다.

해설

2022년의 처방은 증상(배치가 40분 기다림)을 에러 없는 누락으로 바꿨다. 기다림은 보이지만 누락은 보이지 않는다. 처방을 넣을 때는 '대신 무엇을 잃는가'를 함께 적고, 잃는 것을 확인하는 장치(남은 건수 확인)를 같이 넣는다(교재 13화의 안전 조치의 대가).
배치의 '성공'은 처리한 건수가 아니라 남은 건수로 판정한다. 대상 건수, 처리 건수, 남은 건수를 함께 남기면 다음 장애 때 로그만 보고도 무엇이 빠졌는지 안다.
NOWAIT로 바꾸면 첫 잠긴 행에서 ORA-00054로 배치 전체가 실패한다. 빠뜨리지는 않지만 수정 화면 하나 때문에 입고 예정 6건이 모두 늦어진다.
수정 화면을 저장 순간에만 잠그게 바꾸면, 화면을 연 사이에 배치가 상태를 11로 바꾼 발주를 담당자가 고치려 할 수 있다. 저장할 때 상태가 여전히 10인지 확인하는 조건을 UPDATE에 함께 준다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황