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

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

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

MySQL ロック

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

行ロックテーブルロックページロック
マイISAM √
イギリス√
 √
翻訳√
 √

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

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

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

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

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

テーブル ロックは、クエリ ベースで、インデックス条件に従って少量のデータのみを更新するアプリケーションに適しています。行ロックは、インデックス条件と同時クエリに従って、少量の異なるデータを多数同時に更新するアプリケーションに適しています。 (追記: 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 ストレージ エンジンの書き込みブロック読み取りの例

セッション_1セッション_2

film_textテーブルにWRITEロックを取得する

mysql> film_text テーブルへの書き込みをロックします。
クエリは正常、影響を受けた行は 0 行 (0.00 秒)

現在のセッションでは、ロックされたテーブルに対してクエリ、更新、および挿入操作を実行できます。

mysql> film_id = 1001 の film_text から film_id、title を選択します。
+---------+--------------+
| 映画ID | タイトル |
+---------+--------------+
| 1001 | 更新テスト |
+---------+--------------+
セット内の 1 行 (0.00 秒)
mysql> film_text (film_id,title) に値(1003,'Test') を挿入します。
クエリは正常、1 行が影響を受けました (0.00 秒)
mysql> film_text を更新し、title を 'Test' に設定し、film_id を 1001 に設定します。
クエリは正常、1 行が影響を受けました (0.00 秒)
一致した行: 1 変更された行: 1 警告: 0

ロックされたテーブルに対する他のセッションのクエリはブロックされ、ロックが解除されるまで待機する必要があります。

mysql> film_id = 1001 の film_text から film_id、title を選択します。

待って

ロックを解除します:

mysql> テーブルのロックを解除します。
クエリは正常、影響を受けた行は 0 行 (0.00 秒)		待って

セッション2はロックを取得し、クエリは次を返します。

mysql> film_id = 1001 の film_text から film_id、title を選択します。
+---------+-------+
| 映画ID | タイトル |
+---------+-------+
| 1001 | テスト |
+---------+-------+
セット1列目（57.59秒）

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

クエリ ステートメント (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 ストレージ エンジンの読み取りブロック書き込みの例

セッション_1セッション_2

film_textテーブルのREADロックを取得する

mysql> film_text テーブルへの書き込みをロックします。
クエリは正常、影響を受けた行は 0 行 (0.00 秒)

現在のセッションはテーブルレコードを照会できる

mysql> film_id = 1001 の film_text から film_id、title を選択します。
+---------+------------------+
| 映画ID | タイトル |
+---------+------------------+
| 1001 | アカデミー ダイナソー |
+---------+------------------+
セット内の 1 行 (0.00 秒)

他のセッションもこのテーブルのレコードを照会できる

mysql> film_id = 1001 の film_text から film_id、title を選択します。
+---------+------------------+
| 映画ID | タイトル |
+---------+------------------+
| 1001 | アカデミー ダイナソー |
+---------+------------------+
セット内の 1 行 (0.00 秒)

現在のセッションではロックされていないテーブルを照会できません

mysql> film_id = 1001 の場合、film_id、title を film から選択します。
エラー 1100 (HY000): テーブル 'film' は LOCK TABLES でロックされていません

他のセッションはロックされていないテーブルを照会または更新できます

mysql> film_id = 1001 の場合、film_id、title を film から選択します。
+---------+---------------+
| 映画ID | タイトル |
+---------+---------------+
| 1001 | レコードを更新 |
+---------+---------------+
セット内の 1 行 (0.00 秒)
mysql> フィルムセットを更新します。title = 'Test'、film_id = 1001;
クエリは正常、1 行が影響を受けました (0.04 秒)
一致した行: 1 変更された行: 1 警告: 0

現在のセッションでロックされたテーブルを挿入または更新すると、エラーが表示されます。

mysql> film_text (film_id,title) に値(1002,'Test') を挿入します。
エラー 1099 (HY000): テーブル 'film_text' は読み取りロックでロックされており、更新できません
mysql> film_text を更新し、title を 'Test' に設定し、film_id を 1001 に設定します。
エラー 1099 (HY000): テーブル 'film_text' は読み取りロックでロックされており、更新できません

ロックされたテーブルを更新する他のセッションは、ロックを取得するまで待機します。

mysql> film_text を更新し、title を 'Test' に設定し、film_id を 1001 に設定します。

待って

ロックを解除

mysql> テーブルのロックを解除します。
クエリは正常、影響を受けた行は 0 行 (0.00 秒)		待って

セッションがロックを取得し、更新操作が完了します。

mysql> film_text を更新し、title を 'Test' に設定し、film_id を 1001 に設定します。
クエリは正常、1 行が影響を受けました (1 分 0.71 秒)
一致した行: 1 変更された行: 1 警告: 0

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) の同時実行例

セッション_1セッション_2

film_textテーブルにREAD LOCALロックを取得する

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

現在のセッションでは、ロックされたテーブルに対して更新または挿入操作を実行できません。

mysql> film_text (film_id,title) に値(1002,'Test') を挿入します。
エラー 1099 (HY000): テーブル 'film_text' は読み取りロックでロックされており、更新できません
mysql> film_text を更新し、title を 'Test' に設定し、film_id を 1001 に設定します。
エラー 1099 (HY000): テーブル 'film_text' は読み取りロックでロックされており、更新できません

他のセッションは挿入操作を実行できますが、更新は待機します。

mysql> film_text (film_id,title) に値(1002,'Test') を挿入します。
クエリは正常、1 行が影響を受けました (0.00 秒)
mysql> film_text を更新し、title を 'Update Test' に設定し、film_id を 1001 に設定します。

待って

現在のセッションは、他のセッションによって挿入されたレコードにアクセスできません。

mysql> film_id = 1002 の film_text から film_id、title を選択します。
空のセット (0.00 秒)

ロックを解除します:

mysql> テーブルのロックを解除します。
クエリは正常、影響を受けた行は 0 行 (0.00 秒)

待って

現在のセッションのロックが解除されると、他のセッションによって挿入されたレコードを取得できます。

mysql> film_id = 1002 の film_text から film_id、title を選択します。
+---------+-------+
| 映画ID | タイトル |
+---------+-------+
| 1002 | テスト |
+---------+-------+
セット内の 1 行 (0.00 秒)

セッション2はロックを取得し、更新操作が完了します。

mysql> film_text を更新し、title を 'Update Test' に設定し、film_id を 1001 に設定します。
クエリは正常、1 行が影響を受けました (1 分 17.75 秒)
一致した行: 1 変更された行: 1 警告: 0

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 行ロック モード互換性リスト

ロックモードをリクエスト

互換性

現在のロックモード

バツ9 章スは
バツ対立対立対立対立
9 章対立互換性がある対立互換性がある
ス対立対立互換性がある互換性がある
は対立互換性がある互換性がある互換性がある

トランザクションによって要求されたロック モードが現在のロックと互換性がある場合、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 ストレージ エンジンの共有ロックの例

セッション_1セッション_2
mysql> 自動コミットを 0 に設定します。
クエリは正常、影響を受けた行は 0 行 (0.00 秒)
mysql> actor_id = 178 の場合、actor から actor_id、first_name、last_name を選択します。
+----------+------------+------------+
| 俳優ID | 名 | 姓 |
+----------+------------+------------+
| 178 | リサ | モンロー |
+----------+------------+------------+
セット内の 1 行 (0.00 秒)
 mysql> 自動コミットを 0 に設定します。
クエリは正常、影響を受けた行は 0 行 (0.00 秒)
mysql> actor_id = 178 の場合、actor から actor_id、first_name、last_name を選択します。
+----------+------------+------------+
| 俳優ID | 名 | 姓 |
+----------+------------+------------+
| 178 | リサ | モンロー |
+----------+------------+------------+
セット内の 1 行 (0.00 秒)

現在のセッションでは、actor_id=178 のレコードに共有モードの共有ロックが追加されます。

mysql> actor_id = 178 の actor から actor_id、first_name、last_name を選択します。共有モードでロックします。
+----------+------------+------------+
| 俳優ID | 名 | 姓 |
+----------+------------+------------+
| 178 | リサ | モンロー |
+----------+------------+------------+
セット内の1行（0.01秒）

他のセッションは引き続きレコードを照会でき、共有モードでレコードに共有ロックを追加することもできます。

mysql> actor_id = 178 の actor から actor_id、first_name、last_name を選択します。共有モードでロックします。
+----------+------------+------------+
| 俳優ID | 名 | 姓 |
+----------+------------+------------+
| 178 | リサ | モンロー |
+----------+------------+------------+
セット内の1行（0.01秒）

現在のセッションはロックされたレコードを更新し、ロックを待機します。

mysql> 俳優を更新し、last_name を 'MONROE T' に設定し、actor_id を 178 に設定します。

待って

他のセッションもレコードを更新すると、デッドロックが発生します。

mysql> 俳優を更新し、last_name を 'MONROE T' に設定し、actor_id を 178 に設定します。
エラー 1213 (40001): ロックを取得しようとしたときにデッドロックが見つかりました。トランザクションを再起動してください。

ロックを取得した後、更新は成功します。

mysql> 俳優を更新し、last_name を 'MONROE T' に設定し、actor_id を 178 に設定します。
クエリは正常、1 行が影響を受けました (17.67 秒)
一致した行: 1 変更された行: 1 警告: 0

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

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

セッション_1セッション_2
mysql> 自動コミットを 0 に設定します。
クエリは正常、影響を受けた行は 0 行 (0.00 秒)
mysql> actor_id = 178 の場合、actor から actor_id、first_name、last_name を選択します。
+----------+------------+------------+
| 俳優ID | 名 | 姓 |
+----------+------------+------------+
| 178 | リサ | モンロー |
+----------+------------+------------+
セット内の 1 行 (0.00 秒)		 mysql> 自動コミットを 0 に設定します。
クエリは正常、影響を受けた行は 0 行 (0.00 秒)
mysql> actor_id = 178 の場合、actor から actor_id、first_name、last_name を選択します。
+----------+------------+------------+
| 俳優ID | 名 | 姓 |
+----------+------------+------------+
| 178 | リサ | モンロー |
+----------+------------+------------+
セット内の 1 行 (0.00 秒)

現在のセッションでは、actor_id=178 のレコードに更新用の排他ロックを追加します。

mysql> actor_id = 178 の場合、actor から actor_id、first_name、last_name を選択して更新します。
+----------+------------+------------+
| 俳優ID | 名 | 姓 |
+----------+------------+------------+
| 178 | リサ | モンロー |
+----------+------------+------------+
セット内の 1 行 (0.00 秒)

他のセッションはレコードを照会できますが、レコードに共有ロックを追加することはできず、ロックを取得するまで待機します。

mysql> actor_id = 178 の場合、actor から actor_id、first_name、last_name を選択します。
+----------+------------+------------+
| 俳優ID | 名 | 姓 |
+----------+------------+------------+
| 178 | リサ | モンロー |
+----------+------------+------------+
セット内の 1 行 (0.00 秒)
mysql> actor_id = 178 の場合、actor から actor_id、first_name、last_name を選択して更新します。

待って

現在のセッションでは、ロックされたレコードを更新し、更新後にロックを解除できます。

mysql> 俳優を更新し、last_name を 'MONROE T' に設定し、actor_id を 178 に設定します。
クエリは正常、1 行が影響を受けました (0.00 秒)
一致した行: 1 変更された行: 1 警告: 0
mysql> コミット;
クエリは正常、影響を受けた行は 0 行 (0.01 秒)

他のセッションはロックを取得し、他のセッションによって送信されたレコードを取得します。

mysql> actor_id = 178 の場合、actor から actor_id、first_name、last_name を選択して更新します。
+----------+------------+------------+
| 俳優ID | 名 | 姓 |
+----------+------------+------------+
| 178 | リサ | モンロー T |
+----------+------------+------------+
セット1列目（9.59秒）


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ストレージエンジンテーブルがインデックスを使用しない場合のテーブルロックの使用例

セッション_1セッション_2
mysql> 自動コミットを 0 に設定します。
クエリは正常、影響を受けた行は 0 行 (0.00 秒)
mysql> select * from tab_no_index where id = 1;
+------+------+
| ID | 名前 |
+------+------+
| 1 | 1 |
+------+------+
セット内の 1 行 (0.00 秒)		 mysql> 自動コミットを 0 に設定します。
クエリは正常、影響を受けた行は 0 行 (0.00 秒)
mysql> select * from tab_no_index where id = 2;
+------+------+
| ID | 名前 |
+------+------+
| 2 | 2 |
+------+------+
セット内の 1 行 (0.00 秒)
mysql> select * from tab_no_index fromここで、id = 1アップデート。
+------+------+
| ID | 名前 |
+------+------+
| 1 | 1 |
+------+------+
セット内の 1 行 (0.00 秒)
mysql> select * from tab_no_index fromここで、id = 2アップデート。

待って

上の表に示す例では、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ストレージエンジンを使用してテーブルにインデックスを使用する場合の行ロックを使用する例

セッション_1セッション_2
mysql> 自動コミットを 0 に設定します。
クエリは正常、影響を受けた行は 0 行 (0.00 秒)
mysql> select * from tab_with_index where id = 1;
+------+------+
| ID | 名前 |
+------+------+
| 1 | 1 |
+------+------+
セット内の 1 行 (0.00 秒)		 mysql> 自動コミットを 0 に設定します。
クエリは正常、影響を受けた行は 0 行 (0.00 秒)
mysql> select * from tab_with_index where id = 2;
+------+------+
| ID | 名前 |
+------+------+
| 2 | 2 |
+------+------+
セット内の 1 行 (0.00 秒)
mysql> select * from tab_with_index fromここで、id = 1アップデート。
+------+------+
| ID | 名前 |
+------+------+
| 1 | 1 |
+------+------+
セット内の 1 行 (0.00 秒)
mysql> select * from tab_with_index fromここで、id = 2アップデート。
+------+------+
| ID | 名前 |
+------+------+
| 2 | 2 |
+------+------+
セット内の 1 行 (0.00 秒)

（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ストレージエンジンブロッキング例同じインデックスキーを使用して

セッション_1セッション_2
mysql> 自動コミットを 0 に設定します。
クエリは正常、影響を受けた行は 0 行 (0.00 秒)		 mysql> 自動コミットを 0 に設定します。
クエリは正常、影響を受けた行は 0 行 (0.00 秒)
mysql> select * from tab_with_index from id = 1およびname = '1' for usite;
+------+------+
| ID | 名前 |
+------+------+
| 1 | 1 |
+------+------+
セット内の 1 行 (0.00 秒)

session_2はsession_1とは異なるレコードにアクセスしますが、同じインデックスを使用するため、ロックを待つ必要があります。

mysql> select * from tab_with_index from id = 1およびname = '4' for usite;

待って

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

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

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

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

セッション_1セッション_2
mysql> 自動コミットを 0 に設定します。
クエリは正常、影響を受けた行は 0 行 (0.00 秒)		 mysql> 自動コミットを 0 に設定します。
クエリは正常、影響を受けた行は 0 行 (0.00 秒)
mysql> select * from tab_with_index fromここで、id = 1アップデート。
+------+------+
| ID | 名前 |
+------+------+
| 1 | 1 |
| 1 |
+------+------+
セット内の 2 行 (0.00 秒)

session_2は、レコードにアクセスするために名前のインデックスを使用します。

mysql> select * from tab_with_index where name = '2' for update;
+------+------+
| ID | 名前 |
+------+------+
| 2 | 2 |
+------+------+
セット内の 1 行 (0.00 秒)

アクセスされたレコードはsession_1によってロックされているため、ロックを取得するのが待ちます。 :

mysql> select * from tab_with_index where name = '4' for update;

（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ストレージエンジンのギャップロックブロッキングの例

セッション_1セッション_2
mysql> @@tx_isolation を選択します。
+-----------------+
| @@tx_isolation |
+-----------------+
| 繰り返し読み取り |
+-----------------+
セット内の 1 行 (0.00 秒)
mysql> 自動コミットを 0 に設定します。
クエリは正常、影響を受けた行は 0 行 (0.00 秒)		 mysql> @@tx_isolation を選択します。
+-----------------+
| @@tx_isolation |
+-----------------+
| 繰り返し読み取り |
+-----------------+
セット内の 1 行 (0.00 秒)
mysql> 自動コミットを 0 に設定します。
クエリは正常、影響を受けた行は 0 行 (0.00 秒)

現在のセッションでは、存在しないレコードを更新するためのロックを追加します。

mysql> select * from empid = 102 for update;
空のセット (0.00 秒)

この時点で、他のセッションが102のEmpidでレコードを挿入する場合（注：このレコードは存在しません）、ロックも表示されます。

mysql> inserting into emp（empid、...）values（102、...）;

ブロッキングと待機

session_1はロールバックを実行します：

mysql> ロールバック;
クエリOK、0行の影響を受ける（13.04秒）

次のキーロックは他のsession_1フォールバックの後にリリースされるため、現在のセッションでロックを取得し、レコードを正常に挿入できます。

mysql> inserting into emp（empid、...）values（102、...）;
クエリOK、1行の影響を受ける（13.35秒）

回復と複製の必要性、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操作の例

セッション_1セッション_2
mysql> 自動コミットを 0 に設定します。
クエリは正常、影響を受けた行は 0 行 (0.00 秒)
mysql> select * from target_tab;
空のセット (0.00 秒)
mysql> select * from source_tab where name = '1';
+----+-----+----+
| d1 |
+----+-----+----+
| 4 |
| 5 |
| 6 |
| 7 | 1 | 1 |
| 8 |
+----+-----+----+
セット内の行数は 5 です (0.00 秒)		 mysql> 自動コミットを 0 に設定します。
クエリは正常、影響を受けた行は 0 行 (0.00 秒)
mysql> select * from target_tab;
空のセット (0.00 秒)
mysql> select * from source_tab where name = '1';
+----+-----+----+
| d1 |
+----+-----+----+
| 4 |
| 5 |
| 6 |
| 7 | 1 | 1 |
| 8 |
+----+-----+----+
セット内の行数は 5 です (0.00 秒)
mysql> asent into target_tab d1を選択し、source_tabからname where name = '1';
クエリは正常、5 行が影響を受けました (0.00 秒)
記録: 5 重複: 0 警告: 0
mysql> update source_tab set name = '1' where name = '8';

待って

専念;

結果を返す

専念;

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

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

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

セッション_1セッション_2
mysql> 自動コミットを 0 に設定します。
クエリは正常、影響を受けた行は 0 行 (0.00 秒)
mysql> set innodb_locks_unsafe_for_binlog = 'on'
クエリは正常、影響を受けた行は 0 行 (0.00 秒)
mysql> select * from target_tab;
空のセット (0.00 秒)
mysql> select * from source_tab where name = '1';
+----+-----+----+
| d1 |
+----+-----+----+
| 4 |
| 5 |
| 6 |
| 7 | 1 | 1 |
| 8 |
+----+-----+----+
セット内の行数は 5 です (0.00 秒)		 mysql> 自動コミットを 0 に設定します。
クエリは正常、影響を受けた行は 0 行 (0.00 秒)
mysql> select * from target_tab;
空のセット (0.00 秒)
mysql> select * from source_tab where name = '1';
+----+-----+----+
| d1 |
+----+-----+----+
| 4 |
| 5 |
| 6 |
| 7 | 1 | 1 |
| 8 |
+----+-----+----+
セット内の行数は 5 です (0.00 秒)
mysql> asent into target_tab d1を選択し、source_tabからname where name = '1';
クエリは正常、5 行が影響を受けました (0.00 秒)
記録: 5 重複: 0 警告: 0

Session_1は提出されておらず、Session_1の選択の記録を更新できます。

mysql> update source_tab set name = '8' where name = '1';
クエリは正常、5 行が影響を受けました (0.00 秒)
一致する行：5変更：5警告：0
mysql> select * from source_tab where name = '8';
+----+-----+----+
| d1 |
+----+-----+----+
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
+----+-----+----+
セット内の行数は 5 です (0.00 秒)

最初に更新操作を送信します

mysql> コミット;
クエリは正常、影響を受けた行は 0 行 (0.05 秒)

挿入後に送信します

mysql> コミット;
クエリは正常、影響を受けた行は 0 行 (0.07 秒)

この時点で、データを表示し、source_tabの更新の前にターゲット_tabに結果を挿入します。これは、アプリケーションロジックに準拠しています。

mysql> select * from source_tab where name = '8';
+----+-----+----+
| d1 |
+----+-----+----+
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
+----+-----+----+
セット内の行数は 5 です (0.00 秒)
mysql> select * from target_tab;
+------+------+
| ID | 名前 |
+------+------+
| 1.00 |
| 1.00 |
| 6 |
| 7 |
| 1.00 |
+------+------+
セット内の行数は 5 です (0.00 秒)		 mysql> select * from tt1 where name = '1';
空のセット (0.00 秒)
mysql> select * from source_tab where name = '8';
+----+-----+----+
| d1 |
+----+-----+----+
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
+----+-----+----+
セット内の行数は 5 です (0.00 秒)
mysql> select * from target_tab;
+------+------+
| ID | 名前 |
+------+------+
| 1.00 |
| 1.00 |
| 6 |
| 7 |
| 1.00 |
+------+------+
セット内の行数は 5 です (0.00 秒)

上記からわかるように、システム変数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でのロックの比較

分離レベル

一貫した読み取りとロック

構文

コミットされていない読み取り委任状を読んでください繰り返し読み取りシリアル化可能
構文状態
選択等しいなしロックConscenten Read/None Lock Conscenten Read/None Lockロックを共有します
範囲なしロックConscenten Read/None Lock Conscenten Read/None Lock次のキーを共有します
アップデート等しい排他的ロック排他的ロック排他的ロック排他的ロック
範囲排他的な次のキー排他的な次のキー排他的な次のキー排他的な次のキー
入れる該当なし排他的ロック排他的ロック排他的ロック排他的ロック
交換する重要な対立はありません排他的ロック排他的ロック排他的ロック排他的ロック
重要な対立排他的な次のキー排他的な次のキー排他的な次のキー排他的な次のキー
消去等しい排他的ロック排他的ロック排他的ロック排他的ロック
範囲排他的な次のキー排他的な次のキー排他的な次のキー排他的な次のキー
Select ... from ...ロックインシェアモード等しいロックを共有しますロックを共有しますロックを共有しますロックを共有します
範囲ロックを共有しますロックを共有します次のキーを共有します次のキーを共有します
更新のために * from ...を選択します等しい排他的ロック排他的ロック排他的ロック排他的ロック
範囲排他的ロックロックを共有します排他的な次のキー排他的な次のキー

INSERTINGに...選択...

（ソーステーブルロックを指します）

 innodb_locks_unsafe_for_binlog = off次のキーを共有します次のキーを共有します次のキーを共有します次のキーを共有します
innodb_locks_unsafe_for_binlog = onなしロックConscenten Read/None Lock Conscenten Read/None Lock次のキーを共有します

テーブルを作成...選択...

（ソーステーブルロックを指します）

 innodb_locks_unsafe_for_binlog = off次のキーを共有します次のキーを共有します次のキーを共有します次のキーを共有します
innodb_locks_unsafe_for_binlog = onなしロックConscenten Read/None Lock Conscenten Read/None Lock次のキーを共有します

上記の表からわかるように：多くの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ストレージエンジンのデッドロックの例

セッション_1セッション_2
mysql> 自動コミットを 0 に設定します。
クエリは正常、影響を受けた行は 0 行 (0.00 秒)
mysql> select * from table_1ここで、updateの場合はid = 1です。
...

他の治療をいくつかします...

 mysql> 自動コミットを 0 に設定します。
クエリは正常、影響を受けた行は 0 行 (0.00 秒)
mysql> select * from table_2ここで、update for id = 1;
...
更新のためにtable_2から[table_2からid = 1]を選択します。

session_2が排他的ロックを取得したので、待ってください

他の治療をいくつかします...
mysql> select * from table_1ここで、updateの場合はid = 1です。

デッドロック

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

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

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

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

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

セッション_1セッション_2
mysql> 自動コミットを 0 に設定します。
クエリは正常、影響を受けた行は 0 行 (0.00 秒)		 mysql> 自動コミットを 0 に設定します。
クエリは正常、影響を受けた行は 0 行 (0.00 秒)
mysql> select first_name、last_name from Actor where actor_id = 1 for update;
+------------+------------+
| first_name |
+------------+------------+
|ペネロペ|
+------------+------------+
セット内の 1 行 (0.00 秒)
mysql> country（country_id、country）values（110、 'test'）への挿入;
クエリは正常、1 行が影響を受けました (0.00 秒)
mysql> country（country_id、country）values（110、 'test'）への挿入;

待って

mysql> select first_name、last_name from Actor where actor_id = 1 for update;
+------------+------------+
| first_name |
+------------+------------+
|ペネロペ|
+------------+------------+
セット内の 1 行 (0.00 秒)
mysql> country（country_id、country）values（110、 'test'）への挿入;
エラー 1213 (40001): ロックを取得しようとしたときにデッドロックが見つかりました。トランザクションを再起動してください。

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

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

セッション_1セッション_2
mysql> 自動コミットを 0 に設定します。
クエリは正常、影響を受けた行は 0 行 (0.00 秒)		 mysql> 自動コミットを 0 に設定します。
クエリは正常、影響を受けた行は 0 行 (0.00 秒)
mysql> select first_name、last_name from Actor where actor_id = 1 for update;
+------------+------------+
| first_name |
+------------+------------+
|ペネロペ|
+------------+------------+
セット内の 1 行 (0.00 秒)
mysql> first_nameを選択、actor_id = 3のactor from actorのlast_nameを選択します。
+------------+------------+
| first_name |
+------------+------------+
| ed |
+------------+------------+
セット内の 1 行 (0.00 秒)
mysql> first_nameを選択、actor_id = 3のactor from actorのlast_nameを選択します。

待って

mysql> select first_name、last_name from Actor where actor_id = 1 for update;
エラー 1213 (40001): ロックを取得しようとしたときにデッドロックが見つかりました。トランザクションを再起動してください。
mysql> first_nameを選択、actor_id = 3のactor from actorのlast_nameを選択します。
+------------+------------+
| first_name |
+------------+------------+
| ed |
+------------+------------+
セットの1列（4.71秒）

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

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

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

セッション_1セッション_2
mysql> @@tx_isolation を選択します。
+-----------------+
| @@tx_isolation |
+-----------------+
| 繰り返し読み取り |
+-----------------+
セット内の 1 行 (0.00 秒)
mysql> 自動コミットを 0 に設定します。
クエリは正常、影響を受けた行は 0 行 (0.00 秒)		 mysql> @@tx_isolation を選択します。
+-----------------+
| @@tx_isolation |
+-----------------+
| 繰り返し読み取り |
+-----------------+
セット内の 1 行 (0.00 秒)
mysql> 自動コミットを 0 に設定します。
クエリは正常、影響を受けた行は 0 行 (0.00 秒)

現在のセッションでは、存在しないレコードを更新するためのロックを追加します。

mysql> actor（actor_id、first_name、last_name）values（201、 'lisa'、 'tom'）に挿入

他のセッションは、存在しないレコードに更新ロックを追加することもできます。

mysql> actor（actor_id、first_name、last_name）values（201、 'lisa'、 'tom'）に挿入
エラー 1213 (40001): ロックを取得しようとしたときにデッドロックが見つかりました。トランザクションを再起動してください。

他のセッションもレコードをロックしているため、現在の挿入が待機します。

mysql> actor（actor_id、first_name、last_name）values（201、 'lisa'、 'tom'）に挿入

待って

他のセッションがレコードを更新したため、レコードを再度挿入すると、デッドロックと出口が促されます。

mysql> actor（actor_id、first_name、last_name）values（201、 'lisa'、 'tom'）に挿入
エラー 1213 (40001): ロックを取得しようとしたときにデッドロックが見つかりました。トランザクションを再起動してください。

他のセッションが終了したため、現在のセッションでロックを取得し、レコードを正常に挿入できます。

mysql> actor（actor_id、first_name、last_name）values（201、 'lisa'、 'tom'）に挿入
クエリOK、1行の影響を受ける（13.35秒）

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

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

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

セッション_1セッション_2セッション_3
mysql> @@tx_isolation を選択します。
+----------------+
| @@tx_isolation |
+----------------+
| コミット読み取り |
+----------------+
セット内の 1 行 (0.00 秒)
mysql> 自動コミットを 0 に設定します。
クエリは正常、影響を受けた行は 0 行 (0.01 秒)		 mysql> @@tx_isolation を選択します。
+----------------+
| @@tx_isolation |
+----------------+
| コミット読み取り |
+----------------+
セット内の 1 行 (0.00 秒)
mysql> 自動コミットを 0 に設定します。
クエリは正常、影響を受けた行は 0 行 (0.01 秒)		 mysql> @@tx_isolation を選択します。
+----------------+
| @@tx_isolation |
+----------------+
| コミット読み取り |
+----------------+
セット内の 1 行 (0.00 秒)
mysql> 自動コミットを 0 に設定します。
クエリは正常、影響を受けた行は 0 行 (0.01 秒)

session_1アップデートのために共有ロックを取得します：

mysql> select actor_id、first_name、last_name from Actor where actor_id = 201 for updert;
空のセット (0.00 秒)

レコードは存在しないため、Session_2は更新用の共有ロックを取得することもできます。

mysql> select actor_id、first_name、last_name from Actor where actor_id = 201 for updert;
空のセット (0.00 秒)

session_1はレコードを正常に挿入できます。

mysql> actor（actor_id、first_name、last_name）values（201、 'lisa'、 'tom'）に挿入
クエリは正常、1 行が影響を受けました (0.00 秒)

session_2ロックを取得するのを待っているアプリケーションを挿入します：

mysql> actor（actor_id、first_name、last_name）values（201、 'lisa'、 'tom'）に挿入

待って

Session_1は正常に送信されました：

mysql> コミット;
クエリは正常、影響を受けた行は 0 行 (0.04 秒)

session_2はロックを取得し、挿入レコードの主要なキーがこの時点でスローされていることを発見しましたが、共有ロックはリリースされません。

mysql> actor（actor_id、first_name、last_name）values（201、 'lisa'、 'tom'）に挿入
エラー1062（23000）：キー「プライマリ」のエントリ「201」を重複させる

Session_3は、Session_2がレコードをロックしたため、共有ロックを取得するために適用されるため、Session_3が待つ必要があります。

mysql> select actor_id、first_name、last_name from Actor where actor_id = 201 for updert;

待って

この時点で、Session_2がレコードを直接更新する場合、デッドロックの例外がスローされます。

mysql> update actor set last_name = 'lan'ここでactor_id = 201;
エラー 1213 (40001): ロックを取得しようとしたときにデッドロックが見つかりました。トランザクションを再起動してください。

session_2がロックを解放した後、session_3はロックを取得します。

mysql> select first_name、last_name from Actor where actor_id = 201 for update;
+------------+------------+
| first_name |
+------------+------------+
|リサ|
+------------+------------+
セットの1列（31.12秒）

上記の設計と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、共有ロック、排他的ロック、ギャップロックの詳細な使用方法について包括的に説明しています。

以下もご興味があるかもしれません:
  • MySQLの行ロックとテーブルロックの意味と違いの詳細な説明
  • MySQLのテーブルロック、行ロック、排他ロック、共有ロックの使用に関する詳細な説明

<<:  Nginx の負荷分散方法の概要

>>:  Angularプロジェクトにおける共有モジュールの実装の詳細な説明

推薦する

MySQL5.6.31 winx64.zip インストールと設定のチュートリアル

#1. ダウンロード # #2. ローカルに解凍し、必要な構成のmy*.iniを変更します。 #3....

Docker データボリュームコンテナの作成と使用状況分析

データ ボリューム コンテナーは、データ ボリュームをマウントするために特別に使用されるコンテナーで...

検証例 MySQL | 同じ値を持つフィールドを更新すると、binlog に記録されます

1. はじめに数日前、開発仲間から、フィールドを同じ値に更新すると binlog が記録されるかどう...

MySQLクエリインターセプトの詳細な分析

目次1. クエリの最適化1. MySQLチューニングの概要2. 小さなテーブルが大きなテーブルを動か...

Linuxでのaliasコマンドの使い方の詳細な説明

1. 別名の使用alias コマンドは、コマンドのエイリアスを設定するために使用されます。このコマン...

MySQL で置換操作を使用したときにデータ損失が発生する問題の解決策

序文同社の開発者は、データの更新時に replace into ステートメントを使用していました。不...

Synology NAS は Docker コンテナを使用して KMS アクティベーション サーバーを構築し、Windows システムとオフィスをアクティベートします (操作手順)

序文KMS を通じてライセンス認証できる Windows システムは、一般的に VL バージョン、つ...

MySQLはJSON内部フィールドを抽出し、数値としてダンプします

目次背景問題分析1. 属性値はJson形式であり、Json操作関数を使用して処理する必要があります。...

vue ディレクティブ v-bind の使用と注意点

目次1. v-bind: 要素の属性にデータをバインドできる2. v-bind: は次のように省略で...

CSS3 天子グリッドリストのスタイルの書き方

多くのプロジェクトでは、中央に灰色の分割線があり、両側に分割線がないグリッド表示の機能を実装する必要...

Linux でのインストール中にソフトウェア パッケージの依存関係レポートに関連する問題の解決策

目次背景1) yumのkeepchche機能を有効にする: 方法1 2) yum-utils ソフト...

Nginx の一般的な設定とテクニックの概要

序文この記事では、Nginx の一般的な、実用的で興味深い構成をいくつか紹介します。この記事を読んだ...

Reactでプロキシを有効にする2つの実用的な方法

プロキシを有効にする2つの方法React には、直接使用できるカプセル化された Ajax リクエスト...

JavaScriptの信頼性の低い未定義

undefined JavaScript では、値が undefined かどうかを判断したい場合は...

最小限のルートファイルシステムを構築するためにbusyboxを移植するための詳細な手順

Busybox: 小さなコマンドが詰まったスイスアーミーナイフ。ステップ1: ディレクトリ構造を作成...