いくつかのテーブルで bigint ID 列を使用していますが、低い数値から高い数値へとジャンプし続けていることに気付きました。この場合、17 から 1004 ですが、他のテーブルでは 10000 までジャンプしています。この動作が続くと、数千のレコード内で bigint の範囲に達してしまいます。なぜこのようなことが起こるのでしょうか。また、どうすれば修正できるのでしょうか。
スクリーンショットはこちら:
答え1
Bigint の範囲は -9,223,372,036,854,775,808 から 9,223,372,036,854,775,807 です。上限に達するまでにはしばらく時間がかかると思います。一度に 10 万件のレコードをジャンプアップしても、10 億回ジャンプしても上限に達しない可能性があります。それでも心配な場合は、ID を負の範囲から開始するように再シードしてください。そうすれば、心配する前に私たち全員が死んでしまうでしょう。
アイデンティティ値にギャップがあるのはなぜですか?
アプリケーションがテーブルにレコードを挿入しようとすると、プールから番号が削除されます。参照整合性の理由により、挿入は失敗するか、呼び出し元のプログラムがトランザクション挿入を元に戻す (ロールバックする) か、いずれにしても、ID 値は候補プールに存在しなくなります。はい、トランザクションがロールバックされても、番号は使い果たされているため、ロールバックされません。
自分でテストする
SET NOCOUNT ON;
IF OBJECT_ID('tempdb..#Demo') IS NOT NULL
BEGIN
DROP TABLE #Demo;
END
CREATE TABLE #Demo
(
demoid int IDENTITY(1,1) NOT NULL
, CantBeNull varchar(50) NOT NULL
);
-- add a record and look at our identity
INSERT INTO #Demo
(CantBeNull)
VALUES
('First Entry');
SELECT SCOPE_IDENTITY() AS GeneratedId;
-- GO forces the batch to commit (assuming default implicit transaction model)
GO
-- Violate RI
INSERT INTO #Demo
(CantBeNull)
VALUES
(NULL);
SELECT SCOPE_IDENTITY() AS RIViolationGeneratedId;
GO
-- add a record and look at our identity
INSERT INTO #Demo
(CantBeNull)
VALUES
('Third Entry');
SELECT SCOPE_IDENTITY() AS ThirdGeneratedId;
GO
-- Transaction consumes
BEGIN TRAN
INSERT INTO #Demo
(CantBeNull)
VALUES
('Fourth Entry');
SELECT SCOPE_IDENTITY() AS FourthGeneratedId;
ROLLBACK;
INSERT INTO #Demo
(CantBeNull)
VALUES
('Fifth Entry');
SELECT SCOPE_IDENTITY() AS FifthGeneratedId;
GO
SELECT D.* FROM #Demo AS D;
結果タブには、使用されている数字である1、1、3、4、5が表示されますが、4をロールバックしたので、#Demoテーブルのコミットされた結果は次のようになります。
demoid CantBeNull
1 First Entry
3 Third Entry
5 Fifth Entry
この行動は好きではない
これがアイデンティティ プロパティの仕組みです。これを変更する方法はありません。Sequence オブジェクト (2012 の新機能) に切り替えた場合も同様です。
ギャップが懸念事項である場合は、ギャップを消費しているプロセスを特定します。参照整合性 (RI) 違反の場合は、保存を試みる前に検証を強化します。根本原因がロールバックされているトランザクションである場合は、2 フェーズ コミットなどにして、Stage.MyTable に保存します。ID 枯渇が問題である場合は、SEQUENCE オブジェクトに切り替えて、循環をオンにします。定期的にこのテーブルをポーリングしてコミットされたトランザクションを探し、そこから dbo.MyTable に挿入します。これにより、代理キーにギャップが存在する可能性が減ります。
ケンドラ・リトル素敵な分離レベル ポスターは、本当にコミットされているものをチェックするために必要な分離レベルを思い出すのに役立ちます。
