インフィニットループ 技術ブログ

2024年05月31日 (金)

著者 : nob

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

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

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

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

★ 第1回 トランザクション分離レベル
★ 第2回 ロックモニターの読み方
★ 第3回 ロック読取りも SELECT は止められない
★ 第4回 INSERT を止めるインテンションロック
★ 第5回 WHERE 条件と違うレコードロック
★ 第6回 ギャップロックがあっても更新される
★ 第7回 ネクストキーロックと降順インデックス
★ 第8回 共有ロックで発生しやすいデッドロック
★ 第9回 ロック読み取りが READ COMMITTED になる詳細
★ 第10回 AWS Aurora と MySQL の違い
★ 第11回 PingCAP TiDB と MySQL の違い

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

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

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

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

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

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

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

4つの分離レベル

分離レベルダーティーリードノンリピータブルリードファントムリード
READ UNCOMMITTED発生発生発生
READ COMMITTEDなし発生発生
REPEATABLE READなしなし発生
SERIALIZABLEなしなしなし
トランザクション分離レベルの表

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

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

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 の行でオートコミットの1行 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 = 100v = 100 にした更新が入ってきています。この現象がノンリピータブルリードです。まだトランザクション実行中ですが、値を参照する度に他のトランザクションで終了した新しい値に変化します。

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

mysql> DELETE  FROM t1 WHERE k = 100;

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

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

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

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

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

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

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 |
+-----+------+

このまま2枚目の端末で 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 でならんでいます。1枚目の端末に戻って再度全行 select すると

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

と v は 0, 1 のままであり、他のトランザクションの値ですぐ更新されない、REPEATABLE READ の挙動を確認できます。ここでさらに2枚目の端末で 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 においてはファントムも発生しないのが特徴になります。

まとめ

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

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

ブログ記事検索

このブログについて

このブログは、札幌市・仙台市の「株式会社インフィニットループ」が運営する技術ブログです。 お仕事で使えるITネタを社員たちが発信します!