MySQLのロックについて理解しておくべきこと

1. はじめに

MySQL ロックは、その範囲に応じて、グローバル ロック、テーブル ロック、行ロックに分けられます。行ロックはデータベース エンジンによって実装されます。すべてのエンジンが行ロックを提供しているわけではありません。MyISAM は行ロックをサポートしていないため、この記事では InnoDB エンジンを例に行ロックを紹介します。

2. グローバルロック

MySQL は、データベース インスタンス全体をロックするグローバル ロックを提供します。

文法：

読み取りロック付きテーブルのフラッシュ

このステートメントは、通常、バックアップに使用されます。このステートメントを実行すると、データベース内の開いているテーブルがすべて閉じられ、データベース内のすべてのテーブルがグローバル読み取りロックでロックされます。同時に、他のスレッドの更新ステートメント (追加、削除、変更)、データ定義ステートメント (テーブルの作成、テーブル構造の変更)、および更新トランザクションの送信がブロックされます。

MySQL 8.0 以降では、MySQL はバックアップにバックアップ ロックを直接使用できます。

声明：

バックアップのためにインスタンスをロックするインスタンスのロックを解除する

このロックの範囲は広く、 REPAIR TABLE TRUNCATE TABLE, OPTIMIZE TABLE操作やアカウント管理など、ファイルの作成、名前変更、削除を防止します。もちろん、これらの操作はメモリ一時テーブルに対して実行できます。メモリ テーブルにはなぜこれらの制限が適用されないのでしょうか?メモリテーブルはバックアップする必要がないため、これらの条件を満たす必要はありません。

3. テーブルロック

Mysql のテーブル レベル ロックは、メタデータ ロック (MDL) とテーブル ロックの 2 つのカテゴリに分けられます。

メタデータ ロック (MDL) は明示的に使用する必要はなく、テーブルにアクセスすると自動的に取得されます。この機能をサポートするには、MySQL バージョン 5.5 以上が必要です。テーブルを追加、削除、変更、またはチェックすると、テーブルに MDL 読み取りロックが追加されます。テーブル構造が変更されると、MDL 書き込みロックが追加されます。 MDL ロックにはいくつかのルールがあります:

読み取りロックは相互に排他的ではないため、複数のスレッドが同じテーブルを追加、削除、変更、および照会できます。読み取り/書き込みロックと書き込みロックは相互に排他的です。テーブル構造の変更のセキュリティを確保するために、複数のスレッドが同じテーブルにフィールドを追加するなどのテーブル構造操作を実行する必要がある場合は、シリアル化され、ロック待機が必要になります。 MDL の書き込みロック優先度は MDL の読み取りロック優先度よりも高くなりますが、max_write_lock_count システム変数を設定することでこの状況を変更できます。書き込みロック要求がこの変数で設定された数を超えると、MDL 読み取りロック優先度は MDL 書き込みロック優先度よりも高くなります。 (デフォルトではこの数値は大きいため、書き込みロックの優先度が下がることを心配する必要はありません。) MDL ロックは、トランザクションが終了した後にのみ解除する必要があります。

したがって、データベース テーブル構造を操作するときは、長いトランザクションを使用しないように注意する必要があります。これは具体的に何を意味するのでしょうか。例を挙げてみましょう:

上の図は、4 つのセッションによるステートメントの実行を示しています。まず、SessionA はトランザクションを開始しますが、コミットしません。次に、SessionB はクエリを実行します。これらは MDL 読み取りロックを取得するため、互いに影響を及ぼさず、正常に実行できます。SessionC はフィールドを追加します。MDL の書き込みと読み取りは相互に排他的であるため、SessionC はブロックされます。次に、SessionD がクエリ ステートメントの実行を開始します。SessionC がブロックされているため、SessionD もブロックされます。したがって、シミュレートした SessionA のトランザクションは長いトランザクションであり、その後テーブル構造が変更され、テーブルに対する後続のすべての読み取りおよび書き込み操作が実行不可能になります。したがって、実際のシナリオでは、ビジネス リクエストが頻繁に発生する場合、テーブル構造の変更によってライブラリのスレッドがブロックされる可能性があります。

テーブル ロックの構文は次のとおりです。

LOCK TABLES tbl_name [[AS] alias] lock_type [, tbl_name [[AS] alias] lock_type] ... lock_type: { READ [LOCAL] | [LOW_PRIORITY] WRITE} UNLOCK TABLES

テーブル ロックは、読み取りロックと書き込みロックに分けられます。読み取りロックは相互に排他的ではありませんが、読み取りロックを取得した後はデータを書き込むことはできません。読み取りロックを取得していない他のセッションでもテーブルを読み取ることができるため、読み取りロックの目的はテーブルへの書き込みを制限することです。テーブルが読み取りロックによってロックされている場合、挿入ステートメントを実行するとエラーが報告されます。エラーは次のとおりです。

1099 - テーブル 'XXXX' は読み取りロックでロックされており、更新できません

書き込みロックが取得されると、テーブルは読み取りおよび書き込み可能になります。書き込みロックは相互に排他的です。セッションがテーブルの書き込みロックを取得すると、書き込みロックが解除されるまで他のセッションはテーブルにアクセスできません。

テーブルのロックは、 unlock tablesを使用して解除することも、クライアント ポートによって自動的にロック解除することもできます。テーブルのlock tablesテーブルを排他的にロックします。これにより、他のスレッドによるテーブルの読み取りと書き込みが制限されるだけでなく、このスレッドの次の操作オブジェクトも制限されます。

4. 行ロック（InnoDB）

MySQL の行ロックはエンジン レベルで実装されているため、ここでは InnoDB エンジンでの行ロックについて説明します。以下では、InnoDB での一般的な行ロックをいくつか詳しく紹介します。

4.1 共有ロック

共有ロックを使用すると、トランザクションはロックを取得した後に読み取り操作を実行できます。共有ロックは相互に排他的ではありません。1 つのトランザクションが共有ロックを取得した後、別のトランザクションも共有ロックを取得できます。共有ロックを取得した後は書き込み操作は実行できません。

4.2 排他ロック

排他ロックを使用すると、トランザクションはロックを取得した後、行を更新または削除できます。名前が示すように、排他ロックは相互に排他的です。トランザクションが排他ロックを取得した後、ロックが解除されるまで他のトランザクションは排他ロックを取得できません。

4.3 意図ロック

InnoDB は複数の粒度のロックをサポートしており、行ロックとテーブルロックが共存できます。ここで言及したインテンション ロックは実際にはテーブル レベルのロックですが、単独では存在しないため行ロックに入れました。その出現には必ず行ロック (共有ロックまたは排他ロック) が伴います。その主な目的は、テーブル内の行がロックされるか、ロックされていることを示すことです。

インテンション ロックは、行ロックとの組み合わせに応じて次のタイプに分類できます。

• 意図的な排他ロック: テーブル内の特定の行に対して排他ロックが取得されることを示します。
• 意図的な共有ロック: テーブル内の特定の行に対して共有ロックが取得されることを示します。

意図ロックの取得は行ロックの取得の前に行う必要があります。つまり、共有ロックの前に共有意図ロックを取得する必要があり、排他ロックについても同様です。

では、この意図ロックは具体的に何をするのでしょうか?

これを説明する前に、インテンション ロックと行ロックの互換性関係を見てみましょう。