mysqlのロック機構の原理を詳しく解説(1)

Lock は、コンピューターが複数のプロセスまたはスレッドを調整してリソースに同時にアクセスするためのメカニズムです。データベースでは、コンピューティング リソース (CPU、RAM、I/O など) をめぐる従来の競争に加え、データは多くのユーザーによって共有されるリソースでもあります。データへの同時アクセスの一貫性と有効性をどのように確保するかは、すべてのデータベースが解決しなければならない問題です。ロックの競合も、データベースへの同時アクセスのパフォーマンスに影響を与える重要な要素です。この観点から見ると、ロックはデータベースにとって特に重要かつ複雑です。この章では、MySQL ロック メカニズムの特性、一般的なロックの問題、および MySQL ロックの問題を解決するためのいくつかの方法や提案に焦点を当てます。
Mysql は、行ロック、テーブル ロック、読み取りロック、書き込みロックなどの多くのロック メカニズムを使用しており、これらはすべて操作前にロックされます。これらのロックは総称して悲観的ロックと呼ばれます。

MySQL ロックの概要

他のデータベースと比較して、MySQL のロック メカニズムは比較的シンプルであり、その最大の特徴はエンジンがサポートするストレージが異なることです。さまざまなロック機構。たとえば、MyISAM および MEMORY ストレージ エンジンはテーブル レベルのロック (テーブル レベル ロック) を使用し、BDB ストレージ エンジンはテーブル レベルのロックを使用します。ページ ロック (ページ レベル ロック ) だけでなく、テーブル レベル ロック もサポートします。InnoDB ストレージ エンジンは両方の行レベル ロック (行レベル ロック ) をサポートします。テーブルレベルのロックはサポートされていますが、デフォルトでは行レベルのロックが使用されます。
テーブル レベルのロック: 低いオーバーヘッド、高速なロック、デッドロックなし、大きなロック粒度、ロック競合の可能性が最も高く、同時実行性が最も低い。
行レベルのロック: オーバーヘッドが高く、ロックが遅い; デッドロックが発生します; ロックの粒度は最も小さく、ロック競合の可能性は最も低く、同時実行性は最も高くなります。
ページ ロック: オーバーヘッドとロック時間はテーブル ロックと行ロックの間であり、デッドロックが発生します。ロックの粒度はテーブル ロックと行ロックの間で、同時実行性は平均的です。上記の特性から、一般にどのロックが優れているかを言うのは難しいことがわかります。特定のアプリケーションの特性に基づいて、どのロックがより適切であるとしか言えません。ロックの観点から見ると、テーブル レベルのロックは、Web アプリケーションなど、主にクエリベースで、インデックス条件に従って少量のデータのみを更新するアプリケーションに適していますが、行レベルのロックは、アプリケーションに適しています。インデックス条件と少数の異なるデータに基づいて多数の同時更新を行うデータ、および一部のオンライン トランザクション処理 (OLTP) システムなどの同時クエリ アプリケーション。

MyISAM テーブル ロック

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

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

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

スレッドがテーブルの書き込みロックを取得すると、ロックを保持しているスレッドのみがテーブルを更新できます。他のスレッドからの読み取りおよび書き込み操作は、ロックが解放されるまで待機します。

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

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

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

MyISAM は、クエリ ステートメント (SELECT) を実行する前に、関連するすべてのテーブルに読み取りロックを自動的に追加します。更新操作 (UPDATE、DELETE、INSERT など) を実行する前に、関連するテーブルに書き込みロックを自動的に追加します。このプロセスユーザーの介入は必要ないため、通常、ユーザーは LOCK TABLE コマンドを直接使用して MyISAM テーブルを明示的にロックする必要はありません。この例では、明示的なロックは主にデモ目的であり、必須ではありません。
MyISAM テーブルの表示ロックは、通常、トランザクション操作をある程度シミュレートし、特定の時点での複数のテーブルの一貫した読み取りを実現するために行われます。たとえば、各注文の合計金額を記録する注文テーブル order と、各注文の各商品の小計金額を記録する注文詳細テーブル order_detail があり、この 2 つのテーブルを確認する必要があるとします。合計金額が一致するかどうかを確認するには、次の 2 つの SQL を実行する必要がある場合があります。

Select sum(total) from orders;
Select sum(subtotal) from order_detail;

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

Lock tables orders read local, order_detail read local;
Select sum(total) from orders;
Select sum(subtotal) from order_detail;
Unlock tables;

