Deep dive
Isolation level defines how your transaction behaves when other transactions read/write the same data. It’s a correctness vs concurrency trade-off.
Common phenomena:
- **Dirty read**: reading uncommitted data.
- **Non-repeatable read**: reading the same row twice yields different values.
- **Phantom read**: re-running a range query returns different sets of rows.
Typical SQL isolation levels (conceptually):
- `READ UNCOMMITTED` (allows dirty reads)
- `READ COMMITTED` (no dirty reads, can have non-repeatable/phantoms)
- `REPEATABLE READ` (stronger, depends on DB/MVCC)
- `SERIALIZABLE` (strongest, but may abort transactions that can’t be serialized)
Practical guidance
- Start with the DB default (often `READ COMMITTED`) and raise isolation only for the few operations that require it.
- With `SERIALIZABLE`, be ready to retry transactions (serialization failures are normal under contention).
Common pitfalls
- Assuming the same isolation behaves identically across databases (implementation differs).
- Long transactions + high isolation = more blocking/abort rates.
- Trying to fix data races only by raising isolation instead of designing proper constraints/locking patterns.