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

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

序文

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

グローバルロック

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

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

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

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

セッション1セッション2
読み取りロック付きでテーブルをフラッシュします。
テスト制限1から*を選択します。
(正常戻り結果)
テスト制限1から*を選択します。
(正常戻り結果)
テスト(a,b,c)に値(6,6,6)を挿入します。
(エラー)
テスト(a,b,c)に値(8,8,8)を挿入します。# sql1
(ブロック)
テーブルのロックを解除します。
テスト(a,b,c)に値(8,8,8)を挿入します。# sql1
(セッション1のロックが解除されると、sql1はすぐに正常に実行されます)

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

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

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

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

テーブルロック

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

テーブルロック

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

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

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


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

テーブル読み取りロック

セッション1セッション2
テーブルのロックテスト読み取り。
テストlimit1から*を選択します。
(正常戻り結果)
テスト制限1から*を選択します。
(正常戻り結果)
テスト(a,b,c)に値(6,6,6)を挿入します。
(エラー)
test(a,b,c) に値(8,8,8) を挿入します。# sql1
(ブロック)
テーブルのロックを解除します。
test(a,b,c) に値(8,8,8) を挿入します。# sql1
(セッション1がロック解除されると、sql1はすぐに正常に書き込まれます)

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

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

テーブル書き込みロック

セッション1セッション2
テーブルをロックして書き込みをテストします。
テストlimi1から*を選択します。
(正常戻り結果)
テスト制限 1 から * を選択します。# sql1
(ブロック)
テーブルのロックを解除します。
テスト制限から*を選択; # sql1
(セッション1のロックが解除されると、sql1はすぐに結果を返します)
テーブルをロックして書き込みをテストします。
テスト(a,b,c)に値(6,6,6)を挿入します。
(挿入成功)
テスト(a,b,c)に値(8,8,8)を挿入します。# sql 2
(ブロック)
テーブルのロックを解除します。
テスト(a,b,c)に値(8,8,8)を挿入します。# sql2
(セッション1のロックが解除されると、sql2はすぐに正常に実行されます)

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

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

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

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

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

セッション1セッション2セッション3セッション4
始める;
テスト lmi1 から * を選択します。
(正常戻り結果)
テスト制限1から*を選択します。
(正常戻り結果)
テーブルテストを変更して d int を追加します。
(ブロック)
テスト制限1から*を選択します。
(ブロック)

セッション 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 をよろしくお願いいたします。

以下もご興味があるかもしれません:
  • MySQL はどのようにしてマルチバージョンの同時実行性を実現するのでしょうか?
  • MySQLフィルタリングレプリケーションのアイデアの詳細な説明
  • MySQL 外部キー (FOREIGN KEY) の使用例の詳細な説明
  • MySQL のストアド プロシージャを使用して 100 万件のレコードをすばやく生成する方法
  • Pythonインターフェース自動化はpymysqlデータベース操作プロセスを簡単に分析します
  • MySQL トランザクション制御フローと ACID 特性
  • MySQLはストアドプロシージャを使用して数百万のデータを素早く追加します。サンプルコード
  • MySQL で重複時間を削除して時間差を計算する実装
  • MySQL データベースでは、datetime、bigint、timestamp を使用して時間の選択を表します。時間を保存するのに最も効率的なのはどれですか?
  • Redo ログと Undo ログに基づく MySQL クラッシュ回復の分析

<<:  Baidu Union 環境での広告スキル (グラフィック チュートリアル)

>>:  CSS3 の新しいレイアウト: flex の詳細な説明

推薦する

docker-compose.yml ファイル内の一般的なテンプレート コマンドの詳細な説明

注意: docker-compose.yml ファイルを書き込むときは、すべてのコロン (:) とダ...

クリック範囲を拡大する入力チェックボックスを実装する方法

XML/HTML コードコンテンツをクリップボードにコピー< div style = &quo...

nginx プロキシでの複数の 302 応答の解決策 (nginx Follow 302)

proxy_intercept_errors と recursive_error_pages を使...

Win10 64 ビットで圧縮パッケージを使用して最新の MySQL 8.0.18 をインストールするチュートリアル (画像とテキスト付き)

WIN10 64ビットに最新のMySQL8.0.18をインストールダウンロード公式サイトから最新バ...

Ubuntu ブート自動起動サービス設定

Ubuntu でサービスを作成し、自動的に起動する方法: 1. [/lib/systemd/syst...

新しいウィンドウで開くジャンプメニュー、window.open の使い方の紹介

コードをコピーコードは次のとおりです。 <前> <div> <sele...

vue+iviewのメニューとタブの連携方法

Vue+iview メニューとタブのリンク現在、vue+iview を使用してバックエンド管理システ...

react+antd.3x は IP 入力ボックスを実装します

この記事では、IP入力ボックスを実装するための react+antd.3x の具体的なコードを参考ま...

DeepinでPyenvをインストールする手順

序文これまでは、/bin/ ディレクトリのソフトリンクを変更して Python のバージョンを切り替...

MySQLトランザクションの特徴と分離レベルについてお話ししましょう

インターネットにはすでにこの種の記事が溢れていますが、私がこれをまだ書いている理由は単純です。それは...

JavaScript の高度なクロージャの説明

目次1. 閉鎖の概念追加の知識ポイント: 2. 閉鎖の役割: 3. 閉鎖例3.1 liをクリックする...

JavaScriptエラーキャプチャの詳細な説明

目次1. 基本的な使い方とロジック2. 特徴3. エラーオブジェクト4. キャッチアンドスロー戦略の...

Ubuntu 上の MySQL における中国語文字化け問題の解決方法

問題を見つける最近 Django を学習しているのですが、MySQL データと組み合わせてデータを挿...

Springboot プロジェクトに動的にパラメータを渡すための Docker の実装方法

背景最近、Docker 初心者の友人から、毎回プロジェクト構成ファイルにハードコーディングしてサービ...

LinuxサーバーにVueプロジェクトをデプロイする

ケース1 vue-cliはvue3プロジェクトをビルドし、プロジェクトをLinuxサーバーにアップロ...