insertクエリとdaoのaddnewメソッドの違い

Question

環境 Access2019

バックエンド SQL Server2014のテーブルをリンクテーブルとして使用

vbaを使ってデータをリンクテーブルに挿入する場合、insert文を使うのとdaoのaddnewを使う場合で違いはありますでしょうか？？

実は先日insert文を使って、数十レコードずつ挿入している際にidentity設定したIDが1000飛ぶ現象が起きました。IDが飛ぶことはシステム的には問題ないのですが、IDの番号を見て件数を大まかに把握、管理している人もいるので、急に大きく飛ぶとちょっとした混乱が起きます。飛んだ原因を正確に突き止められてはないのですが、色々と調べていると大量レコードのinsert文と関連する記事を見つけました。

数十レコードが大量にあたるのかとは思いますが、出来るだけIDの大きな飛びは避けたいです。

そこでdaoを使ったaddnewなら避けられるかと思い質問しました。

それとも他の対策はありますでしょうか？？

ご教授の程お願いします。

Answer 1

いつもありがとうございます。

>Insertでもaddnewでも飛ぶときは飛ぶ。

>排他ロックすると別の弊害が出るかも。

対処方法が難しく、仕様としてしょうがないことは残念ですが、

Insertとaddnewで大きな違いがないことがはっきりと分かったのは

良かったです。

おかげさまで変なことに悩まず、機能の開発に専念できます。

ありがとうございました。

Answer 2

> バックエンド SQL Server2014のテーブルをリンクテーブルとして使用

> 先日insert文を使って、数十レコードずつ挿入している際に

> identity設定したIDが1000飛ぶ現象が起きました。

IDENTITY プロパティ使用時の動作について

引用:

> パフォーマンス上の理由から 採番される ID 値がキャッシュ上に

> 保持されているため、サーバーの再起動や、データベースの再起動が

> 発生した場合、キャッシュ上に保持された ID 値が失われ、次回

> 行挿入時に、前回挿入された値から非連続的な ID が生成される場合があります。

> また、1つのトランザクション内で連続した値が必ず採番されることも

> 保証されていない**ため、1 つのトランザクション内で連続した値を

> 必ず採番する必要がある場合は、他のトランザクションから列の挿入が

> 行われないよう、該当テーブルに対して、排他ロックを獲得するなどの

> 対策が必要です。

> パフォーマンス上の理由から 採番される ID 値がキャッシュ上に

> 保持されることを記載しましたが、例えば int 型の列に

> IDENTITY プロパティを指定した場合、キャッシュ上には 1000個の

> ID 値がキャッシュされます。

> そのため、1005 までしか採番されていない状況時に、

> サーバーの再起動などによりキャッシュがクリアされると、

> 次回の採番される値が 2001 からとなり、ID 値の連続性が損なわれます。

端的に言えば SQL Sever 2012 以降のバージョンにおける仕様によるものかと。

> 数十レコードが大量にあたるのかとは思いますが、

> 出来るだけIDの大きな飛びは避けたいです。

> そこでdaoを使ったaddnewなら避けられるかと思い質問しました。

INSERT 文だろうが AddNew メソッドだろうが、飛ぶ時は飛びます。

（ AddNew されたレコードの Update に失敗した場合、

トランザクション中にロールバックが発生した場合、

複数のユーザーがほぼ同時に連続実行した AddNew が前後した場合など）

「1 つのトランザクション処理中において」連番に抜けがないように

したいのであれば、テーブルレベルで排他ロックを掛けた上で

レコードの挿入を行なうようにするぐらいしかないでしょう。

（ただし「テーブルレベルで排他ロック」を行なうことによって

別の弊害が出る恐れもあります）