Isolation levels: where databases get honest

There is a specific kind of bug that takes the longest to find, and it lives at the intersection of two truths. The first is that concurrent transactions can interfere with each other in ways that are hard to reproduce, because they depend on timing. The second is that databases offer a dial for this interference, called the isolation level, and the default is not the strongest setting, because the strongest setting costs performance. The bug is the one where the dial setting and the application's assumptions disagree, and the application finds out in production, at 3am, with a support ticket about money.

Every developer who has touched a relational database has heard the word "isolation", and most have never had to think about it, because the default setting works for simple applications. The problem is that simple applications grow. They add queues, they add background jobs, they add retries, and the concurrency that was theoretical becomes real. The isolation level is where the database stops pretending that concurrent transactions are a non issue and starts describing what it will actually let happen.

The four anomalies

Isolation levels are defined by which anomalies they allow, and the anomalies are worth understanding on their own terms, because they are the failure modes. A dirty read is reading a value that another transaction has written but not committed, and acting on data that may vanish when the writer rolls back. A non repeatable read is reading a value, then reading it again later in the same transaction and getting a different answer, because another transaction committed in between. A phantom read is running the same query twice and getting a different set of rows, because another transaction inserted or deleted rows that match the filter.

Each isolation level is a promise about which of these cannot happen. Read uncommitted allows dirty reads. Read committed prevents dirty reads but allows the rest. Repeatable read prevents non repeatable reads, and phantom reads too in Postgres, thanks to its snapshot implementation. Serializable is the strongest, promising that the outcome is the same as if the transactions ran one after another, with no interleaving at all.

The practical summary, and the one most developers need: Postgres's default is read committed, and read committed is not repeatable read. The sentence "I read this value and it can change during my transaction" is not a bug in your code. It is the default behavior, and it is the thing the application was built on, whether it knows it or not.

Read committed is the default, and it is fine

The reason read committed is the default is that it matches how most applications think. Each statement sees the latest committed data. A query does not need a snapshot that predates another transaction's commit. The cost is that two statements inside one transaction can see different versions of the world, and the application can make decisions based on a world that has already changed.

The classic example is the balance check. A transaction reads the account balance, decides the withdrawal is allowed, and writes the withdrawal. Another transaction does the same thing at the same time, and both read the same balance, because they are in different snapshots. Both write, and the balance goes negative, because neither saw the other's write. This is not a bug in the isolation level. It is a bug in the code, which assumed that a read and a write are atomic when they are not. The fix is to make the read and the write one atomic statement, or to lock the row, or to use a transaction that retries on conflict.

Repeatable read changes the shape of the app

Switching to repeatable read makes the transaction see a stable snapshot. The balance read is the same on the second read, which feels safer. The cost is that the database now refuses to let two transactions update the same row if the second one started after the first read it. The second transaction fails with a serialization error, and the application has to retry it.

This is the moment where the word "retry" enters the application's vocabulary, and it is the moment a lot of teams discover they have no retry logic at all. The application that has never seen a serialization failure, because it was on read committed, suddenly has to handle them, because repeatable read made the conflicts visible instead of silent. The conflicts were always there. Repeatable read just refuses to paper over them.

Serializable is a promise with a price

Serializable isolation promises the strongest guarantee: the transactions behave as if they ran one at a time. Postgres implements this with a clever mechanism that detects dangerous patterns, and the price is that transactions fail with serialization errors more often, and the application must retry. The applications that use serializable successfully are the ones that treat retries as a normal part of the flow, and the ones that fail are the ones that assumed a transaction either succeeds or throws a database error, not "try again".

The reason serializable matters is the subset of operations where the anomalies are unacceptable: financial operations, inventory, seat reservations, anything where the count must be exact and the failure mode is a wrong result, not an error. An error is annoying. A wrong balance is a lawsuit.

The hidden variable: locking

Isolation levels describe what the database lets happen, and locking is the mechanism underneath. The two are easy to confuse, and the confusion causes bugs. A row lock is how the database makes sure two transactions do not overwrite the same row, and it is the reason an UPDATE waits when another transaction holds the row. The isolation level is how much a transaction is allowed to see while that waiting and writing is going on.

The classic lock related bug is the deadlock: transaction A locks row 1 and wants row 2, transaction B locks row 2 and wants row 1, and neither can proceed. The database detects it and aborts one of them, and the application gets an error that looks like a random failure. The fix is usually to lock things in a consistent order, which is a code change, not a database setting.

How to actually diagnose

When a mysterious wrong answer shows up, the first question is not "what is the isolation level". It is "what did this code assume about concurrency". The diagnosis is a conversation between the code and the setting. Read the transaction, and ask what happens if two of them run at once, with their statements interleaved. If the answer is wrong, the code has a concurrency assumption, and the setting determines whether the database protects it or lets it break.

The tools for the diagnosis are the ones that show you the interleavings. A stress test that runs the transaction concurrently and checks invariants after. A careful read of the query plans and the locks they take. An understanding of which statements are atomic in the database, which is a surprisingly short list: an UPDATE with a WHERE is atomic, a read then a write is not.

The honest summary

The isolation level is not a performance knob to set once and forget. It is a contract with the database about what your transactions can assume, and the contract changes what your code has to do. Read committed is the default because it is the right default, and it expects your code to be honest about read then write. Repeatable read expects your code to handle retries. Serializable expects your code to handle retries and conflicts as the normal flow.

The teams that get this right do not memorize the levels. They know the one question: what does my transaction do, what does it assume, and what happens when two of them run at once. The database is honest about the answer. The isolation level is where it tells you.