LoginSignup
2
3

More than 3 years have passed since last update.

@akkiihs

【図解】MySQL8.0 InnoDB ロック(インデックスレコード・ギャップ・ネクストキー)の確認・検証

Posted at

概要

インターンのお手伝いで 『MySQL(InnoDB)のロックがどうやっておこなわれているのか？』 の勉強会をすることがあったので記事にします。

もはや何番煎じって感じですが、自分なりのイメージと検証した内容を含めてまとめてみました :pencil:

この記事を通して、MySQLがインデックスレコードに対してロックをかけるという感覚が少しでも伝わればと思います。

環境

MySQL
- バージョン: 8.0
- ストレージエンジン: InnoDB (以下、MySQL = InnoDBとします)
- 分離レベル: Repeatable Read (Default)

今回、利用するテーブルは id と name から構成されるテーブル。id に主キー。

mysql> create table user (
    ->   id int unsigned not null auto_increment,
    ->   name varchar(255) not null default '',
    ->   primary key (id)
    -> );

mysql> show index from user\G
*************************** 1. row ***************************
        Table: user
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
   Expression: NULL

データを3件だけ入れておく （ギャップロックの話をしたいのでインデックスレコード間に隙間を空けておく ※後述）

mysql> select * from user;
+----+------+
| id | name |
+----+------+
|  1 | a    |
| 10 | b    |
| 20 | c    |
+----+------+

導入

本題に入る前に、ロックの話をする導入として『なぜ、分離レベルがRepeatable Readであると、ファジーリード・ファントムリードを防ぐことができるのか？』という点に触れておきます。
※ 分離レベルの表によっては、Repeatable Readであると、ファントムリードは発生することになっているが、MySQLだと発生しない

それを保証しているものが、 MVCC (MultiVersion Concurrency Control) という複数バージョン管理によって同時実行制御できるというもの。

あくまでイメージですが、gitみたいなものと言えばイメージしやすいのかなと思いますが、Conflictを未然に検知するという部分は異なります。

このConflictを ロック を通して検知しています。

本題

ロックは、排他制御のことでRDBMSにおけるデータの整合性を保つための仕組みです。

ロックの種類はざっくりと以下のようになります。

  • テーブルロック
  • レコードロック
    • インデックスレコードロック
    • ギャップロック: インデックスレコードロック間に対するロック
    • ネクストキーロック: 「インデックスレコードロック」とその手前の「ギャップロック」の組み合わせ

MySQLのレコードロックは、 実際のレコードに対してロックがかかっているわけではなく、インデックスレコードに対してかかっている というのが重要なポイントです。

👇 イメージとしてはこんな感じです。
ロックの種類のイメージ

そして、ロックのモードは以下の2種類があります。

  • 共有ロック(以下、S)・・・読み取りの許可
  • 排他ロック(以下、X)・・・追加・更新・削除の許可

※ 正確には、インテンションロック（IS, IX）と呼ばれる、テーブルロックなんだけどトランザクションで必要となるレコードロックを意図するロックがありますが割愛します。

唯一、Sに対してSをかけにいこうとすると互換性があります。それ以外はConflictが発生しロックの解放待ちが発生します。

互換性マトリクス X S
X Conflict Conflict
S Conflict Compatible

ここまで、用語を一通り触れたので、確認に移ります。

確認

MySQL 8.0 からは performance_schema.data_locks でロックの状況を確認できます。

SELECT object_name, index_name, lock_type, lock_mode, lock_status, lock_data
FROM performance_schema.data_locks;

+-------------+------------+-----------+------------------------+-------------+-----------+
| object_name | index_name | lock_type | lock_mode              | lock_status | lock_data |
+-------------+------------+-----------+------------------------+-------------+-----------+
..
| user        | PRIMARY    | RECORD    | X,REC_NOT_GAP          | GRANTED     | 1         |
| user        | PRIMARY    | RECORD    | X                      | GRANTED     | 10        |
+-------------+------------+-----------+------------------------+-------------+-----------+

lock_mode で、ロックの種類を確認できます。

lock_mode での記載 ロックの種類
REC_NOT_GAP インデックスレコードロック
GAP ギャップロック
(記載なし) ネクストキーロック(REC_NOT_GAP + GAP)

👇 ロックのかけ方は以下

ロックのモード クエリ
S
  • SELECT .. FROM .. LOCK IN SHARE MODE
X
  • SELECT .. FROM .. FOR UPDATE
  • UPDATE ..
  • DELETE ..

　　

確認①: インデックスレコードロック

インデックスレコードロックを確認するために、同じインデックスレコードに対して更新をかけてみる

T1> BEGIN; -- 以下、省略
T2> BEGIN; -- 以下、省略

T1> UPDATE user SET name = 'x' WHERE id = 1;
Query OK, 1 row affected (0.00 sec)                                                                                     
Rows matched: 1  Changed: 1  Warnings: 0 

T2> UPDATE user SET name = 'x' WHERE id = 1;
-- Xが取得できず待ち..

T1> ROLLBACK; -- or COMMIT;
-- T2の待ち時間内であればT2のUPDATEが実行される

🔒 ロックの状況
確認① ロックの状況
==> T1で「1」に対してインデックスレコードロックをかけるので、T2では「1」に対してインデックスレコードロックが取得できないので待ち

　　

確認②: ギャップロック, ネクストキーロック

今度はギャップロック, ネクストキーロックを確認するために、インデックスレコードに対して範囲条件で更新をかけてみて、後からその間にデータ登録してみる

T1> UPDATE user SET name = 'x' WHERE id >= 1 AND id <= 10;
Query OK, 2 row affected (0.00 sec)                                                                                     
Rows matched: 2  Changed: 2  Warnings: 0 

T2> INSERT INTO user (id, name) values (2, ‘d’);
-- Xが取得できず待ち..

🔒 ロックの状況
確認② ロックの状況
==> T1で「1」に対するインデックスレコードロック、「10」に対するネクストキーロックがかかり、1と10のみならず、2~9のギャップに対してもロックがかけられるので、T2では「2」をデータ登録しようとしてもロックが取得できないです

　　

確認③: 挿入インテンションギャップロック

確認②で「挿入インテンションギャップロック」というのが登場しました。

これは、ギャップロックの一種で、挿入と意図するもので挿入インテンションギャップロック同士であればConflictは発生しません。

試しに「10」に対する挿入インテンションギャップロックが発生するように、T2, T3でデータ登録してみます。

T1> UPDATE user SET name = 'x' WHERE id >= 1 AND id <= 10;
Query OK, 2 row affected (0.00 sec)                                                                                     
Rows matched: 2  Changed: 2  Warnings: 0 

T2> INSERT INTO user (id, name) values (2, 'd');
-- Xが取得できず待ち..

T3> INSERT INTO user (id, name) values (3, 'e');
-- Xが取得できず待ち..

T1> ROLLBACK;
T2> Query OK, 1 row affected (39.66 sec)
T3> Query OK, 1 row affected (8.57 sec)

🔒 ロックの状況
確認③ ロックの状況
==> ロックの解放待ちの後、T1をROLLBACKまたはCOMMITでロックを解放すると、T2, T3 ともに挿入インテンションギャップロックを取得できます

検証

検証①: ヒットしなかったらロックはかかる？

存在しないインデックスレコード「2」に対して更新（削除）をしようとするとどうなるのか、検証してみます。

T1> UPDATE user SET name = 'x' WHERE id = 2;
Query OK, 0 row affected (0.00 sec)                                                                                     
Rows matched: 0  Changed: 0  Warnings: 0 

T2> INSERT INTO user (id, name) values (3, 'd');
-- Xが取得できず待ち

🔒 ロックの状況
検証① ロックの状況
==> ヒットしなくても、ヒットしなかったギャップに対してロックがかかる

　　

検証②: インデックスを張っていないカラムだとロックはかかる？

インデックスを張っていない name カラムで抽出して更新（削除）をしようとするとどうなるのか、検証してみます。

T1> UPDATE user SET name = 'x' WHERE name = 'b';
Query OK, 1 row affected (0.00 sec)                                                                                     
Rows matched: 1  Changed: 1  Warnings: 0 

T2> INSERT INTO user (id, name) values (21, 'd');
-- Xが取得できず待ち..

🔒 ロックの状況
検証② ロックの状況
supremum pseudo-record というのは、ドキュメントによると「最小上限」の疑似レコードと訳されています。
実際にインデックスレコードは存在しないので擬似的なレコードを生成して、それに対してロックをかけるというわけです。

==> インデックスを張っていないカラムで検索した場合、フルテーブルスキャンになり、すべてのインデックスレコードに対してネクストキーロックがかけられます

では、インデックス（ユニークではない）を張ったらどうなるか？

-- nameにインデックス（ユニークではない）を張る
mysql> ALTER TABLE user ADD INDEX name_idx(name);

T1> UPDATE user SET name = 'x' WHERE name = 'b';
Query OK, 1 row affected (0.00 sec)                                                                                     
Rows matched: 1  Changed: 1  Warnings: 0 

T2> INSERT INTO user (id, name) values (21, 'd');
Query OK, 1 row affected (0.00 sec)
-- ロックが取得できる

🔒 ロックの状況
検証② ロックの状況 インデックス（ユニークではない）を張る
name カラムにインデックスを張ったにも関わらず「b」に対するインデックスレコードロックではなく、ネクストキーロックがかかり、「c」に対するギャップロックがかかりました。

これは、 インデックスがユニークではない からです。実際にインデックスレコード上を走査するものの気持ちになってみると分かるのですが、ユニークではないため、「b」の次のインデックスレコードが「b」である可能性があるからです。

では、インデックス（ユニークである）を張ったらどうなるか？検証③で確認します。

さらに、不思議な点がもう一つあります。さきほど張った name_idx だけでなく PRIMARY インデックスについてもロックがかけられています。

これは、 クラスタインデックスとセカンダリインデックス によるものです。

端的に説明するならば、 PRIMARY 主キーのインデックスが クラスタインデックス で、それ以外のインデックスが セカンダリインデックス です。

MySQLのインデックスはB treeで管理されているため、イメージとしては以下のようなものになります。

クラスタインデックスとセカンダリインデックス

セカンダリインデックスの走査が完了すると、クラスタインデックスの走査に移ります。そのため、セカンダリインデックス name_idx だけでなく、クラスタインデックス PRIMARY にもロックがかかります。

　　

検証③: ユニークインデックスの場合、ロックはどうなるか？

-- nameにインデックス（ユニークである）を張る
mysql> CREATE UNIQUE INDEX name_idex ON user (name);


T1> UPDATE user SET name = 'x' WHERE name = 'b';
Query OK, 1 row affected (0.00 sec)                                                                                     
Rows matched: 1  Changed: 1  Warnings: 0 

T2> INSERT INTO user (id, name) values (21, 'd');
Query OK, 1 row affected (0.00 sec)
-- ロックが取得できる

🔒 ロックの状況
スクリーンショット 2020-11-02 20.48.08.png
==> ユニークなインデックスの場合、「b」が見つかった時点でそれ以上走査する必要がないので「c」に対してのギャップロックはかかりません

　　

まとめ

MySQL(InnoDB)のロックがインデックスレコードに対してかかる様子を、確認・検証を通して図解しました。

検証を通してDB設計やクエリを書く際に気をつけたいこと

  • 存在するデータに対して更新（削除）をすること
    • でないと、存在しなかったギャップに対してギャップロックが取得される
  • 更新（削除）の対象は、インデックスが張ってあるカラムで抽出すること
    • でないと、すべてのインデックスレコードに対してネクストキーロックが取得される可能性がある
    • （だからといって、なんでもかんでもインデックスを張ればいいというものでもありません）
2
3
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
2
3