SE の雑記

SQL Server の情報をメインに Microsoft 製品の勉強内容を日々投稿

SQL Server / SQL Database の IDENTITY プロパティの連番の保証性について

leave a comment

twitter で質問をいただいたので少しまとめてみたいと思います。

SQL Server  / SQL Database では、数値型の連番を簡単に設定するためのプロパティとして IDENTITY があります。
これを設定しておくと自動的に連番が付与されますが、サーバーの再起動等が発生した場合には連番は保証されません。

これについては
IDENTITY (プロパティ) (Transact-SQL)
IDENTITY プロパティ使用時の動作について
に記載されています。

SQL Server 2012 以降は、IDENTITY の説明として以下が追加されています。

サーバーの再起動または他のエラーが発生した後の連続した値: SQL Server では、パフォーマンス上の理由から ID 値をキャッシュすることがあります。割り当てられた値の一部は、データベースの障害やサーバーの再起動が発生したときに失われることがあります。  その結果、挿入時に非連続的な ID 値が生成される場合があります。 非連続的な値が許可されない場合、アプリケーションでは、シーケンス ジェネレーターを NOCACHE オプションを指定して使用するか独自のメカニズムを使用して、キー値を生成する必要があります。

この動作について少し見ていきたいと思います。

現状、SQL Database のバージョンは 11.x になっていますので、SQL Server 2012 相当のデータベースエンジンが使用されています。
そのため、IDENTITY の動作については SQL Server だけでなく SQL Database でも同等の動作となります。

テストとして以下のようなクエリを実行してみます。

IF (SELECT OBJECT_ID('IdentityTest')) IS NOT NULL
BEGIN
	DROP TABLE IdentityTest
END

CREATE TABLE dbo.IdentityTest
	(
	Col1 int NOT NULL IDENTITY (1, 1),
	Col2 uniqueidentifier NULL
	)  ON [PRIMARY]
GO

SET NOCOUNT ON
GO
INSERT INTO IdentityTest (Col2) VALUES(NEWID())
GO
INSERT INTO IdentityTest (Col2) VALUES(NEWID())
GO
SELECT * FROM IdentityTest

2 件のデータが入った状態となりますので結果は以下のようになります。

image

この状態で、SQL Server のプロセスを強制終了して、サービスを再度起動し、新規にデータの挿入を実施してみます。

すると以下のような結果になります。

image

SQL Server 2012 以降は IDENTITY の連番はキャッシュがされており、予期せぬ終了によりキャッシュが廃棄されてしまった場合には、番号の連続性が崩れることがあります。

# サービスの正常終了では連続性が保たれることがあるようですが。

この動作ですが、SQL Server 2012 で追加されたシーケンスオブジェクト相当となっているようです。

CREATE SEQUENCE (Transact-SQL)

シーケンスオブジェクトでも番号をキャッシュをすることで、パフォーマンスを向上させていています。

この動作が SQL Server 2012 の IDENTITY でも使用されており、キャッシュがクリアされた場合は連続性がなくなるようです。

冒頭で紹介した情報には、

非連続的な値が許可されない場合、アプリケーションでは、シーケンス ジェネレーターを NOCACHE オプションを指定して使用するか独自のメカニズムを使用して、キー値を生成する必要があります。

とあり、非連続的な値が許可されない場合には IDENTITY ではなくキャッシュをしないシーケンスオブジェクトで実装する必要があるようです。

先ほどのクエリをキャッシュを使用しないシーケンスオブジェクトで連番を設定するようにした場合には以下のようになります。

IF (SELECT OBJECT_ID('[SeqIdentityTest]')) IS NOT NULL
BEGIN
	DROP SEQUENCE [SeqIdentityTest]
END

CREATE SEQUENCE [dbo].[SeqIdentityTest] 
 AS [int]
 START WITH 1
 INCREMENT BY 1
 NO CACHE 
GO

IF (SELECT OBJECT_ID('IdentityTest')) IS NOT NULL
BEGIN
	DROP TABLE IdentityTest
END

CREATE TABLE dbo.IdentityTest
	(
	Col1 int NOT NULL,
	Col2 uniqueidentifier NULL
	)  ON [PRIMARY]
GO


SET NOCOUNT ON
GO
INSERT INTO IdentityTest VALUES(NEXT VALUE FOR SeqIdentityTest, NEWID())
GO
INSERT INTO IdentityTest VALUES(NEXT VALUE FOR SeqIdentityTest, NEWID())
GO
SELECT * FROM IdentityTest

 

この場合は、連番の設定時にキャッシュを用いないため、サービスが予期せぬ終了をした場合でも連番となっています。

image

ただし、このような設定をした場合の弊害としては、NEXT VALUE をするたびにシーケンスの変更の記録が発生するということです。

シーケンスの変更を永続化する場合、sys.sysobjvalues のシーケンスオブジェクトの行データに対して変更 (LOP_MODIFY_ROW) が行われます。

以下が NOCACHE を使用している場合に、10 行を書き込んだ際のログの内容になります。

SELECT Operation,Context,AllocUnitName,[Page ID],[Slot ID] 
FROM sys.fn_dblog(NULL, NULL) 
WHERE  AllocUnitId = '281474980642816'


image

こちらが、NOCACHE を使用していない場合のログ書き込みとなります。

image

キャッシュをしない場合、シーケンスの永続化を行うため、逐次変更を行っているのが確認できます。

キャッシュをしない場合はこのあたりのオーバーヘッドを考慮する必要が出てきます。

SQL Database でも同様のことを実施したいのですが、11.x の SQL Database のシーケンスオブジェクトを使用することができないため、現状は IDENTITY のキャッシュ破棄による連番の制御をすることはできないようです。

V12 Preview を使用した場合には、シーケンスオブジェクトを作成することができます。

そのため、V12 であれば、以下のクエリを実行することが可能です。

IF (SELECT OBJECT_ID('[SeqIdentityTest]')) IS NOT NULL
BEGIN
	DROP SEQUENCE [SeqIdentityTest]
END

CREATE SEQUENCE [dbo].[SeqIdentityTest] 
 AS [int]
 START WITH 1
 INCREMENT BY 1
 NO CACHE 
GO

IF (SELECT OBJECT_ID('IdentityTest')) IS NOT NULL
BEGIN
	DROP TABLE IdentityTest
END

CREATE TABLE dbo.IdentityTest
	(
	Col1 int NOT NULL,
	Col2 uniqueidentifier NULL
	)  ON [PRIMARY]
GO

CHECKPOINT


SET NOCOUNT ON
GO
INSERT INTO IdentityTest VALUES(NEXT VALUE FOR SeqIdentityTest, NEWID())
GO 10

SELECT * FROM IdentityTest 

 

SQL Server 2012 以降の IDENTITY は ユニークになる連番 (飛び番になる可能性もある) を自動的に設定するためのプロパティ設定 という認識でいたほうがよさそうですね。

なお、IDENTITY を使用している場合には、連番の情報は sys.sysobjvalues ではなく、sys.syscolpars で設定がされているようです。

このテーブルの内容は、以下のようになっています。

image

IDENTITY を使用している列では、idtval という項目に次にキャッシュすればよい IDENTITY の範囲を保持しているようです。

Written by masayuki.ozawa

12月 21st, 2014 at 6:22 pm

Leave a Reply

*