판매 중지로 바꾼 상품이 다음 날 판매 중으로 돌아와 있다

중급UPDATE유통527 / 700

상황

본사 레거시 유통의 MD는 「상품 정보」 화면에서 상품명, 분류, 거래처, 판매 여부를 고친다. 2년 전 동시 수정 사고 뒤 CF01에 수정일시 칸 CHG_DT(DATE)를 더하고, 화면을 연 뒤로 CHG_DT가 바뀌지 않았을 때만 저장하게 했다. 본사 전산 담당자의 설계 메모에는 "수정일시로 충돌 감지, 0건이면 재조회"라고만 적혀 있다. 그런데 지난달 재고팀이 판매 중지한 상품 둘이 다음 날 다시 판매 중이었고, 거래처가 바꾼 상품 분류 하나가 옛 값으로 돌아갔다. 그사이 MD 화면이 그 상품들을 저장한 기록이 있고, 저장은 모두 성공했다.

테이블과 인덱스

CF01 상품: 109행 · 5블록 · PK ITM_NO · 이 문제에서는 CHG_DT DATE(마지막 수정 시각)를 더했다
MD 화면: 저장할 때 화면의 칸을 전부 SET에 넣고 CHG_DT = SYSDATE로 고친다. 지난달 저장은 412번이다.
재고팀 「판매 중지」: 재고가 바닥난 상품을 하루 두 번(09:00, 16:00) 한 문장으로 판매 중지한다. 이 SQL은 CHG_DT를 고치지 않는다.
거래처 연동 배치: 5분마다 거래처 시스템의 상품명과 분류를 받아 상품마다 UPDATE 두 문장(상품명, 분류)을 실행하고 문장마다 COMMIT한다. 두 문장 모두 CHG_DT = SYSDATE로 고친다.

원본 SQL

sql

-- MD 「상품 정보」 화면: 열 때
SELECT ITM_NO, ITM_NM, ITM_CL_CD, CTG_NO, PTNR_NO, SLE_YN, CHG_DT
  FROM CF01
 WHERE ITM_NO = :itm_no;

-- 저장 (0행이면 "다른 사용자가 먼저 고쳤습니다" → 다시 읽는다)
UPDATE CF01
   SET ITM_NM = :itm_nm, ITM_CL_CD = :cl_cd, CTG_NO = :ctg_no,
       PTNR_NO = :ptnr_no, SLE_YN = :sle_yn, CHG_DT = SYSDATE
 WHERE ITM_NO = :itm_no
   AND CHG_DT = :chg_dt;
COMMIT;

-- 재고팀 「판매 중지」 (09:00 · 16:00)
UPDATE CF01 SET SLE_YN = 'N'
 WHERE ITM_NO IN (SELECT ITM_NO FROM CF18 WHERE INV_BS_DT = :today
                   GROUP BY ITM_NO HAVING SUM(INV_QTY) = 0);
COMMIT;

-- 거래처 연동 배치 (5분마다 · 상품마다 두 문장 · 문장마다 COMMIT)
UPDATE CF01 SET ITM_NM = :nm, CHG_DT = SYSDATE WHERE ITM_NO = :itm_no;
COMMIT;
UPDATE CF01 SET ITM_CL_CD = :cl, CHG_DT = SYSDATE WHERE ITM_NO = :itm_no;
COMMIT;

실행계획 학습용 예시

xplan

[사례 ① — 상품 42번 · 12월 4일 · 판매 중지가 되살아났다]
시각         MD (SID 66)                                       재고팀 (SID 140)
08:57:12     화면 열기 → SLE_YN 'Y' · CHG_DT 12-03 17:42:10
09:00:00                                                       판매 중지 UPDATE SLE_YN = 'N' · COMMIT
                                                               (CHG_DT 는 12-03 17:42:10 그대로)
09:03:41     상품명만 고쳐 저장 → WHERE CHG_DT = 12-03 17:42:10 → 1행 · SLE_YN 'Y' 로 다시 쓴다

[사례 ② — 상품 77번 · 12월 11일 · 분류가 옛 값으로 돌아갔다]
시각            연동 배치 (SID 201)                                 MD (SID 66)
10:15:07.10     UPDATE ITM_NM … CHG_DT = SYSDATE · COMMIT
                → CHG_DT 12-11 10:15:07
10:15:07.40                                                         화면 열기 → 분류 '0301' · CHG_DT 12-11 10:15:07
10:15:07.85     UPDATE ITM_CL_CD = '0305' … CHG_DT = SYSDATE · COMMIT
                → CHG_DT 12-11 10:15:07 (같은 초)
10:16:30                                                            거래처만 고쳐 저장 → WHERE CHG_DT = 12-11 10:15:07 → 1행
                                                                    분류 '0301' 로 되돌아갔다

[지난달]  MD 저장 412번 · 0행으로 막힌 저장 6번 · 막혔어야 했는데 통과한 저장 3번 (사례 ① 2번 · 사례 ② 1번)

문제

두 사례에서 CHG_DT 검사가 왜 충돌을 잡지 못했는지 각각 설명하시오.
누가 어떤 SQL로 고치든 MD 화면이 충돌을 잡도록 고치시오. CHG_DT를 TIMESTAMP로 바꾸자는 의견에도 답하시오.

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

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

모범답안

모범 답안 SQL

sql

-- 버전 칸 — 누가 어떤 SQL로 고치든 트리거가 1씩 올린다 (쓰는 쪽이 잊어도 빠지지 않는다)
ALTER TABLE CF01 ADD (VER_NO NUMBER DEFAULT 0 NOT NULL);

