적립금은 덜 쌓이고 환불은 두 번 나가고 결제 완료 주문은 결제 대기로 돌아갔다

모범 답안 SQL ​

-- ① 배송 메모 — 낙관적 제어 (사람이 몇 분 머물고, 같은 주문이 겹치는 일은 드물다)
ALTER TABLE T_ORD ADD (MEMO_VER NUMBER DEFAULT 0 NOT NULL);

SELECT ORD_SEQ, CUS_ID, ORD_ST, DLV_MEMO, MEMO_VER FROM T_ORD WHERE ORD_SEQ = :ord_seq;

UPDATE T_ORD
   SET DLV_MEMO = :memo, MEMO_VER = MEMO_VER + 1      -- 화면이 바꾼 칸만 쓴다 (ORD_ST 는 쓰지 않는다)
 WHERE ORD_SEQ = :ord_seq
   AND MEMO_VER = :memo_ver;
-- 0행이면 COMMIT 하지 않고 "다른 상담원이 먼저 메모를 고쳤습니다" → 다시 읽어 새 메모와 내 입력을 나란히 보여 준다
COMMIT;

-- ② 적립금 — 상대 갱신 한 문장 (계산과 잔액 확인을 잠근 뒤의 값으로)
UPDATE M_CUS
   SET PNT_BAL = PNT_BAL + :delta
 WHERE CUS_ID = :cus_id
   AND PNT_BAL + :delta >= 0;
IF SQL%ROWCOUNT = 0 THEN RAISE_APPLICATION_ERROR(-20021, '적립금이 모자랍니다'); END IF;
COMMIT;

-- ③ 환불 — 비관적 제어 (버튼을 누른 순간부터 COMMIT 까지 약 15ms 만 잠근다 · 3초까지만 기다린다)
SELECT PAY_AMT INTO v_pay FROM T_ORD WHERE ORD_SEQ = :ord_seq FOR UPDATE WAIT 3;
SELECT NVL(SUM(RFND_AMT), 0) INTO v_done FROM T_ORD_RFND WHERE ORD_SEQ = :ord_seq;
IF v_done + :amt > v_pay THEN
  ROLLBACK;
  RAISE_APPLICATION_ERROR(-20031, '환불 한도를 넘습니다');
END IF;
INSERT INTO T_ORD_RFND (RFND_SEQ, ORD_SEQ, RFND_AMT, RFND_DT)
VALUES (SQ_RFND.NEXTVAL, :ord_seq, :amt, SYSDATE);
COMMIT;

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

[고친 뒤 — 같은 세 시간표 (학습용 예시)]
①  10:14:15  저장 UPDATE 가 DLV_MEMO 만 고친다 · ORD_ST 는 콜백이 쓴 COMPLETE 그대로 · MEMO_VER 3 → 4
            같은 메모를 두 상담원이 고치면 늦은 쪽이 0행 → 다시 읽기 (하루 0 ~ 2번)
②  19:20:31  결제 화면의 UPDATE 가 기다렸다가 6,200 을 다시 읽어 3,200 으로 고친다 · 기다림 약 5ms
③  15:02:10  요청 2 가 FOR UPDATE 에서 약 30ms 기다린 뒤 SUM 32,000 을 보고 '환불 한도를 넘습니다'
   감사 지적 세 가지 0건 · 결제 콜백과 배송 배치의 기다림 0 (① 은 저장하는 순간 수 ms 만 잠근다)

[개발팀 안 — ① 에 화면 열 때 FOR UPDATE (같은 하루로 어림)]
   메모 수정 1,400번 × 2 ~ 5분 동안 그 주문 행을 쥔다
   → 그 사이 그 주문의 결제 콜백 · 배송 배치가 TX 6 으로 상담원의 저장까지 기다린다 (하루 약 120번)
   → 화면을 열어 둔 채 자리를 비우면 끝없이

모범 답안 서술 ​

