정산 배치에 SERIALIZABLE을 넣은 뒤로 아침마다 ORA-08177로 실패한다

중급PLSQL한빛유통533 / 700

상황

한빛유통의 「일 정산」 배치는 매일 09:30에 어제 결제 완료된 주문을 정산 테이블에 적재하고 주문에 정산 표시를 한다. 지난달 대사에서 정산에 들어간 주문과 정산 표시가 된 주문이 3건 달랐다. 개발자는 "두 문장이 같은 시점을 보게 하면 된다"며 맨 앞에 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE을 넣었다. 그 뒤로 4월 평일 20일 가운데 13일 동안 배치가 ORA-08177로 실패해, 오과장이 손으로 다시 돌렸다. 다시 돌리면 대개 성공한다.

테이블과 인덱스

T_ORD 주문: 100,000행 · 896블록 · PK_T_ORD (ORD_SEQ) · 인덱스 IX_T_ORD_PAY_DT (PAY_DT) · 이 문제에서는 정산 표시 STLM_YN('N', 'Y')을 더했다 · 어제 결제 완료 주문은 하루 약 1,100건
T_ORD_STLM 정산: 100,000행 · 640블록 · STLM_SEQ는 시퀀스 SQ_STLM_SEQ로 매긴다
고객센터는 09:00부터 어제 주문의 취소와 반품을 처리한다(ORD_ST를 CANCEL로 바꾸고 바로 COMMIT, 09:30 ~ 09:31 사이 평균 3건). 늦게 도착한 결제 콜백도 어제 주문을 COMPLETE로 바꾼다.
이미 정산된 주문이 취소되면 다음 날 「정산 취소」 배치가 따로 처리한다.

원본 SQL

sql

PROCEDURE DAILY_STLM(p_dt DATE) IS
BEGIN
  SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;    -- 지난달 건수 불일치 뒤 추가

  INSERT INTO T_ORD_STLM (STLM_SEQ, ORD_SEQ, CUS_ID, STLM_DT, STLM_AMT)
  SELECT SQ_STLM_SEQ.NEXTVAL, ORD_SEQ, CUS_ID, p_dt, PAY_AMT
    FROM T_ORD
   WHERE PAY_DT >= p_dt AND PAY_DT < p_dt + 1
     AND ORD_ST = 'COMPLETE' AND STLM_YN = 'N';

  UPDATE T_ORD
     SET STLM_YN = 'Y'
   WHERE PAY_DT >= p_dt AND PAY_DT < p_dt + 1
     AND ORD_ST = 'COMPLETE' AND STLM_YN = 'N';

  COMMIT;
END;

실행계획 학습용 예시

xplan

[4월 17일 — 시간표]
09:30:00.02   배치 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE (읽기 시점 고정)
09:30:00.10   INSERT … SELECT → 1,104행 (주문 97,833 은 COMPLETE 로 보여 적재)
09:30:00.61                                고객센터 주문 97,833 취소 UPDATE ORD_ST = 'CANCEL' · COMMIT
09:30:01.22   UPDATE T_ORD … → ORA-08177: can't serialize access for this transaction
              → 배치 ROLLBACK · 09:52 오과장이 다시 돌려 성공

[INSERT … SELECT — ALLSTATS LAST]
---------------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name            | Starts | E-Rows | A-Rows |    A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT                       |                 |      1 |        |      0 | 00:00:00.08 |    1290 |
|   1 |  LOAD TABLE CONVENTIONAL               | T_ORD_STLM      |      1 |        |      0 | 00:00:00.08 |    1290 |
|   2 |   SEQUENCE                             | SQ_STLM_SEQ     |      1 |        |   1104 | 00:00:00.02 |     118 |
|*  3 |    TABLE ACCESS BY INDEX ROWID BATCHED | T_ORD           |      1 |   1080 |   1104 | 00:00:00.01 |     110 |
|*  4 |     INDEX RANGE SCAN                   | IX_T_ORD_PAY_DT |      1 |   1100 |   1180 | 00:00:00.01 |       5 |
---------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter(("ORD_ST"='COMPLETE' AND "STLM_YN"='N'))
   4 - access("PAY_DT">=:B1 AND "PAY_DT"<:B1+1)

