【MySQL】ロックと独立性レベル

文書ディレクトリ

一、前言

二、時計ロック

三、MDLロック

四、行錠

(一)インデックスとロック

五、gapロック^[1]^

六、next-key lock

7、独立性レベル

の選択方法

Ref

一、前言

スナップショット読みの幻読みはmvccによって

を解決する.

現在読んでいる幻読はnext-keyロックにより

を解決する.

読み出しコミット独立性レベルは、一般にgap lock

がない.

繰り返し読み取り可能独立性レベルで、現在の読み取りがトリガーされた場合も、トランザクションの存続期間のデータの一貫性を保証します.具体的には、どのように保証しますか.答えは、ロックをかけて、本トランザクションのデータ整合性を破壊する他のトランザクションをブロックすることです.

二、時計ロック
三、MDLロック
四、行錠
行ロックはInnoDBエンジン独自のものであり、すなわち行ロックはエンジン層で実現される
(一)インデックスとロック
繰り返し可能読み出し時:InnoDBはローにアクセスしたときにのみロックされ、インデックスはInnoDBアクセスのロー数を減らし、ロックの数を減らすことができます.しかし、これはInnoDBがストレージエンジン層で不要なローをすべてフィルタリングできる場合にのみ有効です.インデックスが無効なローをフィルタできない場合、InnoDBがデータを取得してサーバレイヤに戻った後、MySQLサーバはWHERE句を適用できます.この場合、ローのロックは回避できません.InnoDBはローをロックしており、トランザクションのコミット時に解放されます.つまりインデックスが使用されていない場合、InnoDBはテーブル全体をロックします.
example:

name     ：
+----+---------------------+------+
| id | dat                 | name |
+----+---------------------+------+
|  1 | 2020-01-24 12:00:55 | xxx  |
|  2 | 2020-01-24 12:01:34 | yyy  |
|  3 | 2020-02-23 12:25:24 | zzz  |
+----+---------------------+------+

Session A：
START TRANSACTION WITH CONSISTENT SNAPSHOT;
UPDATE tb_date SET name='zzz' WHERE id=3;

Session B：
UPDATE tb_date SET name='yyy' WHERE name='yyy';

  Session B     ，   Session A    id=3    ， Session B          

読み込みコミット時:MySQL 5.1と更新されたバージョンでは、InnoDBは、サーバ側でローをフィルタリングした後にロックを解除できます.このプロパティは、読み込みコミットの独立性レベルに存在し、繰り返し読み取り可能であり、このプロパティはありません.
五、gapロック[1]
 gapロック間は反発せず、他のロック間も反発せず、INSERTとのみ反発し、gapロックはロックギャップに用いられる.   读提交隔离级别一般没有gap lock ( 不过也有例外情况, 比如insert 出现主键冲突的时候,也可能加间隙锁?在外键场景下还是有间隙锁?)
Excample:表あり

CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `c` (`c`)
) ENGINE=InnoDB;

insert into t values(0,0,0),(5,5,5),
(10,10,10),(15,15,15),(20,20,20),(25,25,25);

実行文:

Session A:
mysql> BEGIN;
mysql> SELECT d FROM t WHERE c=5 FOR UPDATE;
 
mysql> SELECT id FROM t WHERE c=5 FOR UPDATE;

Session B:
mysql> INSERT INTO t VALUES (8, 8, 8);

上記のSessionBは、SessionAトランザクションの存続期間のデータ整合性を維持するために、innoDBは、範囲c(0,10)間にギャップロックを付加する(この範囲内には、c=5の行が他のトランザクションに挿入される可能性があるため).まとめ:SessionAはid=5の行に行ロックをかけ、インデックスページcに(0,10)の範囲のgapロックを加えた(注:この文はクラスタインデックスページにgapロックを付けない)
索引の最適化の上書き:文の実行:

Session A:
mysql> BEGIN;
mysql> SELECT id FROM t WHERE c=5 LOCK IN SHARE MODE;

Session B:
mysql> UPDATE t SET d=d+1 WHERE c=5;

