취소된 주문의 상세가 그대로 남았고, 그날 주문 수정 화면은 4분을 기다렸다

모범 답안 SQL ​

-- 「주문 수정」 화면: 주문 행을 먼저 잠근다 (주문을 고치는 모든 프로그램이 '주문 → 상세' 순서)
SELECT ORD_SEQ INTO v_seq
  FROM T_ORD
 WHERE ORD_SEQ = :ord_seq
   FOR UPDATE;
UPDATE T_ORD_DET SET ORD_QTY = :qty
 WHERE ORD_SEQ = :ord_seq AND ORD_DET_NO = :det_no;
UPDATE T_ORD
   SET ORD_AMT = (SELECT SUM(ORD_QTY * UNT_PRC) FROM T_ORD_DET WHERE ORD_SEQ = :ord_seq)
 WHERE ORD_SEQ = :ord_seq;
COMMIT;

-- 「결제 취소 반영」 배치: 통보 한 건이 트랜잭션 하나 · 실패하면 그 건의 일을 전부 되돌린다
DECLARE
  TYPE t_list IS TABLE OF T_PG_CNCL.ORD_SEQ%TYPE;
  v_list t_list;
  e_deadlock EXCEPTION;
  PRAGMA EXCEPTION_INIT(e_deadlock, -60);
BEGIN
  SELECT ORD_SEQ BULK COLLECT INTO v_list
    FROM T_PG_CNCL WHERE DONE_YN = 'N' ORDER BY RCV_DT;
  FOR i IN 1 .. v_list.COUNT LOOP
    PKG_PG.CHECK_CANCEL(v_list(i));                      -- 외부 확인은 락을 잡기 전에
    BEGIN
      UPDATE T_ORD     SET ORD_ST = 'CANCEL' WHERE ORD_SEQ = v_list(i);   -- 주문 → 상세
      UPDATE T_ORD_DET SET ORD_QTY = 0       WHERE ORD_SEQ = v_list(i);
      UPDATE T_PG_CNCL SET DONE_YN = 'Y'     WHERE ORD_SEQ = v_list(i);   -- 다 고친 뒤에 표시
      COMMIT;                                            -- 한 건마다: 락을 쥐는 시간이 몇 ms
    EXCEPTION
      WHEN e_deadlock THEN
        ROLLBACK;                                        -- ORA-00060 은 문장 하나만 되돌린다 → 이 건의 앞 문장까지
        PKG_LOG.WRITE('DEADLOCK RETRY ' || v_list(i));   -- DONE_YN 'N' 그대로 → 다음 회차에 다시
    END;
  END LOOP;
END;

개선 후 실행계획 학습용 예시 · ORA-00060의 되돌림 범위는 교재 12화 실측 ​

[고친 뒤 — 같은 10:15 (학습용 예시)]
시각           상담원 화면 (SID 88)                         결제 취소 반영 배치 (SID 41)
10:15:20.0     T_ORD 88213 FOR UPDATE → 잠금 · 상세 2번 고침
10:15:20.3                                                 UPDATE T_ORD (88213) → 기다림 (SID 88) · 원은 생기지 않는다
10:15:21.0     주문 금액 갱신 · COMMIT
10:15:21.0                                                 깨어남 → 주문 · 상세 · DONE_YN · COMMIT
  배치가 행을 쥐는 시간  통보 한 건에 2 ~ 5 ms (300건을 4분 쥐던 것)    화면의 기다림  4분 6초 → 0.7초

모범 답안 서술 ​

