「등록 완료」가 떴는데 정산 목록에 그 정산이 없다

기초PLSQL한빛유통697 / 700

상황

정산 등록 화면은 저장 버튼을 누르면 프로시저 P_REG_STLM을 부른다. 이 프로시저는 정산을 넣고, 포인트 적립 프로시저 P_ADD_POINT를 부른 뒤 화면에 'OK'를 돌려준다. 화면은 'OK'를 받으면 커밋하고 「등록 완료」를 띄운다. 지난달 조민우가 "등록 완료가 떴는데 목록에 없는 정산이 있다"며 세 건을 가져왔다. 세 건 모두 탈퇴 처리 중인 고객의 정산이었다. 정산팀 규정은 "포인트 적립이 실패해도 정산은 등록하고, 실패한 적립은 다음 날 다시 적립한다"이다.

테이블과 인덱스

T_ORD_STLM 정산: PK_T_ORD_STLM (STLM_SEQ) · 번호는 시퀀스 SQ_STLM_SEQ
T_CUS_POINT 고객 포인트(이 문제용): 고객마다 한 행 · PK (CUS_ID) · POINT_BAL
탈퇴 처리 중인 고객은 포인트 행을 먼저 지운다(개인정보 파기 절차).
T_ERR_LOG 오류 기록(이 문제용): LOG_DT · PGM · KEY_VAL · MSG

원본 SQL

sql

PROCEDURE P_ADD_POINT(p_cus_id VARCHAR2, p_amt NUMBER) IS
  v_bal NUMBER;
BEGIN
  SELECT POINT_BAL INTO v_bal FROM T_CUS_POINT WHERE CUS_ID = p_cus_id FOR UPDATE;
  UPDATE T_CUS_POINT SET POINT_BAL = v_bal + ROUND(p_amt * 0.01) WHERE CUS_ID = p_cus_id;
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;                                    -- 포인트가 꼬이지 않게 되돌린다
    INSERT INTO T_ERR_LOG (LOG_DT, PGM, KEY_VAL, MSG)
    VALUES (SYSDATE, 'P_ADD_POINT', p_cus_id, SQLERRM);
END;

PROCEDURE P_REG_STLM(p_ord_seq NUMBER, p_cus_id VARCHAR2, p_amt NUMBER, o_rslt OUT VARCHAR2) IS
BEGIN
  INSERT INTO T_ORD_STLM (STLM_SEQ, ORD_SEQ, CUS_ID, STLM_DT, STLM_AMT)
  VALUES (SQ_STLM_SEQ.NEXTVAL, p_ord_seq, p_cus_id, SYSDATE, p_amt);
  P_ADD_POINT(p_cus_id, p_amt);
  o_rslt := 'OK';
END;

-- 화면 : CALL P_REG_STLM(…) → o_rslt = 'OK' 이면 COMMIT, 아니면 ROLLBACK

실행계획 학습용 예시

xplan

[T_ERR_LOG — 지난달]
  LOG_DT            PGM          KEY_VAL      MSG
  05/07 10:41:12    P_ADD_POINT  CUS_000777   ORA-01403: no data found
  05/14 15:02:40    P_ADD_POINT  CUS_001203   ORA-01403: no data found
  05/28 09:17:05    P_ADD_POINT  CUS_001876   ORA-01403: no data found

[정산 등록 화면 로그 — 같은 시각]
  P_REG_STLM 결과 'OK' → COMMIT → 「등록 완료」 (세 건 모두)

[T_ORD_STLM]
  세 주문의 정산 0건 · 그 시각에 받은 정산 번호 412,118 · 412,907 · 413,560은 결번

[시험 — 연습 DB, 포인트 행이 없는 고객으로 한 번]
  P_REG_STLM 직후 (화면 COMMIT 전)
    SELECT COUNT(*) FROM T_ORD_STLM WHERE ORD_SEQ = :ord   →  0
    SELECT COUNT(*) FROM T_ERR_LOG  WHERE KEY_VAL = :cus   →  1

문제

화면에 「등록 완료」가 떴는데 정산이 남지 않은 이유를 설명하시오.
정산팀 규정대로 동작하도록 프로시저를 고치시오. 예상하지 못한 에러는 어떻게 처리했는지도 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

PROCEDURE P_ADD_POINT(p_cus_id VARCHAR2, p_amt NUMBER) IS
  v_bal NUMBER;
BEGIN
  SAVEPOINT sp_point;                                -- 이 프로시저가 한 일의 시작점
  SELECT POINT_BAL INTO v_bal FROM T_CUS_POINT WHERE CUS_ID = p_cus_id FOR UPDATE;
  UPDATE T_CUS_POINT SET POINT_BAL = v_bal + ROUND(p_amt * 0.01) WHERE CUS_ID = p_cus_id;
