매일 아침 개점 직후 첫 주문에서 몇몇 매장의 키오스크가 번호 발급 실패를 띄운다

기초PLSQL버거508 / 700

상황

버거원 매장은 주문마다 영수증과 대기 번호판에 오늘의 주문번호를 띄운다. 번호는 매장마다 매일 1번부터 시작하고, 카운터 POS와 키오스크 두 대가 같은 번호 체계를 쓴다. 매일 아침 개점 직후 첫 주문에서 하루 30 ~ 40개 매장의 키오스크나 POS가 "번호 발급 실패, 다시 주문해 주세요"를 띄운다. 그 뒤로는 하루 종일 괜찮다.

테이블과 인덱스

SHOP 매장: 200행 · 5블록 · SHOP_NO 'SH0001' · PK
SHOP_RCPT_NUM 매장별 일별 번호(이 문제를 위해 더한 채번 테이블): SHOP_RCPT_NUM_PK (SHOP_NO, RCPT_DT) · LAST_NO · 하루 200행씩 생긴다
한 매장에서 동시에 번호를 받는 단말은 많아야 세 대다. 번호를 받은 주문은 결제가 끝나면 바로 COMMIT한다(1 ~ 2초)
개점 시각은 매장마다 다르다(06:30 ~ 10:00). 개점 직후에는 키오스크와 카운터에 손님이 한꺼번에 몰린다

원본 SQL

sql

-- PKG_POS.NEXT_RCPT_NO — 오늘의 주문번호
FUNCTION NEXT_RCPT_NO(p_shop VARCHAR2, p_dt VARCHAR2) RETURN NUMBER IS
  v_no NUMBER;
BEGIN
  SELECT LAST_NO + 1 INTO v_no
    FROM SHOP_RCPT_NUM
   WHERE SHOP_NO = p_shop AND RCPT_DT = p_dt
     FOR UPDATE;
  UPDATE SHOP_RCPT_NUM SET LAST_NO = v_no
   WHERE SHOP_NO = p_shop AND RCPT_DT = p_dt;
  RETURN v_no;
EXCEPTION
  WHEN NO_DATA_FOUND THEN                        -- 오늘 첫 주문이면 행을 만든다
    INSERT INTO SHOP_RCPT_NUM (SHOP_NO, RCPT_DT, LAST_NO) VALUES (p_shop, p_dt, 1);
    RETURN 1;
END;

실행계획 학습용 예시

xplan

[시간표 — 매장 SH0088 · 07:00 개점]
시각          카운터 POS (SID 140)                         키오스크 1 (SID 166)
07:00:21.30   SELECT … FOR UPDATE → 0행 (NO_DATA_FOUND)
07:00:21.31                                                SELECT … FOR UPDATE → 0행 (NO_DATA_FOUND)
07:00:21.32   INSERT ('SH0088', '20131014', 1) → 1번
07:00:21.33                                                INSERT ('SH0088', '20131014', 1) → 기다림
07:00:22.90   결제 끝 · COMMIT                             → ORA-00001: unique constraint (DEV.SHOP_RCPT_NUM_PK) violated
                                                           → 키오스크 "번호 발급 실패"

[V$LOCK — 07:00:22]
SID   TYPE  대상 / 이름표            LMODE  REQUEST  BLOCK
140   TM    SHOP_RCPT_NUM              3      0        0
140   TX    140 의 트랜잭션             6      0        1
166   TM    SHOP_RCPT_NUM              3      0        0
166   TX    140 의 트랜잭션             0      4        0

[지난 2주 · 번호 발급 실패]
  하루 31 ~ 42건 · 전부 그 매장의 그날 첫 번째 ~ 세 번째 주문 · 그 뒤로는 0건
  같은 시각에 다른 매장끼리는 한 번도 기다리지 않았다

문제

시간표를 보고 왜 그날 첫 주문에서만 실패하는지, FOR UPDATE가 왜 이 충돌을 막지 못하는지 설명하시오.
개점 직후에도 실패하지 않게 고치시오. 번호를 받는 문장도 줄일 수 있으면 줄이시오.

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

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

모범답안

모범 답안 SQL

sql

-- ① 매일 23:30 — 내일 행을 매장마다 미리 만든다 (개점 첫 주문이 행을 만들 일이 없다)
INSERT INTO SHOP_RCPT_NUM (SHOP_NO, RCPT_DT, LAST_NO)
SELECT SHOP_NO, TO_CHAR(SYSDATE + 1, 'YYYYMMDD'), 0
  FROM SHOP;
COMMIT;

-- ② 번호는 한 문장으로 — 행을 잠그고 올리고 읽는 일이 한 번에 끝난다
FUNCTION NEXT_RCPT_NO(p_shop VARCHAR2, p_dt VARCHAR2) RETURN NUMBER IS
  v_no NUMBER;
