[pgsql-jp: 30189] Serial型の列とトランザクション

[Tetsuo SAKATA]

こんにちは．
坂田@横須賀です．

もともとの佐藤さんの疑問は解決したようですね．
この件に関連して，個人的に連番の扱いについて
興味があるので，調べてみました．

北村さん wrote: [pgsql-jp: 30179] 
> 「連番」を要求しているのであれば、SERIAL型を使う設計が間違っ
> ています。
> 
> id を int4 などにして、
> BEGIN
> 　テーブルをロック
> 　最大値+1を取得
> 　インサート
> COMMIT
> のトランザクション処理が必要だと思われます。
> 
> insert into tb ( id,int,txt )
> values (
>  (select max(t2.id) + 1 from tb t2),
>   11,
>   'abc'
> );
> というようにテーブルロックを使わずに書いてあるのを見たことが
> ありますが、どれくらいの確率か解りませんが、タイミングによっ
> ては id が競合してしまう(PRIMARY KEY ならエラー)となる「気が
> します」。

上記のinsert文を複数のトランザクションで実行しても，
期待した動作をするのではないかと思います．

なぜなら，select max(...) にて，表tb全体を(t2という相関名で)
参照していますから，insert文を実行する間は，max(t2.id)の
値は変わらないと考えられるからです．

#あるいは，隔離性水準を serializable としてファントム防止が
#なされていれば，insert文の実行が完了するまで，このテーブルの
#中身は他のトランザクションからは見えないと思います．

北村さん wrote:[pgsql-jp: 30184]
> PostgreSQL のマニュアル、CREATE SEQUENCE を参照してください。
> シーケンスは、最小値、最大値、開始番号、増加量、キャッシュ数
> などを設定できます。先ほどのメールで説明が変だったので申し訳
> ないのですが、増加量 1 でキャッシュ数 5 の場合、最初のトラン
> ザクション(T1)は 1〜5 を確保(キャッシュ)します。このトランザ
> クションがコミットまたはロールバックする前に別のトランザクショ
> ン(T2)が実行されると、そのトランザクションは 6〜10 をキャッ
> シュします。
> ここは、記憶が曖昧で間違っているかもしれません。終了した「後」
> でもキャッシュ分は破棄されていたかもしれません。つまり、T1
> が 1〜3を使って終了した後に T2 が開始されても、T1でキャッシュ
> 対象であった 4,5 は T1 によって破棄されているかもしれない、
> という意味です。

(commitであれrollbackであれ)トランザクションの終了後は，
キャッシュされていた(＝予約されていた)値は全て破棄されます．

CREATE SEQUENCEのリファレンスマニュアル
http://www.postgresql.jp/document/pg732doc/reference/sql-createsequence.html
の中の注釈の項にある通りです．

(穴(欠番)を無くそうとすると，穴の部分も含めて管理する必要があり，それを
実施すると，結局は以下に説明するのと同じ理屈で，スループットが出なくなる
要因となります)

このような連番のための専用の仕組みが用意されているのは，
谷田さんがお書きになっているように，

谷田さん wrote:[pgsql-jp: 30180] 
> Sequenceについてはその通りです。なんとなれば
> 
> (8 tabでご覧ください)
> tran A          Tran B
> begin
>                 begin
> ser=1
>                 ser=2
>                 commit
> rollback
> 
> のような場合を適切に処理することが出来ません。これを正しく行うためにはロッ
> クが不可欠です。

上記の処理の流れでは，Tran Aのロールバックを処理できないので，
通番(確実に1つずつ増えていく連番，という意味で仮にこの用語を使います)を
実現できていませんね．

これを正しく処理するためには，Tran Aが終了するまで，
Tran Bにてserの値を参照することを禁止する仕組みが必要です．
(上の北村さんの例では，それが実現されていると思います)

しかし，これを正しく実現すると，Tran A が終了するまで Tran Bの
serの参照は待機させられますから，システム全体としてのスループットが
上がらない要因になります．
これは，
http://www.postgresql.jp/document/pg732doc/user/functions-sequence.html
のimportantの項にある通りです．

こういう問題を回避するために，PostgreSQLでは serial 型という
特殊なデータタイプを用意しているわけですね．

> 詳しくは「トランザクション処理」と言う本に、なぜこのような連番がトランザ
> クションで扱うのが難しいのということについての説明があります。相当に難し
> い本ですが。

上記の『トランザクション処理』は，多分これ↓のことだと思いますが，
http://bpstore.nikkeibp.co.jp/item/main/148222810270.html
この本の中には，通番の問題(§7.12, P.525〜)として触れられています．

--------
追伸：
あと，以下の文章を読んでいて気になる点があります．
http://www.postgresql.jp/document/pg732doc/user/datatype.html#DATATYPE-SERIAL
の中の，「5.1.4. シリアルデータ型」の項にて，

『serial は正確にはデータ型ではなくてテーブルの列に一意の識別子を
設定する簡便な表記法です。』
と書かれていますが，テーブルの列(の集まり)に対して，
個別に識別子が設定されるように読めてしまいます．

原文は
http://www.postgresql.org/docs/view.php?version=7.3&idoc=0&file=datatype.html#DATATYPE-SERIAL
"The serial data types are not truly types, but are a notational convenience 
for setting up unique identifier columns in tables."
ですから，多少言葉を補って，

『serialは正確にはデータ型ではなくて，テーブルに対して一意性のある
識別子として用いる列を追加する簡便な表記法です。』

とでもした方が良いのではないかと思いました．
--------

以上，ご参考まで．
-- 
Tetsuo SAKATA, Yokosuka JAPAN.