MySQL ロック（テーブルロック、行ロック、共有ロック、排他ロック、ギャップロック）の詳細な説明

現実世界では、鍵は外の世界から身を隠したいときに使用するツールです。コンピュータでは、複数のプロセスまたはグループが同時にリソースにアクセスできるように調整するメカニズムです。データベースでは、従来のコンピューティング リソース (CPU、RAM、I/O など) の競合に加えて、データは多くのユーザーによって共有され、アクセスされるリソースでもあります。同時データ アクセスの一貫性と有効性をどのように確保するかは、すべてのデータベースが解決しなければならない問題です。ロックの競合も、データベースの同時アクセス パフォーマンスに影響を与える重要な要素です。この観点から、ロックはデータベースにとって特に重要です。

MySQL ロック

他のデータベースと比較すると、MySQL のロック メカニズムは比較的単純です。最も注目すべき特徴は、異なるストレージ エンジンが異なるロック メカニズムをサポートしていることです。ストレージ エンジンに応じて、MySQL のロックの特性は次のように大まかにまとめることができます。

オーバーヘッド、ロック速度、デッドロック、粒度、同時実行パフォーマンス

• テーブルロック: オーバーヘッドが低く、ロックが高速、デッドロックなし、強力なロック、ロック競合の可能性が高い、同時実行性が最も低い

• 行ロック: オーバーヘッドが高く、ロックが遅い。デッドロックが発生する可能性がある。ロックの粒度が小さい。ロック競合の可能性が低い。同時実行性が高い。

• ページロック: コストとロック速度はテーブルロックと行ロックの中間です。デッドロックが発生する可能性があります。ロックの粒度はテーブルロックと行ロックの中間で、同時実行性は平均的です。

上記の特性から、どのロックが最適であるかを一概に言うことは困難です。特定のアプリケーションの特性に基づいて、どのロックがより適しているかを言うことしかできません。ロックの観点から見ると:

テーブル ロックは、クエリ ベースで、インデックス条件に従って少量のデータのみを更新するアプリケーションに適しています。行ロックは、インデックス条件と同時クエリに従って、少量の異なるデータを多数同時に更新するアプリケーションに適しています。 (追記: BDB は InnoDB に置き換えられたため、ここでは MyISAM テーブル ロックと InnoDB 行ロックの問題のみを説明します)

MyISAM テーブルロック

MyISAM ストレージ エンジンはテーブル ロックのみをサポートします。これは、MySQL の最初の数バージョンでサポートされる唯一のロック タイプです。アプリケーションのトランザクション整合性と同時実行性に対する要件が高まり続けるにつれて、MySQL はトランザクションベースのストレージ エンジンの開発を開始しました。その後、ページ ロックをサポートする BDB ストレージ エンジンと行ロックをサポートする InnoDB ストレージ エンジンが徐々に登場しました (InnoDB は実際には別の会社であり、現在は Oracle に買収されています)。ただし、MyISAM テーブル ロックは依然として最も広く使用されているロック タイプです。このセクションでは、MyISAM テーブル ロックの使用について詳しく説明します。

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

システム上のテーブル ロックの競合は、table_locks_waited および table_locks_immediate ステータス変数を調べることで分析できます。

mysql> 'table%' のようなステータスを表示します。
+-----------------------+-------+
| 変数名 | 値 |
+-----------------------+-------+
| テーブルロック即時 | 2979 |
| テーブルロック待機数 | 0 |
+-----------------------+-------+
セット内の行数は 2 です (0.00 秒))

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

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

MySQL テーブル レベルのロックには、テーブル共有読み取りロック (テーブル読み取りロック) とテーブル排他書き込みロック (テーブル書き込みロック) の 2 つのモードがあります。ロック モードの互換性は次の表に示します。

MySQL のテーブルロックの互換性

MyISAM テーブルに対する読み取り操作は、同じテーブルに対する他のユーザーの読み取り要求をブロックしませんが、同じテーブルに対する書き込み要求をブロックすることがわかります。MyISAM テーブルに対する書き込み操作は、同じテーブルに対する他のユーザーの読み取り操作と書き込み操作をブロックします。MyISAM テーブルの読み取り操作と書き込み操作、および書き込み操作自体は、シリアルです。次の表に示す例から、スレッドがテーブルに対する書き込みロックを取得すると、ロックを保持しているスレッドだけがテーブルを更新できることがわかります。他のスレッドの読み取りおよび書き込み操作は、ロックが解除されるまで待機します。

MyISAM ストレージ エンジンの書き込みブロック読み取りの例

テーブルロックを追加する方法

クエリ ステートメント (SELECT) を実行する前に、MyISAM は関連するすべてのテーブルに読み取りロックを自動的に追加します。更新操作 (UPDATE、DELETE、INSERT など) を実行する前に、関連するテーブルに書き込みロックを自動的に追加します。このプロセスでは、ユーザーの介入は必要ありません。したがって、通常、ユーザーは LOCK TABLE コマンドを使用して MyISAM テーブルを直接明示的にロックする必要はありません。この本の例では、明示的なロックは主に利便性のためであり、必須ではありません。

MyISAM テーブルを明示的にロックする目的は、トランザクション操作をある程度シミュレートし、特定の時点で複数のテーブルの一貫した読み取りを実現することです。たとえば、各注文の合計金額を記録する注文テーブル orders があり、各注文の各製品の小計金額を記録する注文詳細テーブル order_detail もあります。これら 2 つのテーブルの合計金額が一致するかどうかを確認するには、次の 2 つの SQL 文を実行する必要があります。

注文から合計を選択します。
order_detail から sum(subtotal) を選択します。

このとき、最初に 2 つのテーブルをロックしないと、最初のステートメントの実行中に order_detail テーブルが変更されている可能性があるため、誤った結果が発生する可能性があります。したがって、正しい方法は次のとおりです。

