MySQL のロックと分類とは何ですか?

1. データベースの同時実行シナリオ

高同時実行シナリオでは、他のミドルウェアを考慮せずに、データベースに次のシナリオが存在します。

• Read: 問題はなく、同時実行制御は必要ありません。

• 読み取りと書き込み: スレッド セーフティの問題があり、トランザクション分離の問題が発生する可能性があり、ダーティ リード、ファントム リード、および反復不可能な読み取りが発生する可能性があります。

• Write: スレッドの安全性の問題があり、最初の種類の更新が失われ、2 番目の種類の更新が失われるなど、更新損失の問題が発生する可能性があります。失った。

#上記の問題に対応して、SQL 標準では、異なる分離レベルでは異なる問題が発生する可能性があると規定しています。

MySQL の 4 つの主要な分離レベル:

分離レベルダーティリード反復不可能な読み取りファントムリードREAD UNCOMMITTED: 未コミットの読み取り発生する可能性があります発生する可能性があります発生する可能性がありますREAD COMMITTED: 読み取りコミット済み ソリューション起こる可能性がある起こる可能性があるREPEATABLE READ: 繰り返し可能な読み取りソリューション解決策発生する可能性のある現象SERIALIZABLE: シリアル化可能解決策解決策solve# ＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃

MySQL が実際に REPEATABLE READ 分離レベルで非再現性の問題を解決していることがわかります。基本的に はファントム読み取りの問題を解決しますが、極端な場合にはファントム読み取りが依然として存在します。

それでは、解決策は何でしょうか?一般的に、解決策は 2 つあります。

1️⃣ 読み取り操作用の MVCC と書き込み操作用のロック

読み取り の場合、RR レベル MVCC で、トランザクションが開始されると、ReadView が生成され、条件を満たす履歴バージョンが ReadView を通じて検索されます。このバージョンは、UNDO ログから構築されます。ReadView が生成されると、実際にはスナップショットが生成されます。今回の SELECT クエリは snapshot read (または一貫性のある読み取り) です。RR では、トランザクション実行中の最初の SELECT 操作のみが ReadView を生成し、その後の SELECT が生成されることがわかっています。操作 再利用 この ReadView により、反復不可能な読み取りやファントム読み取りの問題が大幅に回避されます。

書き込みの場合は、スナップショット読み取りまたは一貫性読み取り中にテーブル内のレコードに対してロック操作が実行されず、ReadView のトランザクションが履歴バージョンであるため、書き込みの場合は最新バージョンの操作は競合しないため、他のトランザクションはテーブル内のレコードを自由に変更できます。

2️⃣ 読み取りおよび書き込み操作がロックされている

ビジネス シナリオの一部でレコードの古いバージョンの読み取りが許可されていないが、レコードを毎回読み取る必要がある場合最新バージョンでは、たとえば銀行預金取引では、

最初に口座の残高を読み出します、次にそれをこの預金額に追加します、そして最後に データベースに書き込みます。口座残高を読み取った後、他のトランザクションがその残高にアクセスしたくない場合は、現在の入金トランザクションが完了するまでのみ、他のトランザクションが口座残高にアクセスできます。この方法では、レコードを読み取るときにレコードをロックする必要があります。つまり、読み取り操作と書き込み操作も書き込み/書き込み操作と同様にキューに入れられます。

ダーティ読み取りの場合、現在のトランザクションは別のコミットされていないトランザクションによって書き込まれたレコードを読み取りますが、別のトランザクションがレコードを書き込んでいる場合、レコードがロックされているの場合、現在のトランザクションはレコードを読み取ることができなくなるため、ダーティ リードの問題は発生しません。

反復不可能な読み取りの場合、現在のトランザクションが最初にレコードを読み取り、別のトランザクションがレコードを変更してコミットした後、現在のトランザクションは次の場合に異なる結果を取得するためです。現在のトランザクションがレコードを読み取るときにレコードがロックされている 場合、値を再度読み取ります。その場合、別のトランザクションはレコードを変更できず、当然、反復不可能な読み取りは発生しません。

ファントム読み取り

の場合、現在のトランザクションが 範囲のレコードを読み取り、その後 別のトランザクションがその範囲に新しいレコードを挿入するためです、現在のトランザクションがこの範囲のレコードを再度読み取ると、新しく挿入された新しいレコードが見つかります。新しく挿入されたレコードをファントム レコードと呼びます。 この範囲をどのように理解すればよいでしょうか?次のようになります。

