Part 3 — Changing data › Chapter 22

Isolation levels and the anomalies they allow

Dirty read, non-repeatable read, phantom, lost update — each mapped to the level that still permits it.

From the advert

“Esperienza su ambienti di produzione” — and this is the chapter that explains the bugs that only ever happen under load.

The four anomalies

🧒 Simple

Two people writing in the same notebook at the same time. How much are they allowed to see of each other's half-finished sentences? Letting them see everything is fast and produces nonsense. Letting them see nothing until the other has finished is safe and slow. The isolation level is where you set that dial.

Anomaly	What happens	Concrete example
Dirty read	You read data another transaction has not committed	You see a €5 000 transfer that is then rolled back
Non-repeatable read	You read the same row twice and it changed	A stock check says 5, then 3, inside one transaction
Phantom read	You run the same query twice and new rows appear	“Orders today” returns 40, then 41
Lost update	Two read-modify-writes; one overwrites the other	Two agents both set the discount, one silently wins

The levels, and what each still permits

Level	Dirty	Non-repeatable	Phantom	Cost
`READ UNCOMMITTED`	✅ allowed	✅	✅	Lowest
`READ COMMITTED`	❌ prevented	✅	✅	Low — the default nearly everywhere
`REPEATABLE READ`	❌	❌	✅ (in theory)	Medium
`SERIALIZABLE`	❌	❌	❌	Highest

🎓 The standard describes anomalies, not implementations

Each engine implements these differently and some of them exceed the requirement. PostgreSQL's REPEATABLE READ is a genuine snapshot and prevents phantoms too, even though the standard allows them. InnoDB's REPEATABLE READ — its default — also prevents phantoms for plain reads, using next-key locks, but not for locking reads without care. Oracle offers only READ COMMITTED and SERIALIZABLE; asking for the other two gets you one of those. Naming one such divergence is what marks the answer as experience rather than a memorised table.

SQL Server is the interesting case

SQL Server historically used pessimistic locking: at READ COMMITTED a reader takes shared locks and therefore blocks on writers. That is why so much legacy T-SQL is peppered with WITH (NOLOCK) — it is people working round reader/writer blocking. Two settings change the model entirely:

Setting	Effect
`READ_COMMITTED_SNAPSHOT ON`	Database-level. `READ COMMITTED` now uses row versions, so readers never block writers — PostgreSQL-like behaviour, and usually the right change
`ALLOW_SNAPSHOT_ISOLATION ON`	Enables the explicit `SNAPSHOT` level: a consistent view for the whole transaction

⚠ Trap — WITH (NOLOCK) is not “a bit faster”

NOLOCK is READ UNCOMMITTED under another name, and it does not merely risk reading uncommitted rows. It can also:

Miss committed rows entirely, or return the same row twice, if a page split moves rows while your scan is passing.
Fail with error 601 (“could not continue scan with NOLOCK due to data movement”).

So a financial report with NOLOCK can produce a number that was never true at any instant. The correct fix for reader/writer blocking is READ_COMMITTED_SNAPSHOT, not a hint on nine hundred queries. Being able to say this precisely is one of the strongest single answers available in a SQL Server interview.

Nothing here is a dirty read. Both sessions behaved correctly at READ COMMITTED.

🎓 Lost update is the one that actually bites

It is not in the standard's table, it happens at the default isolation level in every engine, and it produces no error. The three fixes, in order of preference:

Make the update atomic: SET qty = qty + 5 rather than reading then writing. Free, and it eliminates the window entirely.
Optimistic concurrency: a version column and WHERE version = ?; zero rows affected means you lost, so retry.
Pessimistic locking: SELECT … FOR UPDATE, holding a lock from read to write. Correct, and it costs concurrency.

What SERIALIZABLE really costs

PostgreSQL implements it as Serializable Snapshot Isolation: transactions run optimistically, and the engine aborts one with a serialization_failure (SQLSTATE 40001) if the interleaving could not have happened serially. That means your application must be prepared to retry — a serialisable transaction is not a transaction that always succeeds, it is one that always succeeds or tells you it did not. SQL Server implements it with range locks instead, so the cost appears as blocking and deadlocks rather than as aborts.

Two windows, one row: seeing a non-repeatable read happen, and then seeing it not happen.

-- Two psql windows against the same PostgreSQL database. SQLite cannot show this:
-- it has one writer, so there is no concurrency to observe.

-- window 1
BEGIN;                                                     -- start a transaction and keep it open
SELECT on_hand FROM stock WHERE product_id = 1;            -- 19

-- window 2
BEGIN;                                                     -- a second, independent transaction
UPDATE stock SET on_hand = 5 WHERE product_id = 1;         -- change the same row…
COMMIT;                                                    -- …and make it permanent

-- window 1 again, in the SAME transaction as before
SELECT on_hand FROM stock WHERE product_id = 1;            -- 5 at READ COMMITTED
COMMIT;                                                    -- the value changed under us

-- Now repeat the whole thing with:  BEGIN ISOLATION LEVEL REPEATABLE READ;
-- Window 1 still says 19 the second time. Same data, different guarantee.

What you have just seen

At READ COMMITTED — the default in PostgreSQL, SQL Server and Oracle — each statement sees a fresh snapshot. Reading the same row twice in one transaction can legitimately give two answers.
At REPEATABLE READ the snapshot is taken once per transaction, so the second read matches the first. The row has still changed; you are simply not being shown it.
This is not a bug being fixed. It is a trade: the stronger level costs concurrency, and in PostgreSQL a SERIALIZABLE transaction can be aborted outright and must be retried by the application.
Isolation is a property of the transaction, so it is set at BEGIN — and it cannot be changed after the first statement has run.

Four anomalies: dirty read, non-repeatable read, phantom, lost update.
READ COMMITTED is the default nearly everywhere and permits everything except dirty reads.
The standard names anomalies, not implementations. PostgreSQL's REPEATABLE READ also prevents phantoms; Oracle offers only two levels.
SQL Server readers block writers until READ_COMMITTED_SNAPSHOT is on. That setting, not NOLOCK, is the fix.
NOLOCK can miss committed rows and return duplicates during page splits — not just read uncommitted data.
Lost update is not in the anomaly table, happens at the default level, and raises no error.
Prefer an atomic update (SET x = x + n) over read-modify-write; then optimistic concurrency; then locking.
PostgreSQL's SERIALIZABLE aborts transactions with SQLSTATE 40001. Using it means writing a retry loop.

Interview questions this chapter answers

What isolation levels are there, and what does each prevent?

Read uncommitted (prevents nothing), read committed (no dirty reads — the usual default), repeatable read (no non-repeatable reads), serializable (no phantoms either). Then add that engines differ: PostgreSQL's repeatable read is a full snapshot and prevents phantoms, Oracle only implements two of the four.

Is WITH (NOLOCK) a good idea?

Rarely. It is read uncommitted, so it can read rows that get rolled back — and worse, during page splits it can skip committed rows or return the same row twice, producing a total that was never true. If the problem is readers blocking writers, the fix is READ_COMMITTED_SNAPSHOT at database level.

Two users edit the same record. How do you stop one overwriting the other?

If it is a simple increment, make the write atomic: SET qty = qty + n. Otherwise optimistic concurrency — a version column, WHERE version = ?, and treat zero rows affected as a conflict to report or retry. Pessimistic locking works but holds a lock across the user's thinking time, which is usually unacceptable.