아무도 안 건드렸다는데 정산 화면이 10분 멈췄다

중급UPDATE한빛유통496 / 700

상황

5월 20일(월) 10:04에 정산팀 전체의 정산 등록 화면이 저장 버튼에서 멈췄다. 박팀장은 "배포한 사람 없대요." 하고 전했다. 화면은 10:14에 저절로 풀렸다. 지난 금요일 18:40에 개발팀이 '정산번호 중복 버그 수정' 핫픽스를 반영했다. 정산 번호를 MAX+1 대신 채번 테이블에서 받도록 바꾼 것이다. 10:04에는 정산 보정 배치가 정산 30건을 등록하고 있었다(배치는 30건을 다 등록한 뒤 COMMIT하며, 약 10분 걸린다). 강도현이 2019.03에 남긴 커밋 메모는 이렇다. "시퀀스는 쓰지 않음. 결번이 생긴다(2018 감사 지적)."

테이블과 인덱스

T_ORD_STLM 정산: 100,000행 · 640블록 · 정산 번호 STLM_SEQ는 100,000까지 쓰였다 · PK 없음 · 인덱스 없음
T_STLM_NUM 채번: 1행 · PK (NUM_TP) · 핫픽스 전에는 쓰지 않던 테이블
2018 감사 지적 원문: "정산번호 결번 발생 시 결번 사유를 확인할 수 있는 기록이 없음"
정산팀 확인: 정산 번호는 순서에 의미가 없는 식별 번호지만 겹치면 안 된다

원본 SQL

sql

-- PKG_ORD_STLM.REG_STLM — 정산 한 건 등록 (화면 · 보정 배치가 같이 부른다)
--   5월 17일 핫픽스 "정산번호 중복 버그 수정"
--   이전 판 (2019.03 강도현) : SELECT NVL(MAX(STLM_SEQ), 0) + 1 INTO v_no FROM T_ORD_STLM;
SELECT LAST_NO + 1 INTO v_no
  FROM T_STLM_NUM
 WHERE NUM_TP = 'STLM'
   FOR UPDATE;

UPDATE T_STLM_NUM SET LAST_NO = v_no WHERE NUM_TP = 'STLM';

INSERT INTO T_ORD_STLM (STLM_SEQ, ORD_SEQ, CUS_ID, STLM_DT, STLM_AMT)
VALUES (v_no, :ord_seq, :cus_id, SYSDATE, :amt);
-- COMMIT 은 부른 쪽이 한다 — 화면은 저장 버튼에서 · 보정 배치는 30 건을 다 등록한 뒤

실행계획 실측 · 교재 12화(복사본, 세 세션)

xplan

[세 사람 동시 — 핫픽스 판 · 연습 DB 복사본 (배치가 번호를 받고 약 3초 뒤 COMMIT 하도록 줄였다)]
시각     배치 (SID 53)                              화면 1 (SID 21)                     화면 2 (SID 216)
t+0.0    번호 받기 → 100001 · 등록을 이어 간다
t+0.1                                               번호 받기 → 기다림
t+0.1                                                                                   번호 받기 → 기다림
t+3.2    COMMIT                                     → 100002 (기다림 316 cs)            → 100003 (321 cs)

[V$LOCK — 기다리는 동안]
SID   TYPE  대상 / 이름표                       LMODE  REQUEST  BLOCK
53    TM    T_STLM_NUM                            3       0       0
53    TX    TX-00050011-00000338 (배치의 것)       6       0       1
21    TM    T_STLM_NUM                            3       0       0
21    TX    TX-00050011-00000338                  0       6       0
216   TM    T_STLM_NUM                            3       0       0
216   TX    TX-00050011-00000338                  0       6       0

[블로킹 트리 — V$SESSION (연습에서는 배치의 '일' 을 잠깐 쉬는 것으로 흉내 냈다)]
SID_TREE  EVENT                           SECS  BLOCKER
53        PL/SQL lock timer                  0
  21      enq: TX - row lock contention      2       53
  216     enq: TX - row lock contention      2       53

[이전 판 MAX+1 — 같은 세 사람]
  번호 하나 Buffers 568 (TABLE ACCESS FULL T_ORD_STLM) · 기다림 0 ~ 1 cs · 셋 다 100001 (겹침)
  MAX+1 에 PK 를 걸면 — 화면 둘이 TX 를 REQUEST 4 로 220 cs 기다린 뒤 ORA-00001

문제

시간표와 V$LOCK을 보고 누가 무엇을 잠가서 누구를 언제까지 막는지 설명하시오. 운영에서 화면이 10분 동안 멈춘 이유도 쓰시오.
번호를 주는 방법을 고치시오. 이전 판(MAX+1)으로 되돌리면 안 되는 이유와, 강도현이 시퀀스를 피했던 근거를 지금 어떻게 다룰지도 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- 번호는 업무 트랜잭션에서 떼어 낸다 — 시퀀스 (지금 쓰인 마지막 번호 다음부터)
CREATE SEQUENCE SQ_STLM_SEQ START WITH 100001 CACHE 20;

