はじめに
SQL ServerでIDENTITYを設定したテーブルをロールバックした時に連番が戻らなかったので調べてみました。
Microsoftのサイトによると
失敗したステートメントとトランザクションによって、テーブルに対する現在の ID が変更され、ID 列値に差異が生じる可能性があります。 ID 値がロールバックされることはありません。これは、テーブルに値を挿入するトランザクションがコミットされない場合でも同じです。 たとえば、INSERT ステートメントが IGNORE_DUP_KEY 違反のために失敗しても、テーブルの現在の ID 値は増分されます。
「ロールバックされない」と書かれているので、そういうものなのかと思いましたが、考えてみると当たり前のことでした。
以下のようなテーブルがあり、AさんとBさんがデータを登録します。
CREATE TABLE [m_vegetable] (
[id] INT IDENTITY(1,1),
[name] NVARCHAR(20)
)
| id | name |
|---|---|
| 1 | 大根 |
| 2 | 人参 |
１．Aさんがトランザクションを開始する
２．Aさんがデータを登録する
| id | name |
|---|---|
| 3 | ごぼう |
３．Bさんがデータを登録する
| id | name |
|---|---|
| ? | ジャガイモ |
Aさんのトランザクションはコミットされていません。
この時、Bさんの登録したデータのidは何番が採番されるでしょうか。
「3」が入った場合、Aさんのトランザクションをコミットすると「3」が重複してしまいます。
つまり、idには「4」が入ります。
| id | name |
|---|---|
| 1 | 大根 |
| 2 | 人参 |
| 4 | ジャガイモ |
ここで、AさんがロールバックしてもBさんが登録したデータのidは「4」のままです。
ですので、IDENTITYをロールバックするわけにはいかなくなります。
連番に関するSQL
--自動採番を一時解除
SET IDENTITY_INSERT [m_vegetable] ON
--idを指定して登録
INSERT INTO [m_vegetable] ([id], [name]) VALUES (3, 'ごぼう')
--自動採番に戻す
SET IDENTITY_INSERT [m_vegetable] OFF
| id | name |
|---|---|
| 1 | 大根 |
| 2 | 人参 |
| 3 | ごぼう |
| 4 | ジャガイモ |
-- 最大値をチェック
DBCC CHECKIDENT ([m_vegetable], NORESEED)
--⇒ ID 情報を調べています。現在の ID 値 '4'、現在の列値 '4'。
-- 最大値をセット
DBCC CHECKIDENT ([m_vegetable], RESEED, 100)
--⇒ ID 情報を調べています。現在の ID 値 '100'。
DBCC CHECKIDENT ([m_vegetable], NORESEED)
--⇒ ID 情報を調べています。現在の ID 値 '100'、現在の列値 '4'。
INSERT INTO [m_vegetable] ([name]) VALUES ('白菜')
| id | name |
|---|---|
| 1 | 大根 |
| 2 | 人参 |
| 3 | ごぼう |
| 4 | ジャガイモ |
| 101 | 白菜 |