ローカルで読み取り可能な orders テーブルと、ローカルで読み取り可能な order_detail テーブルをロックします。
注文から合計を選択します。
order_detail から sum(subtotal) を選択します。
テーブルのロックを解除します。

特に以下の2点について説明が必要です。

• 上記の例では、LOCK TABLES 時に「local」オプションが追加されています。その機能は、MyISAM テーブルの同時挿入条件が満たされた場合に、他のユーザーが MyISAM テーブルの末尾に同時にレコードを挿入できるようにすることです。MyISAM テーブルの同時挿入の問題については、次の章でさらに詳しく説明します。

• LOCK TABLES を使用してテーブルを明示的にロックする場合は、関係するすべてのテーブルのロックを同時に取得する必要がありますが、MySQL はロックのエスカレーションをサポートしていません。つまり、LOCK TABLES を実行した後は、明示的にロックされたテーブルにのみアクセスでき、ロックされていないテーブルにはアクセスできません。同時に、読み取りロックが追加された場合は、クエリ操作のみ実行でき、更新操作は実行できません。実際、これは自動ロックの基本的なケースです。MyISAM は常に、SQL ステートメントに必要なすべてのロックを一度に取得します。これが、MyISAM テーブルがデッドロックフリーである理由です。

次の表に示す例では、セッションは LOCK TABLE コマンドを使用して film_text テーブルに読み取りロックを追加します。このセッションはロックされたテーブル内のレコードを照会できますが、他のテーブルを更新またはアクセスするとエラーが発生します。同時に、別のセッションはテーブル内のレコードを照会できますが、更新するとロック待機が発生します。

MyISAM ストレージ エンジンの読み取りブロック書き込みの例

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

（１）アクターテーブルの読み取りロックを取得する：

mysql> lock table actor read;
クエリは正常、影響を受けた行は 0 行 (0.00 秒)

（２）ただし、エイリアス経由でアクセスするとエラーが発生します。

mysql> アクター a、アクター b から a.first_name、a.last_name、b.first_name、b.last_name を選択します。ここで、a.first_name = b.first_name かつ a.first_name = 'Lisa' かつ a.last_name = 'Tom' かつ a.last_name <> b.last_name;
エラー 1100 (HY000): テーブル 'a' は LOCK TABLES でロックされていません

（３）エイリアスは別途ロックする必要がある：

mysql> テーブル actor を a として読み取り、 actor を b として読み取りとしてロックします。
クエリは正常、影響を受けた行は 0 行 (0.00 秒)

（4）エイリアスによるクエリは正しく実行できます。

mysql> アクター a、アクター b から a.first_name、a.last_name、b.first_name、b.last_name を選択します。ここで、a.first_name = b.first_name かつ a.first_name = 'Lisa' かつ a.last_name = 'Tom' かつ a.last_name <> b.last_name;
+------------+------------+------------+------------+
| 名 | 姓 | 名 | 姓 |
+------------+------------+------------+------------+
| リサ | トム | リサ | モンロー |
+------------+------------+------------+------------+

セット内の 1 行 (0.00 秒)

同時挿入

MyISAM テーブルの読み取りと書き込みはシリアルであると上で述べましたが、これは一般的な話です。特定の条件下では、MyISAM テーブルは同時クエリおよび挿入操作もサポートします。

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

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

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

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

次の表に示す例では、session_1 がテーブルの READ LOCAL ロックを取得します。スレッドはテーブルを照会できますが、更新することはできません。他のスレッド (session_2) はテーブルを削除または更新することはできませんが、テーブルに対して同時挿入操作を実行できます。テーブルにホールがないものと想定されます。

MyISAM ストレージ エンジンの読み取りと書き込み (INSERT) の同時実行例

MyISAM ストレージ エンジンの同時挿入機能を使用すると、アプリケーション内の同じテーブルに対するクエリと挿入のロック競合を解決できます。たとえば、concurrent_insert システム変数を 2 に設定すると、同時挿入が常に許可されます。同時に、システムのアイドル期間中に OPTIMIZE TABLE ステートメントを定期的に実行することで、スペースの断片化が解消され、削除されたレコードによって作成されたギャップが再利用されます。 OPTIMIZE TABLE ステートメントの詳細な説明については、第 18 章の「2 つのシンプルで実用的な最適化方法」のセクションを参照してください。

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

前述したように、MyISAM ストレージ エンジンの読み取りロックと書き込みロックは相互に排他的であり、読み取り操作と書き込み操作はシリアルで実行されます。では、あるプロセスが MyISAM テーブルに対して読み取りロックを要求し、別のプロセスも同じテーブルに対して書き込みロックを要求した場合、MySQL はそれをどのように処理するのでしょうか?答えは、書き込みプロセスが最初にロックを取得するということです。それだけでなく、読み取り要求が最初にロック待機キューに到着し、書き込み要求が後に到着した場合でも、読み取りロック要求の前に書き込みロックが挿入されます。これは、MySQL では一般に書き込み要求が読み取り要求よりも重要であるとみなされるためです。これは、MyISAM テーブルが更新操作やクエリ操作が大量に発生するアプリケーションに適していない理由でもあります。更新操作が大量に発生すると、クエリ操作で読み取りロックを取得することが困難になり、クエリ操作が永久にブロックされる可能性があるためです。この状況は時々本当に悪くなることがあります。幸いなことに、いくつかの設定を通じて MyISAM のスケジュール動作を調整できます。

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

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

• INSERT、UPDATE、または DELETE ステートメントの LOW_PRIORITY 属性を指定すると、ステートメントの優先度を下げることができます。

