同時データ アクセスの一貫性と有効性を確保する方法は、すべてのデータベースが解決しなければならない問題です。ロックの競合も、データベースへの同時アクセスのパフォーマンス係数。この観点から見ると、ロックはデータベースにとって特に重要かつ複雑です。
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;
(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;
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 によって自動的に追加されるため、ユーザーの介入は必要ありません。
要約は次のとおりです:
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 をロックしなくなり、結果はアプリケーション ロジックと一致します。分析されます:
#SET TIMESTAMP=1169175130 ;BEGIN;# at 274#070119 10:51:57 サーバー ID 1 end_log_pos 105 クエリthread_id=1 exec_time=0 error_code=0SET 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=0SET TIMESTAMP=1169175089;target_tab に挿入 d1 を選択、 source_tab からの名前 name = '1';# at 593#070119 10:52:14 サーバー ID 1 end_log_pos 620 Xid = 7COMMIT;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.html2: インデックスが一意であり、検索も一意です。ギャップ ロックは必要ありません。それ以外の場合はギャップ ロックが使用されます。公式の説明は次のとおりです: 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 にデッドロックの例を示します。
上記の例では、トランザクションの完了を続行するには、両方のトランザクションが相手が保持する排他ロックを取得する必要があります。この種の循環ロック待機は典型的なデッドロックです。
デッドロックが発生すると、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 の調整を通じてロックの競合とデッドロックを減らすことができます。
できるだけ低い分離レベルを使用します。
インデックスを慎重に設計し、ロックをより正確にするためにインデックスを使用してデータにアクセスするようにしてください。これにより、ロックの競合の可能性が減ります。
適切なトランザクション サイズを選択すると、小規模なトランザクションでロックが競合する可能性が低くなります。
レコード セット表示をロックする場合は、一度に十分なレベルのロックを要求するのが最善です。たとえば、データを変更する場合、最初に共有ロックを適用してから変更するときに排他ロックを要求するのではなく、排他ロックを直接適用することが最善です。これにより、デッドロックが発生しやすくなります。
異なるプログラムがテーブルのグループにアクセスするときは、各テーブルに同じ順序でアクセスすることに同意する必要があります。テーブルの場合は、テーブル内の行に固定された順序でアクセスするようにしてください。これにより、デッドロックの可能性が大幅に減少します。
同時挿入に対するギャップ ロックの影響を避けるために、データにアクセスする際には等しい条件を使用するようにしてください。
実際の必要性を超えるロック レベルを適用しないでください。必要な場合を除き、クエリ時にロックを表示しないでください。
一部の特定のトランザクションでは、テーブル ロックを使用して処理速度を向上させたり、デッドロックの可能性を減らしたりできます。