Isolation levels

Isolation levels define which concurrency anomalies a transaction may observe. SQL standard levels trade correctness guarantees for contention and latency.

| Level | Dirty read | Non-repeatable read | Phantom read | |-------|------------|---------------------|--------------| | Read uncommitted | Possible | Possible | Possible | | Read committed | No | Possible | Possible | | Repeatable read | No | No | Possible* | | Serializable | No | No | No |

*PostgreSQL repeatable read also blocks many phantoms via MVCC snapshot; true serializable uses SSI.

					SET TRANSACTION ISOLATION LEVEL READ COMMITTED; -- PostgreSQL default
-- SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; -- strictest, may retry
				

Read committed is the common default: each statement sees committed data as of statement start. Repeatable read keeps a snapshot for the whole transaction — stable reads, but write skew can still occur without serializable or explicit locking. Serializable detects conflicts and may abort with serialization failure — apps must retry.

On interviews: name the anomaly (dirty, non-repeatable, phantom, write skew) and pick the lowest isolation that forbids it.

Common pitfalls: assuming `REPEATABLE READ` prevents all races; ignoring serialization failure retries; running analytics long reads on production OLTP without snapshot/export; default level differs across engines (MySQL RR ≠ PostgreSQL RR).

The trade-off is balancing strict correctness against abort/retry rates and lock contention — do not default to serializable everywhere; match the product's tolerance for stale reads and lost updates.

Checklist:

• Name the anomaly you must prevent.
• State the engine's default and your chosen level.
• Explain retry policy for serialization failures.
• Separate reporting snapshots from OLTP isolation.
• Connect level choice to a concrete user-facing bug.