MySQL ロック関連知識のまとめ

MySQL のロック

ロックは、並行環境におけるリソースの競合を解決する手段です。その中でも、楽観的並行性制御、悲観的並行性制御、マルチバージョン並行性制御は、データベースの並行性制御に使用される主な技術的手段です（詳細は前回の記事を参照）。MySQL のロックは、悲観的並行性制御です。

MySQL には多くの種類のロックがあり、次のように分類できます。

読み書きによって

データベースの読み取りと書き込みの観点から、データベースロックは次のカテゴリに分類できます。

排他ロック: 排他ロック、X ロック、書き込みロックとも呼ばれます。 X ロックは他のロックと互換性がありません。トランザクションがデータにロックを追加している限り、他のトランザクションはデータに X を設定することはできません。同時に、トランザクションが X ロックを設定した後は、他のトランザクションは他のロックを追加できません。排他ロックを取得したトランザクションのみが、データの読み取りと変更を行うことができます。
共有ロック: 読み取りロックまたは S ロックとも呼ばれます。 SロックはSロックと互換性があり、同時に設置できます。
アップデートロック: U ロックとも呼ばれます。 S ロックの追加は許可されますが、他のトランザクションが U ロックまたは X ロックを適用することはできません。読み取りデータを更新する場合、S ロックは X ロックにアップグレードされます。 U ロックの利点は、トランザクション A が他のトランザクションをブロックせずにデータを読み取ることができ、同時にトランザクション A が最後にデータを読み取ってからデータが変更されていないことを保証できることです。したがって、X ロックと S ロック間の競合を減らし、S ロックを X ロックにアップグレードすることによって発生するデッドロックを回避できます。 MySQL は U ロックをサポートしておらず、SQL Server のみが U ロックをサポートしていることに注意してください。

互換性マトリックスは以下のとおりです（+は互換性あり、-は互換性なしを表します）

右側は追加されたロックです	バツ	ス	あなた
バツ	-	-	-
ス	-	+	+
あなた	-	+	-

粒度別

MySQL はさまざまなレベルのロックをサポートしており、ロックされたデータの範囲も異なります。これは、ロックの粒度と呼ばれるものです。 MySQL には、行レベルロック、ページレベルロック、テーブルレベルロックの 3 つのロックレベルがあります。異なるストレージエンジンは、異なるロック粒度をサポートします。たとえば、MyISAM および MEMORY ストレージエンジンはテーブルレベルのロックを使用し、ページレベルのロックは BDB ストレージエンジンでのみサポートされ、InnoDB ストレージエンジンは行レベルのロックとテーブルレベルのロックをサポートします。デフォルトでは行レベルのロックが使用されます。

特徴

テーブルレベルのロック: オーバーヘッドが低く、ロックが高速で、デッドロックがなく、ロックの粒度が大きく、ロック競合の可能性が最も高く、同時実行性が最も低い。データベースエンジンは、必要なすべてのロックを常に一度に取得し、テーブルロックを常に同じ順序で取得することで、デッドロックを回避します。
行レベルロック: オーバーヘッドが高く、ロックが遅く、デッドロックが発生する可能性があります。ロックの粒度は最小で、ロック競合の可能性は最も低く、同時実行性は最も高くなります。行ロックは常に増分的に取得されるため、デッドロックが発生する可能性があります。
ページロック: オーバーヘッドとロック時間はテーブルロックと行ロックの中間です。デッドロックが発生する可能性があります。ロックの粒度はテーブルロックと行ロックの中間で、同時実行性は平均的です。

以下では、行ロックとテーブルロックについて詳しく説明します。ページロックはほとんど使用されないため、ここでは説明しません。

行ロック

行ごとにデータをロックします。 InnoDB の行ロックは、インデックス上のインデックス項目をロックすることによって実装されます。InnoDB にはクラスター化インデックスが必要であり、行ロックは最終的にクラスター化インデックスに適用されます。非クラスター化インデックスを介してクエリを実行する場合、最初に非クラスター化インデックスがロックされ、次にクラスター化インデックスがロックされます。 where ステートメントにクラスター化インデックスとセカンダリインデックスの両方が含まれている場合、クラスター化インデックスが最初にロックされ、次にセカンダリインデックスがロックされます。ロックは段階的に行われるため、デッドロックが発生する可能性があります。

MySQL の行ロックは、S ロックと X ロックをさらに正確に細分化することで、行ロックの粒度を細かくし、競合を減らします。これが「精密モード」と呼ばれる互換性マトリックスです。 (このマトリックスは公式ドキュメントには記載されていませんが、MySQL lock0lock.c:lock_rec_has_to_wait ソースコードから推測されたものです。)

