Architecture: Transactions and Isolation Levels

August 11, 2026 ArchitectureDatabasesConcurrency

Every database you’ve used shipped with a default isolation level, and almost nobody changes it. That default decides which concurrency bugs your application is allowed to have, which makes it one of the more consequential settings you’ve never thought about.

It’s also the one most likely to be wrong in a way your tests never catch. A concurrency bug needs two things to happen at the same time, and a test suite runs one thing at a time.

What a transaction actually promises

A transaction groups statements so the database treats them as one unit. The usual shorthand for what you get is ACID, listed here with the interesting one last:

• Atomicity: all of it happens, or none of it does.
• Consistency: the database’s own rules (constraints, foreign keys) still hold afterwards.
• Durability: once it commits, it survives a crash. That’s what the write-ahead log is for.
• Isolation: concurrent transactions don’t interfere with each other.

Three of those are close to binary. Your database either survives the power cut or it doesn’t. Isolation is the odd one out: it’s a dial with four settings, you’re on one of them right now, and it’s almost certainly not the strict one.

The things that go wrong

All five of these are bugs real applications ship with. The isolation levels are a menu of which ones you’ll tolerate.

• Dirty read: you read a row another transaction has written but not committed. If it rolls back, you acted on data that never existed. PostgreSQL can’t even express this one, which is why it gets less airtime than the rest, though MySQL will happily give it to you if you ask.
• Non-repeatable read: you read the same row twice in one transaction and get two different answers.
• Phantom read: you run the same query twice and get a different set of rows, because somebody inserted or deleted one that matches your WHERE clause.
• Lost update: two transactions read a row, each works out a new value from what it read, and both write. The second write silently discards the first.
• Write skew: the awkward one. Two transactions read an overlapping set of rows, each checks a rule that spans them, then each writes a different row. Both are individually correct. Together they break the rule.

The SQL-92 standard defines its isolation levels in terms of the first three only:

Notice what’s missing. Lost update and write skew don’t appear in that table at all, and they’re the two that actually cost people money. In 1995 Berenson, Bernstein, Gray, Melton and the O’Neils published A Critique of ANSI SQL Isolation Levels, which showed the standard’s definitions are ambiguous and don’t describe what real databases do. That paper is also where snapshot isolation got its name, because the standard had no room for the thing several vendors had already shipped.

Which is why “repeatable read” means one thing in PostgreSQL and something meaningfully different in MySQL. Both are compliant. The standard just isn’t saying much.

Seeing them happen

These are much easier to see than to describe. Here are four of them as a pair of transactions you can step through, at whichever level you like. Watch the second read in each scenario, and watch which commit fails.

What your database actually does

The levels are a standard. The defaults are not, and neither is what a given name buys you.

MySQL’s default is the one whose name promises most and delivers least. Jepsen’s 2023 analysis of MySQL 8.0.34 found its repeatable read allows lost updates and write skew, and that a transaction can observe two different values for the same row, which is the one thing the level’s name explicitly rules out. Their conclusion: “It isn’t clear what MySQL Repeatable Read actually is.” If you’re on MySQL, don’t reason from the name.

Your ORM almost certainly never sets this either. It opens a transaction and takes whatever the server hands it, so the isolation level of your application is really a property of whichever database you happen to be pointed at. Checking takes about a line:

SHOW default_transaction_isolation;   -- postgres. mysql: SELECT @@transaction_isolation

BEGIN ISOLATION LEVEL SERIALIZABLE;   -- just this transaction
SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL SERIALIZABLE;
ALTER DATABASE mydb SET default_transaction_isolation = 'serializable';

Per transaction is the one you want nearly always. Worth being careful with the last of those: it changes the behaviour of every query anybody has ever written against that database, including the ones nobody has looked at in two years.

Snapshot isolation, and the thing it doesn’t fix

Snapshot isolation is what most people mean when they say repeatable read today. Every transaction reads from a consistent snapshot taken when it began, so readers never block writers and writers never block readers. It’s a good design, and it’s why the middle of that table is so crowded.

