mysql での innoDB ロックの概要-mysql チュートリアル-php.cn

InnoDB がロックされる前にトランザクションを開始する必要があるのはなぜですか?

トランザクションが送信/ロールバックされると、Innodb のロックは自動的に解放されます。 to autocommit= 1. 自動送信がオンになります。

検索条件にインデックスを使用するロックと使用しないロックの違い:

検索条件にインデックスがある場合、特定の行がロックされます。

検索条件が使用されない場合、テーブル全体のスキャンが実行され、すべての行 (存在しないレコードを含む) がロックされます

読み取りロック:

読み取りロックは共有されるか、相互にブロックされません。複数のユーザーは、互いに干渉することなく、同時に同じリソースを読み取ることができます。

書き込みロック:

書き込みロックは排他的です。つまり、書き込みロックは他の書き込みロックと読み取りロックをブロックします。さらに、書き込みロックは読み取りロックよりも優先度が高いため、書き込みロックリクエストは読み取りロックキューの前に挿入できますが、読み取りロックを書き込みロックの前に挿入することはできません

テーブルロック:

InnoDB には、インテント共有ロック (IS)、インテント排他ロック (IX) の 2 つのテーブルロックもあります

行ロック:

InnoDB は、共有ロックと排他ロックという 2 種類の行レベルのロックを実装します

オプティミスティックロック:

オプティミスティックロック、オプティミスティック同時実行制御とも呼ばれ、マルチユーザーの同時トランザクションが処理中に相互に影響を及ぼさないことを前提とし、各トランザクションはロックを生成せずに独自の処理を行うことができます。影響を受けるデータ。データ更新をコミットする前に、各トランザクションはまず、トランザクションがデータを読み取った後に他のトランザクションがデータを変更したかどうかを確認します。他のトランザクションからの更新がある場合、現在コミット中のトランザクションはロールバックされます。

悲観的ロック:

悲観的ロックは、悲観的同時実行制御とも呼ばれ、トランザクション A がデータの特定の行にロックを適用し、このトランザクションがロックを解放すると、他のトランザクションがロックと競合する操作を実行できます。トランザクション A によって課されるロックは、悲観的ロックと呼ばれます。共有ロックと排他的ロック (行ロック、ギャップロック、ネクストキーロック) はどちらも悲観的ロックです。悲観的ロックと楽観的ロックの実装は、たとえば、データベースによって提供されるロックメカニズムに依存します。 select * from news where id=12 for update を実行し、オプティミスティックロックはデータバージョンの記録に依存します。つまり、データを正常に送信できるかどうかの重要な要素としてテーブルにバージョン番号フィールドを追加します。

共有ロック (S):

共有ロックは、読み取りロックとも呼ばれます。トランザクションは、その行に対応する共有ロックを取得できますが、排他ロックは取得できません。トランザクションがデータ行を読み取るとき、他のトランザクションもそれを読み取ることができますが、データ行を追加、削除、または変更することはできません

共有ロックを設定します: SELECT .... LOCK IN SHARE MODE;

排他的lock (X):

排他ロックは書き込みロックとも呼ばれます。トランザクションがデータ行の排他ロックを取得すると、他のトランザクションはその行の他のロック（排他ロックまたは共有ロック）を取得できなくなります。データ行を読み取ります現時点では、他のトランザクションはデータ行を追加、削除、変更、または確認できません

排他ロックを設定します: SELECT .... FOR UPDATE

注:

select ステートメントの場合、 innodb はロックを追加しません。つまり、ロックがまったく存在しないため、複数の選択操作をロックの競合なしに同時に実行できます。

挿入、更新、および削除操作の場合、innodb は関連するデータに排他ロックを自動的に追加します。クエリ選択の場合のみ、手動で排他ロックを設定する必要があります。
Intention Shared Lock (IS):

インテント排他ロック (IX):

次に適用する必要があるロックをデータベースに通知し、テーブル。レコード A に排他ロックを追加する必要がある場合、innodb は最初にこのテーブルを検索し、テーブルに意図的な排他ロックを追加してから、レコード A に共有ロックを追加します。つまり、データ行に排他ロックを追加する前に、まずテーブルの IX ロックを取得する必要があります。共有ロックと意図的共有ロック、排他的ロックと意図的排他的ロックの違い:

共有ロックと排他ロック、システムが特定の状態にある場合、条件に応じて共有ロックまたは排他ロックが自動的に追加されます。共有ロックまたは排他ロックは手動で追加することもできます。

ロックの実装方法:

MySQL では、行レベルのロックはレコードを直接ロックするのではなく、インデックスをロックします。インデックスは主キーインデックスと非主キーインデックスに分けられます。SQL ステートメントが主キーインデックスで動作する場合、MySQL は主キーインデックスをロックします。ステートメントが非主キーインデックスで動作する場合、MySQL は最初にロックします。非主キーインデックス、次にロック関連の主キーインデックス。

