🐬

DBトランザクションの分離レベルの概要と起きうる問題をMySQLを動かしながら理解する

nakaty

2024/08/22に公開

 はじめに直近で関わっているプロジェクトで、同時並行で処理される可能性がある決済処理を実装するにあたり、DBのトランザクションの分離レベルやDBのロックについて学ぶ必要がありました。
「トランザクションの分離レベルについて、RDBMSの公式ドキュメントや関連書籍を読んでも難しくてなかなか理解できない...😇」　そんな悩みを抱えていませんか？
私自身そんな悩みを抱える１人でしたが、MySQLを実際に動かして挙動を調査することで、主要な分離レベルと並行処理で起きうる問題やその解決策について理解を深めることができました！
この記事では、上記調査を通じて学んだことを自分なりにまとめてみました📝
同じような悩みを抱えている方の参考になれば幸いです！

 トランザクションについて
 そもそもトランザクションとは？MySQL 8.4 リファレンスマニュアルの説明👇
トランザクションはコミットまたはロールバック可能なアトミックな作業単位です。トランザクションがデータベースに複数の変更を加える場合、トランザクションがコミットされたときにすべての変更が成功するか、トランザクションがロールバックされたときにすべての変更が取り消されます。
うーん...とりあえず「DBへの一連の操作をひとまとまりとして扱う仕組み」と自分なりに理解しました🙄

 ACID特性トランザクションにはACID特性と呼ばれる４つの重要な性質があります。


特性
内容


原子性 (Atomicity)
全てが実行されるか、全く実行されないかのどちらかである。

一貫性 (Consistency)
トランザクションの開始前と終了後でデータベースの状態が一貫している。

独立性 (Isolation)
複数のトランザクションが同時に実行されても、互いに影響を与えない。

持続性 (Durability)
トランザクションが完了した後、その結果が永続的に保存される。

Isolationを完全に担保しようとすると、複数のトランザクション直列処理で１件ずつ処理する必要がありますが、RDBMSではIsolationの制限を緩めて並列処理を可能としています。

Isolationの制限の程度を定義しているのがトランザクションの分離レベルです。

 トランザクション分離レベルの概要と起きうる問題
 トランザクション分離レベルの概要トランザクションの分離レベルは、同時に実行されるトランザクション同士の干渉をどの程度許容するかを決める設定で、主要な分離レベルは下記の４種類となっています。


分離レベル
概要


READ UNCOMMITTED
他のトランザクションがまだコミットしていない変更を読み取ることができる。最も高速でロックがほとんどかからないが、ダーティリード（後で説明します。）が発生する可能性があるため、データの一貫性が保証されない。

READ COMMITTED
他のトランザクションがコミットした変更だけを読み取る。

REPEATABLE READ
トランザクションが開始された時点のデータを読み続ける。自分のトランザクションがコミットするまで、他のトランザクションがコミットした変更が見えない。

SERIALIZABLE
最も厳しい分離レベルで、トランザクションが完全に直列に実行されるかのように動作する。あるトランザクションが実行されている際、他のトランザクションが同時にアクセスすることを防ぐ。

参考

https://dev.mysql.com/doc/refman/8.0/ja/innodb-transaction-isolation-levels.html

 複数のトランザクションが並列実行された際に起きうる問題複数のトランザクションが同時に並列実行された際に発生する可能性がある問題は下記のとおりです。


問題
内容


ダーティリード
自分のトランザクションがまだコミットしていない変更を他のトランザクションが読み取れる。

ファジーリード
同じトランザクション内で同じ行を複数回読み取る際に、異なる結果を得てしまう。

ファントムリード
同じトランザクション内で同じクエリを再度実行する際に、新しい行が追加されてしまう。

ロストアップデート
二つのトランザクションが同じデータに変更を加えてコミットした場合に、一方の変更が失われてしまう。

また、トランザクション分離レベル別で上記問題が発生する・しないをまとめたのが下記の表です。


分離レベル
ダーティリード
ファジーリード
ファントムリード


READ UNCOMMITTED
発生
発生
発生

READ COMMITTED
発生しない
発生
発生

REPEATABLE READ
発生しない
発生しない
発生

SERIALIZABLE
発生しない
発生しない
発生しない

と、ここまでトランザクション分離レベルと起きうる問題について書いてきましたが、文章だけだとよくわからないですよね？？😇
私自身リファレンスや関連書籍を読むだけだとよくわからなかったので、MySQLを動かして挙動を確認してみました！

 MySQLでの挙動確認それでは、これまでみてきた分離レベルと起きうる問題について、実際にMySQLを動かしながら確認していきます。
ストレージエンジンはInnoDB、トランザクション分離レベルはREPEATABLE READ、という設定で、下記の簡単なテーブルを用意し、挙動確認しました。
mysql> select *  from accounts;
+----+-----------+---------+
| id | user_name | balance |
+----+-----------+---------+
|  1 | Bob       |     100 |
+----+-----------+---------+
トランザクション分離レベルがREPEATABLE READだと、ダーティリードとファジーリードは発生せず、ファントムリードとロストアップデートが発生する、という結果になるはずです。
本当にそうなるのか、実際にMySQLを動かして確認していきます。

 ダーティリードこれ以降、トランザクションA = TA、　トランザクションB = TB とします
TA> BEGIN;

TB> BEGIN;

TA> SELECT balance FROM accounts WHERE id =1;
# 結果
+---------+
| balance |
+---------+
|     100 |
+---------+

TB> SELECT balance FROM accounts WHERE id =1;
# 結果
+---------+
| balance |
+---------+
|     100 |
+---------+
TB> UPDATE accounts SET balance = 150 WHERE id = 1;

TA> SELECT balance FROM accounts WHERE id =1;
# 結果（ダーティリードが発生する場合は150になる）
+---------+
| balance |
+---------+
|     100 |
+---------+

TB> ROLLBACK;

TA> SELECT balance FROM accounts WHERE id =1;
# 結果
+---------+
| balance |
+---------+
|     100 |
+---------+
確認結果：MySQL（InnoDB、REPEATABLE READ）でダーティリードは発生しない
REPEATABLE READでは、トランザクションが開始された時点で、そのトランザクションが見えるデータの「スナップショット」（その時点のデータの状態）が作成されます(※)。
トランザクションが実行されている間は、そのときの状態を読み取るので、他のトランザクションが行った未コミットの変更は一切見えなくなります。
これにより、データの一貫性が保たれ、ダーティリードが発生しないようになっています。
※ InnoDBに実装されているMVCC（Multi-Version Concurrency Control）という仕組みを利用。

 ファジーリードTA> BEGIN;

TB> BEGIN;
TB> SELECT balance FROM accounts WHERE id =1;
# 結果
+---------+
| balance |
+---------+
|     100 |
+---------+
TB> UPDATE accounts SET balance = 150 WHERE id = 1;
TB> COMMIT;

TA> SELECT balance FROM accounts WHERE id =1;
# 結果（ファジーリードが発生する場合は150になる）
+---------+
| balance |
+---------+
|     100 |
+---------+
TA> COMMIT;
TA> SELECT balance FROM accounts WHERE id =1;
# 結果（TAコミット後はTBがコミットした変更内容を読み取れる）
+---------+
| balance |
+---------+
|     150 |
+---------+
確認結果：MySQL（InnoDB、REPEATABLE READ）でファジーリードは発生しない
ファジーリードを防ぐことができる理由は、ダーティリードに記載した内容と同じです。

 ファントムリードTA> BEGIN;
TA> SELECT SUM(balance) FROM accounts;
# 結果
+--------------+
| SUM(balance) |
+--------------+
|          100 |
+--------------+

TB> BEGIN;
TB> SELECT SUM(balance) FROM accounts;
# 結果
+--------------+
| SUM(balance) |
+--------------+
|          100 |
+--------------+
TB> INSERT INTO accounts (user_name, balance) VALUES ('Alice', 200);
# 実行後のテーブル
+----+-----------+---------+
| id | user_name | balance |
+----+-----------+---------+
|  1 | Bob       |     100 |
|  2 | Alice     |     200 |
+----+-----------+---------+
TB> SELECT SUM(balance) FROM accounts;
# 結果
+--------------+
| SUM(balance) |
+--------------+
|          300 |
+--------------+
TB> COMMIT;

TA> SELECT SUM(balance) FROM accounts;
# 結果（ファントムリードが発生する場合は300になる）
+--------------+
| SUM(balance) |
+--------------+
|          100 |
+--------------+
TA> COMMIT;
TA> SELECT SUM(balance) FROM accounts;
# 結果（TAコミット後はTBがコミットした行追加を反映した結果を読み取れる）
+--------------+
| SUM(balance) |
+--------------+
|          300 |
+--------------+
確認結果：MySQL（InnoDB、REPEATABLE READ）でファントムリードは発生しない
ANSI標準では、REPEATABLE READでファントムリードが発生することになっていますが、MySQL（InnoDB、REPEATABLE READ）ではファントムリードが発生しないことがわかりました📝
今回の事例でファントムリードを防ぐことができたのはMVCCのおかげですが、MySQL（InnoDB）では、ファントムリードを防ぐために、MVCCと併せてネクストキーロックと呼ばれるアルゴリズムが使用されています。
今回の記事でネクストキーロックについては詳しく触れていませんが、気になった方は下記をご覧ください。
参考

https://dev.mysql.com/doc/refman/8.0/ja/innodb-next-key-locking.html

 ロストアップデート残高が100の口座について、口座残高を10減らす処理と50増やす処理が、同時に並列実行された場合を例に挙動を確認していきます。
2つの処理完了後、残高は140（100-10+50=140）になっているはずです。
TA> BEGIN;
TA> SELECT balance FROM accounts WHERE id =1 INTO @x;
# 結果
+---------+
| balance |
+---------+
|     100 |
+---------+

TB> BEGIN;
TB> SELECT balance FROM accounts WHERE id =1 INTO @x;
# 結果
+---------+
| balance |
+---------+
|     100 |
+---------+
TB> UPDATE accounts SET balance = @x + 50 WHERE id = 1;
TB> SELECT balance FROM accounts WHERE id =1;
# 結果
+---------+
| balance |
+---------+
|     150 |
+---------+
TB> COMMIT;

TA> UPDATE accounts SET balance = @x - 10 WHERE id = 1;
TA> COMMIT;
TA> SELECT balance FROM accounts WHERE id =1;
# 結果（140であるべきところ90になっている）
+---------+
| balance |
+---------+
|      90 |
+---------+
確認結果：MySQL（InnoDB、REPEATABLE READ）でロストアップデートは発生する
MySQL（InnoDB、REPEATABLE READ）において、トランザクションは開始時点のスナップショットを使ってデータを読み取りますが、今回の例のように、2つのトランザクションが同時に同じデータを変更しようとすると、お互いの存在を知らないまま、後からコミットされたトランザクションが前の変更を上書きしてしまう場合があります。
それでは、ロストアップデートを防ぐにはどうすればいいのでしょうか？🤔
この問題を解決するためには、DBロックに関する知識が必要です。

 InnoDBのロックについてロックは、データベースに対して同時に複数の操作が行われる場合、データの一貫性と整合性を確保するために使用される仕組みです。
今回はInnoDBのロックについて確認していきたいと思います。

 ロックの種類
 共有ロック（Sロック、Shared Lock）共有ロックは、複数のトランザクションが同じデータを読み取ることを許可しますが、データの変更は許可しません。つまり、複数のユーザーが同時に同じデータを読み取ることができますが、データの変更はできません。
下記は、2つのトランザクション（TA、TB）が並列実行される場合で、TAが共有ロックを取得する例です。
TA> BEGIN;
TA> SELECT * FROM accounts WHERE id = 1 LOCK IN SHARE MODE;
# 結果（この行の共有ロックを取得）
+----+-----------+---------+
| id | user_name | balance |
+----+-----------+---------+
|  1 | Bob       |     100 |
+----+-----------+---------+

TB> BEGIN;
TB> SELECT balance FROM accounts WHERE id = 1 LOCK IN SHARE MODE;
# 結果　→ 読み取りは実行できる。
+---------+
| balance |
+---------+
|     100 |
+---------+

TB> UPDATE accounts SET balance = 200 WHERE id = 1;
# 結果　→ 変更は実行できない。TAがコミットされるまでブロックされる
TAが共有ロックを取得している行のデータについて、TBは読み取り可能ですが、変更できないことがこの例でわかります。

 排他ロック（Xロック、Exclusive Lock）排他ロックは、データの読み取りと書き込みの両方を一度に1つのトランザクションにしか許可しません。 つまり、データをロックしたトランザクションだけがそのデータを読み取ったり、変更したりすることができます。
下記は、2つのトランザクション（TA、TB）が並列実行される場合で、TAが排他ロックを取得する例です。
TA> BEGIN;
TA> SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
# 結果（この行の排他ロックを取得）
+----+-----------+---------+
| id | user_name | balance |
+----+-----------+---------+
|  1 | Bob       |     100 |
+----+-----------+---------+

TB> BEGIN;
TB> SELECT balance FROM accounts WHERE id = 1 LOCK IN SHARE MODE;
# 結果 → 読み取りが実行できない

TB> UPDATE accounts SET balance = 200 WHERE id = 1;
# 結果 → 変更も実行できない
TAが排他ロックを取得している行のデータについて、TBは読み取りも変更もできないことがこの例でわかります。
参考

https://dev.mysql.com/doc/refman/8.0/ja/innodb-locking.html

 排他ロックでロストアップデートを防ぐ例先ほどの事例で、ロストアップデートを防ぐためには、排他ロックを利用する必要があります。
MySQL（InnoDB、REPEATABLE READ）で挙動確認した結果は下記の通りです。
TA> BEGIN;
TA> SELECT balance FROM accounts WHERE id = 1 FOR UPDATE INTO @x;
# この時点のテーブル
+----+-----------+---------+
| id | user_name | balance |
+----+-----------+---------+
|  1 | Bob       |     100 |
+----+-----------+---------+

