Railsのid列は必ずしもきれいな連番にはならないんですよ、という話

はじめに

Railsでモデルを作ると、テーブル定義上のid列（プライマリーキー）はデフォルトで自動的に連番が付与されるカラム（auto increment値）になります。

とはいえ、idにしたからといって必ずしも連番になるとは限りません。場合によっては「歯抜け（ギャップ）」が発生こともあります。
そのため、「idの最大値＝そのテーブルの件数」になるとは限りません。

つまり、こういうデータが作成されることもありうる、ということです（たとえ一度もDELETEされなかったとしても）。

id	name
1	Alice
2	Bob
5	Carol

そこでこの記事ではRDBMS別にidの連番が歯抜けになるケースを見ていきます。

この記事で使用するサンプルアプリ

この記事で使用するのは以下のようなモデルを1つだけ持つ、単純なRailsアプリです。

class Item < ApplicationRecord
end

db/schema.rb

ActiveRecord::Schema[7.0].define(version: 2022_02_09_225353) do
  create_table "items", force: :cascade do |t|
    t.datetime "created_at", null: false
    t.datetime "updated_at", null: false
  end
end

コードはGitHubに置いています。

PostgreSQLの場合

以下のようなテストを書いた場合、テストが落ちます。

require "test_helper"

class ItemTest < ActiveSupport::TestCase
  test "id count" do
    Item.transaction do
      Item.create!
      raise ActiveRecord::Rollback
    end
    Item.create!
    assert_equal 1, Item.count
    assert_equal Item.count, Item.last.id
  end
end

# Running:

F

Failure:
ItemTest#test_id_count [/Users/jnito/dev/sandbox/id-count-sandbox/test/models/item_test.rb:11]:
Expected: 1
  Actual: 2

rails test test/models/item_test.rb:4

Finished in 0.022535s, 44.3754 runs/s, 88.7508 assertions/s.
1 runs, 2 assertions, 1 failures, 0 errors, 0 skips

何が起きているのか？

• Item.create!でDBへのINSERTが走ります。このとき、idは1になります。
• raise ActiveRecord::Rollbackで更新がロールバックされるため、上のINSERT処理はキャンセルされます。
  • ここでは明示的にraise ActiveRecord::Rollbackしていますが、実際のRailsアプリケーションでは他のレコードの更新に失敗したものと考えてください。
• その次のItem.create!でDBへのINSERTが走ります。このとき、idは2になります。
• その結果、idの最大値が2で、itemsテーブルの件数が1となるため、「idの最大値＝そのテーブルの件数」が成り立たずassert_equal Item.count, Item.last.idが失敗します。

マニュアルではどう説明されているか？

PostgreSQLの場合、id列はbigserial型になります。この型ではギャップが発生するケースがあることがマニュアルに明記されています。

smallserial、 serialおよびbigserialはシーケンスを使って実装されているため、行の削除が行われていなくとも、列に"穴"や連番の抜けが発生するかもしれません。また、テーブルへ正常に挿入されていないにも関わらず、シーケンスの値を"消費してしまう"こともあります。

8.1. 数値データ型

上でテストした内容は、ここで言う「テーブルへ正常に挿入されていないにも関わらず、シーケンスの値を"消費してしまう"」ケースに該当します。

MySQLの場合

MySQLもPostgreSQLと同じ挙動になり、前述のテストが失敗します。

マニュアルではどう説明されているか？

MySQLの場合、id列はauto_incrementが指定されたbigint型になります。こちらもやはりギャップが発生する可能性がマニュアルに明記されています。

「失われた」自動インクリメント値とシーケンスギャップ
すべてのロックモード (0、1、および 2) では、自動インクリメント値を生成したトランザクションがロールバックされると、これらの自動インクリメント値が「失われます」。 「INSERT のような」ステートメントが完了したかどうか、およびそれを含むトランザクションがロールバックされたかどうかに関係なく、自動インクリメントカラムの値は一度生成されたら、ロールバックできません。 このような失われた値は再使用されません。 したがって、テーブルの AUTO_INCREMENT カラムに格納されている値にはギャップが存在する可能性があります。

MySQL :: MySQL 8.0 リファレンスマニュアル :: 15.6.1.6 InnoDB での AUTO_INCREMENT 処理