上記の 3 つの方法は、更新優先またはクエリ優先の方法のいずれかですが、クエリが比較的重要なアプリケーション (ユーザー ログイン システムなど) での読み取りロック待機という深刻な問題を解決するために使用できます。

さらに、MySQL は、読み取りと書き込みの競合を調整するための妥協策として、システム パラメータ max_write_lock_count に適切な値を設定する方法も提供しています。テーブルの読み取りロックがこの値に達すると、MySQL は書き込み要求の優先度を一時的に下げて、読み取りプロセスにロックを取得する一定の機会を与えます。

書き込み優先スケジューリング メカニズムによってもたらされる問題と解決策については、上記で説明しました。ここでもう 1 つ強調する必要がある点は、実行に長い時間を要する一部のクエリ操作によっても、書き込みプロセスが「枯渇」するということです。したがって、アプリケーションでは、実行時間の長いクエリ操作はできる限り避ける必要があります。一見賢いように見える SELECT ステートメントは複雑で、実行に時間がかかることが多いため、常に SELECT ステートメントを使用して問題を解決しようとしないでください。可能な場合は、中間テーブルやその他の手段を使用して SQL ステートメントを「分解」し、各クエリ ステップをより短時間で完了できるようにすることで、ロックの競合を減らすことができます。複雑なクエリが避けられない場合は、データベースのアイドル時間中に実行されるようにスケジュールする必要があります。たとえば、定期的な統計の一部を夜間に実行するようにスケジュールできます。

InnoDB ロックの問題

InnoDB と MyISAM には 2 つの大きな違いがあります。1 つはトランザクション (TRANSACTION) をサポートしていること、もう 1 つは行レベルのロックを使用していることです。行レベル ロックとテーブル レベル ロックには多くの違いがあります。さらに、トランザクションの導入によっていくつかの新しい問題も発生します。以下では、まず背景知識を紹介し、次に InnoDB ロックの問題について詳しく説明します。

背景

1.トランザクションとそのACID特性

トランザクションは、SQL ステートメントのグループで構成される論理的な処理単位です。トランザクションには次の 4 つのプロパティがあり、これらは通常、トランザクションの ACID プロパティと呼ばれます。

• アトミック性: トランザクションはアトミックな操作単位であり、データに対する変更はすべて実行されるか、まったく実行されないかのいずれかになります。

• 一貫性: トランザクションの開始時と完了時に、データは一貫した状態を維持する必要があります。つまり、データの整合性を維持するために、関連するすべてのデータ ルールをトランザクションの変更に適用する必要があります。トランザクションの終了時には、すべての内部データ構造 (B ツリー インデックスや二重リンク リストなど) も正しくなければなりません。

• 分離: データベース システムは、トランザクションが外部の同時操作の影響を受けない「独立した」環境で実行されるようにするための特定の分離メカニズムを提供します。つまり、トランザクション処理中の中間状態は外部からは見えず、その逆も同様です。

• 耐久性: トランザクションが完了すると、データへの変更は永続的になり、システム障害が発生しても維持できます。

銀行振込は取引の典型的な例です。

2.同時トランザクション処理の問題

シリアル処理と比較して、同時トランザクション処理ではデータベース リソースの使用率が大幅に向上し、データベース システムのトランザクション スループットが向上するため、より多くのユーザーをサポートできます。ただし、同時トランザクション処理には、主に次のような状況を含むいくつかの問題も発生します。

• 更新の損失: 2 つ以上のトランザクションが同じ行を選択し、最初に選択した値に基づいて行を更新すると、各トランザクションが他のトランザクションの存在を認識しないため、更新の損失問題が発生します。つまり、最後の更新によって、他のトランザクションによって行われた更新が上書きされます。たとえば、2 人の編集者が同じドキュメントの電子コピーを作成します。各編集者は自分のコピーを個別に変更し、変更したコピーを保存して、元のドキュメントを上書きします。最後に変更のコピーを保存した編集者は、他の編集者が行った変更を上書きします。この問題は、あるエディターがトランザクションを完了してコミットするまで、別のエディターが同じファイルにアクセスできない場合に回避できます。

• ダーティ リード:トランザクションがレコードを変更しています。トランザクションが完了してコミットされるまで、レコードのデータは不整合な状態にあります。このとき、別のトランザクションも同じレコードを読み取ります。制御されていない場合、2 番目のトランザクションは「ダーティ」データを読み取り、それに基づいてさらに処理を実行し、コミットされていないデータ依存関係を生成します。この現象は、はっきりと「ダーティ リード」と呼ばれます。

• 反復不可能な読み取り:トランザクションは、データを読み取った後、特定の時間にそのデータを読み取り、次に以前に読み取ったデータを読み取ると、読み取ったデータが変更されていたり、一部のレコードが削除されていたりすることがわかります。この現象は「非反復読み取り」と呼ばれます。

• ファントム リード:トランザクションが同じクエリ条件を使用して以前に取得したデータを再読み取りしたときに、他のトランザクションがそのクエリ条件を満たす新しいデータを挿入したことがわかった場合、この現象は「ファントム リード」と呼ばれます。

3.トランザクション分離レベル

前述の同時トランザクション処理によって発生する問題のうち、「更新損失」は通常完全に回避されるはずです。ただし、更新損失の防止は、データベース トランザクション コントローラだけでは解決できません。アプリケーションは、更新するデータに必要なロックを追加する必要があります。したがって、更新損失の防止はアプリケーションの責任になります。

「ダーティ リード」、「非反復リード」、および「ファントム リード」は、実際にはデータベースの読み取り一貫性の問題であり、特定のトランザクション分離メカニズムを提供するデータベースによって解決する必要があります。データベースがトランザクション分離を実装する方法は、基本的に次の 2 種類に分けられます。

• 1 つは、他のトランザクションがデータを変更できないように、データを読み取る前にロックすることです。

