SQL

Oracle 名前付きロック（アドバイザリロック）の取得方法

Oracleで名前付きロック（アドバイザリロック）を現場で使う必要があったので、忘備録です。

具体的には、DBMS_LOCKパッケージを使った名前付きロックの取得と解放のやり方をメモします。

SQLの例

早速、SQLの例は以下のようになります。

DECLARE
   my_lockhandle VARCHAR2(128);
   my_result INTEGER;
BEGIN
   -- ユニークなロックIDを取得
   DBMS_LOCK.ALLOCATE_UNIQUE(lockname => 'MY_LOCK', lockhandle => my_lockhandle);

   DBMS_OUTPUT.PUT_LINE('作られたロックハンドル: ' || my_lockhandle);

   -- ロックを取得
   my_result := DBMS_LOCK.REQUEST(lockhandle => my_lockhandle, lockmode => DBMS_LOCK.X_MODE, timeout => 10);

   IF my_result = 0 THEN
      DBMS_OUTPUT.PUT_LINE('ロックの取得に成功');

      -- ここで排他処理を実行

      -- ロックを解放
      my_result := DBMS_LOCK.RELEASE(lockhandle => my_lockhandle);

      IF my_result = 0 THEN
         DBMS_OUTPUT.PUT_LINE('ロックの解放に成功');
      ELSE
         DBMS_OUTPUT.PUT_LINE('ロックの解放に失敗、、、');
      END IF;

   ELSE
      DBMS_OUTPUT.PUT_LINE('ロックの取得に失敗、、、');
   END IF;
END;
/

１つずつ見ていきましょう

DBMS_LOCK.ALLOCATE_UNIQUE() プロシージャ

6行目のユニーク(UNIQUE)な名前を割り当て(ALLOCATE)たロックの情報を(無ければ作ってから)取得しています。ただ取得しただけで、まだロックを「行使」していません。

   -- ユニークなロックIDを取得
   DBMS_LOCK.ALLOCATE_UNIQUE(lockname => 'MY_LOCK', lockhandle => my_lockhandle);

ここで、DBMS_LOCK.ALLOCATE_UNIQUE() プロシージャの定義は以下のようになります。

DBMS_LOCK.ALLOCATE_UNIQUE( 
   lockname IN VARCHAR2, 
   lockhandle OUT VARCHAR2, 
   expiration_secs IN INTEGER DEFAULT 864000
);

ここで、INパラメータの「lockname」にはお好きなロックの名前 (予約されてる「ORA$」で始まる名前はダメ) を、

「expiration_sesc」 (expiration seconds=期限切れの秒数) は何秒後に（DBMS_LOCK_ALLOCATED表というところに）登録されたそのロック情報が消せるようにするか、です。デフォルト値は10日 (=864000秒) です。(その後にDBMS_LOCK.ALLOCATE_UNIQUE()が呼び出されたときに消されます。)

OUTパラメータの「locakhandle」は、11行目、ロックの「REQUEST(要求する)」に使います。

DBMS_LOCK.REQUEST() ファンクション

  -- ロックを取得
   my_result := DBMS_LOCK.REQUEST(lockhandle => my_lockhandle, lockmode => DBMS_LOCK.X_MODE, timeout => 10);

ここで、DBMS_LOCK.REQUEST() ファンクションの定義は以下のようになります。

DBMS_LOCK.REQUEST( 
   id IN INTEGER || lockhandle IN VARCHAR2, 
   lockmode IN INTEGER DEFAULT X_MODE, 
   timeout IN INTEGER DEFAULT MAXWAIT,
   release_on_commit IN BOOLEAN DEFAULT FALSE
)
RETURN INTERGER ;

ここで、１つめのINパラメータ は「id」または「lockhandle」で、ふつうは先ほどのALLOCATE_UNIQUE()のOUTの「lockhandle」を指定するでしょう。(ちなみに「id」はDBMS_LOCK_ALLOCATED表というのを見ればわかります。)

２つめの「locakmode」では、「排他ロック」: X_MODE や「共有ロック」: S_MODE などのあらかじめ定義された定数を指定します。（ほかにもありますが、この２つで十分なことが多いと思います。「排他ロック」と「共有ロック」の違いについてはここで触れません。）

３つめはロックを取得をしようとするのに対してのtimeout(タイムアウト)を秒単位で指定、

４つめのBOOLEANはコミットまたはロールバックしたときに（COMMIT()またはROLLBACK()を呼び出したときに）ロックを解放するかどうか（デフォルトはFALSE）です。
これがFALSEでしかも明示的に解放されなかった(下記、RELEASE()が呼び出されなかった)としても、セッションが終われば解放されます。

戻り値のINTEGER(整数)は、0～5で、以下です。

  • 0 : 取得成功
  • 1 : タイムアウト
  • 2 : デッドロック発生！
  • 3 : パラメータエラー
  • 4 : すでにその「lockname」のロックを取得済み
  • 5 : 不正なロック操作

DBMS_LOCK.RELEASE() ファンクション

「locakhandle」 は19行目、ロックの「RELEASE(解放する)」にも使います。

      -- ロックを解放
      my_result := DBMS_LOCK.RELEASE(lockhandle => my_lockhandle);

ここで、DBMS_LOCK.RELEASE() ファンクションの定義は以下のようになります。

DBMS_LOCK.RELEASE( 
   id IN INTEGER || lockhandle IN VARCHAR2
)
RETURN INTERGER ;

INパラメータ 「id」または「lockhandle」で、REQUEST()と同様です。

戻り値のINTEGER(整数)は、0, 3, 4, 5で、以下です

  • 0 : 解放成功
  • 3 : パラメータエラー
  • 4 : その「lockname」のロックを取得していない
  • 5 : 不正なロック操作

何か間違いがありましたらコメントを頂ければ幸いです。

(参考: https://docs.oracle.com/cd/E57425_01/121/ARPLS/d_lock.htm)

-SQL
-, ,