LoginSignup
5
1

More than 5 years have passed since last update.

@tlokweng

DBMS_LOCK.SLEEPの代替SQLをいろいろ考えてみよう(Oracle)

Last updated at Posted at 2018-03-27

はじめに

DBMS_LOCK.SLEEPは、指定秒数何もせずただ待つために利用される有名なプロシジャですね。(ちなみに18cからDBMS_SESSION.SLEEPが標準になるらしいです）

ここでは、「指定秒数だけ待つという動作を、オラクルデータベースのいろいろな方法で実現してみようぜ！」という、なんの実用性もない話をすすめてみたいと思います。ただの遊びなんですが、こういう遊びからいろいろ調べたりして学ぶんですよね。

とりあえず、DBMS等のシステムパッケージは禁則として純粋にSQLまたはPL/SQLだけで考えてみます。

ループ系

PL/SQLループ

まぁ、普通に思いつくやつです。次行きましょう。

PL/SQLループ
DECLARE
    t   TIMESTAMP := SYSTIMESTAMP;
BEGIN
    WHILE SYSTIMESTAMP - t < INTERVAL '5' SECOND LOOP NULL; END LOOP;
END;
/

Elapsed: 00:00:05.00

ダイナミックパフォーマンスビュー

V$/X$ 系のテーブルは読み取り一貫性(read consistency)の管理下にないので、クエリ内でも参照するたびに値が変わっていきます。これを利用して、再帰しながらv$timerを繰り返し参照して５秒待つSQLを作ってみます。一応ちゃんと待ってはくれますが、負荷の高い全力再帰ループであるため、非力な環境では非常に精度が悪くなるみたいです。

WITH
    recur (n, ss) AS
        (SELECT 1, hsecs FROM v$timer
         UNION ALL
         SELECT n + 1, ss FROM v$timer, recur WHERE  hsecs - ss < 500 AND n > 0)
SELECT COUNT (*) FROM recur;

Elapsed: 00:00:05.02

ユーザファンクション

もうひとつ、クエリ（ステートメント）レベル読み取り一貫性の影響下にない動きといえば、ユーザファンクションですね。意識してない人も結構いるんですが、SQLクエリから呼ばれるユーザファンクション内ではデータは刻々と変化していくし、SYSDATEの値も変わりますからね1？

ともかく、TIMESTAMP2を返すファンクションを作って再帰させてみます。これも全力再帰ループなので負荷が高いですね。またここでは、インラインファンクションを使ってますが、ストアードファンクションでも同じです。

WITH
    FUNCTION func RETURN TIMESTAMP IS
    BEGIN
        RETURN SYSTIMESTAMP;
    END;

    recur (n, ts) AS
        (SELECT 1, func FROM DUAL
         UNION ALL
         SELECT n + 1, ts FROM recur WHERE  func - ts < INTERVAL '5' SECOND AND n > 0)
SELECT COUNT (*) FROM recur;

Elapsed: 00:00:05.01

非ループ系

行ロック競合

さてさて、ループ系ではシステムに負荷がかかるのでループしないスリープを考えてみます。ここで待ち時間指定のできるSQLステートメント SELECT FOR UPDATEの出番です。これを使って意図的にロック競合をおこし、指定時間待つプロシジャを作ってみます。

ロック競合をおこすには別トランザクションからテーブルの同じ行にアクセスする必要がありますので、AUTONOMOUS_TRANSACTION プロシジャを使用します。AUTONOMOUS_TRANSACTIONはセッション内で独立したトランザクションを使用するストアードプログラムのPRAGMAオプションです。

ところが、同一セッション内で起こるロック競合はデッドロックエラーの対象となり、一定時間以上のロック待ちは強制キャンセルされてしまいます。従って、長期のロック待ちを実現するにはセッション内で別セッションを用意する必要もあります。

If an autonomous transaction attempts to access a resource held by the main transaction (which cannot resume until the autonomous routine exits), a deadlock can occur.

別セッションをつくる一番簡単な方法はやはりパラレルクエリでしょう。パラレルクエリ内でユーザファンクションを呼び出します。ただし、ユーザファンクションを並列実行するためには、PARALLEL_ENABLEオプションが必要で、またこのオプションはトップレベルにしか適用することができません。従って呼び出し元のプロシジャとロック待ちのファンクションの2つのプログラムが必要となります。
ちなみにDB LINKでループバックさせても別セッションになりますが、分散セッションでのロック競合の最大待ち時間は DISTRIBUTED_LOCK_TIMEOUTというダイナミック変更不可のパラメータで定義されているためここでは使えません。

まず、ロック競合の場となるテーブルを作ります。

ロック用テーブル
CREATE TABLE sleep_lock_tbl (id varchar2(10));

次に、自立トランザクションでかつ並列実行可能なファンクションとそれを呼び出すプロシジャを作ります。プロシジャでは、対象テーブルにユニークな行を挿入し行ロックをかけた上で、パラレルクエリでファンクションを呼び出します。ファンクションでは、SELECT FOR UPDATE WAITを用いてロック競合を指定期間意図的に起こします。

行ロック
CREATE OR REPLACE FUNCTION sleep_sec_wait (s VARCHAR2, sec NUMBER)
    RETURN VARCHAR2
    PARALLEL_ENABLE
IS
    PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
    EXECUTE IMMEDIATE 'select id from sleep_lock_tbl where id = :s for update wait ' || TO_CHAR (sec) USING s;
    RETURN s;
END;
/

CREATE OR REPLACE PROCEDURE sleep_sec (sec IN NUMBER DEFAULT 1)
IS
    s   VARCHAR2(10);
BEGIN
    SELECT USERENV ('instance') || ',' || USERENV ('sid') INTO s FROM DUAL;

    DELETE FROM sleep_lock_tbl WHERE id = s;
    INSERT INTO sleep_lock_tbl VALUES (s);
    COMMIT;

    SELECT id INTO s FROM sleep_lock_tbl WHERE id = s FOR UPDATE;
    SELECT /*+ parallel(a 2) */ sleep_sec_wait(s, sec) INTO s FROM sleep_lock_tbl a WHERE id = s;
    -- parallel hint or
    -- execute immediate 'alter session force parallel query parallel 2';

    EXCEPTION
        WHEN OTHERS THEN
        DELETE sleep_lock_tbl WHERE id = s;
        COMMIT;
END;
/

実行してみます。環境によっては、なぜかだか指定した秒数より微妙に早く終わります。よくわからんですけど、内部的に秒単位しかみてないのかもしれません。デッドロックエラーが起きないことを確認するため少し長めにも待ってみますが、うまく行ってるようです。

exec sleep_sec(80);

Elapsed: 00:01:19.09

テーブルロック競合

テーブルロック（排他ロック）を用いても同様なロック待ちが得られます。やり方はほぼ行ロックと同じです。マルチセッションからの同時コールに対応するためには、個別テーブル作成が必要となります。また、DDL_LOCK_TIMEOUTパラメータを利用すれば、WAIT句のないDDLでもロック待ち時間指定が可能となります。

テーブルロック
CREATE OR REPLACE FUNCTION sleep_ddl_wait (tname VARCHAR2, sec IN NUMBER, dummy VARCHAR2)
RETURN NUMBER
PARALLEL_ENABLE
IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
    EXECUTE IMMEDIATE 'LOCK TABLE ' || tname || ' IN EXCLUSIVE MODE WAIT ' || sec;
  --OR
  --EXECUTE IMMEDIATE 'ALTER SESSION SET DDL_LOCK_TIMEOUT = ' || TO_CHAR(sec);
  --EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || tname;

    RETURN NULL;
END;
/

CREATE OR REPLACE PROCEDURE sleep_ddl (sec IN NUMBER DEFAULT 1)
AUTHID CURRENT_USER
IS
tname VARCHAR2(30);
n     NUMBER;

BEGIN
    tname := 'lock_tbl_' || USERENV('instance') || '_' || USERENV('sid');
    EXECUTE IMMEDIATE 'CREATE TABLE ' || tname || '(n NUMBER)';

    EXECUTE IMMEDIATE 'LOCK TABLE ' || tname || ' IN EXCLUSIVE MODE';
    SELECT /*+ PARALLEL(a 2) */ sleep_ddl_wait(tname, sec, dummy) INTO n FROM DUAL a;

    EXCEPTION
        WHEN OTHERS THEN
        EXECUTE IMMEDIATE 'DROP TABLE ' || tname;
END;
/

exec sleep_ddl(35)

Elapsed: 00:00:34.31

再開可能セッション

再開可能（RESUMABLE）セッションは、DMLがデータ領域不足で正常に実行できない場合にも即時エラーとせず必要な領域が確保されるまで待たせておくことができるという便利機能です。で、RESUMABLE_TIMEOUTに待ち時間を指定して、意図的にテーブルスペースの領域不足をおこしやるというかなりシステム寄りの方法になります。まぁ、遊びなのでやるだけやってみます (^^)。

準備(SYS)
CREATE TABLESPACE testts nologging DATAFILE SIZE 81k autoextend OFF; 

ALTER USER testuser QUOTA UNLIMITED ON testts; 
GRANT resumable TO testuser;
再開可能セッション
ALTER SESSION SET resumable_timeout = 10; 
ALTER SESSION ENABLE resumable;

CREATE TABLE resumable_test 
TABLESPACE testts AS 
  SELECT RPAD(TO_CHAR(LEVEL), 4000, 'X') s 
  FROM   dual 
  CONNECT BY LEVEL <= 100; 

ORA-30032: the suspended (resumable) statement has timed out

Elapsed: 00:00:12.07

JAVA言語拡張

これをSQLと言ってよいのかどうか大いに疑問の余地がありますが、オラクルのJAVA言語拡張です。標準クラスを使えばあっけないほど簡単にできます。

CREATE OR REPLACE PROCEDURE sleep_java (n IN NUMBER)
AS
    LANGUAGE JAVA
    NAME 'java.lang.Thread.sleep(int)' ;
/

exec sleep_java(5000);

Elapsed: 00:00:05.03

ユーザプログラムの場合はJAVAオブジェクトを作って、同様にプロシジャから呼ぶだけです。

CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED SleepSec
AS
public class SleepSec
{
    public static int sleep(int sec)
    {
        try {
            Thread.sleep(sec * 1000);
        } catch(InterruptedException e) {}
        return 0;
    }
}
/

CREATE OR REPLACE PROCEDURE sleep_java_sec (sec IN NUMBER)
AS
    LANGUAGE JAVA
    NAME 'SleepSec.sleep(int)' ;
/

exec sleep_java_sec(5);

Elapsed: 00:00:05.01

C言語拡張(共有ライブラリ)

最後に、Cライブラリ拡張です。10gでは設定が非常に面倒くさかった外部プロシジャコールを使いますが、11gからはリスナーに完全に統合されほぼ設定不要です。まぁ余り使う機会はないですけどね。

ここでは、ライブラリのパス設定を省略するために、作成したCライブラリファイルをデフォルトパスである $ORACLE_HOME/lib に移動しています3。

$ cat <<EOF > sleep_sec.c
#include <unistd.h>

int sleep_sec(int sec){
  return sleep(sec);
}
EOF

$ gcc -m64 -fPIC -c sleep_sec.c 
$ gcc -m64 -shared -o sleep_sec.so sleep_sec.o 
$ mv sleep_sec.so $ORACLE_HOME/lib

CREATE OR REPLACE LIBRARY sleep_c_lib as '<oracle home>/lib/sleep_sec.so';
/

CREATE OR REPLACE PROCEDURE sleep_c (sec IN BINARY_INTEGER)
AS
    LANGUAGE C
    LIBRARY SLEEP_C_LIB
    NAME "sleep_sec"
    PARAMETERS (sec int);
/

exec sleep_c(5);

Elapsed: 00:00:05.04

ほかにもあるかもしれません、というかなにがしかあると思いますが、いまのところ以上です。

  1. SYSDATE/SYSTIMESTAMP系はもともと動きが少し特殊ですけどね。 ↩

  2. 秒数以下が切り捨てられますが、DATEでももちろん可。 ↩

  3. ライブラリのパスを許可するには、$ORACLE_HOME/hs/admin/extproc.ora を使用します。 ↩

5
1
2

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
5
1