テーブル user に
• id=1

  を持つデータが 1 つだけあるとします。

トランザクション A が
• id ​​= 1

  のクエリ操作を実行すると、データをクエリできます。範囲クエリ (例: id ​​in) の場合、データをクエリできます。 (1, 2)、1 つのデータのみがクエリされます。

このとき、トランザクション B は
• id ​​= 2

  で新しい操作を実行し、送信します。

このとき、トランザクションAは、
• id ​​in(1,2)

  のクエリを再度実行し、2レコードを読み込むことになり、ファントムリードが発生します。

Note

: RR の反復読み取りにより、id ​​= 2 のレコードは実際には見つかりません。そのため、これを実行すると、一度 update ... ここで id = 2 の範囲を検索するとわかります。 現在のトランザクションが初めてレコードを読み取るときにはファントム レコードが存在しないため、ロックによってファントム読み取りの問題を解決するのは簡単ではありません。そのため、読み取り時にロックするのは少し面倒です。誰をロックすればいいのか分からないからです。

それでは、InnoDB はどのようにしてそれを解決するのでしょうか?まず、InnoDB ストレージ エンジンがどのようなロックを持っているかを見てみましょう。

2. MySQL のロックと分類

MySQL の公式ドキュメントでは、InnoDB ストレージ エンジンに次のタイプのロックが導入されています:

同様に、まだ混乱しているように見えますが、JDK でロックを学習する方法に従って分類できます:

#3. ロックの詳細な分類

#ロックの粒度とは何ですか?いわゆるロックの粒度は、ロックする対象の範囲を指します。

たとえば、自宅のトイレに行く場合、バスルームをロックするだけで済みます。家族の侵入を防ぐために家全体をロックする必要はありません。バスルームはロックの粒度です。

適切なロック粒度はどれくらいですか?

実は、バスルームはトイレに行くだけでなく、シャワーを浴びたり、手を洗ったりするためにも使用されます。これには、ロックの粒度を最適化するという問題が含まれます。

あなたがバスルームでシャワーを浴びるとき、隔離されていれば、他の人が同時に入って手を洗うことができます。トイレ、浴槽、洗面台がすべて分離されており、比較的独立している場合は、 （湿式と乾式が分かれています） 実はこのトイレは3人同時に使えますが、もちろん3人で同じことはできません。これにより、ロックの粒度が向上し、シャワーを浴びるときにバスルームのドアを閉めるだけで済み、他の人は入って手を洗うことができます。バスルームを設計するときにさまざまな機能エリアが分離されていない場合、バスルームのリソースを最大限に活用することはできません。

同様に、MySQL にもロックの粒度があります。通常、行ロック、テーブル ロック、ページ ロックの 3 つのタイプに分かれます。

3.1 行ロック

共有ロックと排他ロックの紹介では、これらは実際には特定の行に対して記録されるため、行ロックとも呼ばれます。

レコードのロックはこのレコードにのみ影響するため、行ロックのロック粒度は MySQL で最も細かくなります。

InnoDB ストレージ エンジンのデフォルトのロックは行ロックです。

これには次の特徴があります:

1. ロック競合の可能性が最も低く、同時実行性が高い

   粒度が小さいため、したがって、ロック リソースの競合の可能性も最小限であるため、ロックの競合の可能性は低くなり、同時実行性が高くなります。

2. 高いオーバーヘッドと遅いロック

   ロックは非常にパフォーマンスを消費します。データベース内の複数のデータがロックされている場合を想像してみてください。必然的に多くのリソースを占有し、ロックの場合は、前のロックが解放されるのを待ってからロックする必要があります。

3. デッドロックが発生します

   デッドロックとは何かについては、読み続けてください。

3.2 テーブル ロック

テーブル レベルのロックは、テーブル レベルのロックです。

テーブル全体をロックします。これにより、デッドロックを大幅に回避できます。これは、MySQL で最も詳細なロック メカニズムでもあります。

MyISAM ストレージ エンジンのデフォルトのロックはテーブル ロックです。 これには次の特徴があります:

1. 低いオーバーヘッドと高速なロック

   テーブル全体がロックされるため、速度が向上します。単一のデータをロックするよりも高速でなければなりません。

2. デッドロックは発生しません

   テーブル全体がロックされており、他のトランザクションはロックをまったく取得できないため、当然のことながら、デッドロックが発生します。

3. ロックの粒度が大きく、ロック競合の可能性が高く、同時実行性が低い

3.3ページ ロック

ページ レベルのロックは MySQL 独自のロック レベルであり、他のデータベース管理ソフトウェアでは一般的ではありません。

ページ レベルのロックの粒度は行レベルのロックとテーブル レベルのロックの間であるため、ロックを取得するために必要なリソース オーバーヘッドとロックが提供できる同時処理機能も上記 2 つの間の範囲になります。さらに、行レベルのロックと同様に、ページレベルのロックもデッドロックを引き起こす可能性があります。

テーブル ロックページ ロックロックの粒度小さい大2つの間のロックの効率を追加遅い速い両者の間競合確率低い高-高大きな#

4. ロック互換性の分類

MySQL では、データの読み取りは主に現在の読み取りとスナップショットの読み取りに分けられます:

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

  スナップショット読み取りは、スナップショット データを読み取ります。ロックのない通常の SELECT はスナップショット読み取りです。

  SELECT * FROM table WHERE ...

• 現在の読み取り値

  現在の読み取り値は、履歴データではなく、最新データを読み取ることです。SELECT をロックしてください、またはデータの追加、削除、変更を行うと、現在読み込まれます。

  SELECT * FROM table LOCK IN SHARE MODE;
  SELECT FROM table FOR UPDATE;
  INSERT INTO table values ...
  DELETE FROM table WHERE ...
  UPDATE table SET ...

ほとんどの場合、データベースは current read 状況で操作され、同時シナリオでは read- 読み取り ## を許可する必要があります。 # 状況は影響を受けません。書き込み-書き込み、読み取り-書き込み、または書き込み-読み取り操作を相互にブロックしたい場合は、MySQL Lockの共有ロックと排他を使用する必要があります 。

4.1 共有ロックと排他的ロック

共有ロック (共有ロック) は、読み取りロックとも呼ばれ、S ロック と呼ばれます。データは同時に読み取ることができますが、トランザクションによってデータを変更することはできません。

排他的ロック (排他的ロック) は、排他的ロックまたは書き込みロックとも呼ばれ、XX ロック と呼ばれます。何かによって行に排他ロックが追加された場合、このトランザクションのみがその行の読み取りと書き込みを行うことができます。このトランザクションが終了するまで、他のトランザクションはその行にロックを追加できません。他のプロセスは読み取りはできますが、書き込み操作は実行できません。そのトランザクションを待つ必要があります。リリース。

ロックを取得する状況を分析してみましょう。トランザクション A とトランザクション B があるとします。

• トランザクション A がレコードの S ロックを取得し、トランザクション B もレコードの S ロックを取得したいとします。この時点で S ロックを取得する レコードの S ロックを取得すると、トランザクション B もロックを取得できます。つまり、トランザクション A とトランザクション B は同時にレコードの S ロックを保持します。

• トランザクション B がレコードの X ロックを取得したい場合、トランザクション A がコミットした後に S ロックが解放されるまで、この操作はブロックされます。

• トランザクション A が最初に X ロックを取得した場合、トランザクション B がレコードの S ロックを取得したいか X ロックを取得したいかに関係なく、トランザクション A がコミットするまでブロックされます。

したがって、S ロックと S ロックは互換性があり、S ロックと X ロックは互換性がなく、X ロックと X ロックも互換性がないと言えます。

4.2 インテンション ロック

インテンション共有ロック (インテンション共有ロック)、IS ロックと呼ばれます。トランザクションがレコードに S ロックを追加する場合、最初にテーブル レベルで IS ロックを追加する必要があります。

Intention Exclusive Lock (Intention Exclusive Lock)、IX ロックと呼ばれます。トランザクションがレコードに X ロックを追加する場合、最初にテーブル レベルで IX ロックを追加する必要があります。

インテンション ロックはテーブル レベルのロックです . これらは、テーブル レベルの S ロックおよび X ロックのときに テーブル内のレコードが追加されるかどうかを 迅速に判断することのみを目的として提案されています。テーブル内にロックされたレコードがあるかどうかを確認するための走査を避けるために、ロックされています。つまり、ISロックはISロックと互換性があり、IXロックはIXロックと互換性があります。

なぜインテンションロックが必要なのでしょうか?

