매장끼리 재고를 주고받는 마감 시간에 ORA-00060이 뜬다

기초UPDATE유통514 / 700

상황

본사 레거시 유통의 「매장 간 재고 이동」 화면은 매장 담당자가 남는 재고를 다른 매장으로 넘길 때 쓴다. 저녁 마감(21:00~22:00)에는 매장 여섯 곳이 서로 재고를 맞추느라 이동이 몰린다. 지난주부터 일주일에 두세 번 화면에 ORA-00060이 뜨고, 그때마다 담당자가 같은 이동을 다시 입력한다. 화면은 아래 프로시저를 부르고, 프로시저가 끝나면 COMMIT한다.

테이블과 인덱스

CF18 일별상품재고: 408행 · 6블록 · PK (SHP_NO, ITM_NO, INV_BS_DT) · 매장 6곳 × 상품 68개 · 이 화면은 기준일 행의 INV_QTY를 현재 재고로 고친다
CF15 입출고상품: 51,497행 · 349블록 · PK (INOUT_NO, ITM_NO) · 이동 전표 한 줄을 넣는다
CF07 매장: 6행 · SHP_NO 1000~1005
이동 하나는 ① 출고 매장 재고 빼기, ② 이동 전표 넣기, ③ 입고 매장 재고 더하기 순서다. 화면과 프로시저 사이는 문장마다 따로 오간다.

원본 SQL

sql

-- PRC_MOVE_STOCK(:from_shp, :to_shp, :itm, :qty, :dt, :inout_no) — 화면이 부르고, 끝나면 COMMIT
UPDATE CF18
   SET INV_QTY = INV_QTY - :qty, MOD_DTT = SYSDATE
 WHERE SHP_NO = :from_shp AND ITM_NO = :itm AND INV_BS_DT = :dt;      -- ① 출고 매장 재고 빼기

INSERT INTO CF15 (INOUT_NO, ITM_NO, INOUT_QTY, REG_DTT)
VALUES (:inout_no, :itm, :qty, SYSDATE);                               -- ② 이동 전표

UPDATE CF18
   SET INV_QTY = INV_QTY + :qty, MOD_DTT = SYSDATE
 WHERE SHP_NO = :to_shp AND ITM_NO = :itm AND INV_BS_DT = :dt;        -- ③ 입고 매장 재고 더하기
COMMIT;

실행계획 학습용 예시

xplan

[시간표 — 6월 14일 21:42 · 상품 1035 · 두 매장이 서로에게 넘겼다]
시각           매장 1000 담당자 (SID 37) : 1000 → 1003     매장 1003 담당자 (SID 52) : 1003 → 1000
21:42:10.120   ① CF18 (1000, 1035) 빼기 → 잠금
21:42:10.124                                               ① CF18 (1003, 1035) 빼기 → 잠금
21:42:10.131   ② 이동 전표
21:42:10.135                                               ② 이동 전표
21:42:10.140   ③ CF18 (1003, 1035) 더하기 → 기다림 (SID 52)
21:42:10.146                                               ③ CF18 (1000, 1035) 더하기 → 기다림 (SID 37)
21:42:13.410   ORA-00060 · ③ 한 문장만 되돌려짐
               화면이 ROLLBACK · 에러 창 → 다시 입력          → 깨어나 ③ · COMMIT

[SID 37 의 트레이스 — Deadlock graph]
                                          ------------Blocker(s)-----------  ------------Waiter(s)------------
Resource Name                             process session holds waits serial  process session holds waits serial
TX-00040012-000004A1-469D09C8-00000000         41      52     X        18822      38      37           X  31109
TX-00090007-00000B3C-469D09C8-00000000         38      37     X        31109      41      52           X  18822

Session 37:  current SQL: UPDATE CF18 SET INV_QTY = INV_QTY + :B5, MOD_DTT = SYSDATE WHERE SHP_NO = :B4 AND ITM_NO = :B3 AND INV_BS_DT = :B2
Session 52:  current SQL: (같은 문장)
Rows waited on:
  Session 37: obj - rowid = 0001A2F3 - AAAaLzAAMAAAACXAAk   (CF18 · SHP_NO 1003 · ITM_NO 1035)
  Session 52: obj - rowid = 0001A2F3 - AAAaLzAAMAAAACXAAA   (CF18 · SHP_NO 1000 · ITM_NO 1035)

[지난 4주 알림 로그] ORA-00060 9번 · 모두 PRC_MOVE_STOCK 의 ③ · 모두 21:00 ~ 22:00

문제

시간표와 데드락 트레이스를 근거로 원이 어떻게 생겼는지 설명하시오. 일주일에 두세 번만 나는 이유도 쓰시오.
이동 방향과 상관없이 원이 생기지 않도록 프로시저를 고치시오.

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

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

모범답안

모범 답안 SQL

sql

-- PRC_MOVE_STOCK — 두 매장 행을 매장 번호가 작은 쪽부터 먼저 잠근다 (이동 방향과 상관없이 늘 같은 순서)
SELECT INV_QTY INTO v_qty
  FROM CF18
 WHERE SHP_NO = LEAST(:from_shp, :to_shp) AND ITM_NO = :itm AND INV_BS_DT = :dt
   FOR UPDATE;
