【PL/SQL】トランザクション分離レベル別の一貫性テストと検証パターン

DROP TABLE iso_log PURGE;
DROP TABLE iso_demo PURGE;

CREATE TABLE iso_demo(
  id      NUMBER PRIMARY KEY,
  cat     VARCHAR2(20) NOT NULL,
  amount  NUMBER(12,2) NOT NULL,
  updated_at TIMESTAMP DEFAULT SYSTIMESTAMP
);

CREATE TABLE iso_log(
  id         NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  test_name  VARCHAR2(80),
  message    VARCHAR2(4000),
  logged_at  TIMESTAMP DEFAULT SYSTIMESTAMP
);

INSERT INTO iso_demo(id, cat, amount) VALUES(1, 'A', 100);
INSERT INTO iso_demo(id, cat, amount) VALUES(2, 'A', 200);
INSERT INTO iso_demo(id, cat, amount) VALUES(3, 'B', 300);
COMMIT;

-- セッションA/Bそれぞれで設定すると、V$SESSIONやログで追跡しやすい
BEGIN
  DBMS_APPLICATION_INFO.SET_MODULE('ISO_TEST', 'SESSION_A');
END;
/

-- セッションA
ROLLBACK;
SELECT SUM(amount) AS sum_a FROM iso_demo WHERE cat = 'A';
-- 期待例: 300

-- セッションB
UPDATE iso_demo
SET amount = amount + 100,
    updated_at = SYSTIMESTAMP
WHERE id = 1;
COMMIT;

-- セッションA
SELECT SUM(amount) AS sum_a FROM iso_demo WHERE cat = 'A';
-- 期待例: 400。文単位で新しいコミット済みデータを読む

-- セッションA
ROLLBACK;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT SUM(amount) AS sum_a FROM iso_demo WHERE cat = 'A';

-- セッションB
UPDATE iso_demo
SET amount = amount + 100,
    updated_at = SYSTIMESTAMP
WHERE id = 1;
COMMIT;

-- セッションA
UPDATE iso_demo
SET amount = amount + 50,
    updated_at = SYSTIMESTAMP
WHERE id = 1;
-- ORA-08177: can't serialize access for this transaction

ROLLBACK;

-- セッションA
ROLLBACK;
SET TRANSACTION READ ONLY;

SELECT COUNT(*) AS cnt_before FROM iso_demo;
SELECT SUM(amount) AS sum_before FROM iso_demo;

-- セッションB
UPDATE iso_demo SET amount = amount + 100 WHERE id IN (1, 2);
COMMIT;

-- セッションA
SELECT COUNT(*) AS cnt_after FROM iso_demo;
SELECT SUM(amount) AS sum_after FROM iso_demo;
-- READ ONLY中は、セッションAから見る結果は固定点のまま

COMMIT;

CREATE OR REPLACE PROCEDURE update_iso_amount_once(
  p_id    IN NUMBER,
  p_delta IN NUMBER
) IS
BEGIN
  UPDATE iso_demo
  SET amount = amount + p_delta,
      updated_at = SYSTIMESTAMP
  WHERE id = p_id;
END;
/

-- 検証用ハーネス。業務部品ではなく、呼び出し側でトランザクションを管理する例。
DECLARE
  e_serialize EXCEPTION;
  PRAGMA EXCEPTION_INIT(e_serialize, -8177);
  v_try PLS_INTEGER := 0;
BEGIN
  LOOP
    v_try := v_try + 1;
    BEGIN
      SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
      update_iso_amount_once(p_id => 1, p_delta => 10);
      COMMIT;
      EXIT;
    EXCEPTION
      WHEN e_serialize THEN
        ROLLBACK;
        INSERT INTO iso_log(test_name, message)
        VALUES('SERIALIZABLE_RETRY', 'retry=' || v_try || ', ORA-08177');
        COMMIT;

        IF v_try >= 3 THEN
          RAISE;
        END IF;

        -- DBMS_LOCK.SLEEPは権限が必要な環境がある。
        -- 権限がない場合は外部テストランナー側で待機させる。
        BEGIN
          DBMS_LOCK.SLEEP(POWER(2, v_try) * 0.1);
        EXCEPTION
          WHEN OTHERS THEN NULL;
        END;
    END;
  END LOOP;
