매달 1일 오전 월 마감 확정을 하는 20분 동안 쇼핑몰 주문이 한 건도 들어오지 않는다

모범 답안 SQL ​

-- 월 마감 확정 — 테이블을 잠그지 않는다 · 고칠 행을 UPDATE 로 먼저 정하고 그 행만 잠근다
DECLARE
  TYPE t_seq IS TABLE OF T_ORD.ORD_SEQ%TYPE;
  v_seq t_seq;
BEGIN
  -- 1. 지난달 COMPLETE · 미정산 주문을 Y 로 바꾸며 잠근다 (조건은 여기서 한 번만 평가된다)
  UPDATE T_ORD SET STLM_YN = 'Y'
   WHERE ORD_DT >= :m_from AND ORD_DT < :m_to
     AND ORD_ST = 'COMPLETE' AND STLM_YN = 'N'
  RETURNING ORD_SEQ BULK COLLECT INTO v_seq;

  -- 2. 방금 Y 로 바꾼 바로 그 주문들로만 정산을 만든다 (그 뒤 COMPLETE 가 된 주문은 다음 달에)
  FORALL i IN 1 .. v_seq.COUNT
    INSERT INTO T_ORD_STLM (STLM_SEQ, ORD_SEQ, CUS_ID, STLM_DT, STLM_AMT)
    SELECT SQ_STLM_SEQ.NEXTVAL, ORD_SEQ, CUS_ID, SYSDATE, ORD_AMT
      FROM T_ORD
     WHERE ORD_SEQ = v_seq(i);

  COMMIT;            -- 회계팀 확인을 기다리지 않는다 · 잠근 2,512행도 1초 안에 풀린다
END;
-- 회계팀이 틀렸다고 하면: 그날 만든 정산을 지우고 STLM_YN 을 'N' 으로 되돌리는 취소 절차를 따로 돌린다

개선 후 실행계획 학습용 예시 · TM 호환성은 교재 12화 실측(실습/01 [D]) ​

[12월 1일 · 고친 절차 (학습용 예시)]
  UPDATE 2,512행 · INSERT 2,512행 · COMMIT · 합계 0.6초 · T_ORD 에 쥔 것은 TM 3 과 그 2,512행의 행 락뿐
  쇼핑몰 주문 등록 · 결제 완료     기다림 0
  회계팀 확인                      COMMIT 뒤 정산 테이블로 · 틀리면 취소 절차

모범 답안 서술 ​

LOCK TABLE T_ORD IN SHARE MODE는 T_ORD에 TM 4(S)를 쥔다. 주문 등록 INSERT와 결제 완료 UPDATE는 TM 3(RX)을 요청하는데, TM 4와 TM 3은 함께 쥘 수 없다(교재 12화의 호환성 매트릭스). 그래서 정산 담당이 COMMIT하는 10:23:17까지 쇼핑몰의 모든 DML이 enq: TM - contention으로 기다렸다(V$LOCK의 TM REQUEST 3이 76개, blocking_session 118). LOCK TABLE 자신도 진행 중이던 결제 2건을 0.8초 기다렸고, 그동안 들어온 요청은 그 뒤에 줄을 섰다. 마감에 필요한 행은 지난달 COMPLETE 주문 2,512행뿐이다. 그런데 테이블 전체를 잠갔으므로 이달 주문, 곧 마감과 상관없는 새 주문 등록까지 막았다. 20분이나 쥔 것은 대사와 회계팀 확인을 트랜잭션 안에서 기다렸기 때문이다. 2021년의 사고는 INSERT와 UPDATE가 같은 조건을 따로 두 번 평가해서 생겼다. 두 문장 사이에 결제가 끝나 COMPLETE가 된 주문은 INSERT 때는 없다가 UPDATE 때 끼어들어, 정산 없이 Y가 되었다. 테이블 잠금은 그 틈을 막았지만 너무 큰 망치였다. 고친 판은 순서를 바꿔 UPDATE를 먼저 한다. UPDATE가 조건을 한 번만 평가하며 고칠 행을 정하고 그 행들을 잠그고, RETURNING으로 받은 주문번호로만 정산을 만든다. 그 뒤에 COMPLETE가 된 주문은 Y가 되지 않으므로 다음 마감에 들어간다. 잠그는 것은 2,512행뿐이고 쇼핑몰의 TM 3과는 부딪치지 않는다. 회계팀 확인은 트랜잭션 밖으로 뺀다. 확정 즉시 COMMIT하고(0.6초), 확인 결과가 틀리면 그날 만든 정산을 지우고 STLM_YN을 'N'으로 되돌리는 취소 절차를 따로 둔다. 트랜잭션을 열어 둔 채 사람을 기다리지 않는다.

해설 ​

• LOCK TABLE … IN SHARE MODE는 읽기 전용 잠금이 아니다. 다른 세션의 조회는 막지 않지만 모든 DML을 막는다. 테이블 잠금이 필요하다고 느낄 때는 먼저 '정말 지켜야 하는 행이 어디까지인가'를 적어 본다. 오라클은 행 락을 테이블 락으로 올리지 않으므로(락 에스컬레이션 없음) 2,512행을 잠가도 부담이 없다.
• UPDATE를 먼저 하는 대신 SELECT … FOR UPDATE로 대상 행을 먼저 잠그고 ROWID를 모아 두는 방법도 효과가 같다. 순서를 INSERT 다음 UPDATE로 그대로 두고 두 문장에 같은 조건을 다시 쓰면 사고가 되살아난다.
• 지난달 COMPLETE 주문을 고치는 화면이 있다면 마감 0.6초 동안 그 행에서 기다릴 수 있다. 20분 동안 쇼핑몰 전체가 서던 것과 비교하면 받아들일 만하다.
• 매달 같은 시각에 주문 0건인 20분이 있었는데 아무도 장애로 보지 않았다. 원래 그런 줄 알았다는 말은 감시 지표가 없다는 뜻이다. 블로킹 트리를 1분마다 찍는 감시(교재 12화 포스트모템의 재발방지 3번)가 있었다면 첫 달에 드러났다.

관련 교재 ​

• 12화 · TM 락: 6모드와 호환성 매트릭스
• 12화 · 락 에스컬레이션이 없다