【PL/SQL】SAVEPOINTで部分ロールバックを実装する完全ガイド｜内部動作・4業務パターン・Saga・JDBC連携・AUTONOMOUS比較

-- 準備
CREATE TABLE t(id NUMBER, val VARCHAR2(20));
CREATE SEQUENCE seq_t START WITH 100 INCREMENT BY 1;

DECLARE
  v_seq1 NUMBER;
  v_seq2 NUMBER;
  v_seq3 NUMBER;
BEGIN
  -- 開始：何もない状態
  INSERT INTO t VALUES (1, 'A');
  v_seq1 := seq_t.NEXTVAL;     -- 100が採番される
  DBMS_OUTPUT.PUT_LINE('seq1=' || v_seq1);

  SAVEPOINT sp1;               -- ★しおり①

  INSERT INTO t VALUES (2, 'B');
  v_seq2 := seq_t.NEXTVAL;     -- 101が採番される
  DBMS_OUTPUT.PUT_LINE('seq2=' || v_seq2);

  SAVEPOINT sp2;               -- ★しおり②

  INSERT INTO t VALUES (3, 'C');
  v_seq3 := seq_t.NEXTVAL;     -- 102が採番される
  DBMS_OUTPUT.PUT_LINE('seq3=' || v_seq3);

  -- sp1まで戻す
  ROLLBACK TO SAVEPOINT sp1;

  -- 何が残っている？
  --   テーブル：(1, 'A') だけ。(2,'B') と (3,'C') は戻る
  --   PL/SQL変数：v_seq1=100, v_seq2=101, v_seq3=102 全て残る
  --   シーケンス：次は103から（採番済みの100,101,102は消費済み）
  --   sp2：消える（sp1より新しいSAVEPOINTは無効化）
  --   sp1：残る（同じSAVEPOINTには何度でも戻れる）

  -- 続行可能：トランザクションは継続中
  INSERT INTO t VALUES (4, 'D');
  COMMIT;
END;
/

DECLARE
  v_ok PLS_INTEGER := 0;
  v_ng PLS_INTEGER := 0;
BEGIN
  FOR rec IN (SELECT id, name, email FROM staging_data) LOOP
    SAVEPOINT before_row;
    BEGIN
      INSERT INTO customers(id, name, email)
      VALUES(rec.id, rec.name, rec.email);
      v_ok := v_ok + 1;
    EXCEPTION
      WHEN DUP_VAL_ON_INDEX THEN
        ROLLBACK TO before_row;
        INSERT INTO err_log(staging_id, reason, ts)
        VALUES(rec.id, 'duplicate', SYSTIMESTAMP);
        v_ng := v_ng + 1;
      WHEN OTHERS THEN
        ROLLBACK TO before_row;
        INSERT INTO err_log(staging_id, reason, ts)
        VALUES(rec.id, SUBSTR(SQLERRM,1,200), SYSTIMESTAMP);
        v_ng := v_ng + 1;
    END;
  END LOOP;

  COMMIT;
  DBMS_OUTPUT.PUT_LINE('OK=' || v_ok || ' / NG=' || v_ng);
END;
/

CREATE OR REPLACE PROCEDURE multi_step_process(
  p_order_id IN NUMBER
) AS
  e_validation_failed EXCEPTION;
BEGIN
  -- ステップ1：注文の在庫予約（必ず成功させたい）
  pkg_inventory.reserve(p_order_id);
  SAVEPOINT after_step1;

  -- ステップ2：与信チェック（失敗したら2だけ取消、1は残す）
  BEGIN
    pkg_credit.check_and_freeze(p_order_id);
  EXCEPTION
    WHEN pkg_credit.e_credit_exceeded THEN
      ROLLBACK TO after_step1;
      pkg_log.warn('credit failed for order=' || p_order_id);
      RAISE e_validation_failed;
  END;

  -- ステップ3：通知（失敗しても致命的ではない）
  BEGIN
    pkg_notify.send(p_order_id);
  EXCEPTION
    WHEN OTHERS THEN
      pkg_log.warn('notify failed for order=' || p_order_id);
      -- 通知失敗は無視して続行（ROLLBACKしない）
  END;

  COMMIT;
