【PL/SQL】DBMS_LOCKで二重起動防止する方法｜排他制御・release_on_commit・実務テンプレート

-- DBAまたは権限を持つユーザーで実行する例
GRANT EXECUTE ON DBMS_LOCK TO app_owner;

-- 実務では app_lock パッケージだけを公開し、
-- DBMS_LOCK の直接利用箇所を集約する設計も有効

DECLARE
  v_handle VARCHAR2(128);
BEGIN
  DBMS_LOCK.ALLOCATE_UNIQUE(
    lockname        => 'APP:BATCH:MONTHLY_BILLING',
    lockhandle      => v_handle,
    expiration_secs => 86400
  );

  DBMS_OUTPUT.PUT_LINE('lock handle=' || v_handle);
END;
/

DECLARE
  v_handle VARCHAR2(128);
  v_result PLS_INTEGER;
BEGIN
  DBMS_LOCK.ALLOCATE_UNIQUE('APP:BATCH:DAILY_EXPORT', v_handle);

  v_result := DBMS_LOCK.REQUEST(
    lockhandle        => v_handle,
    lockmode          => DBMS_LOCK.X_MODE,
    timeout           => 0,
    release_on_commit => TRUE
  );

  IF v_result = 0 THEN
    DBMS_OUTPUT.PUT_LINE('lock acquired');
    -- ここに短い処理を書く
    COMMIT; -- release_on_commit => TRUE なのでここで解放
  ELSIF v_result = 1 THEN
    DBMS_OUTPUT.PUT_LINE('already running');
  ELSE
    DBMS_OUTPUT.PUT_LINE('lock request failed: ' || v_result);
  END IF;
END;
/

-- 危険な例: 途中COMMITでロックが外れる
v_result := DBMS_LOCK.REQUEST(
  lockhandle        => v_handle,
  lockmode          => DBMS_LOCK.X_MODE,
  timeout           => 0,
  release_on_commit => TRUE
);

-- ここでロック取得成功
UPDATE work_table SET status = 'PROCESSING';
COMMIT; -- この時点でロックが解放される

-- この後の長い処理中に、別セッションが同じロックを取得できる可能性がある

DECLARE
  v_handle VARCHAR2(128);
  v_result PLS_INTEGER;
  v_released PLS_INTEGER;
BEGIN
  DBMS_LOCK.ALLOCATE_UNIQUE('APP:BATCH:MONTHLY_BILLING', v_handle);

  v_result := DBMS_LOCK.REQUEST(
    lockhandle        => v_handle,
    lockmode          => DBMS_LOCK.X_MODE,
    timeout           => 0,
    release_on_commit => FALSE
  );

  IF v_result = 0 THEN
    BEGIN
      -- 途中COMMITがあってもロックは保持される
      UPDATE billing_work
      SET status = 'PROCESSING'
      WHERE status = 'READY';
      COMMIT;

      -- 本処理
      NULL;

      v_released := DBMS_LOCK.RELEASE(v_handle);
      COMMIT;
    EXCEPTION
      WHEN OTHERS THEN
        ROLLBACK;
        v_released := DBMS_LOCK.RELEASE(v_handle);
        RAISE;
    END;
  ELSIF v_result = 1 THEN
    DBMS_OUTPUT.PUT_LINE('他セッションが実行中のためスキップ');
  ELSE
    RAISE_APPLICATION_ERROR(-20001, 'DBMS_LOCK.REQUEST failed: ' || v_result);
  END IF;
END;
/

CASE v_result
  WHEN 0 THEN
    DBMS_OUTPUT.PUT_LINE('LOCK_ACQUIRED');
  WHEN 1 THEN
    DBMS_OUTPUT.PUT_LINE('LOCK_TIMEOUT');
  WHEN 2 THEN
    DBMS_OUTPUT.PUT_LINE('LOCK_DEADLOCK');
  WHEN 3 THEN
    DBMS_OUTPUT.PUT_LINE('LOCK_PARAMETER_ERROR');
  ELSE
    DBMS_OUTPUT.PUT_LINE('LOCK_ERROR=' || v_result);
END CASE;

CREATE OR REPLACE PACKAGE app_lock AS
  FUNCTION acquire(
    p_lock_name VARCHAR2,
    p_timeout   NUMBER DEFAULT 0
  ) RETURN VARCHAR2;

  PROCEDURE release(
    p_lock_handle VARCHAR2
  );
END app_lock;
/

CREATE OR REPLACE PACKAGE BODY app_lock AS
  FUNCTION acquire(
    p_lock_name VARCHAR2,
    p_timeout   NUMBER DEFAULT 0
  ) RETURN VARCHAR2 IS
    v_handle VARCHAR2(128);
    v_result PLS_INTEGER;
  BEGIN
    DBMS_LOCK.ALLOCATE_UNIQUE(p_lock_name, v_handle);

    v_result := DBMS_LOCK.REQUEST(
      lockhandle        => v_handle,
      lockmode          => DBMS_LOCK.X_MODE,
      timeout           => p_timeout,
      release_on_commit => FALSE
    );

    IF v_result = 0 THEN
      RETURN v_handle;
    ELSIF v_result = 1 THEN
      RETURN NULL;
    ELSE
      RAISE_APPLICATION_ERROR(-20001, 'lock request failed: ' || v_result);
    END IF;
  END;

  PROCEDURE release(p_lock_handle VARCHAR2) IS
    v_result PLS_INTEGER;
  BEGIN
    IF p_lock_handle IS NOT NULL THEN
      v_result := DBMS_LOCK.RELEASE(p_lock_handle);
      IF v_result <> 0 THEN
        RAISE_APPLICATION_ERROR(-20002, 'lock release failed: ' || v_result);
      END IF;
    END IF;
  END;
END app_lock;
/

DECLARE
  v_handle VARCHAR2(128);
BEGIN
  v_handle := app_lock.acquire('APP:BATCH:DAILY_EXPORT', 0);

  IF v_handle IS NULL THEN
    DBMS_OUTPUT.PUT_LINE('already running. skip.');
    RETURN;
  END IF;

  BEGIN
    -- 本処理。途中COMMITしてもロックは保持される
    NULL;
    COMMIT;

    app_lock.release(v_handle);
  EXCEPTION
    WHEN OTHERS THEN
      ROLLBACK;
      app_lock.release(v_handle);
      RAISE;
  END;
END;
/

CREATE TABLE app_lock_run_log (
  log_id       NUMBER GENERATED BY DEFAULT AS IDENTITY,
  lock_name    VARCHAR2(200) NOT NULL,
  run_status   VARCHAR2(30)  NOT NULL,
  result_code  NUMBER,
  started_at   TIMESTAMP DEFAULT SYSTIMESTAMP,
  finished_at  TIMESTAMP,
  message      VARCHAR2(1000),
  CONSTRAINT pk_app_lock_run_log PRIMARY KEY (log_id)
);

-- 例: ロック取得できずスキップした記録
INSERT INTO app_lock_run_log(lock_name, run_status, result_code, message)
VALUES ('APP:BATCH:DAILY_EXPORT', 'SKIPPED', 1, 'already running');

CREATE OR REPLACE PROCEDURE run_daily_export IS
  v_handle VARCHAR2(128);
BEGIN
  v_handle := app_lock.acquire('APP:RESOURCE:EXPORT_FILE', 0);

  IF v_handle IS NULL THEN
    -- 既に別ジョブが同じファイル出力を実行中
    RETURN;
  END IF;

  BEGIN
    -- ファイル出力や外部連携
    NULL;
    COMMIT;
    app_lock.release(v_handle);
  EXCEPTION
    WHEN OTHERS THEN
      ROLLBACK;
      app_lock.release(v_handle);
      RAISE;
  END;
END;
/

-- 読み取り側
v_result := DBMS_LOCK.REQUEST(
  lockhandle        => v_handle,
  lockmode          => DBMS_LOCK.S_MODE,
  timeout           => 10,
  release_on_commit => FALSE
);

-- 更新前に排他へ変換する例
v_result := DBMS_LOCK.CONVERT(
  lockhandle => v_handle,
  lockmode   => DBMS_LOCK.X_MODE,
  timeout    => 10
);

-- 行そのものを更新するなら SELECT FOR UPDATE が自然
SELECT status
INTO v_status
FROM orders
WHERE order_id = p_order_id
FOR UPDATE;

UPDATE orders
SET status = 'PROCESSING'
WHERE order_id = p_order_id;