화면은 상세(T_ORD_DET) → 주문(T_ORD) 순서로 고치고, 배치는 주문 → 상세 순서로 고친다. 같은 주문 88213에서 화면은 상세 2번 행을 쥔 채 주문 행을 원하고, 배치는 주문 행을 쥔 채 상세 2번 행을 원하므로 서로를 기다린다. 순서가 반대라서 원이 생겼다. 먼저 기다리기 시작한 배치(10:15:20.3)가 약 3초 뒤 ORA-00060을 받았다. 그런데 ORA-00060은 문장 하나(배치의 UPDATE T_ORD_DET)만 되돌린다. 배치의 트랜잭션은 살아 있고, 앞서 고친 T_ORD 88213 행과 T_PG_CNCL 행의 락을 그대로 쥐고 있다. 예외 처리는 로그만 남기고 다음 통보로 넘어가며, COMMIT은 300건을 다 처리한 10:19:27에 한 번 한다. 화면은 그때까지 배치의 TX를 기다렸다(V$SESSION의 blocking_session 41, 4분 6초). 데드락 에러는 원 하나를 끊을 뿐 줄을 풀어 주지 않는다. 데이터가 어긋난 이유도 같다. 되돌려진 것은 상세 UPDATE뿐이고, 같은 트랜잭션의 앞 문장(DONE_YN = 'Y', ORD_ST = 'CANCEL')은 10:19:27에 그대로 커밋됐다. 그래서 주문은 CANCEL인데 상세 수량은 그대로다. 게다가 DONE_YN을 먼저 'Y'로 바꿔 두었으므로 '재시도는 다음 회차'라는 주석과 달리 다음 회차에 다시 잡히지 않는다. 고친 판은 두 가지다. 첫째, 주문을 고치는 모든 프로그램이 주문 → 상세 순서로 잡는다. 화면은 상세를 고치기 전에 SELECT … FROM T_ORD … FOR UPDATE로 주문 행을 먼저 잠근다(주문 행을 그 주문의 대표 락으로 쓴다). 그러면 늦게 온 쪽은 주문 행에서 기다리기만 하고 원이 생기지 않는다. 둘째, 배치는 통보 한 건을 트랜잭션 하나로 처리한다. 주문, 상세, DONE_YN을 고친 뒤 바로 COMMIT하고, ORA-00060을 받으면 ROLLBACK해서 그 건의 앞 문장까지 되돌린다. DONE_YN은 'N'으로 남으므로 다음 회차에 다시 처리된다. PG 확인은 락을 잡기 전에 하므로 배치가 행을 쥐는 시간은 통보 한 건에 몇 ms로 준다.

해설 ​

• 함정: 예외를 잡는 것과 되돌리는 것은 다르다. PL/SQL 블록 안에서 잡은 예외는 그 문장만 되돌린 채 트랜잭션을 이어 간다. 여러 문장이 한 묶음이어야 하는 일(주문, 상세, 처리 표시)은 실패하면 묶음 전체를 ROLLBACK하거나 SAVEPOINT로 되돌려야 한다.
• 300건을 한 트랜잭션으로 묶은 대가: '중간에 죽으면 전부 다시'를 노린 것이겠지만, 그 대신 4분 동안 300건의 주문 행을 쥐고 PG 응답까지 기다린다. 건마다 COMMIT하고 DONE_YN으로 어디까지 했는지 남기면 다시 돌려도 안전하고 락도 짧다(교재 13화의 반쯤 반영).
• 배치를 상세 → 주문 순서로 바꿔도 순서는 맞출 수 있다. 하지만 상세가 여러 행이면 상세끼리의 잠금 순서까지 맞춰야 하므로, 행이 하나뿐인 주문을 대표 락으로 쓰는 쪽이 단순하다. 이 규칙("주문을 고치는 프로그램은 T_ORD를 먼저 잠근다")은 코드 리뷰 기준으로 남겨야 다음 화면이 다시 어기지 않는다.
• ORA-00060을 받는 쪽은 먼저 기다린 쪽이다. 오늘은 배치였지만 화면이 받을 수도 있다. 화면도 ORA-00060을 받으면 ROLLBACK하고 다시 시도하게 짠다(교재 12화 §7).

관련 교재 ​

• 12화 · 아무도 안 건드렸는데 전체가 멈췄다
• 13화 · 안전 조치의 대가