【PL/SQL】例外設計と再試行パターン｜一時障害・冪等性・SAVEPOINT・DBMS_SCHEDULER

CREATE OR REPLACE PACKAGE retry_errors AS
  deadlock_ex      EXCEPTION;
  lock_nowait_ex   EXCEPTION;
  serialize_ex     EXCEPTION;
  snapshot_old_ex  EXCEPTION;

  PRAGMA EXCEPTION_INIT(deadlock_ex,     -60);    -- ORA-00060
  PRAGMA EXCEPTION_INIT(lock_nowait_ex,  -54);    -- ORA-00054
  PRAGMA EXCEPTION_INIT(serialize_ex,    -8177);  -- ORA-08177
  PRAGMA EXCEPTION_INIT(snapshot_old_ex, -1555);  -- ORA-01555
END;
/

DECLARE
  c_max_attempts CONSTANT PLS_INTEGER := 5;
  v_attempt      PLS_INTEGER := 0;
  v_sleep_sec    NUMBER;
BEGIN
  LOOP
    v_attempt := v_attempt + 1;

    BEGIN
      process_payment(p_payment_id => :payment_id);
      COMMIT;
      EXIT;

    EXCEPTION
      WHEN retry_errors.lock_nowait_ex
        OR retry_errors.deadlock_ex
        OR retry_errors.serialize_ex THEN

        ROLLBACK;

        IF v_attempt >= c_max_attempts THEN
          logger_pkg.write_log(
            p_level       => 'ERROR',
            p_message     => 'retry limit exceeded',
            p_process_key => 'payment_id=' || :payment_id,
            p_sql_code    => SQLCODE,
            p_error_message => SQLERRM,
            p_backtrace   => DBMS_UTILITY.FORMAT_ERROR_BACKTRACE
          );
          RAISE;
        END IF;

        v_sleep_sec := POWER(2, v_attempt - 1)
                     + DBMS_RANDOM.VALUE(0, 0.5);

        DBMS_LOCK.SLEEP(v_sleep_sec);
    END;
  END LOOP;
END;
/

CREATE TABLE payment_request (
  request_id  VARCHAR2(100) PRIMARY KEY,
  order_id    NUMBER NOT NULL,
  amount      NUMBER(12,2) NOT NULL,
  status      VARCHAR2(20) NOT NULL,
  created_at  TIMESTAMP DEFAULT SYSTIMESTAMP,
  updated_at  TIMESTAMP
);

MERGE INTO payment_request t
USING (
  SELECT :request_id AS request_id,
         :order_id AS order_id,
         :amount AS amount
    FROM dual
) s
ON (t.request_id = s.request_id)
WHEN NOT MATCHED THEN
  INSERT (request_id, order_id, amount, status)
  VALUES (s.request_id, s.order_id, s.amount, 'WAITING')
WHEN MATCHED THEN
  UPDATE SET updated_at = SYSTIMESTAMP;

BEGIN
  update_order_header(:order_id);

  SAVEPOINT before_optional_coupon;

  BEGIN
    apply_coupon(:order_id, :coupon_code);
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      ROLLBACK TO before_optional_coupon;
      logger_pkg.write_log(
        p_level       => 'WARN',
        p_message     => 'coupon not applied',
        p_process_key => 'order_id=' || :order_id
      );
  END;

  update_order_total(:order_id);
  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
    RAISE;
END;

INSERT INTO target_orders(order_id, customer_id, amount)
SELECT order_id, customer_id, amount
  FROM staging_orders
 WHERE load_id = :load_id
LOG ERRORS INTO err$_target_orders ('LOAD_20260528')
REJECT LIMIT UNLIMITED;

COMMIT;

BEGIN
  FORALL i IN 1 .. l_orders.COUNT SAVE EXCEPTIONS
    UPDATE orders
       SET status = 'CANCELED'
     WHERE order_id = l_orders(i).order_id;

EXCEPTION
  WHEN OTHERS THEN
    FOR i IN 1 .. SQL%BULK_EXCEPTIONS.COUNT LOOP
      INSERT INTO batch_error_log(
        error_index,
        error_code,
        error_message
      ) VALUES (
        SQL%BULK_EXCEPTIONS(i).ERROR_INDEX,
        SQL%BULK_EXCEPTIONS(i).ERROR_CODE,
        SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE)
      );
    END LOOP;
    RAISE;
END;

BEGIN
  UPDATE orders
     SET status = 'CONFIRMED'
   WHERE order_id = :order_id;

  INSERT INTO event_outbox(
    event_id,
    event_type,
    aggregate_id,
    request_id,
    payload_json,
    status,
    retry_count,
    next_retry_at,
    created_at
  ) VALUES (
    event_seq.NEXTVAL,
    'ORDER_CONFIRMED',
    :order_id,
    :request_id,
    :payload_json,
    'WAITING',
    0,
    SYSTIMESTAMP,
    SYSTIMESTAMP
  );

  COMMIT;
END;

BEGIN
  FOR r IN (
    SELECT event_id
      FROM event_outbox
     WHERE status = 'WAITING'
       AND next_retry_at <= SYSTIMESTAMP
     ORDER BY event_id
     FOR UPDATE SKIP LOCKED
  ) LOOP
    BEGIN
      send_event(r.event_id);

      UPDATE event_outbox
         SET status = 'SENT',
             sent_at = SYSTIMESTAMP
       WHERE event_id = r.event_id;

    EXCEPTION
      WHEN OTHERS THEN
        UPDATE event_outbox
           SET retry_count = retry_count + 1,
               next_retry_at = SYSTIMESTAMP + NUMTODSINTERVAL(5, 'MINUTE'),
               last_error = SQLERRM
         WHERE event_id = r.event_id;
    END;
  END LOOP;

  COMMIT;
END;

EXCEPTION
  WHEN OTHERS THEN
    logger_pkg.write_log(
      p_level         => 'ERROR',
      p_message       => 'order import failed',
      p_process_key   => 'load_id=' || :load_id,
      p_sql_code      => SQLCODE,
      p_error_message => SQLERRM,
      p_backtrace     => DBMS_UTILITY.FORMAT_ERROR_BACKTRACE
    );
    ROLLBACK;
    RAISE;

BEGIN
  run_business_process(:request_id);
  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
    logger_pkg.write_log(
      p_level         => 'ERROR',
      p_message       => 'business process failed',
      p_process_key   => 'request_id=' || :request_id,
      p_sql_code      => SQLCODE,
      p_error_message => SQLERRM,
      p_backtrace     => DBMS_UTILITY.FORMAT_ERROR_BACKTRACE
    );
    RAISE;
END;