점심 피크에 결제 버튼을 누르면 1초쯤 멈췄다가 다시 시도하라고 뜬다

기초INSERT버거500 / 700

상황

버거원 POS는 200개 매장에서 결제 버튼을 누를 때마다 주문 저장 프로시저를 부른다. 점심 피크(11:50 ~ 12:30)에는 결제가 초당 15건 남짓 들어온다. 지난달부터 점주들의 민원이 늘었다. "결제 버튼을 누르면 1초쯤 멈췄다가 '주문번호 중복, 다시 시도합니다'가 떠요." 앱은 ORA-00001을 받으면 세 번까지 다시 부르고, 세 번 다 실패하면 결제를 처음부터 다시 하게 한다.

테이블과 인덱스

ORD 주문: 1,000,000행 · 22,422블록(오늘 피크 직전 기준) · ORD_NO NUMBER, 1부터 빈틈없이 이어진 번호라 지금 최댓값은 1,000,000이다
ORD_ITEM 주문상품: 2,472,717행 · ORD_ITEM_PK (ORD_NO, ITEM_ID) · 주문당 평균 2.5행
인덱스 ORD_PK (ORD_NO): BLEVEL 2 · 이 문제의 POS 운영 DB에는 PK 두 개만 있다 · 통계 최신
결제 한 번의 순서: 번호 받기 → ORD INSERT → ORD_ITEM INSERT(상품마다) → 카드 승인 요청(VAN 응답 평균 1.1초) → COMMIT
시퀀스는 아직 없다

원본 SQL

sql

-- PKG_POS.SAVE_ORDER — 결제 버튼 한 번 (ORA-00001 이면 앱이 세 번까지 다시 부른다)
SELECT NVL(MAX(ORD_NO), 0) + 1 INTO v_no FROM ORD;

INSERT INTO ORD (ORD_NO, ORD_DT, ORD_HMS, SHOP_NO)
VALUES (v_no, TO_CHAR(SYSDATE, 'YYYYMMDD'), TO_CHAR(SYSDATE, 'HH24MISS'), :shop_no);

-- 상품마다
INSERT INTO ORD_ITEM (ORD_NO, ITEM_ID, ORD_ITEM_QTY, ORD_DT, ORD_HMS)
VALUES (v_no, :item_id, :qty, TO_CHAR(SYSDATE, 'YYYYMMDD'), TO_CHAR(SYSDATE, 'HH24MISS'));

-- 카드 승인 (VAN) 응답을 받은 뒤
COMMIT;

실행계획 학습용 예시

xplan

[번호 받기 — ALLSTATS LAST]
---------------------------------------------------------------------------------------------
| Id  | Operation                  | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |        |      1 |        |      1 |00:00:00.01 |       3 |
|   1 |  SORT AGGREGATE            |        |      1 |      1 |      1 |00:00:00.01 |       3 |
|   2 |   INDEX FULL SCAN (MIN/MAX)| ORD_PK |      1 |      1 |      1 |00:00:00.01 |       3 |
---------------------------------------------------------------------------------------------

[시간표 — 11:50:03 · 두 매장]
시각           매장 SH0017 (SID 88)                          매장 SH0142 (SID 131)
11:50:03.20    MAX+1 → 1000001 · ORD INSERT · ORD_ITEM INSERT
               카드 승인 요청 (트랜잭션을 연 채)
11:50:03.24                                                  MAX+1 → 1000001 (88 의 행은 COMMIT 전이라 안 보인다)
                                                             ORD INSERT → 기다림
11:50:04.31    승인 · COMMIT                                 → ORA-00001: unique constraint (DEV.ORD_PK) violated
11:50:04.33                                                  앱이 다시 부른다 → MAX+1 → 1000002 · …

[V$SESSION — 11:50:03.9]
SID   EVENT                            SECS  BLOCKING_SESSION  하는 문장
88    SQL*Net message from client         1                    (카드 승인 응답 대기 · 트랜잭션을 연 채)
131   enq: TX - row lock contention       1  88                INSERT INTO ORD …

[V$LOCK — 같은 시각]
SID   TYPE  대상 / 이름표          LMODE  REQUEST  BLOCK
88    TM    ORD                      3      0        0
88    TX    88 의 트랜잭션            6      0        1
131   TM    ORD                      3      0        0
131   TX    88 의 트랜잭션            0      4        0     ← 같은 키를 먼저 넣은 쪽이 COMMIT 할지 ROLLBACK 할지 기다린다

[11:50 ~ 12:30 집계]
  결제 36,240건 · 첫 시도에서 ORA-00001 2,870번 (7.9%) · 세 번 다 실패 41건
  enq: TX - row lock contention 2,870번 · 평균 0.94초

문제

