MySQL のテーブル レベルのロック、行レベルのロック、排他的ロック、共有ロックとは何ですか?

まえがき

私たちのビジネスは比較的複雑であるため、トランザクションを構成する複数の関連する SQL ステートメントが必要です。そこで、まずトランザクションとは何かについて説明しましょう。トランザクションとは、一緒に実行される SQL ステートメントのグループを指します。すべてが正常に実行されるか、すべてが失敗する必要があります。部分的に成功または部分的に失敗することは許可されません。トランザクションには ACID 特性があります:

• アトミック性: トランザクションの一貫性を確保するために、すべて成功するかすべて失敗します。

• 一貫性: たとえば、銀行振込では、ある人のお金を差し引く場合、別の人にお金を追加する必要があります。追加せずに単に差し引くことはできません。このようにして、ビジネス上の問題が発生し、データの一貫性が失われます;

• 永続性:データをコミットすると、データは最初にキャッシュに書き込まれます。キャッシュ内のデータがディスクに転送されるまでにはまだ時間がかかります。上に書いたように、この時点で停電、ダウンタイム、または再起動が発生した場合、データベースの耐久性を確保するために REDO ログが用意されています。

• 分離: このセクションでは、トランザクションの同時実行を許可する必要があるため、トランザクションを分離する必要がある理由を説明します。1 つのビジネスには多くのトランザクションが含まれており、多くの場合、バックグラウンドで多数のビジネスが存在します。すべてのトランザクションが逐次的に実行される場合、マルチスレッド プログラムを作成する場合、処理を実行できるスレッドは 1 つだけになり、非常に非効率になります。したがって、トランザクションは同時に実行する必要がありますが、同時実行には、トランザクションのセキュリティと一貫性と同時実行効率の問題といういくつかの問題が含まれます。これら 2 つのことを参照点として使用します。 MySQL の並行性/分離のレベル。トランザクションが同時に実行されるときにトランザクションをまったく分離しないと、ダーティ リーディングが発生する可能性があります (トランザクション B はトランザクション A のコミットされていないデータを読み取り、トランザクション A のデータを使用します)コミットされていないデータが計算に使用され、他の多くの結果が取得されます。その後、トランザクション A がそのデータをロールバックします。その後、トランザクション B の計算はすべて問題のあるデータであり、ダーティ リードは確実に問題を引き起こします)、 反復不可能な読み取り (同じ条件でデータを取得し、再度クエリを実行するとデータの値が変わっていることがわかります。もちろん、反復不可能な読み取りは問題にならない場合があります。ビジネス シナリオによっては許可されます。これは、ビジネス データのセキュリティと一貫性が厳密に関連しているかどうかと同じ) と ファントム リーディング (トランザクション内の同じ条件の前後で 2 つのクエリの結果のデータ量が異なる) がこれらの問題です。

# 次に、トランザクションの同時実行で発生する問題を解決するために、トランザクションの分離レベルを指定します。

• シリアル化。シリアル化はロックを使用して完全に実装されます。すべてのトランザクションはロックによってソートされ、順番に実行されます。この方法では、データのセキュリティは高くなりますが、同時実行効率は非常に低くなります。通常はこれを行いません。

• 非コミット読み取り。私たちが作成したマルチスレッド プログラムでは、クリティカル セクションのコード セグメントに対する同時実行制御がありません。同時実行性は高くても、データのセキュリティは非常に低くなります。コミット済み読み取りではダーティ読み取りの存在も許可されますが、これは問題があるため、コミットされていない読み取りは決して使用しないでください。

  シリアル化と非コミット読み取りは、実際のプロジェクトでは使用されません。一般に、データベース エンジンは、デフォルトでコミット読み取りと反復読み取りで動作します。これら 2 つの分離レベルは、データのセキュリティと整合性を結合します。データの同時実行効率は、この 2 つです。 MVCC マルチバージョン同時実行制御メカニズム によって実装されます。

