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をかけたままアップロード処理が行われている・・
等、、サーバーやプログラム負荷以外のところで発生してる事が多い気がします。