# Anomalies and Isolation Levels — Database Fundamentals

Source: https://www.skillbyai.com/en/database-fundamentals/t-isolation

> Identify concurrency anomalies and the isolation levels that prevent them.

## Trading safety for concurrency

Weaker isolation lets more transactions run concurrently but allows **anomalies**. A **dirty read** reads another transaction's uncommitted change, which may be rolled back. A **non-repeatable read** reads the same row twice and gets different values because another transaction committed an update in between. A **phantom** occurs when re-running a query returns new rows inserted (or rows deleted) by another committed transaction. A **lost update** happens when two transactions read a value, both compute a new one and the second write overwrites the first. **Write skew** happens when two transactions read overlapping data and update different rows, together breaking a rule (two doctors both going off call). The SQL standard defines **READ UNCOMMITTED**, **READ COMMITTED**, **REPEATABLE READ** and **SERIALIZABLE**, mainly by which of the first three anomalies they forbid. Real systems differ: PostgreSQL's default is READ COMMITTED, its REPEATABLE READ uses **snapshot isolation** (no phantoms, but write skew is possible) and its SERIALIZABLE uses Serializable Snapshot Isolation; MySQL InnoDB defaults to REPEATABLE READ.

## Isolation levels and the anomalies they allow (SQL standard)

Check your database's documentation for its exact behaviour.

```text
level              dirty read   non-repeatable read   phantom
-----------------  -----------  --------------------  ---------
READ UNCOMMITTED   possible     possible              possible
READ COMMITTED     no           possible              possible
REPEATABLE READ    no           no                    possible (standard)
SERIALIZABLE       no           no                    no

lost update fix:  UPDATE stock SET qty = qty - 1 WHERE sku = 'X' AND qty > 0;   -- atomic
             or:  SELECT qty FROM stock WHERE sku = 'X' FOR UPDATE;  ... then UPDATE
```

## Read-modify-write needs care

Reading a value into application code and writing back a computed value is the classic lost-update bug. Use an atomic UPDATE, `SELECT ... FOR UPDATE`, optimistic version columns or SERIALIZABLE with retries.

**Quiz:** Transaction T1 reads a row, T2 updates and commits it, and T1 reads it again getting a different value. Which anomaly is this?

- [ ] Dirty read
- [ ] Phantom read
- [x] Non-repeatable read
- [ ] Deadlock

*Answer:* Non-repeatable read. The same row read twice returns different committed values: a non-repeatable read.