• もう 1 つの方法は、ロックを追加せずに特定のメカニズムを通じてデータ要求時点で一貫性のあるデータ スナップショット (スナップショット) を生成し、このスナップショットを使用して特定のレベル (ステートメント レベルまたはトランザクション レベル) で一貫した読み取りを提供することです。ユーザーの観点から見ると、データベースは同じデータの複数のバージョンを提供できるように見えます。そのため、この技術は MultiVersion Concurrency Control (MVCC または MCC) と呼ばれ、マルチバージョン データベースと呼ばれることがよくあります。

データベースのトランザクション分離が厳格であればあるほど、同時実行の副作用は小さくなりますが、支払う代償は大きくなります。これは、トランザクション分離によって、本質的にトランザクションがある程度「シリアル化」されるためであり、これは明らかに「同時実行」とは矛盾します。同時に、アプリケーションによって、読み取りの一貫性とトランザクションの分離に関する要件は異なります。たとえば、多くのアプリケーションは「非反復読み取り」や「ファントム読み取り」に敏感ではなく、データに同時にアクセスできる能力を重視します。

「分離」と「同時実行」の矛盾を解決するために、ISO/ANSI SQL92 では 4 つのトランザクション分離レベルを定義しています。各レベルには異なる分離レベルがあり、異なる副作用が許容されます。アプリケーションは、ビジネス ロジックの要件に応じて異なる分離レベルを選択することで、「分離」と「同時実行」の矛盾を解消できます。次の表は、これら 4 つの分離レベルの特性をわかりやすくまとめたものです。

4つの分離レベルの比較

最後に、各データベースが必ずしも上記の 4 つの分離レベルを完全に実装しているわけではないことに注意してください。たとえば、Oracle は、Read committed と Serializable の 2 つの標準分離レベルのみを提供し、独自に定義された Read only 分離レベルも提供します。SQL Server は、ISO/ANSI SQL92 で定義された上記の 4 つの分離レベルをサポートすることに加えて、「スナップショット」と呼ばれる分離レベルもサポートしていますが、厳密に言えば、これは MVCC で実装された Serializable 分離​​レベルです。 MySQL は 4 つの分離レベルすべてをサポートしていますが、実装にはいくつかの特殊性があります。たとえば、MVCC 一貫性読み取りは一部の分離レベルで使用されますが、他の分離レベルでは使用されません。これらの内容については、次の章でさらに詳しく説明します。

InnoDB 行ロック競合ステータスを取得する

InnoDB_row_lock ステータス変数をチェックすることで、システム上の行ロックの競合を分析できます。

mysql> 'innodb_row_lock%' のようなステータスを表示します。
+---------------------------------+-------+
| 変数名 | 値 |
+---------------------------------+-------+
| InnoDB_row_lock_current_waits | 0 |
| InnoDB_行ロック時間 | 0 |
| InnoDB_行ロック時間平均 | 0 |
| InnoDB_行ロック時間最大 | 0 |
| InnoDB_行ロック待機 | 0 |
+---------------------------------+-------+
セット内の 5 行 (0.01 秒)

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

具体的な方法は以下の通りです。

mysql> innodb_monitor テーブルを作成します(a INT) ENGINE=INNODB;
クエリは正常、影響を受けた行は 0 行 (0.14 秒)

次に、次のステートメントを使用してそれを表示できます。

mysql> innodb ステータスを表示します\G;
************************** 1. 行 ****************************
タイプ: InnoDB
名前：
状態：
…
…
------------
取引
------------
トランザクションIDカウンター 0 117472192
トランザクション番号 0 未満のパージが完了しました 117472190 元に戻す 0 0
履歴リストの長さ 17
行ロック ハッシュ テーブル内のロック構造体の合計数 0
各セッションのトランザクションのリスト:
---トランザクション 0 117472185、開始されていません、プロセス番号 11052、OS スレッド ID 1158191456
MySQL スレッド ID 200610、クエリ ID 291197 ローカルホスト ルート
---トランザクション 0 117472183、開始されていません、プロセス番号 11052、OS スレッド ID 1158723936
MySQL スレッド ID 199285、クエリ ID 291199 ローカルホスト ルート
InnoDBステータスを表示
…

次のステートメントを発行すると、モニターを停止できます。

mysql> innodb_monitor テーブルを削除します。
クエリは正常、影響を受けた行は 0 行 (0.05 秒)

モニターを設定すると、SHOW INNODB STATUS の表示内容に、テーブル名、ロックの種類、ロックされたレコードのステータスなど、現在のロック待機に関する詳細情報が含まれるようになり、さらなる分析や問題の判別に役立ちます。モニターをオンにすると、デフォルトでは 15 秒ごとに監視内容がログに記録されます。長時間オンにすると、.err ファイルが非常に大きくなります。そのため、問題の原因を確認した後、ユーザーは監視テーブルを削除してモニターをオフにするか、"--console" オプションを使用してサーバーを起動してログ ファイルの書き込みをオフにすることを忘れないようにしてください。

InnoDB の行ロック モードとロック方法

InnoDB は次の 2 種類の行ロックを実装します。

• 共有ロック (S): 1 つのトランザクションが行を読み取ることを許可し、他のトランザクションが同じデータ セットに対して排他ロックを取得することを防ぎます。

• 排他ロック (X): 排他ロックを取得したトランザクションがデータを更新できるようにし、他のトランザクションが同じデータ セットに対して共有読み取りロックや排他書き込みロックを取得することを防ぎます。さらに、行ロックとテーブルロックを共存させ、多粒度ロック機構を実装するために、InnoDB には内部的に使用される 2 つのインテンション ロック (Intention Locks) があり、どちらもテーブル ロックです。

