What are database isolation levels and the anomalies they prevent?

The SQL standard defines read uncommitted, read committed, repeatable read and serializable. Real databases implement them differently. Postgres repeatable read uses a snapshot and prevents non-repeatable reads and phantoms, but it can still allow write skew unless you use serializable. MySQL InnoDB defaults to repeatable read with gap locking.

Anomalies to know

• Dirty read: reading another transaction's uncommitted data
• Non-repeatable read: the same row returns different values within one transaction
• Phantom read: a re-run query returns new rows that match the predicate
• Lost update: two transactions overwrite each other's change
• Write skew: two transactions read a shared condition and each writes a different row, violating an invariant

BEGIN ISOLATION LEVEL SERIALIZABLE;

SELECT count(*) FROM on_call
WHERE shift_id = 12 AND active = true;

UPDATE on_call SET active = false
WHERE shift_id = 12 AND employee_id = 7;

COMMIT;

The right level depends on the invariant. Read committed is a common default for simple web workloads. If a business rule spans multiple rows, such as keeping at least one person on call, a stronger level or an explicit lock is needed. Serializable is safest but requires retry logic and can reduce throughput.