CREATE OR REPLACE TRIGGER TRG_CF01_VER
BEFORE UPDATE ON CF01
FOR EACH ROW
BEGIN
  :NEW.VER_NO := :OLD.VER_NO + 1;
END;
/

-- MD 화면: 열 때 VER_NO 를 함께 읽고, 저장은 그 버전일 때만 (CHG_DT 는 '언제 고쳤나' 표시로만 남긴다)
SELECT ITM_NO, ITM_NM, ITM_CL_CD, CTG_NO, PTNR_NO, SLE_YN, CHG_DT, VER_NO
  FROM CF01
 WHERE ITM_NO = :itm_no;

UPDATE CF01
   SET ITM_NM = :itm_nm, ITM_CL_CD = :cl_cd, CTG_NO = :ctg_no,
       PTNR_NO = :ptnr_no, SLE_YN = :sle_yn, CHG_DT = SYSDATE
 WHERE ITM_NO = :itm_no
   AND VER_NO = :ver_no;
-- 0행이면 COMMIT 하지 않고 "다른 곳에서 먼저 고쳤습니다" → 다시 읽어 보여 준다
COMMIT;

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

xplan

[고친 뒤 — 같은 두 사례 (학습용 예시)]
①  08:57:12 화면 열기 VER_NO 14 → 09:00:00 판매 중지 (트리거가 15 로 올린다)
   → 09:03:41 저장 WHERE VER_NO = 14 → 0행 → 다시 읽기 (SLE_YN 'N')
②  10:15:07.10 연동 상품명 (VER_NO 31) → 10:15:07.40 화면 열기 31 → 10:15:07.85 연동 분류 (32)
   → 10:16:30 저장 WHERE VER_NO = 31 → 0행 → 다시 읽기 (분류 '0305')
  막혔어야 했는데 통과한 저장 3번 → 0 · 판매 중지 SQL 과 연동 배치는 고치지 않았다

모범 답안 서술

저장 UPDATE의 AND CHG_DT = :chg_dt는 "화면을 연 뒤로 누가 고쳤다면 CHG_DT가 바뀌었을 것"이라는 가정 위에 서 있다. 두 사례는 이 가정이 깨지는 두 가지 모양이다. 사례 ①에서 재고팀의 판매 중지 SQL은 SLE_YN만 고치고 CHG_DT는 고치지 않는다. MD가 08:57:12에 화면을 연 뒤 09:00에 판매 중지가 COMMIT됐지만 CHG_DT는 그대로였으므로, 09:03:41의 저장은 검사를 통과해 화면에 남아 있던 SLE_YN = 'Y'를 다시 썼다. 검사 칸을 고치지 않는 쓰기가 하나라도 있으면 그 쓰기는 낙관적 잠금에 보이지 않는다. 사례 ②는 DATE의 정밀도 문제다. DATE는 초 단위까지만 담는다. 연동 배치의 두 문장이 같은 초(10:15:07) 안에 COMMIT되고 MD가 그 사이에 화면을 열었으므로, 두 번째 변경 뒤에도 CHG_DT는 MD가 읽은 값과 같았다. 그래서 거래처가 바꾼 분류가 옛 값 '0301'로 되돌아갔다. 고친 판은 검사 칸을 시각이 아니라 고칠 때마다 1씩 오르는 버전 VER_NO로 바꾸고, BEFORE UPDATE 행 트리거가 :NEW.VER_NO := :OLD.VER_NO + 1로 올리게 한다. 누가 어떤 SQL로 고치든 트리거가 빠짐없이 버전을 올리므로 판매 중지 SQL과 연동 배치를 고치지 않아도 되고, 같은 초에 두 번 고쳐도 버전은 두 번 오른다. MD 화면은 열 때 VER_NO를 읽고 AND VER_NO = :ver_no로 저장한다. 두 사례 모두 0행이 되어 "다른 곳에서 먼저 고쳤습니다"를 띄우고 다시 읽는다. 지난달 통과한 3번은 모두 막힌다. CHG_DT를 TIMESTAMP로 바꾸고 SYSTIMESTAMP로 적자는 의견은 반쪽짜리다. 같은 초의 겹침은 마이크로초로 줄지만, 판매 중지 SQL처럼 검사 칸을 고치지 않는 쓰기는 그대로 놓친다. CHG_DT는 '언제 고쳤나'를 보여 주는 용도로 남겨 둔다. 트리거의 비용은 109행 테이블을 하루 수백 번 고치는 정도라 무시할 만하다.

해설

낙관적 잠금의 검사 칸은 모든 쓰기가 바꿔야 한다. 화면만 지키는 규칙은 배치, 운영자가 손으로 돌린 SQL, 다른 시스템의 연동에서 깨진다. 데이터베이스가 강제하는 트리거로 지키거나, 쓰는 길을 하나로 모은다.
트리거 대신 모든 UPDATE에 VER_NO = VER_NO + 1을 넣어도 된다. 트리거보다 가볍지만 이번 사고의 원인(누군가 빠뜨린다)이 그대로 남는다.
트리거를 두었다면 저장 SQL에서 VER_NO를 또 올리지 않는다. 둘 다 올리면 한 번 고칠 때 2씩 오른다. 동작은 맞지만 버전을 세어 보는 사람을 헷갈리게 한다.
ORA_ROWSCN을 검사 칸으로 쓰려면 테이블을 ROWDEPENDENCIES로 다시 만들어야 행 단위가 된다. 109행 테이블이라 다시 만들기는 쉽지만, 칸 하나를 더하는 편이 모든 개발자가 이해하기 쉽다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황