상품 20종 제한 트리거가 있는데 37종짜리 주문이 들어와 있다

중급DDL버거693 / 700

상황

버거 본사는 2013년 4월부터 주문 한 건에 상품을 20종까지만 받는다(주방 화면 한 장에 20줄이 들어간다). POS가 상품 줄을 한 줄씩 넣던 때 만든 트리거 TRG_ORD_ITEM_MAX가 줄을 넣기 전에 그 주문의 줄 수를 센다. 2013년 12월 POS 중계 서버를 들이면서 상품 줄은 중계 테이블에서 1분마다 INSERT … SELECT로 들어오게 됐다. 첫날 중계 적재가 ORA-04091로 실패하자 개발자가 트리거에 PRAGMA AUTONOMOUS_TRANSACTION 한 줄을 붙였고, 에러가 사라졌다. 오늘 감사팀이 37종짜리 주문을 찾아 왔다.

테이블과 인덱스

ORD_ITEM 주문상품: 2,472,717행 · 55,265블록 · ORD_ITEM_PK (ORD_NO, ITEM_ID)(리프 약 6,600블록) · 주문 1,000,000건, 주문당 평균 2.5종
POS_RELAY_ITEM 중계 테이블(이 문제용): 중계 서버가 결제가 끝난 주문의 상품 줄을 모아 1분마다 회차(RELAY_NO)를 매긴다 · 하루 1,440회차 · 약 3,390줄
중계 적재는 한 회차를 한 문장으로 넣고 바로 커밋한다. 한 주문의 줄은 늘 한 회차에 함께 온다.
ORD_NO는 NUMBER다.

원본 SQL

sql

CREATE OR REPLACE TRIGGER TRG_ORD_ITEM_MAX
BEFORE INSERT ON ORD_ITEM
FOR EACH ROW
DECLARE
  PRAGMA AUTONOMOUS_TRANSACTION;               -- 2013.12.02 중계 적재의 ORA-04091 때문에 붙였다
  v_cnt PLS_INTEGER;
BEGIN
  SELECT COUNT(*) INTO v_cnt FROM ORD_ITEM WHERE ORD_NO = :NEW.ORD_NO;
  IF v_cnt >= 20 THEN
    RAISE_APPLICATION_ERROR(-20021, '주문 ' || :NEW.ORD_NO || '의 상품이 20종을 넘는다');
  END IF;
END;
/

-- POS 중계 적재 (1분마다 한 회차)
INSERT INTO ORD_ITEM (ORD_NO, ITEM_ID, ORD_ITEM_QTY, ORD_DT, ORD_HMS)
SELECT ORD_NO, ITEM_ID, ORD_ITEM_QTY, ORD_DT, ORD_HMS
  FROM POS_RELAY_ITEM
 WHERE RELAY_NO = :relay_no;
COMMIT;

실행계획 학습용 예시

xplan

[2013-12-02 개발자 메모]
  중계 적재 실패 → ORA-04091: table DEV.ORD_ITEM is mutating, trigger/function may not see it
  트리거에 PRAGMA AUTONOMOUS_TRANSACTION 한 줄 → 에러 사라짐

[오늘 — 감사팀 점검]
  상품이 20종을 넘는 주문 23건 · 모두 2013-12-02 이후 · 가장 큰 주문 37종

[시험 DB — 21종짜리 주문 한 건을 중계 테이블에 넣고 같은 적재]
  21 rows created (에러 없음)
  트리거가 센 v_cnt (디버그 출력) : 21번 모두 0

[TKPROF — 하루치 중계 적재 (INSERT … SELECT 1,440번 · 3,391줄)]
SELECT COUNT(*) FROM ORD_ITEM WHERE ORD_NO = :B1          (dep=1 · 자율 트랜잭션 안)

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute   3391      0.19       0.22          0          0          0           0
Fetch     3391      0.07       0.09          0      10173          0        3391

문제

줄 수를 세는 트리거가 있는데 20종 넘는 주문이 들어온 이유를 설명하시오. 첫날 ORA-04091이 왜 났는지도 쓰시오.
20종 규정을 지키면서 중계 적재가 ORA-04091 없이 돌도록 트리거를 고치시오. 이미 들어온 위반 주문을 찾는 SQL도 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

CREATE OR REPLACE TRIGGER TRG_ORD_ITEM_MAX
FOR INSERT ON ORD_ITEM
COMPOUND TRIGGER
  TYPE t_set IS TABLE OF BOOLEAN INDEX BY PLS_INTEGER;   -- ORD_NO를 열쇠로 (같은 주문은 한 번만)
  g_ord t_set;

  AFTER EACH ROW IS
  BEGIN
    g_ord(:NEW.ORD_NO) := TRUE;                          -- 행 구간: SQL 없이 주문 번호만 적어 둔다
  END AFTER EACH ROW;

  AFTER STATEMENT IS                                     -- 문장이 다 끝난 뒤: 자기 테이블을 읽을 수 있다
    k     PLS_INTEGER := g_ord.FIRST;
    v_cnt PLS_INTEGER;
  BEGIN
    WHILE k IS NOT NULL LOOP
      SELECT COUNT(*) INTO v_cnt FROM ORD_ITEM WHERE ORD_NO = k;   -- 같은 트랜잭션이 방금 넣은 줄도 센다
      IF v_cnt > 20 THEN
        RAISE_APPLICATION_ERROR(-20021, '주문 ' || k || '의 상품이 ' || v_cnt || '종 (20종 초과)');
      END IF;
      k := g_ord.NEXT(k);
    END LOOP;
  END AFTER STATEMENT;
