MySQLのロック構造の詳細な説明

Mysqlは3種類のロック構造をサポートしています

テーブルレベルのロック、低オーバーヘッド、高速ロック、デッドロックなし、ロック粒度が大きい、競合の可能性が高い、同時実行性が最も低い
行レベルのロック、低オーバーヘッド、低速ロック、デッドロック、小さなロック粒度、最低の競合確率、最高の同時実行性
ページロック、オーバーヘッド、ロックはテーブルロックと行ロックの間にあり、デッドロックが発生する可能性があり、ロックの粒度はテーブルと行に基づいており、同時実行性は一般的に

InnoDB ロックの問題

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

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

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

共有ロック: 1 つのトランザクションが行を読み取ることを許可し、他のトランザクションが同じデータセットに対して排他ロックを取得することを防ぎます。
排他ロック (X): 排他ロックを取得したトランザクションがデータを更新できるようにし、他のトランザクションが同じデータセットに対して共有読み取りロックや排他書き込みロックを取得することを防ぎます。

さらに、行ロックとテーブルロックを共存させ、多粒度ロック機構を実装するために、InnoDB には内部的に使用される 2 つのインテンションロック (Intention Locks) があり、どちらもテーブルロックです。

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

現在のロックモードと要求されたロックモード	バツ	9 章	ス	は
バツ	対立	対立	対立	対立
9 章	対立	互換性がある	対立	互換性がある
ス	対立	対立	互換性がある	互換性がある
は	対立	互換性がある	互換性がある	互換性がある

InnoDB の行ロックはインデックス上のインデックス項目を通じて実装されます。これは、データ内の対応するデータ行をロックすることによって実装する MySQL や Oracle とは異なります。 InnoDB のこの行ロック実装機能は、InnoDB がインデックス条件を通じてデータを取得する場合にのみ行レベルのロックを使用し、それ以外の場合はテーブルロックを使用することを意味します。

ネクストキーロック

等価条件ではなく範囲条件を使用してデータを取得し、共有ロックまたは排他ロックを要求すると、InnoDB は条件を満たす既存のデータのインデックス項目をロックします。キー値が条件範囲内にあるが存在しないレコードについては「ギャップ」と呼ばれ、InnoDB はこの「ギャップ」もロックします。このロックメカニズムは、いわゆるギャップロック (Next-Key ロック) ではありません。
たとえば、emp テーブルに 101 件のレコードしかなく、empid 値がそれぞれ 1、2、...、100、101 の場合、次の SQL になります。

emp から * を選択して、empid > 100 で更新します。

これは範囲条件検索です。InnoDB は、条件を満たす empid 値が 101 のレコードをロックするだけでなく、empid 値が 101 より大きい「ギャップ」(これらのレコードは存在しません) もロックします。
InnoDB がギャップロックを使用する目的は、一方では、関連する分離レベルの要件を満たすためにファントムリードを防止することです。上記の例では、ギャップロックが使用されていない場合、他のトランザクションが empid が 100 より大きいレコードを挿入し、このトランザクションが上記のステートメントを再度実行すると、ファントムリードが発生します。他方では、リカバリとレプリケーションのニーズを満たすためです。リカバリとレプリケーションのメカニズムの影響、およびさまざまな分離レベルでの InnoDB によるギャップロックの使用について説明します。
当然のことながら、範囲条件を使用してレコードを取得およびロックする場合、InnoDB のロックメカニズムによって、条件を満たすキー値の同時挿入がブロックされ、深刻なロック待機が発生することがよくあります。したがって、実際の開発では、特に同時挿入が多いアプリケーションの場合は、ビジネスロジックを最適化し、等価条件を使用してデータにアクセスして更新し、範囲条件の使用を避けるように最善を尽くす必要があります。

テーブルロックはいつ使用すればよいですか?

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

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

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

もちろん、アプリケーション内でこれら 2 種類のトランザクションが多すぎるべきではありません。多すぎる場合は、MyISAM テーブルの使用を検討する必要があります。
InnoDB では、テーブルロックを使用する際に次の 2 つの点に注意してください。