SQLite3の場合

SQLite3の場合はPostgreSQLやMySQLとは異なり、先ほどのテストはパスします。

# Running:

.

Finished in 0.012709s, 78.6844 runs/s, 157.3688 assertions/s.
1 runs, 2 assertions, 0 failures, 0 errors, 0 skips

SQLite3の場合、id列はautoincrementが指定されたinteger型になります。SQLite3ではsqlite_sequenceというテーブルで連番を制御しているので、トランザクションがロールバックされるとsqlite_sequenceテーブルで管理している連番もロールバックされるんだと思います（想像）。

マニュアルではどう説明されているか？

ただし、SQLite3のマニュアルにはギャップが発生するケースがあることも書かれています。

Note that "monotonically increasing" does not imply that the ROWID always increases by exactly one. One is the usual increment. However, if an insert fails due to (for example) a uniqueness constraint, the ROWID of the failed insertion attempt might not be reused on subsequent inserts, resulting in gaps in the ROWID sequence. AUTOINCREMENT guarantees that automatically chosen ROWIDs will be increasing but not that they will be sequential.

SQLite Autoincrement

DeepL翻訳の結果も併記しておきます（一部手修正）。

なお、「単調増加」は、ROWIDが常に正確に1ずつ増加することを意味するものではありません。1は通常の増加分です。しかし、(例えば)一意性制約のために挿入に失敗した場合、失敗した挿入のROWIDはその後の挿入で再利用されない可能性があり、結果としてROWIDシーケンスにギャップが発生します。AUTOINCREMENTは、自動的に選択されたROWIDが増加することは保証しますが、連続的であることは保証しません。

SQLite3でもこんなコードを書くとギャップが発生する

では試しに、SQLite3でギャップが発生するコードを書いてみましょう。

たとえば先ほどのItemテーブルにnameカラムを追加し、ユニーク制約を付けます。

db/schema.rb

 ActiveRecord::Schema[7.0].define(version: 2022_02_09_235257) do
   create_table "items", force: :cascade do |t|
     t.datetime "created_at", null: false
     t.datetime "updated_at", null: false
+    t.string "name"
+    t.index ["name"], name: "index_items_on_name", unique: true
   end
 end

さらにテストコードを次のように変更します。

require "test_helper"

class ItemTest < ActiveSupport::TestCase
  test "id count" do
    Item.create!(name: 'foo')
    ApplicationRecord.connection.exec_query(<<~SQL)
      INSERT OR IGNORE INTO items
      (name, created_at, updated_at)
      VALUES
      ('foo', 0, 0)
    SQL
    Item.create!(name: 'bar')
    assert_equal 2, Item.count
    assert_equal Item.count, Item.last.id
  end
end

こうするとテストが失敗します。

# Running:

F

Failure:
ItemTest#test_id_count [/Users/jnito/dev/sandbox/id-count-sandbox/test/models/item_test.rb:14]:
Expected: 2
  Actual: 3

rails test test/models/item_test.rb:4

Finished in 0.008400s, 119.0476 runs/s, 238.0952 assertions/s.
1 runs, 2 assertions, 1 failures, 0 errors, 0 skips

このテストが失敗する理由

SQLite3でINSERT OR IGNORE INTOを使ってINSERTすると、ユニーク制約違反が発生してもINSERTには失敗するものの、エラーにはなりません。

一方、idはINSERTされなくても1つインクリメントされたままになります。

そのため、最終的にidの最大値が3で、itemsテーブルの件数が2となり、「idの最大値＝そのテーブルの件数」が成り立たず、assert_equal Item.count, Item.last.idが失敗します。

RailsアプリでわざわざINSERT OR IGNORE INTOを使うのはレアケースだと思いますが、必ずしも「idの最大値＝そのテーブルの件数」にはならないことを示す例としてあえて使ってみました。

まとめ

というわけで、この記事ではRailsのid列がきれいに連番にならずギャップが発生する事例を示してみました。

RDBMSは並列で大量のアクセスをさばく必要があるため、パフォーマンスを重視してauto increment列でもギャップの発生を許容しています。「なんでidが連番にならないの！？」と思ったときは、この記事を読み直してみてください。