Mysqlの行級ロックを理解する——《Mysqlロックを深く追究する》

一、定義
毎回ロックされるのは1行のデータのロックメカニズムであり、行レベルロック(row-level)である.行レベルのロックはMySQLが独自に実現したロック方式ではなく、他のストレージエンジンが独自に実現したものです.
二、長所と短所

の利点

ロック粒度が小さいため、競合率が低く、同時高である.

の欠点

は複雑で、オーバーヘッドが大きい.

ロックが遅く、デッドロックが発生しやすい

三、ストレージエンジンをサポートする
行レベルロックを使用するのは主にInnoDBストレージエンジンとMySQLの分散ストレージエンジンNDBClusterです.
四、行レベルロックタイプ
InnoDBの行レベルロックは同様に2つのタイプに分けられる:共有ロックと排他ロック、ロックメカニズムの実現過程で行レベルロックと表レベルロックを共存させるために、InnoDBも同様に意向ロック(表レベルロック)の概念を使用し、意向共有ロックと意向排他ロックの2つがある.
意向ロックの役割は、トランザクションがリソースロックを取得する必要がある場合に、自分が必要とするリソースが排他ロックによって占有されている場合に、トランザクションがローをロックするテーブルに適切な意向ロックを追加する必要がある場合です.共有ロックが必要な場合は、テーブルに意向共有ロックを追加します.自分が必要な行(または行)に排他ロックを追加する場合は、まずテーブルに意図的な排他ロックを追加します.
意向共有ロックは複数同時に併存することができるが,意向排他ロックは同時に1つしか存在しない.したがって,InnoDBのロックパターンは実際には共有ロック(S),排他ロック(X),意向共有ロック(IS),意向排他ロック(IX)の4つに分けられるといえる.
ロックモードの互換性:
五、行レベルロックの実現方式
InnoDBロー・ロックは、インデックス上のインデックス・アイテムにロックをかけることで実現されます.したがって、インデックス条件でデータを取得する場合にのみ、InnoDBはロー・レベル・ロックを使用します.そうしないと、InnoDBはテーブル・ロックを使用します.その他の注意事項:

インデックス条件クエリを通過しない場合、InnoDBはローロックではなくテーブルロックを使用します.

MySQLのロー・ロックはインデックスに対するロックであり、レコードに対するロックではないため、同行しないレコードにアクセスしても同じインデックス・キーを使用するとロック競合が発生します.

テーブルに複数のインデックスがある場合、異なるトランザクションは異なるインデックスを使用して異なるローをロックできます.また、プライマリ・キー・インデックス、ユニーク・インデックス、または通常のインデックスを使用しても、InnoDBはロー・ロックを使用してデータをロックします.

条件でインデックスフィールドが使用されていても、インデックスを使用してデータを取得するかどうかは、MySQLが異なる実行計画の代価を判断することによって決定されます.MySQLが、ロー・ロックではなくテーブル全体のスキャン効率が高いと考えている場合、例えば、小さなテーブルではインデックスは使用されません.この場合、InnoDBはテーブル・ロックを使用します.したがって、ロック競合を分析するときは、SQLの実行計画を確認して、インデックスが実際に使用されているかどうかを確認することを忘れないでください.

暗黙的なロック:

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

SELECT...IN SHARE MODEで共有ロックを取得し、主にデータ依存関係が必要な場合にある行のレコードが存在するかどうかを確認し、このレコードに対してUPDATEまたはDELETE操作を行う人がいないことを確保する.
しかし、現在のトランザクションでもそのレコードを更新する必要がある場合、デッドロックが発生する可能性が高く、行レコードをロックした後に更新する必要があるアプリケーションでは、SELECT...FOR UPDATE方式で排他ロックを取得する必要があります.
InnoDBはどのようにテーブルロックを追加しますか:
LOCK TABLESでInnoDBテーブルをロックするときは、AUTOMMITを0に設定しないとMySQLはテーブルにロックしません.トランザクションが終了する前に、UNLOCK TABLESでテーブルロックを解除しないでください.UNLOCK TABLESはトランザクションを暗黙的にコミットするからです.COMMITまたはROLLBACKは、LOCK TABLESで追加されたテーブルレベルロックを解放することはできません.UNLOCK TABLESでテーブルロックを解放する必要があります.

SET AUTOCOMMIT=0;
LOCK TABLES t1 WRITE, t2 READ, ...;
[do something with tables t1 and t2 here];
COMMIT;
UNLOCK TABLES;

時計で鍵をかけた以上、どうしてMyISAMエンジンを選ばないのですか.
六、隙間錠(Next-Key錠)
1.ギャップロックの定義:
nnodbのロック規則は、データレコードの最初のインデックスキーの前と最後のインデックスキーの後の空域空間にロック情報をマークすることによって実現される.Innodbのこのロック実装方式は「NEXT-KEY locking」(スロットロック)と呼ばれ、Query実行中に範囲検索を行うと、このキー値が存在しなくても範囲内のすべてのインデックスキー値がロックされるためである.
例:empテーブルに101個のレコードしかない場合、そのempidの値はそれぞれ1,2,...,100101であり、次のSQL:

mysql> select * from emp where empid > 100 for update;

範囲条件の検索であり、InnoDBは、条件を満たすempid値が101のレコードにロックをかけるだけでなく、empidが101より大きい(これらのレコードは存在しない)「ギャップ」にもロックをかける.

ギャップロックの欠点:

