InnoDB の AUTO_INCREMENT とロック

2007/10/09 12:29pm

きっかけは MySQL Users Conference Japan 2007 の講演資料「新ストレージエンジン Falcon のアーキテクチャ詳細技術解説」だった。

Falcon とは MySQL 6.0 で搭載予定の新しいストレージエンジンである。

この講演資料で Falcon が InnoDB より優れている点として、

AUTO_INCREMENT の割当にテーブルロックをかけない

があげられており、少々驚いた。

これはつまり、InnoDB の AUTO_INCREMENT がテーブルロックをかける、ということであり、そのことをいままで知らなかったからだ。

恥ずかしながら、AUTO_INCREMENT のスケーラビリティについては、いままであまり意識したことがなかった。

InnoDB の特別なテーブルロックモード AUTO-INC

リファレンスマニュアルの「13.5.16. InnoDB テーブル上の制約」にもあるように、AUTO_INCREMENT を使うときはテーブルロックが必要だ。

自動インクリメント カウンタにアクセスする時、InnoDB は、トランザクション全体の最後までではなく、現在の SQL ステートメントの最後まで続く、特別なテーブル ロック モード AUTO-INC を利用します。AUTO-INC テーブル ロックが行われている間は、別のクライアントはテーブルに挿入ができない事に注意してください。

AUTO_INCREMENT が設定された InnoDB のテーブルでは、AUTO-INC テーブルロックを同時にひとつのトランザクションしか利用できない。これはスケーラビリティの点で問題になりうる。

MySQL 5.1.22 では解決済み

しかし、嬉しいことに InnoDB auto-inc scalability fixed によると、最新版の MySQL 5.1.22 ではこの問題が解決されたようだ。

リファレンスマニュアルの How AUTO_INCREMENT Handling Works in InnoDB に詳しいが、5.1.22 からは AUTO_INCREMENT カウンタのロック方法として、よりスケーラビリティの高い方式が導入された。

また、innodb_autoinc_lock_mode パラメータを設定することで、新しい方式を利用するかどうかを設定可能で、デフォルトでは単純な INSERT でのみ、新しい方式を利用するようになっている。

ただし、新しいロック方式ではいくつかの点で注意が必要だ。

  1. ステートメントベースのレプリケーションでは問題が起こる可能性がある
  2. 連番になるとは限らない(さまざまな理由で値にはギャップが生じる)

これらは innodb_autoinc_lock_mode の値によっても異なってくるので、リファレンスマニュアルの How AUTO_INCREMENT Handling Works in InnoDB で詳細を確認した方がよいだろう(AUTO_INCREMENT の実装について詳しく書かれているため、機会があれば記事にしたい)。