mysqlロックの詳細
ロックは、コンピュータが複数のプロセスまたはスレッドを調整してリソースに同時にアクセスするメカニズムです.データベースでは、従来のコンピューティングリソース(CPU、RAM、I/Oなど)の競合に加えて、データは多くのユーザが共有できるリソースでもある.データの同時アクセスの一貫性、有効性を保証する方法は、すべてのデータベースが解決しなければならない問題であり、ロック競合もデータベースの同時アクセス性能に影響を与える重要な要素です.
MySQLには3つのロックメカニズムがあり、特性は大体以下にまとめることができる.
・表級ロック:オーバーヘッドが小さく、ロックが速い;デッドロックは発生しません.ロック粒度が大きく、ロック衝突が発生する確率が最も高く、同時度が最も低い.
・行レベルロック:オーバーヘッドが大きく、ロックが遅い;デッドロックが発生します.ロック粒度は最小であり,ロック衝突が発生する確率は最低であり,同時度も最高である.
・ページロック:オーバーヘッドとロック時間は表ロックと行ロックの間にある;デッドロックが発生します.ロック粒度はテーブル・ロックとロー・ロックの間にあり、同時性は一般的です.
他のデータベースに比べて、MySQLのロックメカニズムは比較的簡単で、その最も顕著な特徴は異なるストレージエンジンが異なるロックメカニズムをサポートすることである.例えば、MyISAMとMEMORYストレージエンジンはテーブルレベルロック(table-level locking)を採用している.BBBストレージエンジンはページロック(page-level locking)を採用しているが、テーブルレベルロックもサポートされている.InnoDBストレージエンジンでは、行レベルロック(row-level locking)もテーブルレベルロックもサポートされていますが、デフォルトでは行レベルロックが採用されています.
上記の特徴から、どちらのロックがより良いかを大まかに言うのは難しいが、具体的な応用特徴についてどのロックがより適切かしか言えない.ロックの観点からのみ:
表レベルのロックはクエリーを主とするのに適しており、Webアプリケーションなどのインデックス条件でデータを更新するアプリケーションは少ない.
ロー・レベル・ロックは、オンライン・トランザクション(OLT P)システムなど、インデックス条件に基づいて少量の異なるデータを同時に更新するアプリケーションに適しています.
オーバーヘッドデッドロックロック粒度、ロック競合、同時性.MyISAMテーブルの同時挿入
MyISAMテーブルロックについて説明します
テーブル・レベルのロック競合の問合せ
table_をチェックすることでlocks_waitedとtable_locks_immediateステータス変数は、システム上のテーブルロック競合を分析します.
Table_locks_waitedの値が高い場合は、テーブルレベルのロック競合が深刻であることを示します.
MySQLのテーブルレベルロックには、テーブル共有リードロック(Table Read Lock)とテーブル独占ライトロック(Table Write Lock)の2つのモードがあります.
MySQLでのテーブル・ロックの互換性
要求ロックモードが現在のロックモードと互換性があるかどうか
None
リードロック
書き込みロック
リードロック
はい
はい
いいえ
書き込みロック
はい
いいえ
いいえ
MyISAMテーブルの読み書きはシリアルですが、これは全体的に言えば.一定の条件の下で、MyISAMテーブルもクエリーと挿入操作の同時進行をサポートします.
MyISAMストレージエンジンにはシステム変数concurrent_があります.Insertは、同時挿入の動作を制御するために特化され、その値はそれぞれ0、1または2であってもよい.
・concurrent_Insertが0に設定されている場合、同時挿入は許可されません.
・concurrent_Insertが1に設定されている場合、MyISAMテーブルに空きがない場合(すなわち、テーブルの真ん中に削除されたローがない場合)、MyISAMは、あるプロセスがテーブルを読みながら、別のプロセスがテーブルの最後からレコードを挿入することを許可します.これもMySQLのデフォルト設定です.
・concurrent_Insertが2に設定されている場合、MyISAMテーブルに空洞があるかどうかにかかわらず、テーブルの最後にレコードを同時に挿入できます.
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文に対して一定の“分解”をすることができて、すべてのステップのクエリーを比较的に短い时间で完成させることができて、複雑なクエリーが避けられない場合は、データベースの空き時間帯にできるだけ実行するように手配する必要があります.たとえば、定期統計の一部は夜間に実行するように手配できます.
デッドロック:
デッドロック＜DeadLock＞とは、2つ以上のプロセスが実行中で、
資源を争うことによる互いに待つ現象であり、外力の作用がなければ、それらは推進できない.
この場合、システムがデッドロック状態にあるか、システムがデッドロックを発生したと称する、これらは永遠に互いに等竺するプロセスをデッドロックプロセスと呼ぶ.
MySQLには3つのロックメカニズムがあり、特性は大体以下にまとめることができる.
・表級ロック:オーバーヘッドが小さく、ロックが速い;デッドロックは発生しません.ロック粒度が大きく、ロック衝突が発生する確率が最も高く、同時度が最も低い.
・行レベルロック:オーバーヘッドが大きく、ロックが遅い;デッドロックが発生します.ロック粒度は最小であり,ロック衝突が発生する確率は最低であり,同時度も最高である.
・ページロック:オーバーヘッドとロック時間は表ロックと行ロックの間にある;デッドロックが発生します.ロック粒度はテーブル・ロックとロー・ロックの間にあり、同時性は一般的です.
他のデータベースに比べて、MySQLのロックメカニズムは比較的簡単で、その最も顕著な特徴は異なるストレージエンジンが異なるロックメカニズムをサポートすることである.例えば、MyISAMとMEMORYストレージエンジンはテーブルレベルロック(table-level locking)を採用している.BBBストレージエンジンはページロック(page-level locking)を採用しているが、テーブルレベルロックもサポートされている.InnoDBストレージエンジンでは、行レベルロック(row-level locking)もテーブルレベルロックもサポートされていますが、デフォルトでは行レベルロックが採用されています.
上記の特徴から、どちらのロックがより良いかを大まかに言うのは難しいが、具体的な応用特徴についてどのロックがより適切かしか言えない.ロックの観点からのみ:
表レベルのロックはクエリーを主とするのに適しており、Webアプリケーションなどのインデックス条件でデータを更新するアプリケーションは少ない.
ロー・レベル・ロックは、オンライン・トランザクション(OLT P)システムなど、インデックス条件に基づいて少量の異なるデータを同時に更新するアプリケーションに適しています.
オーバーヘッドデッドロックロック粒度、ロック競合、同時性.MyISAMテーブルの同時挿入
MyISAMテーブルロックについて説明します
テーブル・レベルのロック競合の問合せ
table_をチェックすることでlocks_waitedとtable_locks_immediateステータス変数は、システム上のテーブルロック競合を分析します.
mysql> show status like 'table%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| Table_locks_immediate | 2979 |
| Table_locks_waited | 0 |
+-----------------------+-------+
2 rows in set (0.00 sec))Table_locks_waitedの値が高い場合は、テーブルレベルのロック競合が深刻であることを示します.
MySQLのテーブルレベルロックには、テーブル共有リードロック(Table Read Lock)とテーブル独占ライトロック(Table Write Lock)の2つのモードがあります.
MySQLでのテーブル・ロックの互換性
要求ロックモードが現在のロックモードと互換性があるかどうか
None
リードロック
書き込みロック
リードロック
はい
はい
いいえ
書き込みロック
はい
いいえ
いいえ
MyISAMテーブルの読み書きはシリアルですが、これは全体的に言えば.一定の条件の下で、MyISAMテーブルもクエリーと挿入操作の同時進行をサポートします.
MyISAMストレージエンジンにはシステム変数concurrent_があります.Insertは、同時挿入の動作を制御するために特化され、その値はそれぞれ0、1または2であってもよい.
・concurrent_Insertが0に設定されている場合、同時挿入は許可されません.
・concurrent_Insertが1に設定されている場合、MyISAMテーブルに空きがない場合(すなわち、テーブルの真ん中に削除されたローがない場合)、MyISAMは、あるプロセスがテーブルを読みながら、別のプロセスがテーブルの最後からレコードを挿入することを許可します.これもMySQLのデフォルト設定です.
・concurrent_Insertが2に設定されている場合、MyISAMテーブルに空洞があるかどうかにかかわらず、テーブルの最後にレコードを同時に挿入できます.
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文に対して一定の“分解”をすることができて、すべてのステップのクエリーを比较的に短い时间で完成させることができて、複雑なクエリーが避けられない場合は、データベースの空き時間帯にできるだけ実行するように手配する必要があります.たとえば、定期統計の一部は夜間に実行するように手配できます.
デッドロック:
デッドロック＜DeadLock＞とは、2つ以上のプロセスが実行中で、
資源を争うことによる互いに待つ現象であり、外力の作用がなければ、それらは推進できない.
この場合、システムがデッドロック状態にあるか、システムがデッドロックを発生したと称する、これらは永遠に互いに等竺するプロセスをデッドロックプロセスと呼ぶ.