Transaction Isolation Levels: What Each One Actually Guarantees

Most explanations of isolation levels reproduce the ANSI SQL-92 table: four levels, three anomalies, tidy grid. That table is a poor guide to what your database does, for two reasons.

For a separate people-operations perspective, the article covers discreet monitoring modes and their implications.

It defines levels by which anomalies they forbid, not by any mechanism. Two engines can both claim REPEATABLE READ and behave differently, because they prevent the listed anomalies by different means and permit different things not on the list.

The list of anomalies is incomplete. The standard names dirty read, non-repeatable read and phantom read. Real systems exhibit others — write skew, lost update, read skew — that the standard does not mention, and whether you get them is not determined by the level name.

The useful question is not "what level am I on" but "what can happen to my data, on this engine, at this setting."

The anomalies, precisely

Worth being exact, because the informal descriptions blur together.

Dirty read. T1 writes a row. T2 reads it before T1 commits. T1 rolls back. T2 has read a value that never existed in any committed state.

Non-repeatable read. T1 reads a row. T2 updates and commits. T1 reads the same row again and sees a different value.

Phantom read. T1 runs a query matching a predicate. T2 inserts a row matching that predicate and commits. T1 runs the same query and gets a different set of rows.

Lost update. T1 and T2 both read a value, both compute a new value from it, both write. One write silently overwrites the other. Not in the ANSI list, and one of the most common real-world bugs.

Write skew. T1 and T2 read an overlapping set, each checks a constraint that holds, each writes to a different row, and the combined result violates the constraint neither transaction individually broke. Also not in the ANSI list, and the reason snapshot isolation is not serializable.

Read skew. T1 reads row A, T2 updates A and B and commits, T1 reads B. T1 now holds a view of A and B that was never simultaneously true.

The levels as commonly implemented

Read Uncommitted

Permits dirty reads. In practice, rarely useful and rarely what anyone wants.

Note a quirk: some engines accept the setting and give you something stronger. In PostgreSQL, READ UNCOMMITTED behaves as READ COMMITTED — there is no mechanism for reading uncommitted data, so the level is accepted and ignored.

Read Committed

Every statement sees only data committed before that statement began. The default in PostgreSQL, Oracle and SQL Server.

What it prevents: dirty reads.

What it permits: non-repeatable reads, phantoms, lost updates, write skew, read skew.

The subtlety that catches people: the snapshot is taken per statement, not per transaction. Two identical SELECTs in one transaction can return different data, and this is correct behaviour, not a bug.

The lost update problem is live here. This:

BEGIN;
SELECT balance FROM accounts WHERE id = 1;  -- reads 100
-- application computes 100 - 30
UPDATE accounts SET balance = 70 WHERE id = 1;
COMMIT;

is broken under concurrency. Two transactions doing this concurrently both read 100, both write 70, and 30 disappears. The fix is not a higher isolation level but a different statement:

UPDATE accounts SET balance = balance - 30 WHERE id = 1;

which is atomic at the row level, or SELECT ... FOR UPDATE to take the lock at read time.

Repeatable Read

Here the name means different things on different engines, and this is where most confusion lives.

In PostgreSQL, REPEATABLE READ is implemented as snapshot isolation. The transaction sees a consistent snapshot taken at its start. It prevents non-repeatable reads and, unlike the standard requires, also prevents phantoms — snapshot isolation gives you a consistent view of the whole database, so new rows are invisible regardless of predicate.

It does not prevent write skew. And it introduces a behaviour people find surprising: a transaction may fail at commit with a serialization error, and the application must be prepared to retry.

In MySQL with InnoDB, REPEATABLE READ is the default and behaves differently again. Consistent reads use a snapshot, but locking reads (SELECT ... FOR UPDATE, UPDATE, DELETE) see the latest committed data rather than the snapshot. The combination can produce results that are hard to reason about, and it means the same transaction can observe two different versions of reality depending on statement type. Gap locks prevent most phantoms in practice.

