하루 5,000개 한정 세트가 5,037개 팔렸다

기초UPDATE버거525 / 700

상황

버거원 본사는 신메뉴 출시에 맞춰 「더블 패티 세트」를 전국 하루 5,000개 한정으로 팔았다. POS는 세트를 담을 때마다 본사 DB의 남은 수량을 확인하고 담은 만큼 줄인다. 12:41에 남은 수량이 0이 되어 판매가 멈췄는데, 마감 뒤 주문상품을 세어 보니 5,037개가 팔려 있었다. 재료가 모자란 매장 37곳은 고객에게 쿠폰으로 사과했다. 개발팀은 이렇게 말했다. "UPDATE가 행 락을 기다리게 되어 있으니 동시에 줄여도 안전합니다."

테이블과 인덱스

ITEM_LMT 한정 판매 수량(이 문제를 위해 더한 테이블): PK (ITEM_ID, SALE_DT) · LMT_QTY 한정 수량 · REMAIN_QTY 남은 수량 · 한정 메뉴 하루 한 행
ITEM 상품: 92행 · 5블록 · 더블 패티 세트는 ITEM_ID = 'I0088'
ORD_ITEM 주문상품: 2,472,717행 · ORD_ITEM_PK (ORD_NO, ITEM_ID)
결제 한 번의 순서: 한정 수량 줄이기, ORD와 ORD_ITEM INSERT, 카드 승인(VAN 응답 평균 1.1초), COMMIT
점심 피크(12:00 ~ 12:41)에는 전국 매장에서 이 세트가 분당 70 ~ 90개 팔렸다.

원본 SQL

sql

-- PKG_POS.ADD_LIMITED — 한정 세트를 담을 때 (같은 트랜잭션에서 주문을 넣고, 카드 승인 뒤 COMMIT)
SELECT REMAIN_QTY INTO v_remain
  FROM ITEM_LMT
 WHERE ITEM_ID = :item_id AND SALE_DT = :sale_dt;

IF v_remain < :qty THEN
  RAISE_APPLICATION_ERROR(-20010, '한정 수량이 모두 팔렸습니다');
END IF;

UPDATE ITEM_LMT
   SET REMAIN_QTY = v_remain - :qty
 WHERE ITEM_ID = :item_id AND SALE_DT = :sale_dt;

실행계획 학습용 예시

xplan

[시간표 — 12:31:07 · 남은 수량 412]
시각           매장 SH0031 POS (SID 74)                      매장 SH0158 POS (SID 191)
12:31:07.10    SELECT → 412 · 412 - 1 = 411
               UPDATE … SET REMAIN_QTY = 411 · 주문 INSERT · 카드 승인 요청
12:31:07.30                                                  SELECT → 412 (74 는 COMMIT 전이라 412 가 보인다) · 411 계산
                                                             UPDATE … SET REMAIN_QTY = 411 → 기다림
12:31:08.21    승인 · COMMIT (REMAIN_QTY 411)                → 깨어남 · 계산해 둔 411 을 그대로 쓴다
12:31:09.40                                                  승인 · COMMIT (REMAIN_QTY 411)
               → 두 개가 팔렸는데 남은 수량은 하나만 줄었다

[V$SESSION — 12:31:07.8]
SID   EVENT                            SECS  BLOCKING_SESSION  하는 문장
74    SQL*Net message from client         0                    (카드 승인 응답 대기 · 트랜잭션을 연 채)
191   enq: TX - row lock contention       0  74                UPDATE ITEM_LMT SET REMAIN_QTY = :B3 - :B2 …

[마감 집계]
  한정 5,000 · 판매(ORD_ITEM 집계) 5,037 · 마지막 REMAIN_QTY 0
  12:00 ~ 12:41  enq: TX - row lock contention (ITEM_LMT) 1,906번 · 평균 0.7초
  기다렸다 깨어난 UPDATE 가운데 앞사람이 줄인 값을 덮어쓴 경우 37번

문제

시간표를 보고 SID 191이 행 락을 기다렸는데도 수량이 하나만 줄어든 이유를 설명하시오.
한정 수량을 넘겨 팔지 않도록 고치시오. 고친 뒤에도 남는 기다림이 무엇인지 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- PKG_POS.ADD_LIMITED — 확인과 차감을 한 문장으로 (계산은 잠근 뒤의 값으로 한다)
UPDATE ITEM_LMT
   SET REMAIN_QTY = REMAIN_QTY - :qty
 WHERE ITEM_ID = :item_id
   AND SALE_DT = :sale_dt
   AND REMAIN_QTY >= :qty;

