このセクションでは、内部ロック、つまり複数のセッションによるテーブル内容の競合を管理するために、MySQL サーバー自体の内部で実行されるロックについて説明します。 この種類のロックは、完全にサーバーによって実行され、ほかのプログラムは関与しないため、内部です。 ほかのプログラムによって MySQL ファイルに対して実行されるロックについては、セクション8.11.5「外部ロック」を参照してください。
MySQL は InnoDB テーブルに行レベルロックを使用して、複数のセッションによる同時書き込みアクセスをサポートし、それらを複数ユーザー、高度な並列性、および OLTP アプリケーションに適したものにします。
単一の InnoDB テーブルに対して複数の同時書込み操作を実行する場合に deadlocks を回避するには、データ変更ステートメントがトランザクションの後半にある場合でも、変更が予想される行のグループごとに SELECT ... FOR UPDATE ステートメントを発行して、トランザクションの開始時に必要なロックを取得します。 トランザクションで複数のテーブルを変更またはロックする場合、各トランザクション内で、該当するステートメントを同じ順序で発行します。 デッドロックは、重大なエラーを表すのではなくパフォーマンスに影響します。これは、InnoDB ではデフォルトで detects デッドロック状態が自動的に発生し、影響を受けるトランザクションのいずれかがロールバックされるためです。
同時実行性の高いシステムでは、多数のスレッドが同じロックを待機している場合、デッドロック検出によって速度が低下する可能性があります。 デッドロック検出を無効にし、デッドロック発生時のトランザクションロールバックの innodb_lock_wait_timeout 設定に依存する方が効率的な場合があります。 デッドロック検出は、innodb_deadlock_detect 構成オプションを使用して無効にできます。
行レベルロックの利点:
異なるセッションが異なる行にアクセスする場合、ロックの競合は少なくなります。
ロールバックする変更が少なくなります。
1 つの行を長時間ロックできます。
MySQL では、MyISAM、MEMORY および MERGE テーブルに table-level locking を使用し、一度に更新できるセッションは 1 つのみです。 このロックレベルにより、これらのストレージエンジンは読み取り専用、読み取りの大部分、またはシングルユーザーアプリケーションに適しています。
これらのストレージエンジンは、常にクエリーの最初に 1 回だけ必要なすべてのロックをリクエストし、常に同じ順序でテーブルをロックすることによって、デッドロックを回避します。 トレードオフとは、この戦略によって同時実行性が低下することです。テーブルを変更する他のセッションは、現在のデータ変更ステートメントが終了するまで待機する必要があります。
テーブルレベルロックの利点:
必要なメモリーは比較的少なくなります (行ロックでは、ロックされた行または行のグループごとにメモリーが必要です)
単一のロックだけが必要であるため、テーブルの大部分に対して使用する場合に高速です。
データの大部分に対して
GROUP BY操作を頻繁に実行する場合、またはテーブル全体を頻繁にスキャンする必要がある場合は高速です。
MySQL はテーブル書き込みロックを次のように許可します。
テーブルにロックがない場合、それを書き込みロックします。
そうでない場合、書き込みロックキューにロックリクエストを入れます。
MySQL はテーブル読み取りロックを次のように許可します。
テーブルに書き込みロックがない場合、それを読み取りロックします。
そうでない場合、読み取りロックキューにロックリクエストを入れます。
テーブルの更新は、テーブルの取得よりも高い優先度が与えられます。 そのため、ロックが解放されると、ロックは書き込みロックキュー内のリクエストに使用できるようになり、次に読み取りロックキュー内のリクエストに使用できるようになります。 これにより、テーブルに大量の SELECT アクティビティがある場合でも、テーブルに対する更新が 「starved」 ではなくなります。 ただし、テーブルの更新が多数ある場合、SELECT ステートメントは更新がなくなるまで待機します。
読み取りと書き込みの優先度を変更する方法については、セクション8.11.2「テーブルロックの問題」を参照してください。
Table_locks_immediate および Table_locks_waited ステータス変数をチェックすることでシステム上のテーブルロック競合を分析できます。これらは、テーブルロックのリクエストがすぐに許可された回数と待機する必要があった回数を示します。
mysql> SHOW STATUS LIKE 'Table%';
+-----------------------+---------+
| Variable_name | Value |
+-----------------------+---------+
| Table_locks_immediate | 1151552 |
| Table_locks_waited | 15324 |
+-----------------------+---------+パフォーマンススキーマロックテーブルは、ロック情報も提供します。 セクション27.12.13「パフォーマンススキーマロックテーブル」を参照してください。
MyISAM ストレージエンジンでは、特定のテーブルのリーダーとライター間の競合を軽減するために、同時挿入をサポートしています。MyISAM テーブルでデータファイルの途中に空きブロックがない場合、行は常にデータファイルの末尾に挿入されます。 この場合、ロックなしで MyISAM テーブルに対して同時 INSERT および SELECT ステートメントを自由に組み合わせることができます。 つまり、ほかのクライアントが MyISAM テーブルから読み取ると同時に、それに行を挿入できます。 テーブルの途中で行が削除されるか更新されると、隙間が発生します。 穴がある場合、同時挿入は無効になりますが、すべての穴が新しいデータでいっぱいになると、再度自動的に有効になります。 この動作を制御するには、concurrent_insert システム変数を使用します。 セクション8.11.3「同時挿入」を参照してください。
LOCK TABLES で明示的にテーブルロックを獲得する場合、READ ロックではなく READ LOCAL ロックをリクエストして、テーブルをロックしている間に、ほかのセッションが同時挿入を実行できるようにできます。
同時挿入が不可能な場合に、テーブル t1 で多くの INSERT および SELECT 操作を実行するには、一時テーブル temp_t1 に行を挿入し、実際のテーブルを一時テーブルの行で更新します:
mysql> LOCK TABLES t1 WRITE, temp_t1 WRITE;
mysql> INSERT INTO t1 SELECT * FROM temp_t1;
mysql> DELETE FROM temp_t1;
mysql> UNLOCK TABLES;通常、次の場合、テーブルロックは行レベルロックよりも優れています:
テーブルに対するほとんどのステートメントが読み取りです。
-
テーブルに対するステートメントが読み取りと書き込みの組み合わせであり、そのうち書き込みは 1 つのキーの読み取りでフェッチできる単一の行に対する更新または削除です。
UPDATE tbl_name SET column=value WHERE unique_key_col=key_value; DELETE FROM tbl_name WHERE unique_key_col=key_value; 同時
INSERTステートメントとごく少数のUPDATEまたはDELETEステートメントと組み合わされたSELECT。ライターを使用しない、テーブル全体への多くのスキャンまたは
GROUP BY操作。
高レベルロックでは、行レベルロックよりロックのオーバーヘッドが少ないため、様々なタイプのロックをサポートすることでアプリケーションをより簡単にチューニングできます。
行レベルロック以外のオプション:
複数のリーダーを同時に持つことができるバージョニング (MySQL で同時挿入に使用されるものなど)。 つまり、データベースまたはテーブルでは、アクセスの開始時期に応じて異なるデータのビューがサポートされます。 この他の一般的な用語は、「「時間移動、」」 「copy on write、」または「「オンデマンドでコピー」」です。
コピーオンデマンドは、多くの場合、行レベルロックよりも優れています。 ただし、最悪の場合は、通常のロックを使用するよりもはるかに多くのメモリーを使用できます。
行レベルロックを使用するかわりに、MySQL の
GET_LOCK()およびRELEASE_LOCK()で提供されるロックなど、アプリケーションレベルのロックを使用できます。 これらはアドバイザロックであるため、相互に連携するアプリケーションでのみ機能します。 セクション12.15「ロック関数」を参照してください。