MySQL のロックの仕組みと使用法の分析

この記事では、例を使用して MySQL のロック メカニズムと使用方法を説明します。ご参考までに、詳細は以下の通りです。

MySQL のロック メカニズムは比較的単純で、最も注目すべき機能は、異なるストレージ エンジンが異なるロック メカニズムをサポートしていることです。たとえば、MyISAM および MEMORY ストレージ エンジンはテーブル レベルのロックを使用します。BDB ストレージ エンジンはページ ロックを使用しますが、テーブル レベルのロックもサポートします。InnoDB ストレージ エンジンは行レベルのロックとテーブル レベルのロックの両方をサポートしますが、デフォルトでは行レベルのロックを使用します。

これら 3 種類の MySQL ロックの特徴は、大まかに次のようにまとめることができます。

（１）テーブルレベルロック：オーバーヘッドが低く、ロックが高速で、デッドロックがなく、ロックの粒度が大きく、ロック競合の可能性が最も高く、同時実行性が最も低い。

（２）行レベルロック：オーバーヘッドが高く、ロックが遅く、デッドロックが発生する可能性があり、ロックの粒度が最も小さく、ロック競合の可能性が最も低く、同時実行性が最も高い。

（３）ページロック：オーバーヘッドとロック時間はテーブルロックと行ロックの中間であり、デッドロックが発生する可能性があり、ロックの粒度はテーブルロックと行ロックの中間であり、同時実行性は平均的である。

ロックの観点からのみ見ると、テーブル レベルのロックは、Web アプリケーションなど、クエリ指向でインデックス条件に従って少量のデータのみを更新するアプリケーションに適しています。一方、行レベルのロックは、一部のオンライン トランザクション処理システムなど、インデックス条件と同時クエリに従って少量の異なるデータを多数同時に更新するアプリケーションに適しています。

1. MyISAMテーブルロック

1. クエリテーブルレベルのロック競合

'table%' のようなステータスを表示します。

table_locks_waited の値が高い場合、テーブルレベルのロック競合が深刻であることを意味します。

2. MySQLテーブルレベルロックのロックモード

MySQL テーブル レベルのロックには、テーブル共有読み取りロックとテーブル排他書き込みロックの 2 つのモードがあります。

セッションがテーブルに読み取りロックを追加すると、そのセッションはロックされたテーブルにのみアクセスでき、読み取り操作のみ実行できます。他のセッションはテーブルを読み取ることはできますが、書き込み操作はブロックされ、ロックが解除されるまで待機する必要があります。セッションがテーブルに書き込みロックを追加すると、セッションはロックされたテーブルにのみアクセスして、読み取りおよび書き込み操作を実行できます。テーブルに対する他のセッションの読み取りおよび書き込み操作はブロックされ、ロックが解除されるまで待機する必要があります。

MyISAM テーブルの読み取り操作と書き込み操作、および書き込み操作自体はシリアルです。

3. テーブルロックの追加方法

読み取りロックを追加します:

テーブル tbl_name の読み取りをロックします。

書き込みロックを追加:

テーブル tbl_name の書き込みをロックします。

ロックを解除します:

テーブルのロックを解除します。

クエリ ステートメントを実行する前に、MyISAM は関連するすべてのテーブルに読み取りロックを自動的に追加します。更新操作を実行する前に、関連するテーブルに書き込みロックを自動的に追加します。このプロセスでは、ユーザーの介入は必要ありません。したがって、通常、ユーザーは LOCK TABLE コマンドを使用して MyISAM テーブルを直接明示的にロックする必要はありません。 MyISAM テーブルを明示的にロックするのは通常、トランザクション操作をある程度シミュレートし、特定の時点で複数のテーブルの一貫した読み取りを実現するために行われます。

LOCK TABLES を使用する場合は、使用するすべてのテーブルを一度にロックする必要があるだけでなく、SQL ステートメントで同じエイリアスを使用して、SQL ステートメントに出現する回数だけ同じテーブルをロックする必要があることに注意してください。そうしないと、エラーが発生します。

