InnoDB ロック (レコード、ギャップ、Next-Key ロック) の詳細な説明

レコード ロックは、単一のインデックス レコードをロックします。レコード ロックは常にインデックスをロックし、レコード自体はロックしません。テーブルにインデックスがない場合でも、InnoDB はバックグラウンドで非表示のクラスター化主キー インデックスを作成し、この非表示のクラスター化主キー インデックスがロックされます。したがって、SQL ステートメントがインデックスに従わない場合は、各クラスター化インデックスの後ろに X ロックが追加されます。これはテーブル ロックに似ていますが、原則としてテーブル ロックとはまったく異なります。

ギャップ ロックは、インデックス レコード間のギャップをロックするか、インデックス レコードの前または後をロックしますが、インデックス レコード自体はロックしません。ギャップ ロック メカニズムは、主に繰り返し読み取りモードでのファントム リード問題を解決するために使用されます。以下は、ファントム リードと、ギャップ ロックがファントム リードを解決する方法のデモンストレーションです。この分野に関して、まずいくつかの定義を述べましょう

スナップショットの読み取り:

共有モードまたは更新でのロックなしの単純な選択操作では、スナップショットの読み取りによってロックは追加されず、MySQL の一貫した非ロック読み取りメカニズムが存在するため、スナップショットの読み取りはブロックされません。ただし、トランザクション分離レベルがSERIALIZABLE の場合、スナップショット読み取りも共有の次のキー ロックとともに追加されます。この記事では、SERIALIZABLE 分離レベルについては説明しません。

現在読んでいる本:

公式ドキュメントの用語はロック読み取りで、これは挿入、更新、削除、共有モードでの選択、および更新のための選択を意味します。現在の読み取りでは、後続の where 条件が対応する行レコードにヒットするかどうかに関係なく、スキャンされたすべてのインデックス レコードがロックされます。現在の読み取りによりデッドロックが発生する可能性があります。

意図ロック:

Innodb のインテンション ロックは、主に複数の粒度ロックが共存する場合に使用されます。たとえば、トランザクション A がテーブルに S ロックを追加する場合、テーブル内の行がトランザクション B によってロックされていると、ロックのアプリケーションもブロックされる必要があります。テーブルに大量のデータがある場合、行ごとにロック フラグをチェックするオーバーヘッドが非常に高くなり、システム パフォーマンスに影響します。この問題を解決するために、テーブル レベルで新しいロック タイプを導入して、それが属する行のロック状態を示すことができます。これが「意図ロック」という概念につながります。たとえば、テーブルに 1 億件のレコードがあり、トランザクション A がそのうちのいくつかの行をロックしている場合、トランザクション B はテーブルにテーブル レベルのロックを追加する必要があります。意図的なロックがない場合は、テーブルをチェックして 1 億件のレコードがロックされているかどうかを確認する必要があります。意図ロックが存在する場合、トランザクション A がレコードを更新する前に意図ロックを追加し、次に X ロックを追加すると、トランザクション B は最初にテーブルに意図ロックがあるかどうか、および既存の意図ロックが追加しようとしているロックと競合するかどうかを確認します。競合がある場合は、各レコードを 1 つずつ確認することなく、トランザクション A がそれを解放するまで待機します。トランザクション B がテーブルを更新するとき、どの行がロックされているかを知る必要はありません。 1 つの行がロックされていることだけを知る必要があります。
簡単に言えば、インテンションロックの主な機能は、行ロックとテーブルロックの矛盾に対処し、「トランザクションが行のロックを保持しているか、ロックを保持しようとしている」ことを示すことです。

繰り返し不可能な読み取り:

これは、同じトランザクションで、連続する複数のスナップショット読み取りで読み取られるレコードが同じになる必要があることを意味します。

非反復読み取りのデモンストレーションは比較的単純なので、この記事では説明しません。

ファントムリーディング:

