MYSQLのロックは
4897 ワード
コンピュータ科学では、ロックは、マルチスレッドを実行する際にリソースアクセスを強制的に制限するための同期メカニズムであり、すなわち、同時制御において反発要求の満足を保証するために使用される.
データベースのロックメカニズムでは、DBMSでは、ロックの粒度に応じてデータベースロックを行レベルロック(INNODBエンジン)、表レベルロック(MYISAMエンジン)、ページレベルロック(BBBエンジン)に分けることができます.
行レベルロック
行レベルロックは、Mysqlのロック粒度が最も細いロックであり、現在の操作の行のみに対してロックが行われていることを示します.ロー・レベル・ロックは、データベース・オペレーションの競合を大幅に低減します.そのロック粒度は最小であるが、ロックのオーバーヘッドも最大である.行レベルロックは
特長
オーバーヘッドが大きく、ロックが遅い.デッドロックが発生します.ロック粒度は最小であり,ロック衝突が発生する確率は最低であり,同時度も最高である.
テーブルレベルロック
表レベルのロックはMySQLの中でロックの粒度が最も大きいロックで、現在の操作の表全体にロックを加えることを示して、それは簡単で、資源の消耗が少なくて、大部分のMySQLエンジンに支持されます.最もよく使われるMYISAMとINNODBでは、テーブルレベルのロックがサポートされています.表レベルのロックは、
特長
オーバーヘッドが小さく、ロックが速い.デッドロックは発生しません.ロック粒度が大きく,ロック衝突を発する確率が最も高く,同時度が最も低い.
ページレベルロック
ページ・レベル・ロックは、MySQLのロック・サイズが行レベル・ロックと表レベル・ロックの間にあるロックです.表レベルのロック速度は速いが、衝突が多く、行レベルの衝突は少ないが、速度は遅い.だからトレードオフのページレベルを取って、隣接するレコードのセットを一度にロックします.BDはページレベルロックをサポートする
特長
オーバーヘッドとロック時間は、テーブルロックとローロックの間に境界があります.デッドロックが発生します.ロック粒度は表ロックと行ロックの間にあり、同時性は一般的です.
MySQL常用ストレージエンジンのロックメカニズム
MyISAMとMEMORYはテーブルレベルロック(table-level locking)を採用
BDBはページロック(page-level locking)または表レベルロックを採用し、デフォルトはページロックである
InnoDBは行レベルロック(row-level locking)と表レベルロックをサポートし、デフォルトは行レベルロック
Innodbのロー・ロックとテーブル・ロック
前述したように、Innodbエンジンではローロックもテーブルロックもサポートされていますが、テーブル全体がロックされるのはいつですか?
InnoDBロー・ロックは、インデックス上のインデックス・アイテムにロックをかけることによって実現されます.この点MySQLはOracleとは異なり、データ・ブロック内で対応するデータ・ローにロックをかけることによって実現されます.InnoDBというロー・ロックの実装の特徴は、インデックス条件でデータを取得するだけで、InnoDBはロー・レベル・ロックを使用します.そうしないと、InnoDBはテーブル・ロックを使用します.
実際の応用では、InnoDB行ロックのこの特性に特に注意しなければならない.そうしないと、大量のロック競合が発生し、同時性能に影響を及ぼす可能性がある.インデックス条件クエリを通過しない場合、InnoDBはロー・ロックではなくテーブル・ロックを使用します. MySQLのロー・ロックはインデックスに対するロックであり、レコードに対するロックではないため、同行しないレコードへのアクセスですが、同じインデックス・キーを使用するとロック競合が発生します.デザインを適用するときはこの点に注意してください. テーブルに複数のインデックスがある場合、異なるトランザクションは異なるインデックスを使用して異なるローをロックできます.また、プライマリ・キー・インデックス、ユニーク・インデックス、または通常のインデックスを使用しても、InnoDBはロー・ロックを使用してデータをロックします. 条件でインデックスフィールドが使用されていても、インデックスを使用してデータを取得するかどうかは、MySQLが異なる実行計画の代価を判断することによって決定されます.MySQLが、ロー・ロックではなくテーブル全体のスキャン効率が高いと考えている場合、小さなテーブルの場合、インデックスは使用されません.したがって、ロック競合を分析するときは、SQLの実行計画を確認して、インデックスが実際に使用されているかどうかを確認することを忘れないでください.
行レベルロックとデッドロック
MyISAMではデッドロックは発生しません.MyISAMは常に必要なすべてのロックを一度に取得するか、すべて満たすか、すべて待つかのどちらかです.InnoDBでは,ロックが徐々に得られ,デッドロックの可能性が生じる.
MySQLでは、行レベルのロックはレコードを直接ロックするのではなく、インデックスをロックします.インデックスはプライマリ・キー・インデックスと非プライマリ・キー・インデックスの2つに分けられ、1つのsql文がプライマリ・キー・インデックスを操作すると、MySQLはこのプライマリ・キー・インデックスをロックします.文がプライマリ・キー以外のインデックスを操作している場合、MySQLはプライマリ・キー以外のインデックスをロックしてから、関連するプライマリ・キー・インデックスをロックします.UPDATE、DELETE操作時、MySQLはWHERE条件でスキャンしたすべてのインデックスレコードをロックするだけでなく、隣接するキー値、いわゆるnext-key lockingをロックします.
2つのトランザクションが同時に実行されると、1つはプライマリ・キー・インデックスをロックし、他の関連インデックスを待っています.もう1つは、プライマリ・キー以外のインデックスをロックし、プライマリ・キー・インデックスを待機しています.これでデッドロックが発生します.
デッドロックが発生すると、InnoDBは通常検出され、1つのトランザクションがロックを解放してロールバックし、もう1つのトランザクションがロックを取得してトランザクションを完了します.
デッドロックを避ける方法はいくつかありますが、ここでは一般的な3つだけを紹介します.
1、異なるプログラムが複数のテーブルに同時にアクセスする場合、できるだけ同じ順序でテーブルにアクセスすることを約束し、デッドロックの機会を大幅に低減することができる.
2、同じ事務の中で、できるだけ一度に必要なすべての資源をロックし、デッドロックの発生確率を減らす.
3、非常にデッドロックが発生しやすい業務部分については、アップグレードロック粒度を使用して、表レベルのロックによってデッドロック発生確率を減らすことを試みることができる
ロー・レベル・ロックは、Mysqlのロック粒度が最も細いロックであり、ロー・レベル・ロックはデータベース・オペレーションの競合を大幅に低減します.行レベルロックは共有ロックと排他ロックの2種類に分けられ、共有ロックと排他ロックの概念、使用方法、注意事項などを詳しく紹介します.
Share Lock
共有ロックは、読み取りロックとも呼ばれ、読み取り操作によって作成されるロックです.他のユーザーは、データを同時に読み込むことができますが、すべての共有ロックが解放されるまで、データの排他ロックを取得するために、トランザクションを変更することはできません.
トランザクションTがデータAに共有ロックをかけた場合、他のトランザクションはAに共有ロックを追加するしかなく、排他ロックを追加することはできません.共有ロックが許可されたトランザクションは、データのみを読み取り、データを変更できません.
使用法
クエリ文の後に
排他ロック(eXclusive Lock)
排他ロックはライトロックとも呼ばれ、トランザクションTがデータAに排他ロックを加えた場合、他のトランザクションはAに任意のタイプのロックを追加できません.排他ロックが許可されたトランザクションは、データを読むことも、データを変更することもできます.
使用法
クエリ文の後に
意向ロック
意向ロックは、主に次のローが要求されるロックのタイプを1つのトランザクションで明らかにするために設計された表レベルのロックです.InnoDBの2つのテーブル・ロック:
意向共有ロック(IS):トランザクションがデータ行に共有ロックを追加する準備ができていることを示します.つまり、1つのデータ行に共有ロックを追加する前に、テーブルのISロックを取得する必要があります.
意向排他ロック(IX):上記のように、トランザクションがデータ行に排他ロックを追加する準備ができていることを示し、トランザクションがデータ行に排他ロックを追加する前に、テーブルのIXロックを取得しなければならないことを示します.
意向ロックはInnoDBが自動的に追加したもので,ユーザの介入は不要である.
Insert、update、deleteの場合、InnoDBは自動的に関連するデータに排他ロック(X)を追加します.一般的なSelect文の場合、InnoDBにはロックはありません.トランザクションは、次の文で共有ロックまたは排他ロックを表示できます.
共有ロック:
データベースのロックメカニズムでは、DBMSでは、ロックの粒度に応じてデータベースロックを行レベルロック(INNODBエンジン)、表レベルロック(MYISAMエンジン)、ページレベルロック(BBBエンジン)に分けることができます.
行レベルロック
行レベルロックは、Mysqlのロック粒度が最も細いロックであり、現在の操作の行のみに対してロックが行われていることを示します.ロー・レベル・ロックは、データベース・オペレーションの競合を大幅に低減します.そのロック粒度は最小であるが、ロックのオーバーヘッドも最大である.行レベルロックは
と に分けられる.特長
オーバーヘッドが大きく、ロックが遅い.デッドロックが発生します.ロック粒度は最小であり,ロック衝突が発生する確率は最低であり,同時度も最高である.
テーブルレベルロック
表レベルのロックはMySQLの中でロックの粒度が最も大きいロックで、現在の操作の表全体にロックを加えることを示して、それは簡単で、資源の消耗が少なくて、大部分のMySQLエンジンに支持されます.最もよく使われるMYISAMとINNODBでは、テーブルレベルのロックがサポートされています.表レベルのロックは、
(共有ロック)と (排他ロック)に分けられます.特長
オーバーヘッドが小さく、ロックが速い.デッドロックは発生しません.ロック粒度が大きく,ロック衝突を発する確率が最も高く,同時度が最も低い.
ページレベルロック
ページ・レベル・ロックは、MySQLのロック・サイズが行レベル・ロックと表レベル・ロックの間にあるロックです.表レベルのロック速度は速いが、衝突が多く、行レベルの衝突は少ないが、速度は遅い.だからトレードオフのページレベルを取って、隣接するレコードのセットを一度にロックします.BDはページレベルロックをサポートする
特長
オーバーヘッドとロック時間は、テーブルロックとローロックの間に境界があります.デッドロックが発生します.ロック粒度は表ロックと行ロックの間にあり、同時性は一般的です.
MySQL常用ストレージエンジンのロックメカニズム
MyISAMとMEMORYはテーブルレベルロック(table-level locking)を採用
BDBはページロック(page-level locking)または表レベルロックを採用し、デフォルトはページロックである
InnoDBは行レベルロック(row-level locking)と表レベルロックをサポートし、デフォルトは行レベルロック
Innodbのロー・ロックとテーブル・ロック
前述したように、Innodbエンジンではローロックもテーブルロックもサポートされていますが、テーブル全体がロックされるのはいつですか?
InnoDBロー・ロックは、インデックス上のインデックス・アイテムにロックをかけることによって実現されます.この点MySQLはOracleとは異なり、データ・ブロック内で対応するデータ・ローにロックをかけることによって実現されます.InnoDBというロー・ロックの実装の特徴は、インデックス条件でデータを取得するだけで、InnoDBはロー・レベル・ロックを使用します.そうしないと、InnoDBはテーブル・ロックを使用します.
実際の応用では、InnoDB行ロックのこの特性に特に注意しなければならない.そうしないと、大量のロック競合が発生し、同時性能に影響を及ぼす可能性がある.
行レベルロックとデッドロック
MyISAMではデッドロックは発生しません.MyISAMは常に必要なすべてのロックを一度に取得するか、すべて満たすか、すべて待つかのどちらかです.InnoDBでは,ロックが徐々に得られ,デッドロックの可能性が生じる.
MySQLでは、行レベルのロックはレコードを直接ロックするのではなく、インデックスをロックします.インデックスはプライマリ・キー・インデックスと非プライマリ・キー・インデックスの2つに分けられ、1つのsql文がプライマリ・キー・インデックスを操作すると、MySQLはこのプライマリ・キー・インデックスをロックします.文がプライマリ・キー以外のインデックスを操作している場合、MySQLはプライマリ・キー以外のインデックスをロックしてから、関連するプライマリ・キー・インデックスをロックします.UPDATE、DELETE操作時、MySQLはWHERE条件でスキャンしたすべてのインデックスレコードをロックするだけでなく、隣接するキー値、いわゆるnext-key lockingをロックします.
2つのトランザクションが同時に実行されると、1つはプライマリ・キー・インデックスをロックし、他の関連インデックスを待っています.もう1つは、プライマリ・キー以外のインデックスをロックし、プライマリ・キー・インデックスを待機しています.これでデッドロックが発生します.
デッドロックが発生すると、InnoDBは通常検出され、1つのトランザクションがロックを解放してロールバックし、もう1つのトランザクションがロックを取得してトランザクションを完了します.
デッドロックを避ける方法はいくつかありますが、ここでは一般的な3つだけを紹介します.
1、異なるプログラムが複数のテーブルに同時にアクセスする場合、できるだけ同じ順序でテーブルにアクセスすることを約束し、デッドロックの機会を大幅に低減することができる.
2、同じ事務の中で、できるだけ一度に必要なすべての資源をロックし、デッドロックの発生確率を減らす.
3、非常にデッドロックが発生しやすい業務部分については、アップグレードロック粒度を使用して、表レベルのロックによってデッドロック発生確率を減らすことを試みることができる
ロー・レベル・ロックは、Mysqlのロック粒度が最も細いロックであり、ロー・レベル・ロックはデータベース・オペレーションの競合を大幅に低減します.行レベルロックは共有ロックと排他ロックの2種類に分けられ、共有ロックと排他ロックの概念、使用方法、注意事項などを詳しく紹介します.
Share Lock
共有ロックは、読み取りロックとも呼ばれ、読み取り操作によって作成されるロックです.他のユーザーは、データを同時に読み込むことができますが、すべての共有ロックが解放されるまで、データの排他ロックを取得するために、トランザクションを変更することはできません.
トランザクションTがデータAに共有ロックをかけた場合、他のトランザクションはAに共有ロックを追加するしかなく、排他ロックを追加することはできません.共有ロックが許可されたトランザクションは、データのみを読み取り、データを変更できません.
使用法
SELECT ... LOCK IN SHARE MODE; クエリ文の後に
LOCK IN SHARE MODEを追加すると、Mysqlはクエリ結果の各ローに共有ロックを追加し、他のスレッドがクエリ結果セットのどのローに対して排他ロックを使用していない場合、共有ロックを正常に申請できます.そうしないとブロックされます.他のスレッドは、共有ロックを使用したテーブルを読み込むこともでき、これらのスレッドは同じバージョンのデータを読み出すこともできます.排他ロック(eXclusive Lock)
排他ロックはライトロックとも呼ばれ、トランザクションTがデータAに排他ロックを加えた場合、他のトランザクションはAに任意のタイプのロックを追加できません.排他ロックが許可されたトランザクションは、データを読むことも、データを変更することもできます.
使用法
SELECT ... FOR UPDATE; クエリ文の後に
FOR UPDATEを追加すると、Mysqlはクエリ結果の各ローに排他ロックを追加します.他のスレッドがクエリ結果セットのどのローに対して排他ロックを使用していない場合、排他ロックを正常に申請できます.そうしないとブロックされます.意向ロック
意向ロックは、主に次のローが要求されるロックのタイプを1つのトランザクションで明らかにするために設計された表レベルのロックです.InnoDBの2つのテーブル・ロック:
意向共有ロック(IS):トランザクションがデータ行に共有ロックを追加する準備ができていることを示します.つまり、1つのデータ行に共有ロックを追加する前に、テーブルのISロックを取得する必要があります.
意向排他ロック(IX):上記のように、トランザクションがデータ行に排他ロックを追加する準備ができていることを示し、トランザクションがデータ行に排他ロックを追加する前に、テーブルのIXロックを取得しなければならないことを示します.
意向ロックはInnoDBが自動的に追加したもので,ユーザの介入は不要である.
Insert、update、deleteの場合、InnoDBは自動的に関連するデータに排他ロック(X)を追加します.一般的なSelect文の場合、InnoDBにはロックはありません.トランザクションは、次の文で共有ロックまたは排他ロックを表示できます.
共有ロック:
SELECT ... LOCK IN SHARE MODE;排他錠:SELECT ... FOR UPDATE;