【Oracle】ロック／デッドロックを突き止める調査術（再現SQL付き）

ロック／デッドロックとは

ロック：同じデータに対する同時更新からデータ整合性を守るための排他制御の仕組みです。Oracle では、UPDATE や DELETE の対象行に自動的に行ロック（TXエンキュー）がかかり、トランザクションが COMMIT / ROLLBACK するまで保持されます。この間、同じ行を変更しようとする別セッションは ロック待ち となり、待機イベントenq: TX - row lock contentionを示して停止します。「例外は出ていないのにアプリが固まる」症状の典型的な原因です。

デッドロック：セッションAがBの持つロックを待ち、同時にBがAの持つロックを待つという循環待ちが成立し、双方が永遠に進めなくなる状態です。Oracle はこれを自動検出し、片方にORA-00060を返して文をロールバックすることで膠着を解きます（詳細は後述。「デッドロック ORA-00060 に出会ったら」で解説）。

以降、身近なイメージとして、ロックを「トイレの個室」にたとえます。

個室に籠城する人（ロック保持者）、ドアの前で待つ人（待機セッション）、そして個室の前で二人が互いを待ち合う地獄（デッドロック）

この記事ではOracle Database 19cを使い、実際に再現しながら「誰が犯人か」を突き止める調査術を、ハンズオン形式で解説します。

※3つのセッションを使います。

検証環境

前提はOracle Database 19c（PDB構成）と SQL*Plus。端末（SQL*Plusセッション）を3つ使い、配役はこうします。

• 端末A：ロックを握ったまま個室に籠城する人
• 端末B：ドアの前で待たされる人
• 端末C：V$ ビューを叩く調査用（SYSTEM等で接続）

まず端末Cで、実験用ユーザーと舞台になるテーブルを作ります。

-- PDB に接続して実行（表領域名・接続先は環境に合わせて変更）
CREATE USER locklab IDENTIFIED BY locklab QUOTA UNLIMITED ON users;
GRANT CREATE SESSION, CREATE TABLE TO locklab;
GRANT EXECUTE ON SYS.DBMS_LOCK TO locklab;  -- 本題4で使用。PUBLICには付与されていないため明示的に必要

-- トイレの個室
CREATE TABLE locklab.stalls (
  stall_no  NUMBER PRIMARY KEY,
  occupant  VARCHAR2(30)
) TABLESPACE users;
INSERT INTO locklab.stalls VALUES (1, NULL);
INSERT INTO locklab.stalls VALUES (2, NULL);
COMMIT;

端末Aと端末Bをそれぞれ locklab で接続し、自分のSID（セッション識別番号）を控えておきます。

-- 端末A・B それぞれで実行
SELECT SYS_CONTEXT('USERENV', 'SID') AS my_sid FROM dual;

以降の実行例では 端末A＝SID 52、端末B＝SID 278 として表記します（値は環境ごとに異なるので読み替えてください）。V$ ビューを見る調査SQLはすべて端末Cで実行します（一般ユーザーで見たい場合は SELECT_CATALOG_ROLE 等の付与が必要です）。

ロックを発生させる

端末Aで個室1を確保し、COMMITせずに籠城します。

-- 端末A
UPDATE locklab.stalls SET occupant = 'Aさん' WHERE stall_no = 1;
-- COMMIT も ROLLBACK もせず、そのまま放置（＝籠城）

端末B（被害者）で同じ行を更新しようとすると——

-- 端末B
UPDATE locklab.stalls SET occupant = 'Bさん' WHERE stall_no = 1;
-- プロンプトが返ってこない（＝ドアの前で待機中）

固まりました。調査していきましょう。

まずは V$LOCK で現場を押さえる

V$LOCK は、Oracle が 現在保持しているロックと、ロックやラッチに対する未処理のリクエスト（＝待たされている要求） の両方を一覧表示する動的パフォーマンスビューです。つまり「個室に入っている人」と「ドアの前で待っている人」が同時に見えます。

-- 端末C：ロックを握っている側 or 待たされている側を抽出
SELECT sid, type, id1, id2, lmode, request, block, ctime
FROM   v$lock
WHERE  block = 1      -- このセッションが他をブロックしている
   OR  request > 0;   -- このセッションが何かを待っている

実行例（数値は環境により異なります）：

       SID TY        ID1        ID2      LMODE    REQUEST      BLOCK      CTIME
---------- -- ---------- ---------- ---------- ---------- ---------- ----------
        52 TX     458785       1123          6          0          1        183
       278 TX     458785       1123          0          6          0        176

主な列の意味をざっくり補足します。

