Open9

MySQL InnoDB トランザクション問題集

MySQL
トランザクション
lndcltlndclt

MySQLのトランザクション/ロック周りを改めて色々実験して、忘れてしまったらもったいないので備忘録として自分で問題集を作ってみる。

  • Primary Key は id* というカラムに貼られ、時には複合PKである場合もある。
  • 分離レベルは断りがなければ Repeadable Read とする。
  • クエリは時系列順に並んでいるものとする。
  • MySQL Serverのバージョンは8.4.6とする。
lndcltlndclt

Q.1

以下のクエリ実行結果を見て、これらトランザクションの分離レベルを答えよ。

mysql> select * from t;
+----+-----+
| id | val |
+----+-----+
|  3 |   3 |
|  5 |   5 |
|  7 |   7 |
+----+-----+
3 rows in set (0.00 sec)

# ---------------- txn 1 ---------------------
mysql> SET SESSION TRANSACTION ISOLATION LEVEL ???;
Query OK, 0 rows affected (0.00 sec)

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

# ---------------- txn 2 ---------------------
mysql> SET SESSION TRANSACTION ISOLATION LEVEL ???;
Query OK, 0 rows affected (0.00 sec)

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

# ---------------- txn 1 ---------------------
mysql> update t set val = 0 where id = 3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

# ---------------- txn 2 ---------------------
mysql> select * from t where id = 3;
+----+-----+
| id | val |
+----+-----+
|  3 |   0 |
+----+-----+
1 row in set (0.00 sec)

# ---------------- txn 1 ---------------------
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

# ---------------- txn 2 ---------------------
mysql> select * from t where id = 3;
+----+-----+
| id | val |
+----+-----+
|  3 |   3 |
+----+-----+
1 row in set (0.00 sec)
答え

READ UNCOMMITTED

txn 1 でコミットする前の行更新が txn 2 にも影響してしまっている。すなわち、Dirty Read が発生しており、これが発生する分離レベルは READ UNCOMMITTED のみである。

lndcltlndclt

Q.2

以下のクエリ実行結果を見て、これらトランザクションの分離レベルを答えよ。

mysql> select * from t;
+----+-----+
| id | val |
+----+-----+
|  3 |   3 |
|  5 |   5 |
|  7 |   7 |
+----+-----+
3 rows in set (0.00 sec)

# ---------------- txn 1 ---------------------
mysql> SET SESSION TRANSACTION ISOLATION LEVEL ???;
Query OK, 0 rows affected (0.00 sec)

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

# ---------------- txn 2 ---------------------
mysql> SET SESSION TRANSACTION ISOLATION LEVEL ???;
Query OK, 0 rows affected (0.00 sec)

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

# ---------------- txn 1 ---------------------
mysql> update t set val = 0 where id = 3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

# ---------------- txn 2 ---------------------
mysql> select * from t where id = 3;
+----+-----+
| id | val |
+----+-----+
|  3 |   3 |
+----+-----+
1 row in set (0.00 sec)

# ---------------- txn 1 ---------------------
mysql> commit;
Query OK, 0 rows affected (0.00 sec)

# ---------------- txn 2 ---------------------
mysql> select * from t where id = 3;
+----+-----+
| id | val |
+----+-----+
|  3 |   0 |
+----+-----+
1 row in set (0.00 sec)
答え

READ COMMITTED

今回は txn 1 でコミットする前の行更新は txn 2 には影響していない。つまり、Dirty Read は発生していない。

しかし、txn 1 がコミットされると txn 2 にて読み取る内容が変わってしまっている。すなわち、 Fuzzy Read (Non-Repeatable Read) が発生している。よって、READ COMMITTED であると判断できる。

lndcltlndclt

Q.3

分離レベルが REPEATABLE READ / SERIALIZABLE の場合、以下のクエリで ??? となっている部分の結果はどうなるか？

mysql> select * from t;
+----+-----+
| id | val |
+----+-----+
|  3 |   3 |
|  5 |   5 |
|  7 |   7 |
+----+-----+
3 rows in set (0.00 sec)

# ---------------- txn 1 ---------------------
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

# ---------------- txn 2 ---------------------
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

# ---------------- txn 1 ---------------------
mysql> update t set val = 0 where id = 3;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

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

# ---------------- txn 2 ---------------------
mysql> select * from t where id = 3;
???
答え

txn 1 のコミットよりも前に txn 2 のトランザクションは開始しており、txn 1 のコミットは txn 2 の読み取り結果に影響を与えない(= Repeatable Read)。よって結果は以下のようになる

+----+-----+
| id | val |
+----+-----+
|  3 |   3 |
+----+-----+

なお、この仕組みを実現するために InnoDB は MVCC(Multiversion Concurrency Control) によるスナップショット分離 と Next-Key Lock を採用している。

lndcltlndclt

Q.4

MySQL(InnoDB) では分離レベルが REPEATABLE READ であったとしても Phantom Read が発生しない。これはなぜか？

答え

