分離レベル

同じ問い合わせを 2 回したら、答えが変わった

集計処理の途中で、同じ口座の残高を 2 回読んだとします。

T1: SELECT balance FROM accounts WHERE id = 1;        -- 5000
T2: UPDATE accounts SET balance = 8000 WHERE id = 1;  COMMIT;
T1: SELECT balance FROM accounts WHERE id = 1;        -- 8000

T1 は自分では何も書き換えていないのに、2 回目の答えが違います。合計と内訳が合わない集計表ができあがり、原因を探しても T1 のコードには何も見つかりません。

似た形で、件数が変わることもあります。WHERE status = 'pending' の件数を 2 回数えたら、間に他人が 1 件挿入していて 5 件が 6 件になる。どちらも「読んでいる間に、外の世界が進んでいる」ことから来ています。

どこまで見えてよいかを、こちらが決める

外の世界を完全に止めれば起きません。ただし止めるとは、トランザクションを 1 本ずつ順番に実行することです。同時に 200 リクエストが来るアプリでこれをやれば、待ち行列ができます。そこで DB は「どこまで見えてしまってよいか」を選べるようにしています。これが分離レベルです。

いちばん緩い段では、他人がまだコミットしていない値まで見えます。相手が最後に ROLLBACK すれば、一度も存在しなかった数字を読んだことになります。これを選ぶ理由はほとんどありません。

実務の選択肢は、その 1 つ上からです。「他人が確定させた変更は、途中でも見える」が Read Committed で、PostgreSQL の既定です。冒頭の 5000 が 8000 に化けるのは、この段では仕様どおりの挙動になります。「自分が始めた時点の景色を最後まで固定する」が Repeatable Read で、MySQL の既定です。集計やレポートのように途中で数字が動くと困る処理は、こちらに寄せます。

いちばん厳しい段は、同時に走らせても順番に実行したのと同じ結果になることを保証します。そのぶん、衝突したトランザクションはエラーで打ち切られるので、アプリ側でやり直す前提の作りが必要になります。

上げる前に、ぶつかったことを検出する手がある

分離レベルは接続やトランザクション全体にかかるので、上げると関係のないクエリまで巻き添えで遅くなります。守りたいのが特定の 1 か所だけなら、行に版番号を持たせる手があります。

SELECT version, stock FROM products WHERE id = 10;   -- version=5

UPDATE products
SET stock = stock - 1, version = version + 1
WHERE id = 10 AND version = 5;

更新結果が 0 行なら、読んでから書くまでの間に誰かが先に更新したということです。そのときだけ読み直してやり直します。ぶつからない限り誰も待たないので、Web アプリではよく使われる形です。