InnoDB の行ロックは、インデックスエントリをロックすることによって実現されます。インデックスがない場合、InnoDB は非表示のクラスター化インデックスを通じてレコードをロックします。つまり、データがインデックス条件によって取得されない場合、InnoDB はテーブル内のすべてのデータをロックします。実際の効果はテーブルロックと同じです。

レコードロック: レコードをロックします。つまり、レコードをロックします。

Gap Lock: インデックス項目間の「ギャップ」、最初のレコードの前のギャップ、または最後のレコードの後のギャップをロックします。つまり、レコード自体を除く、レコードの範囲をロックします

Next-key Lock : をロックします。

注:

InnoDB のデフォルトのレベルは、反復可能読み取り (反復読み取り)

レベルです。 ANSI/IOS SQL 標準では、4 つのトランザクション分離レベル: 非コミット読み取り、コミット読み取り、反復可能読み取り、シリアル化可能ギャップロックとネクストキーロックの違い:

ネクストキーロックは、行ロックとギャップロックの組み合わせです。このように、InnoDB はインデックスレコードをスキャンするときに、まず選択したインデックスレコードに行ロック (レコードロック) を追加し、次にインデックスレコードの両側に行ロック (レコードロック) を追加します。ロック。ギャップがトランザクション T1 によってロックされている場合、他のトランザクションはこのギャップにレコードを挿入できません。

行ロックは他のトランザクションの変更または削除を防ぎ、ギャップロックは他のトランザクションの追加を防ぎ、行ロックと GAP ロックの組み合わせによって形成される Next-Key ロックは、RR のファントム読み取りの問題を共同で解決します。データ書き込み時のセクタ。

InnoDB でテーブルロックを使用する場合:

InnoDB を選択する理由はトランザクションと行ロックであることが多いため、InnoDB はほとんどの場合行レベルのロックを使用しますが、場合によってはテーブルの使用も検討します。 -レベルロック

トランザクションでほとんどのデータを更新する必要がある場合、テーブルは比較的大きくなります。デフォルトの行ロックを使用すると、効率が悪いだけでなく、他のトランザクションが簡単に待機することになります。長い時間とロックの競合。
トランザクションは比較的複雑であるため、デッドロックが発生し、ロールバックにつながる可能性があります。

(1) LOCK TALBES を使用して InnoDB にテーブルレベルのロックを追加できますが、

テーブルロックは InnoDB ストレージエンジンレイヤー

によって管理されず、上位レイヤーの MySQL Server によって管理される場合にのみ注意する必要があります。 autocommit=0、innodb_table_lock=1 (デフォルト設定)、InnoDB レイヤーは MySQL によって追加されたテーブルロックを知ることができ、MySQL Server は InnoDB によって追加された行ロックを感知できます。この場合、InnoDB はテーブルレベルに関係する死を自動的に識別できます。ロックしないと、InnoDB はそのようなデッドロックを自動的に検出して処理できません。 (2) LOCAK TABLES を使用して InnoDB をロックする場合は、AUTOCOMMIT を 0 に設定するように注意してください。そうしないと、MySQL はトランザクションの終了前にテーブルをロックしません。UNLOCK TABLES は、テーブルのロックを解除するために UNLOCAK TABLES を使用しないでください。トランザクションをローカルで暗黙的に送信します。LOCAK TABLES で追加されたテーブルレベルのロックは解放できません。テーブルのロックを解放するには、UNLOCK TABLES を使用する必要があります。

例: に書き込む必要がある場合。テーブル t1 とテーブル t からの読み取り

SET AUTOCOMMIT=0;
LOCAK TABLES t1 WRITE, t2 READ, ...;[do something with tables t1 and here];COMMIT;
UNLOCK TABLES;

ログイン後にコピー

デッドロック:

MyISAM では必要なすべてのロックを常に一度に取得し、すべてが満たされているか、すべてが待機しているため、デッドロックは発生しないと言いました。。 InnoDB では徐々にロックが取得されるため、デッドロックが発生する可能性があります。

デッドロックが発生した後、InnoDB は通常それを検出し、1 つのトランザクションにロックを解放してロールバックさせ、別のトランザクションにロックを取得させてトランザクションを完了させることができます。ただし、外部ロックまたはロックが関係している場合、InnoDB はデッドロックを完全に自動的に検出できません。これは、ロック待機タイムアウトパラメーター innodb_lock_wait_timeout を設定することで解決する必要があります。このパラメータはデッドロックの問題を解決するためだけに使用されるわけではないことに注意してください。同時アクセスが比較的多い場合、必要なロックをすぐに取得できないために多数のトランザクションが中断されると、大量のコンピュータリソースが占有されます。問題が発生すると、データベースが停止する可能性もあります。適切なロック待機タイムアウトしきい値を設定することで、この状況を回避できます。