InnoDB のインテンション ロックは、主に複数の粒度ロックが共存する場合に使用されます。たとえば、トランザクション A がテーブルに S ロックを追加したいとします。テーブル内の行がトランザクション B によって X ロックに追加されている場合、そのロックのアプリケーションもブロックされる必要があります。テーブルに大量のデータがある場合、ロック フラグを行ごとにチェックするオーバーヘッドが非常に大きくなり、システムのパフォーマンスに影響します。

たとえば、テーブルに 1 億件のレコードがあり、トランザクション A がいくつかのレコードの行をロックしている場合、トランザクション B はテーブル レベルのロックをテーブルに追加する必要があります。 , 次に、これらの 1 億レコードがロックされているかどうかをテーブルで確認する必要があります。インテンション ロックがある場合、トランザクション A がレコードを更新する前にインテンション ロックを追加し、次に X ロックを追加すると、トランザクション B はまずテーブルにインテンション ロックがあるかどうか、および既存のインテンション ロックが計画しているロックと競合するかどうかを確認します。競合がある場合は、各レコードをチェックせずにトランザクション A が解放されるまで待ちます。トランザクション B は、テーブルを更新するときにどの行がロックされているかを正確に知る必要はありません。少なくとも 1 つの行がロックされていることだけを知る必要があります。

率直に言うと、インテンション ロックの主な機能は、行ロックとテーブル ロックの間の矛盾を解決することです。これにより、トランザクションが特定の行に対してロックを保持しているか、またはその準備を行っていることが表示されます。ロックを保持するには

。

テーブル レベルでのさまざまなロックの互換性 :

#行ロック
			##同時実行パフォーマンス
低	一般		パフォーマンスのオーバーヘッド
小さな	2 つの間にデッドロックはありますか	かどうか

XX Sは XXIS#互換性なし

S

IS

IX

互換性あり互換性あり

互換性なし

互換性なし

互換性あり互換性あり

互換性なし

互換性なし

互換性がありません互換性がありません

互換性がありません

互換性がありません

互換性あり #互換性あり #互換性なし

#

4.3 読み取り操作のロック

MySQL 読み取り操作の場合、ロックするには 2 つの方法があります。

1️⃣ SELECT * FROM table LOCK IN SHARE MODE

現在のトランザクションがこのステートメントを実行すると、読み取りレコードに S ロックが追加されます 、これにより、他のトランザクションはこれらのレコードの S ロックを取得し続けることができます (たとえば、他のトランザクションも SELECT ... LOCK IN SHARE MODE ステートメントを使用してこれらのレコードを読み取ります)。ただし、これらのレコードを取得することはできません。レコードを X ロックします (たとえば、SELECT ... FOR UPDATE ステートメントを使用してこれらのレコードを読み取るか、これらのレコードを直接変更します)。

他のトランザクションがこれらのレコードの X ロックを取得したい場合、現在のトランザクションがコミットした後にこれらのレコードの S ロックが解放されるまでブロックされます。

2️⃣ SELECT FROM table FOR UPDATE

現在のトランザクションがこのステートメントを実行すると、読み取りレコード に X ロック が追加され、他のトランザクションがこれらのレコード S ロックを取得できなくなります (たとえば、 、他のトランザクションはこれらのレコードを読み取るために SELECT ... LOCK IN SHARE MODE ステートメントを使用します)、これらのレコードの X ロックは取得できません (たとえば、SELECT .... FOR を使用します)。 UPDATE ステートメントを使用してこれらのレコードを読み取るか、これらのレコードを直接変更します)。

他のトランザクションがこれらのレコードの S ロックまたは X ロックを取得したい場合、現在のトランザクションがコミットされた後にこれらのレコードの X ロックが解放されるまでブロックされます。

4.4 書き込み操作のロック

DELETE、UPDATE、および INSERT は、MySQL の一般的な書き込み操作です。暗黙的ロック、自動ロック、ロック解除。

1️⃣ DELETE

レコードに対して DELETE 操作を実行するプロセスは、実際には、まず B ツリー内のレコードの位置を特定し、次に、レコードの X ロックを解除してから、マークの削除操作を実行します。このプロセスは、X ロックを取得するロック読み取りメソッドを使用して、B ツリー内で削除されるレコードの位置を特定すると理解することもできます。

2️⃣ INSERT

