Oracle/PostgreSQL:シーケンスが飛ぶ

postgresql
Oracleのシーケンスの歯抜け
データベースに Oracle を利用しているシステムで、シーケンスがちょくちょく飛ぶという現象が発生した。

調べてみると、Oracle では高速に採番するために、決められた数だけ予めメモリに確保するようになっており、サーバの再起動などでメモリが開放されると、確保していた番号は使用済み、未使用にかかわらず破棄され、未確保の領域から新規に番号を確保する。

Oracle の場合、デフォルトのキャッシュサイズは 20 なので、キャッシュした番号が破棄されると、1, 2, 3, 歯抜け 21, 22, … というように20の単位で新規に採番される。

マニュアルでは、キャッシュすることを積極的にすすめている

Oracle recommends using the CACHE setting to enhance performance if you are using sequences in a Real Application Clusters environment.
http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_6015.htm

PostgreSQLのシーケンスの歯抜け

PostgreSQL も採番の仕組みは同じ、ただしこちらはキャッシュサイズが1なので、ライブラリなどがキャッシュサイズをいじっていない限り、歯抜けは発生しにくい。一方で、PostreSQL ではセッションごとに番号が確保されるので、歯抜けが発生しやすい。

マニュアルには歯抜け現象が親切に記載されている

Unexpected results might be obtained if a cache setting greater than one is used for a sequence object that will be used concurrently by multiple sessions. Each session will allocate and cache successive sequence values during one access to the sequence object and increase the sequence object’s last_value accordingly. Then, the next cache-1 uses of nextval within that session simply return the preallocated values without touching the sequence object. So, any numbers allocated but not used within a session will be lost when that session ends, resulting in “holes” in the sequence.
http://www.postgresql.org/docs/9.2/static/sql-createsequence.html

SEQUENCEに歯抜けを発生させない

SEQUENCE 作成時に CACHE に1を指定すればOK。

create sequence no_hole cache 1;

with a cache setting of one it is safe to assume that nextval values are generated sequentially; with a cache setting greater than one you should only assume that the nextval values are all distinct, not that they are generated purely sequentially.
http://www.postgresql.org/docs/9.2/static/sql-createsequence.html

SEQUENCEに歯抜けを発生させる

PostgreSQL で Oracle と同じようにキャッシュサイズ20でシーケンスを作成。
DBサーバをリスタートしたり、別セッションからシーケンスを呼び出して、歯抜けを発生させてみる。

test=# create sequence test1 cache 20;
CREATE SEQUENCE

test=# select nextval('test1');
 nextval
---------
       1
(1 row)

test=# select currval('test1');
 currval
---------
       1
(1 row)

test=# select nextval('test1');
 nextval
---------
       2
(1 row)

# restart server in the background

test=# select nextval('test1');
FATAL:  terminating connection due to administrator command
server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.
The connection to the server was lost. Attempting reset: Succeeded.
test=# select nextval('test1');
 nextval
---------
      21 (1 row)  <- hole in the sequence

# use sequence from another session

$ psql test -c "select nextval('test1')"
 nextval
---------
      41 (1 row)  <- hole in the sequence
Advertisements
Tagged with: , ,
Posted in database

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Archives
%d bloggers like this: