6.3.1 MySQL のテーブルロック方法|無料CGI PHPサイト(新着情報、営業日カレンダーなど)

6.3.1. MySQL のテーブルロック方法

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

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

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

MySQL Enterprise.? MySQL Network Monitoring and Advisory Service はテーブルレベルでの使用時および行レベルロックでの使用時について専門的なアドバイスを提供しています。購読を希望する場合は、http://www-jp.mysql.com/products/enterprise/advisors.htmlを参照してください。

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

MySQLでのテーブルロックメソッドはWRITEロックを用いて以下のように機能します。

テーブルにロックがない場合、write ロックをつけてください。
もしくは、write ロックキューでロック要求を行ってください。

MySQLでのテーブルロックメソッドはREADロックを用いて以下のように機能します。

テーブルに　write ロックがない場合、read ロックをつけてください。
もしくは、read ロックキューでロック要求を行ってください。

ロックが開放されるとき、writeロックキューのスレッドに対してロックは有効であり、それから read ロックキューのスレッドに対しても有効です。これはテーブルの更新を頻繁に行う場合、SELECTステートメントは更新が行われなくなるまで待機することを意味します。

Table_locks_waitedおよびTable_locks_immediateステータス変数をチェックすることでシステム上でテーブルロック競合を分析できます。

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

MyISAMテーブルが中心に空きブロックを持たない場合、行は必ずデータファイルの後部に挿入される。この場合、併発するINSERTおよびSELECTステートメントをロックなしでMyISAMテーブルに対して自由に混合して使用できます。つまり、他のクライアントが読むのと同時にMyISAM テーブルに行を挿入できます。テーブルの途中で行を更新もしくは削除した場合欠落が生じます。欠落がある場合、同時挿入はできませんが、全ての欠落が新しいデータで満たされた場合は、自動的に再使用可能となります。

この機能はconcurrent_insertsシステム変数によって変更されます。項6.3.3. 「同時挿入」を参照してください。

テーブルに対して多数の INSERTおよび SELECT操作を行う必要がある場合、このような待機を回避するには、テンポラリテーブルに行を挿入し、一定の間隔でテンポラリテーブルからの行で実テーブルを更新します。これは以下のコードで実行できます。

mysql> LOCK TABLES real_table WRITE, insert_table WRITE;
mysql> INSERT INTO real_table SELECT * FROM insert_table;
mysql> TRUNCATE TABLE insert_table;
mysql> UNLOCK TABLES;

InnoDBは行ロックを使用する。InnoDBでは、ＳＱＬステートメントのトランザクションの初めではなく、プロセス中に自動的に入手するため、デッドロックが可能です。

行レベルロックの利点

多数のスレッドで異なる行をアクセスする際に、ロックコンフリクトが少なくてすみます。
ロールバックの変更がすくなくてすみます。
1つの行を長時間ロックすることが可能です。

行レベルロックの欠点

テーブルレベルロックよりもメモリを要します。
テーブルの大部分で使用される際、より多くのロックが必要となるためテーブルレベルロックよりも処理速度が遅くなります。
データの大部分に対してGROUP BYオペレーションを実行する場合、もしくは全テーブルを頻繁にスキャンする場合他のロックよりもはるかに遅いです。

以下の場合、行レベルロックに対してテーブルロックが優勢になります。

テーブルのほとんどのステートメントは read です。
1つのキーリードで取得される1つの行に対して、write が更新もしくは削除される場合、read と write が混合となります。
```
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ステートメントと混合されます。
writerを使用しない、全てのテーブルのGROUP BYオペレーションや多くのスキャン。

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

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

バージョニング（同時挿入でＭｙＳＱＬに使用されるような）：同時に１writerと多数のreaderが存在する場合です。これはアクセス開始時に応じたデータに対して、データベースやテーブルは異なるビューをサポートします。これに対する他の共通用語は「タイムトラベル」、「writeのコピー」または「コピーオンデマンドです。」
多くの場合コピーオンデマンドは行レベルロックよりも優勢です。しかし、最悪の場合、通常のロックを使用するよりも、メモリ容量が多く必要となり得ます。
行レベルロックを使用する代わりに、ＭｙＳＱＬではGET_LOCK()やRELEASE_LOCK()といったアプリケーションレベルロックを利用できます。これらはアドバイザリロックで、特に問題のないアプリケーションでのみ機能します。（詳しくは項11.10.4. 「その他の関数」をご確認ください。）

無料PHPプログラム

6.3.1. MySQL のテーブルロック方法