行ロック互換性マトリックス

ギャップロック: ギャップ、つまり最初にオープンした間隔と 2 番目にオープンした間隔 (a、b) のみをロックし、インデックスのギャップをロックして、他のトランザクションがデータを挿入できないようにします。
レコードロック: レコード、特定のレコード行のみをロックします。
次のキーロック: レコードとギャップを同時にロックし、最初の間隔を開いて最後の間隔を閉じます (a、b)。
挿入意図ロック: 挿入時に使用するロック。コードでは、インテントロックを挿入すると、実際に GAP ロックに LOCK_INSERT_INTENTION タグが追加されます。

右側には追加されたロック（互換性がある場合は+、互換性がない場合は-）	グ	R	いいえ	私
グ	+	+	+	+
R	+	–	–	+
いいえ	+	–	–	+
私	–	+	–	+

S ロックと S ロックは完全に互換性があるため、互換性を判断する際に正確なモードを比較する必要はありません。 S,X と X,X 間で使用される正確なモード検出。このマトリックスからはいくつかの特徴がわかります。

INSERT 操作間で競合は発生しません。つまり、あなたがあなたのものを挿入すれば、私も私のものを挿入します。
GAP と Next-Key は挿入を防止します。挿入されたデータは間隔内に正確に収まるため、挿入は許可されません。
GAP、レコード、およびNext-Keyは競合しません
レコード、レコード、および次のキーの間に競合があります。
既存の挿入ロックは、追加されるロックをブロックしません。
ギャップロック (S または X) は挿入操作のみをブロックします。

注記

レコードロックの場合、列は一意のインデックス列または主キー列である必要があり、クエリステートメントは "=" などの完全一致である必要があります。そうでない場合、レコードロックは一時的なキーロックに退化します。
ギャップロックと隣接キーロックは、一意でないインデックスに基づいています。ギャップロックと隣接キーロックは、一意のインデックス列には存在しません。

テーブルロックとテーブルのロックに関する誤解

InnoDB は、インデックス条件を通じてデータが正しく取得された (インデックス障害がない) 場合にのみ行レベルのロックを使用します。それ以外の場合、InnoDB はテーブル内のすべてのレコードをロックします。つまり、テーブル全体をロックします。ここではテーブル全体のロックについて説明していますが、Innodb はテーブルをロックするためにテーブルロックを使用しないことに注意してください。代わりに、以下で紹介する Next-Key Lock を使用してテーブル全体をロックします。インターネット上の多くの記述では、テーブルロックを使用する必要があると書かれていますが、実際にはそうではありません。次の例からこのことがわかります。

次のデータ (MySQL8) があると仮定します。

mysql> ユーザーから * を選択します。
+----+------+-----+
| ID | 名前 | 年齢 |
+----+------+-----+
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 4 | 1 |
| 5 | 1 |
+----+------+-----+

方法1:

テーブルロックを使用してテーブルをロックし、エンジンの状態を確認します

mysql> 開始します。
クエリは正常、影響を受けた行は 0 行 (0.00 秒)

mysql> ユーザーが書き込むテーブルをロックします。
クエリは正常、影響を受けた行は 0 行 (0.00 秒)

mysql> エンジン innodb ステータスを表示\G
...
------------
取引
------------
Trx ID カウンター 4863
トランザクションの n:o < 4862 のパージが完了しました。n:o < 0 の状態を元に戻します: 実行中ですがアイドル状態です
履歴リストの長さ 911
各セッションのトランザクションのリスト:
---トランザクション 281479760456232、開始されていません
使用中の mysql テーブル 1、ロックされている 1 ################## ロック構造体 0、ヒープ サイズ 1136、行ロック 0 であることに注意してください
...

次に、インデックスされていないフィールドをクエリしてロックし、エンジンのステータスを確認します。

## 最後のテーブルのロックを解除します。 lock firstmysql> unlock tables;
クエリは正常、影響を受けた行は 0 行 (0.00 秒)

mysql> コミット;
クエリは正常、影響を受けた行は 0 行 (0.00 秒)

mysql> 開始します。
クエリは正常、影響を受けた行は 0 行 (0.00 秒)

mysql> select * from users where name = 'a' for update;

mysql> エンジン innodb ステータスを表示\G
...
------------
取引
------------
Trx ID カウンター 4864
トランザクションの n:o < 4862 のパージが完了しました。n:o < 0 の状態を元に戻します: 実行中ですがアイドル状態です
履歴リストの長さ 911
各セッションのトランザクションのリスト:
---トランザクション 4863、アクティブ 37 秒
2 つのロック構造体、ヒープ サイズ 1136、6 行ロック #################ここに注意してください...

