IDENTITYの重複のバグ？？

Question

OS : WINDOWS-XP PRO
SQL SERVER : Microsoft SQL Server 2005 Developer Edition
言語 : VS2005のVB.NET 2005
プロジェクト　: Windows Form


IDENTITYが指定されているテーブルで例えば　商品テーブルなどで
次のような場合、

========================================================
テーブルの定義：
    商品コード                int    (主キー列)   IDENTITYの指定
    分類コード    NVARACHAR(5)
    商品名           ＮVARCHAR(50)
    単価                INT
    備考               ＮVARCHAR(50)

　＜データ内容＞

  商品コード 　分類コード                     商品名   　　　単価 　　　　　備考
   01             　00001 (りんご)　　　  スターりんご  　 100　　　　　
　↓　　　　　　00001 (りんご)           ふじりんご         200
  20　              00001 (りんご)          びっくりりんご     250
   21                00002 (メロン)           みやまメロン     400
   ↓                00002 (メロン)
   32                00003(スイカ)
   ↓                 00003(スイカ)
   50                00003(スイカ) 

＜商品テーブルADAPTER＞
  クエリ:
     SELECT  ＊ FROM 商品テーブル  WHERE 分類コード = @分類CD
   
  ========================================================

上記のうち、例えば　分類コード:00001:りんごのデータのグループについて  
追加、訂正を行う場合、どうなのるのであろうか、
まず。TABLE アダプターのクエリで　分類コード:00001:りんごのデータのみ
データセットに読み込み画面上で新規追加を行うとすれば
IDENTITY は51　となるはずです。 
ところが　実際は 21となってしまいます。
どうやら　VB.NET 2005 では画面上に表示されているデータのうち、最後尾の
ＭＡＸ値を求めて　1を加算しているようなのです。
テスト中、テストデータを用意するためにデータを入れ替えて使っているうちに
このような現象がでました。
一旦、テーブルを削除し、新たに　Create しなおしたところ現象がでなくｊなりました。
みなさんはこのような現象はありませんでしょうか？

念のため、重複エラーの発生時、@@Ｉdentity もしくはＭＡＸ　で最後尾の値を求める
ようにして回避するようにしました。

Answer 1

IDENTITY は SQL Server 上で生成されるので、この動きは正当だと思います。
通常 IDENTITY を設定している項目をクライアント側で値を指定して追加をしません。
もしやるのでしたら、一時的に IDENTITY の動作を無効化してから追加します。
※ただし、これをやるには整合性のチェックをかけて、排他的にやらなければなりません。

SQL Server 2005 の場合は、追加したときに生成される IDENTITY の値を、OUTPUT INSERTED.[商品コード] で取得できますよ。

Answer 2

お世話になります。

>>IDENTITY は SQL Server 上で生成されるので、この動きは正当だと思います。

なぜ正当なのでしょうか？　SQL Server  Manegement Studio で操作した場合は　５１になります。
また、このような状況では必ず、重複することを前提にしてプログラムを作成しなければなりません。
Identity は使わない方がよいのでしょうか？　それとも仕様ですか？
納得できません。

Answer 3

> SQL Server  Manegement Studio で操作した場合は　５１になります。

手入力で操作できますか？
IDENTITYの設定をしていたら、そこは入力できないようになっているはずです。
設定がミスってたりしませんか。

Answer 4

>>IDENTITY は SQL Server 上で生成されるので、この動きは正当だと思います。

DataSet内で一意になるように採番しているので、正常だと思います。

先ほどの例で、AさんとBさんがDataSetを取得し、
それぞれの画面で新しいデータを追加したら
Aさんは51、Bさんは52が採番されれば納得されるのでしょうか？

実際は、Aさんに21、Bさんにも21が採番され、
それぞれのDataSetの中だけで一意になるはずです。
その後で、Aさん、BさんがテーブルにInsertするタイミングで、
初めて正しい値（他に人がいなければ51と52）が採番されて、
テーブル内で一意となるはずです。

Answer 5

IDENTITY を指定したものをキーとした場合、クライアント側で採番しては絶対にいけません。
SQL Server Management Studio でデータを追加したら 51 になるというのは正常です。
これは SQL Server 上で現在の値が 50 なので、次のデータは 51 だということで値が決定されるのです。
それをクライアント側のDataSet/DataTable などで、MAX + 1 としたものを IDENTITY の指定されたキーにデータをいれようとしても、この MAX + 1 してもの「そのクライアントだけの MAX + 1」だということで、競合しない保証は絶対にありません。
そもそも、IDENTITY 指定してものにクライアント側で明示的に値を指定していれること自体は、SQL Server 上のテーブルを排他制御化におき、他のクライアントが絶対に更新できない状況にした上で、やらないと競合は必ず起こりえます。
結論としては、これは仕様であり、IDENTITY を指定したものはクライアント側で採番せず、OUTPUT INSERTED.[ID] などでINSERT した後の確定した値を取得することであります。

Answer 6

お世話になります。

実際にVB.NET 2005 でテストしましたところ　 YouGun　さま　のおっしゃるとおり　Dataset では一意の
値で一旦取得されますが更新時に改めて番号が採番されるのを確認できました。
どうやら私の勘違いでバグかなと思い込んでいたようです。
実際の開発プログラムでは誰かが重複してレコードを書きこみを行っていないかどうかをチェックするために
直接ＳＱＬコマンドを　「SQL_Cmd.ExecuteNonQuery()」　で発行して結果を得ていましたが
（例では商品コードの２１番が使われていないかどうかをチェック）もし、使われていた場合は重複エラーとしてメッセージ
を表示するようにしていました。　これが悪かったようですね！！
なにもチェックせずとも勝手にＳＱＬ SERVER 側で番号を取得するので画面上に表示している番号は無視して
いいとは気がつきませんでした。

どうもありがとうございます。　勉強になりました。

　

Answer 7

お世話になります。

クライアント側で　IDENTITY を設定することは御法度ということですね。

>>IDENTITY を指定したものはクライアント側で採番せず、OUTPUT INSERTED.[ID] などでINSERT した後の確定した値を取得することであります。

ＭＳＤＮ　Books Online などで調べましたがどのような　使い方をするのかいま少し理解できませんでした。
ＩＮＳＥＲＴ　命令に　Output 句　が指定できるのは　わかったのですが Insert 命令　に　Output 句　を指定した場合は
追加実行後のレコードがＳｅｌｅｃｔ 命令　と同じように結果セット　が返ってくると　理解しました。

今回のような場合はクライアント側では新規追加　する前にこの命令（Insert 命令　に　Output 句　を指定した場合）　を用いる
ことで解決できるのか　わかりません。　すみません。　こちらの勉強不足です。

 

Answer 8

お世話になります。

確かにVB.NET 2005 の　 DataSet デザイナでは　TAble AdapterのIdentity 列は　Readonly　に なっています。
プロパティ　を操作すれば　書き込みできます。

Answer 9

DataSet/DataTable の場合、INSERT/UPDATE/DELETE を行った場合、再度 Fill で再取得すれば同等の効果が得られます。
ただ、これを行うとデータベースに負担をかけるので、可能であれば DataAdapter/TableAdapter の Update を使って更新するのではなく、自前で処理をして値を更新したほうがよいです。
そのときに INSERTED.[ID] を OUTPUT で取得するということになります。
負荷の高いデータベース（同時接続数が多いものやミッションクリティカルなものなど）では、このように考えます。
＃答えてねっとがやばいのも考え方が問題だったのと同じです。