Isolation Levels and What They Prevent

You're using READ COMMITTED. It's the default isolation level in PostgreSQL, MySQL, Oracle, and SQL Server. Someone chose it before you arrived and never revisited it. You've prevented dirty reads. Congratulations. You're still vulnerable to three other classes of concurrency bug, one of which has corrupted financial systems in production without anyone noticing until the audit.

Isolation levels are one of those topics where the surface area is small: four standard levels, a handful of anomalies. But the details matter enormously. Most applications run at READ COMMITTED and get away with it because the specific anomalies it permits don't happen to trigger in their workload. Most, not all.

Why isolation is hard

Databases are concurrent by design. Many transactions are running at the same time, reading and writing the same rows. The database has to decide what each transaction is allowed to see about what others are doing.

Perfect isolation (every transaction behaves as if it ran alone, serially, on the full database) is simple to define and expensive to implement. It requires either heavy locking (only one transaction can touch a row at a time) or sophisticated conflict detection (let everyone run, detect conflicts at commit time, abort the losers). Both approaches reduce throughput. The more isolation you buy, the more concurrency you give up.

The SQL standard defines four isolation levels as an explicit set of tradeoffs: weaker isolation, higher concurrency; stronger isolation, lower throughput. The question isn't which level is correct in the abstract. It's which anomalies your application can tolerate.

The anomalies, concretely

There are four concurrency anomalies you need to know. Each has a name that sounds academic; each has a concrete scenario where it bites you.

A dirty read is when transaction A writes a row but hasn't committed yet, transaction B reads that row, and A then rolls back. The value B read never legally existed. B made a decision based on a lie. This is what READ UNCOMMITTED permits and READ COMMITTED prevents. Almost every database defaults to READ COMMITTED specifically because dirty reads are obviously catastrophic and cost nearly nothing to prevent.

A non-repeatable read is when transaction A reads a row, transaction B updates and commits that row, and transaction A reads the same row again and gets a different value. Within a single transaction, the same query returned different results. This isn't hypothetical. Any transaction that reads a value, does some computation, and reads it again to verify is exposed to this. READ COMMITTED permits it; REPEATABLE READ prevents it.

A phantom read is when transaction A runs a range query: SELECT * FROM orders WHERE amount > 1000. Gets 47 rows. Transaction B inserts a new order for $2,000 and commits. Transaction A runs the same range query again and gets 48 rows. A "phantom" row appeared. REPEATABLE READ prevents non-repeatable reads on existing rows but doesn't prevent phantoms in the SQL standard. SERIALIZABLE prevents both.

Write skew is different. Two transactions each read a shared condition, each decide independently that it's safe to proceed, and together they violate an invariant that neither of them would have violated alone. It's subtle enough to deserve its own section.

The standard isolation levels

The SQL standard defines four levels. Here's what each prevents:

The asterisk on REPEATABLE READ is important. PostgreSQL's implementation of REPEATABLE READ is actually snapshot isolation, stronger than the SQL standard requires. In Postgres, REPEATABLE READ prevents phantom reads. In MySQL's InnoDB, REPEATABLE READ uses gap locking to prevent phantoms in most cases. The standard permits phantoms at REPEATABLE READ; specific databases may be stricter. Check your database's documentation, not the SQL spec.

READ UNCOMMITTED is almost never used. It's in the standard, most databases support it, and almost no one sets it intentionally. READ COMMITTED is the practical floor for any real application.

Write skew: the subtle one

Write skew doesn't fit neatly into the "one transaction sees another's uncommitted data" framing. Both transactions are reading committed data. Both are writing correctly. The problem is structural.

The canonical example: a hospital requires that at least one doctor is on call at all times. There are currently two doctors on call: Alice and Bob. Both check whether they can go home.

Transaction A (Alice):
  SELECT COUNT(*) FROM on_call WHERE shift = 'tonight'  -- returns 2
  -- "2 doctors, safe to remove myself"
  DELETE FROM on_call WHERE doctor = 'Alice'
  COMMIT

Transaction B (Bob):
  SELECT COUNT(*) FROM on_call WHERE shift = 'tonight'  -- returns 2
  -- "2 doctors, safe to remove myself"
  DELETE FROM on_call WHERE doctor = 'Bob'
  COMMIT

Both transactions read 2 doctors. Both decided it was safe to remove one. Neither wrote to a row the other wrote to. No dirty reads. No non-repeatable reads. No phantoms. And yet the invariant ("at least one doctor on call") is violated. Nobody is on call.

The mechanism: write skew happens when two transactions read an overlapping set of rows, make decisions based on that read, and write to disjoint rows in a way that invalidates the read condition. The database at REPEATABLE READ level sees no conflict because the writes don't overlap. But the logical invariant depended on both reads being durable. They weren't.

Write skew is not theoretical. It surfaces in any system with an invariant like "total balance must stay above zero," "at most N active sessions," or "no double-booking." Banks, scheduling systems, and booking engines are all exposed if they run at READ COMMITTED or REPEATABLE READ without additional precautions. The additional precautions are: SERIALIZABLE isolation, application-level locking (SELECT FOR UPDATE on the relevant rows), or redesigning the schema so the invariant can be expressed as a single-row constraint the database can enforce atomically.

Serializable Snapshot Isolation

The naive implementation of SERIALIZABLE uses strict locking: only one transaction can read or write a row at a time, period. This works but kills concurrency. You've essentially made your database single-threaded for any conflicting access.

PostgreSQL uses a smarter approach called Serializable Snapshot Isolation (SSI), introduced in Postgres 9.1. The idea: let transactions run concurrently using snapshots (each transaction sees the database as it was at the moment the transaction started). Track read/write dependencies between transactions. If the dependency graph contains a cycle (meaning there's a set of transactions whose interleaving could not have been produced by any serial order), it aborts one of them.

In the doctor example, Postgres would detect that A and B both read the same on_call rows and then wrote to on_call. The dependency pattern signals a potential serialization conflict, and one transaction is aborted. The application retries it. Now it runs after the first committed, sees one doctor on call, and correctly refuses to leave.

The performance cost of SSI is low when conflicts are rare, which they usually are in real applications. The overhead is the bookkeeping: a side table of read/write dependencies that the database maintains per transaction and discards on commit. In write-heavy workloads with frequent conflicts, the abort rate rises and throughput drops. In typical applications, SSI at SERIALIZABLE performs within a few percent of READ COMMITTED. The cost of not using it is potential correctness violations.

Pick your anomalies deliberately

Most applications don't audit their isolation level. The default was chosen by whoever installed the ORM, and it's been READ COMMITTED ever since. For many workloads, that's fine. The anomalies READ COMMITTED permits don't arise because the application doesn't have concurrent transactions reading the same rows and modifying based on the result.

But "hasn't happened yet" isn't the same as "can't happen." If your application has any invariant that spans multiple rows (a total that must stay positive, a count that must stay below a limit, a mutual exclusion constraint), it's worth asking whether that invariant is enforced at the database level or only by your application logic. Application logic at READ COMMITTED isn't sufficient. Either use SERIALIZABLE, or be explicit about why your particular access pattern is safe.

And check what your ORM defaults to. Some frameworks set the isolation level per-transaction, some globally, and a few silently upgrade or downgrade it based on whether you're in a read-only or read-write transaction. The isolation level you think you're using and the one the database is actually using are not always the same thing.