[지난달 — SERIALIZABLE 을 넣기 전의 불일치 3건]
주문      INSERT … SELECT 때   UPDATE 때      결과
96,410    COMPLETE (적재)      CANCEL         정산에 들어갔는데 STLM_YN 'N' 으로 남음 (취소된 주문이 정산됐다)
96,522    PENDING (빠짐)       COMPLETE       정산에 없는데 STLM_YN 'Y' → 영영 정산되지 않음
96,587    PENDING (빠짐)       COMPLETE       (같은 모양)

[4월 · 평일 20일]  실패 13일 · 다시 돌린 횟수 17번 · 실패 때마다 걸린 행은 배치가 시작한 뒤 고객센터나 결제 콜백이 고친 어제 주문

문제

지난달의 불일치 3건과 4월의 ORA-08177이 각각 어떻게 생겼는지 격리 수준의 차이로 설명하시오.
기다리기는 하되 실패하지 않고, 적재와 정산 표시가 늘 같은 주문을 가리키도록 배치를 고치시오. "ORA-08177이 나면 자동으로 다시 돌리자"는 의견에도 답하시오.

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

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

모범답안

모범 답안 SQL

sql

PROCEDURE DAILY_STLM(p_dt DATE) IS
  TYPE t_num IS TABLE OF NUMBER;
  TYPE t_cus IS TABLE OF T_ORD.CUS_ID%TYPE;
  v_seq t_num;
  v_cus t_cus;
  v_amt t_num;
BEGIN
  -- 격리 수준은 기본(READ COMMITTED) 그대로 — 대상은 UPDATE 한 문장이 정하고 잠근다
  UPDATE T_ORD
     SET STLM_YN = 'Y'
   WHERE PAY_DT >= p_dt AND PAY_DT < p_dt + 1
     AND ORD_ST = 'COMPLETE' AND STLM_YN = 'N'
  RETURNING ORD_SEQ, CUS_ID, PAY_AMT BULK COLLECT INTO v_seq, v_cus, v_amt;

  -- UPDATE 가 고른 목록 그대로 적재 — 적재와 정산 표시가 같은 주문을 가리킨다
  FORALL i IN 1 .. v_seq.COUNT
    INSERT INTO T_ORD_STLM (STLM_SEQ, ORD_SEQ, CUS_ID, STLM_DT, STLM_AMT)
    VALUES (SQ_STLM_SEQ.NEXTVAL, v_seq(i), v_cus(i), p_dt, v_amt(i));

  COMMIT;
END;

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

xplan

[고친 뒤 — 같은 4월 17일 (학습용 예시)]
09:30:00.02   UPDATE T_ORD … RETURNING → 1,104행에 STLM_YN 'Y' · 1,104행 잠금 · 목록을 배열로
09:30:00.61                                고객센터 주문 97,833 취소 UPDATE → 기다림 (TX 6)
09:30:00.70   FORALL INSERT T_ORD_STLM 1,104행 · COMMIT
09:30:00.70                                → 고객센터 진행 · 97,833 은 정산된 주문이라 다음 날 「정산 취소」 배치가 처리
  정산 1,104건 = 정산 표시 1,104건 · ORA-08177 0 · 고객센터 기다림 최대 약 0.1초
  4월과 같은 조건이면 실패 13일 → 0일

모범 답안 서술

