序文: MySQL で SQL 文を実行すると、予想した時間内に文が完了しません。このような場合、通常は MySQL データベースにログインして問題がないか確認します。通常使用されるコマンドは、どのセッションが存在し、それらのセッションが何をしているかを確認するための show processlist です。テーブル メタデータ ロックを待機中と表示される場合は、MDL メタデータ ロックが発生しています。この記事では、MDL ロックの生成とトラブルシューティングのプロセスについて紹介します。 1. MDL ロックとは何ですか? MDL はメタデータ ロックの略です。 MDL ロックの主な機能は、テーブル メタデータのデータ一貫性を維持することです。テーブルにアクティブなトランザクション (明示的または暗黙的) がある場合、メタデータを書き込むことはできません。そのため、テーブルのメタデータ情報を保護し、DDL 操作と DML 操作間の一貫性を解決または確保するために、MySQL バージョン 5.5 以降では MDL ロックが導入されました。 MDL の導入により、主に 2 つの問題が解決されます。1 つはトランザクション分離の問題です。たとえば、反復可能分離レベルでは、セッション A が 2 つのクエリ中にテーブル構造を変更すると、2 つのクエリ結果が矛盾し、反復可能読み取りの要件を満たすことができません。もう 1 つはデータ複製の問題です。たとえば、セッション A が複数の更新ステートメントを実行し、別のセッション B がテーブル構造を変更して最初に送信した場合、スレーブは最初に変更をやり直し、次に更新をやり直すため、複製エラーが発生します。 メタデータ ロックは、サーバー レイヤー ロック、テーブル レベルのロックです。実行される DML または DDL ステートメントごとに、MDL ロックが適用されます。DML 操作には MDL 読み取りロックが必要であり、DDL 操作には MDL 書き込みロックが必要です (MDL ロック プロセスはシステムによって自動的に制御され、直接介入することはできません。読み取りと読み取りは共有され、読み取りと書き込みは相互に排他的であり、書き込みと書き込みは相互に排他的です)。MDL ロックを申請する操作はキューを形成し、キュー内の書き込みロック取得の優先順位は読み取りロックよりも高くなります。書き込みロック待機が発生すると、現在の操作がブロックされるだけでなく、テーブルに対する後続のすべての操作もブロックされます。トランザクションが MDL ロックを適用すると、トランザクションが完了するまでロックは解除されません。 (ここでは特別なケースがあります。トランザクションに DDL 操作が含まれている場合、MySQL は DDL 操作ステートメントが実行される前に暗黙的にコミットし、DDL ステートメント操作が別のトランザクションとして存在することを確認し、メタデータ排他ロックが確実に解放されるようにします)。
2. MDLロックをシミュレートして見つける MDL ロックは通常、テーブルに対して DML 操作を実行しているコミットされていないトランザクションがあるために DDL 操作が中断されたときに発生します。ただし、MySQL セッションの数が非常に多いため、どのセッションの操作が時間内に送信されず、DDL に影響を与えたかはわかりません。通常、この種の問題をトラブルシューティングする場合、information_schema.innodb_trx テーブルから現在実行中のトランザクションをクエリする必要があります。ただし、SQL が実行されてコミットされていない場合、このテーブルで SQL を確認することはできません。 MySQL 5.7 では、MDL 関連情報を記録するために、performance_schema ライブラリに新しい metadata_locks テーブルが追加されました。まず、MDL ロック ログを有効にする必要があります。有効にするには、次の SQL を実行します。 performance_schema.setup_instruments を更新します 有効 = 'YES'、時間 = 'YES' に設定 WHERE NAME = 'wait/lock/metadata/sql/mdl'; 以下に、MDL ロックをシミュレートして検出するプロセスを示します。 # セッション 1 トランザクション内で DML 操作を実行します。mysql> begin; クエリは正常、影響を受けた行は 0 行 (0.00 秒) mysql> student_tb (stu_id,stu_name) に値 (1009,'xin') を挿入します。 クエリは正常、1 行が影響を受けました (0.00 秒) mysql> student_tb から * を選択します。 +--------------+--------+----------+----------------------+----------------------+ | 増分 ID | スタッ ID | スタッ名 | 作成時刻 | 更新時刻 | +--------------+--------+----------+----------------------+----------------------+ | 1 | 1001 | 1 から | 2019-11-28 16:36:14 | 2019-11-28 16:36:14 | | 2 | 1002 | dfsfd | 2019-11-28 16:36:14 | 2019-11-28 16:36:14 | | 3 | 1003 | fdgfg | 2019-11-28 16:36:14 | 2019-11-28 16:36:14 | | 4 | 1004 | sdfsdf | 2019-11-28 16:36:14 | 2019-11-28 16:36:14 | | 5 | 1005 | dsfsdg | 2019-11-28 16:36:14 | 2019-11-28 16:36:14 | | 6 | 1006 | fgd | 2019-11-28 16:36:14 | 2019-11-28 16:36:14 | | 7 | 1007 | fgds | 2019-11-28 16:36:14 | 2019-11-28 16:36:14 | | 8 | 1008 | dgfsa | 2019-11-28 16:36:14 | 2019-11-28 16:36:14 | | 9 | 1009 | xin | 2019-11-28 17:05:29 | 2019-11-28 17:05:29 | +--------------+--------+----------+----------------------+----------------------+ # セッション 2 DDL 操作を実行してテーブルにフィールドを追加し、DDL がハングしていることを確認します。mysql> alter table student_tb add stu_age int after stu_name; # セッション 3 すべてのセッションをクエリし、MDL ロックが発生していることを確認します。mysql> show processlist; +----+------+-----------+---------+--------+-------+---------------------------------+----------------------------------------------------------------------+ | ID | ユーザー | ホスト | db | コマンド | 時間 | 状態 | 情報 | +----+------+-----------+---------+--------+-------+---------------------------------+----------------------------------------------------------------------+ | 31 | ルート | ローカルホスト | testdb | スリープ | 125 | | NULL | | 32 | root | localhost | testdb | クエリ | 7 | テーブル メタデータ ロックを待機中 | alter table student_tb add stu_age int after stu_name | | 33 | root | localhost | testdb | クエリ | 0 | 開始 | プロセスリストを表示 | +----+------+-----------+---------+--------+-------+---------------------------------+----------------------------------------------------------------------+ # セッション 3 metadata_locks テーブル レコードを確認し、student_tb テーブルに MDL ロック競合があることを確認します。mysql> select * from performance_schema.metadata_locks; +-------------+--------------------+-----------------------+----------------------+---------------+----------+-----------------+----------------+ | OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_DURATION | LOCK_STATUS | SOURCE | OWNER_THREAD_ID | OWNER_EVENT_ID | +-------------+--------------------+-----------------------+----------------------+---------------+----------+-----------------+----------------+ | テーブル | testdb | student_tb | 94189250717664 | SHARED_WRITE | トランザクション | 許可 | | 56 | 34 | | グローバル | NULL | NULL | 139764477045472 | INTENTION_EXCLUSIVE | ステートメント | 許可 | | 57 | 18 | | スキーマ | testdb | NULL | 139764477697808 | INTENTION_EXCLUSIVE | トランザクション | 許可 | | 57 | 18 | | テーブル | testdb | student_tb | 139764477697904 | SHARED_UPGRADABLE | トランザクション | 許可 | | 57 | 18 | | テーブル | testdb | student_tb | 139764477697696 | 排他 | トランザクション | 保留中 | | 57 | 18 | | テーブル | パフォーマンス スキーマ | メタデータ ロック | 139764544135120 | 共有読み取り | トランザクション | 許可 | | 58 | 20 | +-------------+--------------------+-----------------------+----------------------+---------------+----------+-----------------+----------------+ # セッション3は他のシステムテーブルを組み合わせてセッションIDを見つける mysql> performance_schema.metadata_locks から m.*、t.PROCESSLIST_ID を選択し、 performance_schema.threads t を m.owner_thread_id=t.thread_id に結合します。 +-------------+--------------------+-----------------------+----------------------+---------------+----------+-----------------+----------------+----------------+ | OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_DURATION | LOCK_STATUS | SOURCE | OWNER_THREAD_ID | OWNER_EVENT_ID | PROCESSLIST_ID | +-------------+--------------------+-----------------------+----------------------+---------------+----------+-----------------+----------------+----------------+ | テーブル | testdb | student_tb | 94189250717664 | SHARED_WRITE | トランザクション | 許可 | | 56 | 34 | 31 | | グローバル | NULL | NULL | 139764477045472 | INTENTION_EXCLUSIVE | ステートメント | 許可 | | 57 | 18 | 32 | | スキーマ | testdb | NULL | 139764477697808 | INTENTION_EXCLUSIVE | トランザクション | 許可 | | 57 | 18 | 32 | | テーブル | testdb | student_tb | 139764477697904 | SHARED_UPGRADABLE | トランザクション | 許可 | | 57 | 18 | 32 | | テーブル | testdb | student_tb | 139764477697696 | 排他 | トランザクション | 保留中 | | 57 | 18 | 32 | | テーブル | パフォーマンス スキーマ | メタデータ ロック | 139764544135120 | 共有読み取り | トランザクション | 許可 | | 58 | 22 | 33 | | テーブル | performance_schema | スレッド | 139764549217280 | SHARED_READ | トランザクション | 許可 | | 58 | 22 | 33 | +-------------+--------------------+-----------------------+----------------------+---------------+----------+-----------------+----------------+----------------+ # 結果の解釈: 上記の結果から、セッション 31 が student_tb テーブルの SHARED_WRITE ロックを保持していることは明らかです。 # MDL ロックを解除するには、送信されるまで待つか、セッションを手動で終了する必要があります。 3. MDLロックを最適化して回避する方法 MDL ロックが発生すると、テーブルへの後続のアクセスがすべてブロックされ、接続のバックログが発生するため、ビジネスに大きな影響を与えます。日常生活では、MDL ロックの発生を避けるように努めるべきです。参考までに、最適化の提案をいくつか示します。
要約: この記事では、主に MDL ロックを 3 つの側面から説明します。まず、MDL ロックの原因と機能を紹介します。次に、MDL ロックをシミュレートし、検索と解決の方法を示します。最後に、MDL ロックを回避するための提案をいくつか示します。実際、MDL ロックは DB の操作とメンテナンス中に頻繁に発生します。MDL ロックは脅威ではなく、データベース オブジェクトを保護し、データの一貫性を確保するためにのみ使用されます。この記事を読んで、MDL ロックについてより深く理解していただければ幸いです。 上記はMySQL MDLメタデータロックの詳細分析です。MySQL MDLメタデータロックの詳細については、123WORDPRESS.COMの他の関連記事に注目してください。 以下もご興味があるかもしれません:
|
<<: Vue.js ディレクティブのカスタム命令の詳細な説明
>>: Nginx 外部ネットワーク アクセス イントラネット サイト構成操作
Jenkins+Tomcatサーバーの設定中に、Tomcat設定ファイルが変更され、サーバーのTom...
問題を見つける最近、プロジェクトで問題が発生しました。接続が多すぎるため、「接続が多すぎます」という...
1. MySQL 1.1 MySQLのインストールmysql-5.5.27-winx64 ダウンロー...
序文MySQL の構文は誰にとっても難しいものではないと思いますが、この記事では主に MySQL の...
序文最近、プロジェクトで SQL Server を使用するプログラムに遭遇しました。このデータベース...
MySQL エスケープエスケープとは、エスケープ文字の本来の意味を意味します。エスケープ文字の目的は...
Ubuntu 18.04の場合1. sudo apt install python 。コマンドライン...
p>「サービス」で手動で起動すると、 コンソールから起動します: 次に、...\MySQL S...
1 ユーザーを作成し、ユーザーのルートパスとパスワードを指定します useradd -d /home...
私は今日、mybatis を学び、データベースに対していくつかの簡単な追加、削除、変更、クエリを実行...
最近、Linux オペレーティング システムを使用して実行可能ファイルを実行していたところ、「そのよ...
この例では、jQuery を使用してマウス ドラッグ イメージ機能を実装します。まず、ラッパーを設定...
1. テーブル内のフィールドの大文字と小文字の区別を設定するgorm クエリを使用する場合、MySQ...
赤とピンク、およびそれらの 16 進コード。 #990033 #CC6699 #FF6699 #FF...
この記事では、参考までに、Navicatを使用してCSVファイルをMySQLにインポートするための具...