DBの「トランザクション分離レベル」が必要な理由　（PostgreSQLで，ファントム・リードを防止すべきサンプル事例）

データベースには，「トランザクション分離レベル」というものがある。

以下では，それが

なぜ必要なのか？

デフォルトのレベルでは，どうして駄目なのか？

PostgreSQLでは，どうやってレベルを変更・確認するのか？

などを取り上げる。

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

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

複数のトランザクションが同時に実行された場合に、他のトランザクションからの影響がどのくらい「分離」するか，のレベル。

ANSI規格では，４つのレベルがある。

READ UNCOMMITTED （一番低い）
READ COMMITTED
REPEATABLE READ
SERIALIZABLE（一番高い）

徹底比較!! PostgreSQL vs MySQL　第３回：トランザクションの比較
http://thinkit.co.jp/free/article/060...

トランザクション処理に詳しくなろう─ 分離レベルとロックの種類 ─
http://itpro.nikkeibp.co.jp/article/C...

4つのレベルを決めているのはANSI規格

SQL Serverのデフォルト分離レベルは「リード・コミッティド」

分離レベルが高ければデータは安全に守られる。が，ロック待ちが多くなり，パフォーマンスが低下する

PostgreSQLでは２つのレベルがある。

（１）リード・コミッティド

「ダーティーリード」が発生しない。

「ファントムリード」・「ファジーリード」が発生する。

（２）シリアライザブル

「ダーティーリード」が発生しない。

「ファントムリード」・「ファジーリード」も発生しない。

デフォルトのレベルは（１）リード・コミッティド。

上記には，「ダーティーリード」など３つの用語が出てくる。

それらの用語の説明：

ダーティ・リード(Dirty Reads) ：

トランザクション内の未コミットの更新内容が，未コミットであるにも関わらず，外部から見えてしまうこと。

ファジーリード(Non-Repeatable Reads) ：

トランザクション内で同一レコードを２回読み取った際，別のコミット済みトランザクション（UPDATEやDELETE）のせいで，該当レコードの内容が変わってしまうこと。（行の中身が変わってしまう）

ファントムリード(Phantom Reads) ：

トランザクション内で同一条件で２回検索した際，別のコミット済みトランザクションのせいで，取得するレコードが変わってしまうこと。（行が増えたり減ったりする）

これら「３つの現象」の説明は，以下のサイトに詳しい。

（テーブル上のデータを，画像で時系列で解説していてわかりやすい）

トランザクションの隔離とは
http://www.interdb.jp/techinfo/postgr...

PostgreSQLとOracle は“READ COMMITTED”と“SERIALIZABLE”のみサポート

DB2は4つの隔離レベルすべてをサポート

残りの２つの隔離レベル“READ UNCOMMITTED”と“REPEATABLE READ”が有用か否かは、Oracleと他の市販DBMS陣営間で論争の種になっている

ここまでのまとめ

トランザクションの分離について：

「３つの現象」を防ぐために，「４つの分離レベル」がある。

PostgreSQLで利用できるのは「２つの分離レベル」であり，デフォルトは「リード・コミッティド」。

トランザクション分離レベルの設定コマンド

以下では実際に，PostgreSQL上で，トランザクションの分離レベルを設定してみる。

表示・確認する方法：

SHOW TRANSACTION ISOLATION LEVEL;
- 現在のトランザクション分離レベルを確認する。

設定・更新する方法：

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
- トランザクション内で，トランザクション分離レベルをセットする。
- トランザクション内（BEGIN;〜COMMIT;/ROLLBACK;の間）でしか実行できない。

SET TRANSACTION文のマニュアル
http://postgresql.jp/document/pg830do...

SHOW文のマニュアル
http://www.commandprompt.com/ppbook/r...

Ruby on Railsで，トランザクション分離レベルを変更して表示するサンプルコード：

class MyModel < ActiveRecord::Base

  def self.hoge
    
    # 現在のトランザクション分離レベルを表示する
    p connection.execute("SHOW TRANSACTION ISOLATION LEVEL;")[0][0]
    
    # トランザクションを開始する（BEGIN）
    transaction do
      # トランザクション分離レベルをシリアライザブルに設定する
      connection.execute("SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;")

      # 現在のトランザクション分離レベルを表示する
      p connection.execute("SHOW TRANSACTION ISOLATION LEVEL;")[0][0]
      
      # トランザクションを終了する（COMMIT）
    end
    
    # 現在のトランザクション分離レベルを表示する
    p connection.execute("SHOW TRANSACTION ISOLATION LEVEL;")[0][0]
  end

end

補足：

[0][0]というのは，PGResultオブジェクト（＝DBからの返却レコード）の，１行１列目，という意味。

実行：

ruby script/console

>MyModel.hoge

実行結果：

