자동발주 배치가 도는 3분 동안 물류센터 입고 화면이 멈춘다

기초PLSQL유통502 / 700

상황

본사 레거시 유통의 자동발주 배치는 매일 17:30에 재고가 기준 아래로 떨어진 상품을 모아 발주 60건 안팎을 만든다. 한 트랜잭션으로 돌고 약 3분 걸린다. 지난주부터 그 3분 동안 물류센터의 「입고 확인」 화면(발주 진행 코드를 입고 완료로 바꾼다)이 저장 버튼에서 멈춘다. 2주 전에 번호 받기 함수를 고친 사람은 이렇게 적어 두었다. "발주번호가 두 번 겹쳐서 LOCK TABLE 추가 (MAX에 FOR UPDATE는 ORA-01786)."

테이블과 인덱스

CF12 발주: 734행 · 10블록 · CF12_PK (ORD_NO) · ORD_DT VARCHAR2(8) · ORD_PRGS_CD 발주 진행 코드
발주번호 ORD_NO: 발주일자 여덟 자리와 그날의 두 자리 일련번호(예 '2023010205') · 날마다 01부터 다시 시작한다
CF13 발주상품: 7,102행 · 57블록 · PK (ORD_NO, ITM_NO)
본사 MD도 낮에 화면에서 발주를 하루 몇 건 손으로 등록한다(같은 번호 체계, 같은 함수)
「입고 확인」 화면: UPDATE CF12 SET ORD_PRGS_CD = :cd WHERE ORD_NO = :no · 17:00 ~ 18:00이 가장 바쁘다

원본 SQL

sql

-- PKG_PO.NEW_ORDER_NO — 자동발주 배치와 MD 화면이 같이 부른다 (COMMIT 은 부른 쪽이 한다)
FUNCTION NEW_ORDER_NO(p_dt VARCHAR2) RETURN VARCHAR2 IS
  v_no CF12.ORD_NO%TYPE;
BEGIN
  LOCK TABLE CF12 IN EXCLUSIVE MODE;            -- 번호가 겹쳐서 추가 (2주 전)
  SELECT p_dt || LPAD(NVL(MAX(SUBSTR(ORD_NO, 9, 2)), '00') + 1, 2, '0')
    INTO v_no
    FROM CF12
   WHERE ORD_DT = p_dt;
  RETURN v_no;
END;

-- 자동발주 배치 (17:30 · 한 트랜잭션)
FOR r IN (SELECT PTNR_NO FROM … /* 기준 미달 상품을 거래처별로 묶은 60 건 */) LOOP
  v_no := NEW_ORDER_NO(:today);
  INSERT INTO CF12 (ORD_NO, ORD_DT, PTNR_NO, ORD_PRGS_CD) VALUES (v_no, :today, r.PTNR_NO, '10');
  INSERT INTO CF13 (ORD_NO, ITM_NO, ORD_QTY) SELECT v_no, … ;
END LOOP;
COMMIT;

실행계획 학습용 예시

xplan

[V$SESSION — 17:31:40 · 배치가 돈 지 100초]
SID   EVENT                            SECS   BLOCKING_SESSION   하는 문장
212   db file sequential read             0                      INSERT INTO CF13 …                    (자동발주 배치)
57    enq: TM - contention               88   212                UPDATE CF12 SET ORD_PRGS_CD …          (입고 확인 1)
63    enq: TM - contention               61   212                UPDATE CF12 SET ORD_PRGS_CD …          (입고 확인 2)
81    enq: TM - contention               14   212                LOCK TABLE CF12 IN EXCLUSIVE MODE     (MD 발주 등록)

[V$LOCK — 같은 시각]
SID   TYPE  대상                   LMODE  REQUEST  BLOCK
212   TM    CF12                     6      0        1      ← LOCK TABLE … EXCLUSIVE · COMMIT 까지
212   TM    CF13                     3      0        0
212   TX    212 의 트랜잭션           6      0        0
57    TM    CF12                     0      3        0
63    TM    CF12                     0      3        0
81    TM    CF12                     0      6        0

[17:30 ~ 17:33 · 배치 한 번]
  발주 61건 · 발주번호 '2023121501' ~ '2023121561' · 입고 확인 대기 최대 172초
  입고 확인 화면의 조회(SELECT)는 막히지 않았다 · 배치가 COMMIT 하자 기다리던 UPDATE 가 한꺼번에 풀렸다

문제

V$LOCK을 보고 입고 확인 화면이 무엇을 기다리는지, 왜 배치가 끝날 때까지 풀리지 않는지 설명하시오.
발주번호가 겹치지 않으면서 입고 확인 화면을 막지 않도록 번호 받기를 고치시오. 바꾼 뒤에도 남는 기다림이 있다면 무엇인지 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- 발주번호 채번 행 하나 — 이 행만 잠근다 (CF12 는 잠그지 않는다)
CREATE TABLE CF12_NUM (
  NUM_TP  VARCHAR2(10) PRIMARY KEY,
  LAST_DT VARCHAR2(8)  NOT NULL,
  LAST_NO NUMBER(2)    NOT NULL
);
INSERT INTO CF12_NUM VALUES ('PO', '20231215', 61);   -- 오늘까지 나간 마지막 번호로 시작
COMMIT;