これは、トランザクション A で現在の読み取り操作が実行され、別のトランザクション B がトランザクション A の影響範囲内にレコードを挿入することを意味します。このとき、トランザクション A が別の現在の読み取り操作を実行すると、ファントム行が発生します。これと非反復読み取りの主な違いは、トランザクション A では 1 つがスナップショット読み取りで、もう 1 つが現在の読み取りであり、トランザクション B では 1 つが任意の DML 操作で、もう 1 つが単なる挿入である点です。たとえば、A では、共有モードでの select * from test where id<10 lock の結果セットは (1,2,3) です。 このとき、レコード 4 が B のテスト テーブルに挿入されます。 次に、A の再クエリの結果セットは (1,2,3,4) になりますが、これはトランザクション A の最初のクエリの結果セットと一致しません。 ここでの 4 はファントム行です。

デモンストレーション条件:再読み取り分離レベルでは、レコード ロックとギャップ ロックを組み合わせた Next-Key Locks がデフォルトで使用されます。つまり、レコード自体のロックに加えて、インデックス間のギャップもロックされます。したがって、このギャップ ロック メカニズムはデフォルトでオンになっており、ファントム行は生成されません。ファントム行をデモンストレーションする場合は、分離レベルを read-commited に変更するか、REPEATABLE-READ モードでギャップ ロックを無効にすることができます。ここでは 2 番目の方法を使用します。

ファントム リードのデモンストレーションでは、デモンストレーションの前に innodb_locks_unsafe_for_binlog パラメータが導入されており、これによりギャップ ロックを無効にできます。

innodb_locks_unsafe_for_binlog: 静的パラメータ、デフォルト値は 0 で、ギャップ ロックが有効であることを意味します。1 に設定すると、ギャップ ロックが無効であることを意味します。現時点では、MySQL にはレコード ロックのみがあります。ただし、1 に設定されていても、外部キーと一意キーの重複チェックに使用されるギャップ ロックは有効のままであることに注意してください。この時点で、トランザクションの分離レベルが繰り返し読み取りに退化していることは簡単に理解できますが、両者の間にはまだ何らかの違いがあるはずです。気軽に設定しないことをお勧めします。ここでの設定は、単純なファントム リードのデモンストレーション用です。MySQL の以降のバージョンでは、このパラメータが廃止される可能性があります。

セッション1は、まずmyid>95のレコードに現在の読み取りを追加します。

mysql> 表示テーブル test_gap_lock\G を作成します
************************** 1. 行 ****************************
テーブル: test_gap_lock
テーブルの作成: CREATE TABLE `test_gap_lock` (
`id` int(11) NULLではない、
`name` varchar(100) デフォルト NULL,
`myid` int(11) デフォルト NULL,
主キー (`id`)、
ユニークキー `uniq_name` (`name`),
キー `idex_myid` (`myid`)
) エンジン=InnoDB デフォルト文字セット=utf8
セット内の 1 行 (0.00 秒)
mysql> 開始します。
mysql> 更新のために、myid>95 の test_gap_lock から * を選択します。
+----+------------+------+
| ID | 名前 | myid |
+----+------------+------+
| 1 | 江 | 99 |
| 2 | ハブンメイ | 99 |
| 5 | hubingmei4 | 100 |
+----+------------+------+
セット内の 3 行 (0.00 秒)

セッション 2 この時点で、セッション 2 は myid=98 のレコードを正常に挿入します。

test_gap_lock に値(6,'jiang2',98)を挿入します。

クエリは正常、1 行が影響を受けました (0.00 秒)

セッション 1 が再度チェックすると、myid=98 のレコードがすでに存在することがわかります。このレコードはファントム行です。

mysql> 更新のために、myid>95 の test_gap_lock から * を選択します。
+----+------------+------+
| ID | 名前 | myid |
+----+------------+------+
| 1 | 江 | 99 |
| 2 | ハブンメイ | 99 |
| 5 | hubingmei4 | 100 |
| 6 | 江2 | 98 |
+----+------------+------+
セット内の 4 行 (0.00 秒)

ギャップロック機構はファントムリード問題を解決します。デモンストレーション条件: innodb_locks_unsafe_for_binlogの値をデフォルト値の0に戻し、tx_isolationは

REPEATABLE-READ では、SQL が非一意のインデックス idx_myid を使用するように、デモンストレーション中に必ず説明してください (テスト データが小さい場合、オプティマイザがテーブル全体を直接スキャンし、すべてのレコードがロックされ、ギャップ ロックをシミュレートできなくなる可能性があるため)。