It gives you one more thing for free: first updater wins. If two transactions write the same row, the one that commits second is rolled back instead of clobbering a version it never saw. That’s the lost update scenario in the widget, and it’s why lost updates stop being a problem on anything whose repeatable read is really snapshot isolation. MySQL’s isn’t, and has no such rule. That’s how Jepsen found lost updates sitting in its default level.

What it doesn’t do is look at what you read. Write skew falls out of that gap, and it isn’t exotic:

• Two doctors both go off call at once, because each transaction checked that two were on call.
• Two people book the same meeting room, because each checked for overlapping bookings and found none.
• A customer goes overdrawn across two accounts, because both withdrawals checked the combined balance against the same snapshot and each debited a different account.

No two transactions write the same row in any of these, so there’s no conflict for the database to find. The rule that got broken was never written down anywhere it could enforce it.

💡 Every serialization failure, whatever caused it, arrives as SQLSTATE 40001. If you handle exactly one database error code in your application, handle that one.

Serializable, and what it costs

Serializable is the level where the database promises the outcome will match some order of running your transactions one at a time. It’s also the level people are most superstitious about, mostly for reasons that stopped being true a decade ago.

The old implementation was locking: hold read locks until commit, and take range locks so nobody can insert into a range you scanned. That serializes throughput along with the transactions, and it’s still roughly what SQL Server does at SERIALIZABLE, and what MySQL does by rewriting every plain SELECT into SELECT ... FOR SHARE.

PostgreSQL has done something better since 9.1, called serializable snapshot isolation. It runs snapshot isolation as normal, then watches the read-write dependencies between concurrent transactions and aborts one when the pattern it sees couldn’t have come from any serial order. The predicate locks it takes for this show up as SIReadLock in pg_locks, and they never block anybody. From the docs:

This monitoring does not introduce any blocking beyond that present in repeatable read, but there is some overhead to the monitoring, and detection of the conditions which could cause a serialization anomaly will trigger a serialization failure.

The cost is real, it’s just not the cost people expect. You don’t pay in blocking. You pay in aborted transactions, and in false positives, because SSI is conservative enough that it will sometimes kill a transaction that would have been fine. The PostgreSQL docs list the mitigations, and they’re the practical ones:

• Declare read-only transactions READ ONLY, so they can be excluded from most of the tracking.
• Keep transactions small and short. The longer one is open, the more it can conflict with.
• Encourage index scans over sequential scans, which always need a relation-level predicate lock.
• Don’t leave connections idle inside a transaction, and set idle_in_transaction_session_timeout so you find out when you do.

For an OLTP workload with short transactions and low contention, serializable everywhere is defensible, and it buys a real simplification: if each transaction is correct on its own, any mix of them is correct too. On a workload with a few very hot rows you’ll spend your time in the retry loop instead. I’ve not run a whole production system this way, so benchmark it before you believe me.

The fixes that aren’t “raise the isolation level”

Reaching for serializable is one answer. Often it isn’t the one you want, because the anomaly is localized to three lines of code and the level is global.

Do the arithmetic in the database. The lost update in the widget only exists because the new balance got computed in application code from a stale read. This version is safe at every isolation level, because read committed re-reads the row it’s about to update and re-evaluates the WHERE clause against the new version:

-- loses writes: the value was decided in your application
SELECT balance FROM accounts WHERE id = 1;   -- 100
UPDATE accounts SET balance = 90 WHERE id = 1;

-- safe: the value is decided by the row itself, at write time
UPDATE accounts SET balance = balance - 10 WHERE id = 1;

Take the lock you actually need. If you must read, decide, then write, say so and the database will hold the row for you:

SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;

Materialize the conflict. Write skew survives because the transactions touch different rows. Lock the rows the rule is about, and there is a conflict to find again:

-- the rule is "at least one on call for this shift", so lock every row it covers
-- and count them yourself: FOR UPDATE cannot be combined with an aggregate
SELECT doctor FROM shifts WHERE shift_id = 42 AND on_call FOR UPDATE;