번호를 받는 SQL은 3블록짜리인데 두 매장이 왜 같은 번호를 받는지, SID 131이 무엇을 기다리다가 에러를 받는지 시간표와 V$LOCK으로 설명하시오.
번호가 겹치지도 않고 기다리지도 않도록 고치시오. 그 대가로 무엇을 받아들여야 하는지도 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- 번호는 시퀀스가 준다 — 테이블을 읽지도 잠그지도 않는다
-- 영업이 끝난 02:00 에 MAX(ORD_NO) 를 재고, 그보다 넉넉히 큰 값에서 시작한다
CREATE SEQUENCE SQ_ORD_NO START WITH 1100001 CACHE 100;

-- PKG_POS.SAVE_ORDER
INSERT INTO ORD (ORD_NO, ORD_DT, ORD_HMS, SHOP_NO)
VALUES (SQ_ORD_NO.NEXTVAL, TO_CHAR(SYSDATE, 'YYYYMMDD'), TO_CHAR(SYSDATE, 'HH24MISS'), :shop_no)
RETURNING ORD_NO INTO v_no;

-- 상품마다
INSERT INTO ORD_ITEM (ORD_NO, ITEM_ID, ORD_ITEM_QTY, ORD_DT, ORD_HMS)
VALUES (v_no, :item_id, :qty, TO_CHAR(SYSDATE, 'YYYYMMDD'), TO_CHAR(SYSDATE, 'HH24MISS'));

COMMIT;   -- 카드 승인 뒤

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

xplan

[고친 뒤 — 다음 날 같은 점심 피크 (학습용 예시)]
  번호 받기   Buffers 3 → 0 (캐시 100개를 다 쓰면 한 번 SEQ$ 를 고친다) · 기다림 0
  ORA-00001  2,870번 → 0 · enq: TX - row lock contention (ORD) 2,870번 → 0 · 다시 시도 0
  카드 승인을 기다리는 1.1초는 그대로 남지만, 그동안 다른 매장을 막지 않는다

모범 답안 서술

번호를 받는 SQL은 INDEX FULL SCAN (MIN/MAX)로 ORD_PK의 맨 끝 한 건만 읽으므로 3블록이면 끝난다. 문제는 SQL의 크기가 아니라 번호를 받은 뒤 COMMIT하기까지의 시간이다. SID 88은 1000001을 넣고 카드 승인 응답(평균 1.1초)을 기다리는 동안 트랜잭션을 열어 둔다. 그 행은 COMMIT 전이라 SID 131의 MAX에는 보이지 않고, SELECT는 아무것도 잠그지 않으므로 131도 같은 1000001을 받는다. 131이 같은 키를 INSERT하면 유일 인덱스 ORD_PK에서 88의 키와 부딪힌다. 88이 COMMIT할지 ROLLBACK할지 아직 모르므로 131은 88의 TX를 REQUEST 4로 기다린다(enq: TX - row lock contention, blocking_session 88). 88이 COMMIT하는 순간 131은 ORA-00001을 받고, 앱이 다시 불러 1000002를 받는다. 피크 40분 동안 첫 시도의 7.9%(2,870번)가 평균 0.94초를 기다린 뒤 실패했고, 41건은 세 번 다 실패했다. 번호는 시퀀스로 받는다. SQ_ORD_NO.NEXTVAL은 테이블을 읽지도 잠그지도 않고 한 번 나간 번호를 다시 주지 않으므로 겹침도 기다림도 없다(번호 받기 Buffers 3 → 0, ORA-00001 2,870 → 0). CACHE 100으로 두면 번호 100개마다 한 번만 딕셔너리를 고친다. 대가는 빈 번호다. 카드 승인이 거절되어 ROLLBACK하면 그 번호는 쓰이지 않고 남으며, 인스턴스를 재시작하면 캐시에 꺼내 두었던 번호도 사라진다. 주문번호는 순서에 의미가 없는 식별 번호이므로 빈 번호를 허용해도 된다.

해설

MAX에 FOR UPDATE를 붙일 수는 없다. 집계 함수가 있는 SELECT에 FOR UPDATE를 쓰면 ORA-01786이 난다. 그렇다고 LOCK TABLE ORD IN EXCLUSIVE MODE로 줄을 세우면 번호를 받은 트랜잭션이 끝날 때까지 다른 매장의 주문 INSERT가 전부 TM 줄에 선다.
다시 시도하는 앱 코드는 증상을 감춘다. 결제가 실패하지 않으니 에러는 로그에만 남고, 점주가 느끼는 것은 1초짜리 멈춤이다. ORA-00001을 다시 시도로 처리하는 코드가 있으면 그 횟수부터 센다.
카드 승인을 번호를 받기 전에 끝내도록 순서를 바꾸면 트랜잭션도 짧아진다. 시퀀스와 함께 쓰면 승인 거절로 생기는 빈 번호도 준다.
시퀀스로 바꾸면 다음 병목은 인덱스다. 200개 매장이 늘 커지는 번호를 넣으면 ORD_PK의 맨 오른쪽 리프 블록 하나에 몰린다(buffer busy waits, enq: TX - index contention). 동시 세션 수가 정하는 다음 단계의 일이다(교재 12화 §10, 14화).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황