주문에 상품을 추가할 때 가끔 상세번호 중복 에러가 난다

중급INSERT한빛유통501 / 700

상황

한빛유통 콜센터의 「주문 수정」 화면에서는 상담원이 통화 중인 고객의 주문에 상품을 추가한다. 하루 수천 번 쓰는 화면인데, 하루 스무 번쯤 ORA-00001: unique constraint (SQLP.PK_T_ORD_DET) violated가 난다. 로그를 보면 같은 주문에 0.1초 간격으로 요청 두 개가 들어온 경우다. 상담원이 「추가」를 두 번 눌렀거나, 같은 주문을 두 상담원이 동시에 고쳤다. 개발팀은 두 가지 안을 냈다. ① 상세번호를 시퀀스로 받는다. ② 번호를 받기 전에 LOCK TABLE T_ORD_DET IN EXCLUSIVE MODE를 건다.

테이블과 인덱스

T_ORD 주문: 100,000행 · 896블록 · PK_T_ORD (ORD_SEQ) · ORD_AMT는 상세 금액의 합계
T_ORD_DET 주문상세: 200,000행 · 896블록 · PK_T_ORD_DET (ORD_SEQ, ORD_DET_NO) · 주문당 1 ~ 3건
상세번호는 주문마다 1, 2, 3 …으로 매긴다. 배송 전표와 고객 화면에 그대로 찍힌다
같은 주문을 동시에 고치는 일은 드물다. 다른 주문끼리는 상담원 수십 명이 동시에 고친다

원본 SQL

sql

-- PKG_ORD.ADD_ITEM — 「추가」 버튼 한 번
SELECT NVL(MAX(ORD_DET_NO), 0) + 1 INTO v_det_no
  FROM T_ORD_DET
 WHERE ORD_SEQ = :ord_seq;

INSERT INTO T_ORD_DET (ORD_SEQ, ORD_DET_NO, ITM_ID, ORD_QTY, UNT_PRC)
VALUES (:ord_seq, v_det_no, :itm_id, :qty, :prc);

UPDATE T_ORD SET ORD_AMT = ORD_AMT + :qty * :prc
 WHERE ORD_SEQ = :ord_seq;

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 |   FIRST ROW                  |              |      1 |      1 |      1 |00:00:00.01 |       3 |
|*  3 |    INDEX RANGE SCAN (MIN/MAX)| PK_T_ORD_DET |      1 |      1 |      1 |00:00:00.01 |       3 |
-----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("ORD_SEQ"=:ORD_SEQ)

[시간표 — 주문 58214 · 「추가」 두 번]
시각          요청 1 (SID 44)                          요청 2 (SID 97)
10:12:03.10   MAX+1 → 3 · INSERT (58214, 3)
10:12:03.18                                            MAX+1 → 3 (44 의 행은 COMMIT 전이라 안 보인다)
                                                       INSERT (58214, 3) → 기다림 (TX REQUEST 4)
10:12:03.21   UPDATE T_ORD · COMMIT                    → ORA-00001 · 화면 "저장 실패, 다시 시도하세요"

[개발팀 안 ① 시퀀스]      상세번호가 58214-3 이 아니라 58214-200117 처럼 찍힌다
[개발팀 안 ② LOCK TABLE]  번호를 받은 트랜잭션이 끝날 때까지 다른 주문의 상세 INSERT 가 전부 enq: TM - contention

문제

번호를 받는 SQL은 3블록짜리이고 인덱스도 제대로 탄다. 그런데도 중복이 나는 이유를 시간표로 설명하시오.
상세번호를 주문마다 1, 2, 3으로 매기는 규칙은 지키면서 중복이 나지 않게 고치시오. 개발팀의 두 안이 왜 답이 아닌지도 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- PKG_ORD.ADD_ITEM — 그 주문 행을 먼저 잠근다 (같은 주문의 요청끼리만 한 줄로 선다)
UPDATE T_ORD SET ORD_AMT = ORD_AMT + :qty * :prc
 WHERE ORD_SEQ = :ord_seq;

SELECT NVL(MAX(ORD_DET_NO), 0) + 1 INTO v_det_no      -- 그대로 3 블록 (MIN/MAX)
  FROM T_ORD_DET
 WHERE ORD_SEQ = :ord_seq;

