MySql の行レベルのロックとテーブルレベルのロックの詳細な説明-mysql チュートリアル-php.cn

同時データアクセスの一貫性と有効性を確保する方法は、すべてのデータベースが解決しなければならない問題です。ロックの競合も、データベースへの同時アクセスのパフォーマンス係数。この観点から見ると、ロックはデータベースにとって特に重要かつ複雑です。

MySQL ロックの概要

MySQL のロックメカニズムは他のデータベースと比較して比較的シンプルであり、最も重要な特徴は、異なるストレージエンジンが異なるロックメカニズムをサポートしていることです。
たとえば、

MyISAM および MEMORY ストレージエンジンはテーブルレベルのロックを使用します。

InnoDB ストレージエンジンは行レベルのロックとテーブルレベルのロックの両方をサポートしていますが、デフォルトでは行レベルのロックが使用されます。

MySQL のこれら 3 つのロックの特性は、次のように大まかに要約できます。

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

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

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

ロックの観点から見ると、テーブルレベルのロックは、Web アプリケーションなど、主にクエリベースで、インデックス条件に従って更新されるデータが少量しかないアプリケーションに適しています。レベルロックは、インデックス条件に基づいて大量のデータを扱うアプリケーションに適しています。インデックス条件は、少量の異なるデータを同時に更新し、一部のオンライントランザクション処理 (OLTP) システムなど、同時クエリアプリケーションが存在します。次のセクションでは、MySQL テーブルロックと InnoDB 行ロックの問題に焦点を当てます。

MyISAM テーブルロック

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

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

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

mysql> show status like 'table % ';

#| 変数名 | 値 |

| Table_locks_immediate | 2979 |

| Table_locks_waited | 0 |

セット内の 2 行 (0.00 秒) ))

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

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

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

MyISAM テーブルの読み取り操作は、同じテーブルに対する他のユーザーの読み取りリクエストをブロックしませんが、同じテーブルに対する書き込みリクエストはブロックします。

MyISAM テーブルの書き込み操作は、他のユーザーの読み取りリクエストをブロックします。同じテーブルに対するユーザーのリクエストテーブルの読み取りおよび書き込み操作;
MyISAM テーブルの読み取りおよび書き込み操作、および書き込み操作はシリアルです;
スレッドがテーブルの書き込みロックを取得したとき、スレッドはテーブルに対して更新操作を実行できるロックのみを保持できます。他のスレッドからの読み取りおよび書き込み操作は、ロックが解放されるまで待機します。

テーブル film_text の WRITE ロックを取得します

mysql>ロックテーブル film_text write;

クエリ OK、影響を受ける行は 0 (0.00 秒)

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

mysql> select film_id,title from film_text where film_id = 1001;

| film_id | title |

#| 1001 | アップデートテスト |

セット内の 1 行 (0.00 秒)

mysql>insert into film_text (film_id,title) names(1003,'Test');

クエリ OK、1 行が影響を受けました (0.00 秒)

mysql>update film_text set title = 'Test' where film_id = 1001;

クエリ OK、1 行が影響を受けました ( 0.00 秒 )

一致した行: 1 変更: 1 警告: 0

ロックされたテーブルに対する他のセッションのクエリはブロックされているため、ロックが解放されるまで待つ必要があります:

mysql>select film_id, title from film_text where film_id = 1001;

Waiting

release lock:

mysql>unlock tables;

Query OK 、影響を受ける行は 0 (0.00 秒)

Waiting

Session2 がロックを取得すると、クエリは次の結果を返します:

mysql> select film_id,title from film_text where film_id = 1001;

| 映画 ID | タイトル |

| 1001 | テスト |

##セット内の 1 行 (57.59 秒)

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

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

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

Select sum(total) fromorders;

Select sum(subtotal) from order_detail;

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

ロックテーブルの注文はローカルで読み取り、order_detail はローカルで読み取る;

注文から合計(合計)を選択;

注文から合計(小計)を選択order_detail;

Unlock tables;

特に次の 2 点に注意してください。

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

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

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

