MySQLとPostgreSQLのトランザクションまわりをざっくり調べる

MySQLの世界で生き続けてきたので、そろそろPostgreSQLも学ぼうということで、まずはトランザクション周りから。

個人的にはこの2つのRDBMSの違いとして、コマンド以外だと、トランザクション分離レベルとインデックス周りの違いに対するイメージが強かったので、ここから勉強していくことにします。

ちなみに、以下で MySQL として書いていることは、ストレージエンジンとしてデフォルトである、InnoDB での内容を記載しています。

トランザクションとは

トランザクションとは、複数の不可分な処理を1つの単位として扱うものです。

トランザクションの特性

トランザクションを知る上で重要な特性として、ACID特性があります。

  • Atomicity(原子性)
    • ある単位の複数の処理の全てが実行されるか、全く実行されないかのどちらかになる性質
  • Consistency(一貫性)
    • ある処理の開始と終了時点で、特定ルールに基づいた整合性を保つ性質
  • Isolation(独立性)
    • 特定の処理による結果は、その処理が完了するまで他の処理から見ることができない性質
  • Durability(永続性)
    • 処理として確定した結果は恒久的に保存される性質

ACID特性はすべてを逐次処理と同等レベルに満たすことは可能ですが、一方で並行に処理される際にロックによる問題が発生しやすくなったり、一時データが増えたり、並列に実行できないためにパフォーマンスに影響が出たりします。そのため、一般的には、トランザクション間で一定のルールのもとに部分的に影響を与える可能性を残した状態で実行されるようにします。この影響の度合いを、トランザクション分離レベルといいます。

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

トランザクション分離レベルは、標準のSQLによって以下の4つのレベルが規定されています。各RDBMSによって多少の違いがあるのでそちらも記載します。

レベル 内容 MySQLの補足 PostgreSQLの補足
READ UNCOMMITTED コミットされていないデータを別トランザクションが参照する可能性がある アーキテクチャ上、指定しても挙動は READ COMMITTED になる
READ COMMITTED コミット済みのデータのみを参照する デフォルト
REPEATABLE READ トランザクション開始前までにコミット済みのデータのみを参照する デフォルト 9.1からサポート
SERIALIZABLE 並列実行されたトランザクションでも逐次処理と同等の挙動になる 9.0以前はREPEATABLE READを指定してもこの挙動になる

一般的に、RDBMSトランザクション分離レベルのデフォルトは READ COMMITTED が多数を占めています。MySQL のデフォルトが REPEATABLE READ なのは、5.0以前での制限*1によるものです。

トランザクション分離レベルごとに起きる、逐次実行とは異なる挙動があります。

名称 挙動 発生しない分離レベル
Dirty Read Anomaly トランザクションのコミット前データを参照してしまう。ダーティリード。 READ COMMITTED
Fuzzy Read/Non-repeatable Read Anomaly 同一トランザクション内で同じデータを参照した際に、そのトランザクション内で変更していなくても値が変わってしまう。ファジーリード/非再現リード、ノンリピータブルリード REPEATABLE READ
Phantom Read Anomaly 特定の範囲に対して処理をした際に、別トランザクションにより追加・削除のコミットが発生すると、同一トランザクション内でも得られるデータの集合が異なってしまう。ファントムリード SERIALIZABLE(Snapshot Isolation)
Serialization Anomaly トランザクションの処理を直列化した場合の挙動と異なる結果になってしまう。主に各トランザクションでスナップショットを取得して処理をすることによる。直列化異常 SERIALIZABLE

Phantom Read への対応

MySQLPostgreSQLともに REPEATABLE READ レベルの際の Phantom Read に対して対応をしているが、事情が異なります。

MySQL の場合

MySQLの場合、ネクスキーロックによる対応をしています。ネクスキーロックとは、インデックス上で範囲検索時の範囲の1つ次までのレコードまでのロックを取得することで、挿入ができてはいけない範囲にレコードが挿入されることを防ぐようになっています。

図が明示されていて、理解しやすそうな記事へのリンクを貼っておきます。

norikone.hatenablog.com

ちなみに、MySQLでは REPEATABLE READ 時に完全には Phantom Read を防いでいないパターンがあります。

SELECT ... FOR UPDATE (Locking Read)がその例です。とはいえ、これは READ COMMITTED 相当の挙動を想定しているようなものなので仕方ないかもしれません。

PostgreSQL の場合

PostgreSQLの場合、REPEATABLE READ では Phantom Read は発生しないとなっています。ここで利用する方法はネクスキーロックではないとのことです。そのため、MySQLでのロック範囲に注意するなどといったことは起きないようです(どのような方法か明示している文献が見つからなかったので、調べるならコードを読むことになりそうでした)。

このあたりから、PostgreSQL の REPEATABLE READ は正確には Snapshot Isolation*2となっており、Serialization Anomaly のみが発生するとのことです。

Serialization Anomaly によって何が発生するかの例は、 PostgreSQL のドキュメントにあります。ちなみにこれが起こる理由は、後述する MVCC の実装によります。

https://www.postgresql.jp/document/10/html/transaction-iso.html#XACT-SERIALIZABLE

また 9.0以前では SERIALIZABLE が Snapshot Isolation となっていたとのことで、9.1以上にアップデートする際には注意が必要とのことです。

MVCCとはなにか

MultiVersion Concurrency Control の略で、多版型同時実行制御とも呼ばれるものです。

READ COMMITTED や REPEATABLE READ レベルで必要になる機能で、MySQLPostgreSQL ともに仕組みとして導入されています。

MySQL :: MySQL 5.6 リファレンスマニュアル :: 14.2.12 InnoDB マルチバージョン

PostgreSQL 10.5 文書 - 同時実行制御

MVCC では、おもに複数のトランザクションを並行に実行することで、単純なロックをするよりもパフォーマンスを上げることを目的としています。トランザクション間で単にロックをすることで対応するのではなく、同時に並行して処理を行えるように、トランザクション処理の開始時点でのスナップショットを利用するようにすることで解決します。もちろん、その分メモリ等の使用率は上がることにはなりますが、大量のデータに対して同時に走るトランザクション数が多くなければ、気にする必要は多くはないかと思います。

ちなみにスナップショットと言っていますが、必要なデータのコピーをすべて取っているようなイメージではなく、基本的に各トランザクションごとにコミット済みのデータをバージョン管理して、各トランザクションでバージョンを確認することで取得すべきデータを切り替えるようになっています。

このスナップショットによる実行が、結果的に各トランザクションが条件の元にするデータの違いを生み、前述の Serialization Anomaly を生じさせます。

まとめ

トランザクション分離レベルのデフォルト状態、Anomaly の発生や対応の違いがあることがわかりました。

細かいところはもう少し詳細に調べるべきですね。。。