In Oracle, REPEATABLE READ is not offered at all. Oracle provides READ COMMITTED and SERIALIZABLE, where its SERIALIZABLE is snapshot isolation rather than true serializability.

The lesson: "we run at REPEATABLE READ" tells you almost nothing across engines.

Serializable

The only level defined by a guarantee rather than a list of exclusions: the result is equivalent to some serial execution of the transactions.

Implementations differ substantially:

PostgreSQL uses Serializable Snapshot Isolation. It runs snapshot isolation, tracks read/write dependencies between concurrent transactions, and aborts one when a dangerous cycle is detected. Genuinely serializable, and transactions can fail at commit and must be retried. This is not an error condition to log and ignore; the retry is part of the contract.

SQL Server uses range locks by default, which is serializable but blocks rather than aborts. It also offers snapshot isolation as a separate optional level.

MySQL InnoDB implements SERIALIZABLE by converting plain SELECT into SELECT ... LOCK IN SHARE MODE, which is heavy on locking.

Oracle's SERIALIZABLE is snapshot isolation, which permits write skew. It is not serializable in the formal sense, whatever the keyword says.

Write skew, concretely

The anomaly most worth understanding, because it survives snapshot isolation and it produces bugs that look impossible.

The classic case: a hospital requires at least one doctor on call. Two doctors, Alice and Bob, are both on call. Each opens the app to go off call.

-- Alice's transaction              -- Bob's transaction
BEGIN;                              BEGIN;
SELECT count(*) FROM on_call        SELECT count(*) FROM on_call
  WHERE on_call = true;             --   WHERE on_call = true;
-- returns 2, so it is safe         -- returns 2, so it is safe
UPDATE doctors SET on_call = false  UPDATE doctors SET on_call = false
  WHERE name = 'Alice';             --   WHERE name = 'Bob';
COMMIT;                             COMMIT;

Both snapshots showed two doctors on call. Neither transaction wrote to a row the other read. There is no write-write conflict, so snapshot isolation permits both. Nobody is on call.

Fixes, in order of preference:

A database constraint, if the invariant can be expressed as one. This is the only fix that cannot be bypassed by a future code path.

True serializable isolation, with retry logic. PostgreSQL's SSI catches exactly this.

Materialising the conflict — take an explicit lock on a row representing the shift, so the transactions do conflict on a write. Effective and easy to get wrong.

SELECT ... FOR UPDATE on the rows you checked, which turns the read into a write for conflict-detection purposes.

Application-level checks after the fact are not a fix. The window is exactly what you failed to close.

What to actually do

Know your engine's default and what it means there. Not the ANSI table — the engine's own documentation, for the version you run.

Assume lost update is possible at anything below serializable, and write updates that do not read-then-write across a round trip.

Identify your invariants, and for each ask: can two concurrent transactions each preserve it individually and break it together? If yes, that is a write skew candidate and needs a constraint or a real serializable transaction.

If you use serializable, implement retry. A serialization failure is a normal outcome, not an exception. Retry with a bounded count and a small backoff, and make sure the transaction is safe to re-run.

Test under concurrency. Isolation bugs do not appear in single-threaded tests, and they appear in production under load. A test that runs two transactions with a deliberate interleaving is worth more than a hundred sequential ones.

Do not raise the isolation level as a general precaution. It costs throughput, and it does not fix lost update where the application reads and writes across a round trip. Fix the statement, not the setting.

The summary worth keeping

Read Committed: each statement sees a fresh snapshot. Lost update and write skew both possible.

Repeatable Read: means snapshot isolation on PostgreSQL, something else on MySQL, and does not exist on Oracle. Write skew possible on all of them.

Serializable: genuinely serializable on PostgreSQL and SQL Server, snapshot isolation on Oracle despite the name. Requires retry handling where it aborts rather than blocks.

And the sentence to remember: the level name tells you what the vendor calls it. The documentation for your version tells you what it does.