"read committed"
"serializable"
"read committed"

トランザクションが終わったら，serializableではなくなっている。

「設定した分離レベルは，トランザクション内でだけ有効」という事がわかる。

分離レベルが低い場合に発生する不具合のサンプル

分離レベルがデフォルト（read comitted）のままだと，恐ろしい不具合が発生する。

例えば，以下のような状況で。

やりたいこと：

スレッド１：予算から100円おろして，全社員で山分けする。
スレッド２：社員を１人追加する。

スレッド１を開始。

↓　　　　　　　　　　スレッド２を開始。

トランザクションを開始。　　　　↓

↓　　　　　　　　　　　　　　　↓

「予算」から100円おろす。　　　↓

↓　　　　　　　　　　　　　　　↓

「社員」の合計人数を数える。↓

↓　　　　　　　　　　　　　　　↓

予算の分配額を決定する。　　↓

↓　　　　　　　トランザクションを開始。

↓　　　　　　　　　　　　　　　↓

↓　　　　　　　　　「社員」を１人追加。

↓　　　　　　　　　　　　　　　↓

↓　　　　　トランザクションをコミット。

↓　　　　　　　　　　　　　　　↓

分配額を全「社員」に支給。　　↓

↓　　　　　　　　　　　　　　　↓

トランザクションをコミット。　　　↓

↓　　　　　　　　　　　　　　　↓

スレッド１を終了。　　　　　　　　↓

スレッド２を終了。

この場合，9番目の「分配額を決定」の処理は，スレッド２で社員が追加されるよりも前に実行される。

例えば，社員が10人だったら，分配額は10円になる。

しかし・・・ファントム・リードのせいで，

16番目の，スレッド１で読み取る「全社員」には，スレッド２で追加された社員も含まれてしまう。

支給対象となる「全社員」の総数が，10人ではなく11人に変わっている。

にも関わらず，「分配額」である「10円」という値はもう決まってしまっているので，

11人に対して10円ずつ支給されることになる。

予算からのマイナスは，100円。

全社員への支給額は，110円。

矛盾が起きる。

それぞれの２つのトランザクションは正常終了しているにも関わらず，矛盾が発生してしまう。

これが，

「ファントム・リードが危険であって，発生してはならない，と言える理由」

また

「デフォルトのトランザクション分離レベルは低いので危険である，と言える理由」

になる。

練習問題

トランザクション分離レベルをSERIALIZABLEに設定して，

前述の「予算を分配する」処理を実行する，

というコードをRailsとかで書いて実行してみてください。

こうすれば矛盾が起きず，

もとからいた10人の社員だけが10円ずつ支給され

スレッド２で追加された社員は，スレッド１からは見えない（ファントム・リードが起きない）

という事が確認できます。

※コーディングの際の注意：

スレッド間でトランザクションを分離するために，冒頭で　ActiveRecord::Base.allow_concurrency = true　とすること。

Rubyの動かないコード　（中級編）　Ruby on Railsで，スレッドごとにトランザクションを分離したい
http://language-and-engineering.hatenablog.jp/entry/20101229/p1

結論

上記では，「分離レベルがSERIALIZABLEであるべきケース」を取り上げた。

が，いつもSERIALIZABLEである必要はない。

むしろ，分離レベルを上げることによって，データ操作の信頼性は上がるものの，

ロックが多くなるので，後続の処理は「待たされがち」になる。

つまり，分離レベルを上げると，パフォーマンスは悪化する。

だから，アプリの特性や処理の特性ごとに，２つ（or ４つ）の分離レベルを使い分けるのが一番良い。

という事になる。

これが，本エントリの主題

『DBの「トランザクション分離レベル」が必要な理由』

に対する回答だ。

補足

なお，通常は，トランザクション分離レベルをトランザクション発行のたびにわざわざ変更するのではなく，行ロック・テーブルロックなどを使って対処すると思われる。

トランザクションと隔離レベルとロック
http://www.fireproject.jp/feature/pos...

SERIALIZABLEはその名のとおり，複数のトランザクションを同時に処理した結果が,逐次で処理した場合の結果と同じであることを保証する隔離レベル

裏を返せば，Repeatable Readのせいで，先行終了しているはずのトランザクションの内容を参照できない，ということにもなる。

それが嫌な場合は，SELECT FOR UPDATEで行ロックをかけたり，LOCK TABLEでテーブルロックしたりする。

また，本エントリに対してフォロー記事を書いて下さった方がいる。

明日のためにその１：トランザクション処理に依存しすぎない
http://d.hatena.ne.jp/masayang/201101...

商売の世界なら「締め時間」があり、それ以前に受け付けたものなら処理の対象になる、というのが普通

要するに，オンライン処理とバッチ処理がぶつかりはしないでしょ，という事