• コミットされた読み取り、Oracle のデフォルトの作業レベル。コミットされていないデータの読み取りは許可されていません。このレベルでも、非反復読み取りと仮想読み取りの発生は許可されています。

• 反復読み取り、MySQL のデフォルトの作業レベル。トランザクションが再度読み取られたときにも同じデータが取得されることが保証されており、これにより仮想読み取りが部分的に解決されますが、仮想読み取りは引き続き発生します。

注:

• トランザクション分離レベルが高くなるほど、競合を避けるためにより多くのパフォーマンスが費やされます。 、効率は低いです。

• 「反復読み取り」レベルでは、仮想読み取りの問題の一部を実際に解決できますが、アップデートの更新によって引き起こされる仮想読み取りの問題を防ぐことはできません。読み取りの場合でも、シリアル化分離レベルを設定する必要があります。

トランザクション分離レベルの実装原則:

Lock MVCC。シリアル化の基礎となる実装原理はロックです。ロックには、共有ロック、排他ロック、意図共有ロック、意図排他ロック、ギャップ ロック、デッドロックが含まれます。InnoDB のコミット読み取りと反復読み取りの基礎実装原理: MVCC (複数バージョン同時実行制御)、 MVCC は、スナップショット読み取り (同じデータに複数のバージョンがあります)、現在の読み取り、元に戻すログ、やり直しログなどの同時読み取り方法を提供します。 MVCC はコミット読み取りと反復読み取りの原則であり、ロックはシリアル化の原則です

トランザクション ログは ACID 機能を実装するために使用され、共有ロック、排他的ロック、MVCC は整合性 (I) 機能を実装するために使用されます。トランザクション ログは、Undo ログ (ロールバック ログ) と REDO ログ (REDO ログ) に分割されます

#1. テーブル レベルのロックと行レベルのロック

• #テーブル レベル ロック:

  テーブル全体をロックします。オーバーヘッドが小さく (ロックするためにテーブル内の特定の行のレコードを見つける必要がないためです。このテーブルを変更したい場合は、このテーブルのロックを直接申請します)、ロックは高速です。

• デッドロックが存在します;

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

• 行レベルのロック:

  特定の行レコードがロックされます。コストがかかる (テーブル内で対応するレコードを見つける必要があり、テーブルとインデックスを検索するプロセスがある)、ロックが遅い、デッドロックが発生する、ロックの粒度が最も小さい、ロック競合が発生する可能性が高い最も低く、同時実行性は高くなります

MyISAM ストレージ エンジンはテーブル レベルのロックのみをサポートしますが、InnoDB はトランザクション処理、行レベルのロック、

#2. 排他的ロックと共有ロック

##排他的ロック:

X とも呼ばれますロック、書き込みロック

• 共有ロック:
S ロック、読み取りロックとも呼ばれる

• 読み取りおよび読み取り (SS) ) には互換性がありますが、読み取りと書き込み (SX, SX )、書き込みと書き込み (XX) は相互に排他的です

1. 間の排他的ロックと共有ロックの互換性をテストします。さまざまなトランザクション

まずテーブル SQL と内容を確認しましょう

# 分離レベルを確認してください:

#まずトランザクション A を開き、id=7 のデータに排他ロックを追加します。

#Open別のクライアントのトランザクション B:

id=7 に排他ロックまたは共有ロックを追加してもブロックされ、トランザクション A id=7 の行のデータには排他ロック (書き込みロック) が設定されているため、クエリはできません。その他は読み書きできません。

概要: 異なるトランザクション間のデータ ロックの場合、SS ロックのみが共存でき、XX、SX、および XS は共存できません。

2. テスト行ロックが追加されました。インデックス項目

# の実行ロックがインデックス ツリーに追加されます。

#テストが終了するたびに、実行した内容をロールバックしてください。

テーブルのインデックスのないフィールドをフィルター条件として使用します

トランザクション 2 は、chenwei のさまざまな行のレコードを取得します