• SID：セッションの識別子。誰が、を表す番号。
• TYPE：ロックの種類（例：行トランザクションロックは TX、テーブルロックは TM、後述のユーザー定義ロックは UL）。
• LMODE：保持しているロックのモード（0=保持なし、1=NULL、2=行共有、3=行排他、4=共有、5=共有行排他、6=排他。数値は大きさの単純な順位ではなく、ロックモードを表すコードです。モード間の競合可否は互換性表で判断します。）。
• REQUEST：要求しているロックのモード（0 なら待っていない）。
• BLOCK：1 なら「こいつが他人をブロックしている張本人」（RAC環境では、他ノードをブロックしている可能性を示す 2 が入ることもあります）。
• CTIME：現在のモードになってからの経過秒数。長いほど怪しい。

実行例を読み解くとSID 52 が TX（行トランザクションロック）を排他モード（LMODE=6）で握って BLOCK=1。SID 278 は同じIDの資源を REQUEST=6 で要求して待っています。ID1/ID2 の組はロック資源の識別子なので、これが一致している＝同じ個室を取り合っているということです。

小ネタ：TYPE='TM'（テーブルロック）の行では ID1 が対象オブジェクトのIDなので、SELECT object_name FROM dba_objects WHERE object_id = <ID1>; でテーブル名が引けます。

BLOCK=1の行は、少なくとも別のセッションをブロックしているセッションです。

ただし、多段の待ち連鎖では、そのセッション自身も別セッションを待っている可能性があります。

V$SESSIONで「加害者」と「被害者」を確認する

V$LOCK だけだと SID の数字しか分からず、事情聴取になりません。そこで V$SESSION の出番。特に BLOCKING_SESSION 列が優秀で、自分をブロックしているセッションのSID をズバリ教えてくれます。

-- 端末C：待たされている（被害者）セッションと、その加害者を並べて表示
SELECT s.sid,
       s.serial#,
       s.username,
       s.blocking_session       AS blocker_sid,  -- 直接の加害者
       s.final_blocking_session AS root_sid,     -- 待ち連鎖の一番上にいる親玉
       s.event,             -- 何を待っているか（例: enq: TX - row lock contention）
       s.sql_id
FROM   v$session s
WHERE  s.blocking_session IS NOT NULL;

実行例：

 SID    SERIAL# USERNAME  BLOCKER_SID   ROOT_SID EVENT                            SQL_ID
---- ---------- --------- ----------- ---------- -------------------------------- -------------
 278      55123 LOCKLAB            52         52 enq: TX - row lock contention    b3xk9q2m7yfgd

event に enq: TX - row lock contention（行ロックの競合待ち）などと出ていれば、ロック待ちで固まっている動かぬ証拠です。今回は待ちが1段なので BLOCKER_SID と ROOT_SID が同じ値ですが、多段の待ち行列では FINAL_BLOCKING_SESSION が一発で親玉を指してくれます。

加害者のSQLを特定する

加害者（SID 52）が判明したら、何をしていたのかSQLを調べます。ここで罠がひとつ。籠城中の加害者はたいてい「何も実行していないアイドル状態」 です。UPDATEを打ち終えて手が止まっているだけなので、V$SESSION.SQL_ID は NULL。この場合は 直前に実行したSQLを指す PREV_SQL_ID を見ます。

-- 端末C：加害者セッションの状態・現在SQL・直前SQL（SERIAL#は後のKILLでも使う）
SELECT sid, serial#, status, sql_id, prev_sql_id
FROM   v$session
WHERE  sid = 52;   -- 加害者のSID

実行例：

 SID    SERIAL# STATUS   SQL_ID        PREV_SQL_ID
---- ---------- -------- ------------- -------------
  52      40667 INACTIVE               4fz1kcn0y6tqb

STATUS=INACTIVE かつ SQL_ID が空です。
まさに「やるだけやって個室で寝ている」状態です。

PREV_SQL_ID を V$SQL に当てて本文を確認します。

-- 端末C
SELECT sql_id, sql_text
FROM   v$sql
WHERE  sql_id = '4fz1kcn0y6tqb';

SQL_ID        SQL_TEXT
------------- ---------------------------------------------------------------
4fz1kcn0y6tqb UPDATE locklab.stalls SET occupant = 'Aさん' WHERE stall_no = 1

直前に実行したSQLが出ました。なお PREV_SQL_ID はあくまで「直前の1文」なので、加害者がUPDATEのあとに別のSQLを実行していればそちらが表示される点は頭の隅に置いておいてください。

どの行を取り合っているのか

被害者側の V$SESSION には、待っている行の位置情報（ROW_WAIT_* 列）が入っています。DBMS_ROWID でROWIDに組み立てれば、争奪中の行そのものまで特定できます。

