Many sessions use a database at the same time. Their transactions run concurrently: the statements of one run between the statements of another. Without enough isolation, four problems can happen.
In the tables below, time goes down. A and B are two sessions.
Dirty read
A transaction reads a change that another transaction has not committed.
| Time | Session A | Session B |
|---|---|---|
| 1 | BEGIN; Ava - 400 (now 100) | |
| 2 | reads Ava: 100 | |
| 3 | ROLLBACK; (Ava 500 again) |
B used a balance of 100 that never existed in the committed data.
Non-repeatable read
A transaction reads the same row twice and gets two values.
| Time | Session A | Session B |
|---|---|---|
| 1 | reads Ava: 500 | |
| 2 | Ava - 100, COMMIT; | |
| 3 | reads Ava: 400 |
Phantom read
A transaction runs the same query twice, and the second time a new row matches.
| Time | Session A | Session B |
|---|---|---|
| 1 | counts accounts with balance over 100: 2 | |
| 2 | inserts an account with 900, COMMIT; | |
| 3 | counts again: 3 |
The new row is a phantom.
Lost update
Two transactions read the same value, compute a new value, and write it. The second write replaces the first.
| Time | Session A (withdraw 100) | Session B (deposit 50) |
|---|---|---|
| 1 | reads Ava: 500 | |
| 2 | reads Ava: 500 | |
| 3 | writes 500 - 100 = 400 | |
| 4 | writes 500 + 50 = 550 |
The correct balance is 450. The database has 550: the withdrawal is lost.
An UPDATE that computes from the column, SET balance = balance - 100, reads the newest value when it writes. Lesson 8 explains why. An application that reads a value, computes in its own code, and writes a fixed number back can lose updates.