LOCK TABLES を使用する場合は、使用されるすべてのテーブルを一度にロックする必要があるだけでなく、SQL ステートメント内に同じテーブルが何回出現するかについてもロックする必要があり、SQL ステートメントと同じ別名を使用してロックする必要があります。そうしないと、何か問題が発生する可能性があります。

例は次のとおりです。

(1) アクターテーブルの読み取りロックを取得します:

mysql> lock table actress read;

Query OK、影響を受ける行は 0 (0.00 秒)

(2) ただし、エイリアス経由でアクセスするとエラーが表示されます:

mysql> select a.first_name,a.last_name,b.first_name,b.last_name from Actor a,actor b where 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 でロック

(3) エイリアスは個別にロックする必要があります:

mysql>lock tableactor as a read,actor as b read;

Query OK, 0影響を受ける行数 (0.00 秒)

(4) エイリアスに基づくクエリは正しく実行できます:

mysql> select a.first_name,a.last_name,b.first_name,b.last_name俳優 a,俳優 b から、a .first_name = b.first_name および a.first_name = 'Lisa' および a.last_name = 'Tom' および a.last_name b.last_name;

#| リサ | トム| リサ | モンロー |

1 セット内の 1 行 (0.00 秒)

同時挿入数

前述したように、MyISAM テーブルの読み取りと書き込みはシリアルですが、これは全体的な観点からです。特定の条件下では、MyISAM テーブルは同時クエリと挿入操作もサポートします。

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

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

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

concurrent_insert が 2 に設定されている場合、MyISAM テーブルにホールがあるかどうかに関係なく、テーブルの最後にレコードを同時に挿入できます。

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 ステートメントを使用しようとする必要はありません。この一見賢明な SQL ステートメントは、多くの場合より複雑で、実行に時間がかかるからです。可能な場合は、 SQL ステートメントは、中間テーブルやその他の手段を使用してある程度まで「分解」できるため、クエリの各ステップをより短時間で完了できるため、ロックの競合が軽減されます。複雑なクエリが避けられない場合は、データベースのアイドル期間中に実行されるようにスケジュールする必要があります。たとえば、一部の定期統計は夜間に実行するようにスケジュールできます。

InnoDB ロック

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

1. トランザクション (Transaction) とその ACID 属性

トランザクションとは SQL 文の集合から構成される論理的な処理単位であり、トランザクションには次の 4 つの属性があり、通常これらを参照します。トランザクションの ACID 属性として。

(原子性) 原子性: トランザクションは原子的な操作単位であり、データに対するすべての変更は実行されるか、まったく実行されないかのどちらかです。

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

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

(耐久性) 耐久性: トランザクション完了後のデータへの変更は永続的であり、システム障害が発生した場合でも維持できます。

銀行振込はトランザクションの典型的な例です。

2. トランザクションの同時処理によって発生する問題

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

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

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

Non-Repeatable Reads: トランザクションは、一部のデータを読み取った後、以前に読み取ったデータを再度読み取りますが、読み取ったデータが変更されているか、一部のレコードが削除されていることがわかります。この現象を「非反復読み取り」と呼びます。

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

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

上記の同時トランザクション処理によって引き起こされる問題のうち、「更新ロス」は通常完全に回避する必要があります。ただし、更新消失の防止はデータベーストランザクションコントローラだけで解決できるものではなく、アプリケーション側で更新対象のデータに必要なロックを付与する必要があるため、更新消失の防止はアプリケーション側で行う必要があります。

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

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

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

整合性読み取り。スナップショット読み取りとも呼ばれます。 MVCC メカニズムは、送信されたデータをアンドゥで読み取るために使用されます。したがって、その読み取りはノンブロッキングです。

一貫性読み取りでは、特定の時点で送信されたデータを読み取る必要があります。特殊な場合があります。このトランザクションで変更されたデータは、コミットされていないデータであっても、このトランザクションの後半で読み取ることができます。到着。一貫した読み取りとは、for update、共有モードなどの句のない通常の select ステートメントを指します。元に戻すときに送信されたデータが使用され、ロックは必要ありません (MDL を除く)。現在の読み取りは、update、delete、更新用の選択、共有モードでの選択などのステートメントによって実行される読み取りを指します。これらはデータベース内の最新のデータを読み取り、読み取り行とギャップをロックします (RR 分離時間)。ロックを取得できない場合は、ロックが取得されるまで待機するか、タイムアウトになります。

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

