Many transactions, one database
A transaction is a group of statements that should behave as one step. "Take 50 from account A and add 50 to account B" must happen completely or not at all.
Alone, a transaction is easy to reason about. The trouble is that databases run hundreds of them at the same time, on the same rows. The perfect behaviour would be as if they ran strictly one after another. That is safe, and slow, because everyone waits.
So databases offer a dial. Turn it down and transactions wait less but can see each other's half-finished work. Turn it up and they are better protected but slower. The settings on that dial are the isolation levels.
Each level is defined by which strange effects, called anomalies, it still allows.
The anomalies
Step through each one. Time runs from top to bottom.
Two transactions touch the same data at the same time. Time runs from top to bottom.
Dirty read
Reading a change that another transaction has made but not committed. If that transaction rolls back, you acted on data that never officially existed.
Two transactions touch the same data at the same time. Time runs from top to bottom.
Non-repeatable read
Reading the same row twice in one transaction and getting different values, because someone else committed a change in between.
Two transactions touch the same data at the same time. Time runs from top to bottom.
Phantom read
Running the same query twice and getting a different set of rows, because someone else inserted or deleted a row that matches your condition.
Two transactions touch the same data at the same time. Time runs from top to bottom.
The difference from the previous one: there, a row you had read changed. Here, no row you read changed. A new one appeared.
Lost update
Two transactions read a value, each calculates a new one, and each writes it back. The second write overwrites the first, as if it never happened.
Two transactions touch the same data at the same time. Time runs from top to bottom.
The four levels
The SQL standard names four levels. Each one rules out one more anomaly than the one before.
| Level | Dirty read | Non-repeatable read | Phantom read |
|---|---|---|---|
| Read uncommitted | Possible | Possible | Possible |
| Read committed | Prevented | Possible | Possible |
| Repeatable read | Prevented | Prevented | Possible |
| Serializable | Prevented | Prevented | Prevented |
- Read uncommitted: you may see other transactions' unfinished work. Rarely used.
- Read committed: each statement sees only committed data, but two statements in the same transaction can see different committed states.
- Repeatable read: rows you have read will not change under you for the rest of the transaction.
- Serializable: the result is the same as if the transactions had run one at a time.
What to do in practice
Know your default level
And therefore which anomalies your code can meet.
Make read-then-write a single step where you can
UPDATE stock SET count = count - 1 WHERE id = 7cannot lose an update. Reading the count into your program and writing it back can.Lock the row when you must read first
SELECT ... FOR UPDATEmakes other writers wait until you are done.Use serializable for the few things that must be exactly right
Money movements, seat bookings. Be ready to retry: at this level the database may cancel a transaction rather than allow a conflict.
Key takeaways
- Isolation levels trade safety between concurrent transactions against speed.
- Dirty read: seeing uncommitted data. Non-repeatable read: a row changes between two reads. Phantom: new rows appear in a repeated query.
- The four standard levels are read uncommitted, read committed, repeatable read and serializable.
- Most databases default to read committed or repeatable read, not serializable.
- Prefer single atomic statements, lock rows you read before writing, and retry when the database cancels a transaction.