通常の状況では、新しいレコードの挿入操作はロックされません。InnoDB は、この新しいレコードを一種の暗黙的ロックを通じて保護します。挿入されたレコードこのトランザクションがコミットされるまで、他のトランザクションからアクセスされることはありません。

3️⃣ UPDATE

レコードに対して UPDATE 操作を実行する場合は、次の 3 つの状況があります。

① レコードのキー値が変更されていない場合更新された列が占める記憶領域が変更の前後で変わっていない場合は、最初に B ツリー内でこのレコードの位置を特定し、次にレコードの X ロックを取得し、最後に変更操作を実行します。元のレコードの位置。 B ツリー内で変更される場所を記録するプロセスを、X ロックを取得するためのロック読み取り操作と考えることもできます。

② レコードのキー値が変更されておらず、少なくとも 1 つの更新された列が占める記憶領域が変更の前後で変化している場合は、まず B ツリー内でこのレコードの位置を見つけます。レコードを X ロックし、レコードを完全に削除し (つまり、レコードをガベージ リストに完全に移動し)、最後に新しいレコードを挿入します。 B ツリー内で変更されるレコードの位置を特定するこのプロセスは、X ロックを取得するためのロックされた読み取りとみなされ、新しく挿入されたレコードは、INSERT 操作によって提供される暗黙的なロックによって保護されます。

③ レコードのキー値が変更された場合、元のレコードに対して DELETE 操作を実行してから INSERT 操作を実行するのと同じであり、ロック操作は DELETE と DELETE の規則に従って実行する必要があります。入れる。

PS: 書き込みロックがロックされているときに、他のトランザクションが引き続き読み取りできるのはなぜですか??

InnoDB には MVCC 機構 (マルチバージョン同時実行制御) があるため、スナップショット読み取りがブロックされることなく使用できます。

4. ロック粒度の分類

ロック粒度とは何ですか。いわゆるロックの粒度は、ロックする対象の範囲を指します。

たとえば、自宅のトイレに行く場合、バスルームをロックするだけで済みます。家族の侵入を防ぐために家全体をロックする必要はありません。バスルームはロックの粒度です。

適切なロック粒度はどれくらいですか?

実は、バスルームはトイレに行くだけでなく、シャワーを浴びたり手を洗うためにも使用されます。これには、ロックの粒度を最適化するという問題が含まれます。

あなたがバスルームでシャワーを浴びるとき、隔離されていれば、他の人が同時に入って手を洗うことができます。トイレ、浴槽、洗面台がすべて分離されており、比較的独立している場合は、 （湿式と乾式が分かれています） 実はこのトイレは3人同時に使えますが、もちろん3人で同じことはできません。これにより、ロックの粒度が向上し、シャワーを浴びるときにバスルームのドアを閉めるだけで済み、他の人は入って手を洗うことができます。バスルームを設計するときにさまざまな機能エリアが分離されていない場合、バスルームのリソースを最大限に活用することはできません。

同様に、MySQL にもロックの粒度があります。通常、

行ロック、テーブル ロック、ページ ロックの 3 つのタイプに分かれます。 4.1 行ロック

共有ロックと排他ロックの紹介では、これらは実際には特定の行に対して記録されるため、行ロックとも呼ばれます。

レコードのロックはこのレコードにのみ影響するため、行ロックのロック粒度は MySQL で最も細かくなります。

InnoDB ストレージ エンジンのデフォルトのロックは行ロックです。

次のような特徴があります:

• ロック競合の可能性が最も低く、同時実行性が高い

  行ロックの粒度が小さいため、ロック リソースの競合の可能性も最も低くなります。ロックの競合が発生する可能性が低いほど、同時実行性は高くなります。

• 高いオーバーヘッドと遅いロック

  ロックはパフォーマンスを非常に消費します。データベース内の複数のデータがロックされていると想像してみてください。必然的に多くのリソースを占有し、ロックの場合は、前のロックが解放されるのを待ってからロックする必要があります。

• デッドロックが発生します

  デッドロックとは何かについては、読み続けてください。

4.2 テーブル ロック

テーブル レベルのロックはテーブル レベルのロックで、テーブル全体をロックしますこれにより、デッドロックを大幅に回避できます。これは、MySQL で最も詳細なロック メカニズムでもあります。

MyISAM ストレージ エンジンのデフォルトのロックはテーブル ロックです。

これには次の特徴があります:

• 低いオーバーヘッドと高速なロック

  テーブル全体がロックされるため、速度が向上します。単一のデータをロックするよりも高速でなければなりません。

• デッドロックは発生しません

  テーブル全体がロックされており、他のトランザクションはロックをまったく取得できないため、当然のことながら、デッドロックが発生します。

• ロックの粒度が大きく、ロック競合の可能性が高く、同時実行性が低い

4.3ページ ロック

ページ レベルのロックは MySQL 独自のロック レベルであり、他のデータベース管理ソフトウェアでは一般的ではありません。

ページ レベルのロックの粒度は行レベルのロックとテーブル レベルのロックの間であるため、ロックを取得するために必要なリソース オーバーヘッドとロックが提供できる同時処理機能も上記 2 つの間の範囲になります。さらに、行レベルのロックと同様に、ページレベルのロックもデッドロックを引き起こす可能性があります。

ロックの粒度ロックの効率を追加競合確率##同時実行パフォーマンス高低一般パフォーマンスのオーバーヘッド大きな小さな2 つの間にデッドロックはありますかかどうか#

5. 算法实现分类

对于上面的锁的介绍，我们实际上可以知道，主要区分就是在锁的粒度上面，而 InnoDB 中用的锁就是行锁，也叫记录锁，但是要注意，这个记录指的是通过给索引上的索引项加锁。

InnoDB 这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB 才使用行级锁，否则，InnoDB 将使用表锁。

不论是使用主键索引、唯一索引或普通索引，InnoDB 都会使用行锁来对数据加锁。

只有执行计划真正使用了索引，才能使用行锁：即便在条件中使用了索引字段，但是否使用索引来检索数据是由 MySQL 通过判断不同执行计划的代价来决 定的，如果 MySQL 认为全表扫描效率更高，比如对一些很小的表，它就不会使用索引，这种情况下 InnoDB 将使用表锁，而不是行锁。

同时当我们用范围条件而不是相等条件检索数据，并请求锁时，InnoDB 会给符合条件的已有数据记录的索引项加锁。

不过即使是行锁，InnoDB 里也是分成了各种类型的。换言之，即使对同一条记录加上行锁，不同的锁类型也会产生不同的效果。通常有以下几种常用的行锁类型。

5.1 Record Lock

记录锁，单条索引记录上加锁。

Record Lock 锁住的永远是索引，不包括记录本身，即使该表上没有任何索引，那么innodb会在后台创建一个隐藏的聚集主键索引，那么锁住的就是这个隐藏的聚集主键索引。

记录锁是有 S 锁和 X 锁之分的，当一个事务获取了一条记录的 S 型记录锁后，其他事务也可以继续获取该记录的 S 型记录锁，但不可以继续获取 X 型记录锁；当一个事务获取了一条记录的 X 型记录锁后，其他事务既不可以继续获取该记录的 S 型记录锁，也不可以继续获取 X 型记录锁。

5.2 Gap Locks

间隙锁，对索引前后的间隙上锁，不对索引本身上锁。

MySQL 在 REPEATABLE READ 隔离级别下是可以解决幻读问题的，解决方案有两种，可以使用 MVCC 方案解决，也可以采用加锁方案解决。但是在使用加锁方案解决时有问题，就是事务在第一次执行读取操作时，那些幻影记录尚 不存在，我们无法给这些幻影记录加上记录锁。所以我们可以使用间隙锁对其上锁。

如存在这样一张表：

CREATE TABLE test (
    id INT (1) NOT NULL AUTO_INCREMENT,
    number INT (1) NOT NULL COMMENT '数字',
    PRIMARY KEY (id),
    KEY number (number) USING BTREE
) ENGINE = INNODB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8;

# 插入以下数据
INSERT INTO test VALUES (1, 1); 
INSERT INTO test VALUES (5, 3); 
INSERT INTO test VALUES (7, 8); 
INSERT INTO test VALUES (11, 12);

如下：

开启一个事务 A：

BEGIN;

SELECT * FROM test WHERE number = 3 FOR UPDATE;

此时，会对((1,1),(5,3))和((5,3),(7,8))之间上锁。

如果此时在开启一个事务 B 进行插入数据，如下：

BEGIN;

# 阻塞
INSERT INTO test (id, number) VALUES (2,2);

结果如下：

为什么不能插入？因为记录(2,2)要 插入的话，在索引 number上，刚好落在((1,1),(5,3))和((5,3),(7,8))之间，是有锁的，所以不允许插入。 如果在范围外，当然是可以插入的，如：

