정산 금액 저장 버튼 하나가 정산 시스템 화면 전체를 멈춘다

기초SELECT한빛유통521 / 700

상황

정산팀 조민우는 「정산 금액 정정」 화면에서 정산 한 건의 금액을 고친다. 화면은 저장할 때 그 행을 FOR UPDATE로 잠그고, 금액이 그사이 바뀌지 않았는지 확인한 뒤 고친다. 월요일 10:30에 저장 버튼이 응답하지 않아 조민우와 동료들이 몇 번씩 다시 눌렀다. 10:34에는 정산 조회 화면과 로그인까지 멈췄고, 10:36에 매주 도는 「정산 재계산」 배치가 끝나자 모두 풀렸다.

테이블과 인덱스

T_ORD_STLM 정산: 100,000행 · 640블록 · PK_T_ORD_STLM (STLM_SEQ)(12화 재발방지로 추가)
「정산 재계산」 배치: 매주 월요일 10:30에 지난주 정산 약 2만 건의 금액을 한 문장 UPDATE로 다시 계산하고 COMMIT한다(약 6분).
정산 시스템 WAS의 커넥션 풀은 40개다. 요청은 커넥션을 얻은 뒤 응답을 돌려줄 때 반납한다. 웹 화면은 응답이 오지 않아도 버튼을 다시 누를 수 있다.

원본 SQL

sql

-- 「정산 금액 정정」 저장
SELECT STLM_AMT
  FROM T_ORD_STLM
 WHERE STLM_SEQ = :stlm_seq
   FOR UPDATE;                               -- 화면에서 읽은 금액과 같은지 확인한다

UPDATE T_ORD_STLM
   SET STLM_AMT = :new_amt
 WHERE STLM_SEQ = :stlm_seq;
COMMIT;

실행계획 학습용 예시

xplan

[시간표 — 월요일]
시각         누가                         무엇을
10:30:00     정산 재계산 배치 (SID 53)     UPDATE 시작 · 지난주 정산 20,114행을 차례로 잠근다 (COMMIT 은 문장이 끝난 뒤)
10:30:40     조민우                        정산 96,211 (지난주 건) 저장 → FOR UPDATE 에서 기다림
10:30:55     조민우                        응답이 없어 다시 누름 → 새 요청 · 새 커넥션 · 같은 행에서 기다림
10:31 ~ 34   동료 다섯 명                   저장 · 다시 누르기 → 저장 요청 38개가 커넥션 38개를 쥔 채 기다림
10:34:10     정산 WAS                      커넥션 풀 40개 모두 사용 → 조회 화면 · 로그인도 커넥션을 기다림
10:36:12     배치                          COMMIT → 기다리던 요청 38개가 차례로 진행

[V$SESSION — 10:34:30 · 정산 WAS 세션]
EVENT                            세션 수   BLOCKING_SESSION   하고 있는 문장
enq: TX - row lock contention       38     53                SELECT STLM_AMT FROM T_ORD_STLM WHERE STLM_SEQ = :B1 FOR UPDATE
SQL*Net message from client          2                        (방금 응답을 돌려준 조회 화면)
(SID 53 — 배치 · db file scattered read)

[V$LOCK — 같은 시각 · 기다리는 세션 하나]
SID   TYPE  대상                    LMODE  REQUEST  BLOCK
53    TX    배치의 트랜잭션            6      0        1
118   TM    T_ORD_STLM                3      0        0
118   TX    배치의 트랜잭션            0      6        0

[WAS 로그]  10:34:10 ~ 10:36:12 커넥션 대기 타임아웃 412건 (정산 조회 · 로그인 · 정산 정정)

문제

시간표와 V$SESSION을 근거로 정산 몇 건의 저장 요청이 어떻게 정산 시스템 화면 전체를 멈추게 했는지 설명하시오.
저장이 한없이 기다리지 않도록 SQL을 고치고, 화면이 그 에러를 어떻게 처리할지 쓰시오. NOWAIT와 WAIT n 중 무엇을 고를지도 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- 「정산 금액 정정」 저장 — 3초까지만 기다린다
--   ORA-30006 이면 "다른 작업이 이 정산을 처리 중입니다. 잠시 뒤 다시 저장하세요." · 커넥션은 바로 돌려준다
SELECT STLM_AMT
  FROM T_ORD_STLM
 WHERE STLM_SEQ = :stlm_seq
   FOR UPDATE WAIT 3;

