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

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をかけたままアップロード処理が行われている・・

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