mysqlロックメカニズムの研究
6400 ワード
コンカレントの場合、データの一貫性を保証するために、データベースをロックする必要があります.ロックメカニズムの選択はデータベースのコンカレント能力と性能に直接影響します.ロックメカニズムを選択するときは慎重にしてください.
mysqlは主に3種類のロックメカニズムを使用し、粒子度が小さいものから大きいものに順に、行レベルロック、ページレベルロック、表レベルロックとなり、粒子度の増加に伴い、同時能力が低下し、消費も低下し、デッドロックが発生する確率も低下した.
ここでは、主にMyISAMとInnodbの2つのストレージエンジンを使用して、表レベルのロックと行レベルのロックについて説明します.
一.表レベルのロック
表レベルロックは両方のストレージエンジンで使用されるロックメカニズムであり、MyISAMでは表レベルロックのみが使用され、Innodbプリセットでは行レベルロックが使用されますが、検索条件にインデックスが指定されていない場合、Innodbでは表レベルロックが使用されます.
表レベルのロックには、主にリード・ロックとライト・ロックの2つのタイプがあり、主に4つのキューで2つのロックを維持し、2つのキューでロック中のリード・ライト・ロックの情報を維持し、他の2つのキューでは待機中のリード・ライト・ロックの情報を維持し、4つのキューは以下の通りです. Current read-lock queue、すなわち現在リードロックを持つすべてのスレッドのキューであり、キュー内の順序はロックを取得した時間順に 格納. Pending read-lock queue、すなわち、現在リードロックリソースのすべてのスレッドを待っているキューであり、キュー内の順序も時間順に 格納. Current write-lock queue、Current read-lock queueと類似 Pending write-lock queue、Pending read-lock queueと類似 mysqlには読み書きロックのほかに9種類のロックがありますが、本稿では読み書きロックについて説明します.
リードロック
1つのスレッドは、読み込みロックリソースの取得を要求するときに、2つの条件を満たす必要があります.
1.ロックを要求したリソースは現在書き込みロックされていない.
2.書き込みロック待ち行列(Pending write-lock queue)には、より優先度の高い書き込みロック待ち行列はありません.
上記の2つの条件が満たされると、要求された関連情報はCurrent read-lock queueに格納され、そうでなければPending read-lock queueに格納されて待機します.
書き込みロック
1つのスレッドは、書き込みロックリソースの取得を要求するときに、3つの条件を満たす必要があります.
1.Current write-lock queueには同じリソースに対するロック情報が存在しない.
2.Pending write-lock queueにも同じリソースに対するロック待ち情報は存在しない.
3.Current read-lock queueには、同じリソースに対するロック情報が存在します.
書き込みロックリソースは、上記の3つの条件を満たす場合にのみ取得できます.そうしないと、Pending write-lock queueキューに入ります.
上記の状況は、特に優先度が設定されていない場合、1つのリソースがリードロックされている場合、次のリードロックをブロックすることはできないが、ライトロックをブロックすることができる.リソースが書き込みロックされている場合、読み書きロックはブロックされます.書き込みロックに加えて、Pending write-lock queueの他の書き込みロックは、読み取りロックよりも優先度が低い.
二.行レベルロック
行レベルロックはmysql自身が実現するロック方式ではなく、よく使用されるInnodbなどの他のストレージエンジンによって実現される.
主にInnodbのロック特性を解析した.
Innodbには主に共有ロック(リードロック)、排他ロック(ライトロック)と意向共有ロック、意向排他ロックがあり、最初の2つは行レベルロック、後の2つは表レベルロックに使用されます.意向ロックは本当の意味でのロックではなく、ある行の記録を操作することを示すだけなので、意向ロックの間に衝突は発生せず、行にロックをかけるときだけ衝突があります.
これらの関係は次のとおりです.
共有ロック
排他錠
意向共有ロック
意向排他ロック
共有ロック
互換性
コンフリクト
互換性
コンフリクト
排他錠
コンフリクト
コンフリクト
コンフリクト
コンフリクト
意向共有ロック
互換性
コンフリクト
互換性
互換性
意向排他ロック
コンフリクト
コンフリクト
互換性
互換性
Innodbは、データ記録の最初のインデックスキーの前と最後のインデックスキーの後の空域空間にロック情報をマークすることによってロックを実現し、このロック実現方式をギャップロック(next-key locking)と呼ぶ.query実行プロセスが範囲で検索されると、範囲内のすべてのインデックスキー値がロックされます.このキー値が存在しなくても、ロック時にキー値範囲をロックしたデータを挿入できません.
Innodbのロックメカニズムには、次のような問題もあります. queryがインデックスを利用できない場合、Innodbは行レベルロックの使用を放棄し、表レベルロックに変更し、同時低下をもたらす. queryが使用するインデックスがすべてのフィルタ条件を含まない場合、データ取得に使用されるインデックスキーに関連付けられたデータの一部は、そのqueryの結果セットの行列に属していない可能性があるが、ギャップロックの死の範囲であり、特定のインデックスキーではないため、ロックされる. Queryがインデックスを使用してデータを位置決めする場合、使用するインデックスキーが同じでアクセスするデータ行が異なる場合(インデックスはフィルタ条件の一部にすぎない)、同様に がロックされる.
デッドロック
デッドロックが発生すると、Innodbは2つのトランザクションのうち小さいトランザクションロールバックを選択します.トランザクションのサイズは、トランザクションに挿入、更新、または削除されたデータ量に基づいて判断されます.つまり、デッドロックが発生すると、レコード数に影響を与えるトランザクションが完了します.
Innodbロックメカニズムの例
2つのセッションを開いてset autocommit=offを設定します.
1.基本行ロック
時刻
session A
session B
1
update test set b = 'b1' where a=1; (更新されたがコミットされない)
2
update test set b ='b2' where a=1;(ブロックされ、待機)
3
commit;
4
ブロック解除、更新がコミットされます
2.インデックスなしでロー・レベル・ロックがテーブル・レベル・ロックになる
1
update test set b='b3' where b='b2';
2
update test set b='b4' where b='b3';(ブロックされ、待機)
3
commit;
4
ブロック解除、更新完了
3.クリアランスロック
1
update test set b='b4 where a<4 and a>1;'
2
insert into test values(2,'b2');(ブロックされる)
3
commit;
4
ブロック解除、挿入成功
4.共通インデックスを使用した異なるデータブロック
1
update test set b='b4 where a=1 and b='b2';
2
update test set b='b4 where a=1 and b='b3';(ブロックされる)
3
commit;
4
ブロック解除、更新成功
5.デッドロック
1
update test set b='b4 where a=1;
2
update test set b='b5' where a=2;
3
update test set b='b6' where a=2;(ブロックされる)
4
update test set b='b7 where a=1;(ブロックされる)
for updateロック
プロジェクトでは常にfor updateを使用してロックされます.これは排他ロックであり、トランザクションで使用されます.次に、for updateの使用方法を簡単な商品購入例で説明します.
三.ロックメカニズムの最適化
1.MyIsamテーブルレベルロック
MyIsamのロックメカニズムはテーブルレベルのロックのみであり、そのレベルを変更することはできないため、最適化の手段はロック時間が短くなり、同時実行可能な操作をできるだけ同時実行することである.
ロック時間の短縮、すなわちquery実行時間の短縮は、大きく複雑なqueryをいくつかの小さなqueryに分けて実行する. はqueryの実行をより速くする. ロックされたテーブルはできるだけ小さくします. MyIsamテーブルデータファイルの最適化 リード・ライト・ロックの優先度は、デフォルトではライト・ロックの優先度がリード・ロックより高いが、パラメータlow_を介して相互間の優先度を設定することができる.prioroty_updates=1.たとえば、クエリー操作が多数ある場合は、優先度を設定してクエリーを先に行うことができます.
2.Innodb行レベルロック
Innodbは行レベルのロックを使用しており、MyIsamよりも同時性が優れていますが、不適切な操作を使用すると、MyIsamに及ばないほどパフォーマンスが低下します.できるだけすべてのデータ・クエリーをインデックスで完了させることで、ロー・レベルのロックがテーブル・レベルのロックにアップグレードされることを回避できます. 範囲ベースのデータ検索フィルタ条件をできるだけ低減し、ギャップロックによる範囲ロックを回避する. トランザクションのサイズをできるだけ制御し、ロックされたリソース量と時間を減らす. ビジネス環境で許可されている場合、MySQLがトランザクション独立性レベルを実現するためにもたらす追加コストを削減するために、できるだけ低いレベルのトランザクション独立性を使用します.
Innodbの行レベルのロックとトランザクションのため、必ずデッドロックが発生します.以下は、デッドロックの発生確率を減らすためによく使われるアドバイスです.類似の業務モジュールでは、できるだけ同じアクセス順序でアクセスし、デッドロックの発生を防止する. 同じトランザクションでは、できるだけ一度に必要なすべてのリソースをロックし、デッドロックの発生確率を減らすことができます. デッドロックが発生しやすい業務部分については、アップグレードロック粒度を用いて、表レベルロックによってデッドロックの発生確率を低減することを試みることができる.
Table_locks_immediateとTable_locks_waitedの2つのステータス変数は、表レベルのロックが発生した回数を表し、後者は表レベルのロック競合が発生して待機した回数を表す表レベルのロックの状況を表示します.システムのテーブル・レベルのロック・パフォーマンスは、この2つの値で測定できます.
Innodbの場合はコマンド:show status like'innodb_row_lock%'は、関連する状態変数を見て、変数名が分かりやすく、一つ一つ説明しません.
mysqlは主に3種類のロックメカニズムを使用し、粒子度が小さいものから大きいものに順に、行レベルロック、ページレベルロック、表レベルロックとなり、粒子度の増加に伴い、同時能力が低下し、消費も低下し、デッドロックが発生する確率も低下した.
ここでは、主にMyISAMとInnodbの2つのストレージエンジンを使用して、表レベルのロックと行レベルのロックについて説明します.
一.表レベルのロック
表レベルロックは両方のストレージエンジンで使用されるロックメカニズムであり、MyISAMでは表レベルロックのみが使用され、Innodbプリセットでは行レベルロックが使用されますが、検索条件にインデックスが指定されていない場合、Innodbでは表レベルロックが使用されます.
表レベルのロックには、主にリード・ロックとライト・ロックの2つのタイプがあり、主に4つのキューで2つのロックを維持し、2つのキューでロック中のリード・ライト・ロックの情報を維持し、他の2つのキューでは待機中のリード・ライト・ロックの情報を維持し、4つのキューは以下の通りです.
リードロック
1つのスレッドは、読み込みロックリソースの取得を要求するときに、2つの条件を満たす必要があります.
1.ロックを要求したリソースは現在書き込みロックされていない.
2.書き込みロック待ち行列(Pending write-lock queue)には、より優先度の高い書き込みロック待ち行列はありません.
上記の2つの条件が満たされると、要求された関連情報はCurrent read-lock queueに格納され、そうでなければPending read-lock queueに格納されて待機します.
書き込みロック
1つのスレッドは、書き込みロックリソースの取得を要求するときに、3つの条件を満たす必要があります.
1.Current write-lock queueには同じリソースに対するロック情報が存在しない.
2.Pending write-lock queueにも同じリソースに対するロック待ち情報は存在しない.
3.Current read-lock queueには、同じリソースに対するロック情報が存在します.
書き込みロックリソースは、上記の3つの条件を満たす場合にのみ取得できます.そうしないと、Pending write-lock queueキューに入ります.
上記の状況は、特に優先度が設定されていない場合、1つのリソースがリードロックされている場合、次のリードロックをブロックすることはできないが、ライトロックをブロックすることができる.リソースが書き込みロックされている場合、読み書きロックはブロックされます.書き込みロックに加えて、Pending write-lock queueの他の書き込みロックは、読み取りロックよりも優先度が低い.
二.行レベルロック
行レベルロックはmysql自身が実現するロック方式ではなく、よく使用されるInnodbなどの他のストレージエンジンによって実現される.
主にInnodbのロック特性を解析した.
Innodbには主に共有ロック(リードロック)、排他ロック(ライトロック)と意向共有ロック、意向排他ロックがあり、最初の2つは行レベルロック、後の2つは表レベルロックに使用されます.意向ロックは本当の意味でのロックではなく、ある行の記録を操作することを示すだけなので、意向ロックの間に衝突は発生せず、行にロックをかけるときだけ衝突があります.
これらの関係は次のとおりです.
共有ロック
排他錠
意向共有ロック
意向排他ロック
共有ロック
互換性
コンフリクト
互換性
コンフリクト
排他錠
コンフリクト
コンフリクト
コンフリクト
コンフリクト
意向共有ロック
互換性
コンフリクト
互換性
互換性
意向排他ロック
コンフリクト
コンフリクト
互換性
互換性
Innodbは、データ記録の最初のインデックスキーの前と最後のインデックスキーの後の空域空間にロック情報をマークすることによってロックを実現し、このロック実現方式をギャップロック(next-key locking)と呼ぶ.query実行プロセスが範囲で検索されると、範囲内のすべてのインデックスキー値がロックされます.このキー値が存在しなくても、ロック時にキー値範囲をロックしたデータを挿入できません.
Innodbのロックメカニズムには、次のような問題もあります.
デッドロック
デッドロックが発生すると、Innodbは2つのトランザクションのうち小さいトランザクションロールバックを選択します.トランザクションのサイズは、トランザクションに挿入、更新、または削除されたデータ量に基づいて判断されます.つまり、デッドロックが発生すると、レコード数に影響を与えるトランザクションが完了します.
Innodbロックメカニズムの例
create table test(a int(11),b varchar(16))engine=innodb;create index idx_test_a on test(a);2つのセッションを開いてset autocommit=offを設定します.
1.基本行ロック
時刻
session A
session B
1
update test set b = 'b1' where a=1; (更新されたがコミットされない)
2
update test set b ='b2' where a=1;(ブロックされ、待機)
3
commit;
4
ブロック解除、更新がコミットされます
2.インデックスなしでロー・レベル・ロックがテーブル・レベル・ロックになる
1
update test set b='b3' where b='b2';
2
update test set b='b4' where b='b3';(ブロックされ、待機)
3
commit;
4
ブロック解除、更新完了
3.クリアランスロック
1
update test set b='b4 where a<4 and a>1;'
2
insert into test values(2,'b2');(ブロックされる)
3
commit;
4
ブロック解除、挿入成功
4.共通インデックスを使用した異なるデータブロック
1
update test set b='b4 where a=1 and b='b2';
2
update test set b='b4 where a=1 and b='b3';(ブロックされる)
3
commit;
4
ブロック解除、更新成功
5.デッドロック
1
update test set b='b4 where a=1;
2
update test set b='b5' where a=2;
3
update test set b='b6' where a=2;(ブロックされる)
4
update test set b='b7 where a=1;(ブロックされる)
for updateロック
プロジェクトでは常にfor updateを使用してロックされます.これは排他ロックであり、トランザクションで使用されます.次に、for updateの使用方法を簡単な商品購入例で説明します.
$conn = mysql_connect('localhost','root','root') or die (" !!!");
mysql_select_db('test',$conn);
mysql_query("BEGIN"); \\
$res = mysql_query("select cnt from product where id = $pid for update"); \\
... \\ ，
mysql_query("update product set cnt=cnt+$quantity where id = $pid"); \\
mysql_query('commit'); \\ 三.ロックメカニズムの最適化
1.MyIsamテーブルレベルロック
MyIsamのロックメカニズムはテーブルレベルのロックのみであり、そのレベルを変更することはできないため、最適化の手段はロック時間が短くなり、同時実行可能な操作をできるだけ同時実行することである.
ロック時間の短縮、すなわちquery実行時間の短縮
2.Innodb行レベルロック
Innodbは行レベルのロックを使用しており、MyIsamよりも同時性が優れていますが、不適切な操作を使用すると、MyIsamに及ばないほどパフォーマンスが低下します.
Innodbの行レベルのロックとトランザクションのため、必ずデッドロックが発生します.以下は、デッドロックの発生確率を減らすためによく使われるアドバイスです.
Table_locks_immediateとTable_locks_waitedの2つのステータス変数は、表レベルのロックが発生した回数を表し、後者は表レベルのロック競合が発生して待機した回数を表す表レベルのロックの状況を表示します.システムのテーブル・レベルのロック・パフォーマンスは、この2つの値で測定できます.
Innodbの場合はコマンド:show status like'innodb_row_lock%'は、関連する状態変数を見て、変数名が分かりやすく、一つ一つ説明しません.