EXCEPTION
  WHEN e_validation_failed THEN
    -- ステップ1だけCOMMIT or ROLLBACK判断
    -- 在庫予約も含めて全部取り消すなら：
    ROLLBACK;
    RAISE_APPLICATION_ERROR(-20001, 'validation failed');
END;
/

CREATE OR REPLACE PROCEDURE saga_book_travel(
  p_user_id IN NUMBER, p_trip_id IN NUMBER
) AS
  e_rollback_needed EXCEPTION;
BEGIN
  -- 1. ホテル予約
  pkg_hotel.book(p_user_id, p_trip_id);
  SAVEPOINT after_hotel;

  -- 2. 飛行機予約
  BEGIN
    pkg_flight.book(p_user_id, p_trip_id);
  EXCEPTION
    WHEN OTHERS THEN
      ROLLBACK TO after_hotel;
      pkg_hotel.cancel(p_user_id, p_trip_id);   -- 補償
      RAISE e_rollback_needed;
  END;
  SAVEPOINT after_flight;

  -- 3. 決済
  BEGIN
    pkg_payment.charge(p_user_id, p_trip_id);
  EXCEPTION
    WHEN OTHERS THEN
      ROLLBACK TO after_flight;
      pkg_flight.cancel(p_user_id, p_trip_id);  -- 補償
      pkg_hotel.cancel(p_user_id, p_trip_id);   -- 補償
      RAISE e_rollback_needed;
  END;

  COMMIT;
EXCEPTION
  WHEN e_rollback_needed THEN
    ROLLBACK;
    RAISE_APPLICATION_ERROR(-20002, 'saga failed, compensated');
END;
/

CREATE OR REPLACE PROCEDURE run_independent_jobs AS
  v_succeeded PLS_INTEGER := 0;
  v_failed    PLS_INTEGER := 0;
BEGIN
  FOR rec IN (SELECT job_id, job_proc FROM job_queue
              WHERE status = 'PENDING'
              ORDER BY priority DESC) LOOP
    SAVEPOINT before_job;
    BEGIN
      EXECUTE IMMEDIATE 'BEGIN ' || rec.job_proc || '; END;';
      UPDATE job_queue SET status = 'DONE'
       WHERE job_id = rec.job_id;
      v_succeeded := v_succeeded + 1;
    EXCEPTION
      WHEN OTHERS THEN
        ROLLBACK TO before_job;
        UPDATE job_queue
           SET status = 'FAILED',
               last_err = SUBSTR(SQLERRM,1,500),
               failed_at = SYSTIMESTAMP
         WHERE job_id = rec.job_id;
        v_failed := v_failed + 1;
    END;
  END LOOP;

  COMMIT;
  DBMS_OUTPUT.PUT_LINE('Succeeded: ' || v_succeeded || ' / Failed: ' || v_failed);
END;
/

import java.sql.*;

try (Connection conn = DriverManager.getConnection(URL, USER, PASS)) {
    conn.setAutoCommit(false);

    try (PreparedStatement ps1 = conn.prepareStatement(
           "INSERT INTO customers VALUES(?, ?)")) {
        ps1.setLong(1, 100);
        ps1.setString(2, "Alice");
        ps1.executeUpdate();
    }

    // ★ JavaからSavepointを設定
    Savepoint sp1 = conn.setSavepoint("after_customer");

    try (PreparedStatement ps2 = conn.prepareStatement(
           "INSERT INTO orders VALUES(?, ?)")) {
        ps2.setLong(1, 1001);
        ps2.setLong(2, 100);
        ps2.executeUpdate();
    } catch (SQLException e) {
        // 注文だけ取消し、顧客は残す
        conn.rollback(sp1);
    }

    conn.commit();
}