INSERT INTO test (id, number) VALUES (8,8);

5.3 Next-Key Locks

next-key locks 是索引记录上的记录锁和索引记录之前的间隙上的间隙锁的组合，包括记录本身，每个 next-key locks 是前开后闭区间，也就是说间隙锁只是锁的间隙，没有锁住记录行，next-key locks 就是间隙锁基础上锁住右边界行。

InnoDB 默认使用 REPEATABLE READ 隔离级别。在这种情况下，InnoDB 使用 Next-Key Locks 锁进行搜索和索引扫描，这可以防止幻读的发生。

6. 乐观锁和悲观锁

乐观锁和悲观锁其实不算是具体的锁，而是一种锁的思想，不仅仅是在 MySQL 中体现，常见的 Redis 等中间件都可以应用这种思想。

6.1 乐观锁

所谓乐观锁，就是持有乐观的态度，当我们更新一条记录时，假设这段时间没有其他人来操作这条数据。

实现乐观锁常见的方式

常见的实现方式就是在表中添加 version字段，控制版本号，每次修改数据后+1。

在每次更新数据之前，先查询出该条数据的 version版本号，再执行业务操作，然后在更新数据之前在把查到的版本号和当前数据库中的版本号作对比，若相同，则说明没有其他线程修改过该数据，否则作相应的异常处理。

6.2 悲观锁

所谓悲观锁，就是持有悲观的态度，一开始就假设改数据会被别人修改。

悲观锁的实现方式有两种

共享锁（读锁）和排它锁（写锁），参考上面。

7. 死锁

是指两个或两个以上的进程在执行过程中，由于竞争资源或者由于彼此通信而造成的一种阻塞的现象，若无外力作用，它们都将无法推进下去。此时称系统 处于死锁状态或系统产生了死锁。

产生的条件

• 互斥条件：一个资源每次只能被一个进程使用；

• 请求与保持条件：一个进程因请求资源而阻塞时，对已获得的资源保持不放；

• 不剥夺条件：进程已获得的资源，在没有使用完之前，不能强行剥夺；

• 循环等待条件：多个进程之间形成的一种互相循环等待的资源的关系。

MySQL 中其实也是一样的，如下还是这样一张表：

CREATE TABLE `user` (
  `id` bigint NOT NULL COMMENT '主键',
  `name` varchar(20) DEFAULT NULL COMMENT '姓名',
  `sex` char(1) DEFAULT NULL COMMENT '性别',
  `age` varchar(10) DEFAULT NULL COMMENT '年龄',
  `url` varchar(40) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `suf_index_url` (`name`(3)) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

# 数据
INSERT INTO `user` (`id`, `name`, `sex`, `age`, `url`) VALUES ('1', 'a', '1', '18', 'https://javatv.net');
INSERT INTO `user` (`id`, `name`, `sex`, `age`, `url`) VALUES ('2', 'b', '1', '18', 'https://javatv.net');

按照如下顺序执行：

#行ロック	テーブル ロック	ページ ロック
小さい	大	2つの間の
遅い	速い	両者の間
低い	高	-

	A	B
①	BEGIN
②		BEGIN
③	SELECT * FROM user WHERE name='a' FOR UPDATE
④		SELECT * FROM user WHERE name='b' FOR UPDATE
⑤	SELECT * FROM user WHERE name='b' FOR UPDATE
⑥		SELECT * FROM user WHERE name='a' FOR UPDATE

1、开启 A、B 两个事务；

2、首先 A 先查询name='a'的数据，然后 B 也查询name='b'的数据；

3、在 B 没释放锁的情况下，A 尝试对 name='b'的数据加锁，此时会阻塞；

4、若此时，事务 B 在没释放锁的情况下尝试对 name='a'的数据加锁，则产生死锁。

此时，MySQL 检测到了死锁，并结束了 B 中事务的执行，此时，切回事务 A，发现原本阻塞的 SQL 语句执行完成了。可通过show engine innodb status \G查看死锁。

如何避免

从上面的案例可以看出，死锁的关键在于：两个(或以上)的 Session 加锁的顺序不一致，所以我们在执行 SQL 操作的时候要让加锁顺序一致，尽可能一次性锁定所需的数据行。

以上がMySQL のロックと分類とは何ですか?の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。