ギャップロックには、範囲キー値をロックすると、存在しないキー値が無実にロックされ、ロック時にロックキー値範囲内のデータを挿入できないという致命的な弱点があります.一部のシーンでは、パフォーマンスに大きな影響を及ぼす可能性があります.

*  Query         ， Innodb                   ，         ；
*  Quuery                  ，                           Query       ，       ，             ，         ；
*  Query            ，                      （            ），      

3 . ギャップロックの役割:
関連する独立性レベルの要件を満たすために、幻の読み取りを防止します.データのリカバリとレプリケーションの必要性のために.
4.注意
実際の応用開発では、特に多くの応用を同時に挿入し、ビジネスロジックをできるだけ最適化し、できるだけ等しい条件を使用して更新データにアクセスし、使用範囲の条件を避けなければならない.InnoDBは、範囲条件によるロック時にギャップロックを使用するほか、存在しないレコードに等しい条件でロックを要求すると、InnoDBもギャップロックを使用します.
七、明細レベルのロック競合状況の表示
SQLの実行:mysql>show status like'InnoDB_row_lock%';

mysql> show status like 'InnoDB_row_lock%';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| InnoDB_row_lock_current_waits | 0     |
| InnoDB_row_lock_time          | 0     |
| InnoDB_row_lock_time_avg      | 0     |
| InnoDB_row_lock_time_max      | 0     |
| InnoDB_row_lock_waits         | 0     |
+-------------------------------+-------+

ロック競合が深刻な場合は、InnoDB Monitorsを設定することで、ロック競合が発生したテーブルやデータ行などをさらに観察し、ロック競合の原因を分析することもできます.次のようになります.
モニタの設定:mysql>create table InnoDB_monitor(a INT) engine=InnoDB;
表示:mysql>show engine InnoDB status;
表示を停止:mysql>drop table InnoDB_monitor;
八、デッドロック
デッドロックとは何ですか:私がロックを解除するのを待っていて、私はあなたがロックを解除するのを待っていて、デッドロックを形成します.
どのようにデッドロックを発見します:InnoDBの事務管理とロックのメカニズムの中で、専門的にデッドロックを検出するメカニズムがあって、システムの中でデッドロックが発生した後のとても短い時間の内にこのデッドロックの存在を検出することができます
解決策:
ロールバックが小さいトランザクションは、REPEATABLE-READ独立性レベルで、2つのスレッドが同時に同じ条件の記録用SELECT...FOR UPDATEに排他ロックをかけると、この条件に合致しない記録の場合、2つのスレッドはロックに成功します.プログラムは記録がまだ存在しないことを発見し、新しい記録を挿入しようとしたが、2つのスレッドがそうすればデッドロックが発生する.この場合,独立性レベルをREAD COMMITTEDに変更すれば,問題を回避できる.トランザクション・サイズの判断:トランザクションがそれぞれ挿入、更新または削除するデータ量
注意:
デッドロックが発生したシーンにおいてInnoDBストレージエンジンのみが関与している場合、InnoDBはそのデッドロックを検出することができず、ロックタイムアウト制限パラメータInnoDB_lock_wait_timeoutが解決します.
九、最適化行レベルロック
InnoDBストレージエンジンは、ラインレベルのロックを実現しているため、ロックメカニズムの実現においては、テーブルレベルのロックよりも性能損失が高くなる可能性があるが、全体的な同時処理能力においてはMyISAMのテーブルレベルのロックよりはるかに優れている.システムのコンカレント量が高い場合、InnoDBの全体的な性能はMyISAMに比べて明らかな優位性があります.しかし、InnoDBの行レベルロックにも脆弱な面があり、私たちが不適切に使用すると、InnoDBの全体的な性能表現がMyISAMよりも高くないだけでなく、さらに悪くなる可能性があります.
(1)InnoDBの行レベルロックを合理的に利用し,長所を伸ばし短所を避けるためには,以下の作業を行う必要がある.

はできるだけすべてのデータ検索をインデックスによって完成させ、InnoDBがインデックスキーによるロックができないため、テーブルレベルのロックにアップグレードすることを避ける.

合理的にインデックスを設計して、InnoDBにインデックスキーの上でロックをかける時できるだけ正確にさせて、できるだけロック範囲を縮小して、不要なロックをもたらして他のQueryの実行に影響を与えないようにします;

範囲ベースのデータ検索フィルタ条件をできるだけ低減し、ギャップロックによる負の影響によりロックされていない記録をロックしないようにする.

トランザクションのサイズをできるだけ制御し、ロックされたリソース量とロック時間の長さを減らす.

ビジネス環境で許可されている場合は、MySQLがトランザクション独立性レベルを実現するために追加コストを削減するために、できるだけ低いレベルのトランザクション独立性を使用します.

(2)InnoDBの行レベルロックとトランザクション性のため、必ずデッドロックが発生します.以下は、デッドロックの発生確率を減らすための一般的なアドバイスです.

類似の業務モジュールでは、できるだけ同じアクセス順序でアクセスし、デッドロックの発生を防止する.

同じトランザクションでは、できるだけ一度に必要なすべてのリソースをロックし、デッドロックの発生確率を減らすことができます.

デッドロックが発生しやすいビジネス部分では、アップグレードロック粒度を使用して、表レベルロックによってデッドロックの発生確率を低減することを試みることができる.

テキストリンク:https://blog.csdn.net/zcl_love_wx/article/details/81983267