BEGIN
  UPDATE SHOP_RCPT_NUM SET LAST_NO = LAST_NO + 1
   WHERE SHOP_NO = p_shop AND RCPT_DT = p_dt
  RETURNING LAST_NO INTO v_no;

  IF SQL%ROWCOUNT = 0 THEN                        -- ①이 빠진 날 · 새로 연 매장
    BEGIN
      INSERT INTO SHOP_RCPT_NUM (SHOP_NO, RCPT_DT, LAST_NO) VALUES (p_shop, p_dt, 1);
      v_no := 1;
    EXCEPTION
      WHEN DUP_VAL_ON_INDEX THEN                  -- 같은 순간 다른 단말이 먼저 만들었다 → 그 행에서 받는다
        UPDATE SHOP_RCPT_NUM SET LAST_NO = LAST_NO + 1
         WHERE SHOP_NO = p_shop AND RCPT_DT = p_dt
        RETURNING LAST_NO INTO v_no;
    END;
  END IF;
  RETURN v_no;
END;

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

xplan

[고친 뒤 — 같은 매장 · 07:00 개점 (학습용 예시)]
시각          카운터 POS (SID 140)                         키오스크 1 (SID 166)
07:00:21.30   UPDATE … RETURNING → 1번 (행 락)
07:00:21.31                                                UPDATE … RETURNING → 기다림 (TX REQUEST 6)
07:00:22.90   결제 끝 · COMMIT                             → 2번 · 결제 진행
  번호 발급 실패 하루 31 ~ 42건 → 0건 · 둘째 단말은 첫 단말의 결제가 끝날 때까지 1 ~ 2초 기다린다

모범 답안 서술

FOR UPDATE는 있는 행을 잠그는 장치다. 그날 첫 주문이 들어올 때는 SHOP_RCPT_NUM에 오늘 행이 아직 없으므로 잠글 것이 없다. 카운터와 키오스크 둘 다 0행을 읽어 NO_DATA_FOUND로 빠지고, 둘 다 1번으로 INSERT한다. 키오스크의 INSERT는 PK에서 카운터의 키와 부딪혀 카운터의 TX를 REQUEST 4로 기다리고(V$LOCK의 SID 166), 카운터가 결제를 끝내고 COMMIT하자 ORA-00001을 받는다. 그래서 그날 첫 번째 ~ 세 번째 주문에서만 실패하고, 행이 생긴 뒤에는 FOR UPDATE가 제 역할을 한다. 고치는 방법은 개점 전에 행이 있게 하는 것이다. 매일 밤 23:30에 SHOP의 200개 매장마다 내일 행을 LAST_NO 0으로 미리 만든다. 그러면 개점 첫 주문도 있는 행을 잠그므로, 둘째 단말은 실패하는 대신 그 행에서 TX 6으로 기다렸다가 2번을 받는다. 밤 작업이 빠진 날이나 새로 연 매장을 위해서는, 행이 없으면 만들고 동시에 만들다가 DUP_VAL_ON_INDEX가 나면 그 행에서 다시 받는 길을 남겨 둔다. 번호 받기는 SELECT … FOR UPDATE와 UPDATE 두 문장을 UPDATE … RETURNING 한 문장으로 줄인다. 잠그고, 올리고, 읽는 일이 한 번에 끝나므로 왕복이 하나 준다. 잠그는 범위는 (매장, 날짜) 한 행이라 다른 매장끼리는 기다리지 않는다. 지난 2주 동안 다른 매장끼리 한 번도 기다리지 않은 것도 같은 이유다.

해설

MERGE로 바꿔도 같다. 단말마다 MERGE … USING DUAL … WHEN NOT MATCHED THEN INSERT를 부르면 두 세션이 함께 '없음'을 보고 함께 INSERT하므로, 뒤 세션은 같은 TX 4 대기 뒤에 ORA-00001을 받는다. 없는 행을 둘러싼 경쟁은 행을 미리 만들거나 DUP_VAL_ON_INDEX를 받아 다시 하는 것으로 푼다.
남는 기다림: 행 락은 COMMIT까지 가므로 같은 매장의 둘째 단말은 첫 단말의 결제(1 ~ 2초)를 기다린다. 대기 번호판의 번호가 취소된 주문 때문에 하나쯤 건너뛰어도 되는 번호라면, 번호 받기만 자율 트랜잭션으로 떼어 내 이 기다림도 없앨 수 있다. 대신 결제가 취소되면 그 번호는 빈 번호가 된다.
채번 행을 (매장, 날짜)로 나눈 것이 곧 경합을 나눈 것이다. 전 매장이 행 하나를 같이 쓰면 200개 매장이 한 줄로 선다.
하루 200행씩 쌓이는 채번 테이블은 지난 날짜 행을 주기적으로 지운다. 지난 날짜 행은 아무도 잠그지 않으므로 낮에 지워도 된다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황