UPDATE T_ORD_STLM
   SET STLM_AMT = :new_amt
 WHERE STLM_SEQ = :stlm_seq;
COMMIT;
-- 화면: 저장 요청이 돌아올 때까지 버튼을 잠근다 (같은 요청을 두 번 보내지 않는다)

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

xplan

[고친 뒤 — 같은 월요일 10:30 (학습용 예시)]
  저장 한 번        3초 기다린 뒤 ORA-30006 → 안내 문구 · 커넥션 반납
  기다리는 커넥션    최대 6개 (사람 수만큼, 3초씩) · 커넥션 풀 고갈 0 · 조회 화면 · 로그인 정상
  배치가 끝난 뒤     다시 저장하면 0.01초

모범 답안 서술

배치(SID 53)는 10:30부터 지난주 정산 20,114행을 한 문장으로 고치며 차례로 잠그고, 문장이 끝나 COMMIT하는 10:36:12까지 놓지 않는다. 조민우가 고치려던 정산 96,211은 지난주 건이라 저장의 SELECT … FOR UPDATE가 그 행에서 기다렸다(enq: TX - row lock contention, blocking_session 53, TX REQUEST 6). 옵션 없는 FOR UPDATE에는 기다림 한도가 없으므로 배치가 끝날 때까지 기다린다. 기다리는 요청은 커넥션을 쥔 채 기다린다. 응답이 없자 사용자들이 저장을 다시 눌렀고, 누를 때마다 새 요청이 커넥션을 하나 더 얻어 같은 행 앞에 줄을 섰다. 10:34에는 저장 요청 38개가 커넥션 40개 중 38개를 쥐었고, 나머지 둘마저 차자 조회 화면과 로그인도 커넥션을 얻지 못해 멈췄다(커넥션 대기 타임아웃 412건). 줄을 선 것은 정산 몇 건인데 멈춘 것은 화면 전체다. 고친 판은 FOR UPDATE WAIT 3으로 기다림에 한도를 둔다. 3초 안에 잠그지 못하면 ORA-30006을 받으므로, 화면은 이를 잡아 "다른 작업이 이 정산을 처리 중입니다. 잠시 뒤 다시 저장하세요."를 띄우고 커넥션을 바로 돌려준다. 저장 요청이 돌아올 때까지 버튼을 잠가 같은 요청을 두 번 보내지 않게 한다. NOWAIT와 WAIT n 중에서는 WAIT 3을 고른다. NOWAIT는 잠겨 있으면 바로 ORA-00054를 받으므로, 다른 사람이 몇 ms 동안 같은 정산을 저장하는 흔한 충돌에도 에러를 낸다. WAIT 3은 짧은 충돌은 넘기고, 배치처럼 오래 쥔 락에는 3초 만에 포기한다. 숫자는 '사용자가 기다려도 되는 시간'으로 정한다. 배치 쪽도 손볼 수 있다. 2만 행을 6분 동안 한 문장으로 쥐는 대신, 화면을 덜 쓰는 시간대로 옮기거나 정산일 단위로 나눠 짧게 COMMIT하면 화면과 겹치는 시간이 준다.

해설

기다림에는 늘 한도를 둔다. 사람이 누르는 화면의 FOR UPDATE, DDL의 DDL_LOCK_TIMEOUT, 애플리케이션의 쿼리 타임아웃이 모두 같은 생각이다. 한도가 없으면 DB의 줄 하나가 WAS의 커넥션 풀을 거쳐 시스템 전체의 줄이 된다.
WAIT n의 숫자는 짧은 충돌보다 길고 사람의 인내보다 짧게 잡는다. 행 하나를 저장하는 트랜잭션은 몇 ms면 끝나므로 2~5초면 충분하다.
에러를 받은 뒤 화면이 자동으로 다시 시도하게 하면 같은 일이 반복된다. 재시도는 사용자가 안내를 보고 하게 하거나, 횟수와 간격에 한도를 둔다.
화면이 저장할 때 금액이 그사이 바뀌었는지 확인하는 일은 잠그지 않고도 할 수 있다(UPDATE의 WHERE에 읽어 둔 값을 함께 주는 방법). 그 방식의 장단점은 동시성 제어 방식을 다루는 문제에서 따로 본다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황