同時実行の厳しさを決める - 分離レベル (Isolation Levels)
READ UNCOMMITTED / READ COMMITTED / REPEATABLE READ / SERIALIZABLE の 4 段階と発生する異常現象
概念図
構文
sql
SET TRANSACTION ISOLATION LEVEL { READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE };サンプル
REPEATABLE READ で集計の最中に値がブレないようにする
sql
-- PostgreSQL / MySQL / SQL Server
BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT SUM(balance) FROM accounts;
-- 同じトランザクション内なら、途中で他コミットが入っても同じ結果が返る
SELECT SUM(balance) FROM accounts;
COMMIT;4 つの分離レベル
SQL 標準は並行実行時に起きる異常現象を段階的に防ぐ 4 つのレベルを定義しています。
- READ UNCOMMITTED: 未コミットの変更も読める。最速だが最も危険
- READ COMMITTED: コミット済みのデータしか読まない。多くの RDBMS のデフォルト
- REPEATABLE READ: トランザクション内で同じ行を何度読んでも同じ値
- SERIALIZABLE: 直列実行したのと同じ結果を保証。最も厳格
異常現象と防げるレベル
3 つの典型的な異常現象と、各レベルでの発生有無を一覧にします。
| レベル | ダーティリード | ノンリピータブルリード | ファントムリード |
|---|---|---|---|
| READ UNCOMMITTED | 発生 | 発生 | 発生 |
| READ COMMITTED | 防止 | 発生 | 発生 |
| REPEATABLE READ | 防止 | 防止 | 発生 (※) |
| SERIALIZABLE | 防止 | 防止 | 防止 |
- ダーティリード: 他トランザクションの未コミットデータを読んでしまう
- ノンリピータブルリード: 同じ行を 2 回読んだら値が変わっていた (他が UPDATE/COMMIT)
- ファントムリード: 同じ範囲検索で 2 回目に行数が増減 (他が INSERT/DELETE)
※ MySQL (InnoDB) の REPEATABLE READ はギャップロックによりファントムも防ぐ独自実装。PostgreSQL の REPEATABLE READ もスナップショットでファントムを防ぎます。
RDBMS のデフォルト分離レベル
- PostgreSQL:
READ COMMITTED(RC) - MySQL (InnoDB):
REPEATABLE READ(RR) - Oracle:
READ COMMITTED(RC) — REPEATABLE READ は未サポート (SERIALIZABLE で代替)。SET TRANSACTION READ ONLYも選択可 - SQL Server (オンプレ / IaaS):
READ COMMITTED(RC) —READ_COMMITTED_SNAPSHOTオプションでスナップショット方式 (RCSI) に切替可 - Azure SQL Database / Managed Instance: 既定で
READ_COMMITTED_SNAPSHOT = ON（RCSI が初期値） - SQLite: 既定は deferred トランザクション（最新スナップショット寄り）。WAL モードでは複数読み手と単一書き手が並列化するため、厳密な SERIALIZABLE とは異なる
MySQL から PostgreSQL へ移行するとデフォルトが RR→RC に下がり、集計結果がブレ始めて驚くケースがあります。
sql
-- セッション単位で変更 (PostgreSQL/MySQL/SQL Server 共通)
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- PostgreSQL: トランザクション開始と同時に指定
BEGIN ISOLATION LEVEL REPEATABLE READ;
-- SQL Server: セッション全体
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;SERIALIZABLE の落とし穴
SERIALIZABLE は強力ですが代償があります。
- PostgreSQL: SSI (Serializable Snapshot Isolation) により、競合するとトランザクションの片方が
could not serialize accessエラーで失敗する。アプリ側でリトライ処理が必須 - MySQL: 明示的にロックをかけるため、並行性が大きく落ちてデッドロックも増える
- SQL Server: 範囲ロックが多発し、スループットが下がる
不要に SERIALIZABLE を選ばず、本当に必要な箇所だけトランザクション単位で使うのが安全です。