INSERT INTO T_ORD_DET (ORD_SEQ, ORD_DET_NO, ITM_ID, ORD_QTY, UNT_PRC)
VALUES (:ord_seq, v_det_no, :itm_id, :qty, :prc);

COMMIT;

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

xplan

[고친 뒤 — 같은 「추가」 두 번 (학습용 예시)]
시각          요청 1 (SID 44)                                   요청 2 (SID 97)
10:12:03.10   UPDATE T_ORD (58214) → 행 락 · MAX+1 → 3 · INSERT
10:12:03.18                                                     UPDATE T_ORD (58214) → 기다림 (TX REQUEST 6)
10:12:03.21   COMMIT                                            → 깨어남 · MAX+1 → 4 · INSERT · COMMIT
  기다림 0.03초 · 중복 0 · 다른 주문의 「추가」는 기다리지 않는다 · 번호 받기는 그대로 3블록

모범 답안 서술

번호를 받는 SQL에는 문제가 없다. PK_T_ORD_DET에서 그 주문 범위의 맨 끝 한 건만 읽으므로(INDEX RANGE SCAN (MIN/MAX), FIRST ROW) 3블록이면 끝난다. 문제는 같은 주문의 요청 둘이 0.1초 안에 겹칠 때다. 요청 1이 넣은 상세번호 3은 COMMIT 전이라 요청 2의 MAX에는 보이지 않고, SELECT는 아무것도 잠그지 않으므로 요청 2도 3을 받는다. 요청 2의 INSERT는 PK에서 요청 1의 키와 부딪혀 TX를 REQUEST 4로 기다리고, 요청 1이 COMMIT하자 ORA-00001을 받는다. 아직 없는 상세 행은 미리 잠글 수도 없다. 그래서 이미 있는 행, 곧 부모인 T_ORD의 그 주문 행을 먼저 잠근다. 이 프로시저는 원래 마지막에 T_ORD를 UPDATE하므로 그 UPDATE를 맨 앞으로 옮기기만 하면 된다. 같은 주문의 두 번째 요청은 첫 요청이 COMMIT할 때까지 그 행에서 기다렸다가(0.03초) 4를 받는다. 잠그는 범위가 그 주문 하나라서 다른 주문을 고치는 상담원은 기다리지 않는다. 개발팀의 두 안은 답이 아니다. 시퀀스는 겹침은 없애지만 상세번호가 주문마다 1, 2, 3으로 이어지는 규칙을 깬다(58214-200117처럼 찍힌다). LOCK TABLE은 T_ORD_DET 전체에 TM 6을 걸어서, 번호를 받은 트랜잭션이 끝날 때까지 모든 주문의 상세 저장이 줄을 선다. 둘 다 같은 주문 안의 충돌을 막으려고 너무 넓게 잠근다.

해설

부모 행을 잠그는 처방은 번호의 범위가 좁을 때 맞다. 번호의 범위가 '주문 하나'라서 같은 주문의 요청끼리만 줄을 서고, 그 줄은 0.1초짜리 트랜잭션 뒤에 선다. 번호의 범위가 테이블 전체(모든 주문의 주문번호)라면 대표할 부모 행이 없으므로 그때는 시퀀스가 답이다(교재 12화 §6).
잠그는 순서를 정해 둔다. 같은 주문을 고치는 다른 프로시저가 T_ORD_DET를 먼저 고치고 T_ORD를 나중에 고치면, 엇갈린 순서 때문에 ORA-00060(데드락)이 날 수 있다. 주문을 고치는 모든 프로시저가 '주문 행 먼저'를 지키게 한다.
T_ORD에 마지막 상세번호 컬럼을 두고 UPDATE … SET LAST_DET_NO = LAST_DET_NO + 1 … RETURNING으로 받는 방법도 같은 원리다. MAX를 읽지 않아도 되지만 컬럼 하나와 그 값을 맞춰 두는 일이 는다.
상세를 지우는 화면이 있다면 MAX+1은 지운 맨 끝 번호를 다시 준다. 지운 번호가 배송 전표에 이미 찍혔다면, 맨 끝 번호를 다시 써도 되는지 업무 쪽과 정해 둔다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황