【PL/SQL】マルチセッション環境での排他・同時実行制御の高度設計

大規模なOracle/PLSQLシステムでは、複数セッションが同じデータ資源へ同時にアクセスし続けます。思いつきのロック取得や場当たり的な再試行では、デッドロックやスループット低下、待機時間の増大を招きます。ここでは、行ロックと表ロックの前提から、悲観・楽観制御の使い分け、キー単位の直列化、ワーカープールの設計、ジョブの同時実行制御、待機戦略と監視までを、実務で使えるコードとともに体系化します。

ロックの前提と一貫した取得順序

OracleはDML時に行レベルのTXロックを自動取得し、DDLや一部DMLでTM（表）ロックも併発します。デッドロックの多くは「同じリソース集合に対する取得順序の不一致」で起こります。更新対象を決定したら、常に同じソート順で「選ぶ→ロックする→更新する」を守るだけで、発生確率は劇的に下がります。複数テーブルを横断する場合も、テーブル間のロック順序を設計書に固定し、全ての呼び出し点で順守します。

悲観制御の型：FOR UPDATEとNOWAIT／SKIP LOCKED

行を確実に占有したい場面では、更新前に明示ロックを取得します。待たずに即失敗させて呼び出し側の分岐で逃がすか、ロック中の行を飛ばして並列ワーカーが前進するかを状況に応じて選びます。

-- 待たずに失敗（即時判定）。ロック競合時は ORA-00054
SELECT id FROM task_queue
 WHERE status = 'READY'
 FOR UPDATE NOWAIT;

-- ロック中の行を飛ばして進む。複数ワーカーの分散処理に有効
SELECT id, payload FROM task_queue
 WHERE status = 'READY'
 FOR UPDATE SKIP LOCKED;

SKIP LOCKEDは取りこぼしではなく“後で拾う”設計が前提です。巡回ジョブで再スキャンし、残余を回収する流れを合わせて実装します。

楽観制御の型：バージョン列でロストアップデートを防ぐ

競合が稀だが存在する領域では、バージョン列で衝突検知し、失敗時のみ再試行します。アプリから渡された期待バージョンと等しいときだけ更新し、成功時にカウントアップします。

ALTER TABLE customers ADD (row_ver NUMBER DEFAULT 0 NOT NULL);

UPDATE customers
   SET email = :new_email,
       row_ver = row_ver + 1
 WHERE id = :id
   AND row_ver = :expected_ver;

IF SQL%ROWCOUNT = 0 THEN
  RAISE_APPLICATION_ERROR(-20001, 'version conflict');
END IF;

再試行は指数バックオフで限定回数に抑え、無制限な“叩き合い”を避けます。

キー単位の直列化：一意制約／DBMS_LOCK／制御テーブル

特定キーに対して同時に一つだけ処理したい場合、まずは一意制約を“ミューテックス”として活用するのがシンプルです。ロック用テーブルにキーを挿入し、競合時は一意違反で即失敗、成功時のみクリティカルセクションに入ります。

CREATE TABLE key_locks (k VARCHAR2(200) PRIMARY KEY);

-- 取得
BEGIN
  INSERT INTO key_locks(k) VALUES (:key);
EXCEPTION
  WHEN DUP_VAL_ON_INDEX THEN
    RAISE_APPLICATION_ERROR(-20010, 'key busy');
END;
-- ... クリティカルセクション ...
DELETE FROM key_locks WHERE k = :key;
COMMIT;

プロセス横断の明示ロックが欲しい場合はDBMS_LOCKを使います。短時間の占有と即時判定を徹底すると、待機が雪だるま式に膨らむのを防げます。

DECLARE
  h VARCHAR2(128); r PLS_INTEGER;
BEGIN
  DBMS_LOCK.ALLOCATE_UNIQUE('JOB:DAILY_AGG', h);
  r := DBMS_LOCK.REQUEST(h, DBMS_LOCK.X_MODE, timeout => 0, release_on_commit => TRUE);
  IF r <> 0 THEN RAISE_APPLICATION_ERROR(-20000, 'already running'); END IF;
  -- クリティカルセクション
END;
/

ワーカープール設計：キュー×SKIP LOCKEDで効率的に分散

複数ワーカーでキュー表のREADY行を取り合う構成では、SKIP LOCKEDで取り出し、処理中に状態を遷移させるのが王道です。順序保証が不要なら複数セッションが自然に水平分割します。

-- ピックアップ
UPDATE task_queue
   SET status = 'RUNNING', picked_at = SYSTIMESTAMP
 WHERE id IN (
   SELECT id FROM task_queue
    WHERE status='READY'
    ORDER BY priority DESC, id
    FETCH FIRST 1 ROWS ONLY
    FOR UPDATE SKIP LOCKED
 )
RETURNING id INTO :picked_id;

-- 完了または失敗へ遷移
UPDATE task_queue SET status = 'DONE', done_at = SYSTIMESTAMP WHERE id = :picked_id;
-- 失敗時はリトライ回数と次回実行時刻を更新

長時間ロック化を避けるため、ピックアップと“RUNNING”への遷移を同一トランザクションで完了させ、処理本体は別トランザクションで短く刻むと健全です。

ホット行・ホットパーティションの解消：分割とリダイレクト

