Database Isolation Levels

Isolation levels describe how much one transaction is allowed to observe the intermediate work of another transaction. They exist because databases need two things that pull in opposite directions: concurrency and correctness. Stronger isolation reduces surprising reads and write anomalies, but it also reduces throughput or forces the system to do more bookkeeping.

The anomalies usually discussed are these:

• dirty read: a transaction reads data written by another transaction that has not committed yet
• non-repeatable read: the same row is read twice in one transaction and returns different committed values
• phantom read: the same predicate is evaluated twice and returns a different set of rows because matching rows were inserted, deleted, or updated by another transaction

The four classic SQL isolation levels are ordered from weakest to strongest.

Read Uncommitted

This level allows dirty reads. It gives the database maximum freedom to interleave work, but the application may observe values that later disappear due to rollback. That makes it unsuitable for most transactional systems.

Read Committed

This is a common default. A transaction only sees committed data, so dirty reads are prevented. However, a query later in the same transaction may see a newer committed version of a row than an earlier query saw. Non-repeatable reads and phantoms can still occur.

Repeatable Read

This level guarantees that rows already read by a transaction do not appear to change during that transaction. In many engines this is implemented with a snapshot taken at transaction start, often using multi-version concurrency control, or MVCC. Phantom handling depends on the engine. Some products still need range locks to prevent phantoms fully. Others, such as PostgreSQL, give stronger behaviour than the name alone suggests.

Serializable

This is the strongest level. The outcome must be equivalent to some serial order of transactions, even though the database may execute them concurrently underneath. Databases enforce this with locking, predicate locking, optimistic serialisation checks, or a combination. It gives the clearest semantics, but conflicting transactions may block more often or abort and need retry logic.

The implementation detail that matters most in practice is whether the engine uses MVCC, locks, or both. With MVCC, readers often see a consistent historical version rather than blocking writers directly. That improves read concurrency, but long-running transactions keep old row versions alive and increase storage pressure. With lock-heavy execution, contention is more visible as waits and deadlocks.

Choosing an isolation level is not just a correctness decision. It also shapes latency, failure handling, and operational behaviour. If a transaction spans user input or a slow network call, stronger isolation becomes expensive because snapshots or locks live longer. If the application cannot tolerate double spending, stale inventory counts, or duplicate business actions, weaker isolation may be unacceptable unless the write path adds explicit locking or uniqueness constraints.

The safe habit is to choose the weakest level that still preserves the invariant you care about, then test the real workload against the actual database engine. The names are standard, but the exact behaviour is not identical across products.