masatora.net

2023-10-21

MySQL(InnoDB)におけるデフォルトのトランザクション分離レベルにおいて起きうる問題

ACID

MySQL

トランザクション分離レベルの概念を整理した上で、MySQLを使ってアプリケーションを実装する際に意識するべき点をまとめます。

トランザクションは、アプリケーションにおいて、複数の読み書き操作を論理的な単位でまとめる方法です。トランザクションは完全に実行される(commit)か、あるいは全く実行されないか(rollback)のどちらかとなります。

ACIDは、トランザクションに求められる4つの要素を指します。各データベースのACIDの実装はかなり異なるらしいです。

原子性(Atomicity): エラーが発生した際にトランザクションを中断し、そのトランザクションのすべての書き込みを破棄できる
一貫性(Consistency): トランザクションの開始時と終了時でデータが一貫性のある状態になっている
分離性(Isolation): 進行中のトランザクションは他のトランザクションの途中の操作に影響されず、独立して実行される
永続性(Durability): トランザクションのコミットが成功した場合、そのトランザクションで書き込まれたデータが失われない(ハードウェア障害やデータベースがクラッシュしたとしても)

ほとんどのデータベースでは、単一のオブジェクトの原子性や分離性を保証します。問題となるのは、複数のトランザクションが同時に一つのデータを変更しようとしたり、一つのトランザクションが他のトランザクションによって変更されたデータを読み取ろうとする時です。

この問題を回避するための理想的な方法は、直列化可能な分離性（Serializable）を採用することです。これは、トランザクションが順番に（直列的に）実行され、その結果が同じになることを保証します。しかし、パフォーマンスの観点から、多くのデータベースでは「弱いレベルの分離性」を採用しています。

この「弱いレベルの分離性」とは、すべての並行性の問題を回避するのではなく、特定の問題に対する保護のみを提供するものです。具体的には、以下のレベルが存在します：

最も弱いトランザクション分離レベルで、他のトランザクションがまだコミットしていない変更を読み取ることが許されます。

このレベルでは、以下の2つを保証します。

一方で、読み取りスキューやファントムリードという問題が生じる可能性があります。

それぞれのトランザクションがデータベースの一貫性のあるスナップショットからのみ読み取りを行います。すなわち、トランザクションが読み取るデータは、そのトランザクションの開始時点でコミット済みのものだけで、トランザクションの実行途中で別のトランザクションによってコミットされたデータは反映されません。

それにより、Read Committedでは生じる読み取りスキューやファントムリードといった問題を回避できます。

デフォルトで、MySQL(InnoDB)はRepeatable Readを採用しています。ただし、MySQL(InnoDB)のRepeatable Readは更新のロストを自動検出しないらしい。

MySQL(InnoDB)のRepeatable Readの実装において起きうる問題は以下です。

アプリケーションが何らかの値をデータベースから読み取り、その値を変更して書き戻す場合に生じることがあります。

UPDATE counters SET value = value + 1 WHERE key = 'foo';

トランザクションが何らかの値を読み取り、その値に基づいて判断を行った上でそれぞれ値を更新する場合に起きえます。判断の結果が、他のトランザクションの書き込みによって真でなくなってしまう場合を指します。

例として、病院の当直管理アプリケーションを想定します。医師は、最低でも一人の同僚が当直に入っていれば(currently_on_call>=2)自分の当直を取り消せます。