デモンストレーション例 1 (非一意インデックス + 範囲の現在の読み取り)mysql> show create table test_gap_lock\G

************************** 1. 行 ****************************
テーブル: test_gap_lock
テーブルの作成: CREATE TABLE `test_gap_lock` (
`id` int(11) NULLではない、
`name` varchar(100) デフォルト NULL,
`myid` int(11) デフォルト NULL,
主キー (`id`)、
ユニークキー `uniq_name` (`name`),
キー `idex_myid` (`myid`)
) エンジン=InnoDB デフォルト文字セット=utf8
セット内の 1 行 (0.00 秒)

セッション1では、まずセッションの現在の読み取りSQLがインデックスidx_myidを実行することを保証する方法について説明します。

mysql> 開始します。
クエリは正常、影響を受けた行は 0 行 (0.00 秒)
mysql> explain select * from test_gap_lock where myid>100 for update;
+----+-------------+---------------+--------+---------------+---------+-------+-------+------------+-----------------------------+
| id | select_type | テーブル | タイプ | possible_keys | key | key_len | ref | 行 | 追加 |
+----+-------------+---------------+--------+---------------+---------+-------+-------+------------+-----------------------------+
| 1 | SIMPLE | test_gap_lock | range | idex_myid | idex_myid | 5 | NULL | 2 | インデックス条件の使用 |
+----+-------------+---------------+--------+---------------+---------+-------+-------+------------+-----------------------------+
セット内の 1 行 (0.00 秒)
mysql> 更新のために、myid>100 の test_gap_lock から * を選択します。
+----+------------+------+
| ID | 名前 | myid |
+----+------------+------+
| 5 | hubingmei4 | 101 |
| 98 | テスト | 105 |
+----+------------+------+
セット内の 2 行 (0.00 秒)

セッション 2 は、ロックされたギャップが myid>100 であり、56 がこの範囲内にないため、最初に myid=56 を正常に挿入します。myid=109 を挿入すると、セッション 1 がコミット、ロールバック、またはロック待機がタイムアウトするまでスタックします。ロック待機がタイムアウトする前に、同じ SQL がセッション 1 で実行され、結果は依然として id=5,98 のレコードのみであるため、ファントム読み取りの問題は回避されます。

mysql> test_gap_lock に値 (999、'test2'、56) を挿入します。
クエリは正常、1 行が影響を受けました (0.00 秒)
mysql> test_gap_lock に値 (123、'test4'、109) を挿入します。
エラー 1205 (HY000): ロック待機タイムアウトを超えました。トランザクションを再起動してください。

デモンストレーション例 2 (一意でないインデックス + 現在の読み取りが等しい)mysql> select * from test_gap_lock;

+-----+------------+------+
| ID | 名前 | myid |
+-----+------------+------+
| 1 | 江 | 98 |
| 2 | ハブンメイ | 99 |
| 5 | hubingmei4 | 101 |
| 6 | 江2 | 100 |
| 7 | 江22 | 70 |
| 67 | 江222 | 80 |
| 98 | テスト | 105 |
| 123 | テスト4 | 109 |
| 999 | テスト2 | 56 |
+-----+------------+------+
セット内の行数は 9 です (0.00 秒)
セッション 1
mysql> 開始します。
クエリは正常、影響を受けた行は 0 行 (0.00 秒)
mysql> test_gap_lock から myid=100 を削除することを説明します。
+----+-------------+---------------+--------+---------------+----------+-------+-------+------------+
| id | select_type | テーブル | タイプ | possible_keys | key | key_len | ref | 行 | 追加 |
+----+-------------+---------------+--------+---------------+----------+-------+-------+------------+
| 1 | SIMPLE | test_gap_lock | range | idex_myid | idex_myid | 5 | const | 2 | where の使用 |
+----+-------------+---------------+--------+---------------+----------+-------+-------+------------+
セット内の 1 行 (0.00 秒)
mysql> myid=100 の test_gap_lock から削除します。
クエリは正常、2 行が影響を受けました (0.00 秒)

セッション2はmyid=99のレコードを挿入しますが、ギャップロックによりまだブロックされています。myid=97のレコードの挿入は成功します。

