Database Transactions and Isolation Levels, Explained

A few years ago we shipped a payment reconciliation job that, under load, occasionally credited a merchant twice for the same settlement. Not often. Maybe one run in three hundred. The code looked correct, the tests passed, and the bug only showed up when two workers happened to grab overlapping batches at the same instant. The root cause was not a logic error. It was that nobody on the team could tell you, precisely, what the database guaranteed about two transactions running at once.

That gap is common, and it is expensive. Most engineers can recite "ACID" but freeze if you ask what actually happens when two transactions read and write the same row in the same millisecond. This post is my attempt to explain isolation levels the way I wish someone had explained them to me: with the specific failures they prevent, the ones they don't, and what I'd actually pick in a regulated-money system.

What a transaction really promises

A transaction is a bundle of reads and writes that the database treats as one unit. Either all of it lands or none of it does. That is the atomic part, and it is the part everyone understands. If I debit one account and credit another, I never want to live in a world where the debit stuck and the credit vanished.

The harder promise is isolation: the I in ACID. Isolation is the database's answer to the question "what can this transaction see of other transactions that haven't finished yet?" In a perfect world the answer would be "nothing, they might as well be running one at a time." That perfect world is called serializable, and it is also the slowest. Everything below serializable is a negotiated compromise where you trade some correctness for some throughput, whether you meant to or not.

The critical thing to internalize is that the default isolation level on most databases is not the strict one. If you never set a level explicitly, you inherited a default, and that default allows some anomalies you may not have signed up for. That was exactly our double-credit bug.

The three classic anomalies

The SQL standard defines isolation levels by which of three read anomalies they allow. Learn these by their behavior, not their names. The names are dry; the behaviors are the things that page you at 3am.

• Dirty read: your transaction reads a row another transaction has written but not yet committed. If that other transaction rolls back, you acted on data that never truly existed. In money terms, you approved a transfer against a balance that got reversed a moment later.
• Non-repeatable read: you read a row, someone else commits an update to it, and when you read it again inside the same transaction you get a different value. Your logic assumed the balance was stable for the duration of your work. It wasn't.
• Phantom read: you run a query like "all pending payouts over 10,000," someone inserts a new matching row, and your second run of the same query returns an extra row. The individual rows you read didn't change; the set of rows did.

Isolation levels are essentially a menu of which of these you are willing to tolerate. Read committed forbids dirty reads but permits the other two. Repeatable read forbids the first two. Serializable forbids all three, plus a subtler class of write conflicts that the three-anomaly model doesn't even name.

Read committed and its blind spots

Read committed is the default on PostgreSQL, Oracle, and SQL Server, and it is the level most production systems actually run at. It gives you a reasonable guarantee: you only ever see committed data. No dirty reads. For a huge amount of application code, that is genuinely enough, and I don't want to scare anyone into cranking every service up to serializable out of superstition.

But read committed has a specific, dangerous blind spot: the read-modify-write cycle. Consider the code we all write without thinking.

-- Transaction A and Transaction B both run this concurrently
BEGIN;
SELECT balance FROM accounts WHERE id = 42;   -- both read 100
-- application computes new balance = 100 - 30 = 70
UPDATE accounts SET balance = 70 WHERE id = 42;
COMMIT;
-- Final balance is 70, but TWO withdrawals of 30 happened.
-- The correct answer was 40.

Both transactions read 100, both computed 70, both wrote 70. One withdrawal silently evaporated. Read committed did exactly what it promised and still let you corrupt a balance, because the guarantee is about not seeing uncommitted data, not about your read staying valid until you write. This is the "lost update" problem, and it is the one that bites payment systems hardest.

Repeatable read and snapshot isolation

Repeatable read raises the bar: once you read a row, that value is stable for the rest of your transaction. On PostgreSQL and MySQL's InnoDB, this level is implemented with snapshot isolation, meaning your transaction sees a frozen photograph of the database as of the moment it began. Nobody else's commits leak in.

Here is the part people miss. Under PostgreSQL's snapshot-based repeatable read, that lost-update scenario above does not silently corrupt data. Instead, the second transaction to commit gets an error: "could not serialize access due to concurrent update." The database refuses the write rather than losing it. That is a genuinely good outcome, but only if your application actually catches that error and retries. I have seen far too much code that assumes a COMMIT always succeeds and treats a serialization failure as an unhandled exception that 500s the customer.

The isolation level is only half of the contract. The other half is your retry logic. A stricter level that your code doesn't know how to retry against is often worse than a looser level you understand.

Serializable: the gold standard, at a price

