실패해서 되돌렸다는 재고 마감을 세 번 돌리자 재고가 하루 판매량의 세 배만큼 줄었다

모범 답안 SQL ​

-- 백업 테이블은 배포 때 한 번만 만들어 둔다 (DDL 은 트랜잭션 밖에서)
--   CREATE TABLE CF18_BAK AS SELECT SYSDATE AS BAK_AT, s.* FROM CF18 s WHERE 1 = 0;
PROCEDURE P_DAY_CLOSE(p_dt VARCHAR2) IS
  v_yn CF_CLOSE.CLOSE_YN%TYPE;
BEGIN
  -- 0. 그날 마감 행을 먼저 잠그고 본다 — 이미 마감했거나 다른 세션이 마감 중이면 멈춘다
  SELECT CLOSE_YN INTO v_yn FROM CF_CLOSE WHERE CLOSE_DT = p_dt FOR UPDATE NOWAIT;
  IF v_yn = 'Y' THEN
    RAISE_APPLICATION_ERROR(-20001, p_dt || ' 는 이미 마감했다');
  END IF;

  -- 1. 마감 전 재고 백업 — DDL 이 아니라 INSERT (같은 트랜잭션) · 빼기 전에 뜬다
  INSERT INTO CF18_BAK SELECT SYSDATE, s.* FROM CF18 s WHERE s.INV_BS_DT = p_dt;

  -- 2. 그날 판매 수량만큼 재고를 뺀다
  UPDATE CF18 s
     SET s.INV_QTY = s.INV_QTY - (SELECT NVL(SUM(d.SLE_QTY), 0)
                                    FROM CF10 h JOIN CF11 d ON d.SLE_NO = h.SLE_NO
                                   WHERE h.SLE_DT = p_dt AND h.SHP_NO = s.SHP_NO AND d.ITM_NO = s.ITM_NO)
   WHERE s.INV_BS_DT = p_dt;

  -- 3. 리포트용 재고표 — 분류가 없는 상품은 '미분류'
  INSERT INTO CF18_RPT (SHP_NO, ITM_NO, INV_BS_DT, INV_QTY, CTG_NM)
  SELECT s.SHP_NO, s.ITM_NO, s.INV_BS_DT, s.INV_QTY, NVL(g.CTG_NM, '미분류')
    FROM CF18 s
    JOIN CF01 i ON i.ITM_NO = s.ITM_NO
    LEFT JOIN CF02 g ON g.CTG_NO = i.CTG_NO
   WHERE s.INV_BS_DT = p_dt;

  -- 4. 마감 표시 · 커밋은 여기 한 번
  UPDATE CF_CLOSE SET CLOSE_YN = 'Y' WHERE CLOSE_DT = p_dt;
  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;                          -- 이제는 정말 처음 상태로 (중간에 DDL 이 없다)
    RAISE;
END;

개선 후 실행계획 학습용 예시 · DDL의 암시적 COMMIT은 교재 12화 실측(실습/01 [E]) ​

[고친 판 — 같은 상황을 연습 DB 에서 다시 (학습용 예시)]
  1회   ③ 실패 → ROLLBACK → CF18 · CF18_BAK · CF18_RPT 모두 그대로 · CLOSE_YN 'N'
  2회   (분류를 채운 뒤) 전부 성공 · COMMIT 한 번 · 재고는 판매만큼 한 번만 줄었다
  3회   (실수로 또 돌림) ORA-20001 '20231230 는 이미 마감했다' · 아무것도 바뀌지 않음

모범 답안 서술 ​

②의 CREATE TABLE … AS SELECT는 DDL이다. 오라클은 DDL을 실행하기 전에 그 세션의 진행 중인 트랜잭션을 먼저 COMMIT한다(암시적 COMMIT). 그래서 첫 번째 실행에서 ①의 UPDATE는 ②가 시작되는 순간 확정되었고, ③이 ORA-01400으로 실패했을 때 예외 처리부의 ROLLBACK이 되돌린 것은 ② 뒤의 일(③의 INSERT)뿐이다. "실패하면 처음 상태로"라는 운영 문서는 프로시저에 DDL이 들어간 날부터 틀린 말이 되었다. 두 번째 실행에서는 ②가 ORA-00955(이름이 이미 있음)로 실패했다. 그래도 DDL은 실행되기 전에 이미 ①을 COMMIT했다. 실패한 DDL도 앞의 일을 확정한다는 것은 교재 12화의 연습 DB에서도 같았다. 세 번째 실행이 성공하면서 ①은 모두 세 번 반영되었다. ①은 INV_QTY - 판매 수량이라는 상대 계산이라 돌 때마다 또 뺀다. 그래서 여섯 매장의 재고가 그날 판매 2,140개의 세 배만큼 줄었고, 있어야 할 값보다 4,280개가 적다(매장 001의 상품 1000021은 118이어야 하는데 74). 고친 판은 트랜잭션 안에 DDL을 두지 않는다. 백업 테이블 CF18_BAK은 배포 때 한 번 만들어 두고, 마감 때는 INSERT로 백업을 남긴다. INSERT는 DML이라 같은 트랜잭션에 묶이므로, 어디서 실패하든 ROLLBACK이 백업과 재고 차감과 리포트를 함께 되돌린다. 커밋은 맨 끝에 한 번이다. 백업은 맨 앞으로 옮겼다. 원본은 ① 뒤에 백업을 떠서 마감 전 재고가 아니라 이미 뺀 값을 남기고 있었다. 다시 돌릴 때를 대비해 맨 앞에서 CF_CLOSE의 그날 행을 FOR UPDATE NOWAIT로 잠그고 CLOSE_YN을 본다. 이미 'Y'면 멈추고, 다른 세션이 마감 중이면 ORA-00054로 바로 멈춘다. 그래서 같은 날을 두 번 빼는 일이 생기지 않는다. 분류가 없는 신상품은 '미분류'로 채워 ③이 실패하지 않게 한다.

해설 ​

• COMMIT하는 것은 CREATE만이 아니다. TRUNCATE, GRANT, 실패한 DDL도 앞의 일을 COMMIT한다(교재 12화 §9). DBMS_STATS.GATHER_TABLE_STATS처럼 안에서 COMMIT하는 프로시저도 있다. 배치 중간에 이런 호출이 하나라도 있으면 그 배치는 한 트랜잭션이 아니다.
• DDL이 COMMIT하면 락도 풀린다. ①이 잠근 CF18 408행은 ②에서 이미 풀려서, 마감이 끝나기 전에 다른 세션이 그 행을 고칠 수 있었다. 한 트랜잭션이라고 믿고 짠 순서가 모두 흔들린다.
• 꼭 날짜별 테이블이 필요하다면 DDL을 트랜잭션 밖으로 뺀다. 마감을 시작하기 전에 만들어 두거나 별도 단계로 나눈다. 그래도 그 DDL이 실패했을 때 마감을 멈출지는 정해 두어야 한다.
• 이미 틀어진 12월 30일 재고는 세 번째 실행이 뜬 백업으로 되돌리면 안 된다. 그 백업은 이미 세 번 뺀 값이다. 상품별로 그날 판매의 두 배를 되돌려 맞추고, 매장 실사와 대조한다.
• 리포트 INSERT의 NOT NULL 위반 같은 데이터 오류는 앞으로도 생긴다. 실패를 막는 것보다 실패했을 때 남는 것이 없게 짜는 것이 먼저다(교재 13화 §8 반쯤 반영).

관련 교재 ​

• 12화 · DDL: 암시적 COMMIT과 줄 맨 앞
• 13화 · 반쯤 반영: 원자성의 대가