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

[Tetsuo SAKATA]

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

コメントありがとうございます＞北村さん

先ほどの坂田のメールは，訂正しなければなりませんね．

北村さん wrote: [pgsql-jp: 30191] 
> > > 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文の実行が完了するまで，このテーブルの
> > #中身は他のトランザクションからは見えないと思います．
> 
> 説明不足で申し訳ないのですが、上記 SQL のみが多セッションか
> ら実行されるのです。AutoCommit で暗黙的トランザクションを利
> 用していました。BEGIN があり、その次にテーブルロック、そして
> 上記 SQL、COMMIT であれば問題ないと思います。
> 単文で実行される上記 SQL は、
> 1)テーブルtb 、カラムid の最大値を SELECT
> 2)その値に1をプラス
> 3)テーブルtb にインサート
> という流れにすぎません。このトランザクション(T1)が終了する前
> に他のトランザクション(T2)が同じ SQL を実行した場合、1) で
> SELECT される値は T1 の値と同値になってしまいます。

上記の動きでは，北村さんのご指摘のように，T1, T2によって
同じ値のタプルが挿入されてしまいますね．
先ほどのメールを訂正せねばなりません．

で，以下に述べているように，PostgreSQLのデフォルト設定では
実際にこのような動きをする可能性があると思います．

# 先ほどの坂田のメールでは，
# PostgreSQLでは，トランザクションの隔離性がデフォルトでは
# Read Committed であることを考慮していませんでした．
#
# Read Committed トランザクションであれば，上記のSQLの動作の
# 1)にて，各トランザクションはテーブルtb全体を読み出すものの，
#「その時点でコミット済みの値を見る」設定なので，他のトランザクションが
# 表tbにタプルを挿入している場合でも，その更新中のタプル以外は見ることが出来，
# 且つ3)でのタプルの挿入の際には，そのタプルに対してのみロックを
# 取得すれば動作を進めることが出来ます．
# その結果として，1)と3)の間で他のトランザクションに表tbを変更されてしまうことが
# 防止されておらず，上で指摘されている事象が生じる可能性があります．
# 隔離性を serializable に設定しておけば，このようなことは生じないはずです．(*)
#
# 参考URL
# http://www.postgresql.jp/document/pg732doc/user/transaction-iso.html


> 結果は先にも話したとおり、期待通りだった・・・わけです。汗
> 期待通りだったのですが、理屈から言うと「同値が存在してしまう」
> ハズなのに、ならない。実際、上のやり方で5回ほどやったのです
> が、「欠番なし」「同値なし」で出来てしまいました。

なるほど．
余談ですが，こういう予備試験の結果が出てしまうのも(偶然であり
制御できないのでしょうけど)コワイものがありますね．

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