mysql> test_gap_lock に値を挿入します(676,'ギャップが記録されたテスト',99);
エラー 1205 (HY000): ロック待機タイムアウトを超えました。トランザクションを再起動してください。
mysql> test_gap_lock に値を挿入します (675、'ギャップが test1 に記録されました'、97)。
クエリは正常、1 行が影響を受けました (0.00 秒)

例 3 (主キー インデックス + 範囲の現在の読み取り)

mysql> test_gap_lock から * を選択します。
+-----+------------+------+
| ID | 名前 | myid |
+-----+------------+------+
| 1 | 江 | 98 |
| 2 | ハブンメイ | 98 |
| 5 | hubingmei4 | 100 |
| 6 | 江2 | 100 |
| 7 | 江22 | 70 |
| 67 | 江222 | 80 |
| 98 | テスト | 105 |
| 123 | テスト4 | 109 |
| 999 | テスト2 | 56 |
+-----+------------+------+
セット内の行数は 9 です (0.00 秒)
セッション 1
mysql> 開始します。
クエリは正常、影響を受けた行は 0 行 (0.00 秒)
mysql> id > 100 の場合、更新時に test_gap_lock から * を選択します。
+----+-------------+---------------+--------+---------------+---------+------+------+-------------+
| id | select_type | テーブル | タイプ | possible_keys | key | key_len | ref | 行 | 追加 |
+----+-------------+---------------+--------+---------------+---------+------+------+-------------+
| 1 | SIMPLE | test_gap_lock | range | PRIMARY | PRIMARY | 4 | NULL | 2 | where の使用 |
+----+-------------+---------------+--------+---------------+---------+------+------+-------------+
セット内の 1 行 (0.00 秒)
mysql> id > 100 の場合、test_gap_lock から * を選択して更新します。
+-----+-------+------+
| ID | 名前 | myid |
+-----+-------+------+
| 123 | テスト4 | 109 |
| 999 | テスト2 | 56 |
+-----+-------+------+
セット内の 2 行 (0.00 秒)

セッション 2 (id=3 は挿入できます。id=108 はギャップ ロックのため挿入できません。id=123 のレコードは、レコード ロックのため共有モードでは選択できません。id=125 は共有モードで選択して更新できますが、これは非常に奇妙です。これも現在の読み取りと見なす必要があります。ただし、後で公式ドキュメントを確認したところ、ギャップ ロックはギャップにレコードがないため挿入操作のみをブロックすることがわかりました。挿入操作を除いて、他の操作の結果は操作なしと同等であるため、MySQL はそれをブロックしません。)

mysql> test_gap_lock に値を挿入します (108、'ギャップ ロック test3'、123)。
エラー 1205 (HY000): ロック待機タイムアウトを超えました。トランザクションを再起動してください。
mysql> test_gap_lock に値 (3、'gap lock test3'、123) を挿入します。
クエリは正常、1 行が影響を受けました (0.00 秒)
mysql> select * from test_gap_lock where id=125 lock in share mode;
空のセット (0.00 秒)
mysql> explain select * from test_gap_lock where id=125 lock in share mode;
+----+-------------+-------+-------+---------------+-------+------+------+------+----------------------------------------------------+
| id | select_type | テーブル | タイプ | possible_keys | key | key_len | ref | 行 | 追加 |
+----+-------------+-------+-------+---------------+-------+------+------+------+----------------------------------------------------+
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | const テーブルを読み取った後に不可能な WHERE が検出されました |
+----+-------------+-------+-------+---------------+-------+------+------+------+----------------------------------------------------+
セット内の 1 行 (0.00 秒)
mysql> test_gap_lock を更新し、myid=12345 に設定します。ここで、id=125 です。
クエリは正常、影響を受けた行は 0 行 (0.00 秒)
一致した行: 0 変更された行: 0 警告: 0

ギャップ ロックの内部ロック原理 ギャップ ロックの前提条件: 1. トランザクション分離レベルが REPEATABLE-READ、innodb_locks_unsafe_for_binlog パラメータが 0、SQL で使用されるインデックスが非一意インデックスである。

