トランザクション分離レベル - notee/MySQL GitHub Wiki

4つの分離レベル

READ UNCOMMITED

コミットしていないデータも読み込む。即ちあるトランザクションA中でデータに行った変更が、そのトランザクションAがコミットされないうちから他のトランザクションBに読み込まれる。

この分離レベルを用いた場合、一貫性/Consistencyが保たれていない状態のデータを読みうる。トランザクションAは最終的にロールバックするかもしれず、整合性の取れないデータをトランザクションBが読み、操作する可能性がある。このコミットされていない行の読み込みをダーティリード(詳細は下記)と呼ばれる。

他の分離レベルと比べて特にパフォーマンスに優れているわけではないので実際に使用されるのはまれ。

READ COMMITED

コミット済みのデータのみを読み込む。トランザクションA中であるデータを変更した場合、他のトランザクションBはトランザクションAがコミットされるまでその変更を読み込まず、変更前のデータを読む。ただしトランザクションAがコミットされた後はコミット済みデータを読む。たいていのデータベースシステムはこの分離レベルがデフォルト。

この分離レベルを用いた場合、ダーティリードは起こらない。しかしノンリピータブルリード / ファジーリード(詳細は下記)と呼ばれる現象が発生する。

REPEATABLE READ

MySQLのデフォルト。

コミット済みデータのみを読み込む。あるレコードに対し、トランザクション中で最初に読みこんだデータをsnap shotとして保存し、以降の読み取りは行わない。以降のデータ操作にはすべてsnap shotを元データとして用いる。

この分離レベルではノンリピータブルリードは発生しない。しかしファントムリード(詳細は下記)と呼ばれる現象は防げない。(InnoDBではネクストキーロックを行なうため、この分離レベルでもファントムリードが発生しない。...と言われているが起こせる!lock in share modeなどを使えば)

SERIALIZABLE

すべての読み取りにも排他ロックをかけ、トランザクション中には読み取りを行った行に他のトランザクションが操作を行えないようにする。 InnoDBではすべてのSELECTをSELECT ~ LOCK IN SHARE MODE にしたREPEATABLE READ。

この分離レベルではファントムリードが発生しない。

ただしタイムアウトやロック競合が頻発するので普通は使わない。

複数トランザクション同時実行時に発生しうる問題

ダーティリード

コミットされていない行の読み込みと処理。

例えばゲームで考えると、ユーザーがアイテムを買う場合に

  1. ユーザーテーブルにアイテムを足す

  2. ユーザーテーブルからアイテム代を引く

というトランザクション処理をするとする。ここで、アイテム代が足りなくてこのトランザクションがロールバックされるとき、処理1と2の間で他のトランザクションがこのユーザーのアイテムを確認するとアイテムは存在する。したがってこのアイテムを盗むことも可能。イコール買えていないアイテムを盗めることになる。

ノンリピータブルリード / ファジーリード

トランザクション中で同じ文を2度実行したのにそれぞれ異なる結果が帰ってくる現象が起こりうる。トランザクション中に別トランザクションが同じ行に変更をコミットするために起こる。

例えばゲームで考えると、レベル別ユーザー一覧表示をしたい場合に

  1. ユーザーテーブルからレベル1のプレイヤーをselectする

  2. ユーザーテーブルからレベル2のプレイヤーをselectする

という処理を行なう。ここで、1と2の間にレベル1のプレイヤーがレベルアップをし、ユーザーテーブルのレベルにupdateをかけてcommitしたとすると、ノンリピータブルリードが発生する場合このユーザーは1,2の両方のselectで読み取られてしまい、同時に2人存在することになってしまう。

ファントムリード

存在しない行の読み込み。

トランザクションAが範囲選択での読み込みを行い、その範囲にトランザクションBがinsertしcommitを行うとinsertされた行はトランザクションAが認識していないため、もう一度トランザクションAが同じような範囲選択を行うとこの行が見えてしまう。これは同一トランザクション内で別のデータが読み込まれたことになる。

例えば賞金を登録ユーザーで山分けしたい場合、

  1. 総合賞金額を確認
  2. ユーザー人数を確認
  3. 総合賞金額÷ユーザー人数によって一人あたりの賞金額を出す
  4. 全ユーザーに賞金配布

というトランザクションを行うが、ここで分離レベルがREPEATABLE READ以下だと処理3の最中に新たなユーザーがinsertされた場合、このユーザーは4の全ユーザーへの賞金配布の際にファントムリードで配布対象として読み込まれてしまう。この場合総合賞金額とユーザーへ配布した賞金額の総計の整合性が取れなくなる。