複雑なinsert文の書き方について

質問

この春プログラミング業界に就職しました新入社員です。
皆様のお知恵を拝借したく、質問させていただきました。

先日、以下のような処理を任されました。

・顧客ID,注文NO,商品NO,処理日時からなる注文テーブルに、
・同一商品の送付対象となる顧客のIDリスト（csvファイル）を元に、
・新しい注文を追加する

注文テーブルの主キーは顧客IDと注文IDを連結したものであり、
顧客001が過去に３回注文しているとすると、
今回追加すべきレコードは001,4,商品NO,処理日時となります。

急ぎの仕事でしたので格好良さは度外視し、
注文テーブルの注文NOの最大値を求め、
select文で顧客のIDリストと注文テーブルを連結して注文NOを1から最大値まで順に指定して表示し、
顧客IDごとに今回入力すべき注文NOを把握して、
IDリストのcsvファイルをエクセルで編集して顧客ID,注文NO,商品NOからなる
テーブルを作って注文テーブルに流し込み、
注文テーブルの処理日時がNULLになっているレコードを指定してgetdate()で現在時刻を放り込みました。

今回は幸いにして注文NOの最大値が3と小さく、
対象の顧客も1000人ほどと少なかったためにごり押しできましたが、
今後も同じような処理をする必要があるときに同じ手が通じるかどうか、
というかミスが怖くて二度と使いたくありません。

もっとスマートな方法があるのではないかと調べてはみたのですが、
顧客IDごとに注文IDの最大値+1を求めてinsertする辺りの処理が探し当てられず、
ここで質問させていただいた次第です。
よい方法をご存知の方がおられましたら、ご教授いただけましたら幸いです。

回答No.4

nora1962 · ベストアンサー

指摘したことの繰り返しになりますが、ユーザーA，Ｂがいてほぼ同時に作られたSQLを実行した場合、後続のSQLは先行のSQLの終了を待ちません。すなまち、同じMAX値を取得します。

作られたSQLシングルユーザーで間をおいて実行している限りは正常に動くでしょう。しかし、マルチユーザーで負荷が高く鳴った時はエラーを起こす可能性をもっています。潜在的バグですね。

お礼

お礼が遅くなりました。
基本的にＤＢの更新は誰もアクセスしていないことを確認した上で行いますので、とりあえずは大丈夫なようです。
けれど、覚えておくべきことですね。ありがとうございました。

回答No.3

nora1962

トランザクション処理をする場合、
INSERT INTO ... SELECT MAX(注文NO) FROM 注文テーブル
が同時実行されない保証はありません。
そうすると、後続のINSERT文は重複キーエラーを起こしてしまいます。
保証しようすれば、注文テーブルをテーブル単位にロックをかける必要がありますから却ってパフォーマンスが劣化します。
現在のRDBMSはほとんど行レベルロック機能を持っていますからロック粒度を小さくして同時実行性を増やしたほうが全体のスループットが増すと思います。

お礼

二度目のご回答ありがとうございます。
あの後、更に調べを進めまして、どうにか実行可能なSQL文を組むことができました。

insert into 注文テーブル(顧客ID,注文NO,商品NO,処理日時)
select 注文テーブル.顧客ID,
MAX(注文テーブル.注文NO)+1,
1234,
getdate()
FROM 注文テーブル inner join IDリスト
on 注文テーブル.顧客ID = IDリスト.顧客ID
GROUP BY 注文テーブル.顧客ID

1234は仮の商品NOです。
目的の動作をすることはテスト用サーバで確認できました。
ただ情けないことに、今回いただいたお言葉である『同時実行』や『重複キーエラー』といったものについて理解が不足しております。
ですので、上記のSQL文には何か重大な欠陥があるかもしれません。
もしnora1962様からご覧になられて不適切な箇所がありましたら、ご教授いただけますでしょうか。

何度もお手間を取らせてしまい、大変心苦しいのですが、ご検討いただけましたら幸いです。

回答No.2

cotae_bb

自分も深くは理解していないため、
間違っているかもしれませんが、

INSERT INTO
　注文テーブル
(　顧客ID
　,注文回数
　,商品NO
　,処理日時
) VALUES (
　'hoge1'
　,(SELECT
　　　MAX(注文回数)
　　FROM
　　　注文テーブル
　　WHERE
　　　顧客ID = 'hoge1'
　　GROUP BY
　　　顧客ID) + 1
　,'hoge2'
,GETDATE()
);

こういった感じに書いて、hoge1とhoge2を指定すれば
一度で最大数＋１を取得してInsertができると思います。

テストはしていないので、
間違っていたら済みません。

お礼

すばやいご回答ありがとうございます。
初心者という言葉が免罪符になるとは思っていませんが、なにぶん知識・経験ともに不足しておりますので見当違いの解釈をしているかもしれません。
その際は「頭の悪いやつだなあ」と笑って流してやっていただければ幸いです。

挙げていただいたSQL文について、hoge2に関しては単一の商品NOですので直接指定できます。
問題はhoge1のほうでして、対象となる顧客IDの羅列から、一つずつ取り出してhoge1に指定し、一気に実行する方法がわからないのです……。

回答No.1

nora1962

顧客IDごとに採番レコードを持つ採番テーブルを使用するのはどうですか。

トランザクション開始
SELECT 連番値 FROM 採番テーブル WHERE 顧客ID='該当顧客ID' FOR UPDATE
ＩＮＳＥＲＴ INTO 注文テーブル VALUES ( '該当顧客ID', 連番値+1　...　）
UPDATE 採番テーブル SET 連番値　= 連番値 + 1
トランザクション COMMIT

お礼

すばやいご回答ありがとうございます。
初心者という言葉が免罪符になるとは思っていませんが、なにぶん知識・経験ともに不足しておりますので見当違いの解釈をしているかもしれません。
その際は「頭の悪いやつだなあ」と笑って流してやっていただければ幸いです。

注文テーブルの更新は、通常アプリからの操作によってなされております。
今回は大量発注ということで、1000人以上に同じ操作をアプリ上でするよりもDBに直接追加したほうが早いとクライアントから依頼をいただきました。
今後同様の依頼がある可能性を考慮すると、これまでになかった採番テーブルを新たに追加し、更に採番テーブルを常に最新の状態に保つため、注文テーブルへのINSERT命令発行と同時に採番テーブルを更新するトリガーを組む必要があると思うのですが、これは今回の『注文の追加』という依頼の範囲を若干オーバーしているように思えてなりません。
単に私が臆病なだけかもしれませんが……新入社員が上司に進言しても差し支えない範囲の処理でしょうか？