Isolation levels and their anomalies
What transactions that run at the same time can see of each other, and how strictly a database can keep them apart.
5 minute lesson
Transactions do not run alone
A busy database runs hundreds of transactions at once. If each could see everything the others are doing, strange things would happen. Three of them have names.
- Dirty readSeeing a change that is not committedand that may still be rolled back
- Non-repeatable readReading a row twice and getting two valuessomeone committed in between
- Phantom readRunning a query twice and getting new rowssomeone inserted in between
Two reads and two answers
- First read
A report transaction reads the balance of an account: 100.
- Reportreads 100
- Someone commits
Another transaction takes 60 from the account and commits. The report is still running.
- Paymentcommits
- Balancenow 40
- Second read
The report reads the same account again, for its total. Now it gets 40. Within one transaction, the same row had two values: a non-repeatable read.
- Reportreads 40
Transaction A raises a price from 10 to 12 and has not committed. Transaction B reads the price as 12 and prints an invoice. Then A rolls back.
What is this called?
- A dirty read
- A phantom read
- A non-repeatable read
- A deadlock
Show the answer
- A dirty read
B acted on a value that was never committed, and after the rollback never existed. The invoice shows a price the database has never held. This is the worst of the three anomalies, and every level from read committed upwards prevents it.
A dirty read sees uncommitted data, which may be rolled back. Read committed already prevents it.
How strict do you want it
The isolation level of a transaction says which of these it is protected from. Stricter levels cost more: more waiting, or transactions that are cancelled and must be tried again.
- Read committedNever sees uncommitted changesthe default in PostgreSQL
- Repeatable readSees one fixed state throughoutno changing rows and no new rows
- SerializableAs if transactions ran one by onemay cancel one, which then retries
A report reads the balances of all accounts in several queries and adds them up. Payments are committed while it runs, and the total keeps coming out wrong.
Which isolation level gives the report one consistent picture?
- Repeatable read
- Read committed
- Read uncommitted
- No level can do that
Show the answer
- Repeatable read
Under read committed every query sees the latest committed state, so money that moves between two of the report's queries is counted twice or not at all. Repeatable read fixes the view at the start of the transaction: all its queries see the database as it was at that moment, whatever is committed meanwhile.
For several queries that must agree with each other, use repeatable read: one fixed view.
What does an isolation level decide, and what does a stricter one cost?
Show the answer
It decides how much a transaction can see of others that run at the same time. Read committed only hides uncommitted changes. Repeatable read gives the transaction one fixed view from start to end. Serializable guarantees a result as if the transactions had run one after another. Stricter levels cost waiting or cancelled transactions that have to be retried.
Recap
- Concurrent transactions can cause dirty reads, non-repeatable reads and phantoms.
- The isolation level chooses which of them a transaction is protected from.
- Read committed is the usual default. Stricter levels cost waiting or retries.