こんにちは。SWEの中村（@mnmandahalf）です。
最近サービス稼働中にDDLを適用するタイミングで初遭遇したMySQLのエラーについて皆様にご紹介したいと思います。
エラーの内容と発生条件（概要）
① トランザクション分離レベル transaction-isolation が REPEATABLE-READ である
② セッションAのトランザクション内でテーブルt1をメタデータロックしないテーブルt2へのSQLを実行
③ セッションBがテーブルt1に対して特定のDDLを実行
④ セッションAがテーブルt1を参照すると ERROR 1412 (HY000): Table definition has changed, please retry transaction というエラーが発生し、セッションAのトランザクションがロールバックする
※なお、テーブルt2がテーブルt1への外部キー制約を持っている場合はセッションAがt1のメタデータロックを獲得するためDDLの適用がトランザクションの終了まで待たれます。
consistent read (一貫性読み取り) とは
リファレンスマニュアルによると、以下の定義となっています。
snapshot 情報を使用して、同時に実行されている他のトランザクションによって実行された変更に関係なく、ある時点に基づいてクエリー結果を表示する読取り操作。 照会されるデータが別のトランザクションによって変更されている場合、元のデータは Undo ログの内容に基づいて再構築されます。 この方法は、ほかのトランザクションが終了するのを待機するようにトランザクションを強制することによって、並列性を減少させる可能性のあるいくつかのロック問題を回避します。
一言で述べるとある時点のスナップショットを利用した他のトランザクションの影響を受けない一貫性を保った読み取り操作のことを指すようです。
REPEATABLE READ 分離レベルでは、スナップショットは最初の読取り操作が実行された時間に基づきます。 READ COMMITTED 分離レベルでは、スナップショットは各読取り一貫性操作の時間にリセットされます。
とある通り、REPEARTABLE READの場合はスナップショットを取得するタイミングがトランザクションの最初の読み取り操作になることで上記の事象が発生します。
エラーが発生しうるDDL
実際にエラーが発生しうるDDLとそうでないものを知っておくことによって今後のDDL適用のプランニングにも役立つため、以下の表と照らし合わせながらサンプルのようにDDLを実行し当該エラーが発生するかどうか試してみました。
検証方法
まずセッションAでトランザクションを開始し、t1と外部キー関係のないテーブルt2をSELECTします
sessionA> BEGIN; SELECT * FROM t2 LIMIT1;
セッションBを開始し、t1へDDLを実行します
sessionB> /* SOME DDL TO t1 */
再びセッションAでt1をSELECTします
sessionA> SELECT * FROM t1 LIMIT1;
ERROR 1412 (HY000): Table definition has changed, please retry transaction
こちらの操作をMySQL 8.0.31とMySQL 8.0.30でそれぞれ確認します。
※ 本記事でMySQL 8.0.31の場合とMySQL 8.0.30の場合を確認している理由は、手元のMySQLのバージョンで確認した挙動と以下の記事（MySQL 8.0.31利用）で示唆されている挙動（インスタントDDLでも再現する）に差分があることを偶然にも発見し、このバージョンを境目に確認を行ったところ、ちょうど差分がみられたためです。
（こちらの記事はエラー発生時の調査にて大変お世話になりました）
MySQL 8.0.31の場合
カラム操作
NG
- カラムの追加 (
ALGORITHM=INSTANT/INPLACE/COPY問わず) - カラムの削除
- カラムのNULL/NOT NULL化
- カラムのデータ型変更
- カラムのENUM定義の項目削除
- VARCHARの長さ短縮
OK
- カラム名変更
- カラムのENUM定義の追加
- カラムデフォルト値変更
- VARCHARの長さ拡張
外部キー操作
OK
- 外部キー制約の削除
- 外部キー制約の追加 (
foreign_key_checksが無効な場合)
NG
- 外部キー制約の追加 (
foreign_key_checksが有効な場合)
インデックス操作
OK
- セカンダリインデックスの追加/削除
テーブル操作
OK
- テーブル名変更
- 文字セットの指定
- 文字セットのconvert
- 当該テーブルへの外部キー含む新テーブル追加
トリガー操作
OK
- トリガーの追加/削除
※ DDLではないですが、TRUNCATE TABLEでも同様のエラーが発生します。
MySQL 8.0.30の場合
MySQL 8.0.31ではエラーが発生していたDDLのうち、以下はMySQL 8.0.30でエラーが発生しませんでした。
- カラムの追加（
ALGORITHM=INSTANTで実行した場合） - カラムの削除（
ALGORITHM=INSTANTで実行した場合）
結果に対する考察
MySQL 8.0.31で確認したDDLのうち、以下の操作でエラーが発生しうる（consistent readが実現する一貫性を損なうとみなされる）という結果が得られました。
- カラムの追加 (
ALGORITHM=INSTANT/INPLACE/COPY問わず) - カラムの削除
- カラムのNULL/NOT NULL化
- カラムのデータ型変更
- カラムのENUM定義の項目削除
- VARCHARの長さ短縮
- 外部キー制約の追加 (
foreign_key_checksが有効な場合)
オンラインDDLの表[1]と照らし合わせると、カラム操作に関しては「メタデータの変更のみ」が「いいえ」になるDDLがおおむねNGの対象になりそうですが、「ENUM または SET カラムの定義の変更」については変更内容が項目の削除を伴う場合のみNGに該当します。
InnoDBでトランザクション分離レベルがREPEATABLE READの場合、いわゆるダーティリードやファントムリードが発生しない高い一貫性が保たれるようになっています。[2] REPEATABLE READが保ちたい一貫性についてよくよく考えてみると、スナップショットから読み取られたデータのあるべき状態がDDL実行後のデータのあるべき状態に包含されない場合と解釈することができそうです。
例えば外部キー制約を後から追加した場合、元々外部キー制約違反しているデータはDDL実行後に存在してはならないものになりますし、ENUM定義から特定の定義を削除した場合は、その定義を含むデータは存在しないことになります。
そのため、データ自体に矛盾が発生しないカラム名やテーブル名の変更は影響が出ないのではないかと思います。一方で、カラムのNULL化に関しては矛盾が発生しないように思えるので若干不可解な点ではあります。
ちなみに、削除対象のENUM定義を含むデータが存在する場合には以下のエラーが、
ERROR 1265 (01000): Data truncated for column 'col1' at row 1
VARCHARの長さを切り詰めようとすると抵触するデータがある場合に以下のエラーが発生します。
ERROR 1406 (22001): Data too long for column 'col1' at row 1
そのため、データに矛盾が発生しないようにアプリ側で保った状態で、いわゆるテーブル定義のお掃除をするようなケースでこのエラーに遭遇することが考えられそうです。
エラーへの対処方法
最も簡単な対処法は、エラーメッセージにあるとおり、失敗したトランザクションをやり直すことです。あるいは、ユーザーのトランザクションが発生している間にNGに該当するDDLを適用しないことになります。
とは言っても失敗したトランザクションをやり直す機構を直ちに用意することは難しいため、極力上記のエラーが発生してユーザーのトランザクションが失敗する可能性を減らすには、以下のアプローチが取れそうです。
- NGにあたるDDLを実行したい場合はpt-online-schema-changes[3]を使う
- テーブルのリネームやトリガーの作成は影響しないため
- トランザクションを極力短くする
注意点
本記事に記載した内容は私の個人的な実験と推測に基づくものであり、リファレンスマニュアルからエビデンスを取得できない内容が含まれています。MySQLのバージョンやトランザクション分離レベルの設定によって結果が異なるため、実際に本番環境にDDLを適用する前に検証していただくことをお勧めします。
参考
Discussion