END;
/

-- 이미 들어온 위반 주문 (감사팀 전달용)
SELECT ORD_NO, COUNT(*) AS ITEM_CNT
  FROM ORD_ITEM
 GROUP BY ORD_NO
HAVING COUNT(*) > 20;

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

xplan

[시험 DB — 같은 21종 주문]
  ORA-20021: 주문 1000412의 상품이 21종 (20종 초과) → 그 회차의 INSERT 전체가 되돌려진다

[TKPROF — 하루치 중계 적재 (고친 트리거)]
  SELECT COUNT(*) FROM ORD_ITEM WHERE ORD_NO = :B1   Execute 1,372 (회차마다 그 회차의 주문 수만큼) · query 4,116 · 자율 트랜잭션 0

[위반 주문 찾기]
----------------------------------------------------------------------------------------------
| Id  | Operation              | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |             |      1 |        |     23 |00:00:00.61 |    6618 |
|*  1 |  FILTER                |             |      1 |        |     23 |00:00:00.61 |    6618 |
|   2 |   HASH GROUP BY        |             |      1 |   1000K|   1000K|00:00:00.55 |    6618 |
|   3 |    INDEX FAST FULL SCAN| ORD_ITEM_PK |      1 |   2472K|   2472K|00:00:00.20 |    6618 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(COUNT(*)>20)

모범 답안 서술

첫날의 ORA-04091은 행 트리거가 여러 행을 넣는 문장 도중에 자기 테이블(ORD_ITEM)을 읽어서 났다. 행 트리거는 문장이 반쯤 진행된 상태에서 불리므로, 그 문장이 바꾸고 있는 테이블을 읽으면 반쯤 바뀐 모습을 보게 된다. 오라클은 이것을 변경 중인 테이블(mutating)로 막는다. POS가 한 줄씩 넣던 INSERT … VALUES는 한 행짜리 문장이라 예외로 허용되어 2013년 4월부터 문제없이 돌았고, 중계 적재의 INSERT … SELECT에서 처음 드러났다. PRAGMA AUTONOMOUS_TRANSACTION은 에러를 없앴지만 검사도 없앴다. 자율 트랜잭션은 부른 쪽과 따로 시작하는 트랜잭션이라 부른 쪽이 아직 커밋하지 않은 행을 보지 못한다. 트리거가 세는 순간 그 주문의 줄은 모두 같은 중계 적재 문장 안에서 커밋 전이므로 트리거는 늘 0을 센다. 시험에서 21번 모두 0이 나온 것이 그 증거다. 그래서 2013-12-02 이후 20종을 넘는 주문 23건이 들어왔다. ORA-04091이 사라진 것은 문제가 풀려서가 아니라 검사가 아무것도 보지 않게 되어서다. 트리거는 복합 트리거로 바꾼다. 행 구간(AFTER EACH ROW)에서는 SQL 없이 그 문장이 건드린 주문 번호만 배열에 적고, 문장 구간(AFTER STATEMENT)에서 주문마다 한 번 센다. 문장이 다 끝난 뒤라 자기 테이블을 읽을 수 있고, 같은 트랜잭션이 방금 넣은 줄도 보인다. 규정을 넘으면 에러를 던져 그 회차의 INSERT를 되돌린다. 하루에 세는 횟수는 줄마다 3,391번 → 주문마다 1,372번으로 줄고, 자율 트랜잭션은 없어진다. 시험에서 21종 주문은 ORA-20021로 막혔다. 이미 들어온 위반 주문은 GROUP BY ORD_NO HAVING COUNT(*) > 20으로 찾는다. 인덱스 ORD_ITEM_PK만 FAST FULL SCAN으로 읽어 6,618블록에 23건이 나온다. 이 목록을 감사팀에 넘겨 영수증과 대조한다.

해설

함정: 자율 트랜잭션으로 ORA-04091을 피하는 처방은 인터넷에 흔하다. 에러는 사라지지만 트리거가 보는 데이터가 "지금 넣고 있는 것"에서 "남이 이미 커밋한 것"으로 바뀐다. 자기 테이블을 검사하는 트리거에는 맞지 않는다.
에러를 던지면 그 회차(주문 몇 건)의 문장 전체가 되돌려진다. 한 주문 때문에 다른 주문까지 막히지 않게 하려면, 중계 적재가 먼저 HAVING COUNT(*) > 20인 주문을 반송함으로 빼고 나머지를 넣는다. 트리거는 마지막 방어선으로 둔다.
행 트리거에 :NEW 값으로 세지 않고 문장 구간에서 세는 것이 이 문제의 요점이다. 복합 트리거의 배열은 문장마다 새로 시작하므로 세션이 끝날 때까지 남는 패키지 변수와 달리 다른 문장의 주문이 섞이지 않는다.
규정을 확실히 지키려면 제약으로 거는 방법도 있다. 주문 헤더에 상품 종수 칸을 두고 CHECK (ITEM_CNT <= 20)을 걸면 동시에 들어오는 줄까지 막지만, 줄을 넣을 때마다 헤더를 고쳐야 해서 그 행에 락이 몰린다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황