MySQLのグローバルロックとテーブルロックに関する詳細な理解

序文

ロックの範囲に応じて、MySQL のロックは、グローバルロック、テーブルロック、行ロックに大別されます。

行ロックについては以前の記事で触れました

1. グローバルロック

グローバルロックはデータベースインスタンス全体をロックします。 MySQL にFlush tables with read lock (FTWRL) 。

データベース全体を読み取り専用にする必要がある場合は、このコマンドを使用できます。その後、他のスレッドのデータ更新ステートメント（データの追加、削除、変更）、データ定義ステートメント（テーブルの作成、テーブル構造の変更などを含む）、および更新トランザクションのコミットステートメントがブロックされます。

1.1 グローバルロックの使用シナリオ

グローバルロックの一般的な使用シナリオは、データベース全体の論理バックアップ (mysqldump) を作成することです。再び主人と奴隷になったとき

つまり、データベース全体のすべてのテーブルを選択し、テキストとして保存します。

以前は、FTWRL を使用して他のスレッドがデータベースを更新しないようにし、データベース全体をバックアップする方法がありました。バックアッププロセス中は、ライブラリ全体が完全に読み取り専用になることに注意してください。

データベースの読み取り専用状態の危険性:

プライマリデータベースにバックアップすると、バックアップ期間中は更新が実行できず、基本的に業務を停止できます。スレーブでバックアップする場合、スレーブはバックアップ中にマスターから同期されたバイナリログを実行できないため、マスターとスレーブの間に遅延が発生します。

注:上記の論理バックアップには--single-transactionパラメータは含まれていません

グローバルロックを追加するのは良い考えではないようです。しかし、考えてみてください。バックアップをロックする必要があるのはなぜでしょうか?ロックしないとどんな問題が発生するか見てみましょう。

1.2 ロックしないことで起こる問題

例えば、携帯電話カード、パッケージ購入情報

ここには 2 つのテーブルがあります: u_acount (残高テーブル用) と u_pricing (料金パッケージテーブル用)
ステップ：
1. u_accountテーブル内のデータユーザーAの残高: 300
u_pricing テーブルユーザー A パッケージのデータ: 空

2. バックアップを開始します。バックアッププロセスでは、最初に u_account テーブルがバックアップされます。このテーブルをバックアップすると、u_account のユーザー残高は 300 になります。
3. この時、ユーザーは料金100のパッケージを購入し、食事の購入が完了し、購入が成功し、バックアップ期間中のデータがu_printパッケージテーブルに書き込まれました。
4. バックアップ完了

バックアップの結果、u_account テーブルのデータは変更されていませんが、u_pricing テーブルのデータは購入パッケージ 100 に更新されています。

このとき、ユーザー A がバックアップファイルを使用してデータを復元すると、100 ポイントを獲得できます。とても嬉しいと思いませんか?しかし、会社の利益について考えなければなりません。

つまり、ロックされていない場合、バックアップシステムによってバックアップされたデータベースは論理的な時点のものではなく、データは論理的に矛盾しています。

1.3 グローバル読み取りロック (FTWRL) が必要な理由

疑問に思う人もいるかもしれませんが、公式の論理バックアップツールは mysqldump です。 mysqldump が --single-transaction パラメータを使用すると、一貫したスナップショットビューを確保するために、データをインポートする前にトランザクションが開始されます。 MVCC のサポートのおかげで、このプロセス中にデータを正常に更新できます。

FTWRL はなぜ必要なのでしょうか?
一貫性のある読み取りは適切ですが、前提条件として、エンジンがこの分離レベルをサポートしている必要があります。たとえば、トランザクションをサポートしていない MyISAM のようなエンジンの場合、バックアッププロセス中に更新があった場合、最新のデータしか取得できず、バックアップの一貫性が失われます。このとき、FTWRL コマンドを使用する必要があります。

したがって、単一トランザクションアプローチは、すべてのテーブルに対してトランザクションエンジンを使用するライブラリにのみ適用できます。一部のテーブルがトランザクションをサポートしていないエンジンを使用している場合、バックアップは FTWRL メソッドを通じてのみ実行できます。これは、DBA がビジネス開発者に MyISAM ではなく InnoDB を使用するように依頼する理由の 1 つです。

1.4 グローバルロックの2つの方法

1. テーブルのフラッシュ書き込み読み取りロック

2. グローバル readonly=true を設定する

データベース全体を読み取り専用にする必要があるので、set global readonly=true メソッドを使用しないのはなぜでしょうか?確かに、readonly メソッドを使用してもデータベース全体を読み取り専用状態にすることができますが、いくつかの理由から、FTWRL メソッドを使用することをお勧めします。

