MySQL トランザクション分離レベルとロックメカニズムの問題に関する深い理解

概要

データベースは通常、複数のトランザクションを同時に実行します。複数のトランザクションが、同じデータ バッチに対して同時に追加、削除、変更、およびクエリ操作を実行する場合があり、その結果、ダーティ リード、ダーティ ライト、非再現性、およびファントム リードが発生する可能性があります。これらの問題の本質は、データベースにおけるマルチトランザクション同時実行の問題です。トランザクション同時実行の問題を解決するために、データベースはトランザクション分離メカニズム、ロックメカニズム、および MVCC マルチバージョン同時実行制御分離メカニズムを設計し、一連のメカニズムを使用してマルチトランザクション同時実行の問題を解決しました。

トランザクションとそのACID特性

原子性: 操作の不可分性。

一貫性: データの一貫性。

分離: トランザクションは互いに干渉しません。

永続性: データの変更は永続的です。

同時トランザクション処理の問題

ダーティ ライト: 更新が失われ、最後の更新によって他のトランザクションによる更新が上書きされます。

ダーティ リード: トランザクション A は、トランザクション B によって変更されたがコミットされていないデータを読み取ります。

非反復読み取り: トランザクション内の同じクエリは、異なる時間に異なる結果を返します。これは、データの更新および削除操作を目的としています。

ファントム リード: トランザクション A は、後で開始されたトランザクション B によって送信された新しく追加されたデータを読み取ります。これはデータ挿入のためです。

トランザクション分離レベル

READ-UNCONMMITTED、READ-COMMITTED、REPEATABLE-READ、SERIALIZABLE 読み取り不可、読み取り不可、読み取り不可

現在のデータベースのトランザクション分離レベルを表示します。

'tx_isolation' のような変数を表示する

トランザクション分離レベルを設定します。

tx_isolation='REPEATABLE-READ' を設定します。

MySQL のデフォルトのトランザクション分離レベルは、繰り返し読み取りです。Spring でプログラムを開発する場合、分離レベルが設定されていない場合は、デフォルトで MySQL で設定されている分離レベルが使用されます。Spring が設定されている場合は、設定されている分離レベルが使用されます。

ロックの詳細

ロックは、複数のプロセスまたはスレッドを調整してリソースに同時にアクセスするためのコンピューター メカニズムです。

ロックの分類

パフォーマンスの観点からは、楽観的ロック（バージョン比較によって実装）と悲観的ロックに分けられます。

データベース操作の種類に応じて、読み取り/書き込みロックと書き込みロック（悲観的ロック）があります。

読み取りロック（共有ロック、S ロック（共有））：同じデータに対して、複数の読み取り操作を互いに影響を与えることなく同時に実行できます。

書き込みロック（排他ロック、X ロック（排他））：現在の書き込み操作が完了する前に、他の書き込みロックと読み取りロックをブロックします。

データベース操作の粒度から：テーブルロックと行ロック

テーブル ロック: 各操作でテーブル全体をロックします。オーバーヘッドが低く、ロックが高速です。デッドロックは発生しません。ロックの粒度は大きく、ロック競合の可能性は最も高く、同時実行性は最も低くなります。通常、テーブル全体のデータ移行のシナリオで使用されます。

# テーブル ロックを手動で追加します lock table table name read(write), table name 2 read(write);
# テーブルに追加されたロックを表示します。開いているテーブルを表示します。
# テーブルロックの削除、テーブルのロック解除;

行ロック: 各操作でデータの行がロックされます。オーバーヘッドが高く、ロックが遅く、デッドロックが発生する可能性があります。ロックの粒度は最小で、ロック競合の可能性は最低で、同時実行性は最高です。

InnoDB と MYISAM の最大の違いは次のとおりです。1. InnoDB はトランザクションをサポートします。2. InnoDB は行レベルのロックをサポートします。

要約:

MyISAM は、クエリ ステートメントを実行する前に、関連するテーブルに読み取りロックを自動的に追加します。また、更新、挿入、および削除操作を実行するときに書き込みロックを追加します。

InnoDB は、クエリ ステートメントを実行する前に行をロックしません (非シリアル分離レベル)。更新、挿入、および削除操作を実行するときに行ロックが追加されます。

読み取りロックは書き込みをブロックしますが、読み取りはブロックしません。書き込みロックは読み取りと書き込みの両方をブロックします。

行ロックとトランザクション分離レベルのケーススタディ

mysql テーブルを準備する

1. ダーティ リード: トランザクション A は、別のトランザクションによって変更されたがコミットされていないデータを読み取ります。この状況は単純なので、詳細には説明しません。対応するトランザクション分離レベルはコミットされずに読み取られます。

2. 非反復読み取り、対応するトランザクション分離レベル: 読み取りコミット

取引A:

セッショントランザクション分離レベルをコミット読み取りに設定します。
 
トランザクションを開始します。
 
t_user から * を選択します。

トランザクションB:

セッショントランザクション分離レベルをコミット読み取りに設定します。
 
トランザクションを開始します。
 
-- t_user に値 (1,'张',8) を挿入します。
t_user を更新し、age = 9 に設定し、id = 1 にします。
 
専念;

トランザクション A が初めてクエリ ステートメントを実行すると、結果は次のようになります。

この時点で、トランザクション B は完了していますが、トランザクション A はまだ終了していません。クエリの実行を続行すると、結果は次のようになります。