同じ行や同じパーティションにアクセスが集中すると、たとえ設計が正しくても待機が増えます。キー空間を分割し、集約表は「追記型でシャーディングキー＋時間」を主キーにして、最新状態は別に派生させるとホットスポットを回避できます。更新をINSERT…ON CONFLICT的に「追記」に寄せ、夜間に集計してEXCHANGE PARTITIONで入れ替える構成は、待機とUNDOの両方に効きます。

トランザクションの粒度設計：短いクリティカルセクションとチャンク確定

ロックを伴う区間は必ず短く保ちます。大量更新はキー範囲や期間でチャンク分割し、各チャンクの終わりにCOMMITします。ERRLOGやSAVEPOINTを併用し、恒久障害は部分ロールバックで飛ばし、一時的競合のみ再試行する流れにすると、全体停止を防げます。

FOR r IN (
  SELECT id FROM targets WHERE processed='N' ORDER BY id
) LOOP
  SAVEPOINT sp_row;
  BEGIN
    -- 最小限のロック区間で更新
    UPDATE targets SET ... WHERE id = r.id;
  EXCEPTION
    WHEN OTHERS THEN
      ROLLBACK TO sp_row; -- 個別行だけ戻して継続
  END;
  IF MOD(r.id, 50000) = 0 THEN COMMIT; END IF;
END LOOP;
COMMIT;

隔離レベルの選択：READ COMMITTEDとSERIALIZABLEの使い分け

読み取りの一貫性が厳密に必要な集計はSERIALIZABLEで固定して取り、更新系はREAD COMMITTEDのまま最小ロックで前進するのが一般的です。SERIALIZABLEでの更新競合はORA-08177として現れるため、前提として再試行設計が不可欠です。報告系ではSET TRANSACTION READ ONLYでスナップショットを固定し、長時間SELECTが更新系と干渉しないよう時間帯やパーティションでの分離を併用します。

外部副作用の分離：アウトボックスでDB外の排他を切り離す

メールやHTTPなどDB外の資源はデータベースのロックでは守れません。DMLと同一トランザクションでアウトボックス表にイベントを記録し、別ワーカーがFOR UPDATE SKIP LOCKEDで取り出して配送します。再試行で二重送信にならないよう、外部側は冪等キーで受け止めます。

INSERT INTO outbox(event_key, type, payload, created_at)
VALUES(:key, 'ORDER_CREATED', :json, SYSTIMESTAMP);

-- 配送ワーカー側
SELECT id FROM outbox
 WHERE sent_at IS NULL
 FOR UPDATE SKIP LOCKED;
-- 成功したら sent_at を更新して確定

再試行戦略とバックオフ：限定対象・限定回数・指数待機

再試行はロック競合やデッドロック、シリアライズ競合のような“一時障害”だけに限定します。指数バックオフで群衆行動を避け、上限到達で確実に失敗させます。

DECLARE
  v_try PLS_INTEGER := 0;
BEGIN
  LOOP
    v_try := v_try + 1;
    BEGIN
      -- 衝突しやすい更新
      UPDATE ...;
      COMMIT; EXIT;
    EXCEPTION
      WHEN resource_busy OR deadlock_detected THEN
        ROLLBACK;
        IF v_try <= 5 THEN DBMS_LOCK.SLEEP(POWER(2, v_try) * 0.2); ELSE RAISE; END IF;
    END;
  END LOOP;
END;
/

ジョブの同時実行制御：DBMS_SCHEDULERとアプリ側ロックの二層

スケジューラのmax_runsやauto_dropだけに頼らず、ジョブ開始時にDBMS_LOCKや一意制約で“すでに稼働中”を即時検出し、二重起動を防ぎます。日次や時間帯単位のジョブはパーティションキーに合わせたチャンク実行とし、失敗時の再開点を記録しておくと運用負荷が下がります。

監視とトラブルシュート：待機イベントとロック保持者の特定

発生後の調査では、待機イベント、ロック待ちのセッション、ブロッカーを即時に洗い出します。実運用では以下のクエリを“定番”として手元に置いておきます。

-- 何を待っているか
SELECT sid, event, seconds_in_wait
  FROM v$session
 WHERE state = 'WAITING' AND wait_class = 'Concurrency';

-- ロック待ちと保有者
SELECT l1.sid waiter, l2.sid holder, l1.id1, l1.id2, l1.type
  FROM v$lock l1 JOIN v$lock l2
    ON l1.id1 = l2.id1 AND l1.id2 = l2.id2 AND l1.type = l2.type
 WHERE l1.block = 0 AND l2.block = 1;

-- 開いているカーソルの傾向（リーク検知にも）
SELECT sql_id, COUNT(*) cnt FROM v$open_cursor
 WHERE sid = SYS_CONTEXT('USERENV','SID')
 GROUP BY sql_id ORDER BY cnt DESC;

障害再発防止には、実行計画とインデックス設計、パーティション設計、ロック順序の一貫性、そして“長いクリティカルセクションを作らない”ことを合わせて点検します。

まとめ

同時実行制御の骨格は単純です。ロックは常に同じ順序で取得し、占有区間は短く保ち、取り合いはSKIP LOCKEDで前進させ、稀な競合はバージョン列で検知して限定的に再試行します。キー単位の直列化には一意制約やDBMS_LOCKを使い、ホットスポットはシャーディングや追記型設計で解消します。外部副作用はアウトボックスで切り離し、ジョブは二層で排他します。最後に、待機イベントとロック構造を継続監視し、設計・運用の両輪で“詰まりにくい”システムへチューニングしていくことが、マルチセッション環境で高い可用性とスループットを両立させる近道です。