MySQL テーブル ロック、行ロック、排他ロック、共有ロックの使用方法

1. トランザクション分離メカニズムの選択

• まったく気にしない場合は、uncommitted read# を使用してください。 ## トランザクション分離メカニズム。これらのスレッドがデータベースを同時に操作できる場合、ダーティ リード (コミットされていないデータが読み取られる)、非反復読み取り (2 つのクエリ値が異なる)、ファントム リード (2 つのクエリ データ ボリュームが異なる)など。問題は、 データのセキュリティは最も低く、利点は同時実行効率が非常に高いことです。通常は使用されません

• If we

  シリアル化 (ロックの実装に依存)、すべてのトランザクションはロックによってソートされます。 データのセキュリティは向上しますが、同時実行効率が低すぎるため、通常は使用されません

• したがって、通常は

  Committed Read とRepeatable Read を使用します。これら 2 つの分離レベルは、データのセキュリティ、整合性、同時実行効率のバランスをとり、MVCC マルチバージョン同時実行によって制御されます。コミット読み取りと反復読み取りの原則、ロックはシリアル化の原則)

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

テーブル レベルのロック:テーブル全体をロックします。オーバーヘッドが小さく (ロックするためにテーブル内の特定の行のレコードを見つける必要がないためです。このテーブルを変更したい場合は、このテーブルのロックを直接申請します)、ロックは高速です。デッドロックは発生しません。ロックの粒度は大きく、ロックの競合が発生します。確率は高く、同時実行性は低くなります。

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

InnoDB ストレージ エンジンはトランザクション処理をサポートし、テーブルは行レベルのロックをサポートし、同時実行機能はより優れています

1. InnoDB の行ロックは、インデックス上のインデックスを介して行われます。これは、テーブル内の行レコードをロックするのではなく、項目をロックすることによって実装されます。これは、InnoDB が行レベルのロックを使用して取得することのみを意味します。それ以外の場合、InnoDB はテーブル ロックを使用します

InnoDB の行ロック実装は行レコードではなくインデックス フィールドに追加されるロックであるため、InnoDB の下のテーブルの行は異なります。フィルター条件として同じインデックス フィールドが使用されている場合、エンジンにアクセスすると、ロックの競合が引き続き発生し、同時実行ではなく逐次的にのみ実行できます。
SQL でインデックスが使用されている場合でも、ロックの競合は発生します。 , MySQL オプティマイザを通過した後、
3. if インデックスを使用するよりもテーブル全体のスキャンの方が効率的であると考えられます。この時点では、インデックスの使用は放棄されます。そのため、行ロックは使用されません

   , but table locks
   will be used. たとえば、一部の小さなテーブルの場合、MySQL はインデックス

#3 を使用しません。排他的ロック (Exclusive) と共有ロック (共有)

排他ロック、X ロックとも呼ばれる、書き込みロック
共有ロック (S ロックとも呼ばれる)、読み取りlock
読み取り(SS) 互換性はありますが、読み取り書き込み(SX, SX)と書き込み(XX)は排他的です。トランザクションに X ロックと S ロックを追加する間:

A トランザクションはデータ オブジェクト A に S ロックを追加します。A に対して読み取り操作を実行できますが、更新操作は実行できません。ロック期間中は、その他のトランザクションは S ロックを A に追加できますが、X ロックは追加できません

• トランザクションがデータ オブジェクト A に X ロックを追加すると、A の読み取りと更新が可能になります。ロック期間中、他のトランザクションはA

• 表示ロック: 共有モードでロックを選択して強制的に共有ロックを取得し、更新のために … を選択して排他的ロックを取得します

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

最初に SQL とテーブルの内容を確認しましょう

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

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

次に、別のクライアントを使用してトランザクションを開きます

#別のトランザクションのサービス スレッドを使用して、ブロックされている id=7 のデータに排他ロックを追加します。

id=7 のデータに排他ロックを追加しようとしました。共有ロックはまだブロックされています

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

2. 行ロックの追加テスト インデックス アイテムで

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

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

トランザクション 2 はまた、このレコードのランキング ロックされ、予想通り失敗します。トランザクション 2 が chenwei のレコードの排他的ロックを取得します。成功するかどうかを試してください。

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

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

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

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

name にインデックスを追加すると、2 つのトランザクションが異なる行に対して排他的ロック (更新用) を取得できることがわかり、InnoDB の行ロックが

インデックス項目に追加されたのは、名前がインデックスを通過するためです。zhangsan を通じて、補助インデックス ツリー上で行レコードが配置されている行レコードの ID を見つけます。が 7 の場合、 主キー インデックス ツリーに移動し、対応する行レコードの排他ロックを取得します (個人的な推測では、補助インデックス ツリーと主キー インデックス ツリー内の対応するレコードがロックされていると思います)

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

(すべてのトランザクションは排他的ロックまたは共有ロックを使用し、ユーザーが手動でロックする必要はありません)

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

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

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

#現時点では、挿入で排他ロックを追加する必要がありますが、トランザクション 1 がテーブル全体に共有ロックを追加しているため、トランザクション 2 はテーブルを正常にロックできなくなります。 (SX は共存しません)

ロールバック

name にインデックスを追加したため、上記の選択はデータに行共有ロックを追加することと同じです。 zhangsan という名前の

トランザクション 2 更新

現時点ではテーブル全体がトランザクション 1 の共有ロックによってロックされているため、トランザクション 2 は更新できません

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

# 主キー インデックス ID を使用して

## を更新できるかどうかを確認してみましょう

# これはまだブロックされていますが、ここで名前の代わりに id を使用している後ろのフィールドは、名前でも補助インデックス ツリーを通じて対応する主キーを検索し、その後、主キーで対応するレコードを検索します。キー インデックス ツリー、 および主キー インデックス ツリー上のレコードがロックされています

(個人的な推測では、補助インデックス ツリーと主キー インデックス ツリーに対応するデータがロックされていると考えられます)

id=8 のデータが正常に更新されました。 ID 7 の行データには行ロックのみが追加されるため、ID 8 のデータを正常に操作できます

#インデックスがある場合は行ロックが使用されます。インデックスがない場合は、テーブル ロックを使用します。

テーブル レベルのロックまたは行レベルのロックは、ロックの粒度を指します。共有ロックと排他的ロックは、ロックの性質を指します。テーブル ロックか行ロックかにかかわらず、共有ロックには区別があります。ロックと排他的ロック

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