ダーティリード、ノンリピータブルリード、ファントムリードを起こして挙動を確認してみた

はじめに

こんにちは、SHIFT の開発部門に所属しているmurasawaです。
今期より中途で入社、バックエンド関連の開発を担当しています。

現在、研修でデータベースやRestAPIについて基本的な事から学んでいます。学んだことをアウトプットし理解を深めていくとともに技術の共有として役に立てば幸いです。

今回はトランザクション分離レベルによっては起こりえる ダーティリード、ノンリピータブルリード、ファントムリードを起こして見たいと思います。

使用環境 mysql:5.7.35

コンソール上で操作し、現象をおこします。

今回使用するテーブル、データは以下になります。

mysql> desc users;
+-------+----------------------+------+-----+---------+----------------+
| Field | Type                 | Null | Key | Default | Extra          |
+-------+----------------------+------+-----+---------+----------------+
| id    | int(10) unsigned     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(255)         | YES  |     | NULL    |                |
| score | smallint(5) unsigned | YES  |     | NULL    |                |
+-------+----------------------+------+-----+---------+----------------+
3 rows in set (0.03 sec)

mysql> select * from users; 
+----+------+-------+
| id | name | score |
+----+------+-------+
|  1 | sato |  1000 |
|  2 | kato |   700 |
+----+------+-------+
2 rows in set (0.02 sec)

ダーティリード

あるトランザクションAがコミットされていない状態でも、別のトランザクションBなどから変更後の値を参照できてしまう現象。

準備

トランザクション分離レベルによってはダーティーリードなどの現象は起こらないように制御されているため、変更する必要があります。

トランザクション分離レベルには
「READ UNCOMMITTED」, 「READ COMMITTED」, 「REPEATABLE READ」, 「SERIALIZABLE」があります。
デフォルトは「REPEATABLE READ」となっています。

分離レベルによって発生し得る問題は以下のようになっています。

READ UNCOMMITTED→「ダーティリード、　ファジーリード、　ファントムリード」
READ COMMITTED→ 「ファジーリード、　ファントムリード」
REPEATABLE READ→「ファントムリード」
SERIALIZABLE→発生しない

ダーティリード、ノンリピータブルリード、ファントムリードを起こすためにトランザクション分離レベルをREAD UNCOMMITTEDに変更します。

コンソールを2つ開き、両方で以下のコマンドを実行してください。

現在のトランザクションレベルを確認

mysql> SELECT @@GLOBAL.tx_isolation, @@tx_isolation;
+-----------------------+-----------------+
| @@GLOBAL.tx_isolation | @@tx_isolation  |
+-----------------------+-----------------+
| REPEATABLE-READ       | REPEATABLE-READ |
+-----------------------+-----------------+
1 row in set (0.01 sec)

READ UNCOMMITTEDに変更

// この変更方法はコンソールを開きなおすとREPEATABLE-READに変更されます。
// 一時的な方法として活用ください。
mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @@GLOBAL.tx_isolation, @@tx_isolation;
+-----------------------+------------------+
| @@GLOBAL.tx_isolation | @@tx_isolation   |
+-----------------------+------------------+
| REPEATABLE-READ       | READ-UNCOMMITTED |
+-----------------------+------------------+
1 row in set (0.02 sec)

mysql> 

まず一つ目のコンソールをトランザクションAとしてusersテーブルのデータを確認、更新します。

トランザクションA

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

mysql> select * from users;
+----+------+-------+
| id | name | score |
+----+------+-------+
|  1 | sato |  1000 |
|  2 | kato |   700 |
+----+------+-------+
2 rows in set (0.02 sec)

mysql> update users set name = 'suzuki' where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> 

まだコミットはしていません。

次に別の二つ目のコンソールをトランザクションBとしてusersテーブルのデータを確認します。

トランザクションB

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

mysql> select * from users;
+----+--------+-------+
| id | name   | score |
+----+--------+-------+
|  1 | suzuki |  1000 |
|  2 | kato   |   700 |
+----+--------+-------+
2 rows in set (0.02 sec)

mysql> update users set name = 'fujimoto' where id=1;
1205 - Lock wait timeout exceeded; try restarting transaction

mysql>

コミットしていないのに変更後の値を読めてしまっています。

さすがにトランザクションでのデータ更新は受け付けてくれません。

ダーティリード問題点

コミットやロールバック前のデータを別のトランザクションで参照できてしまうため、
最終的にロールバックされた場合など、ほかのトランザクションで誤ったデータと参照してしまうことになります。

ノンリピータブルリード

あるトランザクションAが2回同じデータを読み込む際に、1回目と2回目の読み込みで値が変わる現象

準備

ノンリピータブルリードを起こすためにトランザクション分離レベルをREAD COMMITTEDに変更します。

コンソールを2つ開き、両方で以下のコマンドを実行してください。

READ COMMITTEDに変更

mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @@GLOBAL.tx_isolation, @@tx_isolation;
+-----------------------+----------------+
| @@GLOBAL.tx_isolation | @@tx_isolation |
+-----------------------+----------------+
| REPEATABLE-READ       | READ-COMMITTED |
+-----------------------+----------------+
1 row in set (0.02 sec)

mysql>

実行

トランザクションAでusersテーブルのデータを確認します。

トランザクションA

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

mysql> select * from users;
+----+------+-------+
| id | name | score |
+----+------+-------+
|  1 | sato |  1000 |
|  2 | kato |   700 |
+----+------+-------+
2 rows in set (0.02 sec)

mysql> 

続いてトランザクションBでusersテーブルを更新してコミットします。

トランザクションB

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

mysql> update users set name = 'suzuki' where id=1;
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)

mysql>

もう一度、トランザクションAでusersテーブルのデータを確認します。

トランザクションA

mysql> select * from users; 
+----+--------+-------+
| id | name   | score |
+----+--------+-------+
|  1 | suzuki |  1000 |
|  2 | kato   |   700 |
+----+--------+-------+
2 rows in set (0.02 sec)

mysql>

一回目でsatoだった名前が、suzukiに代わってしまっています。

ノンリピータブルリード問題点

トランザクションAが1回目に取得したデータはsatoだったため、それを前提として処理を進めていきます。

ほかのトランザクションBによってそのsatoのデータがsuzukiに変更され、後続の処理がうまくいかなくなる可能性があります。

ファントムリード

あるトランザクションで2回同じデータを読み込んだ際に、2回目の読み込みで1回目には存在しなかったデータが読み込めてしまう現象

準備

ファントムリードを起こすためにトランザクション分離レベルをREPEATABLE READに変更しますと言いたいところですが、特定のトランザクションの分離レベルでではMVCC(MultiVersion Concurrency Control)という仕組みを用いているため、 一貫性のある読み取り操作が可能になっています。
他のトランザクションが更新したデータを取得する際に、更新前の状態を見ることができるためファントムリードは起きていないように見えます。

ですので今回はトランザクション分離レベルをREAD COMMITTEDのままにしてファントムリードを起こしてみます。

実行

トランザクションAでusersテーブルのデータを確認します。

トランザクションA

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

mysql> select * from users; 
+----+--------+-------+
| id | name   | score |
+----+--------+-------+
|  1 | suzuki |  1000 |
|  2 | kato   |   700 |
+----+--------+-------+
2 rows in set (0.02 sec)

mysql> 

トランザクションBで新しくデータを挿入しコミットします。

トランザクションB

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

mysql> insert into users value(3,"yamamoto",1200);
Query OK, 1 row affected (0.00 sec)

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

mysql> 

再度、トランザクションAでusersテーブルのデータを確認します。

トランザクションA

mysql> select * from users; 
+----+----------+-------+
| id | name     | score |
+----+----------+-------+
|  1 | suzuki   |  1000 |
|  2 | kato     |   700 |
|  3 | yamamoto |  1200 |
+----+----------+-------+
3 rows in set (0.02 sec)

mysql> 

トランザクションAの1回目の読み込みでは存在しなかったデータが二回目で読み込めてしまっています。

ファントムリード問題点

1回目の読み込みでは2件だったデータが2回目のデータでは3件になってしまっている。

もし、1回目と2回目でcountを取るような処理がある場合や、1回目の処理で取得してデータで更新を行い、それを踏まえて２回目の処理を行う場合など不整合が生じてしまう。

トランザクションレベル分離レベルの選択

今回トランザクションの分離レベルごとに起こる可能性のある現象を実際に起こしてみました。

「問題があるのならば何も発生しない「SERIALIZABLE」でよいのではと思ってしまいがちですが、トランザクション分離レベルを上げるとそれだけ速度や並列処理のパフォーマンスが落ちてしまうため、 何でもかんでも「SERIALIZABLE」でよいという結論にならないのが難しいところです。

システムによってはパフォーマンスを重視して分離レベルを下げたり、厳密性を重視してあげたりすることもあると思います。

参考

ダーティリード、リピータブルリード、ファントムリードをちゃんと理解してからトランザクション分離レベルを理解しよう

トランザクション分離レベルについてのまとめ

終わりに

トランザクション分離レベルによって起こりえる現象を実際に起こしてみて、 何が問題なのか、なぜREPEATABLE READが多いのかがよくわかりました。

現状、トランザクション分離レベルを厳密に考えなければいけないような設計はまだ行っていませんが、今後意識して行きたいと思います。

今回はわざと起こしましたが、実作業で遭遇しないようにしっかりとトランザクション分離レベルを意識していきましょう。

このブロガーのほかの記事を読む

「#SatoshiMurasawa」の新着タグ記事一覧｜note ――つくる、つながる、とどける。

---

執筆者プロフィール：Satoshi Murasawa
前社ではRPAツールの技術サポート、開発を1年半経験。 SHIFTでは、バックエンドエンジニアとして入社し、node.jsやmysqlに触れはじめた。
DBに漠然とした興味があり、勉強して部の中でDB関連で役割を持つことができたらよいなと思っています。

セミナーアーカイブ動画「成長プロダクトの必須条件～自動化でつくる攻めの品質保証基盤～」｜ ソフトウェアテストのSHIFT

資料ダウンロード「セキュリティベンダー選定のための比較チェックシート」｜ 株式会社SHIFT

株式会社SHIFT 全国採用 の求人一覧

お問合せはお気軽に
https://service.shiftinc.jp/contact/

SHIFTについて（コーポレートサイト）
https://www.shiftinc.jp/

SHIFTのサービスについて（サービスサイト）
https://service.shiftinc.jp/

SHIFTの導入事例
https://service.shiftinc.jp/case/

お役立ち資料はこちら
https://service.shiftinc.jp/resources/

SHIFTの採用情報はこちら
https://recruit.shiftinc.jp/career/