MySQLロックの詳細
4317 ワード
MySQLロック
他のデータベースに比べて、MySQLのロックメカニズムは簡単で、最も顕著な特徴は異なるストレージエンジンが異なるロックメカニズムをサポートすることです.異なるストレージエンジンによって、MySQLのロックの特性は大体以下のようにまとめることができる.
行ロック
テーブルロック
ページロック
MyISAM
√
BDB
√
√
InnoDB
√
√
オーバーヘッド、ロック速度、デッドロック、粒度、同時パフォーマンス表ロック:オーバーヘッドが小さく、ロックが速い.デッドロックは発生しません.ロック力が大きく、ロック衝突が発生する確率が高く、同時度が最低 である.行ロック:オーバーヘッドが大きく、ロックが遅い.デッドロックが発生します.ロック粒度が小さく、ロック衝突が発生する確率が低く、同時度が 高い.ページロック:オーバーヘッドとロック速度がテーブルロックとローロックの間にある.デッドロックが発生します.ロック粒度は表ロックと行ロックの間にあり、同時性は一般的に である.
テーブル・ロックは、クエリーを主とし、インデックス条件でデータを更新するアプリケーションが少ない場合に適しています.ロー・ロックは、インデックス条件に基づいて少量の異なるデータを同時に大量に更新し、同時に同時クエリーを実行するアプリケーションに適しています.PS:BDBは既にInnoDBに取って代わられているため,MyISAMテーブルロックとInnoDB行ロックの問題のみについて議論する)
MyISAMテーブルロック
MySQLのテーブル・レベル・ロックには、テーブル共有リード・ロックとテーブル独占ライト・ロックの2つのモードがあります.(Table Write Lock).MyISAMテーブルの読み取り操作は、同じテーブルに対する他のユーザの読み取り要求をブロックすることはないが、同じテーブルに対する書き込み要求をブロックする;MyISAMテーブルに対する書き込み操作は、他のユーザの同じテーブルに対する読み取りと書き込み操作をブロックする;MyISAMテーブルの読み取り操作と書き込み操作の間、および書き込み操作の間はシリアルである!
テーブルロックの追加方法
MyISAMは、クエリ文(SELECT)を実行する前に、関連するすべてのテーブルに自動的にリード・ロックをかけ、更新操作(UPDATE、DELETE、INSERTなど)を実行する前に、関連するテーブルに自動的にライト・ロックをかけるプロセスは、ユーザの介入を必要としないため、ユーザが直接LOCK TABLEコマンドでMyISAMテーブルに明示的にロックをかける必要はないのが一般的である.
ロックの表示
1.LOCK TABLESで表に明示的に表ロックを追加する場合は、表に関連するすべてのロックを同時に取得する必要があります.MySQLではロックアップグレードはサポートされていません.すなわち、LOCK TABLESを実行すると、明示的にロックされたテーブルにのみアクセスでき、ロックされていないテーブルにはアクセスできない.2.同時に、読み込みロックが追加されている場合は、更新操作ではなくクエリー操作のみが実行されます.3.実は、オートロックの場合も基本的には、MyISAMは常にSQL文に必要なすべてのロックを一度に取得します.これもMyISAMテーブルにデッドロック(Deadlock Free)が現れない理由です.
InnoDBロックの問題
InnoDBとMyISAMの最大の違いは2つあります.1つはトランザクション(TRANSACTION)をサポートすること、2つは行レベルロックを採用することです.
InnoDBの行ロックモード及びロック方法
InnoDBは、以下の2種類の行ロックを実現しています.共有ロック(S):1つのトランザクションが1行を読むことを許可し、他のトランザクションが同じデータセットの排他ロックを取得することを阻止します. 排他ロック(X):排他ロックのトランザクション更新データを取得することを許可し、他のトランザクションが同じデータセットの共有リードロックと排他ライトロックを取得することを阻止する.また、ローロックとテーブルロックの共存を可能にするために、多粒度ロック機構を実現するために、InnoDBには、両方ともテーブルロックである2種類の内部で使用される意向ロック(Intention Locks)がある. 意向共有ロック(IS):トランザクションはデータ行に行共有ロックを追加するつもりで、トランザクションは1つのデータ行に共有ロックを追加する前にテーブルのISロックを取得しなければならない. 意向排他ロック(IX):トランザクションはデータ行に排他ロックを追加するつもりで、トランザクションはデータ行に排他ロックを追加する前にテーブルのIXロックを取得しなければならない. 意向ロックはInnoDBが自動的に追加し、ユーザーの介入を必要としない.UPDATE、DELETE、INSERT文の場合、InnoDBはデータセットに関連する排他ロック(X)を自動的に追加します.通常のSELECT文では、InnoDBにロックはありません.
トランザクションは、次の文でレコードセットに共有ロックまたは排他ロックを追加できます.共有ロック(S):SELECT*FROM table_name WHERE…LOCK IN SHARE MODE. 排他錠(X):SELECT*FROM table_name WHERE … FOR UPDATE.
InnoDB行ロック実装方式
InnoDBロー・ロックは、インデックス上のインデックス・アイテムにロックをかけることによって実現されます.この点MySQLはOracleとは異なり、データ・ブロック内で対応するデータ・ローにロックをかけることによって実現されます.InnoDBというロー・ロックの実装の特徴は、インデックス条件でデータを取得するだけで、InnoDBはロー・レベル・ロックを使用します.そうしないと、InnoDBはテーブル・ロックを使用します.
(1)インデックス条件クエリを通過しない場合,InnoDBはローロックではなくテーブルロックを確実に使用する.
(2)MySQLの行ロックはインデックスに対するロックであり、レコードに対するロックではないため、同行しないレコードへのアクセスであるが、同じインデックスキーを使用すると、ロック競合が発生する.この点に注意して設計を適用する.
(3)テーブルに複数のインデックスがある場合、異なるトランザクションは異なるインデックスを使用して異なるローをロックすることができ、また、プライマリ・キー・インデックス、ユニーク・インデックス、または通常のインデックスを使用しても、InnoDBはロー・ロックを使用してデータをロックします.
(4)条件にインデックスフィールドが使用されていても、インデックスを使用してデータを取得するかどうかは、MySQLが実行計画の異なる代価を判断することによって決定されます.MySQLが、ロー・ロックではなく、小さなテーブルに対しては、ロー・ロックを使用しないなど、フル・テーブル・スキャンの効率が高いと判断した場合、InnoDBはロー・ロックを使用します.したがって、ロック競合を分析する際には、忘れないでくださいSQLの実行計画を確認し、インデックスが実際に使用されているかどうかを確認しました.
ギャップロック(Next-Keyロック)等しい条件ではなく範囲条件でデータを検索し、共有または排他ロックを要求すると、InnoDBは条件を満たす既存のデータ記録のインデックス項目にロックをかけます.キー値が条件範囲内にあるが存在しない記録については、「ギャップ(GAP)」と呼ばれ、InnoDBもこの「ギャップ」にロックをかけます.このロック機構はいわゆるギャップロック(Next−Keyロック)である.
例えば、empテーブルに101個のレコードしかない場合、そのempidの値はそれぞれ1,2,...,100101であり、以下のSQL:Select*from emp where empid>100 for updateである.範囲条件の検索であり、InnoDBは、条件を満たすempid値が101のレコードにロックをかけるだけでなく、empidが101より大きい(これらのレコードは存在しない)「ギャップ」にもロックをかける.
InnoDBがギャップロックを使用する目的は,一方では,関連する独立性レベルの要件を満たすために幻読みを防止するためであり,他方ではその復元と複製の要件を満たすためである.
デッドロックについては前述したように、MyISAMテーブルロックはdeadlock freeである.これは、MyISAMが常に必要なすべてのロックを一度に取得するか、すべて満たすか、待つかのいずれかであるため、デッドロックは発生しないからである.しかし、InnoDBでは、単一のSQLからなるトランザクションを除いて、ロックは徐々に取得され、InnoDBでデッドロックが発生する可能性があることが決定されます.以下に示すように、生死の鍵をかける例です.
原文住所:https://www.cnblogs.com/jpfss/p/8890250.html
他のデータベースに比べて、MySQLのロックメカニズムは簡単で、最も顕著な特徴は異なるストレージエンジンが異なるロックメカニズムをサポートすることです.異なるストレージエンジンによって、MySQLのロックの特性は大体以下のようにまとめることができる.
行ロック
テーブルロック
ページロック
MyISAM
√
BDB
√
√
InnoDB
√
√
オーバーヘッド、ロック速度、デッドロック、粒度、同時パフォーマンス
テーブル・ロックは、クエリーを主とし、インデックス条件でデータを更新するアプリケーションが少ない場合に適しています.ロー・ロックは、インデックス条件に基づいて少量の異なるデータを同時に大量に更新し、同時に同時クエリーを実行するアプリケーションに適しています.PS:BDBは既にInnoDBに取って代わられているため,MyISAMテーブルロックとInnoDB行ロックの問題のみについて議論する)
MyISAMテーブルロック
MySQLのテーブル・レベル・ロックには、テーブル共有リード・ロックとテーブル独占ライト・ロックの2つのモードがあります.(Table Write Lock).MyISAMテーブルの読み取り操作は、同じテーブルに対する他のユーザの読み取り要求をブロックすることはないが、同じテーブルに対する書き込み要求をブロックする;MyISAMテーブルに対する書き込み操作は、他のユーザの同じテーブルに対する読み取りと書き込み操作をブロックする;MyISAMテーブルの読み取り操作と書き込み操作の間、および書き込み操作の間はシリアルである!
テーブルロックの追加方法
MyISAMは、クエリ文(SELECT)を実行する前に、関連するすべてのテーブルに自動的にリード・ロックをかけ、更新操作(UPDATE、DELETE、INSERTなど)を実行する前に、関連するテーブルに自動的にライト・ロックをかけるプロセスは、ユーザの介入を必要としないため、ユーザが直接LOCK TABLEコマンドでMyISAMテーブルに明示的にロックをかける必要はないのが一般的である.
ロックの表示
Lock tables orders read local, order_detail read local;
Select sum(total) from orders;
Select sum(subtotal) from order_detail;
Unlock tables;
1.LOCK TABLESで表に明示的に表ロックを追加する場合は、表に関連するすべてのロックを同時に取得する必要があります.MySQLではロックアップグレードはサポートされていません.すなわち、LOCK TABLESを実行すると、明示的にロックされたテーブルにのみアクセスでき、ロックされていないテーブルにはアクセスできない.2.同時に、読み込みロックが追加されている場合は、更新操作ではなくクエリー操作のみが実行されます.3.実は、オートロックの場合も基本的には、MyISAMは常にSQL文に必要なすべてのロックを一度に取得します.これもMyISAMテーブルにデッドロック(Deadlock Free)が現れない理由です.
InnoDBロックの問題
InnoDBとMyISAMの最大の違いは2つあります.1つはトランザクション(TRANSACTION)をサポートすること、2つは行レベルロックを採用することです.
InnoDBの行ロックモード及びロック方法
InnoDBは、以下の2種類の行ロックを実現しています.
トランザクションは、次の文でレコードセットに共有ロックまたは排他ロックを追加できます.
InnoDB行ロック実装方式
InnoDBロー・ロックは、インデックス上のインデックス・アイテムにロックをかけることによって実現されます.この点MySQLはOracleとは異なり、データ・ブロック内で対応するデータ・ローにロックをかけることによって実現されます.InnoDBというロー・ロックの実装の特徴は、インデックス条件でデータを取得するだけで、InnoDBはロー・レベル・ロックを使用します.そうしないと、InnoDBはテーブル・ロックを使用します.
(1)インデックス条件クエリを通過しない場合,InnoDBはローロックではなくテーブルロックを確実に使用する.
(2)MySQLの行ロックはインデックスに対するロックであり、レコードに対するロックではないため、同行しないレコードへのアクセスであるが、同じインデックスキーを使用すると、ロック競合が発生する.この点に注意して設計を適用する.
(3)テーブルに複数のインデックスがある場合、異なるトランザクションは異なるインデックスを使用して異なるローをロックすることができ、また、プライマリ・キー・インデックス、ユニーク・インデックス、または通常のインデックスを使用しても、InnoDBはロー・ロックを使用してデータをロックします.
(4)条件にインデックスフィールドが使用されていても、インデックスを使用してデータを取得するかどうかは、MySQLが実行計画の異なる代価を判断することによって決定されます.MySQLが、ロー・ロックではなく、小さなテーブルに対しては、ロー・ロックを使用しないなど、フル・テーブル・スキャンの効率が高いと判断した場合、InnoDBはロー・ロックを使用します.したがって、ロック競合を分析する際には、忘れないでくださいSQLの実行計画を確認し、インデックスが実際に使用されているかどうかを確認しました.
ギャップロック(Next-Keyロック)等しい条件ではなく範囲条件でデータを検索し、共有または排他ロックを要求すると、InnoDBは条件を満たす既存のデータ記録のインデックス項目にロックをかけます.キー値が条件範囲内にあるが存在しない記録については、「ギャップ(GAP)」と呼ばれ、InnoDBもこの「ギャップ」にロックをかけます.このロック機構はいわゆるギャップロック(Next−Keyロック)である.
例えば、empテーブルに101個のレコードしかない場合、そのempidの値はそれぞれ1,2,...,100101であり、以下のSQL:Select*from emp where empid>100 for updateである.範囲条件の検索であり、InnoDBは、条件を満たすempid値が101のレコードにロックをかけるだけでなく、empidが101より大きい(これらのレコードは存在しない)「ギャップ」にもロックをかける.
InnoDBがギャップロックを使用する目的は,一方では,関連する独立性レベルの要件を満たすために幻読みを防止するためであり,他方ではその復元と複製の要件を満たすためである.
デッドロックについては前述したように、MyISAMテーブルロックはdeadlock freeである.これは、MyISAMが常に必要なすべてのロックを一度に取得するか、すべて満たすか、待つかのいずれかであるため、デッドロックは発生しないからである.しかし、InnoDBでは、単一のSQLからなるトランザクションを除いて、ロックは徐々に取得され、InnoDBでデッドロックが発生する可能性があることが決定されます.以下に示すように、生死の鍵をかける例です.
原文住所:https://www.cnblogs.com/jpfss/p/8890250.html