SERIALIZABLE로 바꿨는데도 거래처 발주 한도가 또 넘쳤다

실전PLSQL유통534 / 700

상황

본사 레거시 유통은 거래처마다 신용 한도를 두고, 입고되지 않은 발주 금액의 합이 한도를 넘으면 발주 등록을 막는다. MD 네 명이 같은 거래처의 발주를 동시에 등록하는 일이 잦다. 지난달 한도 3,000만 원인 거래처 P007의 미입고 발주 합계가 3,412만 원이 되어 거래처가 출고를 멈췄다. 조민우는 "동시에 읽어서 생긴 일"이라며 발주 등록 프로시저를 SERIALIZABLE로 바꿨다. 이번 달에도 같은 거래처가 3,310만 원이 되었다. 에러 로그에 ORA-08177은 한 번도 없다.

테이블과 인덱스

CF12 발주: 734행 · 10블록 · CF12_PK (ORD_NO) · PTNR_NO · ORD_PRGS_CD(10 발주완료, 11 입고중, 12 입고완료) · 이 문제에서는 발주 금액 ORD_AMT와 인덱스 IX_CF12_PTNR (PTNR_NO, ORD_PRGS_CD)를 더했다
CF12_PTNR 거래처 한도(이 문제를 위해 더한 테이블): PTNR_NO PK · CRDT_LMT 신용 한도 · 거래처 30곳
발주 등록 한 번은 10ms 안쪽이다. 입고가 끝나면 입고 화면이 ORD_PRGS_CD를 12로 바꾼다. 발주 금액을 고치는 「발주 수정」 화면도 있다.

원본 SQL

sql

PROCEDURE REG_PO(p_ptnr VARCHAR2, p_no VARCHAR2, p_amt NUMBER) IS
  v_lmt  NUMBER;
  v_open NUMBER;
BEGIN
  SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;      -- 지난달 한도 초과 뒤 추가

  SELECT CRDT_LMT INTO v_lmt FROM CF12_PTNR WHERE PTNR_NO = p_ptnr;

  SELECT NVL(SUM(ORD_AMT), 0) INTO v_open
    FROM CF12
   WHERE PTNR_NO = p_ptnr AND ORD_PRGS_CD IN ('10', '11');

  IF v_open + p_amt > v_lmt THEN
    RAISE_APPLICATION_ERROR(-20041, '거래처 한도를 넘습니다');
  END IF;

  INSERT INTO CF12 (ORD_NO, ORD_DT, PTNR_NO, ORD_PRGS_CD, ORD_AMT)
  VALUES (p_no, TO_CHAR(SYSDATE, 'YYYYMMDD'), p_ptnr, '10', p_amt);
  COMMIT;
END;

실행계획 학습용 예시

xplan

[시간표 — 거래처 P007 · 한도 30,000,000 · 미입고 26,200,000]
시각           MD 1 (SID 52) · 발주 3,800,000                    MD 2 (SID 87) · 발주 3,100,000
14:05:11.300   SET TRANSACTION … SERIALIZABLE
14:05:11.302                                                      SET TRANSACTION … SERIALIZABLE
14:05:11.303   SUM → 26,200,000 · + 3,800,000 = 30,000,000 ≤ 한도
14:05:11.304                                                      SUM → 26,200,000 · + 3,100,000 = 29,300,000 ≤ 한도
14:05:11.305   INSERT 발주 2023121407 · COMMIT
14:05:11.306                                                      INSERT 발주 2023121408 · COMMIT
               → 미입고 33,100,000 (한도 초과) · 기다림 0 · 에러 0

[V$LOCK — 14:05:11.305 (두 세션이 모두 INSERT 한 순간)]
SID   TYPE  대상                  LMODE  REQUEST  BLOCK
52    TM    CF12                    3      0        0
52    TX    52 의 트랜잭션           6      0        0      ← 새 행 2023121407 하나
87    TM    CF12                    3      0        0
87    TX    87 의 트랜잭션           6      0        0      ← 새 행 2023121408 하나
      (두 세션이 같은 행을 고치지 않는다 — 서로 기다릴 일도 ORA-08177 이 날 일도 없다)

[한도 초과가 된 등록]  지난달 34,120,000 (READ COMMITTED 일 때) · 이번 달 33,100,000 (SERIALIZABLE 로 바꾼 뒤)

문제

시간표와 V$LOCK을 근거로 두 등록이 모두 한도 검사를 통과한 이유와, SERIALIZABLE이 이를 막지 못한 이유를 설명하시오.
한도를 넘는 발주가 들어가지 않도록 고치는 방법을 둘 이상 비교하고, 하나를 골라 SQL로 작성하시오. 고른 방법의 대가도 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

PROCEDURE REG_PO(p_ptnr VARCHAR2, p_no VARCHAR2, p_amt NUMBER) IS
  v_lmt  NUMBER;
  v_open NUMBER;
BEGIN
  -- 격리 수준은 기본(READ COMMITTED)으로 되돌린다
  -- ① 그 거래처의 한도 행을 먼저 잠근다 — 같은 거래처의 등록끼리만 한 줄로 선다
  SELECT CRDT_LMT INTO v_lmt
    FROM CF12_PTNR
   WHERE PTNR_NO = p_ptnr
     FOR UPDATE;

  -- ② 잠근 뒤에 합계를 읽는다 — 새 문장이라 앞사람이 COMMIT 한 발주까지 보인다
  SELECT NVL(SUM(ORD_AMT), 0) INTO v_open
    FROM CF12
   WHERE PTNR_NO = p_ptnr AND ORD_PRGS_CD IN ('10', '11');

  IF v_open + p_amt > v_lmt THEN
    ROLLBACK;
    RAISE_APPLICATION_ERROR(-20041, '거래처 한도를 넘습니다');
  END IF;

  INSERT INTO CF12 (ORD_NO, ORD_DT, PTNR_NO, ORD_PRGS_CD, ORD_AMT)
  VALUES (p_no, TO_CHAR(SYSDATE, 'YYYYMMDD'), p_ptnr, '10', p_amt);
  COMMIT;