FUNCTION NEW_ORDER_NO(p_dt VARCHAR2) RETURN VARCHAR2 IS
  v_last CF12_NUM.LAST_NO%TYPE;
BEGIN
  -- 날짜가 바뀌었으면 1 부터 · 한 문장이라 읽기와 올리기 사이에 틈이 없다
  UPDATE CF12_NUM
     SET LAST_NO = CASE WHEN LAST_DT = p_dt THEN LAST_NO + 1 ELSE 1 END,
         LAST_DT = p_dt
   WHERE NUM_TP = 'PO'
  RETURNING LAST_NO INTO v_last;
  RETURN p_dt || LPAD(v_last, 2, '0');
END;

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

xplan

[고친 뒤 — 17:31:40 (학습용 예시)]
SID   EVENT                            SECS   BLOCKING_SESSION   하는 문장
212   db file sequential read             0                      INSERT INTO CF13 …        (자동발주 배치)
57    SQL*Net message from client         2                      (입고 확인 — 저장 끝)
81    enq: TX - row lock contention      14   212                UPDATE CF12_NUM …         (MD 발주 등록)

SID   TYPE  대상                   LMODE  REQUEST  BLOCK
212   TM    CF12                     3      0        0
212   TM    CF12_NUM                 3      0        0
212   TX    212 의 트랜잭션           6      0        1      ← CF12_NUM 의 'PO' 행 하나
81    TX    212 의 트랜잭션           0      6        0
  입고 확인 대기 최대 172초 → 0 · MD 발주 등록은 여전히 배치가 COMMIT 할 때까지 기다린다

모범 답안 서술

배치(SID 212)는 번호를 받을 때마다 LOCK TABLE CF12 IN EXCLUSIVE MODE를 부른다. V$LOCK에서 CF12의 TM LMODE 6, BLOCK 1이 그 락이다. 테이블 락도 트랜잭션이 끝날 때, 곧 배치가 61건을 다 만들고 COMMIT할 때 풀린다. 그래서 3분 내내 CF12 전체가 잠긴다. 입고 확인 화면의 UPDATE는 배치와 다른 행을 고치는데도 CF12에 TM 3(RX)을 얻어야 하고, TM 6과는 함께 쥘 수 없다. 그래서 REQUEST 3으로 enq: TM - contention을 기다린다(최대 172초). EXCLUSIVE 모드에서도 조회는 막히지 않으므로 화면은 저장 버튼에서만 멈춘다. 겹침을 막는 데 필요한 것은 번호를 받는 세션끼리만 줄을 세우는 일이다. 발주번호 채번 행 하나(CF12_NUM의 'PO')를 두고 UPDATE … RETURNING 한 문장으로 받는다. 날짜가 바뀌면 CASE로 1부터 다시 시작하므로, 그날 첫 번호를 만들려고 행을 새로 넣다가 부딪힐 일도 없다. 이제 배치가 잠그는 것은 채번 행 하나뿐이고 CF12에는 보통의 TM 3만 걸리므로, 입고 확인 화면은 기다리지 않는다(최대 172초 → 0). 남는 기다림도 있다. 행 락도 COMMIT까지 가므로, 배치가 도는 3분 동안 MD가 화면에서 발주를 등록하면 그 행에서 기다린다. 줄이려면 배치가 필요한 61개를 한 번에 받거나(LAST_NO를 61만큼 올린다), 번호 받기를 짧은 별도 트랜잭션으로 떼어 낸다. 떼어 내면 배치가 ROLLBACK할 때 빈 번호가 남는다.

해설

집계에는 FOR UPDATE를 쓸 수 없다. SELECT MAX(…) … FOR UPDATE는 ORA-01786으로 거부된다. 그래서 LOCK TABLE로 넘어가기 쉬운데, 행 하나만 잠그면 되는 일에 테이블 전체를 잠근 셈이다.
TM 락도 트랜잭션 단위다. LOCK TABLE은 COMMIT이나 ROLLBACK으로만 풀린다. 함수 안에서 부르든 맨 앞에서 부르든 부른 쪽의 트랜잭션이 끝날 때까지 간다(교재 12화 §3).
두 자리 일련번호는 하루 99건이 한계다. 배치가 61건을 만들고 MD가 손으로 몇 건 더하므로 여유가 크지 않다. LAST_NO를 NUMBER(2)로 두면 100번째에서 ORA-01438로 멈추므로, 한계에 닿은 날을 조용히 넘기지 않는다.
다른 정답: CF12_PK에서 그날 범위의 MAX(ORD_NO)를 인덱스 맨 끝 한 건으로 읽고, 겹치면 ORA-00001을 받아 다시 받는 방법도 있다. 락을 하나도 걸지 않는 대신 다시 받는 코드가 필요하다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황