デッドロックを回避する方法はたくさんあります。一般的な方法を 3 つ紹介します。

異なるプログラムが複数のテーブルに同時にアクセスする場合は、同じ順序でテーブルにアクセスすることに同意するようにしてください。これにより、デッドロックの可能性を大幅に減らすことができます。 2 つのセッションが 2 つのテーブルに異なる順序でアクセスすると、デッドロックが発生する可能性が非常に高くなります。ただし、アクセスが同じ順序で実行される場合、デッドロックは回避される可能性があります。
同じトランザクション内で、デッドロックの可能性を減らすために、必要なすべてのリソースを一度にロックするようにしてください。
デッドロックが非常に発生しやすいビジネス部分の場合は、アップグレードされたロック粒度を使用して、テーブルレベルのロックを通じてデッドロックの可能性を減らすことができます。
プログラムがデータをバッチで処理する場合、各スレッドが一定の順序でレコードを処理するようにデータが事前にソートされていれば、デッドロックの可能性も大幅に減らすことができます。
REPEATEABLE-READ 分離レベルでは、2 つのスレッドが SELECT...ROR UPDATE を使用して同じ条件レコードに同時に排他ロックを追加する場合、レコードがレコードと一致しない場合、両方のスレッドはロックに成功しました。プログラムはレコードがまだ存在しないことを検出し、両方のスレッドがこれを実行すると、デッドロックが発生します。この場合、分離レベルを READ COMMITTED に変更すると、問題を回避できます。
分離レベルがREAD COMMITEDの場合、両方のスレッドが先にSELECT...FOR UPDATEを実行した場合、条件を満たすレコードがあるかどうかを判定し、ない場合はレコードを挿入します。このとき、正常に挿入できるのは 1 つのスレッドだけであり、もう 1 つのスレッドはロックを待機します。最初のスレッドが送信すると、2 番目のスレッドは再び主キーによりエラーを起こしますが、このスレッドはエラーを起こします。専用ロックを取得します！このとき、3番目のスレッドが排他ロックを申請しに来ると、やはりデッドロックが発生します。この場合、挿入操作を直接実行してから主キー重複例外をキャッチするか、主キー重複エラーが発生した場合は必ず ROLLBACK を実行して取得した排他ロックを解放します

ps: デッドロックが発生した場合は、 SHOW INNODB STATUS コマンドを使用して、最後のデッドロックの原因と改善策を特定できます。

概要:

InnoDB テーブルについては、主に次の点があります

(1) InnoDB のマーケティングはインデックスに基づいていますデータがインデックスを通じてアクセスされない場合、InnoDB はテーブルロックを使用します。

(2) InnoDB ギャップロックのメカニズムと、InnoDB がギャップロックを使用する理由。

(3) 分離レベルが異なると、InnoDB のロックメカニズムと一貫した読み取り戦略が異なります。

(4) MySQL のリカバリとレプリケーションは、InnoDB のロックメカニズムと一貫した読み取り戦略にも大きな影響を与えます。

(5) ロックの競合、さらにはデッドロックを完全に回避することは困難です。

InnoDB のロック特性を理解した後、ユーザーは設計や SQL の調整、その他の次のような対策を通じてロックの競合とデッドロックを減らすことができます。

より低い分離レベルを使用してみます
インデックスを慎重に設計して試してくださいデータへのインデックス付きアクセスにより、ロックがより正確になり、ロックの競合の可能性が減ります。
適切なトランザクションサイズを選択すると、小規模なトランザクションでロックが競合する可能性が低くなります。
レコードセット表示をロックするときは、一度に十分なレベルのロックをリクエストするのが最善です。たとえば、データを変更する場合、最初に共有ロックを適用してから変更時に排他ロックを要求するのではなく、排他ロックを直接適用することが最善です。これにより、デッドロックが発生しやすくなります。
異なるプログラムがテーブルのセットにアクセスするときは、同じ順序で各テーブルにアクセスすることに同意する必要があります。テーブルの場合は、テーブル内の行に固定された順序でアクセスするようにしてください。これにより、デッドロックの可能性を大幅に減らすことができます。
同時挿入に対するギャップロックの影響を避けるために、データにアクセスする際には等価条件を使用するようにしてください。
実際の必要性を超えるロックレベルを適用しないでください。必要な場合を除き、クエリ時にロックを表示しないでください。
一部の特定のトランザクションでは、テーブルロックを使用して処理速度を向上させたり、デッドロックの可能性を減らしたりできます。

以上がmysql での innoDB ロックの概要の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。