SELECT INV_QTY INTO v_qty
  FROM CF18
 WHERE SHP_NO = GREATEST(:from_shp, :to_shp) AND ITM_NO = :itm AND INV_BS_DT = :dt
   FOR UPDATE;

-- 두 행을 다 쥔 뒤에는 ① ② ③ 의 순서가 상관없다
UPDATE CF18 SET INV_QTY = INV_QTY - :qty, MOD_DTT = SYSDATE
 WHERE SHP_NO = :from_shp AND ITM_NO = :itm AND INV_BS_DT = :dt;
INSERT INTO CF15 (INOUT_NO, ITM_NO, INOUT_QTY, REG_DTT)
VALUES (:inout_no, :itm, :qty, SYSDATE);
UPDATE CF18 SET INV_QTY = INV_QTY + :qty, MOD_DTT = SYSDATE
 WHERE SHP_NO = :to_shp AND ITM_NO = :itm AND INV_BS_DT = :dt;
COMMIT;

-- 화면: ORA-00060 을 받으면 ROLLBACK 하고 한 번 더 시도한다 (사람이 다시 입력하지 않게)

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

xplan

[고친 뒤 — 같은 두 이동을 동시에 (학습용 예시)]
시각           매장 1000 담당자 (SID 37)                    매장 1003 담당자 (SID 52)
21:42:10.120   (1000, 1035) 잠금 → (1003, 1035) 잠금
21:42:10.124                                                (1000, 1035) 잠금 → 기다림 (SID 37)
21:42:10.151   ① ② ③ · COMMIT
21:42:10.152                                                깨어남 → (1003, 1035) 잠금 → ① ② ③ · COMMIT
               기다림 28 ms · 원이 생기지 않는다 · 두 매장 재고의 합은 그대로

모범 답안 서술

시간표를 보면 매장 1000 담당자(SID 37)는 ①에서 자기 출고 매장인 (1000, 1035) 행을 먼저 잠그고, 매장 1003 담당자(SID 52)는 자기 출고 매장인 (1003, 1035) 행을 먼저 잠갔다. 이어서 ③에서 서로 상대가 쥔 행을 원한다. 37은 52를, 52는 37을 기다리므로 원이 닫힌다. 트레이스의 Deadlock graph에서 두 줄이 서로를 가리키고(holds X, waits X는 TX 6, 곧 행 락이다), Rows waited on에 두 세션이 기다린 CF18 행이 하나씩 찍혀 있다. 같은 두 행을 이동 방향에 따라 반대 순서로 잡은 것이 원인이다. 일주일에 두세 번만 나는 것은 창이 짧아서다. ①에서 ③까지는 몇 ms이고, 같은 상품을 반대 방향으로 옮기는 두 이동이 그 몇 ms 안에 겹쳐야 원이 생긴다. 이동이 몰리는 마감 시간에만 그런 일이 생긴다. 1000이 1003에, 1003이 1005에, 1005가 1000에 같은 상품을 넘기는 식으로 셋 이상이 돌아가며 옮겨도 같은 원이 생긴다. ORA-00060은 먼저 기다리기 시작한 쪽(37)의 ③ 문장 하나만 되돌린다. 37의 트랜잭션은 ①의 행 락을 쥔 채 살아 있으므로 화면이 ROLLBACK해야 52가 이어 간다. 이 화면은 ROLLBACK을 하므로 52는 진행했고, 37의 담당자가 다시 입력했다. 고친 판은 이동 방향과 상관없이 두 매장 행을 매장 번호가 작은 쪽부터 FOR UPDATE로 먼저 잠근다(LEAST, GREATEST). 모든 이동이 같은 순서로 잡으므로 늦게 온 쪽은 첫 행에서 기다리기만 하고 원은 생기지 않는다(기다림은 몇십 ms). 두 행을 다 쥔 뒤의 ①, ②, ③은 순서가 상관없다. 화면도 ORA-00060을 받으면 ROLLBACK하고 한 번 더 시도하게 해 두면, 다른 자원에서 원이 생겨도 사람이 다시 입력하지 않는다.

해설

데드락을 없애는 기본은 같은 자원을 같은 순서로 잡는 것이다. 순서의 기준은 방향이나 화면과 상관없이 정해지는 값(키 순서)이어야 한다. '출고 먼저, 입고 나중'은 업무 순서라서 방향이 바뀌면 행의 순서도 바뀐다.
한 문장으로 두 행을 고치는 방법(WHERE SHP_NO IN (:a, :b)와 CASE)도 있다. 보통은 인덱스 순서로 행을 잠그지만 그 순서는 문서가 약속하지 않는다. 순서를 코드에 드러내는 쪽이 읽는 사람에게도 분명하다.
데드락은 줄을 풀어 주지 않는다. 원 하나를 끊을 뿐이다. 에러를 받은 쪽이 ROLLBACK하지 않고 COMMIT하면 ①만 반영되어 재고가 사라진다(교재 12화 §7).
데드락 트레이스는 에러를 받은 세션의 것만 남는다. 알림 로그의 ORA-00060 줄에서 트레이스 파일을 찾아 Deadlock graph와 Rows waited on을 읽는다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황