타임딜 10시 정각, 주문하기를 누른 화면이 몇 분씩 돌다가 사이트 전체가 멈췄다

모범 답안 SQL ​

-- ① 「주문하기」: 재고 선점 — 짧은 트랜잭션 하나 (결제창을 띄우기 전에 COMMIT)
UPDATE product_stock
   SET stock_qty = stock_qty - :qty
 WHERE product_id = :pid
   AND stock_qty >= :qty;                                    -- 0건이면 품절: 결제창을 띄우지 않는다
INSERT INTO stock_hold (hold_id, product_id, customer_id, qty, status, expire_at)
VALUES (:hid, :pid, :cid, :qty, 'HOLD', SYSDATE + 10/1440);   -- 10분 안에 결제하지 않으면 풀린다
INSERT INTO orders (order_id, customer_id, order_date, status, total_amount)
VALUES (:oid, :cid, SYSDATE, 'PENDING', :amt);
INSERT INTO order_items (order_item_id, order_id, product_id, quantity, unit_price)
VALUES (:oiid, :oid, :pid, :qty, :price);
COMMIT;                                                       -- 재고 행을 쥐는 시간: 몇 ms

-- (PG 결제창 — DB 트랜잭션이 없다)

-- ② 결제 완료: 선점을 확정 — 짧은 트랜잭션 하나
UPDATE stock_hold SET status = 'DONE'
 WHERE hold_id = :hid AND status = 'HOLD';                   -- 0건이면 만료 정리가 먼저 가져갔다 → 결제 취소
INSERT INTO payments (payment_id, order_id, payment_method, amount, paid_at)
VALUES (:payid, :oid, :method, :amt, SYSDATE);
UPDATE orders SET status = 'PAID' WHERE order_id = :oid;
COMMIT;

-- ③ 만료 정리 (1분마다): 결제하지 않은 선점을 재고로 돌려놓는다
DECLARE
  TYPE t_ids IS TABLE OF stock_hold.hold_id%TYPE;
  v_ids t_ids;
  v_pid stock_hold.product_id%TYPE;
  v_qty stock_hold.qty%TYPE;
BEGIN
  SELECT hold_id BULK COLLECT INTO v_ids
    FROM stock_hold WHERE status = 'HOLD' AND expire_at < SYSDATE;
  FOR i IN 1 .. v_ids.COUNT LOOP
    UPDATE stock_hold SET status = 'EXPIRED'
     WHERE hold_id = v_ids(i) AND status = 'HOLD'             -- 그사이 결제가 확정했으면 0건
    RETURNING product_id, qty INTO v_pid, v_qty;
    IF SQL%ROWCOUNT = 1 THEN
      UPDATE product_stock SET stock_qty = stock_qty + v_qty WHERE product_id = v_pid;
    END IF;
    COMMIT;
  END LOOP;
END;

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

[고친 판 — 같은 타임딜을 부하 시험으로 (학습용 예시)]
  ① 선점 트랜잭션   재고 행을 쥐는 시간 2 ~ 4 ms · 첫 1분 3,100번 · 기다림 합계 1.9초 · 커넥션 풀 최대 사용 23개
  500개 선점 완료   10:00:07 (그 뒤의 주문하기는 0건 → 바로 품절 화면)
  ② 결제 완료       10:10까지 471건 · 만료로 돌아온 29개는 10:11 정리 뒤 다시 판매 → 10:16 품절
  다른 화면          영향 없음 (커넥션 대기 0)

모범 답안 서술 ​