(1) LOCK TALBES を使用して InnoDB にテーブルレベルのロックを追加できますが、テーブルロックは InnoDB ストレージエンジンレイヤーではなく、上位レイヤーの MySQL Server によって管理されることに注意してください。autocommit=0 および innodb_table_lock=1 (デフォルト設定) の場合にのみ、InnoDB レイヤーは MySQL によって追加されたテーブルロックを認識し、MySQL Server は InnoDB によって追加された行ロックを認識できます。この場合、InnoDB はテーブルレベルのロックを含むデッドロックを自動的に識別できます。それ以外の場合、InnoDB はそのようなデッドロックを自動的に検出して処理できません。

(2) LOCAK TABLES を使用して InnoDB をロックする場合、AUTOCOMMIT を 0 に設定するように注意してください。そうしないと、MySQL はテーブルをロックしません。トランザクションが終了する前に UNLOCAK TABLES を使用してテーブルロックを解除しないでください。UNLOCK TABLES は暗黙的にトランザクションをコミットします。COMMIT または ROLLBACK は、LOCAK TABLES によって追加されたテーブルレベルのロックを解除できません。テーブルロックを解除するには、UNLOCK TABLES を使用する必要があります。正しい方法は、次のステートメントに示されています。
たとえば、テーブル t1 に書き込み、テーブル t から読み取る必要がある場合は、次のように実行できます。

AUTOCOMMIT=0 を設定します。
テーブルを t1 書き込み、t2 読み取り、... にロックします。
[テーブル t1 とここで何かを実行します];
専念;
テーブルのロックを解除します。

デッドロック

InnoDB では、単一の SQL 文で構成されるトランザクションを除き、ロックが段階的に取得されるため、InnoDB がデッドロックする可能性があります。
デッドロックが発生すると、InnoDB は通常それを自動的に検出し、1 つのトランザクションにロックを解除させてロールバックさせ、もう 1 つのトランザクションにロックを取得させてトランザクションの完了を続行させます。ただし、外部ロックまたはロックが関係する場合、InnoDB はデッドロックを完全に自動的に検出できないため、ロック待機タイムアウトパラメータ innodb_lock_wait_timeout を設定して解決する必要があります。このパラメータはデッドロック問題を解決するためだけに使用されるのではないことに注意してください。同時アクセスが多い場合、必要なロックをすぐに取得できないために多数のトランザクションが中断されると、大量のコンピュータリソースが占有され、深刻なパフォーマンスの問題を引き起こし、データベースのパフォーマンスが低下することもあります。適切なロック待機タイムアウトしきい値を設定することで、この状況を回避できます。

一般的に、デッドロックはアプリケーション設計の問題であり、ビジネスプロセス、データベースオブジェクトの設計、トランザクションサイズ、およびデータベースへのアクセスに使用される SQL ステートメントを調整することで、ほとんどの場合回避できます。次の例では、デッドロックの一般的な方法をいくつか紹介します。

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

（２）プログラムがバッチ処理でデータを処理するとき、各スレッドが一定の順序でレコードを処理するように事前にデータをソートしておけば、デッドロックの可能性を大幅に減らすことができます。

（３）トランザクションにおいて、レコードを更新する場合には、まず共有ロックを適用し、更新時に排他ロックを適用するのではなく、十分なレベルのロック、つまり排他ロックを直接適用する必要があります。そうしないと、デッドロックが発生する可能性があります。

（4）REPEATEABLE-READ分離レベルでは、2つのスレッドが同時にSELECT ... ROR UPDATEを使用して同じ条件レコードに排他ロックを追加すると、条件に一致するレコードがない場合、両方のスレッドがロックに成功します。プログラムはレコードがまだ存在しないことを検出し、新しいレコードを挿入しようとします。 2 つのスレッドがこれを行うと、デッドロックが発生します。この場合、分離レベルを READ COMMITTED に変更すると問題を回避できます。

