Transactions & Isolation
Two requests that are each correct can still corrupt your data when they run at the same time. Transactions promise to stop that, but most databases, by default, only keep part of the promise. Here's exactly which part.
The whole idea
Why transactions
Every piece of code in that diagram is correct. It reads the balance, checks there’s enough, and writes the new balance. Run it once and it works. Run it twice at the same moment and the account pays out 160 from 100. On March 2, 2014, the Bitcoin exchange Flexcoin learned this the expensive way: an attacker sent thousands of simultaneous transfer requests, moving coins between accounts “until the sending account was overdrawn, before balances were updated”. It lost 896 BTC, then worth about $600,000, and closed.
Bugs like this come from two places, and both get worse with scale:
- Partial failure. A process crashes, a disk fills or a network drops between two writes that only make sense together.
- Concurrency. Two clients read and write the same data at once, and their steps interleave in an order nobody tested.
A transaction is the database’s answer to both. It groups several reads and writes into one logical unit that either commits as a whole or aborts as a whole, and it lets the application pretend, to a degree we’ll make precise, that it’s the only thing running. The application gets to retry an aborted transaction instead of reasoning about every half-finished state.
ACID
The safety guarantees of a transaction are usually summarised as ACID, an acronym Theo Härder and Andreas Reuter coined in 1983. The four letters are not equally meaningful.
- Atomicity is about faults, not concurrency. If anything goes wrong before commit, every write the transaction made is discarded. “Abortability” would be a better name.
- Consistency means your invariants hold, such as “credits and debits balance”. The database can enforce a few
(foreign keys, uniqueness,
CHECKconstraints), but mostly this is a property of your code. It’s the odd letter out. - Isolation is about concurrency: concurrent transactions shouldn’t step on each other. That’s the rest of this lesson.
- Durability: once committed, data survives crashes. On one machine that means the write-ahead log was flushed to disk before the commit was acknowledged; in a replicated system it also means copies on other nodes.
Atomicity
Atomicity is what makes retries safe. Without it, an error halfway through a multi-step change leaves you to work out which steps landed. With it, the answer is always “none”, so you can just run it again.Only mostly safe. If the commit succeeded but the acknowledgement was lost, a blind retry runs the transaction twice. Making the transaction idempotent, for example with a request ID, closes that gap.
Isolation levels
The strongest form of isolation is serializability: the result is the same as if the transactions had run one at a time, in some order. It’s expensive (the next lesson is about how databases do it), so most databases default to something weaker and let you opt in. Those weaker isolation levels each block some anomalies and allow others.
The SQL-92 standard defined four levels by which of three “phenomena” each one allows: dirty reads, non-repeatable reads and phantoms. In 1995, Berenson, Bernstein, Gray, Melton and the O’Neils published “A Critique of ANSI SQL Isolation Levels”, showing that those definitions are ambiguous, miss anomalies such as dirty writes and lost updates, and can’t even describe snapshot isolation, which real databases were already shipping. Atul Adya’s 1999 thesis later redefined the levels precisely, as kinds of cycle in a graph of dependencies between transactions.
The names vendors use never caught up. The same name can mean different guarantees:
| Database | Default level | What its “Repeatable Read” is | What its “Serializable” is |
|---|---|---|---|
| PostgreSQL | Read Committed | snapshot isolation | serializable (SSI) |
| MySQL (InnoDB) | Repeatable Read | weaker than snapshot isolation | serializable (locking) |
| Oracle | Read Committed | not offered | snapshot isolation |
| SQL Server | Read Committed (locking) | locking repeatable read | serializable (locking) |
Read committed
The most basic useful level makes two promises:
- No dirty reads. You only see data that has been committed.
- No dirty writes. You only overwrite data that has been committed.
Dirty reads
Dirty writes are the uglier cousin. If two transactions each update the same two rows and their writes interleave, you can end up with one row from each, a state neither of them meant to create. Every isolation level, even Read Uncommitted, prevents them the same way: a writer takes a row lock and holds it until it commits or aborts, so a second writer to the same row has to wait.
Reads are handled differently, because making readers take the same locks would let one long write freeze every
reader of that row. Instead the database keeps the last committed value next to any uncommitted one and hands
readers the committed one. SQL Server’s default is the exception: under locking Read Committed, readers take
short shared locks and can block behind writers. Its READ_COMMITTED_SNAPSHOT option switches to the versioned
approach, and it’s on by default in Azure SQL Database.
Read Committed is the default in Postgres, Oracle and SQL Server. In Postgres, asking for Read Uncommitted just gives you Read Committed.
Snapshot isolation
Read Committed still lets the ground move under you between statements. Each statement sees the latest committed data, so a transaction that reads twice can see two different worlds.
Read skew
For a two-statement web request, that window is short and often harmless; reload the page and the numbers agree. For a backup, an analytics query, or an integrity check that runs for an hour, it’s fatal: the backup contains some rows from before a transfer and some from after, and restoring it makes money appear or vanish.
Snapshot isolation fixes it by giving each transaction a consistent snapshot: it sees everything that was committed when it started, and nothing committed after. The key property is that readers never block writers, and writers never block readers. A long read-only query can run against its snapshot while writes carry on underneath it.
Writes still need rules. If two transactions under snapshot isolation update the same row, the database lets the first committer win and aborts the other. Postgres reports “could not serialize access due to concurrent update” (SQLSTATE 40001); Oracle raises ORA-08177. The application is expected to retry.
Snapshot isolation is Postgres’s Repeatable Read, Oracle’s Serializable, SQL Server’s SNAPSHOT level, and what
MongoDB’s multi-document transactions provide. It’s a big step up from Read Committed. It still isn’t
serializable, as we’ll see.
MVCC
How do you give every transaction its own snapshot without copying the database? You keep several versions of each row and let each transaction pick the version its snapshot should see. That’s multi-version concurrency control (MVCC), and almost every major database uses it.
Inside MVCC
The two big implementations put the old versions in different places:
- Postgres stores every version in the table’s heap. Each tuple carries
xmin(the transaction that created it) andxmax(the one that deleted or replaced it). A snapshot is essentially the list of transactions that had committed when it was taken, and a tuple is visible if its creator is in that list and its deleter isn’t. Dead tuples stay until VACUUM removes them. - InnoDB (MySQL) updates the row in place and writes the previous version to an undo log, linked from the row. A reader whose snapshot predates the change follows that chain backwards to rebuild the version it should see. A background purge thread discards undo records no snapshot needs. Oracle works the same way with undo segments.
Read Committed and snapshot isolation share the machinery. Under Read Committed, Postgres takes a new snapshot for every statement; under snapshot isolation, it takes one for the whole transaction.
Lost updates
Read Committed and snapshot isolation are about what a transaction can see. Neither, by itself, says what happens when two transactions read a value, modify it in application code, and write it back. That’s the pattern behind counters, balances, inventory, and any ORM that loads an object, changes a field and saves it.
Lost update
There are four standard fixes, roughly in order of preference:
- Atomic writes. Push the read-modify-write into the database:
UPDATE posts SET likes = likes + 1, MongoDB’s$inc, Redis’sINCR. The database locks the row for the duration, so concurrent increments queue. The hero diagram’s withdrawal can be one statement too:UPDATE accounts SET balance = balance - 80 WHERE id = 1 AND balance >= 80. Under Read Committed, Postgres re-checks theWHEREclause against the latest committed row after waiting for the lock, so the second withdrawal matches zero rows. - Explicit locks.
SELECT … FOR UPDATElocks the rows it returns until commit, so a second read-modify-write of the same rows waits for the first to finish. It works, and it’s easy to forget one code path. - Compare-and-set. Write only if the value is unchanged since you read it:
UPDATE … SET balance = 20, version = 8 WHERE id = 1 AND version = 7. Zero rows updated means someone got there first, so re-read and retry. This is optimistic locking, and many ORMs build it in. - Automatic detection. Under snapshot isolation, Postgres’s Repeatable Read, Oracle’s Serializable and SQL
Server’s
SNAPSHOTall notice that you’re overwriting a row changed since your snapshot, and abort you. MySQL’s Repeatable Read does not: it silently applies the write to the latest version, so lost updates get through.
Write skew
A lost update is two transactions writing the same row. Its harder generalisation is two transactions that read the same data and then write different rows. No single row is written twice, so neither row locks nor first-committer-wins notices anything.
Write skew
The general shape is always the same:
- A
SELECTchecks some condition: the total balance, whether anyone else is on call, whether a seat is free. - The application decides what to do based on that result.
- It writes, and that write changes the answer the other transaction’s step 1 got.
Snapshot isolation can’t stop this, because each transaction’s snapshot was perfectly consistent; it just went stale between the check and the write. The fixes:
- Serializable isolation, the only general cure. The next lesson shows how databases provide it.
- Lock what the decision depends on.
SELECT … FOR UPDATEon every row the check read, not just the one you write. - A constraint, when the rule can be expressed as one, because then the database checks it on every write. A rule spanning several rows usually can’t.
Phantoms
Write skew gets nastier when the check is about rows that don’t exist yet: “is this username taken?”, “does any booking overlap 2–3 p.m.?”, “are fewer than 100 tickets sold?” Then there’s nothing to lock. A write in one transaction that changes the result of another transaction’s search is called a phantom.
Phantoms
Postgres’s documentation says its Repeatable Read prevents “phantom reads”, and it does: rerun the same query inside the transaction and you get the same rows. That’s a different thing from preventing phantom-driven write skew, which it allows. The ways out:
- Unique constraints for “must not exist twice”, like usernames.
- Exclusion constraints in Postgres for “must not overlap”, like room bookings:
EXCLUDE USING gist (room_id WITH =, during WITH &&). - Materialize the conflict. Create rows that stand for the thing being contended: a row per show, per seat or per time slot, and lock that. It works, but it lets concurrency control leak into the data model, so use it last. It also depends on the level: under Read Committed the count after the lock sees fresh data, but under Postgres’s Repeatable Read the snapshot predates the lock, so update the row (a sold counter, say) rather than only locking it, and the second buyer gets a serialization failure.
- Serializable isolation, which tracks the predicate you searched for, not just the rows you found.
Trade-offs
Weaker isolation buys concurrency and fewer aborts with anomalies you have to design around yourself.
| Level | Prevents | Still allows | Cost |
|---|---|---|---|
| Read Committed | dirty reads, dirty writes | read skew, lost updates, write skew, phantoms | cheapest; per-statement snapshots |
| Snapshot isolation | the above, plus read skew; lost updates (in most databases) | write skew, phantom-driven write skew | old versions to store and clean up; aborts on write-write conflicts |
| Serializable | every anomaly between transactions | nothing | blocking or more aborts, depending on the implementation; retries required |
In the real world
- ACIDRain. In 2017, Todd Warszawski and Peter Bailis analysed 12 self-hosted e-commerce platforms deployed on over two million websites and found 22 exploitable concurrency bugs: gift cards spent past their balance, vouchers reused, inventory corrupted, an item slipped into a cart during checkout without being charged. Only 5 of the 22 were caused by the database’s default isolation level. The other 17 were scope bugs: the application didn’t put the related reads and writes in the same transaction at all, and no isolation level can fix that.
- Poloniex, two days after Flexcoin in 2014, lost 12.3% of its bitcoin when several withdrawals processed at nearly the same moment pushed a balance negative. Its owner said the auditing “was not explicitly looking for negative balances”.
- Postgres has been the clearest of the major databases about what its levels mean. Until version 9.1 (2011), its “Serializable” was really snapshot isolation. 9.1 renamed that behaviour Repeatable Read and made Serializable truly serializable, and its documentation maps each level to the anomalies it allows.
- Replicas can weaken the guarantee. In 2025, Jepsen found that Amazon RDS for PostgreSQL Multi-AZ clusters occasionally showed a long fork: two readers on different nodes saw two concurrent transactions commit in opposite orders, which snapshot isolation forbids. The likely cause was that the primary made commits visible in a different order from the one in its WAL, which the replicas replay. Jepsen’s MySQL work found a similar replica-ordering problem on RDS MySQL.
Pitfalls
- “It’s in a transaction, so it’s safe.” At Read Committed, the default almost everywhere, a transaction
prevents dirty reads and little else. Check-then-act code needs more than
BEGIN … COMMIT. - Trusting the name of the level. MySQL’s Repeatable Read and Postgres’s Repeatable Read prevent different anomalies, and Oracle’s Serializable allows write skew. Test the behaviour you rely on.
- Read-modify-write in application code. The ORM pattern of load, change, save is a lost update waiting for traffic. Use an atomic update, a lock or a version column.
- Locking only the row you write. Write skew comes from the rows you read. Lock those, or use Serializable.
- Long-running transactions. An idle-in-transaction session pins old versions, bloats tables and holds up
VACUUM. Set
idle_in_transaction_session_timeoutin Postgres, and watch InnoDB’s history list length. - Retrying half a transaction. A serialization failure (SQLSTATE 40001) means rerun the whole transaction from the start, including its reads, and make sure side effects like emails aren’t sent twice.
Recall
Go deeper
- Berenson et al., “A Critique of ANSI SQL Isolation Levels” (SIGMOD 1995): the paper that named dirty writes, lost updates, read and write skew, and defined snapshot isolation.
- Atul Adya, “Weak Consistency: A Generalized Theory and Optimistic Implementations for Distributed Transactions” (MIT, 1999): isolation levels defined as cycles in a dependency graph, the vocabulary Jepsen and Hermitage use.
- PostgreSQL docs: Transaction Isolation: the clearest vendor documentation of what each level allows, with worked examples.
- Martin Kleppmann, Hermitage: the same anomaly tests run against Postgres, MySQL, Oracle, SQL Server, CockroachDB and more.
- Jepsen, “MySQL 8.0.34” (2023): what MySQL’s Repeatable Read actually guarantees, and what RDS replicas do to Serializable.
- Warszawski & Bailis, “ACIDRain: Concurrency-Related Attacks on Database-Backed Web Applications” (SIGMOD 2017): how real applications lose money to these anomalies, and why transaction scope matters as much as level.
- Jepsen, “Amazon RDS for PostgreSQL 17.4” (2025): snapshot isolation broken by replication, in a healthy cluster.
- Ben Dicken, “Database Transactions” (PlanetScale, 2026): an interactive tour of how MySQL and Postgres implement isolation differently.
Keep going
The atomic notes for this lesson live in the knowledge graph:
Next on the map: Serializability (how databases make write skew and phantoms impossible, and what it costs) and Distributed Transactions (committing atomically across more than one machine).