5.1 は、MyISAM テーブルと MEMORY テーブルのテーブル レベルのロック、BDB テーブルのページ レベルのロック、および InnoDB をサポートしています。テーブルの行レベルのロック。多くの場合、アプリケーションにどのロック タイプが最適であるかをトレーニングに基づいて推測することは可能ですが、特定のロック タイプが別のロック タイプよりも優れているかどうかを判断することは一般に困難です。すべてはアプリケーションに依存し、アプリケーションの異なる部分には異なるロック タイプが必要になる場合があります。行レベルのロック ストレージ エンジンを使用するかどうかを決定するには、アプリケーションが何を行うか、および使用する select ステートメントと update ステートメントをどのように組み合わせているかを確認する必要があります。たとえば、ほとんどの Web アプリケーションは、多数の選択と少数の削除を実行し、キー値の更新のみを実行し、特定のテーブルの挿入は少数のみ実行します。基本的な MySQL MyISAM セットアップは適切に調整されています。
MySQL では、テーブル レベルのロックを使用するストレージ エンジンの場合、テーブルがロックされていてもデッドロックは発生しません。これは、クエリの開始直後に必要なすべてのロックを常に要求し、常に同じ順序でテーブルをロックすることで管理されます。
WRITE の場合、MySQL で使用されるテーブル ロック方法は次のように機能します。
◆ テーブルにロックがない場合は、テーブルに書き込みロックを設定します。
◆ それ以外の場合は、ロック要求を書き込みロック キューに入れます。
READ の場合、MySQL で使用されるロック方法は次のように機能します。
◆ テーブルに書き込みロックがない場合は、テーブルに読み取りロックを設定します。
◆ それ以外の場合は、ロック要求を読み取りロック キューに入れます。
ロックが解放されると、書き込みロック キュー内のスレッドによってロックが取得され、次に読み取りロック キュー内のスレッドによってロックが取得されます。
これは、テーブルに多数の更新がある場合、SELECT ステートメントは更新がなくなるまで待機することを意味します。
INSERT ステートメントが競合しない場合は、ロックせずに、MyISAM テーブルに対して並列 INSERT ステートメントと SELECT ステートメントを自由に混在させることができます。
InnoDB は行ロックを使用し、BDB はページ ロックを使用します。どちらのストレージ エンジンでも、デッドロックが発生する可能性があります。これは、トランザクションの開始時ではなく、SQL ステートメントの処理中に InnoDB が自動的に行ロックを取得し、BDB がページ ロックを取得するためです。
行レベルのロックの利点:
· 多数のスレッドで異なる行がアクセスされる場合でも、ロックの競合はほとんど発生しません。
· 少数の変更のみでロールバックします。
・単一列を長時間ロックできます。
行レベルのロックの欠点:
· ページ レベルまたはテーブル レベルのロックよりも多くのメモリを消費します。
· テーブルの大部分で使用すると、より多くのロックを取得する必要があるため、ページ レベルまたはテーブル レベルのロックよりも遅くなります。
· ほとんどのデータに対して GROUP BY 操作を頻繁に実行する場合、またはテーブル全体を頻繁にスキャンする必要がある場合、他のロックよりも大幅に遅くなります。
· 高レベルのロックでは、ロックのコストが行レベルのロックよりも低いため、さまざまなタイプのロックをサポートすることでアプリケーションを簡単に拡張することもできます。
次の場合、テーブル ロックはページ レベルまたは行レベルのロックよりも優先されます。
· テーブル上のステートメントのほとんどが読み取りに使用されます。
· 厳密なキーを使用して
読み取りと更新を
行う
と、単一の読み取りキーで抽出できる行を更新または削除できます。
並列 INSERT ステートメントと、ごく少数の UPDATE ステートメントまたは DELETE ステートメント。
· 書き込み操作を行わずに、テーブル全体に対して多数のスキャンまたは GROUP BY 操作が行われます。
行レベルまたはページレベルのロックとは異なるオプション:
· バージョン管理 (たとえば、MySQL で並列挿入に使用される手法)。1 つの書き込み操作と多数の読み取り操作を同時に行うことができます。これは、アクセスがいつ開始されるかに応じて、データベースまたはテーブルが異なるデータ ビューをサポートしていることを意味します。その他の一般的な用語としては、「タイム トラッキング」、「コピー オン ライト」、「コピー オン デマンド」などがあります。
· オンデマンド レプリケーションは、多くの場合、ページ レベルまたは行レベルのロックより優先されます。ただし、最悪の場合、通常のロックを使用するよりも多くのメモリを使用する可能性があります。
· 行レベルのロックに加えて、MySQL の GET_LOCK() や RELEASE_LOCK() などのアプリケーション レベルのロックを使用できます。これらは勧告的なロックであり、正常に実行されているアプリケーションでのみ機能します。
最大のロック速度を達成するために、MySQL は InnoDB と BDB を除くすべてのストレージ エンジンに対して (ページ、行、または列のロックではなく) テーブル ロックを使用します。 InnoDB および BDB テーブルの場合、MySQL は、LOCK TABLES でテーブルを明示的にロックする場合にのみテーブル ロックを使用します。LOCK TABLES を使用しない場合は、InnoDB が自動行レベル ロックを使用し、BDB がページ レベルのロックを使用してトランザクションの分離を確保するためです。
ただし、大規模なテーブルの場合、ほとんどのアプリケーションではテーブル ロックの方が行ロックよりも優れていますが、いくつかの欠点があります。テーブル ロックを使用すると、多くのスレッドがテーブルから同時に読み取ることができますが、スレッドがテーブルに書き込みたい場合は、最初に排他的アクセスを取得する必要があります。更新中、テーブルにアクセスする他のすべてのスレッドは、更新が完了するまで待機する必要があります。
一般に、テーブルの更新はテーブルの取得よりも重要であると考えられているため、より高い優先順位が与えられます。これにより、テーブルに大量の SELECT アクティビティが存在する場合でも、テーブルを更新するアクティビティが枯渇することがなくなります。
テーブル ロックは、ハード ディスクがいっぱいでスレッドが処理する前に空き領域が必要なためにスレッドが待機している場合などに問題を引き起こす可能性があります。この場合、問題のテーブルにアクセスしようとしているすべてのスレッドも、より多くのハード ディスク領域が利用可能になるまで待機状態になります。
テーブル ロックは、次の状況でも問題になります。
· 顧客が長時間実行されるクエリを発行する。
· その後、別のクライアントが同じテーブルを更新します。クライアントは、SELECT が完了するまで待つ必要があります。
· 別のクライアントが同じテーブルに対して別の SELECT ステートメントを発行します。 UPDATE は SELECT よりも優先順位が高いため、SELECT ステートメントは UPDATE が完了するまで待機し、最初の SELECT が完了するまで待機します。
テーブル ロックによって引き起こされる競合を回避または軽減するためのいくつかの方法を以下に説明します。
· SELECT ステートメントの実行速度を上げてみます。これを行うには、いくつかの要約テーブルを作成する必要がある場合があります。
· --low-priority-updates を使用して mysqld を起動します。これにより、テーブルを更新 (変更) するすべてのステートメントに SELECT ステートメントよりも低い優先順位が与えられます。この場合、前の状況の 2 番目の SELECT ステートメントは、最初の SELECT が完了するのを待たずに、UPDATE ステートメントの前に実行されます。
· SET LOW_PRIORITY_UPDATES=1 ステートメントを使用すると、特定の接続内のすべての更新で低優先度を使用するように指定できます。
· LOW_PRIORITY 属性を使用すると、特定の INSERT、UPDATE、または DELETE ステートメントに低い優先順位を与えることができます。
· HIGH_PRIORITY 属性を使用すると、特定の SELECT ステートメントに高い優先順位を与えることができます。
· max_write_lock_count システム変数に低い値を指定して mysqld を起動し、特定の数の挿入が完了した後、MySQL がテーブルを待機しているすべての SELECT ステートメントの優先順位を一時的に高くします。これにより、一定数の WRITE ロックの後に READ ロックを与えることができます。
· INSERT と SELECT の組み合わせに問題がある場合は、同時 SELECT と INSERT をサポートする新しい MyISAM テーブルの使用に切り替えてください。
· 同じテーブル上で挿入と削除を混在させる場合、INSERT DELAYED が非常に役立ちます。
· 同じテーブル上で SELECT ステートメントと DELETE ステートメントを混在させるときに問題が発生した場合は、DELETE の LIMIT オプションが役に立ちます。
· SELECT ステートメントに SQL_BUFFER_RESULT を使用すると、テーブルのロック時間を短縮できます。
· mysys/thr_lock.c のロック コードは、単一のキューを使用するように変更できます。この場合、書き込みロックと読み取りロックは同じ優先順位を持つため、一部のアプリケーションでは役立ちます。
MySQL のテーブル ロックに関するいくつかのヒントを次に示します。
· 同じテーブル内の多くの行をチェックする必要がある選択と更新を混在させない場合は、並列操作を実行できます。
· 1 つのロックで多数の更新を行うと、ロックを使用しない更新よりもはるかに高速になるため、LOCK TABLES を使用して速度を向上させることができます。テーブルの内容を複数のテーブルに分割することも効果的です。
· MySQL でテーブルをロックするときに速度の問題が発生した場合は、テーブルを InnoDB または BDB テーブルに変換してパフォーマンスを向上させることができます。