Isolation Levels

Perfect isolation would run every transaction as if it were alone — correct, and too slow, because it would mean no real concurrency. So SQL defines levels that trade correctness guarantees for throughput, and you choose.

The anomalies

Each level is defined by which of these it prevents.

Dirty read — you read a row another transaction has changed but not committed. If it rolls back, you acted on a value that never existed.

Non-repeatable read — you read a row twice in one transaction and get different values, because someone committed a change in between.

Phantom read — you run the same query twice and get different rows, because someone inserted or deleted rows matching your WHERE clause.

The difference between the last two is worth being precise about: non-repeatable is an existing row changing; phantom is the set of rows changing.

The four levels

* in the standard. In practice, see below.

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN;
-- ...
COMMIT;

What your database actually does

The standard describes minimums. Real implementations differ enough that quoting the table alone will get you caught out:

Two things to take from this. READ UNCOMMITTED is largely theoretical — most databases will not give you dirty reads even when asked. And PostgreSQL and MySQL prevent phantoms at REPEATABLE READ, despite the standard permitting them, because both implement it with snapshots rather than locks.

MVCC — why readers do not block

PostgreSQL, MySQL/InnoDB and Oracle use multi-version concurrency control. A write does not overwrite a row; it creates a new version. Readers see the version that was current when their transaction (or statement) began.

The consequence is the one that matters day to day: readers never block writers, and writers never block readers. A long report does not stop the application from writing.

The cost is that old versions accumulate and must be cleaned up — PostgreSQL's VACUUM, MySQL's purge thread. A transaction left open for hours prevents cleanup of everything newer, which is the other reason lesson 37 said to keep them short.

SQL Server historically used locking instead, which is why READ_COMMITTED_SNAPSHOT exists and why "readers blocking writers" is a familiar SQL Server complaint.

Lost updates and SELECT FOR UPDATE

The anomaly the table above does not name, and the one you will actually hit:

Transaction A: SELECT quantity FROM stock WHERE id = 1;   -- reads 10
Transaction B: SELECT quantity FROM stock WHERE id = 1;   -- reads 10
Transaction A: UPDATE stock SET quantity = 9 WHERE id = 1;
Transaction B: UPDATE stock SET quantity = 9 WHERE id = 1;

Two items sold, one deducted. Both transactions did exactly what they were told.

Three fixes:

Do the arithmetic in the database — the simplest and usually the right answer:

UPDATE stock SET quantity = quantity - 1 WHERE id = 1 AND quantity > 0;

Pessimistic locking — lock the row when you read it, so the second transaction waits:

BEGIN;
SELECT quantity FROM stock WHERE id = 1 FOR UPDATE;
UPDATE stock SET quantity = 9 WHERE id = 1;
COMMIT;

Optimistic locking — carry a version and check it did not move:

UPDATE stock SET quantity = 9, version = version + 1
WHERE id = 1 AND version = 3;

If that updates zero rows, someone else got there first and the application retries. This scales better than locking when conflicts are rare.

SERIALIZABLE and retries

SERIALIZABLE guarantees the result is as if transactions ran one after another. PostgreSQL implements it optimistically: transactions proceed, and one is aborted with a serialization failure if the outcome could not have occurred serially.

That means any application using SERIALIZABLE must be able to retry a failed transaction. It is not a setting you can switch on without changing code.

Choosing a level

• READ COMMITTED — the default, and correct for the overwhelming majority of work.
• REPEATABLE READ — reports that must see one consistent snapshot across several queries.
• SERIALIZABLE — genuine correctness requirements (financial invariants), with retry logic.
• READ UNCOMMITTED — effectively never.

Reach for explicit locking or a version column before reaching for a higher isolation level. It is usually a smaller change with a more predictable cost.

Common mistakes

• Quoting the standard's table as though it described your database — it does not.
• Assuming REPEATABLE READ prevents lost updates — it does not; that needs locking or a version check.
• Using SERIALIZABLE with no retry logic — the transaction will fail and the failure is not a bug.
• Read-modify-write in application code — do the arithmetic in SQL.

Interview question

What is the difference between a non-repeatable read and a phantom read?

Non-repeatable: the same row returns a different value on a second read. Phantom: the same query returns a different set of rows because someone inserted or deleted. Adding that PostgreSQL and MySQL both prevent phantoms at REPEATABLE READ despite the standard allowing them shows you have used them rather than only read about them.

Check yourself

1. Which anomaly does READ COMMITTED prevent, and which does it allow?
2. Why do readers not block writers under MVCC?
3. Give two ways to prevent a lost update.