mysql MDLメタデータロックの詳細な分析

序文：

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 ステートメント操作が別のトランザクションとして存在することを確認し、メタデータ排他ロックが確実に解放されるようにします)。

注意: トランザクションをサポートする InnoDB エンジンテーブルとトランザクションをサポートしない MyISAM エンジンテーブルの両方で、メタデータロック待機現象が発生します。メタデータロック待機現象が発生すると、テーブルへの後続のすべてのアクセスがこの待機中にブロックされ、接続が蓄積され、ビジネスに影響が生じます。

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 ロックを記録するには、metadata_locks テーブルを有効にします。
ブロックされた側がアクティブに停止するように、パラメータ lock_wait_timeout を小さい値に設定します。
トランザクションを標準化された方法で使用し、トランザクションをタイムリーに送信し、大規模なトランザクションの使用を避けます。
監視とアラームを強化して、MDL ロックをタイムリーに検出します。
DDL 操作とバックアップ操作は、業務のオフピーク時間帯に実行されます。
クエリのためにトランザクションを開くために使用するツールを少なくし、グラフィカルツールを適切なタイミングで閉じます。

要約:

この記事では、主に MDL ロックを 3 つの側面から説明します。まず、MDL ロックの原因と機能を紹介します。次に、MDL ロックをシミュレートし、検索と解決の方法を示します。最後に、MDL ロックを回避するための提案をいくつか示します。実際、MDL ロックは DB の操作とメンテナンス中に頻繁に発生します。MDL ロックは脅威ではなく、データベースオブジェクトを保護し、データの一貫性を確保するためにのみ使用されます。この記事を読んで、MDL ロックについてより深く理解していただければ幸いです。

上記はMySQL MDLメタデータロックの詳細分析です。MySQL MDLメタデータロックの詳細については、123WORDPRESS.COMの他の関連記事に注目してください。

以下もご興味があるかもしれません: