8.10.2 テーブルロックの問題

InnoDB テーブルでは、複数のセッションとアプリケーションが互いに待機したり、不整合の結果を生成したりすることなく、同じテーブルに対して同時に読み取りや書き込みを実行できるように、行レベルロックを使用します。このストレージエンジンでは、LOCK TABLES ステートメントは特別な保護を提供せず、代わりに並列性が低くなるため、この使用を避けてください。自動の行レベルロックにより、これらのテーブルがもっとも重要なデータを格納するもっともビジーなデータベースに適合し、同時にテーブルのロックやロック解除が必要ないためアプリケーションロジックが簡単になります。その結果、InnoDB ストレージエンジンは MySQL 5.6 のデフォルトです。

MySQL は InnoDB を除く、すべてのストレージエンジンに対して、テーブルロック (ページ、行、またはカラムロックの代わりに) を使用します。ロック操作自体には、あまりオーバーヘッドがありません。ただし、一度に 1 つのセッションしかテーブルに書き込むことができないため、これらのほかのストレージエンジンでの最高のパフォーマンスのため、頻繁にクエリーされ、めったに挿入または更新されないテーブルに対して主にそれらを使用します。

InnoDB を優先するパフォーマンスの考慮事項

テーブルを作成するために、InnoDB を使用するか、別のストレージエンジンを使用するかを選択する場合、テーブルロックの次の短所を考慮してください。

• テーブルロックにより、多くのセッションを同時にテーブルから読み取ることができますが、セッションでテーブルに書き込む必要がある場合、まず排他的アクセスを取得する必要がありますが、これはまずほかのセッションがテーブルを処理し終わるのを待つ必要がある可能性があることを意味します。更新中、この特定のテーブルにアクセスしようとするほかのすべてのセッションは、更新が完了するまで待機する必要があります。

• ディスクがいっぱいで、セッションを続行するには空き領域が使用できるようになる必要があるため、セッションが待機している場合にテーブルロックによって問題が発生します。この場合、問題のテーブルにアクセスしようとするすべてのセッションが、より多くのディスク領域が使用できるようになるまで待機状態になります。

• 実行に長時間かかる SELECT ステートメントにより、その間ほかのセッションのテーブルの更新が妨げられ、ほかのセッションが遅くなり、応答していないように見えます。セッションが更新のためにテーブルへの排他アクセスを取得するのを待機している間、SELECT ステートメントを発行するほかのセッションはそのあとに列をなし、読み取りセッションでも並列性が低くなります。

ロックパフォーマンスの問題の回避

次の項目では、テーブルロックによって発生する競合を回避または軽減するいくつかの方法について説明します。

• セットアップ時に CREATE TABLE ... ENGINE=INNODB を使用するか、既存のテーブルに対して ALTER TABLE ... ENGINE=INNODB を使用して、テーブルを InnoDB ストレージエンジンに切り替えることを考慮します。このストレージエンジンの詳細については、第14章「InnoDB ストレージエンジン」を参照してください。

• テーブルをロックする時間が短くなるように、SELECT ステートメントを最適化して、実行を高速化します。これを実行するには、いくつかのサマリーテーブルを作成する必要がある場合があります。

• --low-priority-updates で mysqld を起動します。テーブルレベルロックのみを使用するストレージエンジン (MyISAM、MEMORY、および MERGE など) の場合、これにより、テーブルを更新 (変更) するすべてのステートメントに SELECT ステートメントより低い優先度を与えます。この場合、先述のシナリオの 2 つめの SELECT ステートメントは UPDATE ステートメントの前に実行され、最初の SELECT の終了を待機しません。

• 特定の接続で発行されたすべての更新を低い優先度で実行させるように指定するには、low_priority_updates サーバーシステム変数を 1 に等しく設定します。

• 特定の INSERT、UPDATE、または DELETE ステートメントに低い優先度を与えるには、LOW_PRIORITY 属性を使用します。

• 特定の SELECT ステートメントに高い優先度を与えるには、HIGH_PRIORITY 属性を使用します。セクション13.2.9「SELECT 構文」を参照してください。

• max_write_lock_count システム変数の値を低くして mysqld を開始し、テーブルに対する特定の数の挿入が行われたあとにテーブルを待機しているすべての SELECT ステートメントの優先度を一時的に強制的に高めます。これにより、特定の数の WRITE ロックのあとの READ ロックが許可されます。

• SELECT と組み合わせた INSERT に問題がある場合は、同時 SELECT ステートメントと INSERT ステートメントをサポートする MyISAM テーブルに切り替えることを考慮します。(セクション8.10.3「同時挿入」を参照してください。)

• 同じ非トランザクションテーブルに対して挿入と削除を組み合わせる場合、INSERT DELAYED が役立つことがあります。セクション13.2.5.2「INSERT DELAYED 構文」を参照してください。

  注記

  MySQL 5.6.6 現在、INSERT DELAYED は非推奨であり、将来のリリースで削除されます。代わりに INSERT (DELAYED を付けない) を使用してください。

• 組み合わされた SELECT と DELETE ステートメントに問題がある場合、DELETE への LIMIT オプションが役立つことがあります。セクション13.2.2「DELETE 構文」を参照してください。

• SELECT ステートメントで SQL_BUFFER_RESULT を使用すると、テーブルロックの時間の短縮に役立つことがあります。セクション13.2.9「SELECT 構文」を参照してください。

• テーブルの内容を個別のテーブルに分割すると (クエリーを 1 つのテーブルのカラムに対して実行し、更新を別のテーブルのカラムに制限することによって)、役立つことがあります。

• 単一のキューを使用するように、mysys/thr_lock.c のロックコードを変更できます。この場合、書き込みロックと読み取りロックは同じ優先度を持ち、一部のアプリケーションに役立つことがあります。