セッションBはブロックされません.アクセスしたオブジェクトのみがロックされるためです.セッションAはidをクエリーします.cインデックスページの上書きインデックスを直接使用して結果を取得できます.そのため、クラスタインデックスページにid=5という行をロックしていませんが、セッションAがFOR UPDATEを実行している場合は異なります.これからデータを更新すると判断されるので、したがって、プライマリ・キー・インデックスに条件を満たすローに上りロックが付加されます.
Note:

インデックスクエリを使用する場合、クエリのローが存在しない場合、ロー・ロックは追加されませんが、適切な範囲でgapロックが追加されます.これは、トランザクションの存続期間におけるデータの一貫性を保証するためです.

インデックスクエリを使用しないと、全テーブルクエリが実行され、InnoDBはすべてのローに上りロックが付加するとともに、すべてのギャップにgapロックが付加される.実際には、InnoDBは暗黙的にテーブルレベルロックを呼び出すことはなく、テーブル全体をロックすることはこのように実現される

である.
六、next-key lock
ギャップロックと行ロックはnext-key lockと呼ばれ、各next-key lockは前開後閉区間、例えば文である

mysql> SELECT d FROM t WHERE c=5 FOR UPDATE;

next-keyロック(0,5)とgapロック(5,10)が追加されました
ロックおよび最適化ルール[2]:ルール1:InnoDBロックの基本単位はnext-key lockである.next-key lockは前開後閉区間規則2:検索中にアクセスしたオブジェクトのみロック最適化1:インデックス上の等値クエリー、一意のインデックスにロックをかける場合、next-key lockは行ロック最適化2:インデックス上の等値クエリーに退化し、右遍歴し、最後の値が等値条件を満たさない場合、next-key lockはギャップロック規則3に退化した:範囲クエリー、右にクエリーを遍歴する時、まず左境界のnext-keyロックを確定し、一意性インデックスに対して、左に最初の条件を満たす値まで検索し、非一意インデックスに対して、左に最初の条件を満たさない値まで検索する.右境界のnext-keyロックを決定すると、インデックスが一意であるかどうかにかかわらず、最初の条件を満たさない値まで右に検索されます.クエリーを左に巡回すると逆になります.例:

mysql> SELECT * FROM t WHERE id>=10 AND id<=20 FOR UPDATE;

まず左境界を決定し、idは一意性インデックスであり、左に最初の条件を満たす値10を検索し、そこでnext-key lock(10,15)を加えると、id>=10に等値クエリid=10が隠されているため、最適化1に基づいて、上りロック10を加え、右境界を決定し、最初の条件を満たさない値25を右に検索し、next-keyロック(20,25)を加え、この文のロック範囲を10、(10,15)、(15,20)、(20,25)とする必要がある.

mysql> SELECT * FROM t WHERE c>=10 AND c<=20 FOR UPDATE;

まず左境界を決定し、cは非一意性インデックスであり、左に最初の条件を満たさない値5を検索し、そこでnext-key lock(5,10)を加えて右境界を確定し、右方向に最初の条件を満たさない値25を検索し、next-keyロック(20,25)を加え、この文のロック範囲(5,10)、(10,15)、(15,20)、(20,25)ルール4:Limitはロック範囲を小さくすることができる[2][3]ルール5:ORDER BY DESCは遍歴方向を変えることができる[2]!
Note:ロック規則を解析するときにnext-key lockで解析できます.しかし、具体的に実行する場合は、ギャップロックとローロックの2つのセグメントに分けて実行されることを知っておく必要があり、まずgapロックを加え、さらにローロックを加える[2].
七、隔離レベルの選択方法
   インターネットプロジェクトでは一般的に隔離レベルを読み取りコミットとし、binlogのフォーマットをRowとする[4].
Ref
[1]MysqlのGAPロック(ギャップロック)[2](極客時間)なぜ私は1行の文だけを変更して、こんなにロックが多いのですか?[3]MySQLでgap lock/next-key lockに関する質問[4]インターネットプロジェクトでmysqlが選択すべきトランザクション独立性レベル