MySQL/Aurora/TiDBロック入門 – 第１回トランザクション分離レベル

MySQL InnoDB および AWS Aurora や PingCAP TiDB におけるロックの仕組みやトランザクションの動作を全１１回のシリーズで解説します！

最初はベースとして重要な MySQL 8.0 InnoDB 前提でユーザー視点でのロックの仕組みを学び、後半第１０回以降では MySQL 互換 DB として人気の高い AWS Aurora や PingCAP TiDB と MySQL InnoDB との違いについて学びます。

１回目の今回はロック機構と切っても切り離せないトランザクションとその分離レベルについて、実際に挙動を確かめながら解説します。ライブ感のある説明も理解に役立ちますので、解説動画も付けてみました。合わせてご覧ください！

★ 第１回 トランザクション分離レベル
★ 第２回 ロックモニターの読み方
★ 第３回 ロック読取りも SELECT は止められない
★ 第４回 INSERT を止めるインテンションロック
★ 第５回 WHERE 条件と違うロック読取り
★ 第６回 performance_schema でロックを測定
★ 第７回 ギャップロックがあっても更新される
★ 第８回 ネクストキーロックと降順インデックス
★ 第９回 共有ロックで発生しやすいデッドロック
★ 第１０回 ロック読み取りが READ COMMITTED になる詳細
★ 第１１回 AWS Aurora と MySQL の違い
★ 第１２回 PingCAP TiDB と MySQL の違い

トランザクション分離レベルとは

関係演算を基盤にしたリレーショナルデータベース (RDB) へのクエリを簡単に記述するためのクエリ言語として IBM の System R 開発時に考案され、商用製品としては Oracle で初めて実装 (History of SQL) された古い歴史を持つクエリ言語が SQL です。当時ビジネスアプリケーション向けのプログラミング言語として人気だった COBOL を意識して、エンジニア以外の人にも短時間のトレーニングで使えるように英単語のキーワードを多用し、使いやすさを重視したクエリ言語として設計されました。

実用性と容易さを重視した SQL ですが、多くのデータ処理を引き受けるというデータベースの特性から同時並行でのトランザクション処理が必要になりました。更新を伴う並行処理をどう調停するかというのはとても複雑な問題です。複雑な並行トランザクション処理の挙動が製品毎に違うと利用者にとっては学習コストが高くなり不便であるため、米国国家規格協会 (ANSI) によって並行処理の挙動パターンとして標準化したのが ANSI トランザクション分離レベルです。もともとの目的は「どう並行処理させるか」の定義でしたが標準化が難しく、最終的には「発生して欲しくない現象を３つ定義し、そのうちのどれが起きてしまうのか」という方法によって以下の３つの事象と４つのレベルに分類されました。

ダーティリード (Dirty Read) : あるトランザクション中で、他のトランザクションがまだコミットしていない更新を読めてしまうこと

ノンリピータブルリード (Non Repeatable Read) : あるトランザクション中に、あるデータが複数回読まれたが、他のトランザクションからの更新でその値が一貫しなくなる現象です。ファジーリードとも呼ばれます。

ファントムリード (Phantom Read) : あるトランザクション中に、あるデータの範囲が複数回読まれたが、他のトランザクションからの追加や削除で読んだデータの個数自体が一貫していない現象です。

ファントムは「途中で他から変更される」という意味ではノンリピータブルと似ていますが、個数の増減に特化した現象と考えると理解しやすいです。

４つの分離レベル

InnoDB の REPEATABLE READ は特殊で、今後解説するギャップロックやネクストキーロックのおかげでノンリピーターブルリードの他にファントムも防ぐことが出来ていて、この表でいうところの SERIALIZABLE と同等の挙動になっていますが、 MySQL 上の設定としては REPEATABLE READ とされています。

READ UNCOMMITTED は分離が弱すぎ、SERIALIZABLE は強すぎる分離レベルということで、それぞれあまり使われていませんが、READ COMMITTED は 「書き込みが単独なのはしかたないが、リードは並行して多数の処理を行いたい」という一般的なニーズと合致しやすく、多くのデータベースでデフォルトの分離レベルとなっています。加えて REPEATABLE READ は MySQL InnoDB でのデフォルト分離レベルであるため、この２つの分離レベルの挙動を理解しておくことは重要です。

READ COMMITTED でノンリピータブルリードを観測してみる

今回使う t1 テーブルです。k がプライマリキーで 3 行格納されています。

+-----+------+
| k   | v    |
+-----+------+
|   1 |    0 |
|  10 |    0 |
| 100 |    0 |
+-----+------+

最初にグローバル指定でトランザクション分離レベルを READ COMMITTED に変更します

SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;

既存セッションには適用されないため一旦 mysql クライアントを終了し、再接続してから、確認します

mysql> SELECT @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| READ-COMMITTED          |
+-------------------------+

トランザクションを開始して、k = 10 の行で v = v +1 します。

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

mysql> SELECT * FROM t1;
+-----+------+
| k   | v    |
+-----+------+
|   1 |    0 |
|  10 |    0 |
| 100 |    0 |
+-----+------+
3 rows in set (0.00 sec)

mysql> UPDATE t1 SET v = v + 1 WHERE k = 10;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

この状態で開いたままにし別の端末で k = 100 の行でオートコミットの１行 update を実行します

mysql> UPDATE t1 SET v = 100 WHERE k = 100;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

k = 10 で変更したのみの最初の端末に戻って、全行 select すると

mysql> SELECT * FROM t1;
+-----+------+
| k   | v    |
+-----+------+
|   1 |    0 |
|  10 |    1 |
| 100 |  100 |
+-----+------+

k = 100 で v = 100 にした更新が入ってきています。この現象がノンリピータブルリードです。まだトランザクション実行中ですが、値を参照する度に他のトランザクションで終了した新しい値に変化します。

さらに２つ目の端末で k = 100 の行を削除してみます。

mysql> DELETE  FROM t1 WHERE k = 100;

まだトランザクション実行中の最初の端末で再度全行読むと

mysql> SELECT * FROM t1;
+-----+------+
| k   | v    |
+-----+------+
|   1 |    0 |
|  10 |    1 |
+-----+------+

このように、トランザクション中でも他の INSERT や DELETE によって行の数自体が変化するのがファントムリードで、ノンリピータブルリードとファントム両方が発生するのが READ COMMITTED の特徴になります。

MySQL の REPEATABLE READ でファントムが発生しないのを観測する

前述と同じ方法で、今度は分離レベルを REPEATABLE READ に変更して実験してみましょう。

１枚目の端末でトランザクションを開始、全行を読むと以下２行があります。

mysql> SELECT * FROM t1;
+-----+------+
| k   | v    |
+-----+------+
|   1 |    0 |
|  10 |    0 |
+-----+------+

k = 10 の行で v = v + 1 します

mysql> UPDATE t1 SET v = v + 1 WHERE k = 10;

mysql> SELECT * FROM t1;
+-----+------+
| k   | v    |
+-----+------+
|   1 |    0 |
|  10 |    1 |
+-----+------+

このまま２枚目の端末で k = 1 の行で v = v + 1 します

mysql> UPDATE t1 SET v = v + 1 WHERE k = 1;

mysql> SELECT * FROM t1;
+-----+------+
| k   | v    |
+-----+------+
|   1 |    1 |
|  10 |    0 |
+-----+------+

v は上から 1, 0 でならんでいます。１枚目の端末に戻って再度全行 select すると

mysql> SELECT * FROM t1;
+-----+------+
| k   | v    |
+-----+------+
|   1 |    0 |
|  10 |    1 |
+-----+------+

と v は 0, 1 のままであり、他のトランザクションの値ですぐ更新されない、REPEATABLE READ の挙動を確認できます。ここでさらに２枚目の端末で k = 100 の行に挿入すると

mysql> INSERT INTO t1 VALUES (100, 0);

mysql> SELECT * FROM t1;
+-----+------+
| k   | v    |
+-----+------+
|   1 |    1 |
|  10 |    0 |
| 100 |    0 |
+-----+------+

で v は 1,0,0 になり、最初の端末では

mysql> SELECT * FROM t1;
+-----+------+
| k   | v    |
+-----+------+
|   1 |    0 |
|  10 |    1 |
+-----+------+

のままであり、追加された行を即時に観測することはありません。

このように MySQL の REPEATABLE READ においてはファントムも発生しないのが特徴になります。

まとめ

今回はロック入門の第１回ということで、トランザクションの基本となる分離レベルと、READ COMMITTED と REPEATABLE READ の挙動の違い、MySQL におけるファントムリードが発生しない REPEATABLE READ について解説しました。

次回第２回は本シリーズの重要なトピックの一つともいえる InnoDB のロックモニターの読み方、使い方について解説いたします。ご期待ください！