Oracleのトランザクション分離は「一貫性読取」を前提としたMVCC（Multi-Version Concurrency Control）で実装され、既定のREAD COMMITTEDは文単位、SERIALIZABLEとREAD ONLYはトランザクション単位の一貫性を提供する。理屈を理解していても、実務では「どの隔離レベルで何が起きるか」を再現して検証できないと、夜間バッチやダッシュボードで思わぬ不整合や競合に遭遇する。この記事では、二つのセッションを用いた再現手順、検証クエリの設計、ORA-08177の取り扱い、UNDO前提の確認、報告系のREAD ONLY運用、そしてテスト自動化の雛形までを、箇条書きに頼らず手順とコードでまとめる。
検証用スキーマ初期化と前提
検証対象のテーブルとデータを用意し、セッション識別が分かるようにモジュール名を埋め込んでおく。READ COMMITTEDの“文単位一貫性”と、SERIALIZABLEの“スナップショット一貫性”の差が数行で観測できるデータ配列を作る。下記は最小のセットアップである。
-- 検証テーブル
DROP TABLE iso_demo PURGE;
CREATE TABLE iso_demo(
id NUMBER PRIMARY KEY,
cat VARCHAR2(20) NOT NULL,
amount NUMBER(12,2) NOT NULL,
created TIMESTAMP DEFAULT SYSTIMESTAMP
);
INSERT INTO iso_demo VALUES(1,'A',100,DEFAULT);
INSERT INTO iso_demo VALUES(2,'A',200,DEFAULT);
INSERT INTO iso_demo VALUES(3,'B',300,DEFAULT);
COMMIT;
-- セッション識別（A/Bそれぞれで実行）
BEGIN DBMS_APPLICATION_INFO.SET_MODULE('ISO_TEST','SESSION_A'); END;
/
READ COMMITTEDでの一貫性と“読取の揺れ”の実測
READ COMMITTEDは各SQL文の開始時点での一貫性を保証する。よって同一トランザクション内でも、二回目以降の同一SELECTは“その時点の最新コミット”を読む。これが実運用で非繰り返し読取のような挙動に見える。次の手順は二つのセッションA/Bで観測を行う。
-- セッションA（既定のREAD COMMITTED）
SELECT SUM(amount) FROM iso_demo WHERE cat='A'; -- 結果: 300
-- セッションB（同時進行で値を変更しコミット）
UPDATE iso_demo SET amount = amount + 100 WHERE id=1; COMMIT;
-- セッションA（同じSELECTを再度）
SELECT SUM(amount) FROM iso_demo WHERE cat='A'; -- 結果: 400（前回と変化）
ここでは非繰り返し読取が観測できるが、読み途中で値が入れ替わることはない。単一のSELECT文中には古い版と新しい版が混在せず、常にその文の開始時点の一貫性が保たれる点を押さえておく。
SERIALIZABLEでのスナップショット固定とORA-08177の実測
SERIALIZABLEはトランザクション開始時の一貫性を全ての文で保つ。その代償として、並行トランザクションと更新競合が起きるとORA-08177（can’t serialize access for this transaction）を返す。次の手順で再現する。
-- セッションA：シリアライザブルで開始
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- スナップショット固定後に参照（時点t0）
SELECT SUM(amount) FROM iso_demo WHERE cat='A'; -- ここでは300とするため初期化済み前提
-- セッションB：同じ行を更新しコミット（時点t1 > t0）
UPDATE iso_demo SET amount = amount + 100 WHERE id=1; COMMIT;
-- セッションA：固定スナップショット上で更新を試みる
UPDATE iso_demo SET amount = amount + 50 WHERE id=1;
-- ORA-08177が発生（シリアライズ不可）
ROLLBACK;
SERIALIZABLEは“古いスナップショットに基づく決定が、実世界で矛盾する”と判断した瞬間に衝突を例外で知らせる。これをテストで確実に拾い、呼び出し側で限定回数の再試行を実装するのが実運用の作法となる。
READ ONLYでのレポート固定点取得とUNDO前提の検証
長尺のレポートやETLの参照側はREAD ONLYを使うと、全クエリが同一スナップショットで実行されるため、集計の整合が保証される。ただしUNDO保持に依存するため、古いバージョンが捨てられるとORA-01555になり得る。テストでは意図的に遅延や大規模更新を混ぜて耐性を確認する。
-- セッションA：READ ONLYで固定
SET TRANSACTION READ ONLY;
SELECT COUNT(*) FROM iso_demo; -- 固定点t0が設定される
-- セッションB：大量更新＋コミットを実行（UNDO圧迫を模擬）
BEGIN
FOR i IN 1..50000 LOOP
UPDATE iso_demo SET amount = amount + 1 WHERE id = MOD(i,3)+1;
IF MOD(i,1000)=0 THEN COMMIT; END IF;
END LOOP;
COMMIT;
END;
/
-- セッションA：長時間の集計や結合を実行してORA-01555の有無を観測
SELECT cat, SUM(amount) FROM iso_demo GROUP BY cat;
COMMIT;
READ ONLYは更新不可だが、参照の一貫性が強力になる。UNDO保持時間や表領域設計を変えながら、失敗しないバッチ窓の長さを測定しておくと本番運用の安全域が見える。
ORA-08177の受け止め方と限定リトライの雛形
シリアライズ競合は例外で通知されるため、呼び出し側で限定回数の指数バックオフ付き再試行を実装する。冪等性が前提であることをテストで担保し、同一入力の反復で状態が悪化しないことを必ず検証する。
CREATE OR REPLACE PROCEDURE upd_serializable(p_id NUMBER, p_delta NUMBER) IS
v_try PLS_INTEGER := 0;
BEGIN
LOOP
v_try := v_try + 1;
BEGIN
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
UPDATE iso_demo SET amount = amount + p_delta WHERE id = p_id;
COMMIT;
EXIT;
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE = -8177 AND v_try <= 5 THEN
ROLLBACK;
DBMS_LOCK.SLEEP(POWER(2,v_try)*0.1);
ELSE
ROLLBACK; RAISE;
END IF;
END;
END LOOP;
END;
/
テストでは二つのセッションから同一行へ同時にこのプロシージャを走らせ、最終値が期待と一致するか、リトライ回数が上限内か、失敗時のログが採れているかを確認する。
FOR UPDATEとREAD COMMITTEDの組合せで直列化を意図的に作る検証
悲観制御の基本形であるFOR UPDATEは、READ COMMITTEDでも行レベルの直列化を保証する。検証ではSKIP LOCKEDやNOWAITの挙動を合わせて観測する。次の手順はワーカープールの先取り分散の再現になる。
-- Aがロックを取得して保持
SELECT id FROM iso_demo WHERE id=1 FOR UPDATE;
-- BがNOWAITで即時失敗を確認
SELECT id FROM iso_demo WHERE id=1 FOR UPDATE NOWAIT; -- ORA-00054
-- BがSKIP LOCKEDで回避取得（ロック行を飛ばして進む）
SELECT id FROM iso_demo WHERE id IN (1,2) FOR UPDATE SKIP LOCKED;
この検証を行列の増加や並行度を変えながら走らせ、ワーカー数とホット行の関係、待機時間、スループットの最適点を事前に掴んでおくと、実装時の設計判断が速くなる。
“一貫性の揺れ”を検知するハッシュ検証クエリの設計
非繰り返し読取の検知には、結果集合のハッシュを二度連続で計算し差異を捕まえる方法が実装しやすい。READ COMMITTEDのテストで、バックグラウンド更新の有無によりハッシュが変化するかを自動で判断できる。
-- 集合のハッシュを取る（順序不定のため正規化してから）
WITH s AS (
SELECT id, cat, amount FROM iso_demo ORDER BY id
)
SELECT STANDARD_HASH(LISTAGG(id||':'||cat||':'||amount,'|') WITHIN GROUP(ORDER BY id)) AS sig
FROM s;
このシグネチャ計算をREAD ONLYとREAD COMMITTEDでそれぞれ繰り返し、READ ONLYでは不変、READ COMMITTEDでは外部更新時に変化、という期待どおりの挙動をCIで検証しておく。
UNDOと一貫性の関係を定量確認するテスト観点
一貫性はUNDO保持が前提で、長時間のREAD ONLYや巨大なハッシュ結合は古いバージョンを参照する可能性が高い。テストではUNDO_RETENTIONや表領域サイズを段階的に変え、一定サイズのフルスキャンや並列処理の下でORA-01555の閾値を把握する。定量化のために行数と経過時間、直前の大量更新開始からの経過をログし、しきい値をSLAに反映する。
レポート系におけるREAD ONLYの実装テンプレート
ダッシュボードや帳票バッチは、開始直後に固定点を取り、以降はその時点の真実のみを参照する。これにより集計間の“不一致”が排除される。テンプレートは次のとおりである。
DECLARE
v_start TIMESTAMP := SYSTIMESTAMP;
v_sum_a NUMBER; v_sum_b NUMBER;
BEGIN
SET TRANSACTION READ ONLY;
SELECT SUM(amount) INTO v_sum_a FROM iso_demo WHERE cat='A';
SELECT SUM(amount) INTO v_sum_b FROM iso_demo WHERE cat='B';
COMMIT; -- READ ONLY終了
INSERT INTO report_audit(start_at, sum_a, sum_b) VALUES(v_start, v_sum_a, v_sum_b);
END;
/
このパターンにより、参照中に行われた更新に引きずられて集計合計が揺らぐ事態を回避できる。加えてAWRやSQL MonでPGA/Tempの挙動を採っておき、計算の溢れがないかも併せて観測する。
自動テストの雛形と並行実行のシナリオ化
CIで二セッションを擬似的に表現するには、DBMS_SCHEDULERで二つの匿名PL/SQLを同時起動する、あるいは外部テストランナーから別接続を開く。下記は単純な並行起動例で、ORA-08177を所期の挙動として捕捉し、ログへ書く。
BEGIN
DBMS_SCHEDULER.CREATE_JOB(job_name => 'T1',
job_type => 'PLSQL_BLOCK',
job_action => q'[
BEGIN
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
UPDATE iso_demo SET amount=amount+10 WHERE id=1; DBMS_LOCK.SLEEP(1); COMMIT;
END; ]',
enabled => TRUE);
DBMS_SCHEDULER.CREATE_JOB(job_name => 'T2',
job_type => 'PLSQL_BLOCK',
job_action => q'[
DECLARE e EXCEPTION; PRAGMA EXCEPTION_INIT(e,-8177);
BEGIN
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
UPDATE iso_demo SET amount=amount+5 WHERE id=1; COMMIT;
EXCEPTION WHEN e THEN INSERT INTO iso_log(msg, at) VALUES('ORA-08177',SYSTIMESTAMP); COMMIT; END; ]',
enabled => TRUE);
END;
/
ジョブ終了後にiso_logへORA-08177が記録されていること、iso_demoの最終値が期待レンジに収まっていることを検証して合否を決める。失敗時の再現性を高めるため、DBMS_APPLICATION_INFOやSET_IDENTIFIERで相関IDを各ジョブに割り当てておくと追跡が容易になる。
まとめ
READ COMMITTEDは文単位、SERIALIZABLEとREAD ONLYはトランザクション単位でスナップショットを固定する。READ COMMITTEDでは同一トランザクション内の繰り返しSELECTが揺れ得る一方、SERIALIZABLEは一貫性を守るために競合を例外で通知する。READ ONLYは長尺集計の整合を保証するがUNDOに依存する。検証は二セッションのシナリオで実測し、ハッシュシグネチャで“揺れ”を検知し、ORA-08177の限定再試行を雛形で備える。これらの検証パターンをCIに組み込み、隔離レベルの選択とUNDO/並行度の前提を数値で持つことが、本番での予測可能性を最大化する最短コースである。