END;
/

-- セッションA: ロックを取得して保持
ROLLBACK;
SELECT id, amount
FROM iso_demo
WHERE id = 1
FOR UPDATE;

-- セッションB: 即時失敗を確認
SELECT id, amount
FROM iso_demo
WHERE id = 1
FOR UPDATE NOWAIT;
-- ORA-00054

-- セッションB: ロック行を避けて処理候補を取る
SELECT id, amount
FROM iso_demo
WHERE id IN (1, 2, 3)
FOR UPDATE SKIP LOCKED;

SELECT
  COUNT(*) AS row_count,
  SUM(amount) AS sum_amount,
  SUM(ORA_HASH(id || ':' || cat || ':' || TO_CHAR(amount))) AS hash_sum,
  MIN(updated_at) AS min_updated_at,
  MAX(updated_at) AS max_updated_at
FROM iso_demo;

-- 小規模データで順序込みの厳密な比較をしたい場合だけLISTAGGを使う
SELECT STANDARD_HASH(
         LISTAGG(id || ':' || cat || ':' || amount, '|')
           WITHIN GROUP (ORDER BY id)
       ) AS strict_signature
FROM iso_demo;

SELECT
  begin_time,
  end_time,
  undoblks,
  txncount,
  maxquerylen,
  ssolderrcnt
FROM v$undostat
ORDER BY begin_time DESC
FETCH FIRST 12 ROWS ONLY;

DECLARE
  v_job1 VARCHAR2(128) := 'ISO_T1_' || TO_CHAR(SYSTIMESTAMP, 'YYYYMMDDHH24MISSFF3');
  v_job2 VARCHAR2(128) := 'ISO_T2_' || TO_CHAR(SYSTIMESTAMP, 'YYYYMMDDHH24MISSFF3');
BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
    job_name   => v_job1,
    job_type   => 'PLSQL_BLOCK',
    job_action => q'[
      DECLARE
        e_serialize EXCEPTION;
        PRAGMA EXCEPTION_INIT(e_serialize, -8177);
      BEGIN
        DBMS_APPLICATION_INFO.SET_MODULE('ISO_TEST','JOB_1');
        SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
        UPDATE iso_demo SET amount = amount + 10 WHERE id = 1;
        DBMS_LOCK.SLEEP(2);
        COMMIT;
      EXCEPTION
        WHEN e_serialize THEN
          ROLLBACK;
          INSERT INTO iso_log(test_name, message)
          VALUES('SCHEDULER_TEST', 'JOB_1 ORA-08177');
          COMMIT;
      END;]',
    enabled => TRUE,
    auto_drop => TRUE
  );

  DBMS_SCHEDULER.CREATE_JOB(
    job_name   => v_job2,
    job_type   => 'PLSQL_BLOCK',
    job_action => q'[
      DECLARE
        e_serialize EXCEPTION;
        PRAGMA EXCEPTION_INIT(e_serialize, -8177);
      BEGIN
        DBMS_APPLICATION_INFO.SET_MODULE('ISO_TEST','JOB_2');
        SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
        UPDATE iso_demo SET amount = amount + 5 WHERE id = 1;
        COMMIT;
      EXCEPTION
        WHEN e_serialize THEN
          ROLLBACK;
          INSERT INTO iso_log(test_name, message)
          VALUES('SCHEDULER_TEST', 'JOB_2 ORA-08177');
          COMMIT;
      END;]',
    enabled => TRUE,
    auto_drop => TRUE
  );
END;
/

SELECT test_name, message, logged_at
FROM iso_log
ORDER BY logged_at DESC;