How two shoppers can both buy the last concert ticket
An isolation level sets how much transactions running at the same time see of each other's work. SQL names four, from loosest to strictest: Read Uncommitted, Read Committed, Repeatable Read and Serializable.
Say one seat is left. Two checkouts each read seats_left = 1, insert a ticket and write seats_left = 0. Together they sell two tickets for one seat. Loose levels allow this; Serializable does not.
Levels differ in which anomalies (wrong results) they rule out. Dirty reads see changes that aren't committed. Non-repeatable reads see a row change between two reads. Phantoms are new rows matching a rerun query.
How databases work · 6 of 92 / 3
Isolation levels
Real-life use case
PostgreSQL and MySQL ship with different default levels
PostgreSQL defaults to Read Committed, where each statement sees only data committed before it began. So a report that sums balances twice in one transaction can get two totals if a transfer commits in between.
MySQL's InnoDB engine defaults to Repeatable Read, where the whole transaction keeps the snapshot from its first read.
Apps that need full safety can run SET TRANSACTION ISOLATION LEVEL SERIALIZABLE. PostgreSQL may then abort a transaction with a serialization failure (error 40001), and the app retries it.
How databases work · 6 of 93 / 3
Isolation levels
How it helps
Stricter levels hide more, at the cost of waits and retries
Under MVCC, the levels differ mostly in when the snapshot (a frozen view of committed rows) is taken. Read Committed takes one per statement. Repeatable Read takes one per transaction, so a row read twice can't change.
Serializable promises the same result as running the transactions one at a time in some order. PostgreSQL tracks what each transaction reads and writes, and aborts one when no serial order could explain the pattern.
The tracking, waits and retries cost throughput. So many apps pick a looser level and accept its known anomalies.
What it’s forStates the guarantee the strictest level, Serializable, makes about overlapping transactions.