• 意図的な共有ロック (IS): トランザクションは、データ行に行共有ロックを追加することを意図しています。トランザクションは、データ行に共有ロックを追加する前に、まずテーブルの IS ロックを取得する必要があります。

• 意図的な排他ロック (IX): トランザクションはデータ行に排他ロックを追加することを意図しています。トランザクションは、データ行に排他ロックを追加する前に、まずテーブルの IX ロックを取得する必要があります。

上記のロックモードの互換性は次の表に示されています。

InnoDB 行ロック モード互換性リスト

トランザクションによって要求されたロック モードが現在のロックと互換性がある場合、InnoDB は要求されたロックをトランザクションに付与します。そうでない場合、つまり 2 つに互換性がない場合、トランザクションはロックが解放されるまで待機します。

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

• 共有ロック: SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE。

• 排他ロック (X): SELECT * FROM table_name WHERE ... FOR UPDATE。

共有ロックを取得するには、SELECT ... IN SHARE MODE を使用します。これは主に、データの依存関係が必要な場合にレコードの行が存在するかどうかを確認し、このレコードに対して UPDATE または DELETE 操作を誰も実行しないようにするために使用されます。ただし、現在のトランザクションでもレコードを更新する必要がある場合は、デッドロックが発生する可能性があります。行レコードをロックした後に更新する必要があるアプリケーションの場合は、SELECT... FOR UPDATE メソッドを使用して排他ロックを取得する必要があります。

次の表に示す例では、SELECT ... IN SHARE MODE を使用してレコードをロックしてから更新し、何が起こるかを確認します。actor テーブルの actor_id フィールドが主キーです。

InnoDB ストレージ エンジンの共有ロックの例

SELECT...FOR UPDATE を使用してレコードをロックしてから更新すると、次の状況が発生します。

InnoDB ストレージ エンジンの排他ロックの例

InnoDB 行ロックの実装

INNODB行ロックは、インデックスにインデックス項目をロックすることで実装されます。これは、データブロックに対応するデータ行をロックすることで実装されています。 INNODBのRow Lockの実装機能は、INNODBがインデックス条件を通じてデータが取得された場合にのみ行レベルのロックを使用することを意味します。

実際のアプリケーションでは、INNODBの列ロックのこの機能に特に注意を払う必要があります。そうでない場合、多数のロック競合が発生する可能性があるため、同時性のパフォーマンスに影響します。以下は、これを説明するためのいくつかの実用的な例です。

（1）インデックス条件なしでクエリをする場合、InnoDBは行ロックの代わりにテーブルロックを使用します。

次の例では、TAB_NO_INDEXテーブルには、最初にインデックスがありません。

mysql>作成テーブルtab_no_index（id int、name varchar（10））エンジン= innodb;
クエリは正常、影響を受けた行は 0 行 (0.15 秒)
mysql> tab_no_index値に挿入（1、 '1'）、（2、 '2'）、（3、 '3'）、（4、 '4'）;
クエリは正常、4 行が影響を受けました (0.00 秒)
記録: 4 重複: 0 警告: 0

INNODBストレージエンジンテーブルがインデックスを使用しない場合のテーブルロックの使用例

上の表に示す例では、Session_1は1つの行に排他的ロックを追加するだけですが、Session_2が他の行に排他的ロックを要求すると、ロック待機が発生します！その理由は、インデックスがなければ、InnoDBはテーブルロックのみを使用できるからです。次の表に示すように、InnoDBはそれにインデックスを追加すると、条件を満たす行をロックするだけです。

IDフィールドに通常のインデックスを使用して、tab_with_indexテーブルを作成します。

mysql>作成テーブルtab_with_index（id int、name varchar（10））エンジン= innodb;
クエリは正常、影響を受けた行は 0 行 (0.15 秒)
mysql> table tab_with_indexを変更すると、index id（id）;
クエリOK、影響を受ける4行（0.24秒）
記録: 4 重複: 0 警告: 0

InnoDBストレージエンジンを使用してテーブルにインデックスを使用する場合の行ロックを使用する例

（2） MySQLの行ロックはインデックスに対してロックされているため、レコードではなく、同じインデックスキーを使用して異なる行のレコードにアクセスするとロック競合が発生します。アプリケーションを設計するときは、これに留意してください。

次の表に示す例では、テーブルtab_with_indexのIDフィールドにはインデックスがありますが、名前フィールドにはインデックスがありません。

mysql> Table tab_with_indexを変更するindex name;
クエリOK、影響を受ける4行（0.22秒）
記録: 4 重複: 0 警告: 0
mysql> tab_with_index値に挿入（1、 '4'）;
クエリは正常、1 行が影響を受けました (0.00 秒)
mysql> select * from tab_with_index where id = 1;
+------+------+
| ID | 名前 |
+------+------+
| 1 | 1 |
| 1 |
+------+------+
セット内の 2 行 (0.00 秒)

INNODBストレージエンジンブロッキング例同じインデックスキーを使用して

（3）テーブルには複数のインデックスがある場合、異なるトランザクションは、異なるインデックスを使用して、プライマリキーインデックス、一意のインデックス、または通常のインデックスが使用されるかどうかにかかわらず、INNODBはRow Locksを使用してデータをロックします。

次の表に示す例では、テーブルTab_with_indexのIDフィールドにはプライマリキーインデックスがあり、名前フィールドには通常のインデックスがあります。

mysql> Table tab_with_indexを変更すると、index name（name）;
クエリOK、5行の影響を受ける（0.23秒）
記録: 5 重複: 0 警告: 0

INNODBストレージエンジンテーブルに異なるインデックスを使用するブロック例

（4）インデックスフィールドが条件で使用されていても、MySQLがより効率的であると考えている場合、MySQLのコストを判断することにより、インデックスを使用してMySQLによって決定されます。したがって、ロックの競合を分析するときは、SQLの実行計画を確認して、インデックスが実際に使用されているかどうかを確認することを忘れないでください。

