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

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 % ';

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

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;

| 1001 | テスト |

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 つの分離レベルの特性をわかりやすくまとめたものです。

それぞれの特定のデータベースは、上記の 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%';

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 ロックを取得する必要があります。

トランザクションによって要求されたロック モードが現在のロックと互換性がある場合、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 に追加し、複数バージョンのデータ整合性読み取りテクノロジを使用しません!)

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

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

# ＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃＃

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

## at 379

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

SET TIMESTAMP=1169175134;

BEGIN;

公式ドキュメント説明のアドレスは: 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 にデッドロックの例を示します。

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

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

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

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

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

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

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

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

(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 の調整を通じてロックの競合とデッドロックを減らすことができます。

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

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

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

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

異なるプログラムがテーブルのグループにアクセスするときは、各テーブルに同じ順序でアクセスすることに同意する必要があります。テーブルの場合は、テーブル内の行に固定された順序でアクセスするようにしてください。これにより、デッドロックの可能性が大幅に減少します。

同時挿入に対するギャップ ロックの影響を避けるために、データにアクセスする際には等しい条件を使用するようにしてください。

実際の必要性を超えるロック レベルを適用しないでください。必要な場合を除き、クエリ時にロックを表示しないでください。

一部の特定のトランザクションでは、テーブル ロックを使用して処理速度を向上させたり、デッドロックの可能性を減らしたりできます。