反復不可能な読み取りの問題が発生しています。トランザクション内の 2 つのクエリのデータ結果が矛盾しており、他のトランザクションによって送信されたデータが読み取られます。

3. 繰り返し可能読み取りの場合、トランザクション分離レベルを繰り返し可能読み取りに設定します。

トランザクション A の最初の実行の結果は次のとおりです。

トランザクション B は age=8 を実行、変更、更新し、コミットします。結果は次のようになります。

左側はトランザクション A です。クエリ結果は最初と同じで、非反復読み取りの問題が解決されています。直接クエリ、この時点では age=8 です。

MVCC (マルチバージョン同時実行制御) メカニズムは、繰り返し読み取り分離レベルで使用されます。選択操作ではバージョン番号は更新されず、スナップショット読み取り (履歴バージョン) になります。挿入、更新、および削除操作ではバージョン番号が更新され、現在の読み取り (現在のバージョン) になります。

4. ファントムリード、3で次のように新しいデータを追加します

このとき、トランザクション A は再度クエリを実行し、結果は次のようになります。

結果は最初と同じです。このシナリオでは、繰り返し読み取り分離レベルによって、繰り返し不可能な読み取りとファントム読み取りの問題が効果的に防止されます。

トランザクション Aが最初のクエリの後に無条件更新を実行すると、その更新はトランザクション B によって新しく追加されたデータを含むすべての行に影響します。この時点でクエリを再度実行すると、結果は次のようになります。

ファントム リードが発生します。MySQL によるファントム リードの公式説明は、トランザクションの 2 番目の選択で余分な行計算ファントム リードが発生するというものです。

5. シリアル化可能な InnoDB クエリもロックされます。クエリが範囲の場合、データ行がまだ挿入されていない場合でも、各レコード行が配置されているギャップ範囲を含む範囲内のすべての行がロックされます。

ギャップロック

Session_1 は、update t_user set name = '哈哈' where id>8 and id<18 を実行します。その後、他のセッションはこの範囲内のすべての行とギャップにデータを挿入または変更することはできません。

ギャップロックは繰り返し読み取り分離レベルでのみ有効です

ネクストキーロック

ネクストキー ロックは、列ロックとギャップ ロックを組み合わせたものです。ギャップ ロック (8,18) の範囲では、実際に既存の値が見つかります。たとえば、この間隔に最も近い ID は 3,20 です。したがって、範囲 (3,20] は実際には行ロックの範囲内にあります。

非インデックス行ロックはテーブルロックにアップグレードされます

ロックは主にインデックスに追加されます。インデックス以外のフィールドが更新されると、行ロックがテーブル ロックになることがあります。

InnoDB の行ロックはレコードのロックではなく、インデックスのロックです。インデックスは無効にできません。無効にすると、行ロックからテーブルロックにアップグレードされます。

行をロックするには、共有モード (共有ロック) と更新モード (排他ロック) のロックを使用することもできます。

結論は：

Innodb ストレージ エンジンは行レベルのロックを実装しているため、ロック メカニズムの実装によって生じるパフォーマンスの低下はテーブル レベルのロックよりも大きくなる可能性がありますが、全体的な同時処理機能の点では MYISAM のテーブル レベルのロックよりもはるかに優れています。

ただし、Innodb の行レベル ロックには脆弱な側面もあります。不適切に使用すると、全体的なパフォーマンスが低下する可能性があります。

行ロック分析

InnoDB_row_lockステータス変数をチェックして、システム上の行ロック競合を分析します。

'innodb_row_lock%' のようなステータスを表示します。 

さらに重要なものは次のとおりです。

Innodb_row_lock_time_avg (平均待機時間)

Innodb_row_lock_waits (待機の合計数)

Innodb_row_lock_time (合計待機時間)

待機回数が多く、各待機時間が短くない場合は、システム内で待機回数が多くなる理由を分析し、分析結果に基づいて最適化プランを策定する必要があります。

デッドロック

セッショントランザクション分離レベルを繰り返し読み取りに設定します。
 
トランザクションを開始します。
 
更新のために、id = 2 の t_user から * を選択します。
更新のために、id = 1 の t_user から * を選択します。

トランザクション A は最初に id=1 をロックし、次に id=2 をロックします。トランザクション B は逆の順序でロックするため、デッドロックが発生します。結果は次のようになります。

ほとんどの場合、MySQL はデッドロックを自動的に検出し、デッドロックの原因となったトランザクションをロールバックできますが、解決策がない場合もあります。

最近のデッドロック ログ情報を表示します。

エンジンの InnoDB ステータスを表示します\G;

ロックの最適化の提案:

1. 非インデックス行ロックがテーブルロックにアップグレードされるのを避けるために、すべてのデータ取得をインデックスを通じて完了するようにしてください。

2. ロックの範囲を最小限に抑えるようにインデックスを適切に設計します。

3. ギャップロックを回避するために、インデックス条件の範囲を可能な限り縮小します。

4. トランザクション サイズを制御し、ロックされるリソースの量と時間の長さを減らし、トランザクションの最後にトランザクション ロックを伴う SQL を実行するようにします。

5. トランザクションを可能な限り低いレベルで分離する

これで、MySQL トランザクション分離レベルとロック メカニズムの詳細な理解に関するこの記事は終了です。MySQL トランザクション分離レベルとロック メカニズムに関するより関連性の高い情報については、123WORDPRESS.COM の以前の記事を検索するか、次の関連記事を引き続き参照してください。今後とも 123WORDPRESS.COM をよろしくお願いいたします。