InnoDB は行ロックをサポートしており、主キー ID をフィルター条件として使用したところ、トランザクション 1 とトランザクション 2 は異なる行のロックを正常に取得できました。しかし、現在、chenwei という名前の排他ロックを取得できないことがわかりました。これはなぜでしょうか?説明しましょう:

InnoDB の行ロックは、テーブルの行レコードをロックするのではなく、インデックス エントリをロックすることによって実装されます

そして、フィルター条件として名前を使用する場合、インデックスは使用されません。したがって、当然、行ロックは使用されませんが、テーブルロックが使用されます。これは、インデックスを通じてデータが取得される場合にのみ InnoDB が行レベルのロックを使用し、それ以外の場合、InnoDB はテーブル ロックを使用することを意味します!!!

名前フィールドにインデックスを追加します:

次に、今の操作を実行します:

name にインデックスを追加した後、2 つのトランザクションが異なる行に対して排他的ロック (更新用) を取得できることがわかりました。これは、InnoDB の行ロックがインデックス項目に追加されていることを再度証明しています。

name がインデックスを通過するようになったので、zhangsan を使用して、補助インデックス ツリー内でその名前が配置されている行レコードの ID (7) を見つけて、次に進みます。レコード排他ロック (個人的な推測では、補助インデックス ツリーと主キー インデックス ツリー内の対応するレコードがロックされていると考えられます)

3. シリアル化分離レベル テスト

すべてのトランザクションのシリアル化はすべて共有ロックまたは排他ロックであるため、手動で追加する必要はありません。選択すると共有ロックが取得され、挿入、削除、更新すると排他的ロックが取得されます。

シリアル化分離レベルの設定:

2 つのトランザクションは同時に共有ロックを取得できます (SS 共存:

次に、トランザクション 2 でデータを挿入します。

これは、挿入する必要があるためです。 added ロックしますが、トランザクション 1 がテーブル全体に共有ロックを追加したため、トランザクション 2 はテーブルを正常にロックできなくなります (sx は共存しません)

ロールバックしてすべてのロック取得ステータスを変更します。 ロールback:

##2 つのトランザクションを開く:

インデックスが追加されるため上記の選択は、zhangsan

#Transaction 2update;

Transaction という名前のデータに行共有ロックを追加することと同じです。現時点ではテーブル全体がトランザクション 1 の共有ロックによってロックされているため、2 は更新できません。

トランザクション 2 は補助インデックス ツリーで zhangsan を検索し、対応する主キー値を見つけて、主キー インデックス ツリーは対応するレコードを見つけましたが、このレコード行は共有ロックによってロックされていることがわかりました。トランザクション 2 は共有ロックを取得できますが、排他ロックを取得できません

これを再度使用します。主キー インデックスを試して、ID を更新できるかどうかを確認します。

はまだブロックされています。where の後ろのフィールドは現在使用されていますが、名前の代わりに ID を使用すると、補助インデックス ツリーからも名前が見つかります。対応する主キー、次に主キー インデックス ツリーに移動して対応するレコードを検索すると、主キー インデックス ツリー上のレコードはロックされます

id=8 のデータを更新し、成功しました。選択したとき、id=7 のデータに行ロックがかかっているだけです。もちろん、id=8 のデータも正常に操作できます。

インデックスがある場合は行ロックを使用し、インデックスがない場合はテーブル ロックを使用します。行レベルのロックはロックの粒度を指しますが、共有ロックと排他的ロックはロックの性質 (テーブル ロックか行ロックか) を指します。共有ロックと排他ロックには区別があります。

シリアル化は排他ロックと共有ロックを使用します。反復読み取りレベルでは、手動でロックしない場合、MVCC が使用されます。このメカニズムは実際にはロックを使用しません。手動でロックを追加することもできます。 InnoDB はインデックスを作成しません。テーブル ロックを使用します。インデックス項目がクエリで使用される場合、行ロックが使用されます。行ロックは、単にデータの行をロックするのではなく、インデックスをロックします。

以上がMySQL のテーブル レベルのロック、行レベルのロック、排他的ロック、共有ロックとは何ですか?の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。