That only covers rows that already exist. If the rule can be broken by an INSERT rather than an UPDATE, row locks have nothing to hold and you want the next one instead.

Let a constraint do it. A unique index is the cheapest serialization primitive in the building, and unlike everything above it holds no matter how many application servers you’re running or how their calls interleave. Check-then-insert is a race; inserting and handling the duplicate key error isn’t. Exclusion constraints do the same job for overlapping ranges, which solves the booking problem outright.

Version your rows. The optimistic pattern, for when you need read-modify-write across a user’s think time and can’t hold a transaction open that long:

UPDATE documents SET body = $1, version = version + 1
WHERE id = $2 AND version = $3;
-- 0 rows updated means somebody else got there first

You need the retry loop either way

Whatever you pick, transactions fail for reasons that aren’t your caller’s fault. Serialization failures and deadlocks are the two you’ll actually meet, both are retryable, and almost nobody handles them.

const sleep = (ms: number) => new Promise((r) => setTimeout(r, ms));

async function withRetry<T>(fn: () => Promise<T>, tries = 5): Promise<T> {
  for (let attempt = 0; ; attempt++) {
    try {
      return await fn();
    } catch (err) {
      const code = (err as { code?: string }).code;
      const retryable = code === "40001" || code === "40P01"; // serialization, deadlock
      if (!retryable || attempt >= tries - 1) throw err;
      // exponential backoff with jitter: without the jitter, everything that
      // collided the first time collides again on the same schedule
      await sleep(2 ** attempt * 10 + Math.random() * 20);
    }
  }
}

Two rules make it work. The transaction body has to be safe to run twice, which means nothing that escapes the database (no emails, no webhooks, definitely no charging a card) until the commit comes back clean. And the retry has to re-run the whole thing, reads included. A retry that reuses the values it read the first time round just reproduces the bug that caused the abort.

There’s one failure the loop above deliberately doesn’t catch, which is a connection that dies during the COMMIT itself. That outcome isn’t failed, it’s unknown: the commit may well have landed, and blindly re-running the transaction debits the account a second time. Checking the error code instead of catching everything is what keeps you out of it. If you do need to handle the case properly, put an idempotency key in the data, so the second attempt can recognise the first one and do nothing.

Aurora DSQL is where this stopped being optional for me. It’s fixed at repeatable read and uses optimistic concurrency control, so nothing blocks and every conflict turns up at commit as ERROR: change conflicts with another transaction (OC000) (SQLSTATE 40001). The docs are blunt that this happens more often than lock waits would, and they’re right. What you get back is that contention becomes one thing you handle in one place, rather than a lock you have to reason about at every call site. On MCA Benches, two draft captains clicking at once is settled by the primary key on (draft, pick number): the second write aborts on its own, and I never had to add a lock or a queue.

Keep transactions short

Most of what makes isolation painful is transactions that stay open too long. A long transaction holds a snapshot, and under MVCC a held snapshot means the database can’t clean up the old row versions it might still need. It also widens the window where something can conflict with you, and at serializable, where something can abort you.

The practical version: open the transaction as late as you can, commit as early as you can, and never wait on the network inside one. An HTTP call inside a transaction is somebody else’s p99 becoming your lock duration.

Where to land

If you want one recommendation: stay on your database’s default, and be deliberate at the handful of places where correctness depends on what you read. You can usually count those on one hand. They’re the ones involving money or a uniqueness rule, and the fixes above are cheaper and more local than changing the level for everything.

Move to serializable when you stop being able to count them, or when you can no longer convince yourself you’ve found them all. It’s a real option now rather than the last resort it was in 2010, and the price is a retry loop you should have written anyway.

The one thing I’d avoid is assuming the default protects you because it has a reassuring name. Read committed will pass every test you write, and then lose an update the first time two people click the same button at the same moment.

The next thing that breaks all of this is distance. Everything above assumes one database that can see all your data at once. The moment it can’t, these guarantees need consensus across machines to hold up, which is where sharding and two-phase commit come in.