特に次の 2 点に注意してください:
1. 上記の例では、同時実行性を満たすために使用される LOCK TABLES に "local" オプションを追加しています。 MyISAM テーブルの同時挿入の問題については、後ほど詳しく説明します。
2. LOCK TABLES を使用して明示的にテーブル ロックをテーブルに追加する場合、テーブルに関係するすべてのロックを同時に取得する必要があり、MySQL はロックのアップグレードをサポートしていません。つまり、LOCK TABLES の実行後は、明示的にロックされたテーブルにのみアクセスでき、ロックが解除されたテーブルにはアクセスできなくなります。同時に、読み取りロックを追加すると、クエリ操作のみが実行でき、更新操作は実行できなくなります。実際、これは基本的に自動ロックの場合に当てはまり、MyISAM は常に SQL ステートメントに必要なすべてのロックを一度に取得します。これが、MyISAM テーブルがデッドロックにならない (デッドロック フリー) 理由です。

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

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

mysql> lock table actor read; 
Query OK, 0 rows affected (0.00 sec)

(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 and a.first_name = &#39;Lisa&#39; and a.last_name = &#39;Tom&#39; and a.last_name <> b.last_name;
ERROR 1100 (HY000): Table ‘a’ was not locked with LOCK TABLES

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

mysql> lock table actor as a read,actor as b read;
Query OK, 0 rows affected (0.00 sec)

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

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 
and a.first_name = &#39;Lisa&#39; and a.last_name = &#39;Tom&#39; and a.last_name <> b.last_name;
+————+———–+————+———–+ 
| first_name | last_name | first_name | last_name | 
+————+———–+————+———–+ 
| Lisa | Tom | LISA | MONROE | 
+————+———–+————+———–+ 
1 row in set (0.00 sec)

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

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

mysql> show status like 'table%';
1Variable_name | Value 
Table_locks_immediate | 2979 
Table_locks_waited | 0 
2 rows in set (0.00 sec))

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

同時挿入

前述したように、MyISAM テーブルの読み取りと書き込みはシリアルですが、これは全体的なものです。特定の条件下では、MyISAM テーブルは同時クエリと挿入操作もサポートします。
MyISAM ストレージ エンジンには、同時挿入動作を制御するために特別に使用されるシステム変数 concurrent_insert があり、その値はそれぞれ 0、1、または 2 です。

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

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

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

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

上で述べたように、MyISAM テーブルの読み取りと書き込みはシリアルですが、これは一般的に言えます。特定の条件下では、MyISAM テーブルは同時クエリと挿入操作もサポートします。
MyISAM ストレージ エンジンには、同時挿入動作を制御するために特別に使用されるシステム変数 concurrent_insert があり、その値はそれぞれ 0、1、または 2 です。

concurrent_insert が 0 に設定されている場合、同時挿入は許可されません。 concurrent_insert が 1 に設定されている場合、MyISAM テーブルにホールがない (つまり、テーブルの途中に削除された行がない) 場合、MyISAM は 1 つのプロセスがテーブルを読み取ることを許可し、その間に別のプロセスはテーブルの最後からレコードを挿入します。テーブル。これは MySQL のデフォルト設定でもあります。 concurrent_insert が 2 に設定されている場合、MyISAM テーブルにホールがあるかどうかに関係なく、テーブルの最後にレコードを同時に挿入できます。

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

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

MyISAM ストレージ エンジンの同時挿入機能を使用して、次の問題を解決できます。アプリケーションに同じテーブルを挿入する クエリと挿入のロック競合。たとえば、concurrent_insert システム変数を 2 に設定すると、常に同時挿入が許可されます。同時に、システムのアイドル期間中に OPTIMIZE TABLE ステートメントが定期的に実行され、領域の断片化が解消され、レコードの削除によって生じた中間ホールが回復されます。

MyISAM ロックのスケジュール

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

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

2. コマンド SET LOW_PRIORITY_UPDATES=1 を実行して、この接続によって発行される更新リクエストの優先度を下げます。

3. INSERT、UPDATE、および DELETE ステートメントの LOW_PRIORITY 属性を指定して、ステートメントの優先順位を下げます。

上記の 3 つの方法は更新が先かクエリが先ですが、クエリが比較的重要なアプリケーション (ユーザー ログイン システムなど) での読み取りロック待機という深刻な問題を解決するために使用できます。
さらに、MySQL は、読み取りと書き込みの競合を調整するための妥協方法、つまり、システム パラメータ max_write_lock_count に適切な値を設定する方法も提供します。テーブルの読み取りロックがこの値に達すると、MySQL は一時的に書き込みリクエストをキャンセルします。優先度が低くなり、読み取りプロセスにロックを取得する一定のチャンスが与えられます。

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

InnoDB ロックについては後ほど説明します。

その他の関連する質問については、PHP 中国語 Web サイトを参照してください: Mysql ビデオ チュートリアル

以上がmysqlのロック機構の原理を詳しく解説(1)の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。