背景
(1)オンプレミスのMySQLサーバをAmazon Auroraに移行したい
- このとき、全てのテーブルをInnoDBエンジンに移行する必要がある
- MyISAMエンジンを利用できないため（Amazon Auroraの制約）
(2)一方、MyISAMエンジンを使ってユニークなidを発行するテーブル（採番テーブル）が存在する
- MySQLのデザインパターンの一つ。採番テーブル・シーケンステーブルと呼ばれる。詳細は https://dev.mysql.com/doc/refman/8.0/ja/information-functions.html#function_last-insert-id の「シーケンスカウンタ」や https://qiita.com/Nyokki/items/c68dddff2368c557c4bf の「方法3」を参照
- これをそのままInnoDBに移行すると、問題が発生する場合がある
この記事では、(1)(2)を踏まえて、採番テーブルをInnoDBに移行する際の問題と対策について説明する。
採番テーブルをInnoDBに移行する際の問題点
- 問題が発生する必要条件は、採番処理がトランザクションの中で実行されている場合である。トランザクションの外で実行されている場合は、問題は発生しない。
- また、本記事は、トランザクション分離レベルが
REPEATABLE READの場合を前提としている。それ以外の場合は、挙動が大きく変わってくるのでスコープ外となる。
問題1. トランザクションがロールバックすると採番テーブルの値が戻ってしまう
- 採番テーブルをInnoDBにすると、トランザクションが有効になる。すると、トランザクションがロールバックされた時に、採番テーブルの値もロールバックされてしまう。それにより、次のトランザクションで同じidが発行されてしまう。
- それが問題になるかは要件次第である。ロールバックされたトランザクションに対しても一意なidを発行してトレースしたい場合などは、この挙動は問題になる。
問題2. 採番処理後、トランザクションが開放されるまで採番テーブルにロックが掛かってしまう
- InnoDBのトランザクションの仕様で、
UPDATE文は行ロックを掛ける。 - 採番処理をするとidを格納している行に
UPDATEが実行されるので、ロックが掛かり、他のトランザクションは採番ができずに、待ち状態となってしまう。 - ロックはトランザクションがコミットされるまで開放されないので、トランザクション中に時間の掛かる処理が含まれていると、パフォーマンスの悪影響が発生する可能性がある。
採番テーブルをInnoDBに移行する方法
以下の2通りの対策があり、どちらかを採用することで問題を解消できる。
対策A. 採番処理をトランザクションの外で実行するようにする
今回の問題は採番処理をトランザクションの中で行うことにあるので、トランザクション外で実行するようにアプリケーションを改修することで解決できる。
対策B. 採番テーブルをリプレイスする
以下のようなテーブルを作成し、採番ルールも変更して移行する。
-
AUTO_INCREMENTで採番する。AUTO_INCREMENTはロールバックされない性質を利用する。 - 下記のSQLであれば、ロックされることもない。
- よって、トランザクション中で実行しても問題がない。
DDL
CREATE TABLE sequence (
id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY
) engine = InnoDB;
DML
INSERT INTO sequence VALUES();
SELECT LAST_INSERT_ID();
このとき、シーケンステーブルにはデータが溜まっていく点に注意。トランザクション中で古いデータを削除する方法もある(ref1, ref2)が、DELETE 文を実行すると行ロックが掛かってしまい、「問題2」の問題が発生してしまう。
対策AとBの比較
対策Aの採用理由
- アプリケーションの改修だけで完結するので、作業負担やインパクトが小さい
対策Bの採用理由
- 対策Aの採用が難しい場合。
- 採番処理がトランザクション内の前の処理に依存していたり、トランザクションの例外処理の都合など。
- 対策Aは、アプリケーションレベルの対策である。開発者はアプリケーション追加改修時、採番処理をトランザクションの中にうっかり記述しないように気をつけ続けなければならない。一方対策Bは、テーブルレベルでの対策となるため、アプリケーション開発者はテーブルの利用方法を気にする必要がなくなり、より安全であるとの見方もできる。