「分離」と「同時実行性」の間の矛盾を解決するために、ISO/ANSI SQL92 では 4 つのトランザクション分離レベルが定義されています。各レベルは分離の度合いが異なり、さまざまな副作用が許容されます。アプリケーションは以下に基づくことができます。独自のビジネスロジックでは、さまざまな分離レベルを選択することで、「分離」と「同時実行」の間の矛盾のバランスを取る必要があります。表 20-5 は、これら 4 つの分離レベルの特性をわかりやすくまとめたものです。

MySql の行レベルのロックとテーブルレベルのロックの詳細な説明

それぞれの特定のデータベースは、上記の 4 つの分離レベルを必ずしも完全に実装しているわけではありません。Oracle は、読み取りコミットとシリアル化可能という 2 つの標準分離レベルのみを提供し、独自に定義された読み取り専用分離レベルも提供します。SQL Server は、上記の ISO/ANSI SQL92 をサポートします。定義されている 4 つの分離レベルに加えて、「スナップショット」と呼ばれる分離レベルもサポートしていますが、厳密には、MVCC を使用して実装された Serializable 分離レベルです。

MySQL は 4 つの分離レベルすべてをサポートしていますが、特定の実装では、いくつかの特徴があります。たとえば、一部の分離レベルでは、MVCC 整合性読み取りが使用されますが、場合によっては使用されません。これらの内容については後述します。これについては、この章でさらに詳しく説明します。

4. InnoDB の行ロックの競合状況を取得する

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

mysql> show status like 'innodb_row_lock%';

| Variable_name

#| InnoDB_row_lock_current_waits | 0 || InnoDB_row_lock_time | 0 || InnoDB_row_lock_time_avg | 0 |

| InnoDB_row_lock_time_max | 0 |

| InnoDB_row_lock_waits | 0 |

5 行セット (0.01 秒)

InnoDB_row_lock_waits や InnoDB_row_lock_time_avg の値が比較的高いなど、ロックの競合が深刻であることが判明した場合は、InnoDB モニターを次のように設定することもできます。さらにロック競合が発生しているテーブルやデータを観察し、ロック競合の原因を分析します。

具体的な方法は次のとおりです:

mysql> CREATE TABLE innodb_monitor(a INT) ENGINE=INNODB;

クエリ OK、影響を受ける行は 0 件 (0.14 秒)

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

mysql> Show innodb status\G;

モニターは次のステートメントを発行することで表示を停止できます:

mysql> ; DROP TABLE innodb_monitor;

Query OK, 0 rows according to (0.05 sec)

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

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

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

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

排他的ロック (X): 排他的ロックを取得するトランザクションがデータを更新できるようにし、他のトランザクションが同じデータセットに対する共有読み取りロックと排他的書き込みロックを取得できないようにします。

さらに、行ロックとテーブルロックを共存させ、複数粒度のロックメカニズムを実装できるようにするために、InnoDB には内部的に使用される 2 つのインテンションロック (インテンションロック) もあります。ロックはテーブルロックです。

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

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

MySql の行レベルのロックとテーブルレベルのロックの詳細な説明トランザクションによって要求されたロックモードが現在のロックと互換性がある場合、InnoDB は要求されたロックをトランザクションに許可します。そうでない場合は、 2 つは互換性がないため、トランザクションはロックが解放されるまで待機します。

インテンションロックは InnoDB によって自動的に追加されるため、ユーザーの介入は必要ありません。

要約は次のとおりです:

1. UPDATE、DELETE、および INSERT ステートメントの場合、InnoDB は関連するデータセットに排他ロック (X) を自動的に追加します。通常の SELECT ステートメントの場合、InnoDB はロックを追加しません;

3. トランザクションは、次のステートメントを通じてレコードセットに共有ロックまたは排他ロックを追加できます。

共有ロック(S): SELECT * FROM table_name WHERE ... 共有モードでロック。

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

