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

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 を介してタイムアウトを設定するのが最適です。

以下もご興味があるかもしれません:
  • MySQLのテーブル構造を変更する際に知っておきたいメタデータロックの詳しい解説
  • MYSQL メタデータ ロック (MDL ロック) MDL ロックの問題分析
  • MySQLスレーブは列の外部キーチェックと自動増分ロックを遅延します
  • MySQLのネクストキーロックのロック範囲についての簡単な説明
  • MySQL ロック制御同時実行方法
  • PHP+MySQL の高同時ロックトランザクション処理問題の解決方法
  • MYSQL メタデータ ロック (MDL ロック) の理論とロック タイプ テスト

<<:  webpackでHMRを手動で実装するいくつかの方法

>>:  VMwareがwin10ホームバージョンに64ビットオペレーティングシステムをインストールできない問題を解決します

推薦する

Reactのヒントはフックの依存関係の問題を解消する方法を教えます

reactプロジェクトで非常に一般的なシナリオ: const [watchValue、setWatc...

CSS で複数の境界線を実装するためのヒント

1. 複数の国境[1]背景: ボックスシャドウ、アウトライン使用シナリオの多様性を考慮すると、複数の...

Mysql 日付クエリの詳細な紹介

現在の日付を照会する CURRENT_DATE() を選択します。 CURDATE() を選択する;...

CSS を使用して三角形を実装する一般的な手法 (複数の方法)

面接の経験によっては、CSS に関する質問がよく見られ、CSS を使用して三角形を描画する方法につい...

MySQLにおける静的変数の役割の詳細な説明

MySQLにおける静的変数の役割の詳細な説明静的変数の使用 静的変数サンプルコード: 関数テスト()...

ウェブページ作成時に標準 HTML コードを使用する際のポイント

<br />多くのウェブサイト デザイナーが犯す最も一般的な間違いは、ウェブページが I...

JavaScript の find() メソッドと filter() メソッドの違いのまとめ

目次序文JavaScript find() メソッドJavaScript filter() メソッド...

Apache Tika を使用してファイルが破損しているかどうかを検出する方法

Apache Tika は、さまざまな形式のファイルからファイル タイプを検出し、コンテンツを抽出す...

Linuxのwhichコマンドの具体的な使い方

Linux でファイルを見つけたいのに、その場所がわからないことがよくあります。次のコマンドを使用し...

HTML シンプルショッピング数量アプレット

この記事では、参考までにシンプルなHTMLショッピング数量アプレットを紹介します。具体的な内容は次の...

HTML における iFrame タグの 2 つの使用法

最近、私は「ぶどうコレクション」というプロジェクトに取り組んでいます。簡単に言うと、Budou ペー...

MySQL バイナリログデータ復旧: 誤ってデータベースを削除した場合の詳細な説明

MySQL Binログデータの回復: 誤ってデータベースを削除した場合前書き: テスト マシンで誤っ...

JavaScript は、マウスがテーブル行を通過するときに色の識別を実装します。

この記事では、マウスがテーブルの行を通過するときにJavaScriptを使用して色ラベルを表示する方...

Vue router-viewとrouter-linkの実装原理

使用 <div id="アプリ"> <router-link ...