-- 겹침은 제약이 막는다 (시퀀스 밖에서 손으로 넣는 번호까지)
ALTER TABLE T_ORD_STLM ADD CONSTRAINT PK_T_ORD_STLM PRIMARY KEY (STLM_SEQ);

-- PKG_ORD_STLM.REG_STLM — 채번 테이블을 읽지도 잠그지도 않는다
INSERT INTO T_ORD_STLM (STLM_SEQ, ORD_SEQ, CUS_ID, STLM_DT, STLM_AMT)
VALUES (SQ_STLM_SEQ.NEXTVAL, :ord_seq, :cus_id, SYSDATE, :amt);

-- 결번 사유는 업무 규정에 적는다 — "취소 · 장애로 쓰이지 않은 번호는 결번" (감사 지적 원문: 결번 사유 기록)

개선 후 실행계획 실측 · 교재 12화(복사본, 세 세션)

xplan

[세 사람 동시 — 시퀀스 판 (교재 12화 [R4])]
  번호 하나 Buffers 0 (캐시를 채우는 20 번에 한 번 4) · 기다림 0 cs · 겹침 0
  번호 100001 · 100002 · 100003 — 배치가 ROLLBACK 하면 그 번호는 결번으로 남는다

모범 답안 서술

배치(SID 53)가 채번 테이블의 한 행을 FOR UPDATE로 잠갔다(TX LMODE 6, BLOCK 1). 화면 둘은 같은 행을 원하므로 배치의 트랜잭션을 REQUEST 6으로 기다린다. 이벤트는 enq: TX - row lock contention이고 blocking_session은 53이다. TM은 셋 다 3이라 서로 막지 않는다. 줄은 테이블이 아니라 앞사람의 트랜잭션 뒤에 선다. 행 락은 COMMIT할 때 풀린다. 번호를 받는 함수가 COMMIT하지 않으므로, 번호를 받은 쪽의 트랜잭션이 끝날 때까지 채번 행이 잠긴다. 보정 배치는 30건을 다 등록한 뒤에야 COMMIT하므로 약 10분이 걸리고, 그동안 번호를 원하는 모든 화면이 10분을 기다렸다. 번호 받기는 5블록짜리 일인데 기다림은 배치의 길이만큼 길어진다. 이전 판 MAX+1로 되돌리면 기다림은 없지만 번호가 겹친다. COMMIT 전의 100001은 다른 세션에 보이지 않고 SELECT는 아무것도 잠그지 않으므로, 셋이 같은 MAX를 읽고 셋 다 100001을 쓴다(번호 하나에 FULL 568블록). PK를 걸어도 겹치는 대신 TX 4로 기다린 뒤 ORA-00001이 난다. 그래서 시퀀스로 바꾼다. 번호 하나에 Buffers 0, 기다림 0, 겹침 0이다. 대신 ROLLBACK이나 캐시 소실로 결번이 생긴다. 2018 감사 지적 원문은 '결번 금지'가 아니라 '결번 사유를 확인할 수 있는 기록이 없음'이었고, 정산팀은 겹치지만 않으면 된다고 확인했다. 따라서 결번 사유를 업무 규정에 적으면 요구를 지킬 수 있다. 겹침은 PK로 막아 둔다(지금 T_ORD_STLM에는 PK가 없다).

해설

정말 결번이 금지라면(법정 연번이 요구되는 서류) 결론이 바뀐다. 그때는 줄을 짧게 한다. 번호를 트랜잭션 맨 끝에서 받거나, 채번과 등록만 하는 짧은 트랜잭션으로 떼어 낸다. 그래도 줄 자체는 남는다.
자율 트랜잭션 채번(PRAGMA AUTONOMOUS_TRANSACTION으로 채번 테이블을 고치고 바로 COMMIT)을 쓰면 기다림은 번호를 받는 순간뿐이다. 하지만 호출한 쪽이 ROLLBACK해도 번호는 이미 올라가 있으므로 결번이 생긴다는 점은 시퀀스와 같다(교재 12화 [F]).
PK를 걸기 전에 확인할 것이 있다. 운영에는 이전 판 MAX+1이 만든 겹친 번호가 이미 있을 수 있다('중복 버그'가 핫픽스의 이유였다). GROUP BY STLM_SEQ HAVING COUNT(*) > 1로 찾아 정리하지 않으면 PK 추가가 ORA-02437로 실패한다.
시퀀스에서 결번이 생기는 경우는 둘이다. 번호를 받은 트랜잭션이 ROLLBACK할 때와, 캐시가 사라질 때(공유 풀 비우기나 재시작, CACHE 20이면 최대 19개)다. NOCACHE로 두면 번호마다 딕셔너리를 고친다.
장애 첫 5분에는 블로킹 트리를 찍고 시각을 적는다(V$는 락이 풀리면 사라진다). 뿌리 세션을 죽여도 되감는 동안에는 락이 그대로 남는다. 그리고 '아무도 안 건드렸다'는 말을 들으면, 바뀐 것(diff)을 찾아 장애와 이어 붙이는 일부터 한다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 실측 · 교재 12화(복사본, 세 세션) ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 실측 · 교재 12화(복사본, 세 세션) ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황