Help us understand the problem. What is going on with this article?

サービス稼働中にMySQLでALTER TABLEしたら Waiting for table metadata lock が溢れて死んだ

More than 5 years have passed since last update.

ALTER TABLE実行する時は注意しましょう、、という事で。

ALTER TABLE

ALTER TABLEすると以下のような挙動でカラム変更されるようです

  • 他のセッションからのREADを許可し、WRITEをブロック
  • 新しいtable定義の一時テーブルを作成
  • 一時tableに元tableのデータをすべてコピー
  • 一時tableを元table名にリネームして元tableを削除
  • ブロックされていたWRITE系クエリを反映

全コピしてるので、ALTER TABLEを実行する時にどでかいtableのサイズの場合は長い時間WRITEがブロックされるので注意が必要です。

ALTER TABLEを実行した環境

上記を踏まえた上で、以下のようなテーブルにALTER TABLEを実行してカラム追加する事にした

  • レコード数はちょっとしかない
  • READはものすごいくるけど、WRITEはない

こんなテーブルだとALTER TABLEでどうにかなってしまう事はないだろう、と。
そもそもレコード数はわずか4レコードしかないくらいのものだったのでWRITEすらブロックされる時間も一瞬なはず。

実際実行したらWaiting for table metadata lockが溢れて死んだ

実際に実行したら結果が帰ってこない。あれ?って思ってたらWaiting for table metadata lockが溢れてMySQLが応答しなくなりサービスがダウンしちゃいました。

原因

結果を書くと、ALTER TABLEを実行したtableが他セッションでtransaction中だった事が原因でした。
そのtransactionが終わるまでALTER TABLEはWaiting for table metadata lockで待ちとなり、その後きたSELECTもWaiting for table metadata lockになりました。

検証

以下のように検証

セッションA

transactionを開始してSELECT文を一回実行する

sessionA> start transaction;
Query OK, 0 rows affected (0.00 sec)
sessionA> select * from test;
+----+------+
| id | a    |
+----+------+
|  1 | a    |
+----+------+
2 rows in set (0.00 sec)

これでtransactionはACTIVEになる

セッションB

別セッションでSELECTを実行してみる。

sessionB> select * from test;
+----+------+
| id | a    |
+----+------+
|  1 | a    |
+----+------+
1 rows in set (0.00 sec)

問題なし。

ALTER TABLEを実行する

sessionB> ALTER TABLE test add column b char(32) default 'b';

結果が帰ってこない

セッションC

ALTER TABLE実行が帰ってこない状態で、さらに別セッションでSELECTを実行する

sessionC> select * from test;

結果が帰ってこない

セッションD

さらに別セッションを開いて現在の状態を表示する

sessionD> show processlist;
+------+------+-----------+----------+---------+------+---------------------------------+----------------------------------------------------------+
| Id   | User | Host      | db       | Command | Time | State                           | Info                                                     |
+------+------+-----------+----------+---------+------+---------------------------------+----------------------------------------------------------+
| 1 | root | localhost | hoge | Sleep   |  312 |                             | NULL                                         |
| 2 | root | localhost | hoge | Query   |  218 | Waiting for table metadata lock | ALTER TABLE test add column b char(32) default 'b' |
| 3 | root | localhost | hoge | Query   |  113 | Waiting for table metadata lock | select * from test                                  |
| 4 | root | localhost | hoge | Query   |    0 | init                            | show processlist                                         |
+------+------+-----------+----------+---------+------+---------------------------------+----------------------------------------------------------+
3 rows in set (0.00 sec)

結果が帰ってこないALTER と SELECTがWaiting for table metadata lockになっている。

セッションA

transactionをcommitしたりrollbackして終了する。

sessionA > rollback;
Query OK, 0 rows affected (0.00 sec)

セッションB

sessionB> ALTER TABLE test add column b char(32) default 'b';
Query OK, 0 rows affected (4 min 38.58 sec)
Records: 0  Duplicates: 0  Warnings: 0

test tableにかかってたtransactionが終わった瞬間ALTERが実行された

セッションC

sessionC> select * from test;
+----+------+
| id | a    |
+----+------+
|  1 | a    |
+----+------+
1 row in set (2 min 53.18 sec)

test tableにかかってたtransactionが終わった瞬間SELECTが実行された

結論

通常、長時間transactionがかかったままなんて事はないのでレアケースかもしれませんがALTER TABLE実行する時、対象がREADのみで少ないテーブルでも問題が発生する事があります。

実行する前にはshow processlist;であまりに長いsleep状態のプロセスがtransaction張った状態で対象tableを触ってるんじゃないか、とか確認してから実行したらちょっとは安心かもしれません。

ちなみに、長いtransactionを張ってしまう原因としては

  • プログラムが外部サービスとの連携していてレスポンスが帰ってこない時にタイムアウトまでtransactionがかかったままに
  • ファイルアップロード等のサービスの際にtransactionをかけたままアップロード処理が行われている・・

等、、サーバーやプログラム負荷以外のところで発生してる事が多い気がします。

cs_sonar
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
No comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
ユーザーは見つかりませんでした