상품평가 한 건 등록에 SQL이 두 번 오가고, 신상품 출시 시각에는 평가 등록이 ORA-00001로 실패한다

실전PLSQL한빛유통456 / 700

상황

고객이 상품평가를 등록하면 화면은 평가(T_ITM_EVL)를 넣은 뒤 상품별 평가 요약(T_ITM_EVL_SUM)을 고치는 프로시저 ADD_EVL_SUM을 부르고, 둘을 한 번에 커밋한다. 평가는 하루 1만 건쯤 들어온다. 지난 화요일 10:00에 신상품 8개를 출시했는데, 10:00 ~ 10:02 사이에 평가 등록 3건이 ORA-00001로 실패해 고객 화면에 '등록 실패'가 떴다. 개발팀은 MERGE로 바꾸면 해결된다고 한다. 박팀장은 방법별 비교와 근거를 요청했다.

테이블과 인덱스

T_ITM_EVL 상품평가: 5,000행 · 48블록 · PK_T_ITM_EVL (ITM_ID, EVL_LST_NO)
T_ITM_EVL_SUM(상품별 평가 요약, 이 문제를 위해 둔 테이블): 상품 100개 + 신상품 8개 · PK (ITM_ID) · EVL_CNT, EVL_PT_SUM, LAST_EVL_DT · 상품의 첫 평가가 들어올 때 행이 생긴다
M_ITM 상품: 100행(출시 뒤 108행) · PK_M_ITM
하루 평가 1만 건 가운데 상품의 첫 평가는 그날 출시한 상품 수만큼이다(지난 화요일 8건).

원본 SQL

sql

PROCEDURE ADD_EVL_SUM(p_itm VARCHAR2, p_pt NUMBER) IS
  v_cnt NUMBER;
BEGIN
  SELECT COUNT(*) INTO v_cnt
    FROM T_ITM_EVL_SUM
   WHERE ITM_ID = p_itm;                         -- 요약 행이 있나
  IF v_cnt = 0 THEN
    INSERT INTO T_ITM_EVL_SUM (ITM_ID, EVL_CNT, EVL_PT_SUM, LAST_EVL_DT)
    VALUES (p_itm, 1, p_pt, SYSDATE);
  ELSE
    UPDATE T_ITM_EVL_SUM
       SET EVL_CNT = EVL_CNT + 1, EVL_PT_SUM = EVL_PT_SUM + p_pt, LAST_EVL_DT = SYSDATE
     WHERE ITM_ID = p_itm;
  END IF;
END;

실행계획 학습용 예시

xplan

[TKPROF 요약 — 지난 화요일 평가 10,000건 (학습용 예시)]
  SQL                                                          Execute     Rows    query  current
  SELECT COUNT(*) FROM T_ITM_EVL_SUM WHERE ITM_ID = :B1         10,000   10,000   10,000        0
  UPDATE T_ITM_EVL_SUM SET EVL_CNT = EVL_CNT + 1, …              9,989    9,989    9,989   10,312
  INSERT INTO T_ITM_EVL_SUM (…) VALUES (…)                          11        8       14       57
  합계  SQL 실행 20,000 · 논리 읽기 약 40,400 · redo 약 3.1 MB

[오류 로그 — 10:00:07 ~ 10:01:52]
  ORA-00001: unique constraint (SQLP.PK_T_ITM_EVL_SUM) violated   3건
    신상품 3개에서, 같은 상품의 첫 평가가 두 세션에서 거의 동시에 들어왔다
  평가 등록 화면 '등록 실패' 3건 (평가 INSERT까지 함께 롤백)

문제

지금 프로시저가 평가 한 건마다 하는 일과, 신상품 출시 시각에 ORA-00001이 난 이유를 설명하시오.
(가) UPDATE를 먼저 하고 0건이면 INSERT, (나) INSERT를 먼저 하고 중복이면 UPDATE, (다) MERGE … USING DUAL을 SQL 실행 횟수, 읽는 양과 redo, 동시 등록에서의 안전성으로 비교하시오.
고른 방법으로 프로시저를 다시 작성하시오.

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

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

모범답안

모범 답안 SQL

sql

PROCEDURE ADD_EVL_SUM(p_itm VARCHAR2, p_pt NUMBER) IS
BEGIN
  -- (가) UPDATE 먼저 · 요약 행이 있는 99.9%는 여기서 끝난다
  UPDATE T_ITM_EVL_SUM
     SET EVL_CNT = EVL_CNT + 1, EVL_PT_SUM = EVL_PT_SUM + p_pt, LAST_EVL_DT = SYSDATE
   WHERE ITM_ID = p_itm;

  IF SQL%ROWCOUNT = 0 THEN                   -- 첫 평가일 때만
    BEGIN
      INSERT INTO T_ITM_EVL_SUM (ITM_ID, EVL_CNT, EVL_PT_SUM, LAST_EVL_DT)
      VALUES (p_itm, 1, p_pt, SYSDATE);
    EXCEPTION
      WHEN DUP_VAL_ON_INDEX THEN             -- 같은 순간 다른 세션이 먼저 넣었다 → 그 행을 고친다
        UPDATE T_ITM_EVL_SUM
           SET EVL_CNT = EVL_CNT + 1, EVL_PT_SUM = EVL_PT_SUM + p_pt, LAST_EVL_DT = SYSDATE
         WHERE ITM_ID = p_itm;
    END;
  END IF;
