Isolation levels decide how much one transaction can see of another one's in-flight work. Higher levels prevent more odd results but cost more concurrency. The standard has four, ordered from weakest to strongest.
The anomalies
- Dirty read: you read a change another transaction has not committed yet. If it rolls back, you read data that never existed.
- Non-repeatable read: you read a row twice in one transaction and get different values, because someone committed an update in between.
- Phantom read: you run the same range query twice and the second time new rows appear, because someone inserted rows that match.
Which level prevents what
Level dirty read non-repeatable phantom READ UNCOMMITTED possible possible possible READ COMMITTED prevented possible possible REPEATABLE READ prevented prevented possible* SERIALIZABLE prevented prevented prevented
*The SQL standard allows phantoms at REPEATABLE READ, but some engines are stricter. Postgres's REPEATABLE READ uses a snapshot, so it does not show phantoms either.
How engines do it: MVCC
Most modern databases use multi-version concurrency control. Writers create a new version of a row instead of overwriting it, and each transaction reads from a snapshot of committed versions. Readers do not block writers and writers do not block readers.
Defaults to remember: Postgres and SQL Server default to READ COMMITTED. MySQL's InnoDB defaults to REPEATABLE READ. Snowflake supports READ COMMITTED only.
Where this shows up in a pipeline
A reporting query runs for 10 minutes while a load is committing batches. Under READ COMMITTED, each statement sees the data committed when that statement started, but two separate statements in the same session can see different data. If you run two queries to build one report and they must agree, either run both inside one snapshot-level transaction, or query a table that is swapped in atomically. Higher isolation is rarely the first answer in analytics. Clean batch boundaries usually are.