次に、ID 2、3、4 のデータを削除し、インデックスされていないフィールドをクエリしてロックし、エンジンのステータスを確認します。

mysql> IDが(2,3,4)であるユーザーから削除します。
クエリは正常、3 行が影響を受けました (0.00 秒)

mysql> コミット;
クエリは正常、影響を受けた行は 0 行 (0.00 秒)

mysql> 開始します。
クエリは正常、影響を受けた行は 0 行 (0.00 秒)

mysql> select * from users where name = 'a' for update;

mysql> エンジン innodb ステータスを表示\G
...
------------
取引
------------
トランザクションIDカウンタ 4870
トランザクションの n:o < 4869 のパージが完了しました。n:o < 0 の状態を元に戻します: 実行中ですがアイドル状態です
履歴リストの長さ 914
各セッションのトランザクションのリスト:
---トランザクション 4869、アクティブ 9 秒
2 つのロック構造体、ヒープ サイズ 1136、3 つの行ロック #################ここに注意してください...

ここでテーブルロックを使用することは、インデックスを使用して特定の行をロックすることはできないため、テーブル全体をロックすることとは異なることがわかります。 2 番目と 3 番目の操作では、ロックされた行も異なります。これは、2 つの操作間のギャップの数が異なるためです。したがって、Next-Key Lock ではなく、テーブルロックが使用されていることがわかります。最初は (-∞,1],(1,2],(2,3],(3,4],(4,5],(5,∞] がロックされ、2 回目は (-∞,1],(1,5],(5,∞] がロックされました。

方法2:

また、次のステートメントを使用してロック情報を表示することもできます。行ロックが使用されていること、間隔 (データを挿入できない) とレコードがロックされているため、Next-Key Lock であることもわかります。

mysql> select ENGINE_TRANSACTION_ID,LOCK_TYPE,LOCK_MODE from performance_schema.data_locks where ENGINE_TRANSACTION_ID in (トランザクション ID) ;
+----------------------+-----------+------------+
| ENGINE_TRANSACTION_ID | LOCK_TYPE | LOCK_MODE |
+----------------------+-----------+------------+
| 4889 | 表 | IX |
| 4889 | レコード | X |
| 4889 | レコード | X |
| 4889 | レコード | X |
+----------------------+-----------+------------+
セット内の行数は 10 です (0.00 秒)

LOCK_TYPE: InnoDBの場合、オプションの値はRECORD（行ロック）、TABLE（テーブルロック）です。

LOCK_MODE: InnoDBの場合、可能な値はS[,GAP]、X[,GAP]、IS[,GAP]、IX[,GAP]、AUTO_INC、およびUNKNOWNです。 AUTO_INC と UNKNOWN を除く他のすべてのロックモードには、GAP ロック (存在する場合) が含まれます。

詳細については、MySQL のドキュメント (https://dev.mysql.com/doc/ref...) を参照してください。

テーブルロック

テーブル全体を直接ロックし、テーブル内のすべてのレコードに影響します。テーブル読み取りロックとテーブル書き込みロックの互換性については、上記の分析を参照してください。

MySQL には、テーブル読み取りロックとテーブル書き込みロックに加えて、異なる粒度のロックの互換性判断を解決するために存在する、インテンションロックという特別なテーブルロックもあります。

意図ロック

ロックの粒度が異なるため、テーブルロックの範囲が行ロックの範囲をカバーするため、テーブルロックと行ロックが競合します。たとえば、トランザクション A がテーブル内のデータ行に行ロックを追加し、次にトランザクション B がテーブルロックを追加しようとします。通常、競合が発生するはずです。行ロックのみの場合、競合があるかどうかを判断するためにすべてのデータ行を走査する必要があり、これはあまり効率的ではありません。そのため、意図的なテーブルロックがあります。

インテンションロックの主な目的は、行ロックとテーブルロックを共存させることです。行ロックを適用する前に、トランザクションはまずテーブルのインテンションロックを適用し、成功した後に行ロックを適用する必要があります。注: 意図ロックを申請するアクションはデータベースによって完了されるため、開発者が申請する必要はありません。

インテンションロックはテーブルレベルのロックですが、トランザクションがテーブル全体ではなくレコードの行を読み取ったり書き込んだりしていることを示します。したがって、インテンションロック間で競合は発生しません。実際の競合は行ロックを追加するときにチェックされます。

意図ロックは、意図読み取りロック (IS) と意図書き込みロック (IX) に分けられます。