InnoDB の AUTO_INCREMENT とロック
きっかけは 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 ではこの問題が解決されたようだ。
- Bugs: #16979: AUTO_INC lock in InnoDB works a table level lock
- How AUTO_INCREMENT Handling Works in InnoDB
リファレンスマニュアルの How AUTO_INCREMENT Handling Works in InnoDB に詳しいが、5.1.22 からは AUTO_INCREMENT カウンタのロック方法として、よりスケーラビリティの高い方式が導入された。
また、innodb_autoinc_lock_mode パラメータを設定することで、新しい方式を利用するかどうかを設定可能で、デフォルトでは単純な INSERT でのみ、新しい方式を利用するようになっている。
ただし、新しいロック方式ではいくつかの点で注意が必要だ。
- ステートメントベースのレプリケーションでは問題が起こる可能性がある
- 連番になるとは限らない（さまざまな理由で値にはギャップが生じる）
これらは innodb_autoinc_lock_mode の値によっても異なってくるので、リファレンスマニュアルの How AUTO_INCREMENT Handling Works in InnoDB で詳細を確認した方がよいだろう（AUTO_INCREMENT の実装について詳しく書かれているため、機会があれば記事にしたい）。