MySQL でテーブル メタデータ ロックを待機する理由と方法

MySQL が alter table などの DDL 操作を実行すると、テーブル メタデータ ロックの待機シナリオが発生する可能性があります。さらに、TableA のテーブル変更操作がテーブル メタデータ ロックの待機状態で停止すると、TableA に対する後続の操作 (読み取りを含む) も、テーブルを開く段階でテーブル メタデータ ロックの待機のロック待機キューに入るため、実行できなくなります。このようなロック待機キューが本番環境のコア テーブルに出現すると、悲惨な結果を招くことになります。

alter table が Waiting for table metadata lock を生成する理由は、実は非常に単純で、通常は次のような単純なシナリオによるものです。

シナリオ 1: 長いトランザクションの実行により DDL がブロックされ、同じテーブルに対する後続のすべての操作がブロックされる

show processlist を通じて、TableA で進行中の操作 (読み取りを含む) があることがわかります。この時点で、alter table ステートメントはメタデータ排他ロックを取得できず、待機します。

これは最も基本的な状況であり、MySQL 5.6 のオンライン DDL と競合しません。一般的なテーブル変更操作中（下図参照）は、作成後のステップで排他メタデータ ロックが取得されます。テーブル変更プロセス（通常最も時間のかかるステップ）が実行されると、テーブルへの読み取りと書き込みは正常に続行されます。これはオンライン DDL のパフォーマンスであり、以前のようにテーブル変更プロセス全体で書き込みがブロックされることはありません。 (もちろん、すべての種類の変更操作がオンラインで実行できるわけではありません。詳細については、公式マニュアルを参照してください: http://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-overview.html)
解決策: DDL が配置されているセッションを終了します。

シナリオ 2: トランザクションが送信されず、DDL がブロックされ、同じテーブルに対する後続のすべての操作がブロックされる

show processlist では TableA の操作を確認できませんが、実際にはコミットされていないトランザクションがあり、information_schema.innodb_trx で確認できます。トランザクションが完了するまで、TableA のロックは解除されず、alter table はメタデータの排他ロックを取得しません。

解決策: select * from information_schema.innodb_trx\G を使用して、コミットされていないトランザクションの sid を見つけ、それを強制終了してロールバックします。

シナリオ3:

show processlist を通じて TableA に対して操作は行われず、information_schema.innodb_trx には進行中のトランザクションはありません。これは、明示的なトランザクションで、TableA に対して失敗した操作が実行された (たとえば、存在しないフィールドが照会された) ためであると考えられます。この時点では、トランザクションは開始されていませんが、失敗したステートメントによって取得されたロックは依然として有効であり、解放されていません。失敗したステートメントは、performance_schema.events_statements_current テーブルから見つけることができます。

公式マニュアルには次のように記載されています。

サーバーが、構文的には有効だが実行中に失敗したステートメントのメタデータ ロックを取得した場合、ロックは早期に解放されません。失敗したステートメントはバイナリ ログに書き込まれ、ロックによってログの一貫性が保護されるため、ロックの解放はトランザクションの終了まで延期されます。

つまり、構文エラーを除いて、他のエラーのあるステートメントによって取得されたロックは、トランザクションがコミットまたはロールバックされるまで解放されません。しかし、この動作の理由は理解しにくいです。失敗したステートメントはバイナリ ログに書き込まれ、失敗したステートメントはバイナリ ログにまったく記録されないため、ロックによってログの一貫性が保護されるからです。

解決策: performance_schema.events_statements_current から sid を見つけて、セッションを強制終了します。DDL が配置されているセッションを強制終了することもできます。

つまり、alter table ステートメントは非常に危険です (実際には、その危険性はコミットされていないトランザクションや長いトランザクションによって引き起こされます)。操作の前に、操作対象のテーブルで進行中の操作がないこと、コミットされていないトランザクションがないこと、明示的なトランザクションにエラー ステートメントがないことを確認することをお勧めします。監視なしで実行されるテーブル変更メンテナンス タスクがある場合は、メタデータ ロックの長時間待機を回避するために、lock_wait_timeout を介してタイムアウトを設定するのが最適です。