＼スニダンを開発しているSODA inc.のAdvent Calendar 2023 20日目の記事です!!!／
はじめに
リレーショナルデータベースにおけるトランザクションを利用したシステム開発において、意図しないアノマリーが発生しないようにすることはとても重要です。
アノマリー(anomaly)とは？
anomalyの単純な和訳としては「例外」や「異常」という意味。
リレーショナルデータベースの文脈では、直列実行(serializable)ではない実行時に発生する異常状態パターンのことを指す。
また、アノマリーはトランザクション分離レベルと関係があり、どのトランザクション分離レベルで設定されているかによって許容されるアノマリーが変わってきます。実際に普段の業務でMySQLのトランザクション処理を設計する際、これらの情報を調べる機会が多かったためその内容をなるべく網羅的に書いてみました。
では早速、MySQL(InnoDB)におけるトランザクション分離レベルとアノマリー、そしてその関係について見ていきましょう！
トランザクション分離レベル
まず、トランザクション分離レベルとは具体的にはどのようなものでしょうか？
トランザクション分離レベルとは？
トランザクション分離レベルについてMySQLのドキュメントによると下記のように説明されています。
the isolation level is the setting that fine-tunes the balance between performance and reliability, consistency, and reproducibility of results when multiple transactions are making changes and performing queries at the same time.
簡単に訳すと、「複数トランザクションによる変更クエリが同時実行される場合のパフォーマンスと信頼性・一貫性・結果の再現性を微調整する設定」という内容です。
ここでいう「パフォーマンス」と「信頼性・一貫性・結果の再現性」の両者には言うまでもなくトレードオフの関係があります。分離レベルの水準が高ければ高いほど、処理が直列化されて「信頼性・一貫性・結果の再現性」は向上しますが、一方で並列性が低くなり「パフォーマンス」は低下します。
そのためトランザクション分離レベルはシステム要件に合わせて、そのトレードオフを考慮したうえで設定することが重要です。
ANSI/ISO標準のSQL-92で示された４つの分離レベルを見ていきましょう。
なお、InnoDBでもこれらの分離レベルが提供されています。
READ UNCOMMITED
他のトランザクションのコミット済みでないデータを含めて読み取ります。
トランザクションの並行性が上がることで、他トランザクションのコミットされていない不完全なデータまで読み取ってしまう可能性が高くなりますが、その分性能は向上します。
READ COMMITTED
常に他のトランザクションによるコミット済みのデータのみを読み取ります。
ただしコミット済みのデータは読み取るため、同一トランザクション中で同じデータを読み込むたびに値が変化するというアノマリーが発生する可能性があります。
REPEATABLE READ
InnoDBではデフォルトの分離レベルであり、一般的に使用される分離レベルでもす。
あるトランザクションの実行間、読み取り対象のデータが他のトランザクションによって更新されず、同一トランザクション中において同じデータを何度読み取っても毎回同じ値であることが保証されます。
しかし、他のトランザクションが追加・削除したデータは読み取り対象となるため、Phantom Readが発生する可能性があります。
SERIALIZABLE
複数の同時に動作するトランザクションの実行結果が、時間的重なりなく逐次実行した場合と同じ結果となることが保証されます。最も安全にデータを操作できますが、一方で性能は低下します。
トランザクション分離レベルとアノマリーの関係
トランザクション分離レベルと一般的なアノマリーを比較すると以下のような関係になります。
| 分離レベル | Dirty Read | Fuzzy Read | Phantom Read | Write Skew |
|---|---|---|---|---|
| READ UNCOMITTED | ⭕ | ⭕ | ⭕ | ⭕ |
| READ COMITTED | ❎ | ⭕ | ⭕ | ⭕ |
| RPEATABLE READ | ❎ | ❎ | 🔺 | ⭕ |
| SERIALIZABLE | ❎ | ❎ | ❎ | ❎ |
- ⭕ : 許容される(発生する)
- ❎ : 許容されない(発生しない)
- 🔺 : 許容されるがInnoDBなどでは発生しない
SQL-92で定義された一般的な説明と、InnoDBをはじめとした一般的なDBエンジンの挙動にこのようなギャップがあるため学び始めはすこし分かりづらくなってしまう印象です。
Snapshot Isolationという分離レベルが関わっていますがここでは深く言及しません。参考に文献を記載しています。
上記の説明でconsitent readsとlocking readsが登場しましたが、こららの併用に関しても注意点があります。
少し話がそれてしまいましたが、意図しない挙動を抑制する上で知っておいて損はない内容だと思います。
それでは、表に登場した一般的なアノマリーを具体的に確認していきましょう！
アノマリー
表に登場した一般的なアノマリーを図解してみました。
Dirty Read
コミット済みでない値を読んでしてしまうことで発生するアノマリーです。
一連の処理は以下のような流れになってます。
- T2が値を更新
- T1がT2のコミット前に値を参照
- T2がロールバック
上記の例では、T1がコミット済みでない値を読み取ってしまっています。
そのためT2がロールバックされると、T1は間違った値をもとに処理を進めることになります。
発生しないようにするには?
コミット済みの値を参照すれば解決できそうです。
- 一般的には
READ COMMITED以上の分離レベルを使用すればよい - InnoDBにおいても同様
-
READ COMMITEDではコミット済み最新スナップショットを参照するため -
RPEATABLE READではトランザクションにおける最初のSELECT時点のスナップショットを参照するため -
SERIALIZABLEではLocking Readを行うため(更新時に専有ロックされている)
-
Fuzzy Read
同一行に対して一貫性のない状態を読んでしまう現象を指すアノマリーです。
Non-Repeatable ReadやInconsistent Readとも呼ばれており、一般化するとRead Skewと同類のアノマリーと言えます。Fuzzy Readは同一行に対して一貫性がない読み取りをしてしまう現象を指すのに対して、Read Skewは複数のデータ間で一貫性のない状態を読み取ってしまう現象を指すようです。
以下のような流れになります。
- T1がuserを参照
- その直後にT2がuserのnameを更新
- T1がもう一度userを参照
結果としてT1において、読み取ったデータに対する一貫性がなくなっています。
発生しないようにするには?
読み取りにおいてMVCCかレコードロックが機能すればよさそうです。
- 一般的には
UNREPEATABLE READ以上の分離レベルを使用する - InnoDBにおいても同様
-
RPEATABLE READではトランザクションにおける最初のSELECT時点のスナップショットを参照するため -
SERIALIZABLEではLocking Readを行うため(更新時に専有ロックされている)
-
Phantom Read
あるデータの集合を読んでいる場合に、別トランザクションによる追加や削除操作により、再度集合を読んだ際に不整合が発生してしまうことを指すアノマリーです。
こちらも一般化するとRead Skewと同類の現象です。
一連の処理は以下のような流れになります。
- T1がusers全体を参照
- その後T2がusersにレコードを追加
- T1がもう一度users全体を参照
結果として、T1に1度目の参照時に存在しなかったはずの行(Phantom)を2度目の読み込み時に参照してしまっています。
発生しないようにするには?
MVCCによる参照かレコードロックが機能すればよさそうです。
特にレコードロックとしては、ギャップロックやネクストキーロックをする必要があります。
- 一般的には
SERIALIZABLE以上の分離レベルを使用する - InnoDBにおいては
REPEATABLE READ以上でも発生を防げる-
RPEATABLE READではトランザクションにおける最初のSELECT時点のスナップショットを参照するため -
SERIALIZABLEではlocking readsによるテーブルロックが発生するため
-
Write Skew
複数のトランザクションをコミットした結果が、それらのトランザクションを1つずつあらゆる可能な順序で実行する場合と一貫性がない状態を指す直列化異常に分類されるアノマリーです。
文章では少し分かりづらいので図で見ていきましょう。
一連の処理は以下のような流れになります。
- T1をBEGIN
- T2をBEIGN
- T1はuser_id=1であるレコードののamountの合計を参照
- T1はuser_id=2へその合計を追加
- T2はuser_id=2であるレコードのamountの合計を参照
- T2はuser_id=1へその合計を追加
- T1とT2をCOMMIT
このときトランザクションが仮に下の順序で直列実行されたとします。
T1 BEGIN → T1 COMMIT → T2 BEGIN → T2 COMMIT
この場合、T2がuser_id=2であるレコードのamount合計の参照する際には、T1がINSERTした結果を考慮されて合計は1000になるはずです。一方、上記の例ではそれらの考慮がされていないことに気づくはずです。
発生しないようにするには?
参照があった段階でレコードロックがかかるようにすればよさそうです。
- 一般的には
SERIALIZABLE以上の分離レベルを使用する - InnoDBにおいても同様
-
SERIALIZABLEではlocking readsによるテーブルロックが発生するため
-
その他のアノマリー
読み取り不整合3つと直列化異常の1つのアノマリーを紹介しましたが、少し登場したRead SkewやDirty WriteやLost Updateなどの更新系の不整合も存在します。
紹介しきれていないアノマリーについて気になる方は是非検索して挙動を確認してみてください。
まとめ
今回はMySQL(InnoDB)におけるトランザクション分離レベルと比較しながら、アノマリーを紹介してみました。またその実装における細かい挙動についても簡単に解説してみました。
使用しているDBにおけるトランザクション分離レベルを把握して、発生が考えられるアノマリーやクエリの挙動を把握することは、未然に意図しない挙動を防ぐという点においてとても重要だということがわかっていただけたかと思います。
知っていることによって、システム設計時にDB設計を再検討したり、アプリケーション側で対応したりするといった対策をスムーズにたてられるはずです。
また、リレーショナルデータベースはとても奥が深い分野と思います。
興味が湧いた方は、下記参考文献を載せてますので是非digってみてください！
参考
-
MySQL 8.0 Reference Manual Transaction Isolation Levels - MySQL
やはり公式ドキュメント。
バックエンドでDBやっていると最も見る機会が多いドキュメントです。 -
データ指向アプリケーションデザイン - O’Reilly Japan
7章が分かりやすかったです。 -
A Critique of ANSI SQL Isolation Levels - Microsoft
ANSI/ISO標準のSQL-92規格のトランザクション分離レベルに対する批判論文です。
SQL-92はロックベースの分離レベルであり、それにSnapshot Isolationをもとに分離レベルを再定義しています。MySQLの分離レベル名称として出てこないため本記事では言及しませんでしたが、MVCCの実装などが密接に関係しているので興味があれば見てると面白いかもしれません。 -
いろんなAnomaly - Qiita
様々なアノマリーについて解説されています。
株式会社SODAの開発組織がお届けするZenn Publicationです。 是非Entrance Bookもご覧ください！ → recruit.soda-inc.jp/engineer
Discussion