Skip navigation links
4.3.2 テーブルロック関連の問題 »
Section Navigation      [Toggle]

4.3.1. 内部ロック方法

この節では、内部ロックについて説明します。内部ロックは、複数セッションによるテーブル内容の競合を管理するために、MySQL サーバー自体の内部で実行されます。このようなロックは、すべてサーバーによって実行され、ほかのプログラムは関連しないため、内部ロックと呼ばれます。外部ロックは、どのプログラムがいつテーブルにアクセスしてよいかをサーバーとほかのプログラムが相互に調整するためにテーブルファイルをロックする場合に発生します。項4.3.4. 「外部ロック」 を参照してください。

MySQL は、MyISAM、MEMORY、および MERGE テーブルにはテーブルレベルロックを使用し、InnoDB テーブルには行レベルロックを使用します。

ほとんどの場合、どのロックタイプがアプリケーションに適しているか推察することが可能ですが、一概にどのロックタイプが優れているかを判断するのは困難です。すべてはアプリケーションに依存しており、かつアプリケーションの部分毎に異なるロックタイプがあります。

行レベルロックで保存エンジンを使用するか判断する場合、ユーザーはアプリケーションの役割と、使用されている select と update ステートメントを確認する必要があります。たとえば、大抵の Web アプリケーションは多くの選択を実行し、相対的に削除はほとんど行わず、主にキー値に基づいた更新を行い、かつ特定のテーブルに挿入します。ベース MySQL MyISAM セットアップはよくチューニングされています。

MySQL のテーブルロックは、テーブルレベルロックを使用するストレージエンジンに対して、デッドロックフリーです。デッドロックは、クエリーの開始時に、同時に必要とされるすべてのロックを常に要求すること、そして同じ順序のテーブルを常にロックすることで回避されます。

MySQL はテーブルの write ロックを次のように許可します。

  1. テーブルにロックがない場合、write ロックをつけてください。

  2. または、write ロックキューでロック要求を行ってください。

MySQL はテーブルの read ロックを次のように許可します。

  1. テーブルに write ロックがない場合、read ロックをつけてください。

  2. または、read ロックキューでロック要求を行ってください。

テーブルの更新には、テーブルの取り出しよりも高い優先度が与えられます。したがって、ロックが解放されると、ロックは write ロックキュー内の要求で使用可能になり、次に read ロックキュー内の要求で使用可能になります。これにより、特定のテーブルに対して重い SELECT アクティビティーが存在する場合でも、更新が 「資源枯渇」 にさらされないことが確実になります。ただし、これはテーブルの更新を頻繁に行う場合、SELECT ステートメントは更新が行われなくなるまで待機することを意味します。

読み取りと書き込みの優先度を変更する方法については、項4.3.2. 「テーブルロック関連の問題」を参照してください。

Table_locks_immediate および Table_locks_waited ステータス変数を確認することでシステム上でテーブルロック競合を分析できます。これらはそれぞれ、テーブルロックの要求がすぐに許可された回数と待たされた回数を示します。 

mysql> SHOW STATUS LIKE 'Table%';
+-----------------------+---------+
| Variable_name         | Value   |
+-----------------------+---------+
| Table_locks_immediate | 1151552 |
| Table_locks_waited    | 15324   |
+-----------------------+---------+

MyISAM ストレージエンジンでは、特定のテーブルに対する読み取りと書き込みの競合を軽減するために、同時挿入がサポートされています。MyISAM テーブルのデータファイルの途中に空きブロックがない場合、行は常にデータファイルの末尾に挿入されます。この場合、併発する INSERT および SELECT ステートメントをロックなしで MyISAM テーブルに対して自由に混合して使用できます。つまり、ほかのクライアントが読むのと同時に MyISAM テーブルに行を挿入できます。テーブルの途中で行を更新または削除した場合欠落が生じます。欠落がある場合、同時挿入はできませんが、すべての欠落が新しいデータで満たされた場合は、自動的に再使用可能となります。この機能は concurrent_insert システム変数によって変更されます。項4.3.3. 「同時挿入」 を参照してください。

LOCK TABLES で明示的にテーブルロックを取得する場合、READ ロックではなく READ LOCAL ロックを要求することができます。これでテーブルをロックしている間に、ほかのセッションが同時挿入を実行できます。

実テーブル real_table に対して多数の INSERT および SELECT 操作を行う必要がある場合に、同時挿入が不可能なときは、一時テーブル temp_table に行を挿入し、定期的に一時テーブルからの行で実テーブルを更新します。これは次のコードで実行できます。 

mysql> LOCK TABLES real_table WRITE, temp_table WRITE;
mysql> INSERT INTO real_table SELECT * FROM temp_table;
mysql> DELETE FROM temp_table;
mysql> UNLOCK TABLES;

InnoDB は行ロックを使用する。InnoDB では、SQL ステートメントのトランザクションの初めではなく処理中に自動的にロックを取得するため、デッドロックが発生する可能性があります。

行レベルロックの利点

  • 多数のセッションで異なる行をアクセスする際に、ロックの衝突が少なくてすみます

  • ロールバックの変更が少なくてすみます

  • 1 つの行を長時間ロックすることが可能です

行レベルロックの欠点

  • テーブルレベルロックよりもメモリーを要します

  • テーブルの大部分で使用される際、より多くのロックが必要となるためテーブルレベルロックよりも処理速度が遅くなります

  • データの大部分に対して GROUP BY 操作を実行する場合、またはテーブル全体を頻繁にスキャンする場合は、ほかのロックよりも低速です

一般に、次の場合は行レベルロックよりテーブルロックの方が優れています。

  • テーブルに対するほとんどのステートメントが read である

  • テーブルに対するステートメントが read と write の混合であり、そのうち write は 1 つのキー読み込みで取得できる 1 つの行に対する更新または削除である 

    UPDATE tbl_name SET column=value WHERE unique_key_col=key_value;
DELETE FROM tbl_name WHERE unique_key_col=key_value;

  • SELECT を同時 INSERT ステートメントおよびごく少数の UPDATE または DELETE ステートメントと組み合わせる

  • テーブル全体に対する多数のスキャンや GROUP BY 操作を行い、書き込みは行わない

高レベルロックで、異なるタイプのロックをサポートすることによって、より簡単にアプリケーションのチューニングが行えます。というのも、ロックオーバーヘッドは低レベルロックに対して少ないからです。

行レベルロック以外のオプション:

  • バージョニング (同時挿入で MySQL に使用されるような): 同時に 1writer と多数の reader が存在する場合です。これはアクセス開始時に応じたデータに対して、データベースやテーブルは異なるビューをサポートします。これを表すほかの一般的な用語は、「タイムトラベル」、「書き込み時コピー」、「コピーオンデマンド」 などです。

  • 多くの場合コピーオンデマンドは行レベルロックよりも優勢です。しかし、最悪の場合、通常のロックを使用するよりも、メモリー容量が多く必要となり得ます。

  • 行レベルロックを使用する代わりに、MySQL の GET_LOCK() と RELEASE_LOCK() で提供されているようなアプリケーションレベルロックを利用できます。これらはアドバイザリロックなので、相互に連携するアプリケーションでのみ機能します。項7.11.4. 「その他の関数」 を参照してください。