（５）分離レベルがREAD COMMITEDの場合、両方のスレッドが最初にSELECT...FOR UPDATEを実行すると、条件を満たすレコードがあるかどうかが判断されます。ない場合は、レコードを挿入します。この時点では、挿入を正常に実行できるのは 1 つのスレッドのみで、他のスレッドはロックを待機します。最初のスレッドが送信すると、2 番目のスレッドは主キーが原因でエラーになりますが、このスレッドはエラーが発生しているにもかかわらず、排他ロックを取得します。このとき、3 番目のスレッドが再度排他ロックを申請すると、デッドロックが発生します。この場合、挿入操作を直接実行してから主キー重複例外をキャッチするか、主キー重複エラーが発生した場合は必ず ROLLBACK を実行して取得した排他ロックを解放します。

MyISAM と InnoDB の違い

MyISAMテーブルロックについては、主に以下の点が挙げられます。

（１）共有読み取りロック（S）は互いに互換性がありますが、共有読み取りロック（S）と排他書き込みロック（X）、および排他書き込みロック（X）は相互に排他的であり、読み取りと書き込みはシリアルになります。
（2）特定の条件下では、MyISAM はクエリと挿入を同時に実行できます。これを利用して、アプリケーション内で同じテーブルと挿入に対するロック競合の問題を解決できます。
(3) MyISAM のデフォルトのロックスケジューリングメカニズムは書き込み優先ですが、必ずしもすべてのアプリケーションに適しているわけではありません。ユーザーは、LOW_PRIPORITY_UPDATES パラメータを設定するか、INSERT、UPDATE、および DELETE ステートメントで LOW_PRIORITY オプションを指定することにより、読み取り/書き込みロックの競合を調整できます。
（4）テーブルロックはロック粒度が大きく、読み取りと書き込みがシリアルであるため、更新操作が多いとMyISAMテーブルで深刻なロック待機が発生する可能性があります。ロックの競合を減らすためにInnoDBテーブルの使用を検討できます。

InnoDBテーブルの場合、主なポイントは次のとおりです。

(1) InnoDBのマーケティングはインデックスに基づいています。データがインデックスを介してアクセスされない場合、InnoDBはテーブルロックを使用します。
（２）InnoDBギャップロックの仕組みとInnoDBがギャップロックを使用する理由。
（3）分離レベルが異なると、InnoDBのロックメカニズムと一貫性のある読み取り戦略が異なります。
（4）MySQLのリカバリとレプリケーションは、InnoDBのロックメカニズムと一貫性のある読み取り戦略にも大きな影響を与えます。
（５）ロックの競合やデッドロックを完全に回避することは困難である。

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

可能であれば、より低い分離レベルを使用する
インデックスを慎重に設計し、可能な限りインデックスを使用してデータにアクセスし、ロックをより正確にして、ロックの競合の可能性を減らします。
適切なトランザクションサイズを選択します。トランザクションが小さいと、ロックの競合が発生する可能性が低くなります。
レコードセットを明示的にロックする場合は、一度に十分なレベルのロックを求めるのが最適です。たとえば、データを変更する場合は、最初に共有ロックを適用し、変更時に排他ロックを要求すると簡単にデッドロックが発生する可能性があるため、排他ロックを直接適用するのが最適です。
異なるプログラムが一連のテーブルにアクセスする場合、同じ順序でテーブルにアクセスするようにする必要があります。テーブルの場合は、固定された順序で行にアクセスするようにします。これにより、デッドロックの可能性が大幅に減少します。
同時挿入に対するギャップロックの影響を回避するには、等しい条件を使用してデータにアクセスするようにしてください。
実際に必要なロックレベルよりも多くのロックレベルを適用しないでください。必要な場合を除き、クエリ時にロックを表示しないでください。
特定のトランザクションでは、テーブルロックを使用して処理速度を上げたり、デッドロックの可能性を減らしたりすることができます。

MySql 楽観的ロック悲観的ロック

