シーケンス（順序）で欠番が発生する原因5つ

time 2017/03/02

folder ORACLE

ORACLEのシーケンス（順序）で欠番が発生することがあります。

本記事ではその原因について、まとめました。

シーケンスで欠番が発生！

シーケンスは、一意の値を生成するのに非常に便利です。

例えば、1から採番を開始、1つずつカウントアップするシーケンスを作成し、受注データの伝票番号を自前で採番する時に使用するといった使い方をします。

1つずつカウントアップするので、基本的には欠番は発生しないはずです。しかし、欠番が起きることがあります。

-- receive_order（受注テーブル）
--  1.customer_id（得意先ID）
--  2.order_no（伝票番号） ← シーケンスを利用して採番
--  3.insert_date（登録日）

CUSTOMER_ID ORDER_NO   INSERT_DATE
----------- ---------- -----------
00100       0000000001 20170301
00200       0000000002 20170301
00100       0000000003 20170302
00300       0000000021 20170303     ← 伝票番号が欠番している
00300       0000000022 20170303

-- receive_order（受注テーブル）

-- 1.customer_id（得意先ID）

-- 2.order_no（伝票番号） ← シーケンスを利用して採番

-- 3.insert_date（登録日）

CUSTOMER_ID ORDER_NO INSERT_DATE

----------- ---------- -----------

00100 0000000001 20170301

00200 0000000002 20170301

00100 0000000003 20170302

00300 0000000021 20170303 ← 伝票番号が欠番している

00300 0000000022 20170303

欠番が発生する原因

シーケンスを利用していて欠番が発生する原因は、以下の5つが考えられます。

ロールバック

ロールバックでデータの更新を取り消しても、カウントアップしたシーケンスは元に戻りません。トランザクション中にORACLEがダウンした場合も、再起動時のインスタンスリカバリでロールバックされるので同様に欠番が発生します。

SELECT test_seq1.NEXTVAL a FROM DUAL;
    A
-----
    1

ROLLBACK;

SELECT test_seq1.NEXTVAL a FROM DUAL;
    A
-----
    2

SELECT test_seq1.NEXTVAL a FROM DUAL;

-----

ROLLBACK;

SELECT test_seq1.NEXTVAL a FROM DUAL;

-----

ORACLEの再起動

ORACLEを再起動すると、事前にキャッシュしておいた値がクリアされてしまうため欠番が発生します。

シーケンスのキャッシュは、処理速度向上のために共有プール上に事前にシーケンスから値を取得して保持しておく機能です。

ORACLEを再起動すると、共有プールがクリアされてしまうため、キャッシュしておいたシーケンス値もクリアされ、結果として欠番となってしまいます。シーケンスをキャッシュしない設定の場合は発生しません。

-- キャッシュ20
SELECT test_seq1.NEXTVAL a FROM DUAL;
    A
-----
    1

～ORACLE再起動～

SELECT test_seq1.NEXTVAL a FROM DUAL;
    A
-----
   21

-- キャッシュ20

SELECT test_seq1.NEXTVAL a FROM DUAL;

-----

～ORACLE再起動～

SELECT test_seq1.NEXTVAL a FROM DUAL;

-----

キャッシュは、初回のシーケンスアクセス時と、キャッシュされた値を使い切った時に行われます。つまり、ORACLE起動後、一度もシーケンスにアクセスしていない状態でORACLEの再起動があっても、欠番は発生しません。

また、RAC環境の場合は、各ノードごとにキャッシュが行われているので、RACを構成する内の1つのノードが再起動した場合でも、欠番が発生します。

共有プールのクリア

明示的に共有プールのクリアを行った場合も、欠番が発生します。

これについても、シーケンスをキャッシュしない設定の場合は発生しません。

-- キャッシュ20
SELECT test_seq1.NEXTVAL a FROM DUAL;
    A
-----
    1

-- 共有プールのクリア
ALTER SYSTEM FLUSH SHARED_POOL;

SELECT test_seq1.NEXTVAL a FROM DUAL;
    A
-----
   21