MySQL グローバルロックとテーブルレベルロックの具体的な使用法

序文

実際の企業開発環境で MySQL を使用する場合、MySQL を使用するのは私だけではありません。チームが明示的に MySQL を使用したり、ビジネスが暗黙的に MySQL を使用したりします。そのため、複数のユーザーまたはクライアントが接続して使用する場合、同時データ アクセスの一貫性をどのように確保するかという質問を検討する必要があります。この記事では、MySQL トランザクション分離レベルではなく、MySQL ロックについて説明します。

グローバルロック

MySQL のグローバル ロックは、開いているすべてのテーブルを閉じ、すべてのテーブルを読み取り専用にします。コマンドは次のとおりです。

# グローバルロック（FTWRLと呼ばれる）
読み取りロック付きでテーブルをフラッシュします。

# ロック解除コマンド UNLOCK TABLES;

FTWRL での実験: (以下の実験はすべて MySQL 8.0.22 で完了しました)

上記の実験から、FTWRL を実行すると、すべてのテーブルが読み取り専用になり、その他の更新操作がブロックされることがわかります。

グローバル ロックの主な機能は、データベース全体の論理バックアップを作成すること、つまり、データベース内の各テーブルを選択してテキストとして保存することです。

バックアップ処理中は、データベース全体が読み取り専用状態となり、リスクが極めて高くなります。マスター データベースでバックアップを実行すると、すべてのビジネス テーブルでデータを変更できなくなります。スレーブ データベースでバックアップを実行すると、スレーブ データベースはマスター データベースから送信された binlog を実行できず、マスター データベースとスレーブ データベース間で遅延が発生します。

幸いなことに、InnoDB ストレージ エンジンはトランザクションをサポートしており、mysqldump にはパラメータ single-transaction があり、トランザクション内で一貫性のあるスナップショットを作成し、すべてのテーブルをバックアップできます。このパラメータを使用すると、バックアップ中にデータが変更される可能性があるため、通常の開発では InnoDB ストレージ エンジンを使用することをお勧めします。

テーブルロック

テーブル レベル ロックには、テーブル ロックとメタデータ ロックの 2 種類があります。

テーブルロック

テーブル ロックは、テーブル読み取りロックとテーブル書き込みロックに分けられます。MySQL のコマンドは次のとおりです。

# テーブル読み取りロック lock tables test read;

# テーブル書き込みロック lock tables test write;

次に、テーブル読み取りロックとテーブル書き込みロックの違いを実験を通して確認してみましょう。

テーブル読み取りロック

セッション 1 セッションにテーブル読み取りロックが追加されます。この時点で、セッション 1 とセッション 2 は両方ともデータを正常に読み取ることができますが、セッション 1 はデータの書き込み時にエラーを報告し、セッション 2 はデータの書き込み時にブロックされます。セッション 2 は、セッション 1 がロック解除された後にのみ、データを正常に書き込むことができます。

この実験から、テーブルがロックされた後、このスレッドと他のスレッドはデータを読み取ることができ、このスレッドはデータを書き込むときにエラーを報告し、他のスレッドはデータを書き込むときにブロックされることがわかります。

テーブル書き込みロック

上記の実験から、テーブルがロックされた後、現在のスレッドは読み取りおよび書き込み操作を実行でき、他のスレッドの読み取りおよび書き込み操作はブロックされることがわかります。

メタデータ ロック (MDL ロック)

MySQLでは、データベースDDLはトランザクションの範囲に含まれません。セッション1でデータ行を選択すると、セッション2ではこのテーブルにxxxという列が追加されます。このとき、トランザクションの特性が破壊されたり、binlogの順序が乱れたりするなどのバグが発生することがあります（同様のバグはMySQL公式サイトでも発表されているので、興味があれば調べてみてください）。

上記の問題を解決するために、MySQL 5.5.3 でメタデータ ロックが導入されました。MDL ロックは明示的に使用する必要はありません。MySQL はデフォルトでそれを追加します。その機能は、データベースの読み取りと書き込みの正確性を保証することです。以下では、MDL を使用してメタデータ ロックを表します。

テーブルを追加、削除、クエリ、または変更すると、MDL 読み取りロックがデフォルトで追加されます。テーブルのテーブル構造を変更すると、MDL 書き込みロックがデフォルトで追加されます。

セッション 1 が最初にテストをクエリすると、MDL 読み取りロックが取得され、正常にデータをクエリできます。次に、セッション 2 はデータを照会するときに MDL 読み取りロックも取得するため、競合は発生せず、データを正常に照会できます。

しかし、セッション 3 になると、MDL 書き込みロックを取得する必要があります。このとき、セッション 1 の MDL 読み取りロックが解除されていないため、ブロックされます。その後、セッション 4 にも MDL 読み取りロックが必要になりますが、セッション 3 がブロックされているため、セッション 4 もブロックされます。

これがオンライン ビジネス テーブルの場合、このシナリオにより後続の操作がすべて無効になり、テーブルは読み取りおよび書き込み不能になります。クライアントが MySQL 再試行メカニズムを構成すると、タイムアウトが発生したときにセッションが再確立され、再度要求が行われ、新しいスレッドが継続的に追加されるため、MySQL がクラッシュします。

上記の例から、ステートメントの実行時に MDL ロックがデフォルトで追加されますが、ステートメントの実行後には解除されないことがわかります。MDL ロックは、トランザクション全体がコミットされた後にのみ解除されます。

したがって、開発者としては、遅いクエリを避け、トランザクションがタイムリーに送信されるよう保証し、大規模なトランザクションを避けるなどの対策を講じる必要があります。また、DBA としては、ビジネスのピーク時間帯に DDL 操作を実行しないよう努める必要があります。

要約する

• グローバル ロックにより、すべてのテーブルが読み取り専用になり、すべての更新操作がブロックされます。
• テーブル読み取りロックは、このスレッドと他のスレッドの両方がテーブルを読み取ることができることを意味します。このスレッドが書き込みを行うと、エラーが報告され、他のスレッドはブロックされます。
• テーブル書き込みロックにより、このスレッドは読み取りと書き込みが可能になりますが、他のスレッドは読み取りと書き込みがブロックされます。
• トランザクションとDDLの同時実行によって発生するバグを解決するためにMDLロックを導入

参考文献

• MySQL 徹底解説、第 2 版: 20.3.8 テーブル ロックを使用するタイミング
• 「MySQL 実践 45 講義」Lin Xiaobin 著
  

これで、MySQL のグローバル ロックとテーブル レベル ロックの具体的な使用法に関するこの記事は終了です。MySQL のグローバル ロックとテーブル レベル ロックの詳細については、123WORDPRESS.COM の以前の記事を検索するか、以下の関連記事を引き続き参照してください。今後とも 123WORDPRESS.COM をよろしくお願いいたします。