- PR -

PostgreSQL での同時挿入について

1|2 次のページへ»
投稿者投稿内容
knock
会議室デビュー日: 2003/09/02
投稿数: 17
投稿日時: 2003-11-28 11:27
こんにちは。
現在 PostgreSQL 7.3.2 を使ってデータベースを勉強しております。
そこで疑問に思ったことがありますので、質問させていただきます。

例えば商品受注データをデータベースで管理しようとしたときに、
受注テーブルと受注詳細テーブルを以下のように用意したとします。

CREATE TABLE 受注 (
　受注ID SERIAL,
　顧客ID INTEGER,
　受注日時 TIMESTAMP,
　PRIMARY KEY(受注ID)
);

CREATE TABLE 受注詳細 (
　受注ID INTEGER,
　受注詳細ID INTEGER,
　商品ID INTEGER,
　注文数 INTEGER,
　PRIMARY KEY(受注ID, 受注詳細ID)
);

※顧客ID と商品 ID を格納するテーブルは別途用意してあります。

受注が発生した時、受注テーブルと受注詳細テーブルにデータを追加する必要がある
のですが、受注詳細テーブルに挿入する受注 ID はどのように取得すればいいのでしょうか？
直感的にはトランザクションを用いて
1. まず受注テーブルにデータ挿入
2. SELECT 文で最新の受注テーブルの受注 ID を取得
3. 2.で取得した受注 ID を用いて受注詳細テーブルに挿入
という流れになるのかなと思うのですが、いかかでしょうか。

トランザクション周りについてきちんと理解していないためにこのような疑問が
生じたとは思っておりますが、ぜひともご指導の程よろしくお願いいたします。

[ メッセージ編集済み 編集者: knock 編集日時 2003-11-28 11:31 ]
アティ
ベテラン
会議室デビュー日: 2003/08/14
投稿数: 91
お住まい・勤務地: KANAGAWA
投稿日時: 2003-11-28 12:00
SERIAL型を使うのであれば、それでいいと思います。
SERIAL型は、シーケンスを用いているので、あとは、
Googleで「PostgreSQL シーケンス」で検索すれば出てくると思いますよ。
_________________
_/_/_/
_/うちの会社の変なところ〜
_/1条.毎年300人新卒採用
_/2条.大事な事項(就業規則等)の変更発表は、施行前日
_/(以下略)
knock
会議室デビュー日: 2003/09/02
投稿数: 17
投稿日時: 2003-11-28 12:27
アティ様

早速のご返信ありがとうございます。
調べましたところ、SELECT 文で currval を使えばきちんと最新のシーケンス番号が
取れるようですね。
またトランザクションできちんとテーブルをロックしていないと受注テーブルに挿入してから
受注詳細テーブルに挿入する間に、別の受注データが挿入されてしまい、その状態で
最新のシーケンス番号を取得しても２つのデータの整合性が取れないですね...
まだ手探りの状況で色々試さないとダメですね。

もし、このような状況で他に一般的な方法がありましたらアドバイスよろしくお願いします。
永井和彦
ぬし
会議室デビュー日: 2002/07/03
投稿数: 276
お住まい・勤務地: 東京都
投稿日時: 2003-11-28 12:43
引用:

knockさんの書き込み (2003-11-28 12:27) より:
アティ様

早速のご返信ありがとうございます。
調べましたところ、SELECT 文で currval を使えばきちんと最新のシーケンス番号が
取れるようですね。
またトランザクションできちんとテーブルをロックしていないと受注テーブルに挿入してから
受注詳細テーブルに挿入する間に、別の受注データが挿入されてしまい、その状態で
最新のシーケンス番号を取得しても２つのデータの整合性が取れないですね...
まだ手探りの状況で色々試さないとダメですね。

もし、このような状況で他に一般的な方法がありましたらアドバイスよろしくお願いします。


一トランザクション内で、

1. まずSEQUENCEからnextvalで値を取得(受注ID)
2. 1で得た値を用いて受注テーブルにINSERT
3. 1で得た値を用いて受注詳細テーブルにINSERT
4. COMMIT

が安全そうなのではないかなと思います。
プリンス
ベテラン
会議室デビュー日: 2003/07/05
投稿数: 78
お住まい・勤務地: 神奈川
投稿日時: 2003-11-28 12:53
永井さんのやり方のほかに、採番テーブルを使うほう方があります。
実業務では受注IDに日付等をつけたり、ブレーク処理する場合があるためです。

シーケンスは

Begin
Select for update で採番テーブルから最新の番号(受注ID)を採る。
番号を更新する。
Commit

Begin
受注ヘッダにInsert (取得した受注IDで)
受注明細にInsert (取得した受注IDで)
Commit

になります。
knockさんは少し誤解してますが、基本的にテーブルロックとトランザクションは違います。
このロジックでは受注トランザクションでテーブルロックは使用していないのに注意してください。
受注IDはプライマリキーですのでインデックスが使用され、レコードロックが使用され、トランザクションが平行して走ります。
knock
会議室デビュー日: 2003/09/02
投稿数: 17
投稿日時: 2003-11-28 13:30
永井様　プリンス様
ご返答ありがとうございます。

プリンスさんの方法は考えてもいませんでした。つまり受注番号を管理する採番テーブルを別途用意して行うということでよろしいでしょうか。
確かにロックの仕組みをきちんと理解しておらず恥ずかしい限りです...

さらに質問したいのですが、永井さんの方法で一トランザクションを実行した際に
明示的なロックは行う必要はないということでよろしいのでしょうか？
どうもトランザクションを実行する際にテーブル全体をロックしていないと気持ちが
悪い印象です。

また、プリンスさんがおっしゃている以下のような方法ですと

A:Begin
A:Select for update で採番テーブルから最新の番号(受注ID)を採る。
A:番号を更新する。
A:Commit

B:Begin
B:受注ヘッダにInsert (取得した受注IDで)
B:受注明細にInsert (取得した受注IDで)
B:Commit

トランザクションA を実行した直後に再度トランザクションA が実行された場合には
トランザクションB が一回分実行されない可能性はないのでしょうか？
もしくはそれを防ぐ方法が select for update なのでしょうか？

何回も恐縮ですが、ご教授よろしくお願いいたします。
アティ
ベテラン
会議室デビュー日: 2003/08/14
投稿数: 91
お住まい・勤務地: KANAGAWA
投稿日時: 2003-11-28 14:10
引用:

永井和彦さんの書き込み (2003-11-28 12:43) より:

一トランザクション内で、

1. まずSEQUENCEからnextvalで値を取得(受注ID)
2. 1で得た値を用いて受注テーブルにINSERT
3. 1で得た値を用いて受注詳細テーブルにINSERT
4. COMMIT

が安全そうなのではないかなと思います。

knockさんの書いた場合も、安全だと思いますよ。
curvalで取得される値は、自セッションで取得された最新の値ですから。
プリンス
ベテラン
会議室デビュー日: 2003/07/05
投稿数: 78
お住まい・勤務地: 神奈川
投稿日時: 2003-11-28 14:35
引用:


A:Begin
A:Select for update で採番テーブルから最新の番号(受注ID)を採る。
A:番号を更新する。
A:Commit

B:Begin
B:受注ヘッダにInsert (取得した受注IDで)
B:受注明細にInsert (取得した受注IDで)
B:Commit

トランザクションA を実行した直後に再度トランザクションA が実行された場合には
トランザクションB が一回分実行されない可能性はないのでしょうか？
もしくはそれを防ぐ方法が select for update なのでしょうか？

何回も恐縮ですが、ご教授よろしくお願いいたします。

トランザクションAは永井さんの自動採番の部分に相当します。
SELECT FOR UPDATEがPostgreSQLにあるか知らないですが、
要するに採番レコードに対して更新ロックを掛けてますので、
べつのスレッドがトランザクションAを開始するとブロックされます。
no waitをつけるとあきらめてロールバックします。

一般的にトランザクションテーブル全体にロックをかけることは
しません。トランザクションが逐次実行になり基幹システムとしては
用をなしません(スループットがあがらない)。またロックを持ったスレッドが暴走したらアウトです。またデッドロック発生の危険性をはらんでいます。
1|2 次のページへ»

スキルアップ／キャリアアップ（JOB@IT）