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

データベースロックのメカニズムで紹介したように、DBMS では、データベースロックは、ロックの粒度に応じて行レベルのロック (INNODB エンジン)、テーブルレベルのロック (MYISAM エンジン)、ページレベルのロック (BDB エンジン) に分割できます。

行レベルのロック

行レベルのロックは、Mysql で最も詳細なロックであり、現在の操作の行のみがロックされることを意味します。行レベルのロックにより、データベース操作における競合を大幅に減らすことができます。ロックの粒度は最も小さくなりますが、ロックのオーバーヘッドも最大になります。行レベルのロックは、共有ロックと排他ロックに分類されます。

特徴

ロックの追加にはコストがかかり、時間がかかります。デッドロックが発生する可能性があります。ロックの粒度は最も小さく、ロックの競合の可能性は最も低く、同時実行性も最も高くなります。

テーブルレベルロック

テーブルレベルロックは、MySQL でロックの粒度が最も大きいロックです。これは、実装が簡単で、リソースの消費が少なく、ほとんどの MySQL でサポートされています。エンジン。最も一般的に使用される MYISAM と INNODB は、テーブルレベルのロックをサポートします。テーブルレベルのロックは、テーブル共有読み取りロック (共有ロック) とテーブル排他的書き込みロック (排他ロック) に分類されます。

特徴

オーバーヘッドが低く、ロックが速いため、デッドロックが発生せず、ロックの粒度が高く、ロックの競合の可能性が最も高く、同時実行性が最も低くなります。

ページレベルのロック

ページレベルのロックは、ロック粒度が行レベルのロックとテーブルレベルのロックの間である MySQL のロックです。テーブルレベルのロックは高速ですが、競合が多くなります。行レベルのロックは競合がほとんどありませんが、低速です。そこで、隣接するレコードのグループを一度にロックする、侵害されたページレベルが採用されました。 BDB はページレベルのロックをサポートします

特徴

オーバーヘッドとロック時間はテーブルロックと行ロックの間であり、ロックの粒度はテーブルロックと行ロックの間であり、同時実行性は平均的です

MySQL で一般的に使用されるストレージエンジンのロック機構

MyISAMとMEMORYはテーブルレベルのロック(テーブルレベルのロック)を使用します

BDBはページレベルのロック(ページレベルのロック)またはテーブルレベルのロックを使用し、デフォルトはページロックです

InnoDB は行レベルのロック (行レベルのロック) とテーブルレベルのロックをサポートしており、デフォルトは行レベルのロックです

Innodb の行ロックとテーブルロック

前述したように、Innodb エンジンは行ロックとテーブルロックの両方をロックする場合、テーブル全体がロックされるのはいつか、また 1 行のみがロックされるのはどのような場合でしょうか?

InnoDB の行ロックは、インデックスのインデックスエントリをロックすることによって実装されます。これは、データブロック内の対応するデータ行をロックすることによって実装される MySQL や Oracle とは異なります。 InnoDB のこの行ロック実装機能は、InnoDB がインデックス条件を通じてデータを取得する場合にのみ行レベルのロックを使用することを意味します。それ以外の場合、InnoDB はテーブルロックを使用します。

実際のアプリケーションでは、InnoDB 行ロックのこの機能に特別な注意を払う必要があります。そうしないと、大量のロック競合が発生し、同時実行パフォーマンスに影響を与える可能性があります。

インデックス条件なしでクエリを実行する場合、InnoDB は行ロックの代わりにテーブルロックを使用します。
MySQLの行ロックはレコードではなくインデックスに対するものであるため、異なる行のレコードにアクセスしても同じインデックスキーを使用するとロック競合が発生します。アプリケーションを設計する際には、この点に注意してください。
テーブルに複数のインデックスがある場合、異なるトランザクションは異なるインデックスを使用して異なる行をロックできます。また、主キーインデックス、一意のインデックス、または通常のインデックスのいずれを使用する場合でも、InnoDB は行ロックを使用してデータをロックします。
条件でインデックスフィールドが使用されている場合でも、MySQL がフルテーブルスキャンの方が効率的であると判断した場合、データの取得にインデックスを使用するかどうかが MySQL によって決定されます。一部の小さなテーブルの場合、インデックスは使用されません。その場合、InnoDB は行ロックの代わりにテーブルロックを使用します。したがって、ロックの競合を分析するときは、SQL 実行計画をチェックして、インデックスが実際に使用されているかどうかを確認することを忘れないでください。

行レベルのロックとデッドロック

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

MySQL では、行レベルのロックはレコードを直接ロックするのではなく、インデックスをロックします。インデックスは主キーインデックスと非主キーインデックスに分けられます。SQL ステートメントが主キーインデックスで動作する場合、MySQL は主キーインデックスを最初にロックします。非主キーインデックスを削除し、関連する主キーインデックスをロックします。 UPDATE および DELETE 操作中に、MySQL は WHERE 条件によってスキャンされたすべてのインデックスレコードをロックするだけでなく、隣接するキー値もロックします (いわゆるネクストキーロック)。

2 つのトランザクションが同時に実行されると、1 つは主キーインデックスをロックし、他の関連するインデックスを待機します。もう 1 つは、非主キーインデックスをロックし、主キーインデックスを待機しています。デッドロックが発生します。

デッドロックが発生した後、InnoDB は一般にそれを検出し、1 つのトランザクションにロックを解放してロールバックさせ、別のトランザクションにロックを取得させてトランザクションを完了させることができます。

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

1. 異なるプログラムが複数のテーブルに同時にアクセスする場合は、同じ順序でテーブルにアクセスするようにしてください。これにより、デッドロックの可能性を大幅に減らすことができます。

2. デッドロックの可能性を減らすために、同じトランザクション内で必要なすべてのリソースを一度にロックしてみます。

3. デッドロックが発生しやすいビジネス部分については、テーブルレベルのロックを通じて、アップグレードされたロック粒度を使用してみてください。

行レベルのロックは、Mysql レベルのロックで最も細かいロック粒度です。データベース操作の競合を大幅に削減します。行レベルのロックは共有ロックと排他ロックに分けられ、共有ロックと排他ロックの概念や使い方、注意点について詳しく紹介します。

共有ロック (共有ロック)

共有ロックは、読み取りロックとも呼ばれ、読み取り操作によって作成されるロックです。他のユーザーは同時にデータを読み取ることができますが、すべての共有ロックが解放されるまで、トランザクションはデータを変更する (データの排他的ロックを取得する) ことはできません。

トランザクション T がデータ A に共有ロックを追加した場合、他のトランザクションは A に共有ロックを追加することしかできず、排他ロックを追加することはできません。共有ロックが付与されたトランザクションはデータの読み取りのみが可能で、データを変更することはできません。

使用法

SELECT ... LOCK IN SHARE MODE;

クエリステートメントの後に LOCK IN SHARE MODE を追加すると、他のスレッドがクエリ内の行をロックしない場合、Mysql はクエリ結果の各行に共有ロックを追加します。結果セット。行が排他ロックを使用している場合、共有ロックを正常に適用できますが、それ以外の場合はブロックされます。他のスレッドも共有ロックを使用してテーブルを読み取ることができ、これらのスレッドは同じバージョンのデータを読み取ります。