2 トランザクション分離レベルが REPEATABLE-READ、innodb_locks_unsafe_for_binlog パラメータが 0、sql が範囲の現在の読み取り操作です。この場合、非一意のインデックスでなくてもギャップ ロックが追加されます。

ギャップロックのロック手順

上記の例 1 (非一意インデックス + 範囲カレント読み取り) と 3 (主キーインデックス + 範囲カレント読み取り) は理解しやすいです。例 2 (非主キーインデックス + 等しいカレント読み取り) もギャップロックを生成するのはなぜでしょうか? これは、Btree インデックスの原理から始める必要があります。Btree インデックスが順番に配置され、InnoDB に主キーのクラスター化インデックスがあることは誰もが知っています。私の描画能力には限界があります。例として、例 2 のロックプロセスを分析しました。手書きのロックプロセスは次のとおりです。

図のデータ編成順序から、myid=100のレコードが2つあることがわかります。ギャップロックを追加すると、gap1（98、100）、gap2（100、100）、gap3（100、105）の3つのギャップが生成されます。これら3つのオープン区間のmyid値は（高校の数学を正しく覚えていれば）挿入できません。明らかに、gap1にも（myid=99、id=3）（myid

=99、id=4)、gap2 には実際のギャップがなく、gap3 には (myid=101、id=7) などのレコードがあります。さらに、myid=100 の 2 つのレコードにレコード ロックが追加されます。つまり、これらの 2 つのデータ サービスは他のセッションから読み取ることができません (例 3 を参照)。

ネクストキーロック

デフォルトでは、MySQL のトランザクション分離レベルは繰り返し読み取りであり、innodb_locks_unsafe_for_binlog パラメータは 0 であるため、デフォルトで次のキー ロックが使用されます。いわゆるネクストキー ロックは、レコード ロックとギャップ ロックを組み合わせたもので、レコード自体をロックするだけでなく、インデックス間のギャップもロックされます。

次に、トランザクション分離レベルが繰り返し読み取りであると仮定して、ほとんどの SQL タイプをロックする方法を分析します。

から選択

いかなる種類のロックもなし

共有モードでロックを選択

スキャンされたすべてのインデックス レコードに共有の次のキー ロックが適用され、主キーのクラスター化インデックスには排他ロックが適用されます。

更新のために選択

スキャンされたインデックスレコードに排他的ネクストキーロックを追加し、プライマリキーのクラスター化インデックスに排他ロックを追加します。

更新..where 削除..where

スキャンされたインデックスレコードに次のキーロックを追加し、プライマリキーのクラスター化インデックスに排他ロックを追加します。

挿入します。

単純な挿入では、挿入された行に対応するインデックス レコードに排他ロックが追加されます。これはレコード ロックであり、ギャップはないため、他のセッションがギャップにレコードを挿入することをブロックしません。ただし、挿入操作の前にロックが追加されます。公式ドキュメントでは、これを挿入意図ギャップ ロック、つまり意図的なギャップ ロックと呼んでいます。この意図的なギャップ ロックの目的は、複数のトランザクションが同じギャップに同時に挿入する場合、挿入されたレコードがギャップ内の同じ位置にない限り、他のセッションを待たずに完了できるため、挿入操作で実際のギャップ ロックを追加する必要がないことを示すことです。テーブルにインデックス idx_test があり、テーブルにレコード 1 と 8 がある場合、各トランザクションは 2 から 7 までの任意のレコードを挿入でき、現在挿入されているレコードにのみレコード ロックを追加し、競合がないため、他のセッションが自分とは異なるレコードを挿入することをブロックしないと想像してください。

一意キー違反エラーが発生した場合、重複したインデックス レコードに読み取りロックが設定されます。複数のセッションが同時に同じ行レコードを挿入する場合、別のセッションがその行に対して排他ロックを取得していると、デッドロックが発生します。

挿入によるデッドロック現象のデモンストレーション1

mysql> テーブル t1\G の作成を表示します
************************** 1. 行 ****************************
表: t1
テーブルの作成: CREATE TABLE `t1` (
`i` int(11) NOT NULL デフォルト '0',
主キー (`i`)
) エンジン=InnoDB デフォルト文字セット=utf8
セット内の 1 行 (0.00 秒)

セッション 1