END;
-- 「발주 수정」 화면도 금액을 늘리기 전에 같은 한도 행을 같은 순서로 먼저 잠근다

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

xplan

[고친 뒤 — 같은 두 등록 (학습용 예시)]
14:05:11.303   MD 1  CF12_PTNR 'P007' FOR UPDATE → 잠금 · SUM 26,200,000 · INSERT · COMMIT 14:05:11.309
14:05:11.304   MD 2  CF12_PTNR 'P007' FOR UPDATE → 기다림 (TX 6 · 약 5ms)
14:05:11.309   MD 2  깨어남 · SUM → 30,000,000 (MD 1 의 발주가 보인다) · + 3,100,000 > 한도 → '거래처 한도를 넘습니다'
  미입고 합계 30,000,000 · 다른 거래처의 등록은 기다리지 않는다 · 같은 거래처끼리만 몇 ms 줄을 선다

모범 답안 서술

두 MD는 거의 같은 순간에 같은 합계 26,200,000을 읽었다. MD 2가 SUM을 읽을 때 MD 1의 발주는 COMMIT 전이라 보이지 않았다. 그래서 둘 다 자기 발주만 더한 30,000,000과 29,300,000을 한도와 비교해 통과했고, 각자 새 발주 행을 INSERT했다. 둘을 합치면 33,100,000이다. 각자는 규칙을 지켰는데 합쳐서 규칙이 깨지는 이 모양을 쓰기 스큐(write skew)라고 한다. SERIALIZABLE은 이것을 막지 못한다. 오라클의 SERIALIZABLE은 트랜잭션 시작 시점의 데이터를 보여 주고, 시작 뒤에 다른 트랜잭션이 고친 같은 행을 고치려 할 때만 ORA-08177을 낸다. V$LOCK을 보면 두 세션이 쥔 TX는 각자 새로 넣은 행 하나뿐이고, 같은 행을 고치지 않는다. 부딪칠 행이 없으니 기다림도 에러도 없이 둘 다 COMMIT했다. 오히려 SERIALIZABLE에서는 트랜잭션 내내 시작 시점의 합계를 보므로, 나중에 다시 읽어도 남의 발주를 보지 못한다. 고치는 방법 둘을 비교한다. ① 거래처 한도 행을 먼저 잠근다. 같은 거래처의 등록은 반드시 같은 행에서 부딪치므로 한 줄로 서고, 뒷사람은 앞사람이 COMMIT한 뒤에 합계를 읽는다. 이때 격리 수준은 READ COMMITTED여야 한다. READ COMMITTED에서는 잠금을 얻은 뒤 실행하는 SUM이 새 문장이라 앞사람의 발주까지 보지만, SERIALIZABLE에 두면 잠근 뒤에도 시작 시점의 합계를 본다. ② 한도 테이블에 미입고 합계 칸 OPEN_AMT를 두고 UPDATE CF12_PTNR SET OPEN_AMT = OPEN_AMT + :amt WHERE PTNR_NO = :p AND OPEN_AMT + :amt <= CRDT_LMT로 확인과 갱신을 한 문장에 한다. 합계를 매번 읽지 않아 빠르지만, 입고 완료와 발주 취소, 금액 수정이 모두 OPEN_AMT를 맞춰야 하고 하나라도 빠지면 칸이 실제 합계와 어긋난다. ①을 고른다. 등록은 10ms 안쪽이라 같은 거래처끼리 몇 ms 줄을 서는 대가가 작고, 잠그는 것은 그 거래처 한 행뿐이라 다른 거래처의 등록은 기다리지 않는다. 합계는 늘 CF12에서 바로 읽으므로 어긋날 칸도 없다. 대가는 발주 금액을 늘리는 다른 프로그램(발주 수정 화면)도 같은 순서로 한도 행을 먼저 잠가야 한다는 것이다. 한 곳이라도 빠지면 그 길로 다시 넘친다.

해설

오라클의 SERIALIZABLE은 스냅샷 격리다. 같은 행을 두 트랜잭션이 고치는 충돌(갱신 손실)은 막지만, 서로 다른 행을 쓰면서 서로가 읽은 조건을 깨는 쓰기 스큐는 막지 않는다. 규칙이 여러 행의 합이나 개수에 걸려 있으면 격리 수준만으로는 지킬 수 없다.
거래처별 합계를 구체화 뷰(ON COMMIT 리프레시)로 두고 CHECK 제약을 거는 선언적 방법도 있다. 규칙을 DB가 지키지만, COMMIT마다 뷰를 고치느라 같은 거래처의 COMMIT이 한 줄로 서고 구조가 복잡해진다.
LOCK TABLE CF12 IN EXCLUSIVE MODE로 줄을 세우면 모든 거래처의 발주 등록과 입고 화면까지 막는다. 줄은 규칙의 범위(거래처 하나)만큼만 세운다.
잠그는 순서도 정해 둔다. 한도 행 다음에 발주 행이라는 순서를 모든 프로그램이 지키지 않으면, 발주를 먼저 고치고 한도를 나중에 잠그는 프로그램과 엇갈려 ORA-00060이 날 수 있다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황