IF SQL%ROWCOUNT = 0 THEN
  RAISE_APPLICATION_ERROR(-20010, '한정 수량이 모두 팔렸습니다');
END IF;
-- 주문 INSERT → 카드 승인 → COMMIT 은 그대로

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

xplan

[고친 뒤 — 같은 두 매장 (학습용 예시)]
12:31:07.10   74  : UPDATE … REMAIN_QTY - 1 → 411 · 카드 승인 요청
12:31:07.30   191 : UPDATE … → 기다림
12:31:08.21   74  : COMMIT        191 : 깨어남 · 지금 값 411 로 WHERE 를 다시 보고 410 으로 고친다
  판매 5,037 → 5,000 · 넘겨 판 수량 0 · 남은 수량이 모자라면 UPDATE 0행 → "모두 팔렸습니다"
  ITEM_LMT 한 행의 기다림은 그대로 (차감부터 COMMIT 까지 카드 승인 1.1초)

모범 답안 서술

SID 191은 SELECT로 412를 읽고 앱에서 411을 계산한 뒤 UPDATE했다. SID 74가 아직 COMMIT 전이라 191의 SELECT에는 74의 차감이 보이지 않았다. 191의 UPDATE는 74가 잠근 행에서 enq: TX - row lock contention으로 기다렸고, 74가 COMMIT하자 깨어났다. 하지만 SET에 쓸 값은 이미 412로 계산해 둔 411이었으므로 깨어난 뒤에도 그대로 411을 썼다. 74가 줄인 하나가 덮여 사라진 것이다. 기다림은 두 세션이 동시에 쓰는 것만 막을 뿐, 잠그기 전에 읽은 옛 값으로 계산한 결과까지 바로잡지는 못한다. 이런 갱신 손실이 점심 피크에 37번 일어나 5,037개가 팔렸다. 고친 판은 확인과 차감을 UPDATE 한 문장으로 한다. SET REMAIN_QTY = REMAIN_QTY - :qty는 오라클이 행을 잠근 뒤의 현재 값으로 계산한다. 191처럼 기다렸다 깨어난 UPDATE도 74가 COMMIT한 411을 다시 읽어 WHERE를 확인하고 410으로 고친다. REMAIN_QTY >= :qty를 WHERE에 넣었으므로 남은 수량이 모자라면 0행을 고치고, SQL%ROWCOUNT가 0이면 품절로 처리한다. 따로 SELECT할 필요가 없고 넘겨 파는 수량은 0이 된다(5,037 → 5,000). 남는 기다림도 있다. 차감한 행의 락은 COMMIT까지 가므로, 카드 승인 1.1초 동안 이 세트를 파는 다른 매장은 그 한 행에서 한 줄로 선다(평균 0.7초, 1,906번). 줄이려면 차감을 카드 승인 뒤, COMMIT 직전으로 옮긴다. 그러면 쥐는 시간이 수 ms로 줄지만, 승인 뒤에 품절이면 승인을 취소해야 한다.

해설

읽고 계산하고 쓰는 세 단계를 앱에서 나누면 그 사이에 다른 트랜잭션이 끼어든다. 값을 바꾸는 계산은 SET 안에, 바꿔도 되는지의 조건은 WHERE 안에 두면 한 문장이 잠금과 확인과 계산을 함께 한다.
다른 정답은 읽을 때 SELECT … FOR UPDATE로 잠그는 비관적 제어다. 191은 SELECT에서 기다렸다가 411을 읽으므로 맞게 계산한다. 다만 문장이 하나 늘고 잠그는 시점이 앞당겨질 뿐 기다림은 같다.
기다렸다 깨어난 UPDATE가 다시 보는 것은 테이블에 있는 컬럼의 현재 값이다. 바인드 변수로 넘긴 값(v_remain - :qty)은 앱이 이미 계산한 상수라 다시 계산되지 않는다. 그래서 원본은 기다리고도 틀렸다.
차감을 결제 뒤로 옮기거나, 선점한 뒤 바로 COMMIT하고 결제가 실패하면 되돌리는 설계도 있다. 사람이 결제창에 오래 머무는 화면일수록 이쪽이 중요해지고, 선점에는 만료와 정리가 따라붙는다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황