まず、一部のシステムでは、readonly 値は、データベースがプライマリデータベースであるかバックアップデータベースであるかを判断するなどの他のロジックに使用されます。したがって、グローバル変数を変更すると影響が大きくなるため、使用することはお勧めしません。

2 番目に、例外処理メカニズムに違いがあります。 FTWRL コマンドを実行した後にクライアントが異常切断された場合、MySQL は自動的にグローバルロックを解除し、データベース全体が正常に更新できる状態に戻ります。データベース全体を読み取り専用に設定した後、クライアントで例外が発生すると、データベースは読み取り専用状態のままになります。これにより、データベース全体が長時間書き込み不可の状態になり、リスクが高くなります。

3番目に、読み取り専用はスーパーユーザー権限では無効です

注: ビジネス更新は、データの追加、削除、変更 (DML) だけでなく、フィールドの追加やテーブル構造の変更 (DDL) などの操作も含まれる場合があります。どちらの方法を使用する場合でも、データベースがグローバルにロックされると、フィールドを追加するデータベース内のすべてのテーブルがロックされます。

グローバルにロックされていない場合でも、フィールドの追加はスムーズには進みません。テーブルレベルのロックもあります。

2. テーブルレベルロック

MySQL には 2 種類のテーブルレベルロックがあります。1 つはテーブルロック、もう 1 つはメタデータロック (MDL) です。

2.1 テーブルロック

lock tables table name read; #テーブルは読み取れますが、ddl および dml で追加、削除、または変更することはできません。テーブルデータのみ読み取ることができます。

テーブルをロックテーブル名読み取り; # 読み取りも書き込みもできません

テーブルロックの構文は lock tables … read/write です。 FTWRL と同様に、ロック解除テーブルを使用してロックをアクティブに解除することも、クライアントが切断したときに自動的にロックを解除することもできます。ロックテーブル構文は、他のスレッドの読み取りと書き込みを制限するだけでなく、このスレッドの後続の操作オブジェクトも制限することに注意してください。

たとえば、スレッド A でステートメント lock tables t1 read, t2 write; が実行されると、t1 の書き込みと t2 の読み取りおよび書き込みを行う他のスレッドのステートメントはブロックされます。同時に、テーブルのロック解除を実行する前に、スレッド A は t1 の読み取りや t2 の読み取りと書き込みなどの操作のみを実行できます。 t1 への書き込みは許可されておらず、当然他のテーブルにアクセスすることはできません。

より細かい粒度のロックが登場する前は、同時実行を処理するためにテーブルロックが最も一般的に使用されていました。行ロックをサポートする InnoDB などのエンジンでは、同時実行を制御するために lock tables コマンドが使用されることは通常ありません。結局のところ、テーブル全体をロックすることは大きな影響を及ぼします。

2.2 MDLロック

テーブルレベルロックのもう 1 つのタイプは、MDL (メタデータロック) です。 MDL は明示的に使用する必要はなく、テーブルにアクセスするときに自動的に追加されます。 MDL の役割は、読み取りと書き込みの正確性を保証することです。クエリがテーブル内のデータを走査し、実行中に別のスレッドがテーブル構造を変更して列を削除すると、クエリスレッドによって取得された結果がテーブル構造と一致しなくなり、これは絶対に受け入れられないことが想像できます。

そのため、MySQL 5.5 で MDL が導入されました。テーブルを追加、削除、変更、またはクエリすると、MDL 読み取りロックが追加され、テーブルに構造変更が加えられると、MDL 書き込みロックが追加されます。

読み取りロックは相互に排他的ではないため、複数のスレッドが同時にテーブルを追加、削除、変更、および照会することができます。
読み取り/書き込みロックと書き込みロックは相互に排他的であり、テーブル構造を変更する操作のセキュリティを確保するために使用されます。したがって、2 つのスレッドが同時にテーブルにフィールドを追加する場合、一方のスレッドは、もう一方のスレッドが終了するまで待機してから実行を開始する必要があります。

MDL ロックはデフォルトで追加されますが、無視できないメカニズムです。

たとえば、次の例では、小さなテーブルにフィールドを追加するとデータベース全体がクラッシュするという落とし穴に陥る人をしばしば見かけます。

テーブルにフィールドを追加したり、フィールドを変更したり、インデックスを追加したりするには、テーブルのデータ全体をスキャンする必要があることを知っておく必要があります。大きなテーブルを操作する場合は、オンラインサービスに影響を与えないように特に注意する必要があります。実際、小さな時計でも、慎重に操作しないと問題が発生する可能性があります。テーブル t が小さなテーブルであると仮定して、次の一連の操作を見てみましょう。