지난달의 불일치는 기본 격리 수준인 READ COMMITTED에서 생겼다. READ COMMITTED에서 읽기 일관성은 문장 단위라, INSERT … SELECT와 UPDATE가 각자 시작한 순간의 T_ORD를 본다. 두 문장 사이(약 1초)에 고객센터가 96,410을 취소하자, INSERT 때는 COMPLETE라 적재됐는데 UPDATE 때는 CANCEL이라 표시되지 않았다. 늦은 결제 콜백이 96,522와 96,587을 COMPLETE로 바꾸자 그 반대가 생겨 정산 없이 'Y'만 찍혔다. 두 문장이 같은 WHERE를 써도 같은 행을 가리킨다는 보장이 없다. SERIALIZABLE은 트랜잭션 전체가 시작 시점(09:30:00.02)의 데이터를 보게 하므로 두 문장이 같은 대상을 본다. 대신 오라클은 그 시점 뒤에 다른 트랜잭션이 고치고 COMMIT한 행을 이 트랜잭션이 고치려 하면, 기다렸다가 새 값을 덮어쓰는 대신 ORA-08177로 문장을 거부한다. 주문 97,833은 시작 시점에는 COMPLETE였지만 09:30:00.61에 고객센터가 취소하고 COMMIT했으므로, UPDATE가 그 행에 닿자 ORA-08177이 났다. 09:30 무렵에는 어제 주문을 고치는 취소와 콜백이 평균 3건 있으므로 20일 가운데 13일이 실패했다. 고친 판은 격리 수준을 READ COMMITTED로 되돌리고, 대상을 정하는 일과 잠그는 일을 UPDATE 한 문장에 맡긴다. UPDATE가 고친 1,104행의 ORD_SEQ, CUS_ID, PAY_AMT를 RETURNING … BULK COLLECT로 받아 그 목록 그대로 FORALL로 적재하므로, 적재와 정산 표시가 늘 같은 주문을 가리킨다. UPDATE가 잠근 행을 고객센터가 고치려 하면 TX 6으로 배치의 COMMIT까지 약 0.1초 기다리고, 그 취소는 정산된 주문의 취소로 다음 날 정산 취소 배치가 처리한다. UPDATE가 잠그기 직전에 다른 세션이 취소한 주문은, 오라클이 문장을 다시 시작해 현재 값으로 조건을 다시 보므로 대상에서 빠진다. 배치는 기다릴 뿐 실패하지 않는다. 4월과 같은 조건이면 실패는 13일 → 0일이 된다. "ORA-08177이면 자동으로 다시 돌리자"는 의견은 이 배치에는 맞지 않는다. 09:30 무렵에는 충돌이 늘 있으므로 다시 돌려도 또 실패할 수 있고, 실패와 재시도를 몇 번 거치는 동안 정산 시각이 밀린다. SERIALIZABLE의 재시도는 충돌이 드문 짧은 트랜잭션에 맞는다.

해설

오라클의 SERIALIZABLE에서는 나중에 고치려는 쪽이 진다. 다른 트랜잭션이 시작 뒤에 고친 행을 만나면 기다렸다 덮어쓰지 않고 ORA-08177을 낸다. 읽기만 하는 트랜잭션이라면 이 에러는 나지 않으므로, 한 시점에서 읽기만 할 때는 READ ONLY가 더 맞다.
문서는 SERIALIZABLE 트랜잭션이 고치는 테이블의 INITRANS를 3 이상으로 두라고 권한다. 블록의 ITL 정보로 충돌을 판단하므로, 슬롯이 모자라면 실제 충돌이 없어도 ORA-08177이 날 수 있다.
표시 칸을 먼저 'P'로 바꿔 대상을 찍고, 그 행만 적재한 뒤 'Y'로 바꾸는 방법도 같은 원리다. 대상이 수십만 건이라 배열에 담기 부담스러우면 이쪽을 쓴다.
배치가 시작한 뒤에 COMPLETE가 된 어제 주문은 적재도 표시도 되지 않고 'N'으로 남는다. 어제 하루가 아니라 최근 며칠의 'N'을 대상으로 잡으면 다음 회차가 줍는다. 불일치를 없애는 일과 늦게 온 주문을 빠뜨리지 않는 일은 따로 챙겨야 한다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황