次の例では、取得した値のデータ型はインデックスフィールドとは異なりますが、MySQLはデータ型変換を実行できません。これは、2つのSQLの実行計画を説明して明確に見ることができます。

この例では、TAB_With_Indexテーブルの名前フィールドにはインデックスがありますが、名前フィールドはVARCHARタイプと比較されていない場合、名前は型で構成され、完全なテーブルスキャンが実行されます。

mysql> Table tab_no_indexを変更すると、index name（name）;
クエリOK、4行の影響を受ける（8.06秒）
記録: 4 重複: 0 警告: 0
mysql> select * from tab_with_index fromここでname = 1 \ g
************************** 1. 行 ****************************
id: 1
選択タイプ: シンプル
表：tab_with_index
タイプ: すべて
baseid_keys：name
キー: NULL
キー長さ: NULL
参照: NULL
行数: 4
追加: where の使用
セット内の 1 行 (0.00 秒)
mysql> select * from tab_with_index fromここで、name = '1' \ g
************************** 1. 行 ****************************
id: 1
選択タイプ: シンプル
表：tab_with_index
タイプ: ref
baseid_keys：name
キー：名前
key_len：23
参照: 定数
行数: 1
追加: where の使用
セット内の 1 行 (0.00 秒)

ギャップロック（次のキーロック）

データを取得して共有または排他的なロックを要求する範囲の条件を使用すると、条件範囲内にあるが存在しない条件を満たす既存のデータレコードのインデックス条件をロックします。

たとえば、emp テーブルに 101 件のレコードしかなく、empid 値がそれぞれ 1、2、...、100、101 の場合、次の SQL になります。

select * from empid> 100を更新してください。

これは範囲条件検索です。InnoDB は、条件を満たす empid 値が 101 のレコードをロックするだけでなく、empid 値が 101 より大きい「ギャップ」(これらのレコードは存在しません) もロックします。

GAPロックを使用したInnoDBの目的は、ファントムの測定値を防ぎ、上記の例の要件を満たすことです。ロックメカニズムに対する回復と複製の影響、およびさまざまな分離レベルでのINNODBでのギャップロックの使用について、さらに導入は後続の章で説明されます。

当然のことながら、範囲条件を使用してレコードを取得およびロックする場合、InnoDB のロック メカニズムによって、条件を満たすキー値の同時挿入がブロックされ、深刻なロック待機が発生することがよくあります。したがって、実際のアプリケーション開発、特により並行した挿入を備えたアプリケーションでは、ビジネスロジックを最適化し、等しい条件を使用してデータにアクセスして更新し、スコープ条件の使用を避けるようにしてください。

また、範囲の条件をロックするときにギャップロックを使用することに加えて、INNODBは、存在しないレコードをロックするために等しい条件が要求する場合、ギャップロックも使用することに注意する必要があります。

以下の表に示す例では、EMPテーブルに101のレコードしかない場合、それらのEMPIDの値はそれぞれ1、2、...、100、101です。

InnoDBストレージエンジンのギャップロックブロッキングの例

回復と複製の必要性、InnoDBロックメカニズムへの影響

MySQLは、BINLOGを介して挿入、更新、削除などのデータを更新するための成功したSQLステートメントを実行し、MySQLデータベースの回復とマスタースレーブの複製を実現します（この本の「管理」の紹介を参照）。 MySQL回復メカニズム（複製は実際には奴隷MySQLにおける継続的なビンログベースの回復です）には、次の特性があります。

l最初に、MySQL回復はSQLステートメントレベルです。つまり、BINLOGのSQLステートメントを再実行します。これは、データベースファイルブロックに基づいたOracleデータベースとは異なります。

l第二に、MySQLのビンログはトランザクション提出の順序で記録され、回復もこの順序で実行されます。これは、Oracleがシステムの変更番号（SCN）に応じてデータを回復することとは異なります。

上記の2つのポイントから、MySQLの回復メカニズムでは、トランザクションがコミットされる前に、他の同時トランザクションがロック条件を満たすレコードを挿入できないことを要求しています。これは、InnoDBが多くの場合、範囲条件でレコードを更新する場合、readedまたは繰り返し読み取り可能な分離レベルでギャップロックを使用する必要がありますが、次のセクションではInnoDBのロックの違いが導入されません。

さらに、「Source_Tab Whereから[ターゲットに挿入]をselectに挿入してください...」や「table new_tab ... select ... from source_tab where ...（ctas）」などのsqlステートメントの場合、ユーザーはsource_tabで更新操作を行いませんでしたが、mysqlはこの種のsqlステートメントを特別に処理しています。まず、下の表の例を見てみましょう。

元のテーブルをロックするCTAS操作の例

上記の例では、Source_Tabテーブルのデータを単に読み取るだけで、通常の選択ステートメントの実行と同等であり、一貫して読むだけです。 Oracleは、MVCCテクノロジーによって実装されたマルチバージョンデータを使用して、source_tabにロックを追加せずに使用します。 InnoDBはマルチバージョンデータも実装しており、通常の選択を一貫して読み取るためにロックを必要としません。

なぜmysqlはこれを行うのですか？その理由は、回復と複製の正確性を確保するためです。ロックが追加されていない場合、他のトランザクションが上記のステートメントの実行中にsource_tabを更新すると、データ回復の結果にエラーが発生する可能性があるためです。これを実証するには、session_1がトランザクションを実行する前に、innodb_locks_unsafe_for_binlogを「on」に設定することです（そのデフォルト値がオフになります）。

元のテーブルをロックしないCTAS操作によって引き起こされるセキュリティ問題の例