Phantom Read とは、行挿入を含むトランザクションのコミットによって、他トランザクション内での検索結果が変わってしまうことをいう。InnoDB では分離レベルが REPEATABLE READ である場合、MVCC(Multiversion Concurrency Control) によるスナップショット分離と Next-Key Lock によって、Phantom Read を防ぐ。

参考になるスライド
https://speakerdeck.com/mpyw/postgres-niokerutoranzakusiyonfen-li-reberu?slide=41

lndcltlndclt

Q.5

txn 1 にて以下のような排他ロックをとっている場合、別トランザクションの txn 2 にて以下の (a) から (h) の insert クエリのうち、実行可能なものを全て選べ。

mysql> select * from t;
+----+-----+
| id | val |
+----+-----+
|  2 |   2 |
|  5 |   5 |
|  8 |   8 |
| 11 |  11 |
+----+-----+
4 rows in set (0.00 sec)

# ---------------- txn 1 ---------------------
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t where id between 4 and 9 for update;
+----+-----+
| id | val |
+----+-----+
|  5 |   5 |
|  8 |   8 |
+----+-----+
2 rows in set (0.00 sec)

# ---------------- txn 2 ---------------------
mysql> insert into t (id, val) values (1, 1);     # (a)
mysql> insert into t (id, val) values (3, 3);     # (b)
mysql> insert into t (id, val) values (4, 4);     # (c)
mysql> insert into t (id, val) values (6, 6);     # (d)
mysql> insert into t (id, val) values (7, 7);     # (e)
mysql> insert into t (id, val) values (9, 9);     # (f)
mysql> insert into t (id, val) values (10, 10);   # (g)
mysql> insert into t (id, val) values (12, 12);   # (h)
答え

(a)と(h)

ギャップロックについて問う問題。Indexの並びとそのギャップを列挙すると以下のようになる。

... 2 ... 5 ... 8 ... 11 ...

排他ロックをとると、ヒットしたIndexと、その前後のギャップにinsertができなくなる。

... 2 [ ... 5 ... 8 ... ] 11 ...

なお、上記の例で言えば id が 3, 10 のレコードを入れる場合のように実際には別トランザクションで挿入が発生したとしても Phantom Read が発生しないケースもあるが、実装をシンプルにするため/安全側に倒すために直前・直後のギャップを全てロックしている。

lndcltlndclt

Q.6

commit; をせずに begin; を複数回実行した場合の挙動はどうなるか？

答え

2度目以降のbeginでは暗黙的にcommitが実行され、新たなトランザクションが開始される。
なお、トランザクションのネストをすることはできないが、 SAVEPOINT 命令を使えば特定の地点までロールバックすることができるので必要に応じてこのコマンドを使おう。

参考記事: トランザクションのネストの使い方まとめた（初心者向け）

lndcltlndclt

Q.7

txn 1 にて以下のような排他ロックをとっている場合、別トランザクションの txn 2 にて以下の (a) から (f) の insert クエリのうち、実行可能なものを全て選べ。

mysql> select * from t;
+-----+-----+-----+
| id1 | id2 | val |
+-----+-----+-----+
|   2 |  10 | 210 |
|   2 |  20 | 220 |
|   3 |  10 | 310 |
|   3 |  20 | 320 |
+-----+-----+-----+
4 rows in set (0.00 sec)

# ---------------- txn 1 ---------------------
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t where id1 = 3 for update;
+-----+-----+-----+
| id1 | id2 | val |
+-----+-----+-----+
|   3 |  10 | 310 |
|   3 |  20 | 320 |
+-----+-----+-----+
2 rows in set (0.00 sec)

# ---------------- txn 2 ---------------------
mysql> insert into t (id1, id2, val) values (2, 5, 205);      # (a)
mysql> insert into t (id1, id2, val) values (2, 15, 215);     # (b)
mysql> insert into t (id1, id2, val) values (2, 25, 225);     # (c)
mysql> insert into t (id1, id2, val) values (3, 15, 315);     # (d)
mysql> insert into t (id1, id2, val) values (3, 25, 325);     # (e)
mysql> insert into t (id1, id2, val) values (4, 10, 410);     # (f)
答え

(a)と(b)

これもギャップロックについて問う問題。複合インデックスの一部を指定して排他ロックをとった場合は範囲選択であるので、以下のようにギャップロックがかかる。

... 2-10 ... 2-20 [ ... 3-10 ... 3-20 ...]
lndcltlndclt

メモ: あとで以下の問題を追加する

Q.

  • MySQL で Phantom Read が起きうるケース2問
    • スナップショット作成されるタイミングでの読み取り
    • ロック読み取りでは READ COMMITTED 相当の動作をする

https://speakerdeck.com/carta_engineering/geek-camp-academia-2023-nakka?slide=31

https://nippondanji.blogspot.com/2013/12/innodbrepeatable-readlocking-read.html

Q.

インテンションロックの話 (insert 時にかかるロックの話)

Q.

共有ロックと排他ロックの違い

Q.

デッドロックが起きる条件。典型例と応用例の2つ