Oracle® 非公式 SQL, PL/SQL & DBA's リファレンス

連続した複数のシーケンス番号を一括して取得する方法

シーケンスは順序値を生成する便利な機能であるが、複数セッションから同時に 連続した複数のシーケンス番号 を発番しているような処理の場合にはセッション間で番号が互い違いになる可能性がある。

これを回避するためにアプリケーションによるロック機構を使用し採番処理を直列化（シリアライズ／シリーズ 化）することで 同時処理を制限する。
しかし、代償として本来は並列可能な処理を排他処理で直列化することによる同時実行性能が低下する可能性がある。

シーケンスのシリアライズを実現するためにアプリケーションによるロック（アプリケーション・ロック／ユーザー・ロック）を使用する。 Oracle にはユーザー・ロックを使用するためのパッケージ (DBMS_LOCK) が用意されている。 （初期状態では PUBLIC に対して呼び出し権限が付与されていないので使用する場合には権限の付与が必要）

シーケンスの発番とロックのタイミング

シーケンスを複数セッションで同時に発番しないためにユーザー・ロックを使用する方法は大まかに２つ考えられる。

1. シーケンスを使用するトランザクション全体を直列化する
2. シーケンスを必要なだけ先行予約するプロシージャを用意して、その呼び出しの間だけを直列化する

ここでは 2. のケースで実装してみる。
（ 1. の場合には発番する直前の部分で COMMIT 時に自動的にロックをリリースするモードのロックを取得する）

複数連番ジェネレーター／シーケンスを必要なだけ先行予約するファンクション

このファンクションはシーケンス名とシーケンスの予約分を指定して呼び出す。戻り値は発番した最初の番号となる。

ソースにおけるポイントと注意点

• ロック名としてシーケンス名を使用
• SQL から呼び出すために AUTONOMOUS_TRANSACTION 宣言としている（DBMS_LOCK で暗黙コミットが発生する）
• ロック取得の待ち時間は 3秒 (TIMEOUT_SEC 定数)
  タイムアウト時には例外 (TIMEOUT_ON_RESOURCE) が発生
• ロックの解放は COMMIT ではなく明示的な解放
• シーケンス番号の取り出しに DUAL 表をループで使用している（処理件数により遅くなる可能性がある(※)）
  Oracle 11g からは PL/SQL から直接シーケンスオブジェクトにアクセスできるので SQL が不要。
• 増分が +1 以外の場合や巡回(CYCLE) するシーケンスについて考慮していない
• ユーザー・ロックが施されていない場所で sequence.NEXTVAL が並列して実行されている場合には効果がない

(※) 増分値が尋常ではない場合には 自律型トランザクション と シーケンスの現在値を再設定する を 応用して ALTER SEQUENCE INCREMENT BY で増分値を一時的に増加させる手法などを用いるとよいだろう。

BULKCOLLECT_SEQ ファンクション

CREATE OR REPLACE FUNCTION RIVUS.BULKCOLLECT_SEQ(
	P_SEQNAME	IN VARCHAR2,
	P_COUNT	IN NATURALN
)
RETURN NUMBER
IS
	PRAGMA AUTONOMOUS_TRANSACTION;
	eLockFailure	EXCEPTION;
 
	TIMEOUT_SEC	CONSTANT NUMBER := 3;
 
	vSQL		VARCHAR2(100);
	vSeq		NUMBER;
	vDummy		NUMBER;
	vSeqName	VARCHAR2(30);
	vLock		VARCHAR2(128);
	vLockState	NUMBER;
BEGIN
	vSeqName := UPPER(P_SEQNAME);
	-- USER LOCK SECTION
	DBMS_LOCK.ALLOCATE_UNIQUE(vSeqName, vLock);
	vLockState := DBMS_LOCK.REQUEST(vLock, DBMS_LOCK.X_MODE, TIMEOUT_SEC);
	IF (vLockState <> 0) THEN
		RAISE eLockFailure;
	END IF;
	-- LOCK SECTION END
 
	vSQL := 'SELECT ' || vSeqName || '.NEXTVAL INTO :SEQ FROM DUAL';
	EXECUTE IMMEDIATE vSQL INTO vSeq;
	FOR i in 2..P_COUNT
	LOOP
		EXECUTE IMMEDIATE vSQL INTO vDummy;
	END LOOP;
	-- USER UNLOCK
	vLockState := DBMS_LOCK.RELEASE(vLock);
	IF (vLockState <> 0) THEN
		RAISE eLockFailure;
	END IF;
	-- UNLOCK SECTION END
 
	RETURN vSeq;
EXCEPTION
	WHEN OTHERS THEN
		RAISE TIMEOUT_ON_RESOURCE;
END;
/

-- SIMPLE_SEQ というシーケンスから 30 回分のシーケンスを予約する
BEGIN
	...
	vSeqNo := BULKCOLLECT_SEQ('SIMPLE_SEQ', 30);
	INSERT ... VALUES (vSeqNo, ...);
	INSERT ... VALUES (vSeqNo+1, ...);
	...
END;

注意点

• RAC 環境でシーケンスのキャッシュを有効にしている場合には連続した番号を取得できない場合がある。
  ⇒ 参考： シーケンスについての FAQ
• DBMS_LOCK は PUBLIC ロールに含まれていないため、別途実行権限を付与する必要がある。

DBMS_LOCK パッケージの実行権限を GRANT する例

SQL> CONN / AS SYSDBA
SQL> GRANT EXECUTE ON DBMS_LOCK TO RIVUS ;

参考： ロール ／ 定義者権限と実行者権限

---