MySQL の Innodb のロックについて

いろいろ分からなかったので同じ DB に複数セッションで接続をして試してみた。

この時参考にしていたページは

MySQL :: MySQL 5.6 リファレンスマニュアル :: 14.2.8 InnoDB のさまざまな SQL ステートメントで設定されたロック

今回は次のようなテーブルを用意した。

CREATE TABLE tokens (
  token   varchar(255) NOT NULL,
  created datetime NOT NULL,
  PRIMARY KEY (`token`),
  KEY created (`created`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

トランザクション開始直後に INSERT を行う

セッション A

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into tokens (token, created) values ("2", now());
Query OK, 1 row affected (0.00 sec)

mysql> insert into tokens (token, created) values ("3", now());
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted
mysql> insert into tokens (token, created) values ("4", now());
Query OK, 1 row affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

セッション B

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into tokens (token, created) values ("1", now());
Query OK, 1 row affected (0.01 sec)

mysql> insert into tokens (token, created) values ("2", now());
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted
mysql> insert into tokens (token, created) values ("3", now());
Query OK, 1 row affected (0.01 sec)

mysql> commit;
Query OK, 0 rows affected (0.01 sec)


mysql> select * from tokens;
+-------+---------------------+
| token | created             |
+-------+---------------------+
| 1     | 2018-08-02 08:41:19 |
| 2     | 2018-08-03 07:48:23 |
| 3     | 2018-08-03 07:48:56 |
| 4     | 2018-08-03 07:49:53 |
+-------+---------------------+

操作したこと

セッション A, B でトランザクションを開始
セッション B にて token に "1" を INSERT
セッション A にて token に "2" を INSERT
セッション B にて token に "2" を INSERT -> 行にロックが掛かってるため Interrupt した
セッション B にて token に "3" を INSERT
セッション A にて token に "3" を INSERT -> 行にロックが掛かってるため Interrupt した

これから分かったこと

トランザクションを開始して INSERT を行うと INSERT をした行にロックが掛かり、他のセッションからその行への更新や挿入はトランザクションが終了するまで待ち続ける。
しかし他のセッションが他の行に対する更新や挿入を行うことができる

トランザクション開始直後に UPDATE を行う

セッション A

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update tokens set created = "2018-08-04 08:41:29" where token = "1";
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update tokens set created = "2018-08-02 08:41:29" where token = "2";
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted
mysql> insert into tokens (token, created) values ("6", now());
Query OK, 1 row affected (0.01 sec)

mysql> commit;
Query OK, 0 rows affected (0.01 sec)

セッション B

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update tokens set created = "2018-08-02 08:41:29" where token = "1";
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted
mysql> update tokens set created = "2018-08-04 08:41:29" where token = "2";
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.01 sec)

操作したこと

セッション A, B でトランザクションを開始
セッション A にて token の "1" を UPDATE
セッション B にて token の "1" を UPDATE -> 行にロックが掛かってるため Interrupt した
セッション B にて token の "2" を UPDATE
セッション A にて token の "2" を UPDATE -> 行にロックが掛かってるため Interrupt した
セッション A にて token に "6" を INSERT

これから分かったこと

UPDATE もトランザクション開始直後の INSERT と同じことがわかった

トランザクション開始 SELECT FOR UPDATE を行う

ここからが本番。今までは各行にロックが掛かる様子を確認してきた。SELECT FOR UPDATE ではどうなるのかを確認していく。

セッション A - (SELECT FOR UPDATE)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from tokens for update;
+-------+---------------------+
| token | created             |
+-------+---------------------+
| 1     | 2018-08-02 08:41:19 |
| 2     | 2018-08-03 07:48:23 |
| 4     | 2018-08-03 07:49:53 |
| 5     | 2018-08-03 07:50:26 |
| 6     | 2018-08-03 07:54:13 |
+-------+---------------------+
14 rows in set (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.01 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from tokens where token = "1" for update;
+-------+---------------------+
| token | created             |
+-------+---------------------+
| 1     | 2018-08-02 08:41:19 |
+-------+---------------------+
1 row in set (0.00 sec)

セッション B

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into tokens (token, created) values ("7", now());
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted

mysql> rollback;
Query OK, 0 rows affected (0.01 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into tokens (token, created) values ("7", now());
Query OK, 1 row affected (0.01 sec)

操作したこと

セッション A にてトランザクション開始直後 select * from tokens for update を実行
セッション B にてトランザクション開始直後 token に "7" を INSERT -> テーブルにロックが掛かってるため Interrupt した
セッション A, B にて rollback してトランザクションを開始
セッション A にて where token = "1" で SELECT FOR UPDATE
セッション B にて token に "7" を INSERT -> できた

分かったこと

SELECT FOR UPDATE を行うと検索範囲の行ロックを行うことができる
SELECT * FROM FOR UPDATE だとテーブルロック

[BTW1] ギャップロックについて

ギャップロックなるものが存在するらしい。

良く分かるMySQL Innodbのギャップロック

例えば次のような index かつ PRIMARY KEY ではないテーブルを作る。

CREATE TABLE users (
    id int,
    created datetime,
    KEY id (`id`)
);

事前に次のようなデータにしておく。

mysql> select * from users;
+------+---------------------+
| id   | created             |
+------+---------------------+
|    2 | 2018-02-20 12:22:00 |
|    5 | 2018-02-20 12:22:00 |
|    7 | 2018-02-20 12:22:00 |
+------+---------------------+
3 rows in set (0.00 sec)

セッション A

mysql> explain select * from users where id = 2\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: users
   partitions: NULL
         type: ref
possible_keys: id
          key: id
      key_len: 5
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from users where id = 2 for update;
+------+---------------------+
| id   | created             |
+------+---------------------+
|    2 | 2018-02-20 12:22:00 |
+------+---------------------+
1 row in set (0.00 sec)

セッション B

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert hello (id, created) values (3, now());
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted
mysql> insert hello (id, created) values (6, now());
Query OK, 1 row affected (0.00 sec)

mysql> insert hello (id, created) values (1, now());
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted
mysql> insert hello (id, created) values (0, now());
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted
mysql> insert hello (id, created) values (-1, now());
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted
mysql> insert hello (id, created) values (-100, now());
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted
mysql> insert hello (id, created) values (-1000000000, now());
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted

操作したこと

ここでもう一度テーブルの中身

mysql> select * from users;
+------+---------------------+
| id   | created             |
+------+---------------------+
|    2 | 2018-02-20 12:22:00 |
|    5 | 2018-02-20 12:22:00 |
|    7 | 2018-02-20 12:22:00 |
+------+---------------------+
3 rows in set (0.00 sec)

セッション A にてトランザクション開始直後 select * from users where id = 2 for update を実行
セッション B にて id = 3 を INSERT -> ギャップロックが掛かって INSERT ができない
セッション B にて id = 6 を INSERT
セッション B にて id = 1 を INSERT -> ギャップロックが掛かって INSERT ができない
セッション B にて id = 0 を INSERT -> ギャップロックが掛かって INSERT ができない
セッション B にて id = -1 を INSERT -> ギャップロックが掛かって INSERT ができない
セッション B にて id = -100 を INSERT -> ギャップロックが掛かって INSERT ができない
セッション B にて id = -1000000000 を INSERT -> ギャップロックが掛かって INSERT ができない

分かったこと

トランザクション直後に SELECT FOR UPDATE を index かつ PRIMARY KEY ではないカラムに行った場合
別トランザクションで id = 2 を INSERT しようとした場合次のインデックス and 前のインデックスまでの範囲に INSERT ができないことがわかった
つまり INSERT できない値 x の範囲は (x <= 2 && 2 < x && x < 5)

[BTW2] Explain について

一番見るべき重要なフィールドは

type
Extra

っぽい。

type に関して参考になるリンク。上から順に検索速度が早くなる。

MySQL :: MySQL 5.7 Reference Manual :: 8.8.2 EXPLAIN Output Format

アルパカ三銃士

〜アルパカに酔いしれる獣たちへ捧げる〜

MySQL の Innodb のロックについて

トランザクション開始直後に INSERT を行う

セッション A

セッション B

操作したこと

これから分かったこと

トランザクション開始直後に UPDATE を行う

セッション A

セッション B

操作したこと

これから分かったこと

トランザクション開始 SELECT FOR UPDATE を行う

セッション A - (SELECT FOR UPDATE)

セッション B

操作したこと

分かったこと

[BTW1] ギャップロックについて

セッション A

セッション B

操作したこと

分かったこと

[BTW2] Explain について