Serializable guarantees that whatever concurrent schedule the database actually ran, the end result is identical to some order of running those transactions one after another. No dirty reads, no non-repeatable reads, no phantoms, no lost updates, no write skew. It is the level where you get to reason about your code as if concurrency didn't exist, which is a wonderful thing to be able to do.

PostgreSQL implements this with Serializable Snapshot Isolation, which is clever: it doesn't lock everything, it tracks read-write dependencies between transactions and aborts one if it detects a dangerous cycle. The cost is not usually raw latency; it is the abort rate. Under contention you will see more serialization failures, and every one of them means a retry. If your transactions are long or touch hot rows, that retry storm can quietly eat your throughput. I have watched a serializable workload spend more time retrying than doing work because someone wrapped a slow external API call inside the transaction. Don't do that.

Write skew, the anomaly nobody warns you about

Here is the failure that convinced me isolation levels are worth teaching properly. Suppose you have a rule: at least one administrator must remain on call. Two admins, Alice and Bob, both currently on call. Each independently decides to go off call. Each transaction reads "how many admins are on call? Two. Fine, more than one, I can leave." Each then updates its own row. Both commit.

Under snapshot isolation (repeatable read), both transactions saw a consistent snapshot, neither modified a row the other touched, so there is no update conflict to detect. Both succeed. Now zero admins are on call, and your invariant is broken even though every individual transaction obeyed the rule. This is write skew, and it slips right past repeatable read because the transactions read overlapping data but wrote to disjoint rows. Only serializable catches it. The fintech version of this is two transactions each verifying an account has enough aggregate credit before drawing down separate sub-limits.

The practical defense, if you can't afford full serializable, is to force a conflict the database can see. Take an explicit lock on the shared thing you're reasoning about, or funnel the invariant through a single row that every relevant transaction updates. Ugly, but it turns an invisible anomaly into a plain old lock contention you can measure.

How I actually decide in practice

I don't set a global isolation level and forget it. I pick per operation, and I bias toward strictness exactly where money integrity lives. My rough rules, earned the hard way:

• Reporting, dashboards, list views, anything read-only where a slightly stale number is harmless: read committed. Cheap and fine.
• Any read-modify-write on a balance, ledger, or limit: either serializable, or read committed with an explicit SELECT ... FOR UPDATE that locks the row before I compute. The lock is often the pragmatic choice because it is easy to reason about and easy to explain in a code review.
• Invariants that span multiple rows (the on-call admin, the aggregate credit limit): serializable, and I accept the retry cost, or I materialize the invariant into one lockable row.

And whatever level I choose, I make the retry explicit. A serialization failure is not an error in the "something is broken" sense; it is the database telling you to try again. Code that treats it as a bug will either crash or, worse, get "fixed" by someone lowering the isolation level to make the error go away, which reintroduces the corruption you were preventing.

public async Task<T> WithRetry<T>(Func<Task<T>> work, int maxAttempts = 3)
{
    for (var attempt = 1; ; attempt++)
    {
        try { return await work(); }
        catch (PostgresException ex) when (ex.SqlState == "40001")
        {
            // 40001 = serialization_failure; retry with backoff
            if (attempt >= maxAttempts) throw;
            await Task.Delay(TimeSpan.FromMilliseconds(50 * attempt));
        }
    }
}

The ORM trap

Most of the isolation bugs I have seen in the wild were not written in SQL. They were written in an ORM by someone who had no idea a transaction was even involved. Entity Framework, Hibernate, and friends will happily open a transaction at read committed, let you load an entity, mutate it in memory over the course of a slow request, and save it back, with a fat window in between where anything can change underneath you. The lost update is baked in and invisible.

The fix is optimistic concurrency: add a version or row-version column, and let the ORM include it in the WHERE clause of the UPDATE. If someone else bumped the version while you were thinking, your update matches zero rows, the ORM throws a concurrency exception, and you retry against fresh data. It is the same idea as serializable's conflict detection, done at the application layer, and it works even at read committed. If you take one concrete action from this post, make it this: put a version column on every table that holds a mutable balance.

Conclusion

The thing I want to leave you with is that isolation levels are not a knob you turn up for "more safety" and down for "more speed." They are a precise contract about which specific concurrency anomalies your code is exposed to, and the only way to use them well is to know which anomaly you are actually afraid of in a given piece of code. Our double-credit bug wasn't fixed by going serializable everywhere. It was fixed by one FOR UPDATE on the settlement row and a retry loop, because we finally understood that the danger was a read-modify-write, not a dirty read. Name the anomaly first. The right level falls out of that, and so does a good night's sleep.