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
  • RT @__apf__: How to write a research paper: a guide for software engineers & practitioners. docs.google.com/presentation/d… /cc @inwyrd 6 months ago
  • RT @HayatoChiba: 昔、自然と対話しながら数学に打ち込んだら何かを悟れるのではと思いたち、専門書1つだけ持ってパワースポットで名高い奈良の山奥に1週間籠ったことがある。しかし泊まった民宿にドカベンが全巻揃っていたため、水島新司と対話しただけで1週間過ぎた。 それ… 6 months ago
  • RT @googlecloud: Ever wonder what underwater fiber optic internet cables look like? Look no further than this deep dive w/ @NatAndLo: https… 6 months ago
  • @ijin UTC+01:00 な時間帯で生活しています、、、 1 year ago
  • RT @mattcutts: Google's world-class Site Reliability Engineering team wrote a new book: amazon.com/Site-Reliabili… It's about managing produc… 1 year ago
%d bloggers like this: