不整合が起きてはならない場合、トランザクションはシリアライザブル

リレーショナルデータベースが優れている点はトランザクションをサポートしている点です。トランザクションは手続きが一貫性ある形で実行されることを保証してくれます。しかし、トランザクションを使えばOK、という物ではありません。

もしトランザクションさえ使っていればOKと思っていた方はトランザクション分離レベルを理解してください。

トランザクションアイソレーション（分離）レベル

トランザクションには種類があります。

トランザクション分離（アイソレーション）レベル (Wikipedia)

ANSI/ISO SQL標準で定められている分離レベルは、下記の4種類で定義されている。

• SERIALIZABLE ( 直列化可能 )

複数の並行に動作するトランザクションそれぞれの結果が、いかなる場合でも、それらのトランザクションを時間的重なりなく逐次実行した場合と同じ結果となる.このような性質を直列化可能性（Serializability）と呼ぶ.SERIALIZABLEは最も強い分離レベルであり、最も安全にデータを操作できるが、相対的に性能は低い。ただし同じ結果とされる逐次実行の順はトランザクション処理のレベルでは保証されない。

• REPEATABLE READ ( 読み取り対象のデータを常に読み取る )

ひとつのトランザクションが実行中の間、読み取り対象のデータが途中で他のトランザクションによって変更される心配はない。同じトランザクション中では同じデータは何度読み取りしても毎回同じ値を読むことができる。

ただし ファントム・リード(Phantom Read) と呼ばれる現象が発生する可能性がある。ファントム・リードでは、並行して動作する他のトランザクションが追加したり削除したデータが途中で見えてしまうため、処理の結果が変わってしまう。

• READ COMMITTED ( 確定した最新データを常に読み取る )

他のトランザクションによる更新については、常にコミット済みのデータのみを読み取る。 MVCC はREAD COMMITTEDを実現する実装の一つである。

ファントム・リード に加え、非再現リード(Non-Repeatable Read)と呼ばれる、同じトランザクション中でも同じデータを読み込むたびに値が変わってしまう現象が発生する可能性がある。

• READ UNCOMMITTED ( 確定していないデータまで読み取る )

他の処理によって行われている、書きかけのデータまで読み取る。

PHANTOM 、 NON-REPEATABLE READ 、さらに ダーティ・リード(Dirty Read) と呼ばれる現象（不完全なデータや、計算途中のデータを読み取ってしまう動作）が発生する。トランザクションの並行動作によってデータを破壊する可能性は高いが、その分性能は高い。

このようにISO標準で分離レベルは定義されています。

データベースのトランザクション分離レベル

例えば、PostgreSQLは3つトランザクションをサポートし、デフォルトのトランザクション分離レベルはREAD COMMITTEDです。MySQLはサーバー起動時にデフォルトのトランザクション分離レベルが指定可能になっています。14.2.4 Consistent Nonlocking Readsに書かれている通り、MySQLもデフォルトのトランザクション分離レベルはREPEATABLE READです。