SELECT ... IN SHARE MODE を使用して共有ロックを取得します。これは主に、データの依存関係が必要な場合にレコードの特定の行が存在するかどうかを確認し、誰も UPDATE または DELETE 操作を実行しないようにするために使用されます。このレコードでは。

ただし、現在のトランザクションでもレコードを更新する必要がある場合は、デッドロックが発生する可能性があります。行レコードをロックした後に更新する必要があるアプリケーションの場合は、SELECT... FOR UPDATE を使用する必要があります。排他的なロックを取得するメソッド。

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

InnoDB の行ロックは、インデックス上のインデックス項目をロックすることで実装されます。MySQL や Oracle とは異なり、後者はインデックス項目をロックすることで実装されます。これは、対応するデータ行をロックすることによって実現されます。

InnoDB の行ロック実装機能は、インデックス条件を通じてデータが取得される場合にのみ、InnoDB が行レベルのロックを使用することを意味します。それ以外の場合、InnoDB はテーブルロックを使用します。

実際のアプリケーションでは、InnoDB 行ロックのこの機能に特別な注意を払う必要があります。そうしないと、大量のロック競合が発生し、同時実行パフォーマンスに影響を与える可能性があります。

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

(2) MySQL の行ロックはレコードではなくインデックスのロックであるため、異なる行のレコードにアクセスする場合でも、

ただし、同じインデックスがキーとして使用されている場合、ロックの競合である可能性があります。アプリケーションを設計する際には、この点に注意してください。

(3) テーブルに複数のインデックスがある場合、異なるトランザクションは異なるインデックスを使用して異なる行をロックできます。

さらに、主キーインデックス、一意のインデックス、または通常のインデックスのいずれを使用するかにかかわらず、InnoDB は行ロックを使用してデータをロックします。

(4) 条件でインデックスフィールドが使用されている場合でも、データの取得にインデックスを使用するかどうかは、さまざまな実行プランのコストを判断して MySQL によって決定されます。一部の非常に小さなテーブルの場合、インデックスは使用されません。この場合、InnoDB は行ロックの代わりにテーブルロックを使用します。したがって、ロックの競合を分析するときは、SQL 実行計画をチェックして、インデックスが実際に使用されているかどうかを確認することを忘れないでください。 MySQL がインデックスを使用しない状況の詳細については、この章の「インデックスの問題」セクションの序論を参照してください。

7. ギャップロック(ネクストキーロック)

等値条件の代わりに範囲条件を使用してデータを取得し、共有ロックまたは排他ロックをリクエストすると、InnoDB は条件を満たす既存のデータレコードのインデックスエントリをロックします。キー値は条件範囲内にありますが、このレコードは「GAP」と呼ばれ、InnoDB もこの「ギャップ」をロックします。このロック機構は、いわゆるギャップロック (Next-Key ロック) です。 emp テーブルに 101 レコードしかない場合、empid 値は 1,2,...,100,101 になります。次の SQL:

Select * from emp where empid > 100 for update;

は範囲条件の取得で、InnoDB は条件を満たす emid 値 101 のレコードをロックするだけでなく、empid が 101 より大きい「ギャップ」もロックします (これらのレコードは存在しません)。ギャップロックを使用する InnoDB の目的は、一方ではファントム読み取りを防止し、関連する分離レベルの要件を満たすことです。上記の例では、ギャップロックが使用されていない場合、他のトランザクションが emid が 100 を超えるレコードを挿入した場合に、このトランザクションが上記のステートメントを再度実行すると、ファントム読み取りが発生しますが、その一方で、リカバリとレプリケーションのニーズを満たすためです。ロックメカニズムに対するリカバリとレプリケーションの影響、およびさまざまな分離レベルでの InnoDB のギャップロックの使用については、後続の章でさらに紹介します。

明らかに、範囲条件を使用してレコードを取得およびロックすると、InnoDB のロックメカニズムにより、修飾された範囲内でのキー値の同時挿入がブロックされ、深刻なロック待機が発生することがよくあります。したがって、実際のアプリケーション開発、特に同時挿入が多いアプリケーションでは、ビジネスロジックの最適化に最善を尽くし、更新データへのアクセスに均等条件を使用し、範囲条件の使用を避ける必要があります。