4. 同時挿入

MyISAM ストレージ エンジンには、同時挿入動作を制御するために特に使用されるシステム変数concurrent_insertがあります。その値は 0、1、または 2 です。

（1） concurrent_insertが0に設定されている場合、同時挿入は許可されません。

(2) concurrent_insertが1に設定されている場合、MyISAMテーブルに穴がない場合（つまり、テーブルの途中で削除された行がない場合）、MyISAMは1つのプロセスがテーブルを読み取り、別のプロセスがテーブルの最後からレコードを挿入することを許可します。これは MySQL のデフォルト設定でもあります。

（3） concurrent_insertが2に設定されている場合、テーブルに穴があるかどうかに関係なく、MyISAMテーブルの末尾へのレコードの同時挿入が許可されます。

テーブル ロック コマンドに「local」オプションを追加するだけです。つまり、 lock table tbl_name local read です。MyISAM テーブルの同時挿入条件が満たされると、他のユーザーはテーブルの末尾にレコードを同時に挿入できますが、更新操作はブロックされ、ロックされたユーザーは他のユーザーが同時に挿入したレコードにアクセスできなくなります。

5. MyISAM ロック スケジューリング

ライターとリーダーが同時に同じ MyISAM テーブルに対して書き込みロックと読み取りロックを要求すると、ライターが最初にロックを取得します。それだけでなく、読み取り要求が最初にロック待機キューに到着し、書き込み要求が後に到着した場合でも、読み取りロック要求の前に書き込みロックが挿入されます。これは、MySQL では一般に書き込み要求が読み取り要求よりも重要であるとみなされるためです。これは、MyISAM テーブルが更新操作やクエリ操作を大量に実行するアプリケーションに適していない理由でもあります。更新操作を大量に実行すると、クエリ操作で読み取りロックを取得するのが困難になり、永久にブロックされる可能性があるためです。

次の設定を通じて MyISAM のスケジュール動作を調整します。

（1）起動パラメータlow-priority-updatesを指定すると、MyISAMエンジンはデフォルトで読み取り要求を優先します。

（２） SET LOW_PRIORITY_UPDATES=1コマンドを実行すると、接続によって発行される更新要求の優先度が下がります。

（３）ステートメントのLOW_PRIORITY属性を指定して、INSERT、UPDATE、またはDELETEステートメントの優先順位を下げます。

（4）システムパラメータmax_write_lock_countに適切な値を設定します。テーブルの読み取りロックがこの値に達すると、MySQLは書き込み要求の優先度を一時的に下げて、読み取りプロセスにロックを取得する機会を与えます。

2. InnoDB ロックの問題

1. InnoDBの行ロック競合をクエリする

'innodb_row_lock%' のようなステータスを表示します。

InnoDB_row_lock_waitsとInnoDB_row_lock_time_avgの値が比較的高い場合、ロック競合が深刻であることを意味します。この場合、InnoDB モニターを設定して、ロック競合が発生しているテーブルとデータ行をさらに監視し、ロック競合の原因を分析できます。

モニターを開くには:

innodb_monitor テーブルを作成します(a INT) ENGINE=INNODB;
InnoDB ステータスを表示します\G;

モニターを停止するには:

innodb_monitor テーブルを削除します。

モニターをオンにすると、デフォルトでは 15 秒ごとに監視内容がログに記録されます。長時間オンにすると、.err ファイルが非常に大きくなります。そのため、問題の原因を確認した後、ユーザーは監視テーブルを削除してモニターをオフにするか、サーバーを "--console" オプションで起動してログ ファイルの書き込みをオフにすることを忘れないようにしてください。

2. InnoDBの行ロックとロック方法

InnoDB には、共有ロック (S) と排他ロック (X) の 2 種類の行ロックがあります。行ロックとテーブル ロックを共存させ、多粒度のロック メカニズムを実装するために、InnoDB には、内部で使用される 2 つの意図ロック (意図共有ロックと意図排他ロック) もあります。どちらの意図ロックもテーブル ロックです。トランザクションは、データ行をロックする前に、まず対応するテーブルの意図的なロックを取得する必要があります。

意図的なロックは、ユーザーの介入なしに InnoDB によって自動的に追加されます。 UPDATE、DELETE、および INSERT ステートメントの場合、InnoDB は関連するデータ セットに排他ロック (X) を自動的に追加します。通常の SELECT ステートメントの場合、InnoDB はロックを追加しません。トランザクションは、次のステートメントを通じてレコード セットに共有ロックまたは排他ロックを明示的に追加できます。

autocommit=0 に設定します。

共有ロック:

SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE

排他ロック（X）：

SELECT * FROM table_name WHERE ... FOR UPDATE

ロックを解除します:

テーブルのロックを解除します。

(トランザクションは暗黙的にコミットされます)

トランザクションがテーブルに対して共有ロックを取得すると、他のトランザクションはテーブル内のレコードを照会したり、レコードに共有ロックを追加したりできるようになります。トランザクションがテーブルを更新するときに、テーブルに共有ロックを追加した別のトランザクションがある場合、そのトランザクションはロックが解除されるまで待機する必要があります。他のトランザクションも同時にテーブルを更新すると、デッドロックが発生し、他のトランザクションは終了し、現在のトランザクションが更新操作を完了します。トランザクションがテーブルに対して排他ロックを取得すると、他のトランザクションはテーブルのレコードを照会することしかできず、共有ロックを追加したりレコードを更新したりすることはできず、待機状態になります。

3. InnoDBの行ロックの実装方法

InnoDB の行ロックは、インデックス上のインデックス項目をロックすることによって実装されます。InnoDB のこの行ロック実装機能は、次のことを意味します。

(1) InnoDB は、インデックス条件を通じてデータを取得する場合にのみ行レベルのロックを使用します。それ以外の場合は、InnoDB はテーブル ロックを使用します。

（2）MySQLの行ロックはレコードではなくインデックスに対してロックされるため、同じインデックスキーを使用して異なる行のレコードにアクセスするとロックの競合が発生します。

(3) テーブルに複数のインデックスがある場合、異なるトランザクションは異なるインデックスを使用して異なる行をロックできます。また、主キーインデックス、一意のインデックス、または通常のインデックスのいずれを使用する場合でも、InnoDB は行ロックを使用してデータをロックします。 (別のインデックスが使用されていますが、レコードが別のセッションによってロックされている場合は待機する必要があります。)

(4) 条件にインデックスフィールドが使用されている場合でも、データを取得するためにインデックスを使用するかどうかは、さまざまな実行プランのコストを判断することによってMySQLによって決定されます。MySQLは、非常に小さなテーブルなど、フルテーブルスキャンの方が効率的であると判断した場合、インデックスを使用しません。この場合、InnoDBは行ロックではなくテーブルロックを使用します。

4. ギャップロック

範囲条件を使用してデータを取得する場合、InnoDB は、キー値が条件範囲内にあるが存在しないレコードもロックします。このロックは「ギャップ ロック」と呼ばれます。 InnoDB は、ファントム リードを防止し、リカバリとレプリケーションのニーズを満たすためにギャップ ロックを使用します。ただし、このロック メカニズムは、適格範囲内のキー値の同時挿入をブロックし、深刻なロック待機を引き起こすため、範囲条件を使用してデータを取得することは避ける必要があります。

範囲条件によるロック時にギャップ ロックを使用するだけでなく、InnoDB は、存在しないレコードのロック要求に等価条件が使用される場合にもギャップ ロックを使用します。

5. リカバリとレプリケーションの要件がInnoDBのロック機構に与える影響

MySQL は BINLOG を使用して、データを更新する成功した INSERT、UPDATE、DELETE などの SQL ステートメントを記録し、それによって MySQL データベースのリカバリとマスター スレーブ レプリケーションを実現します。 MySQL リカバリ メカニズム (レプリケーションは実際にはスレーブ MySQL の BINLOG に基づく継続的なリカバリです) には、次の特性があります。

