The short answer
These are three problems that can occur when transactions run at the same time without enough isolation.
Dirty read: you read data that another transaction has changed but not yet committed. If that transaction rolls back, you acted on a value that never existed.
Non-repeatable read: you read the same row twice in one transaction and get different values, because another transaction committed a change in between.
Phantom read: you run the same query twice and the second time it returns extra rows, because another transaction inserted rows that match your condition.
The SQL standard defines four isolation levels. Each one prevents more problems, and costs more in speed:
| 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 |
Some databases behave a little differently from this table. For example, repeatable read in some systems also prevents phantoms. The table is the standard's minimum, and you should check the documentation of the database you use.
Which to choose: read committed is the default in many databases and is fine for most applications. Use a higher level only when a calculation depends on the data staying unchanged for the whole transaction, such as a financial report.
Interview shortcut
> Dirty = uncommitted data. Non-repeatable = same row changes. Phantom = new rows appear.