mysql> 開始します。
クエリは正常、影響を受けた行は 0 行 (0.00 秒)
mysql> t1 VALUES(1) に挿入します。
クエリは正常、1 行が影響を受けました (0.00 秒)

セッション2は現在停止しています

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

セッション3もこの時点で停止しています

mysql> 開始します。

クエリは正常、影響を受けた行は 0 行 (0.00 秒)

mysql> t1 VALUES(1) に挿入します。

セッション1 今回はセッション1をロールバックします

mysql> ロールバック;
クエリは正常、影響を受けた行は 0 行 (0.00 秒)

セッション 2 の挿入は成功したが、セッション 3 でデッドロックが検出され、ロールバックされたことがわかります。

セッション 2 クエリ OK、1 行が影響を受けました (28.87 秒)

セッション 3 エラー 1213 (40001): ロックを取得しようとしたときにデッドロックが見つかりました。トランザクションを再起動してください。

デッドロックの原因分析:

まず、セッション 1 がレコードを挿入し、そのレコードの排他ロックを取得します。この時点で、セッション 2 とセッション 3 は両方とも主キー競合エラーを検出しますが、セッション 1 はコミットされていないため、レコードの挿入に成功したとは見なされず、直接エラーを報告することができません。そのため、セッション 2 とセッション 3 は両方ともレコードの共有ロックを申請しますが、この時点ではまだ共有ロックを取得していないため、待機キューに入っています。この時点で、セッション 1 はロールバックし、行レコードの排他ロックを解放し、その後、セッション 2 とセッション 3 の両方が行の共有ロックを取得します。セッション 2 とセッション 3 がレコードを挿入する場合、排他ロックを取得する必要がありますが、両方とも共有ロックを持っているため、排他ロックを取得できず、デッドロックが発生します。この時点でセッション 1 がロールバックされるのではなくコミットされると、セッション 2 とセッション 3 の両方で主キーの競合エラーが直接報告されます。デッドロックログを確認すると、結果が一目でわかります

挿入2によるデッドロック

もう一つの同様のデッドロックは、セッション 1 が id=1 のレコードを削除し、それをコミットしないことです。このとき、セッション 2 とセッション 3 は id=1 のレコードを挿入します。この時点で、セッション 1 はコミットされます。セッション 2 とセッション 3 が挿入する必要がある場合、排他ロックを取得する必要があり、デッドロックが発生します。セッション 1 がロールバックすると、セッション 2 とセッション 3 は主キーの競合エラーを報告します。ここではもうデモは行いません。

重複キーの更新時に挿入...

このタイプの SQL と挿入ロックの違いは、キーの競合が検出されると、共有ロックではなく排他ロックが直接適用されることです。

交換する

置換操作でキーの競合が検出されない場合、そのロック戦略は挿入操作のロック戦略と同様になります。キーの競合が検出された場合は、排他ロックも直接適用されます。

INSERT INTO T SELECT ... FROM S WHERE ...

T テーブルでのロック戦略は通常の挿入の場合と同じです。さらに、S テーブル内の関連レコードに共有の次キー ロックが追加されます。 （繰り返し読み取りモードの場合はロックされません）

CREATE TABLE ... SELECT ... 選択したテーブルに共有のネクストキーロックを追加します

自動増分 ID のロック戦略

テーブル内のフィールドが自動インクリメント列の場合、InnoDB はインデックスの末尾に排他ロックを追加します。この自動増分値にアクセスするには、テーブル レベルのロックが必要です。ただし、このテーブル レベルのロックの期間は、トランザクション全体ではなく、現在の SQL のみです。つまり、現在の SQL が実行されると、テーブル レベルのロックは解除されます。このテーブル レベルのロックが保持されている間は、他のセッションはレコードを挿入できません。

外部キー検出のためのロック戦略

外部キー制約が存在する場合、挿入、更新、または削除操作では制約がチェックされ、外部キーの競合の有無に関係なく、対応するレコードに共有レコード ロックが設定されます。

上記の記事では、innodb ロック (レコード、ギャップ、Next-Key ロック) について詳しく説明しています。私が皆さんに共有したいのはこれだけです。参考になれば幸いです。また、123WORDPRESS.COM を応援していただければ幸いです。