①은 화면이 바꾸지도 않은 ORD_ST를 화면 값으로 다시 썼다. 상담원이 10:11:02에 연 화면에는 PENDING이 남아 있었고, 그사이 10:11:40에 결제 콜백이 COMPLETE로 바꾸고 COMMIT했다. 10:14:15의 저장은 SELECT가 잠그지 않았으므로 기다리지도 않고 PENDING으로 덮어썼다. ②는 두 세션이 같은 잔액 5,000을 읽고 앱에서 계산한 값을 썼다. 결제 화면의 UPDATE는 배치의 행 락을 기다렸지만 깨어난 뒤에도 미리 계산한 2,000을 써서 적립 1,200이 사라졌다. 두 경우 모두 갱신 손실이다. ③은 두 요청이 모두 COMMIT 전의 환불을 보지 못해 SUM을 0으로 읽고 각자 INSERT했다. 서로 다른 새 행을 넣었으므로 아무도 기다리지 않았고, 환불이 64,000원 나갔다. 세 곳은 처리의 길이와 겹치는 빈도가 달라서 방식도 달라야 한다. ①은 사람이 2 ~ 5분 머물고 같은 주문을 두 사람이 고치는 일은 하루 0 ~ 2번이므로 낙관적 제어를 쓴다. 화면이 바꾼 DLV_MEMO만 쓰고, 메모 버전 MEMO_VER를 읽어 두었다가 AND MEMO_VER = :memo_ver로 저장하며 1 올린다. 0행이면 다시 읽어 보여 준다. ORD_ST를 쓰지 않으므로 결제 콜백과는 부딪칠 일 자체가 없다. ②는 5ms짜리 계산이고 같은 고객이 분당 몇 번씩 겹친다. 낙관적 제어로 하면 겹칠 때마다 진 쪽이 다시 시도해야 하므로, 계산을 UPDATE 안으로 옮기는 상대 갱신 한 문장이 맞다. PNT_BAL = PNT_BAL + :delta는 행을 잠근 뒤의 현재 값으로 계산하고, PNT_BAL + :delta >= 0이 잔액 확인을 같은 순간에 한다. 0행이면 잔액 부족이다. 줄은 UPDATE부터 COMMIT까지 약 5ms뿐이다. ③은 주문 하나의 여러 환불 행을 합쳐 보고 결정하므로 한 문장으로 쓰기 어렵고, 사람이 머무는 시간 없이 15ms 안에 끝난다. 이런 곳은 비관적 제어가 맞다. 시작할 때 SELECT … FOR UPDATE WAIT 3로 주문 행을 잠그면, 같은 주문의 두 번째 요청은 첫 요청의 COMMIT을 수십 ms 기다린 뒤 SUM 32,000을 보고 거절된다. WAIT 3으로 한도를 두어 결제 콜백이 잠깐 쥔 행에서 끝없이 기다리지 않게 한다. 개발팀 안대로 ①에서 화면을 열 때 FOR UPDATE를 걸면, 상담원이 통화하는 2 ~ 5분 동안 그 주문 행의 락을 쥔다. 그 주문을 고치려는 결제 콜백과 배송 배치가 TX 6으로 같은 시간을 기다리고, 상담원이 화면을 열어 둔 채 자리를 비우면 기다림은 끝이 없다. 겹치는 일이 하루 0 ~ 2번인 화면에 하루 1,400번의 몇 분짜리 락을 거는 셈이다. 비관적 제어는 ③처럼 짧고 사람이 끼지 않는 곳에 쓴다.

해설 ​

• 세 방식은 언제 확인하고 누가 기다리는지가 다르다. 비관적 제어는 읽을 때 잠가 남을 기다리게 하고, 낙관적 제어는 잠그지 않고 저장할 때 확인해 진 쪽이 다시 한다. 상대 갱신은 확인과 계산을 한 문장 안에 넣어 그 문장이 잠근 동안만 기다리게 한다.
• ②를 낙관적 제어(버전 칸)로 해도 틀리지는 않는다. 다만 같은 고객의 적립과 사용이 몰리는 저녁에는 진 쪽이 다시 시도해야 하고, 재시도 코드가 배치와 화면 모두에 들어가야 한다. 계산이 한 칸의 덧셈이면 상대 갱신이 가장 싸다.
• ③의 다른 답은 T_ORD에 환불 누계 칸을 두고 UPDATE … SET RFND_SUM = RFND_SUM + :amt WHERE … AND RFND_SUM + :amt <= PAY_AMT로 확인과 갱신을 한 문장에 하는 방법이다. 빠르지만 누계 칸을 환불 이력과 맞춰 두는 일이 생긴다.
• ①의 MEMO_VER는 메모만 지키는 버전이다. 화면이 ORD_ST를 쓰지 않게 바꿨으므로 콜백이 버전을 올릴 필요가 없다. 화면이 쓰는 칸을 먼저 줄이면 버전이 지켜야 할 범위도 준다.
• 낙관적 제어는 진 쪽에게 무엇을 보여 줄지까지 설계해야 한다. 0행만 알리고 화면을 비우면 상담원이 입력한 메모가 사라진다. 새 값과 내 입력을 나란히 보여 주고 고르게 한다.

관련 교재 ​

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