上記からわかるように、システム変数INNODB_LOCKS_UNSAFE_FOR_BINLOGの値を「ON」に設定した後、INNODBはsource_tabをロックしなくなり、結果はアプリケーションロジックにも沿っていますが、Binlogの内容を分析する場合：

......
SET TIMESTAMP = 1169175130;
始める;
＃at 274
＃070119 10:51:57サーバーID 1 end_log_pos 105 query thread_id = 1 exec_time = 0 error_code = 0
SET TIMESTAMP = 1169175117;
surce_tab set name = '8' where name = '1'を更新します。
＃379
＃070119 10:52:10サーバーID 1 end_log_pos 406 xid = 5
専念;
＃406
＃070119 10:52:14サーバーID 1 end_log_pos 474 query thread_id = 2 exec_time = 0 error_code = 0
SET TIMESTAMP = 1169175134;
始める;
＃at 474
＃070119 10:51:29サーバーID 1 end_log_pos 119 query thread_id = 2 exec_time = 0 error_code = 0
SET TIMESTAMP = 1169175089;
ターゲットに挿入ターゲットに挿入d1を選択し、source_tabからname where name = '1';
＃at 593
＃070119 10:52:14サーバーID 1 end_log_pos 620 xid = 7
専念;
......

Binlogでは、更新操作の場所は挿入前にあります...このビンログがデータベースの回復に使用される場合、リカバリの結果は実際のアプリケーションロジックと一致しません。

上記の例を使用すると、「source_tab select * from source_tab where ...」および「select new_tab ... select ... select ... select ... from select ...」を使用する代わりに、source_tab select * from select * from select *を作成する際にmysqlがsource_tabをロックする理由を理解することは難しくありません。また、上記のステートメントの選択が範囲条件である場合、INNODBはソーステーブルにギャップロック（次のロック）も追加することに注意する必要があります。

したがって、挿入...選択...および作成テーブルの作成...選択...ステートメントは、ソーステーブルの同時の更新を防ぎ、ソーステーブルロックを待機させる可能性があります。クエリが複雑な場合、それは深刻なパフォーマンスの問題を引き起こし、アプリケーションでそれを使用しないようにする必要があります。実際、MySQLはこのタイプのSQLを非決定的なSQLと呼び、推奨されません。

このSQLを使用してアプリケーションにビジネスロジックを実装する必要があり、ソーステーブルの同時更新に影響を与えたくない場合は、次の2つの測定値を取得できます。

• 最初に、上記の例でプラクティスを採用し、innodb_locks_unsafe_for_binlogの値を「on」に設定し、mysqlにマルチバージョンデータを使用して一貫して読み取ります。しかし、価格は、データがBinlogで適切に回復またはコピーされない可能性があるため、この方法は推奨されません。

• 2つ目は、「Select * From Source_Tab ... Into Outfile」と「Load Data Infile ...」を組み合わせて間接的に実装することです。

異なる分離レベルでのInnoDBの一貫した読み取りとロックの違い

前述のように、ロックとマルチバージョンデータは、INNODBが一貫した読み取りとISO/ANSI SQL92の分離レベルを実装する手段です。同時に、データの回復および複製メカニズムの特性は、SQLの一貫した読み取り戦略とロック戦略にも大きな影響を与えます。これらの特性は、読者が表示するために、次の表に示されているコンテンツにまとめられています。

InnoDBストレージエンジンの異なる分離レベルでの異なるSQLでのロックの比較

上記の表からわかるように：多くのSQLでは、分離レベルが高くなるほど、InnoDBがレコードセットに追加されるロック（特にスコープ条件を使用する場合）にロックが追加されるほど、ロック競合の可能性が高くなり、したがって、同時トランザクションのパフォーマンスへの影響が大きくなります。したがって、当社のアプリケーションでは、より低い分離レベルを使用して、ロックの競合の可能性を減らすようにする必要があります。実際、トランザクションロジックを最適化することにより、ほとんどのアプリケーションが読み取り委員会の分離レベルを使用するだけで十分です。より高い分離レベルを必要とする一部のトランザクションでは、セットセッショントランザクション分離レベルの再現可能な読み取りまたはセットセッショントランザクションレベルシリアル化可能なセットセットセッションレベルを実行することにより、分離レベルを動的に変更することにより、要件を満たすことができます。

テーブルロックを使用するタイミング

InnoDB テーブルの場合、ほとんどの場合、行レベルのロックを使用する必要があります。これは、トランザクションと行ロックが InnoDB テーブルを選択する理由となることが多いためです。ただし、個々の特別なトランザクションでは、テーブルレベルのロックも考慮することができます。

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

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

もちろん、アプリケーションにこれら2つのトランザクションの多くがあまりにも存在しないはずです。そうしないと、Myisamテーブルの使用を検討する必要があります。

InnoDB では、テーブルロックを使用する際に次の 2 つの点に注意してください。

（1）テーブルはInnodbにテーブルレベルのロックを追加できますが、テーブルロックはInnodbストレージエンジンレイヤーによって管理されていないことに注意する必要がありますケース、InnoDBは、テーブルレベルのロックを含むデッドロックを自動的に識別できます。デッドロックについては、次のセクションで引き続き説明します。

（2）ロックテーブルを使用して、Autocommitを0に設定するように注意してください。それ以外の場合は、テーブルのロックを解除してテーブルのロックをリリースしません。正しい方法は、次のステートメントに示されています。

たとえば、テーブル t1 に書き込み、テーブル t から読み取る必要がある場合は、次のように実行できます。

AUTOCOMMIT=0 を設定します。
ロックテーブルT1書き込み、T2読み取り、...;
[ここでテーブルT1とT2を使用して何かをしてください];
専念;
テーブルのロックを解除します。

デッドロックについて