재고 행 하나가 사이트 전체를 멈춘 경로는 이렇다. 고객 1의 트랜잭션은 product_stock 117번 행을 UPDATE로 잠근 뒤, 결제창에서 고객이 결제를 마칠 때까지(평균 40초) COMMIT하지 않는다. 행 락은 COMMIT할 때 풀리므로 그동안 같은 상품을 사려는 모든 세션이 그 행에서 TX 6으로 기다린다. V$LOCK에서 196개 세션이 SID 1311의 TX를 REQUEST 6으로 원하고, 뿌리 1311은 SQL*Net message from client, 곧 결제창 앞에서 트랜잭션을 연 채 쉬고 있다. 앞사람이 결제를 마쳐야 다음 사람이 결제창을 볼 수 있으니 결제가 한 명씩 차례로 일어났고, 창을 닫은 고객은 타임아웃까지 3분 동안 줄 전체를 붙잡았다. 그래서 12분 동안 14개만 팔렸다. 기다리는 요청은 커넥션을 쥔 채 기다린다. 첫 1분에 3,100번이 몰리자 10:03에 커넥션 풀 200개가 전부 이 UPDATE에서 기다리게 됐고, 타임딜과 상관없는 상품 목록과 로그인도 커넥션을 얻지 못해 멈췄다(WAS 로그 18,420건). 두 방법을 비교한다. 첫째, 결제 전에 짧게 선점하는 방법은 「주문하기」에서 재고를 조건부로 빼고(stock_qty >= :qty), 선점 기록(만료 10분)과 주문을 넣은 뒤 결제창을 띄우기 전에 바로 COMMIT한다. 재고 행을 쥐는 시간이 40초에서 몇 ms로 줄어 3,100명이 7초 안에 500개를 나눠 가진다. 대가는 결제하지 않은 선점이 만료까지 재고를 묶는다는 것이다. 그동안 다른 고객에게는 품절로 보이고, 만료 정리 작업을 따로 돌려야 한다. 둘째, 결제가 끝난 뒤에 빼는 방법은 결제를 먼저 받고 조건부 UPDATE로 재고를 뺀다. 재고를 묶지 않고 정리 작업도 없지만, 500개를 넘는 고객이 결제를 마칠 수 있어서 UPDATE가 0건이면 이미 받은 결제를 취소(환불)해야 한다. 타임딜처럼 수요가 재고의 몇 배인 판매에서는 취소가 수천 건이 되고 고객 경험도 나쁘다. 그래서 선점을 고른다. ① 선점 트랜잭션은 몇 ms이고, 결제창 동안에는 DB 트랜잭션이 없다. ② 결제 완료는 선점을 확정하는 짧은 트랜잭션이다. ③ 만료 정리는 1분마다 만료된 선점을 EXPIRED로 바꾸고 재고를 돌려놓는다. 만료 정리와 결제 확정이 같은 선점을 두고 겹칠 수 있으므로 둘 다 status = 'HOLD' 조건으로 UPDATE해 한쪽만 이기게 한다. 결제 확정이 0건이면 정리가 먼저 재고를 돌려놓은 것이므로 그 결제는 취소한다. 재고가 음수가 되는 일은 조건부 차감이 막는다.

해설 ​

• 트랜잭션 안에서 사람이나 외부 시스템을 기다리지 않는다. 결제창, 확인 팝업, 외부 API 응답을 기다리는 동안 쥔 락은 그 사람의 속도로만 풀린다. 락은 바꿀 값이 정해진 뒤 마지막 순간에 잡고 바로 COMMIT한다(교재 12화의 '줄은 앞사람의 트랜잭션에 선다').
• FOR UPDATE NOWAIT나 WAIT n으로 바꾸는 것은 답이 아니다. 줄은 짧아지지만 고객 대부분이 "잠시 후 다시"를 받고, 한 명씩 결제하는 구조는 그대로다.
• 인기 상품 행 하나에 몰리는 것 자체가 문제가 되는 규모라면 재고를 여러 행(버킷 10개)에 나눠 담아 선점할 때 하나를 고르게 하거나, 대기열(입장 순번)을 앞에 두어 DB에 들어오는 요청 수를 제한한다. 둘 다 '남은 재고' 계산이 복잡해지는 대가가 있다.
• 선점 만료 10분은 결제창의 실제 소요 시간 분포(평균 40초, 타임아웃 3분)를 보고 업무와 정한다. 너무 짧으면 결제 중인 고객의 선점이 풀리고, 너무 길면 재고가 오래 묶인다.

관련 교재 ​

• 12화 · 아무도 안 건드렸는데 전체가 멈췄다