-- 端末C
SELECT s.sid,
       o.owner || '.' || o.object_name AS locked_object,
       DBMS_ROWID.ROWID_CREATE(
         1,                    -- 拡張ROWID
         o.data_object_id,
         s.row_wait_file#,
         s.row_wait_block#,
         s.row_wait_row#)      AS wait_rowid
FROM   v$session s
JOIN   dba_objects o
  ON   o.object_id = s.row_wait_obj#
WHERE  s.blocking_session IS NOT NULL;

実行例：

 SID LOCKED_OBJECT    WAIT_ROWID
---- ---------------- ------------------
 278 LOCKLAB.STALLS   AAAR9jAAMAAAACHAAA

-- 端末C：現場を直接見に行く
SELECT * FROM locklab.stalls WHERE ROWID = 'AAAR9jAAMAAAACHAAA';

  STALL_NO OCCUPANT
---------- ------------------------------
         1

個室1が現場です（OCCUPANT が空に見えるのは、Aさんの更新がまだCOMMITされていないため。読取り一貫性）。

ブロックの連鎖を見る

加害者がさらに別の誰かに待たされている、という多段構造もよくあります。CONNECT BY で待ち行列をツリー表示すると、根本原因（一番上のボス）が一目瞭然です。

-- 端末C
SELECT LPAD(' ', 2*(LEVEL-1)) || s.sid AS sid_tree,
       s.username,
       s.event
FROM   v$session s
START WITH s.blocking_session IS NULL          -- 誰にもブロックされておらず、
       AND s.sid IN (SELECT blocking_session   -- かつ誰かをブロックしている「親玉」だけを起点に
                     FROM   v$session
                     WHERE  blocking_session IS NOT NULL)
CONNECT BY NOCYCLE PRIOR s.sid = s.blocking_session;  -- NOCYCLE はデッドロック瞬間の循環対策の保険

START WITH を「親玉」に絞り込んでいるのがポイントです。単に blocking_session IS NULL だけを起点にすると、ブロックと無関係なセッション全員が起点になってしまいます。

実行例：

SID_TREE   USERNAME  EVENT
---------- --------- ------------------------------
52         LOCKLAB   SQL*Net message from client
  278      LOCKLAB   enq: TX - row lock contention

親玉（SID 52）の待機イベントが SQL*Net message from client、つまり「クライアントからの次の入力をのんびり待っているだけ」。犯人に自覚なし。

演習：端末Bで先に stall_no=2 をUPDATEしてから stall_no=1 をUPDATEし、さらに4つ目の端末（locklab接続）で stall_no=2 をUPDATEすると、3階層の家系図が観察できます（試したあとは追加分をROLLBACKして戻してください）。

最終手段（セッションキル）

業務影響を確認したうえで、居座っているセッションをキルする場合はこちら（本番では慎重に、承認を得てから！）。

-- 端末C：'SID,SERIAL#' の形式で指定（SERIAL#は先ほどの加害者調査クエリで確認済み）
ALTER SYSTEM KILL SESSION '52,40667' IMMEDIATE;

-- RACだとインスタンスIDも必要
-- ALTER SYSTEM KILL SESSION 'sid,serial#,@inst_id';

IMMEDIATE を付けると、後片付けの完了を待たずに即座に制御が戻ります（RAC環境では 'SID,SERIAL#,@INST_ID' 形式）。

実行すると、固まっていた端末BのUPDATEが即座に完了します（1行が更新されました。）。一方、退場させられた端末Aで次に何か実行すると：

ORA-00028: your session has been killed

これにて一件落着。

次の実験のために、端末Bで ROLLBACK; を実行し、端末Aは接続し直しておいてください。

デッドロック ORA-00060 に出会ったら

冒頭の「２人がトイレの前で仁王立ち」状態がデッドロックです。ありがたいことに Oracle は自動でデッドロックを検出し、片方のトランザクションに対して次のエラーを返します。

ORA-00060: deadlock detected while waiting for resource

このとき Oracle は、デッドロックを引き起こした文をロールバックして膠着を解きます。ただしトランザクション全体が自動でロールバックされるわけではない点に注意。エラーを受け取ったアプリ側が、適切に ROLLBACK するかリトライするかを判断する責任があります。

デッドロックを実際に起こしてみる

個室2つと犯人2人がいれば、デッドロックは狙って起こせます。番号順に実行してください。

-- ① 端末A：個室1を確保
UPDATE locklab.stalls SET occupant = 'Aさん' WHERE stall_no = 1;

-- ② 端末B：個室2を確保
UPDATE locklab.stalls SET occupant = 'Bさん' WHERE stall_no = 2;

-- ③ 端末A：個室2も欲しい（Bが出るのを待って固まる）
UPDATE locklab.stalls SET occupant = 'Aさん' WHERE stall_no = 2;

-- ④ 端末B：個室1も欲しい（Aが出るのを待つ → 相互待ちの完成）
UPDATE locklab.stalls SET occupant = 'Bさん' WHERE stall_no = 1;

④の実行後、端末Aまたは端末Bのどちらか一方にORA-00060が返ります。どちらに返るかは保証されません。

以下では、端末AにORA-00060が返った場合を例に説明します。

ここで注目。エラーで取り消されたのは端末Aの ③（stall_no=2 へのUPDATE）だけ で、①のUPDATEとトランザクション自体は生きています。その証拠に、端末Bはまだ固まったままです（Aが握り続けている個室1を待っている）。

-- 端末A：明示的にロールバックして、初めて事件が終わる
ROLLBACK;

端末Aが ROLLBACK した瞬間、端末Bの④が完了して返ってきます（今回は端末Bも ROLLBACK; しておきましょう）。これが「文ロールバックのみ、トランザクションは残る」の実物です。アプリが ORA-60 を握りつぶしてトランザクションを放置すると、端末Aのような籠城トランザクションが残り、第二の被害者を生み続けます。

トレースファイルを読む

デッドロックが起きると、サーバー側に トレースファイル（発生時の状況を記録した診断ファイル）が出力されます。どのSQLとどの行が絡んでデッドロックになったのかが記録されるので、再発防止の分析にはこれを読み解くのが王道です。トレースの出力先は診断ディレクトリ（ADR）配下で、アラートログから該当ファイル名をたどれます。実際に探してみましょう。

-- 端末C：トレースディレクトリの場所を確認
SELECT value FROM v$diag_info WHERE name = 'Diag Trace';

VALUE
--------------------------------------------------------------
/u01/app/oracle/diag/rdbms/orclcdb/orclcdb/trace

OS上でそのディレクトリに移動し、アラートログの記録と該当トレースファイルを探します。

cd /u01/app/oracle/diag/rdbms/orclcdb/orclcdb/trace
grep ORA-00060 alert_orclcdb.log | tail -3    # アラートログにも発生記録が残る
grep -l 'DEADLOCK DETECTED' *.trc             # 該当トレースファイルを特定

トレースファイルにはデッドロックの記録がそのまま残っています（抜粋イメージ、整形して簡略化）：

DEADLOCK DETECTED ( ORA-60 )
Deadlock graph:
                       ---------Blocker(s)--------  ---------Waiter(s)---------
Resource Name          process session holds waits  process session holds waits
TX-000A0011-00000B2C        45      52     X             48     278           X
TX-0004001F-00000D91        48     278     X             45      52           X
Rows waited on:
  Session 52:  obj - rowid = ...
  Session 278: obj - rowid = ...
----- Current SQL Statement for this session -----
UPDATE locklab.stalls SET occupant = 'Aさん' WHERE stall_no = 2

Deadlock graph を見ると、2つのTXリソースを 互いに Blocker と Waiter として持ち合っている対称形がはっきり分かります。Rows waited on で行、Current SQL Statement で凶器のSQLまで記録されるので、再発防止分析はここから始めます。

補足：デッドロックは「アプリが複数の行・テーブルを更新する順番がバラバラ」なときに起きがちです。更新順序をコード全体で統一するだけで、かなりの確率で予防できます。トイレも一列に並べば喧嘩は起きません。

DBMS_LOCK で自作ロックを使うときの落とし穴

アプリ側で「この処理は同時に1つしか走らせたくない」といった排他制御をしたいとき、DBMS_LOCK パッケージでユーザー定義ロックを作れます。

公式ドキュメントによると、DBMS_LOCK で予約されたユーザーロックは Oracle 本来のロックと同じ仕組み であり、デッドロック検出を含む Oracle ロックの全機能 を備えています。自作ロックであっても、デッドロックになればちゃんと検出してくれるということです。

ただし重要な注意が3つあります。

• 分散トランザクションで使うユーザーロックは、必ず COMMIT 時に解放すること。 さもないと、Oracle が 検出できないデッドロック（自動検出の網にかからない膠着）を起こす可能性があります。
• 効率が良いのはセッションあたり数百個程度のロックまで。 ロックを湯水のように取ると効率が落ちます。
• ALLOCATE_UNIQUE は呼び出すたびに暗黙的に COMMIT を実行します。 トランザクションの途中で呼ぶと、そこまでの変更が意図せず確定します。ハンドル取得はトランザクション開始前に済ませるか、呼び出し元のトランザクションに影響しない ALLOCATE_UNIQUE_AUTONOMOUS（自律型トランザクション版）を使いましょう。

SET SERVEROUTPUT ON

DECLARE
  l_handle  VARCHAR2(128);
  l_result  INTEGER;
BEGIN
  -- 名前付きロックのハンドルを取得（注意：この呼び出しは暗黙COMMITを伴う）
  DBMS_LOCK.ALLOCATE_UNIQUE('MY_BATCH_LOCK', l_handle);

  -- 排他モードで確保を試みる（タイムアウト10秒）
  l_result := DBMS_LOCK.REQUEST(
                lockhandle        => l_handle,
                lockmode          => DBMS_LOCK.X_MODE,  -- 排他
                timeout           => 10,
                release_on_commit => TRUE);

  IF l_result = 0 THEN
    DBMS_OUTPUT.PUT_LINE('ロック取得成功。処理を実行します。');
    -- ここに排他したい処理
  ELSE
    DBMS_OUTPUT.PUT_LINE('取得できませんでした。戻り値=' || l_result);
  END IF;
END;
/

REQUEST の戻り値は次のとおりです。

戻り値	意味
0	成功
1	タイムアウト（時間内に取れなかった）
2	デッドロック（検出して諦めさせられた）
3	パラメータエラー
4	すでに同じロックを保持している
5	不正なロックハンドル

自作ロックの取り合いを観察する

これも2端末で再現できます。まず端末Aで上のブロックを実行すると「ロック取得成功」と出ます。release_on_commit => TRUE なので、COMMIT（またはROLLBACK）するまでロックは保持されたままです。そのまま端末Bで同じブロックを実行すると、10秒待たされたあと——

取得できませんでした。戻り値=1

タイムアウト（戻り値=1）です。この状態で端末Cから V$LOCK を覗くと：

-- 端末C
SELECT sid, type, id1, lmode, request, ctime
FROM   v$lock
WHERE  type = 'UL';

       SID TY        ID1      LMODE    REQUEST      CTIME
---------- -- ---------- ---------- ---------- ----------
        52 UL 1073742205          6          0         95

自作ロックも TYPE='UL'（User Lock）として V$LOCK に堂々と現れます。つまり本題1〜2の捜査術がそのまま通用するということです。端末Aで COMMIT; すればロックは解放され、端末Bで再実行すると今度は成功します。

release_on_commit => TRUE にしておくと、上記の「COMMIT で解放」の推奨に沿えるので、分散トランザクションでの事故を防げます。

後片付け

実験が終わったら後片づけを。

-- 端末A・B：未コミットのトランザクションが残っていないことを確認して切断
ROLLBACK;
EXIT

-- 端末C：実験用ユーザーごと削除（stalls表も一緒に消えます）
DROP USER locklab CASCADE;

まとめ

ロック調査は、慌てず次の順でたどれば大抵は解決します。

1. V$LOCK で「保持ロック」と「待機リクエスト」を確認し、BLOCK = 1 の犯人候補を探す。ID1/ID2 が一致していれば同じ資源の取り合い。
2. V$SESSION.BLOCKING_SESSION で被害者と加害者を名指しし、FINAL_BLOCKING_SESSION や CONNECT BY で連鎖の親玉まで遡る。
3. V$SQL で加害者の実行SQLを確認（アイドル中の加害者は SQL_ID が空なので PREV_SQL_ID から辿る）し、業務影響を判断してから必要なら退場（KILL）。
4. ORA-00060（デッドロック） は自動検出＋文ロールバックで解けるが、トランザクションは残るのでアプリ側の ROLLBACK／リトライ設計が必須。トレースファイルの Deadlock graph で再発防止分析。
5. DBMS_LOCK の自作ロックはデッドロック検出付きだが、COMMIT での解放・個数・ALLOCATE_UNIQUE の暗黙 COMMIT に注意。

ロックは悪者ではなく、データの整合性を守るために不可欠な仕組みです。要は「誰がどこの個室に、どれくらい居座っているか」を可視化できれば怖くありません。

参考

• Oracle Database リファレンス「V$LOCK」
• Oracle Database リファレンス「V$SESSION」
• Oracle Database PL/SQLパッケージおよびタイプ・リファレンス「DBMS_LOCK」

※ V$SQL の列、ORA-00060 の挙動、デッドロック用トレースファイルなどの詳細は、同ライブラリ内のリファレンス・エラー・メッセージ・管理者ガイド等の該当章もあわせてご確認ください。