上記のように、Myisam Tableロックはデッドロックです。これは、Myisamが常に必要なすべてのロックを取得するか、待っているため、デッドロックはありません。ただし、単一のSQLで構成されるトランザクションを除いて、InnoDBでは、ロックが徐々に取得され、InnoDBでデッドロックが可能であると判断されます。以下は、デッドロックの例です。

InnoDBストレージエンジンのデッドロックの例

上記の例では、両方のトランザクションが相手が保持している排他的ロックを取得して、このサーキュラーロックが典型的なデッドロックであることです。

デッドロックが発生した後、InnoDBは一般に自動的に検出し、1つのトランザクションをロックとフォールバックにし、もう1つのトランザクションがロックを取得し、トランザクションを完了し続けます。ただし、外部ロックが関与している場合、またはテーブルロックが関与している場合、InnoDBはデッドロックを完全に検出することはできません。これは、ロック待機タイムアウトパラメーターINNODB_LOCK_WAIT_TIMEOUTを設定することで解決する必要があります。このパラメーターは、デッドロックの問題を解決するために使用されるだけではありません。必要なロックをすぐに取得できないため、多数のトランザクションが一時停止される場合、多数のコンピューターリソースを占有し、深刻なパフォーマンスの問題を引き起こし、データベースをドラッグします。適切なロック待機タイムアウトしきい値を設定することで、この状況を回避できます。

一般的に、デッドロックはアプリケーション設計の問題です。以下は、デッドロックを避けるためのいくつかの一般的な方法を導入する例です。

（1）アプリケーションでは、異なるプログラムが複数のテーブルに同時にアクセスする場合、同じ順序でテーブルにアクセスすることに同意してみてください。これにより、デッドロックの可能性が大幅に減少します。次の例では、2つのセッションでは2つのテーブルに異なる順序でアクセスできるため、デッドロックの可能性は非常に高くなります！ただし、同じ順序でアクセスすると、デッドロックを回避できます。

Innodbストレージエンジンのテーブルオーダーによって引き起こされるデッドロックの例

（2）プログラムがバッチでデータを処理する場合、データが事前にソートされている場合、各スレッドが固定順序で記録を処理することを確認すると、デッドロックの可能性を大幅に減らすことができます。

INNODBストレージエンジンのテーブルデータの一貫性のない操作順序によって引き起こされるデッドロックの例

（3）トランザクションでは、レコードを更新する場合は、十分なレベルのロック、つまり排他的ロックのロックを直接申請する必要があります。最初に共有ロックを申請しないでください。その後、ユーザーが排他的ロックを適用すると、他のトランザクションが同じレコードを取得し、ロック競合と均一なレコードを取得する可能性があります。

（4）前述のように、再現可能な読み取り分離レベルでは、2つのスレッドがSelectを使用する場合...更新のために同時に排他的ロックを追加するために、条件を満たすレコードがない場合、両方のスレッドが正常にロックされます。プログラムはレコードがまだ存在しないことを検出し、新しいレコードを挿入しようとします。 2 つのスレッドがこれを行うと、デッドロックが発生します。この場合、下に示すように、コミットされた読み物に隔離レベルを変更すると、問題を回避できます。

Innodbストレージエンジンの分離レベルによって引き起こされるデッドロックの例1

（5）隔離レベルがコミットされた場合、両方のスレッドが[最初に更新]を実行する場合、条件を満たしていない場合はレコードを挿入します。現時点では、1つのスレッドのみを正常に挿入でき、もう1つのスレッドはロックを待機します。この時点で、排他的ロックを適用する3番目のスレッドがある場合、デッドロックも発生します。

この場合、挿入操作を直接実行してから、プライマリキー再発例外をキャッチするか、プライマリキー再発エラーに遭遇した場合は、以下に示すように、常にロールバックで取得した排他的ロックを実行できます。

Innodbストレージエンジンの分離レベルによって引き起こされるデッドロックの例2

上記の設計とSQL最適化測定により、デッドロックを大幅に削減できますが、デッドロックは完全に回避することが困難です。したがって、プログラミングで常にデッドロックの例外をキャッチして処理することは、プログラミングの習慣です。

デッドロックが発生した場合、Show InnoDBステータスコマンドを使用して、最後のデッドロックの原因を決定できます。返品結果には、デッドロックをトリガーするSQLステートメント、トランザクションが取得したロック、ロックが待っているもの、ロールバックされるトランザクションなど、デッドロック関連のトランザクションに関する詳細情報が含まれます。これに基づいて、デッドロックの原因と改善尺度を分析できます。これは、show innodbステータスのサンプル出力です。

mysql> show innodb status \ g
……。
------------------------
最近検出されたデッドロック
------------------------
070710 14:05:16
*** （１）取引：
トランザクション0 117470078、アクティブ117秒、プロセス番号1468、OSスレッドID 1197328736挿入
使用中の MySQL テーブル 1、ロックされているテーブル 1
ロック待機5ロック構造体、ヒープサイズ1216
mysqlスレッドID 7521657、クエリID 673468054 localhostルートアップデート
カントリー（country_id、country）値への挿入（110、 'test'）
………
*** （２）取引：
トランザクション0 117470079、Active 39 Sec、Process No 1468、OSスレッドID 1164048736開始インデックス読み取り、Innodb 500内で宣言されたスレッド
使用中の MySQL テーブル 1、ロックされているテーブル 1
4ロック構造体、ヒープサイズ1216、ログエントリを元に戻す1
MySQLスレッドID 7521664、クエリID 673468058 localhostルート統計
first_name、last_name from Actor where actor_id = 1を更新する
*** (2) ロックを保持する:
………
*** (2) このロックが許可されるのを待機しています:
………
*** トランザクションをロールバックします (1)
…

この記事では、MySQLテーブルロック、Row Lock、共有ロック、排他的ロック、ギャップロックの詳細な使用方法について包括的に説明しています。