END;

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

xplan

[같은 10,000건 · 방법별 (학습용 예시)]
  방법                               SQL 실행    논리 읽기    redo       동시 첫 평가 3건
  지금 (COUNT → INSERT/UPDATE)         20,000     약 40,400   약 3.1 MB  ORA-00001 3건 (등록 실패)
  (가) UPDATE 먼저, 0건이면 INSERT      10,014     약 20,400   약 3.1 MB  중복을 잡아 UPDATE로 다시 처리 (실패 0)
  (나) INSERT 먼저, 중복이면 UPDATE     19,992     약 60,300   약 9.0 MB  UPDATE로 다시 처리 · 실패한 INSERT 9,992번
  (다) MERGE … USING DUAL               10,000     약 20,400   약 3.1 MB  예외를 잡지 않으면 ORA-00001 3건

[(가)의 TKPROF 요약]
  UPDATE T_ITM_EVL_SUM …   Execute 10,003 (첫 시도 10,000 + 중복 뒤 다시 3) · Rows 9,992
  INSERT INTO T_ITM_EVL_SUM …   Execute 11 · Rows 8 (3건은 DUP_VAL_ON_INDEX로 잡힘)

모범 답안 서술

지금 프로시저는 평가 한 건마다 SQL을 두 번 보낸다. 먼저 SELECT COUNT(*)로 요약 행이 있는지 묻고, 있으면 UPDATE, 없으면 INSERT한다. 평가 1만 건 가운데 요약 행이 이미 있는 경우가 9,989건이므로 COUNT()는 거의 언제나 '있다'를 알려 줄 뿐이고, 그 확인은 UPDATE가 스스로 할 수 있다(처리 건수가 0이면 없는 것이다). 그래서 SQL 실행이 20,000번이다. ORA-00001은 확인과 INSERT 사이의 틈에서 났다. 두 세션이 같은 신상품의 첫 평가를 거의 같은 순간에 넣으면, 상대의 INSERT는 아직 커밋되지 않아 보이지 않으므로 둘 다 COUNT()에서 0건을 보고 INSERT로 간다. 뒤에 온 세션은 PK 인덱스에서 앞 세션의 커밋을 기다렸다가 ORA-00001을 받는다. 프로시저가 예외를 처리하지 않으니 평가 INSERT까지 함께 롤백되어 고객 화면에 '등록 실패'가 떴다. (가) UPDATE를 먼저 하면 요약 행이 있는 9,989건은 UPDATE 한 번으로 끝나서 SQL 실행이 20,000 → 10,014, 논리 읽기가 약 40,400 → 20,400으로 준다. 처리 건수가 0일 때만 INSERT하고, 그 INSERT가 동시 등록 때문에 DUP_VAL_ON_INDEX로 실패하면 그 사이 생긴 행을 UPDATE로 다시 고친다. 세 번의 동시 첫 평가도 모두 등록된다. (나) INSERT를 먼저 하면 거의 모든 평가에서 INSERT가 실패한다(9,992번). 실패한 INSERT는 테이블에 행을 넣고 인덱스에서 중복을 발견한 뒤 되돌리므로 redo와 undo를 남기고 예외 처리 비용까지 든다. SQL 실행은 19,992번, redo는 약 9MB로 셋 가운데 가장 비싸다. 새 행이 대부분인 적재라면 맞는 순서지만 여기서는 반대다. (다) MERGE는 한 문장이라 실행 횟수와 읽는 양이 (가)와 비슷하다. 하지만 MERGE도 '없다'를 판정한 뒤 넣으므로, 두 세션이 동시에 '없다'고 보면 똑같이 ORA-00001이 난다. MERGE로 바꾸면 해결된다는 말은 틀렸다. MERGE를 쓰더라도 DUP_VAL_ON_INDEX를 잡아 다시 처리해야 한다. 그래서 (가)를 고른다. 요약 행이 있는 경우가 99.9%라 UPDATE 먼저가 실행 횟수가 가장 적고, 동시 첫 평가는 예외 처리로 안전하게 다시 처리한다.

해설

틈을 아예 없애는 답: 상품을 등록할 때(M_ITM INSERT) 요약 행을 0으로 같이 만들어 두면, 평가 등록은 언제나 UPDATE 한 문장이다. INSERT 분기도, 동시 첫 평가 문제도 사라진다.
잠금 줄 서기: 인기 상품 하나에 평가가 몰리면 모든 세션이 같은 요약 행을 UPDATE하므로 커밋 전까지 줄을 선다(enq: TX - row lock contention). 평가 INSERT와 요약 UPDATE를 한 트랜잭션에 두는 한 피할 수 없으니 평가 등록 트랜잭션을 짧게 유지한다. 몰림이 심하면 요약을 실시간으로 고치지 않고 몇 분마다 집계하는 설계도 견줘 본다(교재 12화).
요약 테이블이 꼭 필요한가: 상품당 평가가 50건 안팎이면 PK_T_ITM_EVL로 그 상품의 평가를 바로 묶어도 몇 블록이다. 요약 테이블은 화면 조회가 아주 잦거나 평가가 아주 많을 때 값을 한다.
예외 처리에서 WHEN OTHERS로 모든 오류를 삼키면 안 된다. 다시 처리할 수 있는 DUP_VAL_ON_INDEX만 잡고 나머지는 그대로 올려 보낸다(교재 15화).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황