TB> BEGIN;
TB> SELECT balance FROM accounts WHERE id =1 FOR UPDATE INTO @x;
# 結果（トランザクションAがコミットされるまでブロックされる）

TA> UPDATE accounts SET balance = @x - 10 WHERE id = 1;
TA> COMMIT;
# この時点のテーブル
+----+-----------+---------+
| id | user_name | balance |
+----+-----------+---------+
|  1 | Bob       |      90 |
+----+-----------+---------+

TB> UPDATE accounts SET balance = @x + 50 WHERE id = 1;
# トランザクションAのコミット後に実行される
TB> COMMIT;
# この時点のテーブル(ロストアップデートが発生しなくなっている)
+----+-----------+---------+
| id | user_name | balance |
+----+-----------+---------+
|  1 | Bob       |     140 |
+----+-----------+---------+
排他ロックを利用したことで、ロストアップデートを防ぎ、期待した通りの結果を得ることができました🙌

 まとめ今回の記事では、MySQLを実際に動かしながら、トランザクションの分離レベルの概要や、それに伴う問題を確認してきました。
以下に、記事を作成する中で重要だと感じたポイントをまとめます。
トランザクションの分離レベルは、ACID特性のI（Isolation）の制限の程度を定義したもの
主要な分離レベルは４つ（READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ、SERIALIZABLE）
並行処理で起きうる主要な問題は4つ（ダーティリード、ファジーリード、ファントムリード、ロストアップデート）
デフォルト設定（InnoDB、REPEATABLE READ）のMySQLにおいて、複数のトランザクションが並列実行される場合、ダーティリード、ファジーリード、ファントムリードは発生しないが、ロストアップデートが発生する可能性がある。
DBロックは、データベースに対して同時に複数の操作が行われる場合におけるデータの一貫性と整合性を確保するために利用される仕組み
InnoDBのロックの種類として、共有ロックと排他ロックがある。
排他ロックを利用することでロストアップデートを防ぐことができる。
トランザクションの分離レベルやロックの概念について、公式リファレンスや書籍で学び始めた時はとても難しいと感じましたが、実際に手を動かして確認することで、少しずつではありますが理解を深めることができたと感じています。
熟練者の方からすると当たり前の内容ばかりだったかもしれませんが、私と同じようにDBについて学び始めた方の参考になれば幸いです！
最後まで読んでいただき、ありがとうございました🙇‍♂️

特性	内容
原子性 (Atomicity)	全てが実行されるか、全く実行されないかのどちらかである。
一貫性 (Consistency)	トランザクションの開始前と終了後でデータベースの状態が一貫している。
独立性 (Isolation)	複数のトランザクションが同時に実行されても、互いに影響を与えない。
持続性 (Durability)	トランザクションが完了した後、その結果が永続的に保存される。

分離レベル	概要
READ UNCOMMITTED	他のトランザクションがまだコミットしていない変更を読み取ることができる。最も高速でロックがほとんどかからないが、ダーティリード（後で説明します。）が発生する可能性があるため、データの一貫性が保証されない。
READ COMMITTED	他のトランザクションがコミットした変更だけを読み取る。
REPEATABLE READ	トランザクションが開始された時点のデータを読み続ける。自分のトランザクションがコミットするまで、他のトランザクションがコミットした変更が見えない。
SERIALIZABLE	最も厳しい分離レベルで、トランザクションが完全に直列に実行されるかのように動作する。あるトランザクションが実行されている際、他のトランザクションが同時にアクセスすることを防ぐ。

問題	内容
ダーティリード	自分のトランザクションがまだコミットしていない変更を他のトランザクションが読み取れる。
ファジーリード	同じトランザクション内で同じ行を複数回読み取る際に、異なる結果を得てしまう。
ファントムリード	同じトランザクション内で同じクエリを再度実行する際に、新しい行が追加されてしまう。
ロストアップデート	二つのトランザクションが同じデータに変更を加えてコミットした場合に、一方の変更が失われてしまう。

分離レベル	ダーティリード	ファジーリード	ファントムリード
READ UNCOMMITTED	発生	発生	発生
READ COMMITTED	発生しない	発生	発生
REPEATABLE READ	発生しない	発生しない	発生
SERIALIZABLE	発生しない	発生しない	発生しない

合同会社春秋テックブログPublication

合同会社春秋のテックブログです！

はじめに

トランザクションについて

そもそもトランザクションとは？

ACID特性

トランザクション分離レベルの概要と起きうる問題

トランザクション分離レベルの概要

複数のトランザクションが並列実行された際に起きうる問題

MySQLでの挙動確認

ダーティリード

ファジーリード

ファントムリード

ロストアップデート

InnoDBのロックについて

ロックの種類

共有ロック（Sロック、Shared Lock）

排他ロック（Xロック、Exclusive Lock）

排他ロックでロストアップデートを防ぐ例

まとめ

Discussion