• 13.2. トランザクションの分離 (PostgreSQL 9.4マニュアル）
• 13.3.6 SET TRANSACTION Syntax (MySQL 5.6マニュアル）
• 14.2.4 Consistent Nonlocking Reads (MySQL 5.6マニュアル）

PostgreSQLマニュアルのテーブルを見ると違いが分かりやすいです。

リードコミッテドの場合、

ファントム・リード に加え、非再現リード(Non-Repeatable Read)と呼ばれる、同じトランザクション中でも同じデータを読み込むたびに値が変わってしまう現象が発生する可能性がある。

とWikipediaに書いてある通り不整合が発生します。簡単に言うと

• ファントム・リード – タイミング次第で見えなかったデータが見えるよう、見えないようになること
• 非再現リード（ノンリピータブル・リード） – 同じデータの読み込みができなくなること

ファントム・リードやノンリピータブル・リードは複雑なクエリでないと起きない、と思っている方も居るかも知れません。

不整合が起きる例

トランザクションの不整合は単一レコードへのアクセスでも起きてしまいます。例えば、セッションIDのデータベースにデフォルトのリードコミッテド分離レベルを利用した場合、ブラウザから複数の接続、複数のブラウザタブ／クライアントからの複数接続（単一ページでもWebブラウザは複数の接続を利用してWebサーバーに接続してデータを取得する）が在るため、１つのデバイスからのアクセスでも不整合が起こりえます。

具体的には

• 接続Aがトランザクションを開始しセッションデータを読み取る
• 接続Bがトランザクションを開始しセッションデータを読み取る

が同時に起こる可能性があります。Webアプリがアクセス回数をセッションデータに保存している場合、接続A／接続Bの両方が同じアクセス回数のデータにアクセスし、両方が１つカウンタを増加しても、どちらかの結果しか残りません。

つまり、接続A／接続Bがアクセスを開始する前のカウンタが100であったなら、接続A／接続Bの処理が終わった時点で102にならなければならないのですが、接続A／接続Bのアクセスが同時なら101になる、ということです。

トランザクションを使っているのに不整合が起きるなんて！と思うかも知れませんが、同時実効性を上げるにはリードコミッテド分離レベルが向いています。多くのアプリケーションではリードコミッテド分離レベルで十分であることが多い（SELECTでコミット済みデータが読み取れれば良い）からです。

データの不整合を防ぐ

データの不整合を防ぐにはリードコミッテドより高い分離レベルのトランザクション、リピータブルリードまたはシリアライザブルを利用する必要があります。

例えば、

• 接続Aがトランザクションを開始しセッションデータを読み取る
• 接続Bがトランザクションを開始しセッションデータを読み取る

が同時に発生した場合でも、シリアライザブル分離レベルのなら遅く始まった方のトランザクションが早く始まった方のトランザクションが終るまでブロックされ、不整合が発生しません。

よく分からない場合はシリアライザブルを利用すると不整合は発生しません。トランザクションを利用した処理がシリアル化（直列化） されるため、常に整合性が取れた結果になります。同時実効性が犠牲になるだけなので、クリティカルなデータの更新／参照にはシリアライザブルを使うと良いでしょう。勿論、違いを理解してリピータブルリードで十分な場合（トランザクション内で同じ値であれば良い場合）、これを利用した方が同時実効性／パフォーマンスは向上します。

リピータブルリード、シリアライザブルの分離レベルではトランザクションが失敗する可能性があります。アプリケーションはトランザクションのコミットが失敗した場合、再度トランザクションをやり直す、などのコードを用意しておかなければなりません。

参考：

• 13.2. トランザクションの分離 (PostgreSQL 9.4マニュアル）
• 13.3.6 SET TRANSACTION Syntax (MySQL 5.6マニュアル）

例外：SERIALIZABLEでない方が良い場合

原則は「整合性が必要な場合はSERIALIZABLEを使う」ですが、例外もあります。その条件は以下です。

• 一行のみの参照および更新の場合、明示排他ロックの方が良い（場合がある）

この場合、SELECT FOR UPDATEで明示排他ロックで十分です。

以下のスクリプトはPostgreSQLの場合の例です。

<?php
$conn = pg_pconnect('host=localhost user=yohgaki');

$select = 'SELECT count FROM counter FOR UPDATE;';
$update = 'UPDATE counter SET count= $1;';

pg_query($conn, 'BEGIN;');
// READ COMMITTED + FOR UPDATEの場合、次のクエリの実行前にロック解放を待つ
$res = pg_query($conn, $select);
$rows = pg_fetch_all($res);
var_dump($rows);
pg_query_params($conn, $update, [++$rows[0]['count']]);
pg_query($conn, 'COMMIT;');
// １つの行しか使っていないので、結果的に全てのリクエストでカウンタが更新される

これをSERIALIZABLEでやろうとすると、次のようなコードになります。（実用コードとして十分ではなく、エラー時の処理が甘いです）

<?php
$conn = pg_pconnect('host=localhost user=yohgaki');

//$select = 'SELECT count FROM counter FOR UPDATE;';
$select = 'SELECT count FROM counter;';
$update = 'UPDATE counter SET count= $1;';

$retry = 10;
while ($retry--) {
    pg_query($conn, 'BEGIN;');
    $res = pg_query($conn, 'SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;');
    $res = pg_query($conn, $select); // FOR UPDATEで明示ロックしないと、このクエリは実行してから待つ
    $rows = pg_fetch_all($res);
    var_dump($rows);
    $res = pg_query_params($conn, $update, [++$rows[0]['count']]);
    if (pg_transaction_status($conn) === PGSQL_TRANSACTION_INERROR && $retry--) {
        pg_query($conn, 'COMMIT;');
        continue;
    }
    $res = pg_query('COMMIT;');
    break;
}

同時実行によるトランザクション失敗時の再実行を行うために、while ($retry–) で指定回数まで再実行を試みます。

このスクリプトをWebサーバーにインストールし、ab -c 50 -n 10000 http://server/test.php などとして実行すると、1/4程度のカウント漏れが発生します。$retryを100にしても数十カウント漏れを確認できました。更に、同時実行性を制限するFOR UPDATEを付けても、1つ2つのカウント漏れが発生します。（fsync=onだと更に多くなる）

このようにSERIALIZABLE分離レベルを使うと非常に多くのトランザクション再実行をしないと整合性が取れない上、上限を設けるとカウント漏れの可能性を廃除できません。カウンタ型のトランザクションにはSERIALIZABLEなしの方が整合性維持に向いています。

トランザクション実行の詳細は実装依存になります。同じPostgreSQLでも9.1以降とそれ以前では動作が異なります。実際に利用する場合、自分の環境の動作を確認してから利用してください。

まとめ

トランザクションはデータの不整合を防ぐ為の仕組みです。しかし、トランザクション分離レベルには複数のレベルがあり、デフォルトの分離レベル（PostgresSQLの場合はREAD COMMITTED。実装によりデフォルトは異り、データベースが異なると同じ分離レベルでも動作が異なります。）ではデータの整合性を完全に保証することができない場合があります。

トランザクションは「使いさえすれば整合性が保たれる」ものではありません。必要に応じてリピータブルリード／シリアライザブル分離レベルを使用しないと、残高がおかしくなったり、予約でオーバーブッキングしてしまう、といった問題が発生してしまいます。

トランザクション分離レベルの知識はシステム開発者の必須基礎知識です。確実に押さえておきましょう。

PostgreSQLにはRETURNING句という便利な拡張機能があります。これを利用すると挿入／更新直後にSELECTするといったトランザクションをを減らすことができます。

追記

最初、MySQLのデフォルトトランザクション分離レベルを間違えて書いていたので修正しました。序でに自分の記憶のリフレッシュも兼てデフォルトの分離レベルをまとめてみました。細かい動作はマニュアルで確認してください。色々条件／制限があります。

• PostgreSQL： リードコミッテド
• MySQL：リピータブルリード
• SQLite3：シリアライザブル
• Oracle：リードコミッテド
• MS SQL Server：リードコミッテド
• DB2：リードコミッテド（カーソル固定）