EXCEPTION
  WHEN NO_DATA_FOUND THEN                            -- 아는 실패: 포인트 행이 없는 고객 (탈퇴 처리 중)
    ROLLBACK TO sp_point;                            -- 자기 몫만 되돌린다 · 부른 쪽의 정산 INSERT는 남는다
    INSERT INTO T_ERR_LOG (LOG_DT, PGM, KEY_VAL, MSG)
    VALUES (SYSDATE, 'P_ADD_POINT', p_cus_id, 'NO_POINT_ROW · 다음 날 재적립 대상 · 금액 ' || p_amt);
  WHEN OTHERS THEN                                   -- 모르는 실패: 자기 몫을 되돌리고 부른 쪽에 알린다
    ROLLBACK TO sp_point;
    RAISE;
END;

-- P_REG_STLM은 그대로 둔다 (모르는 실패면 예외가 화면까지 올라가고, 화면은 ROLLBACK한 뒤 「등록 실패」를 띄운다)

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

xplan

[시험 — 포인트 행이 없는 고객 (고친 판)]
  P_REG_STLM 직후 : T_ORD_STLM 1건 · T_ERR_LOG 1건 (재적립 대상) → 화면 COMMIT → 둘 다 남는다

[시험 — 포인트 잔액 CHECK 제약 위반을 흉내]
  P_REG_STLM → ORA-02290이 화면까지 올라온다 → 화면 ROLLBACK → 「등록 실패」 · 정산도 포인트도 남지 않는다

모범 답안 서술

P_ADD_POINT의 ROLLBACK은 프로시저 경계를 모른다. 프로시저 안에서 한 일만이 아니라 트랜잭션 전체, 곧 P_REG_STLM이 앞서 넣은 정산 INSERT까지 되돌린다. 탈퇴 처리 중인 고객은 포인트 행이 이미 지워져 있어 SELECT INTO가 NO_DATA_FOUND를 냈고, WHEN OTHERS가 이를 잡아 ROLLBACK한 뒤 오류 기록을 새 트랜잭션에 넣었다. 예외를 삼켰으니 P_REG_STLM은 실패를 모른 채 'OK'를 돌려주었고, 화면은 오류 기록 한 줄만 든 트랜잭션을 커밋하고 「등록 완료」를 띄웠다. 정산은 사라졌고 시퀀스 번호 세 개는 결번이 됐다. 시험에서 커밋 전에 정산 0건, 오류 기록 1건이 보인 것이 그 모습이다. 트랜잭션을 끝내는 COMMIT과 ROLLBACK은 맨 바깥인 화면이 정한다. 안쪽 프로시저는 자기 몫만 되돌린다. 그래서 P_ADD_POINT의 시작에 SAVEPOINT sp_point를 두고, 실패하면 ROLLBACK TO sp_point로 포인트 쪽 일만 되돌린다. 아는 실패(포인트 행이 없다)는 NO_DATA_FOUND로 따로 잡아 "다음 날 재적립 대상"을 금액과 함께 기록하고, 정산 INSERT는 남긴다. 화면이 커밋하면 정산과 재적립 기록이 함께 남는다. 정산팀 규정 그대로다. 예상하지 못한 에러는 WHEN OTHERS에서 자기 몫을 되돌린 뒤 RAISE로 다시 던진다. 예외가 화면까지 올라가면 화면은 ROLLBACK하고 「등록 실패」를 띄우므로 사용자가 실패를 안다. WHEN OTHERS로 모든 에러를 삼키고 성공처럼 보이게 하는 것이 이번 사고의 뿌리였다. 지난달의 세 건은 오류 기록의 시각과 화면 로그로 찾아 다시 등록한다.

해설

함정: "포인트가 꼬이지 않게"라는 주석의 뜻은 맞지만 도구가 틀렸다. ROLLBACK은 트랜잭션 단위라서 부른 쪽이 한 일까지 지운다. 불린 프로시저에서 쓸 수 있는 것은 ROLLBACK TO SAVEPOINT뿐이다.
같은 이유로 불린 프로시저 안의 COMMIT도 부른 쪽의 일을 함께 확정한다. 공통 프로시저는 COMMIT과 ROLLBACK을 하지 않고, 트랜잭션의 끝은 맨 바깥이 정한다(교재 13화 §8의 원자성).
오류 기록이 화면의 ROLLBACK과 상관없이 남아야 한다면 그 INSERT만 자율 트랜잭션 프로시저로 뺀다. 다만 정산이 등록됐는지와 오류 기록이 어긋날 수 있으니, 다음 날 재적립 작업은 정산이 실제로 있는 건만 집는다.
WHEN OTHERS THEN NULL이 행을 빠뜨린다면(교재 15화 §9), WHEN OTHERS THEN ROLLBACK은 남의 일까지 지운다. 둘 다 에러가 났다는 사실을 화면에서 지운 것이 공통점이다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황