MySQL: 「テーブル メタデータ ロックの待機中」問題の恒久的な解決策

Question

MySQL データベースは、3 つの Web アプリケーションにストレージ バックエンド サービスを提供します。ただし、最近、「テーブルメタデータロックを待機しています」というエラーが永続的に発生しました。これはほぼ常に発生しますが、理由がわかりません。

リーリー

もちろん、対応するプロセスを強制終了することもできます。ただし、データベース「bookmaker3」のテーブル構造を作成しようとしているプログラムを再起動すると、新しく作成されたプロセスが再び Metallock になります。

データベースを削除することさえできません:

リーリー

これにより金属製のロックも生成されます。

この問題を解決するにはどうすればよいですか?

Answer 1

残念ながら、受け入れられている解決策は間違っています。それは完全に正しいです

これは確かに (ほぼ 確かに、以下を参照) やるべきことです。しかし、その後、

が表示されます。

...そして 1398 は ロックとの接続ではありません。どうして？ 1398 は、ロックを待機している接続です。これは、まだロックを取得していないことを意味するため、強制終了しても効果はありません。ロックを保持しているプロセスは引き続きロックを保持し続けるため、 何らかの操作を実行しようとしている次の スレッドも 停止し、適切な順序で「メタデータ ロックの待機」に入ります。 「メタデータ ロックを待機している」プロセス (WFML) が同様にブロックしないことを保証することはできませんが、WFML プロセスを強制終了するだけでは

何も起こらないことは確かです。 本当の理由は、

別のプロセスがロックを保持しているためです

、そしてさらに重要なことに、SHOW FULL PROCESSLIST はどのプロセス を直接教えてくれないのです。 確実に言えることは、

「メタデータ ロックを待機中」とマークされたプロセスは

存在しないということです。この人たちは被害者だと言えるでしょう。

SHOW FULL PROCESSLIST

WILL プロセスが 何かを実行しているかどうかを示します (はい)。通常は機能します。ここで、ロックを保持しているプロセス は何もせず 、同様に何もせず「スリープ中」として報告する他のスレッドに隠されています。 SHOW FULL PROCESSLIST で DML を実行しているプロセス、または「データ送信」状態にあるプロセスが表示された場合は、

その

がほぼ確実に原因です。他のプロセスは、ロックが解放されるのを待っています (暗黙的なロックである可能性があります。プロセスは LOCK TABLE を発行する必要はまったくなく、実際には別の方法でロックされます)。ただし、プロセスは操作を実行していないときにロックを保持し、「スリープ中」として適切にマークされることがあります。 OP の場合、犯人 はほぼ確実に プロセス

1396

です。このプロセスはプロセス 1398 より前に開始されており、現在 スリープ 状態にあり、 46秒続きました。 1396 は明らかにやるべきことをすべて実行したため (MySQL に関する限り、現在スリープ状態であることが判明し、46 秒間スリープ状態が続いています)、# で に入ったスレッドはありません。 ## ロックを保持し、スリープ前も保持することができます (そうでないと 1396 も停止します)。 MySQL の「デッドロックフリー」ロック ポリシーにより、どのプロセスもロックを保持したり、ロックを解放したり、再びロックを復元したりすることはできません。そのため、ロック待機は常に、ロックをまだ保持している、または一度も保持したことがないプロセスによって実行されます。以前のロックはプロセスによって引き起こされました。これは、ロック「キュー」が に順序付けされていることを保証するため、便利です (以下でこの事実を利用します)。 重要

: 制限付きユーザーとして MySQL に接続する場合、

SHOW FULL PROCESSLIST では、すべてのプロセスが 表示されません。したがって、ロックは目に見えないプロセスによって保持されている可能性があります。

つまり: SHOW FULL PROCESSLIST ですべてが表示され、Running プロセスが表示される場合は、おそらくそのプロセスが責任を負っており、そのプロセスが実行中の処理を完了するまで待つ必要があります。または、あなたはそれを殺すことができます - 自己責任で）。 この回答の残りの部分では、プロセスが明白な理由

もなく待機しており、誰も何もしていないように見えるという、混乱を招く状況について説明します。

Betterプロセスリストの表示

リーリー

上記は、SLEEP 状態のプロセスのみを表示するように調整でき、とにかく時間の降順に並べ替えるので、ハングしたプロセス (通常は「待機中」の順序により になります) を見つけやすくなります。メタデータの場合、「ロックの直前に 1 回スリープします」; は常に保留中のスリープ時間よりも 多いスリープの 1 つです。 ＃＃＃大事なこと＃＃＃

「メタデータ ロックを待機している」プロセスは

別の

にしておきます。

手っ取り早く汚い解決策、あまりお勧めできませんが、手っ取り早いです

同じデータベース上の「スリープ」状態にあり、「メタデータ ロックを待機中」状態にある最も古い

スレッドよりも

古いすべての

プロセスを強制終了します。 アルノー・アマウリーならこうする: WaitingForMetadataLock に少なくとも 1 つのスレッドがあるデータベースごとに:

このデータベース上の WFML の最も古い接続は Z 秒間存在しています

• このデータベース上のすべての「スリープ」スレッドおよび Z より古いスレッドは停止する必要があります。念のため、新鮮なものから始めてください。
  • そのデータベースに古い非スリープ接続がある場合、それがロックを保持している可能性がありますが、
  何かを実行しています。もちろん強制終了することもできますが、特に更新/挿入/削除の場合は、自己責任で実行してください。
  各
  • KILL の後、状況を再評価し、それに応じてプロセスを再開します。待機中のプロセスは現在実行されているか、一時的に実行されていて現在はスリープしている可能性があります。 彼らは今でも新しいメタデータ ロックを保持している可能性があります
  。
  • 100 回中 99 回、強制終了されるスレッドは、スリープ状態にあり、メタデータ ロックを待っている古いスレッド
  より古い
  最も若い ## スレッドです。 #the rout:
リーリー

(*) TIME シーケンスには実際にはミリ秒が含まれていますが、それが表示されていないだけだと言われています。したがって、両方のプロセスの時間値は 19 ですが、最も低いプロセスの方が若いはずです。

より集中的な修理 SHOW ENGINE INNODB STATUS を実行し、「TRANSACTION」セクションを確認します。特に、

のようなものが見つかります。 リーリー

次に、

SHOW FULL PROCESSLIST

を使用して、スレッド ID 1396 が #1701 トランザクションでどのような処理を行っているかを確認します。おそらく「スリープ」状態になっていると思われます。つまり、アクティブなロックを持つアクティブなトランザクション (#1701) は、元に戻すログ エントリがあるため、いくつかの変更も加えられていますが、現在はアイドル状態です。

これ

はあなたが殺さなければならないスレッドです。これらの変更は失われます。 MySQL で何もしないことは、一般的に何もしないことを意味するわけではないことに注意してください。 MySQL からいくつかのレコードを取得し、FTP アップロード用の CSV を構築する場合、FTP アップロード中、MySQL 接続はアイドル状態になります。

実際には、MySQL を使用するプロセスと MySQL サーバーが同じコンピュータ上にあり、そのコンピュータで Linux が実行されており、root 権限を持っている場合、どの プロセス が要求されたファイルを所有しているかを確認する方法があります。ロックを接続します。これにより、プロセスが 本当に 何かを実行しているかどうか (CPU 使用率、最悪の場合は

strace -ff -p pid

に基づいて) を判断できるようになり、強制終了しても安全かどうかを判断するのに役立ちます。誰か。

なぜこのようなことが起こるのでしょうか?

「永続的」または「プールされた」MySQL 接続を使用する Web アプリケーションでこの問題が発生するのを確認しましたが、通常は時間の節約はほとんどありません。Web アプリケーションのインスタンスは終了しますが、 接続は終了しません。したがって、生きたままロックされており、他の人からブロックされています。

私が見つけたもう 1 つの興味深いアプローチ は、上記の仮説で、いくつかの行 を返すクエリを実行し、その一部のみを取得するというものです 。クエリが「自動クリーン」に設定されていない場合 (ただし、基礎となる DBA が設定している場合)、接続は開いたままになり、テーブルの完全なロックが防止されます。行を選択し、エラーがあるか（存在しない）どうか（存在する必要がある）を確認することで行が存在することを確認するコードでこれに遭遇しましたが、は実際には取得されませんでした行 .

PHP と PDO

PDO には永続的な接続機能があります。これは、PDO が接続をプールせず、各接続を閉じないようにする方法です。非常に厄介。

開くときに、オプションを設定します (4 番目のオプションは new PDO()):

リーリー

切断時:

リーリー

データベースに問い合わせる

最新の MySQL を使用している場合、

ですが、あまり新しくない 、これは非推奨になる予定です 、原因を見つけるもう 1 つの方法は次のとおりです (ここでも権限情報モードが必要になります)。 リーリー 実際の解決策には時間と労力がかかります

このアーキテクチャによって問題が発生することがよくあります:

Web アプリケーションが終了するか、Web アプリケーションの軽量スレッド インスタンスが終了すると、

コンテナー/接続プールは

存在しない可能性があります。接続を開いたままにするのは container であるため、明らかに接続は閉じられていません。予想どおり、MySQL は操作 が完了したとはみなしません。 Web アプリケーション自体がクリーンアップしない場合 (トランザクションの

ROLLBACK

または COMMIT がない、UNLOCK TABLES がないなど)、Web アプリケーションはアプリケーションは実行を開始します 任意の操作 はまだ存在します ため、他の操作は引き続きブロックされる可能性があります。 その場合、解決策は 2 つあります。さらに悪いことに、

アイドル タイムアウトを短縮する

があります。しかし、クエリ間の待ち時間が長すぎるとどうなるか考えてみてください (文字通り、「MySQL サーバーがなくなった」)。利用可能な場合は、mysql_ping を使用できます (まもなく非推奨になります。 PDO には回避策 があります。 あるいは、 エラーが発生したかどうか を確認することもできます。この問題が発生した場合は、接続を再度開きます(これが Python の方法です。) したがって、パフォーマンスのコストはわずかですが、これは実行可能です。 より優れた、よりスマートなソリューションの実装はそれほど簡単ではありません。すべての行を取得するかすべてのクエリ リソースを解放することを確認し、すべての例外をキャッチして正しく処理するか、可能であれば 永続的な接続を完全にスキップして、スクリプト自体をクリーンアップできるようにしてください。各インスタンスが独自の接続を作成するか、

スマート プール ドライバーを使用します

(PHP PDO では、明示的に false に設定された PDO::ATTR_PERSISTENT を使用します)。 代わりに (PHP のように)、トランザクションをコミットまたはロールバックすることでデストラクターと例外ハンドラーに接続を強制的にクリーンアップさせることもできます (これで十分です)。あるいは、明示的なテーブルのロック解除と RELEASE_ALL_LOCKS( )、または良好な結果を得るには、接続自殺 (KILL CONNECTION_ID()) を送信します。

既存の結果セット リソースをクエリして解放する方法がわかりません。唯一の方法は、これらのリソースをプライベート配列に保存することです。