【Oracle】ORA-00054 完全ガイド｜resource busy and acquire with NOWAIT の原因・DDL のロック待機・対処法まで解説

-- SELECT FOR UPDATE NOWAIT: ロック取得できなければ即 ORA-00054 を返す
SELECT employee_id, salary FROM employees
WHERE department_id = 60
FOR UPDATE NOWAIT;   -- ロック中なら即エラー
-- ORA-00054: resource busy and acquire with NOWAIT specified, or timeout expired

-- SELECT FOR UPDATE WAIT N: 最大 N 秒待機してからあきらめる
SELECT employee_id, salary FROM employees
WHERE department_id = 60
FOR UPDATE WAIT 5;   -- 5秒待ってもロック取得できなければ ORA-00054
-- タイムアウト秒数は整数で指定（小数不可）

-- SELECT FOR UPDATE（待機指定なし）: ロックが解放されるまで無限に待機する
SELECT employee_id, salary FROM employees
WHERE department_id = 60
FOR UPDATE;   -- ロックが解放されるまで待機（デッドロックのリスクに注意）

-- SELECT FOR UPDATE SKIP LOCKED: ロック中の行をスキップして処理する
-- キュー処理・ジョブ管理など「空いている行を取得して処理する」用途に最適
SELECT employee_id, salary FROM employees
WHERE department_id = 60
FOR UPDATE SKIP LOCKED;
-- ロック中の行は結果に含まれない → 他のセッションが処理していない行のみを処理できる

-- SKIP LOCKED を使ったキュー処理パターン
SELECT job_id, job_data FROM job_queue
WHERE status = 'PENDING'
AND ROWNUM <= 10          -- バッチサイズ
FOR UPDATE SKIP LOCKED;   -- 他のセッションが処理中のジョブはスキップ

-- DDL_LOCK_TIMEOUT: DDL がロック待機する最大秒数（デフォルト: 0 = 即エラー）
-- 0 の場合: ロックが取得できなければ即 ORA-00054

-- セッション単位で DDL の待機時間を設定する
ALTER SESSION SET DDL_LOCK_TIMEOUT = 30;   -- 最大 30秒待機してから DDL を実行する

-- システム全体に設定する（DBA権限）
ALTER SYSTEM SET DDL_LOCK_TIMEOUT = 30;

-- 設定後は DDL がロック待機するようになる（接続セッションがある状態でも待機する）
ALTER TABLE employees ADD column_new VARCHAR2(100);
-- ロックが取得できない場合 → 30秒待機 → それでも取得できなければ ORA-00054

-- DDL の前にロック保持セッションを確認する（後述の方法で特定してから DDL を実行）
-- 本番環境でのメンテナンス作業: 低負荷の時間帯 + DDL_LOCK_TIMEOUT で余裕を持たせる
ALTER SESSION SET DDL_LOCK_TIMEOUT = 300;  -- 5分待機
ALTER TABLE employees ADD salary_grade NUMBER;

-- ORA-00054 が発生した場合: ロックを保持しているセッションを特定する

-- ステップ1: ロックを保持しているセッションを確認する
SELECT
    s.sid,
    s.serial#,
    s.username,
    s.status,
    s.sql_id,
    s.machine,
    s.program,
    ROUND(s.last_call_et / 60, 1) AS idle_min,   -- アイドル時間（分）
    s.logon_time
FROM V$SESSION s
WHERE s.status = 'ACTIVE'
   OR s.sid IN (
       SELECT blocking_session FROM V$SESSION WHERE blocking_session IS NOT NULL
   )
ORDER BY idle_min DESC;

-- ステップ2: DML ロックの詳細を確認する（どのオブジェクトをロックしているか）
SELECT
    s.sid,
    s.username,
    l.type        AS lock_type,    -- TM: テーブルロック, TX: トランザクションロック
    l.mode_held   AS held,         -- ロックモード（0=なし,2=行共有,3=行排他,4=共有,5=共有行排他,6=排他）
    l.mode_requested AS requested,
    o.object_name AS locked_object,
    o.object_type
FROM V$LOCK l
JOIN V$SESSION s ON l.sid = s.sid
LEFT JOIN DBA_OBJECTS o ON l.id1 = o.object_id
WHERE l.block = 1   -- ブロッカー（他のセッションをブロックしている）
ORDER BY s.username, o.object_name;

-- ステップ3: ロックを保持しているセッションを切断する（DBA権限）
-- ※ 切断はビジネスへの影響を確認してから実施する
ALTER SYSTEM KILL SESSION '42,1234';   -- SID=42, SERIAL#=1234
-- IMMEDIATE: すぐに切断（KILL SESSION のデフォルトは待機あり）
ALTER SYSTEM KILL SESSION '42,1234' IMMEDIATE;