MySQLで「Waiting for table metadata lock」のエラーが発生して、詳しく知りたくなったので、関連しているオンラインDDLとともに調べ、手元で試してみたことをまとめました。
MySQLのバージョンは 5.6、ストレージエンジンは InnoDB です。
この記事の多くは、MySQL 公式ドキュメントの以下のページを参考に書いています。
公式ドキュメント①：14.11.1 オンライン DDL の概要
公式ドキュメント②：14.11.2 オンライン DDL でのパフォーマンスと並列性に関する考慮事項
- DDLとは
- オンラインDDLとは
- オンラインDDLに対応しているDDL
- Ruby on RailsのMigrationでオプションをつける方法
- オンラインDDLの注意点とWaiting for table metadata lock が発生するケース
- Waiting for table metadata lock が発生するケースを実際に試してみる
- おわりに
DDLとは
DDL は Data Definition Language の略で、データ定義言語です。
SELECT などのテーブルのレコードを操作するものではなく、データベース自体を操作するものを指します。
CREATE, ALTER, DROP などが DDL です。
対して、SELECT, UPDATE, DELETE などの、テーブルのレコードを操作するものは DML（データ操作言語、Data Manipulation Language）と呼ばれています。
オンラインDDLとは
MySQL5.6より前のバージョンでは、一部を除いたDDLは、テーブルの全ての行のコピーやDDL実行中のDMLのブロックを必要とするコストの高い操作でした。
そのため、DDLを行うためにはサービスをメンテモードにするなどの対応が必要でした。
これに対し、MySQL5.6で拡張されたオンラインDDLは、テーブル全体のコピーを行わず、実行中のDMLのブロックも必要とせずにDDLを行えるようになりました。
テーブル全体のコピーとDMLブロックが不要なDDLをオンラインDDLと呼んでいますが、「テーブルコピーは不要だがDMLブロックは必要」などといったDDLも一部存在します。
公式ドキュメント① にオンラインDDLについての詳しい説明があります。
オンラインDDLを有効にするために特別なことをする必要はなく、オンラインDDLが可能な場合はオンラインDDLが適用されます。
このことは、MySQL公式ドキュメント 14.11.3 オンライン DDL の SQL 構文 に記載があります。
オンラインDDLに対応しているDDL
公式ドキュメント① に、それぞれのDDLでオンラインDDLのどの操作が可能かが一覧表でまとまっています。
一覧表で「インプレース？」が「はい」になっているものがテーブル全体のコピーが不要なDDL、「並列DMLを許可？」が「はい」になっているものがDDL実行中にDMLの実行が可能なものです。
ざっくりですが、よく行う操作だと カラムのデータ型変更以外はほぼオンラインDDLに対応していると思って良いと思います。
便利な機能として、DDLに「ALGORITHM=INPLACE, LOCK=NONE」というオプションをつけて実行すると、オンラインDDLができない場合に実行せずにエラーを返してくれます。
「ALGORITHM=INPLACE」は、テーブル全体のコピーが必要ない INPLACE 方式でDDLを実行することを指定し、「LOCK=NONE」はDDL中にロックせずに並列DMLを可能にすることを指定します。これらのオプションは、指定した方法でDDLが実行できない場合にエラーにします。
確実にオンラインDDLで実行したい場合は、このオプションをつけてDDLを実行すると良さそうです。
ALGORITHM=INPLACE は公式ドキュメント①、LOCK=NONE は公式ドキュメント② の中で説明されています。
また、DDLを実行した際にテーブル全体のコピーを行ったかどうかを確認できます。
DDL実行のレスポンスが「0 row affected」の場合、テーブル全体のコピーが行われずにDDLが実行されています。
こちらは、公式ドキュメント② で説明されています。
オンラインDDLができないカラムの型変換で、実際にオプションを試してみました。
まずオプションをつけずに実行すると問題なく成功しました。usersテーブルの age カラムを varchar(255) 型に変更しています。
mysql> ALTER TABLE `users` CHANGE `age` `age` varchar(255) DEFAULT NULL; Query OK, 4800 rows affected, 2 warnings (0.19 sec) Records: 4800 Duplicates: 0 Warnings: 2
レスポンスが「4800 rows affected」となっているので、テーブル全体のコピーが行われたと分かります。
オプションをつけて実行すると想定通りエラーとなりました。
mysql> ALTER TABLE `users` CHANGE `age` `age` varchar(255) DEFAULT NULL, ALGORITHM=INPLACE, LOCK=NONE; ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.
INPLACE 方式だとできないので、テーブル全体のコピーを伴う COPY 方式で実行してね、というエラーが発生しています。
Ruby on RailsのMigrationでオプションをつける方法
私は普段 Ruby on Rails を書いており、Rails の Migration で前述のオプションをつける方法を調べました。
私が調べた限り、execute で任意のDDLを実行する以下の方法で行うしかなさそうでした。この例は、usersテーブルに test というコメントをつけるDDLです。
class Test < ActiveRecord::Migration def up execute "ALTER TABLE `users` COMMENT 'test', ALGORITHM=INPLACE, LOCK=NONE" end def down execute "ALTER TABLE `users` COMMENT '', ALGORITHM=INPLACE, LOCK=NONE" end end
== 20200706064613 Test: migrating =============================================
-- execute("ALTER TABLE `users` COMMENT 'test', ALGORITHM=INPLACE, LOCK=NONE")
D, [2020-07-06T16:15:50.402667 #2523] DEBUG -- : (15.1ms) ALTER TABLE `users` COMMENT 'test', ALGORITHM=INPLACE, LOCK=NONE
-> 0.0155s
== 20200706064613 Test: migrated (0.0156s) ====================================execute を使った任意のDDL実行については、こちらの記事を参考にしました。
Execute SQL in Rails migrations. You may come across the following… | by Josua Schmid | Medium
migration時に実行されるSQLを確認する方法は、こちらの記事を参考にしました。（デフォルトでは migration時に実行される SQL は出力されません。）
Rails で migrate 時に実行される SQL を確認する - volpe’s diary
オンラインDDLの注意点とWaiting for table metadata lock が発生するケース
オンラインDDLには注意点があり、オンラインDDLの開始前、完了前にそれぞれ短時間ではあるものの排他的アクセスが必要となります。
つまり、オンラインDDLの開始前と完了前にDDLの対象テーブルに実行中のトランザクションがあった場合、そのトランザクションがコミットまたはロールバックするまで待機する必要があります。
さらに、オンラインDDLがトランザクションの完了を待機している状態で同じテーブルへDMLを実行すると、DMLの対象レコードがトランザクションの対象レコードと別であってもオンラインDDLと同様に待機状態になってしまいます。
このようなケースで、待機状態になっているDDL、DML が Waiting for table metadata lock の状態になっています。
メタデータ（meta data）とは、カラム名、データベース名などのデータベースについての情報のことです。
トランザクション中のメタデータの変更を防ぐため、トランザクション中はメタデータのロックがかかるようになっており、DDLを実行すると Waiting for table metadata lock となります。
トランザクションが長引いたりすると、Waiting for table metadata lock がたまり続けて障害の引き金になる可能性もあります。
図にすると次のようになります。左の列がトランザクション、中央の列が DML、右の列がオンラインDDL を表しています。
詳細は 公式ドキュメント② と、公式ドキュメント 8.10.4 メタデータのロック に記載されています。
Waiting for table metadata lock が発生するケースを実際に試してみる
トランザクション中にDMLのみを行うケース
まずはオンラインDDLを実行しないケースを試してみます。
トランザクションを実行します。
mysql> start transaction; Query OK, 0 rows affected (0.01 sec) mysql> update users set name='test2' where id=1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0
別セッションで、トランザクションを実行しているテーブルに DML を行います。
mysql> select name from users where id=2; +-----------+ | name | +-----------+ | taro | +-----------+ 1 row in set (0.00 sec)
無事結果が返ってきました。
オンラインDDL を実行していない通常のケースでは、同じテーブルでトランザクションが実行中であっても DML は正常に完了しています。
トランザクション中にオンラインDDL、DMLを行うケース
トランザクションを実行します。
mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> update users set name='test' where id=1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0
別セッションで DDL を行います。（実行しているカラムへのコメント追加はオンラインDDLが可能なものです。）
mysql> ALTER TABLE `users` CHANGE `gender` `gender` int(11) DEFAULT 2 NOT NULL COMMENT 'test comment'; （応答なし）
結果は返らず、トランザクションの完了待ちになっています。
さらに別セッションで、トランザクションを実行しているテーブルに DML を行います。
mysql> select name from users where id=2; （応答なし）
こちらもトランザクションの完了待ちになりました。
この時に、show full processlist を実行すると、応答なしとなっている2つのセッションが「Waiting for table metadata lock」となっています。
mysql> show full processlist; +----+------+-----------------+------+---------+------+---------------------------------+------------------------------------------------------------------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------------+------+---------+------+---------------------------------+------------------------------------------------------------------------------------------------+ | 1 | root | localhost | fril | Sleep | 47 | | NULL | | 2 | root | localhost | fril | Query | 36 | Waiting for table metadata lock | ALTER TABLE `users` CHANGE `gender` `gender` int(11) DEFAULT 2 NOT NULL COMMENT 'test comment' | | 3 | root | localhost | fril | Query | 24 | Waiting for table metadata lock | select name from users where id=2 | | 4 | root | localhost | fril | Query | 0 | init | show full processlist | +----+------+-----------------+------+---------+------+---------------------------------+------------------------------------------------------------------------------------------------+
そしてトランザクションを commit すると、応答がなかった2つのセッションで応答が返ってきました。
おわりに
オンラインDDLが可能なDDLだからといって、何も考えずにDDLを行うのは危険ですね。
ただ、長時間のトランザクションが発生しないテーブルであれば、オンラインDDLを実行するオプションをつけて実行することでリスクはかなり小さく抑えられそうです。
オンラインDDL実行前には、show full processlist でオンラインDDL をしようとしているテーブルに滞留しているトランザクションがないことを確認した方が安全ですね。
トランザクションの実行時間を調べる際にはこちらの記事が参考になりそうです。
【MySQL】トランザクションの実行時間を調査する - stmn tech blog
ただし、オンラインDDL 終了時にも短時間の排他的ロックが必要なので、オンラインDDL 開始時にトランザクションがないからといってノーリスクになる訳ではないそうです。
公式ドキュメントを読み込み、手元で実際に試してみると理解がかなり深まりますね。
MySQLの理解がまだまだ浅いので、バージョンが古いですが、過去読んだこの2冊を読み直していこうかな、と思っています。
現場で使える MySQL (DB Magazine SELECTION)
- 作者:松信 嘉範
- 発売日: 2006/03/17
- メディア: 単行本
- 作者:Baron Schwartz,Peter Zaitsev,Vadim Tkachenko
- 発売日: 2013/11/25
- メディア: 大型本
間違えている箇所や説明が分かりにくい箇所等あれば、コメントなどでご教示いただけると大変ありがたいです。
