データベースの信頼性と速度を左右する、トランザクション分離レベルについて

DB&サーバ

2018.04.17

こんにちは。sagaraです。先日のIPA情報処理技術者試験、受験者の方はお疲れ様でした。
わたしはDB区分で高度情報試験に初参戦しましたが、総計5時間の試験はなかなかにヘヴィでした…！まだまだデータベースは要勉強なので、今回はトランザクションについてです。

トランザクションのACID特性

信頼できるデータベースにはいくつかの性質があります。
レコードのうち半分だけしか更新されなかったり、更新したはずのデータが実は反映されてなかったり、
知らない間にレコードがなくなっていたり、といったことがあると困るということですね。

単一または複数のクエリを含む一連の処理をトランザクションと呼びますが、このトランザクションに要求される性質にACID特性というものがあります。FE試験やAP試験ではおなじみ。

Atomicity(原子性/不可分性)…全部処理されるか全部されないかのどちらか
Consistency(一貫性)…同じ処理の結果は常に一致する
Isolation(独立性)…他のトランザクションの影響を受けない
Durablity(耐久性/永続性)…障害があっても更新された内容は保持される

起こりうる問題

独立性や一貫性が不足するときに起こりうる問題として、以下のようなものがあります。
DB午前試験で出現します。~~関係ないけど、ファントムリード（幻影読解？）って名前、カッコいい。~~

ダーティーリード（まだコミット処理されていない値を参照し、その後参照元がロールバックされた）
アンリピータブルリード（同じ列値を2回読み込んだら、1回目と2回目が違う値になっている）
ファントムリード（2回目に列をカウントしたら、その間に増えた（減った）レコードもカウントしていた）

トランザクションの分離レベル(隔離性水準)

データベースでは以上のような問題を防ぐための設定が用意されています。
トランザクションの独立性と処理速度はトレードオフになるので、そのバランスをどうするかを、トランザクションの分離レベルで設定できるのですね。

トランザクション分離レベルにはREAD UNCOMMITED, READ COMMITED, REPEATABLE READ, SERIALIZABLEの4つの段階があります。 READ UNCOMMITEDが最も分離レベルが低く、SERIALIZABLEが一番高いです。

分離レベルについては、このサイトの記事に結構わかりやすい例をあげて説明されていました。

Database Isolation Levels And Their Effects On Performance And Scalability

　　　100万行のデータを持つOrdersテーブルがある。
8:00 ユーザAがクエリ'SELECT * FROM Orders'を実行した。
　　　（全行をSELECTするのに約5分くらいかかる。実際は非推奨）
8:01 ユーザBがOrdersテーブルの最後の行を更新してコミットした。
8:04 ユーザAのクエリが、ユーザBの更新した行にたどり着いた。さてどうなる？

更新されたテーブルの最終行で、ユーザAのクエリが、更新前のものを読むか、更新後のものを読むか？いうことですね。答えは「トランザクション分離レベルに依存」しますよ、と言っています。

①READ UNCOMMITEDの場合

ユーザAからは、ユーザBが更新した行が見えます。
他のトランザクションのコミットされていない変更が見えるので、ダーティーリード（後にロールバックされる値を読んでしまう現象）を引き起こす可能性があります。

②READ COMMITEDの場合

ユーザAからは、ユーザBが更新した行は見えません。
ユーザAには、ユーザAのクエリが開始された時点での行が見えています。しかし、トランザクション内で複数のクエリを発行する場合には、アンリピータブルリードが発生する恐れがあります。

READ COMMITEDなトランザクション内で同じ列値を2回更新するようなクエリを発行する場合、 2回目の更新の前後では、異なる列値が読まれる可能性があります。

③REPEATABLE READの場合

ユーザAからは、ユーザBが更新した行は見えません。
ユーザAには、ユーザAのトランザクションが開始された時点での行が見えています。　　

記事には『同一トランザクション内で読込(READ)する場合は常に最初の読込時点での値を用いる』と書いてあります。この最初の読込時点での値をスナップショットと呼び、データベースはスナップショットを保持するための領域を確保します（MySQLではロールバックセグメント、OracleではUNDOセグメントと呼ばれるようです）。そのため、上の二つの分離レベルに比べると処理速度は低下します。

この分離レベルではファントムリードが起こる可能性があります。 Mysql(innoDBエンジン)の初期設定はREPEATABLE READですが、通常はファントムリードが起こらないような設計になっているようです。

ネクストキーロックによるファントム問題の回避 - MySQL

④SERIALIZABLEの場合

トランザクションが完全な独立性を持ちます。トランザクションは直列化可能性が維持される範囲内でのみ、複数のトランザクションが平行に実行されます。上の記事によると、およそ(REPEATABLE READ)と挙動が似ている、と説明があります。上3つに比べて、処理速度は低下します。

OracleではREPEATABLE READをサポートせず、最高分離レベルとしてSERIALIZABLEを利用するようです。 MySQLではREPEATABLE READの場合にSELECT文が使われるところが、自動的にSELECT ... LOCK IN SHARE MODEとなる（テーブルに対して共有ロックがかけられる）ようです。