（1）MySQLのリカバリはSQL文レベルで行われ、つまりBINLOG内のSQL文を再実行します。

（２）MySQLのBinlogはトランザクションを送信順に記録し、リカバリもこの順序で実行されます。

したがって、MySQL のロック メカニズムのリカバリおよびレプリケーション要件は、トランザクションがコミットされる前に、他の同時トランザクションがロック条件を満たすレコードを挿入できない、つまりファントム リードが許可されないことです。

また、一般的な選択文の場合、MySQL は一貫性を実現するためにマルチバージョン データを使用し、ロックを必要としません。ただし、「 insert into target_tab select * from source_tab where ... 」や「 create table new_tab ...select ... From source_tab where ... 」などの SQL 文の場合、ユーザーは source_tab に更新を行っていませんが、MySQL はそのような SQL 文に対して特別な処理を行い、source_tab に共有ロックを追加しました。これは、ロックが追加されていない場合、この SQL 文の実行中に別のトランザクションが source_tab を更新して最初にコミットすると、BINLOG では更新操作が SQL 文の前に配置されるためです。この BINLOG を使用してデータベースをリカバリすると、リカバリ結果が実際のアプリケーション ロジックと一致しなくなり、レプリケーションによってマスター データベースとスレーブ データベースの間に不整合が発生します。これは、target_tab または new_tab に実際に挿入されるデータは、source_tab が別のトランザクションによって更新される前のデータですが、BINLOG は更新前のデータと、その後の select...insert... ステートメントの実行を記録するためです。上記のステートメントの SELECT が範囲条件である場合、InnoDB はソース テーブルにギャップ ロックも追加します。したがって、この種の SQL ステートメントは元のテーブルへの同時更新をブロックするため、可能な限り回避する必要があります。

6. InnoDBテーブルロックを使用する状況と注意事項

InnoDB テーブルの場合、ほとんどの場合に行レベルのロックを使用する必要がありますが、主に次の 2 つの状況では、一部の特殊なトランザクションでテーブルレベルのロックも検討できます。

（1）トランザクションは大部分またはすべてのデータを更新する必要があり、テーブルは比較的大きいです。デフォルトの行ロックを使用すると、トランザクションの実行効率が低くなるだけでなく、他のトランザクションが長時間待機したり、ロックの競合が発生したりする可能性があります。この場合、テーブルロックを使用してトランザクションの実行速度を上げることを検討できます。

（２）トランザクションは複数のテーブルに関係しており、比較的複雑であるため、デッドロックが発生し、多数のトランザクションロールバックが発生する可能性があります。この場合、デッドロックを回避し、トランザクションのロールバックによって発生するデータベースのオーバーヘッドを削減するために、トランザクションに関係するテーブルを一度にロックすることも検討できます。

また、InnoDB でテーブルロックを使用する場合は、次の 2 つの点に注意する必要があります。

(1) LOCK TABLES を使用して InnoDB にテーブルレベルのロックを追加できますが、テーブルロックは InnoDB ストレージエンジン層ではなく、その上位層である MySQL Server によって管理されます。autocommit =0およびinnodb_table_locks=1 (デフォルト設定) の場合にのみ、InnoDB 層は MySQL によって追加されたテーブルロックを認識し、MySQL Server は InnoDB によって追加された行ロックも認識できます。この場合、InnoDB はテーブルレベルのロックに関連するデッドロックを自動的に識別できます。それ以外の場合、InnoDB はそのようなデッドロックを自動的に検出して処理できません。

(2) LOCK TABLESを使用して InnoDB テーブルをロックする場合、AUTOCOMMIT を 0 に設定するように注意してください。そうしないと、MySQL はテーブルをロックしません。トランザクションが終了する前にUNLOCK TABLESを使用してテーブル ロックを解除しないでください。UNLOCK TABLES は暗黙的にトランザクションをコミットするためです。COMMIT または ROLLBACK は、 LOCK TABLESによって追加されたテーブル レベルのロックを解除できません。テーブル ロックを解除するには、 UNLO​​CK TABLESを使用する必要があります。

