Waiting for table metadata lockの対処の仕方、おさらい。
MySQL Casual Advent Calendar 2018 - Qiita 8日目の記事です。
駆け込み。
やきなおしです。
わりとニーズがあるらしく、このネタで過去エントリにちょいちょいアクセスがくるので、とにかく急いでいる人用に、シンプルにまとめました。
MySQL5.6、5.7で使えます。
たぶん8.0でも使えるんじゃないかな。知らんけど。
MySQL5.7でPerformance_schema.metadata_locksが追加されたけど、これも設定入れないと使えないんだよね。
犯人を特定する。
1. information_schema.innodb_trxを全件参照する。
列は以下の通り。
trx_id trx_state trx_started trx_requested_lock_id trx_wait_started trx_weight trx_mysql_thread_id trx_query trx_operation_state trx_tables_in_use trx_tables_locked trx_lock_structs trx_lock_memory_bytes trx_rows_locked trx_rows_modified trx_concurrency_tickets trx_isolation_level trx_unique_checks trx_foreign_key_checks trx_last_foreign_key_error trx_adaptive_hash_latched trx_adaptive_hash_timeout trx_is_read_only trx_autocommit_non_locking
2. trx_stateが「RUNNING」で、trx_startedが異常に古いもの（表示される中で一番古いもの。*1）を探し、trx_mysql_thread_idを控える。
9割方、こいつがトランザクションを開始してテーブルを触った（metadata lockを取得した）まま、Commit/Rollbackしない（metadata lockを解放しない）まま放置して、DDLが阻害されてる。
この場合、SQL自体はもう発行されてない（終わっている）状態でSLEEPしてるんだよね。
純粋に長いSQLが実行中で「Waiting for table metadata lock」が出た場合は、終わるのを待っていればそのうち解消します。
日単位で返ってこないSQLでなければね。
犯人をやっつける。
1. SUPER権限を持っているMySQLユーザーで接続し、以下のコマンドを実行。
kill スレッドID
なお、AWS RDS/MySQLやAurora MySQL Compatibleの場合、SUPER権限は使えませんので、代替提供されている以下のコマンドを実行します。
CALL mysql.rds_kill(スレッドID);
接続元となるプログラム/アプリケーションがわかっていて、そちらからきれいに停止ができるのであればそうしたほうがよいです。
2. 解消したことを確認する
SHOW FULL PROCESSLISTに「Waiting for table metadata lock」が出てこなくなっていればOK。
しばらくしても解消しない場合、まだ犯人がいるので、「犯人の特定」からやりなおし。
合わせて読んでね。
最後に、やっぱりこの本を推しておく！
詳解MySQL 5.7 止まらぬ進化に乗り遅れないためのテクニカルガイド (NEXT ONE)
- 作者: 奥野幹也
- 出版社/メーカー: 翔泳社
- 発売日: 2016/08/26
- メディア: 単行本（ソフトカバー）
- この商品を含むブログを見る