特別な注意は、InnoDB は、範囲条件でロックするときにギャップロックを使用するだけでなく、存在しないレコードのロックを要求するために等しい条件が使用された場合にもギャップロックを使用することです。

リカバリとレプリケーションの必要性、InnoDB ロックメカニズムへの影響

MySQL は、BINLOG を通じてデータを更新するための INSERT、UPDATE、DELETE およびその他の SQL ステートメントの正常な実行を記録し、次のことを実現します。 MySQL データベースのリカバリとマスター/スレーブレプリケーション。 MySQL のリカバリメカニズム (レプリケーションは実際にはスレーブ Mysql 上で継続的な BINLOG ベースのリカバリです) には次の特徴があります。

まず、MySQL のリカバリは SQL ステートメントレベルで行われます。つまり、BINLOG で SQL ステートメントを再実行します。これは、データベースファイルブロックに基づく Oracle データベースとは異なります。

第二に、MySQL の BINLOG はトランザクションが送信された順序で記録され、リカバリもこの順序で実行されます。この点も Oracle とは異なります。Oracle は、システム変更番号 (SCN) に従ってデータを復元します。各トランザクションが開始されると、Oracle はグローバルに一意の SCN を割り当てます。SCN の順序とトランザクション開始の時間順序は一貫しています。

上記の 2 つの点から、MySQL の回復メカニズムでは、トランザクションが送信される前に、他の同時トランザクションがそのロック条件を満たすレコードを挿入できないこと、つまりファントム読み取りが許可されていないことが必要であることがわかります。 ISO/ANSI SQL92 の「反復読み取り」分離レベル要件を超えていますが、実際にはトランザクションをシリアル化する必要があります。

さらに、「insert into target_tab select * from source_tab where ...」や「create table new_tab ...select ... From source_tab where ...(CTAS)」などの SQL ステートメントの場合、ユーザーsource_tab では更新操作は行われませんが、MySQL はこの種の SQL ステートメントに対して特別な処理を行います。

(ここでは、InnoDB は共有ロックをsource_tab に追加し、複数バージョンのデータ整合性読み取りテクノロジを使用しません!)
MySql の行レベルのロックとテーブルレベルのロックの詳細な説明

上記の例では、単純にデータを読み取ります。 source_tab テーブルは通常の SELECT ステートメントの実行と同等であり、一貫した読み取りを使用するだけです。 ORACLE はまさにこれを行い、MVCC テクノロジーによって実装されたマルチバージョンデータを使用して、source_tab にロックを追加することなく一貫した読み取りを実現します。 InnoDB もマルチバージョンデータを実装しており、通常の SELECT の一貫性のある読み取りにはロックを必要としないことはわかっていますが、ここでは InnoDB は共有ロックをsource_tab に追加し、マルチバージョンデータの一貫性のある読み取りテクノロジを使用しません。

MySQL がこのようなことを行うのはなぜですか?その理由は、リカバリとレプリケーションの正確性を確保するためです。ロックを行わないと、上記のステートメントの実行中に他のトランザクションがsource_tabを更新すると、誤ったデータ回復結果が生じる可能性があるためです。これを実証するために、前の例を繰り返します。違いは、session_1 がトランザクションを実行する前に、システム変数 innodb_locks_unsafe_for_binlog の値が「on」に設定されることです (デフォルト値はオフです)

MySql の行レベルのロックとテーブルレベルのロックの詳細な説明 # ＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃

上記からわかるように、システム変数 innodb_locks_unsafe_for_binlog の値を "on" に設定した後、InnoDB は source_tab をロックしなくなり、結果はアプリケーションロジックと一致します。分析されます:

#SET TIMESTAMP=1169175130 ;

BEGIN;

# at 274

#070119 10:51:57 サーバー ID 1 end_log_pos 105 クエリthread_id=1 exec_time=0 error_code=0

SET TIMESTAMP=1169175117;

source_tab set name = '8' where name = '1';

## at 379

#070119 10:52:10 サーバー ID 1 end_log_pos 406 error_code=0

SET TIMESTAMP=1169175134;

BEGIN;

## at 474

#070119 10:51:29 サーバー ID 1 end_log_pos 119 クエリ thread_id=2 exec_time=0 error_code=0

SET TIMESTAMP=1169175089;

target_tab に挿入 d1 を選択、 source_tab からの名前 name = '1';

# at 593

#070119 10:52:14 サーバー ID 1 end_log_pos 620 Xid = 7

COMMIT;

BINLOG では、更新操作の場所が INSERT であることがわかります。...SELECT の前に、この BINLOG をデータベースのリカバリに使用すると、リカバリ結果は実際のアプリケーションロジックと一致しません。コピーすると、マスターデータベースとスレーブデータベースの間で不整合が発生する可能性があります。

したがって、INSERT...SELECT... ステートメントと CREATE TABLE...SELECT... ステートメントにより、ソーステーブルへの同時更新が妨げられ、ソーステーブルのロックが待機する可能性があります。クエリが複雑な場合は、パフォーマンスに重大な問題が発生するため、アプリケーションでの使用は避ける必要があります。実際、MySQL ではこの種の SQL を非決定的 SQL と呼び、推奨されません。

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

まず、上記の例を見てください。同様に、innodb_locks_unsafe_for_binlog の値を「on」に設定して、MySQL に複数バージョンのデータ整合性読み取りの使用を強制します。ただし、その代償として、binlog を使用するとデータが正しく復元またはコピーされない可能性があるため、この方法はお勧めできません。

2 つ目は、「select * from source_tab ... Into outfile」ステートメントと「load data infile ...」ステートメントの組み合わせを使用して、間接的にこれを実現する方法です。この方法では、MySQL は source_tab をロックしません。

8. さまざまな分離レベルでの InnoDB の一貫した読み取りとロックの違い

前述したように、ロックとマルチバージョンデータは、InnoDB の一貫した読み取りと ISO/ANSI SQL92 分離の実装の鍵となります。したがって、分離レベルが異なると、SQL の処理時に InnoDB によって使用される一貫した読み取り戦略と必要なロックも異なります。同時に、データのリカバリとレプリケーションのメカニズムの特性も、一部の SQL 一貫性読み取り戦略とロック戦略に大きな影響を与えます。読者の便宜のために、これらの特性を表 20-16 に示すように要約します。

1: 分離レベルが RC の場合、ギャップロックは使用されません。公式ドキュメントでは次のように説明されています:

各一貫した読み取りは、同じトランザクション内であっても、独自の新しいデータを設定および読み取ります。

ロック読み取り (FOR UPDATE または LOCK IN SHARE MODE を使用した SELECT)、UPDATE ステートメント、および DELETE ステートメントの場合、InnoDB ロックレコードのインデックスのみを作成し、その前のギャップは作成しないため、ロックされたレコードの隣に新しいレコードを自由に挿入できます。ギャップロックは、外部キー制約のチェックと重複キーのチェックにのみ使用されます。

公式ドキュメント説明アドレスは次のとおりです: https://dev.mysql.com/doc/refman/5.5/en/innodb-transaction-isolation-levels.html

2: インデックスが一意であり、検索も一意です。ギャップロックは必要ありません。それ以外の場合はギャップロックが使用されます。公式の説明は次のとおりです:

REPEATABLE READ

これは、デフォルトの分離レベルです。 InnoDB. 同じトランザクション内の一貫した読み取りは、最初の読み取りによって確立されたスナップショットを読み取ります。これは、同じトランザクション内で複数のプレーン (ロックなし) SELECT ステートメントを発行した場合、これらの SELECT ステートメントは相互に関しても一貫していることを意味します。セクション 14.8 を参照してください。 2.3「一貫した非ロック読み取り」。

ロック読み取り (FOR UPDATE または LOCK IN SHARE MODE を使用した SELECT)、UPDATE、および DELETE ステートメントの場合、ロックはステートメントが一意の検索条件を持つ一意のインデックスを使用するかどうかによって異なります。、または範囲タイプの検索条件。

一意の検索条件を持つ一意のインデックスの場合、InnoDB は見つかったインデックスレコードのみをロックし、その前のギャップはロックしません。

他の検索条件の場合は、 InnoDB は、ギャップロックまたはネクストキーロックを使用して、スキャンされたインデックス範囲をロックし、その範囲でカバーされるギャップへの他のセッションによる挿入をブロックします。ギャップロックとネクストキーロックの詳細については、セクション14.8.1「InnoDB のロック」を参照してください。 .

公式ドキュメント説明のアドレスは: https://dev.mysql.com/doc/refman/5.5/en/innodb-transaction-isolation-levels.html

9. テーブルロックを使用する場合?

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

最初の状況は、トランザクションでほとんどまたはすべてのデータを更新する必要があり、テーブルが比較的大きい場合です。デフォルトの行ロックが使用されている場合、トランザクションの実行効率が低いだけでなく、また、他のトランザクションがロックやロックの競合のために長時間待機する可能性もあります。この場合、トランザクションの実行速度を向上させるためにテーブルロックの使用を検討できます。

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

もちろん、アプリケーション内にこれら 2 種類のトランザクションが多すぎてはいけません。そうでない場合は、MyISAM テーブルの使用を検討する必要があります。

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 で追加されたテーブルレベルのロックを解放できず、テーブルロックは UNLOCK TABLES で解放する必要があります。正しい方法については、次のステートメントを参照してください。

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

SET AUTOCOMMIT=0;

LOCK TABLES t1 WRITE, t2 READ, ...;

[ここでテーブル t1 と t2 を処理します];

COMMIT;

UNLOCK TABLES;

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

上で述べたように、MyISAM テーブルロックにはデッドロックがありません。これは、MyISAM が必要なすべてのロックを常に一度に取得するためです。デッドロックが発生しないように待機します。ロックします。しかし、InnoDB では、単一の SQL で構成されるトランザクションを除いて、段階的にロックが取得されるため、InnoDB でデッドロックが発生する可能性があると判断されます。表 20-17 にデッドロックの例を示します。

MySql の行レベルのロックとテーブルレベルのロックの詳細な説明

上記の例では、トランザクションの完了を続行するには、両方のトランザクションが相手が保持する排他ロックを取得する必要があります。この種の循環ロック待機は典型的なデッドロックです。

デッドロックが発生すると、InnoDB は通常、それを自動的に検出し、1 つのトランザクションがロックを解放してロールバックする一方で、別のトランザクションがロックを取得してトランザクションの完了を続行します。ただし、外部ロックまたはテーブルロックが関係している場合、InnoDB はデッドロックを完全に自動的に検出できないため、ロック待機タイムアウトパラメーター innodb_lock_wait_timeout を設定することで解決する必要があります。このパラメータはデッドロックの問題を解決するためだけに使用されるわけではなく、同時アクセスが比較的多い場合、必要なロックをすぐに取得できないために多数のトランザクションが中断されると、大量のコンピュータリソースを占有します。深刻なパフォーマンス上の問題を引き起こし、問題はデータベース全体にまで影響を及ぼします。適切なロック待機タイムアウトしきい値を設定することで、この状況を回避できます。

一般に、デッドロックはアプリケーションの設計の問題であり、ビジネスプロセス、データベースオブジェクトの設計、トランザクションサイズ、データベースにアクセスする SQL ステートメントを調整することで、ほとんどのデッドロックを回避できます。

以下では、例を通してデッドロックを回避するための一般的な方法をいくつか紹介します。

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

MySql の行レベルのロックとテーブルレベルのロックの詳細な説明

(2) プログラムがデータをバッチで処理する場合、各スレッドが固定順序でレコードを処理するようにデータが事前にソートされていると、パフォーマンスが大幅に低下する可能性もあります。デッドロックが発生する可能性があります。

MySql の行レベルのロックとテーブルレベルのロックの詳細な説明

(3) トランザクション内でレコードを更新したい場合は、十分なレベルのロック、つまり排他ロックを直接適用する必要があります。ユーザーが排他ロックを適用すると、他のトランザクションが同じレコードの共有ロックを取得し、ロックの競合やデッドロックが発生する可能性があるため、排他ロックを適用します。具体的なデモンストレーションについては、セクション 20.3.3 の例を参照してください。

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

MySql の行レベルのロックとテーブルレベルのロックの詳細な説明

(5) 分離レベルが READ COMMITTED の場合、両方のスレッドが最初に SELECT...FOR UPDATE を実行した場合、条件を満たすレコードがあるかどうかを確認します。、、レコードを挿入します。現時点では、挿入に成功できるのは 1 つのスレッドだけであり、もう 1 つのスレッドはロックを待機します。最初のスレッドが送信すると、2 番目のスレッドは主キーが原因でエラーになります。ただし、このスレッドはエラーを起こしますが、専用ロックを取得します！このとき、3番目のスレッドが排他ロックを申請した場合もデッドロックが発生します。

この場合、挿入操作を直接実行して主キー重複例外をキャッチするか、主キー重複エラーが発生したときに常に ROLLBACK を実行して取得した排他ロックを解放することができます。

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

デッドロックが発生した場合は、SHOW INNODB STATUS コマンドを使用して、最後のデッドロックの原因を特定できます。返される結果には、デッドロックの原因となった SQL ステートメント、トランザクションが取得したロック、待機しているロック、ロールバックされたトランザクションなど、デッドロック関連のトランザクションに関する詳細情報が含まれます。これを基にデッドロックの原因と改善策を分析できます。

次は SHOW INNODB STATUS の出力例です:

mysql> show innodb status \G

InnoDB の概要

この章では、MySQL の MyISAM に焦点を当てます。テーブルレベルのロックと InnoDB の行レベルロックの実装特性について説明し、2 つのストレージエンジンでよく発生するロックの問題と解決策について説明します。

MyISAM テーブルロックについては、主に次の点が説明されています。

(1) 共有読み取りロック (S) は互換性がありますが、共有読み取りロック (S) は排他的書き込みと互換性がありません。ロック (X) と排他的書き込みロック (X) は相互に排他的です。つまり、読み取りと書き込みはシリアルです。

(2) 特定の条件下では、MyISAM ではクエリと挿入を同時に実行できます。これを使用して、アプリケーション内の同じテーブルに対するクエリと挿入のロック競合の問題を解決できます。

(3) MyISAM のデフォルトのロックスケジュールメカニズムは書き込み優先ですが、必ずしもすべてのアプリケーションに適しているわけではありません。ユーザーは、LOW_PRIORITY_UPDATES パラメータを設定するか、INSERT、UPDATE、およびDELETE ステートメント、ロック競合。

(4) テーブルロックのロック粒度が大きく、読み取りと書き込みがシリアルであるため、更新操作が多い場合、MyISAM テーブルで重大なロック待機が発生する可能性があります。InnoDB テーブルの使用を検討できます。 . ロックの競合を減らすため。

InnoDB テーブルについて、この章では主に次の内容について説明します。

InnoDB の行ロックはロックインデックスに基づいており、インデックスを介してデータにアクセスしない場合、InnoDB はテーブルロックを使用します。

InnoDB ギャップロック (ネクストキー) メカニズムと、InnoDB がギャップロックを使用する理由を紹介します。

分離レベルが異なると、InnoDB のロックメカニズムと一貫した読み取り戦略も異なります。

MySQL のリカバリとレプリケーションは、InnoDB のロックメカニズムと一貫した読み取り戦略にも大きな影響を与えます。

ロックの競合、さらにはデッドロックを完全に回避することは困難です。

InnoDB のロック特性を理解した後、ユーザーは次のような設計と SQL の調整を通じてロックの競合とデッドロックを減らすことができます。

できるだけ低い分離レベルを使用します。

インデックスを慎重に設計し、ロックをより正確にするためにインデックスを使用してデータにアクセスするようにしてください。これにより、ロックの競合の可能性が減ります。

適切なトランザクションサイズを選択すると、小規模なトランザクションでロックが競合する可能性が低くなります。

レコードセット表示をロックする場合は、一度に十分なレベルのロックを要求するのが最善です。たとえば、データを変更する場合、最初に共有ロックを適用してから変更するときに排他ロックを要求するのではなく、排他ロックを直接適用することが最善です。これにより、デッドロックが発生しやすくなります。