7. デッドロックについて

MyISAM テーブル ロックはデッドロック フリーです。MyISAM は常に必要なすべてのロックを一度に取得し、それらすべてを満たすか待機するかのいずれかを行うため、デッドロックは発生しません。しかし、InnoDB では、単一の SQL 文で構成されるトランザクションを除き、ロックが段階的に取得されるため、InnoDB でデッドロックが発生する可能性があります。

デッドロックが発生すると、InnoDB は通常それを自動的に検出し、1 つのトランザクションにロックを解除させてロールバックさせ、もう 1 つのトランザクションにロックを取得させてトランザクションの完了を続行させます。ただし、外部ロックまたはテーブル ロックが関係する場合、InnoDB はデッドロックを自動的に検出できません。これは、ロック待機タイムアウト パラメータinnodb_lock_wait_timeoutを設定して解決する必要があります。

一般的に、デッドロックはアプリケーション設計の問題です。ほとんどのデッドロックは、ビジネス プロセス、データベース オブジェクトの設計、トランザクション サイズ、データベースにアクセスするための SQL ステートメントを調整することで回避できます。以下に、例を挙げてデッドロックを回避する一般的な方法をいくつか示します。

(1) アプリケーションでは、異なるプログラムが複数のテーブルに同時にアクセスする場合、同じ順序でテーブルにアクセスするようにする必要があります。これにより、デッドロックの可能性が大幅に減少します。

（２）プログラムがバッチ処理でデータを処理するとき、各スレッドが一定の順序でレコードを処理するように事前にデータをソートしておけば、デッドロックの可能性を大幅に減らすことができます。

（３）トランザクションにおいて、レコードを更新する場合は、まず共有ロックを申請し、更新時に排他ロックを申請するのではなく、十分なレベルのロック、つまり排他ロックを直接申請する必要があります。これは、ユーザーが排他ロックを申請した時点で、他のトランザクションが既に同じレコードに対して共有ロックを取得している可能性があり、ロックの競合やデッドロックが発生する可能性があるためです。

（4） REPEATABLE-READ分離レベルでは、2つのスレッドが同時にSELECT...FOR UPDATEを使用して同じ条件レコードに排他ロックを追加すると、条件を満たすレコードがない場合、両方のスレッドがレコードを正常にロックします。プログラムはレコードがまだ存在しないことを検出し、新しいレコードを挿入しようとします。 2 つのスレッドがこれを行うと、デッドロックが発生します。この場合、分離レベルをREAD COMMITTED に変更すると問題を回避できます。

（５）分離レベルがREAD COMMITTEDの場合、両方のスレッドが最初にSELECT...FOR UPDATEを実行して条件を満たすレコードがあるかどうかを確認し、ない場合はレコードが挿入されます。この時点では、挿入に成功できるのは 1 つのスレッドのみで、他のスレッドはロックを待機します。最初のスレッドが送信すると、2 番目のスレッドは主キーが原因でエラーになりますが、このスレッドはエラーが発生しても排他ロックを取得します。このとき、3 番目のスレッドが再度排他ロックを申請すると、デッドロックが発生します。この場合、挿入操作を直接実行してから主キー重複例外をキャッチするか、主キー重複エラーが発生した場合は必ず ROLLBACK を実行して取得した排他ロックを解放します。

MySQL 関連のコンテンツにさらに興味がある読者は、次のトピックを確認してください: 「MySQL データベース ロック関連スキルの概要」、「MySQL ストアド プロシージャ スキルの概要」、「MySQL 共通関数の概要」、「MySQL ログ操作スキルの概要」、および「MySQL トランザクション操作スキルの概要」。

この記事が皆様のMySQLデータベース設計に役立つことを願っています。