mdl ロックの詳細を表示するには、 show full processlist

最初にセッション A が開始され、テーブル t に MDL 読み取りロックが追加されることがわかります。セッション B も MDL 読み取りロックを必要とするため、正常に実行できます。

その後、セッション A の MDL 読み取りロックが解除されていないため、セッション C はブロックされますが、セッション C には MDL 書き込みロックが必要なので、ブロックすることしかできません。

セッション C のみがブロックされている場合は大きな問題ではありませんが、テーブル t に対する新しい MDL 読み取りロックの後続のすべての要求もセッション C によってブロックされます。前述のように、テーブルに対するすべての操作は最初に MDL 読み取りロックを適用する必要があり、これはテーブルが完全に読み取りおよび書き込み不能になっていることを意味します。

特定のテーブルに対するクエリステートメントが頻繁に実行され、クライアントに再試行メカニズムがある場合、つまり、タイムアウト後に新しいセッションが開始され、再度要求される場合、このライブラリのスレッドはすぐにいっぱいになります。

トランザクション内の MDL ロックは、ステートメントの実行開始時に適用されますが、ステートメントの終了後すぐには解放されません。代わりに、トランザクション全体がコミットされた後に解放されます。
注: 通常、行ロックにはロックタイムアウト期間があります。ただし、MDL ロックにはタイムアウト制限がなく、トランザクションがコミットされない限りロックされたままになります。

2.2.1 このMDLロックを解決する方法

上で言いましたが、このトランザクションをコミットするかロールバックしますか?この取引を見つけるには

このトランザクションを見つけるにはどうすればよいでしょうか? information_schema.innodb_trxでトランザクションの実行時間をチェックしてください。

# 60 秒を超えるトランザクションを表示します。mysql> select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>60\G;
trx_started はトランザクションが実行された時刻を示します。#現在のシステム時刻を表示しますmysql> select now();

トランザクションの開始時刻とシステムの現在時刻を見ると、トランザクションが非常に長い時間実行されていることがわかります。

このスレッドIDを確認してください

この長いトランザクションのスレッドIDを処理する方法

まず、show full processlist; のホストフィールドを見て、データベースに接続しているユーザーを確認します。たとえば、localhost 環境にいて、commit または rollback を入力します。localhost 環境でない場合は、プログラムが接続されているため、強制終了する必要があります。

2.2.2 私に起こった興味深い出来事

前回、DBA から、マスタースレーブ間の遅延が大きい問題を解決する方法を尋ねられました。

遅延をどのように解決したかお聞きしました。マスタースレーブ遅延の具体的な原因はご存知ですか?

マルチスレッドは有効になっているが、遅延は依然として非常に大きく、マルチスレッドは基本的に使用されていないとのことでした。

マスタースレーブ間の遅延はマルチスレッドレプリケーションを有効にすることで解決する必要があることをどうやって知ったのかと尋ねたところ、インターネットのブログで誰かがそう言っていたと聞き、血を吐きそうになりました。

その後、マスタースレーブ遅延が正常だったときにどのような操作が実行されたかを尋ねたところ、alter table 構造を変更したとのことでした。

次に、MDL ロックが原因かどうかを確認するように依頼しました。 show full processlist依頼したところ、MDL ロックであることが判明しました。

次に、長いトランザクションを見つけるように指示し、見つけたら、この長いトランザクションの操作を強制終了できるかどうかを開発者と話し合ってください。

注:これは私が実際に遭遇した問題であり、誰かが私にこの質問をしました。まず、この結果の原因は何だったのか、事前に何をしたのかを知り、問題を解決する必要があります。最も根本的には、原因を知り、次回はそれを避ける必要があります。

また、オンライン環境、システムバージョン、アプリケーションバージョン、発生した問題を盲目的に信じないでください。

2.3 小さなテーブルにフィールドを安全に追加するにはどうすればよいですか?

まず、長いトランザクションの問題を解決する必要があります。トランザクションがコミットされない場合、MDL ロックが占有されます。 MySQL information_schema ライブラリの innodb_trx テーブルでは、現在実行中のトランザクションを確認できます。 DDL 変更を実行するテーブルで長いトランザクションが進行中の場合は、まず DDL を一時停止するか、長いトランザクションを強制終了することを検討してください。そのため、オフピーク時に DDL の変更を行う必要があります。もちろん、具体的にどの DDL を使用するかを検討し、公式のオンライン DDL を参照する必要もあります。

2.4 オンラインDDLプロセス