悲観的ロック

悲観的ロックの特徴は、まずロックを取得してから業務操作を実行することです。つまり、ロックの取得は失敗する可能性が高いと考えるのが「悲観的」であるため、業務操作を実行する前にロックが正常に取得されていることを確認する必要があります。いわゆる「1 つのロック、2 つのチェック、3 つの更新」は、通常、悲観的ロックの使用を指します。一般的に、データベースでの悲観的ロックにはデータベース自体のサポートが必要です。つまり、悲観的ロックは、一般的に使用される select ... for update 操作を通じて実装されます。データベースが更新のための選択を実行すると、選択されたデータ行の行ロックを取得します。したがって、同時に実行された他の更新のための選択が同じ行を選択しようとすると、それらは排除され（行ロックが解除されるまで待機する必要があります）、ロック効果が得られます。 select for update によって取得された行ロックは、現在のトランザクションの終了時に自動的に解放されるため、トランザクション内で使用する必要があります。

ここで注意すべき点は、データベースによって select for update の実装とサポートが異なることです。たとえば、Oracle は select for update no wait をサポートしています。つまり、ロックを取得できない場合は、待機せずにすぐにエラーが報告されます。MySQL には no wait オプションはありません。 MySQL のもう 1 つの問題は、select for update ステートメントの実行中にスキャンされたすべての行がロックされ、簡単に問題が発生する可能性があることです。したがって、MySQL で悲観的ロックを使用する場合は、テーブル全体のスキャンではなくインデックスを使用するようにしてください。

楽観的ロック

楽観的ロックの特徴は、まずビジネス操作を実行し、絶対に必要な場合を除いてロックを取得しないことです。つまり、ロックの取得は成功する可能性が高いと「楽観的に」考えているため、実際にデータを更新するビジネス操作の最後のステップでのみロックを取得する必要があります。

データベースでの楽観的ロックの実装は完全に論理的であり、データベースからの特別なサポートは必要ありません。一般的なアプローチは、ロックする必要があるデータにバージョン番号またはタイムスタンプを追加し、次のように実装することです。

1. データ AS old_data、バージョン AS old_version FROM … を選択します。
2. 取得したデータに基づいて業務処理を実行し、new_dataとnew_versionを取得する
3. SET data = new_data、version = new_version、WHERE version = old_version を更新します。
if (更新された行 > 0) {
  // 楽観的ロックの取得が成功し、操作が完了しました} else {
  // 楽観的ロックの取得に失敗しました。ロールバックして再試行します}

データベース内の同じ行を更新する場合、同時実行は許可されません。つまり、データベースが更新ステートメントを実行するたびに、更新された行の書き込みロックが取得され、行が正常に更新されるまでロックが解放されません。そのため、業務操作を実行する前に、ロックする必要があるデータの現在のバージョン番号を取得し、実際にデータが更新されるときに、バージョン番号を再度比較して、前回取得したバージョン番号と同じであることを確認し、その間に同時変更が発生していないことを確認するためにバージョン番号を更新します。更新に失敗した場合、古いバージョンのデータが同時に変更され、存在しなくなったと考えられます。このとき、ロックの取得に失敗したとみなされ、業務操作全体をロールバックし、必要に応じてプロセス全体を再試行する必要があります。

ロック取得失敗がない場合、楽観的ロックは悲観的ロックよりもオーバーヘッドが低くなりますが、失敗が発生するとロールバックのオーバーヘッドが比較的大きくなります。そのため、ロック取得失敗の確率が比較的小さいシナリオに適しており、システムの同時実行パフォーマンスを向上させることができます。楽観的ロックは、業務操作中にデータベースとの接続を維持できない場合など、悲観的ロックを適用できない特別なシナリオにも適用できます。

以上がMySQLのロック構造の詳しい説明です。MySQLのロック構造についてさらに詳しく知りたい方は、123WORDPRESS.COMの他の関連記事もぜひご覧ください。

以下もご興味があるかもしれません: