Of ACID's four letters, three are non-negotiable and one has a volume knob: isolation. You can ask for the perfect illusion that you're the only user —and pay for it with less concurrency and transactions that abort— or relax it in exchange for performance, accepting that certain odd things happen. Those odd things have had names since 1992, there are four of them, and this lesson demonstrates them one by one in two sessions.

Then come the standard SQL's four levels with the classic table of which anomaly each one allows; and after that, what almost no course tells you: that table doesn't describe PostgreSQL. PostgreSQL doesn't implement READ UNCOMMITTED, and its REPEATABLE READ already prevents phantoms, which the standard doesn't require. We'll finish with the 40001 error, the module's most important one, and with its practical consequence: if you raise the level, your application has to know how to retry.

Every example is reproducible with two psql terminals on the freshly reloaded database, in 09-01's two-session format.

1. Dirty read
2. Non-repeatable read
3. Phantom read
4. Lost update and serialization anomaly
5. The four levels of standard SQL
6. What PostgreSQL really does
7. Each engine's default level
8. How the level is set and how it's checked
9. READ COMMITTED against REPEATABLE READ in two sessions
10. SERIALIZABLE and the 40001 error
11. The retry pattern with exponential backoff
12. Which level to choose
13. Common Mistakes and Tips
14. Exercises
15. Conclusion

Dirty read: a transaction reads data another has written and not yet committed, which can disappear with a ROLLBACK.

In an engine that allowed the dirty read, at t3 session B would see 99.00 — a price that has never existed for anybody, because at t4 it's undone. If B were the process generating the price-comparison feed, GreenStore would have published a matcha tea at €99.

In PostgreSQL, by contrast, B sees 22.00. Always. At any isolation level, including the one called READ UNCOMMITTED:

And it isn't that PostgreSQL "makes an effort" to avoid it: with MVCC the dirty read is impossible by construction. The row's new version carries an xmin from an uncommitted transaction, and 09-02's visibility rule discards it without further ado. There's no code to remove and no level to lower.

Dialect note: the dirty read does exist, and it does get used. SQL Server implements READ UNCOMMITTED for real, and its famous WITH (NOLOCK) hint is exactly that: reading without respecting locks or commits. It's put in reports so as not to block anybody, and in exchange you can see uncommitted rows, duplicated rows and missing rows in the same scan. MySQL/InnoDB implements it too. PostgreSQL, Oracle and SQLite don't.

Non-repeatable read: the same query, over the same row, returns different values within a single transaction, because another one modified it and committed in between.

The GreenStore case: the analyst is computing the margin report and, halfway through, the purchasing manager raises the matcha's price.

Two reads of the same row within the same transaction, two different values. If the report sums amounts at t2 and computes percentages at t4, the percentages won't match the amounts and nobody will know why.

The cause is exactly 09-02's: in READ COMMITTED, each statement takes a new snapshot. In REPEATABLE READ the snapshot is a single one for the whole transaction, and t4 would still return 22.00 — you'll see it in section 9.

Phantom read: the same query returns a different set of rows, because another transaction inserted or deleted rows satisfying the condition.

The difference from the previous one is subtle but important: there a row's value changed; here which rows there are changes.

The report will say there are 21 orders and 47 lines, and somebody will spend the afternoon looking for the order with no lines. That order 21 is the phantom: it appeared halfway through the report.

The detail almost nobody tells you: the SQL standard allows phantoms in REPEATABLE READ, but PostgreSQL doesn't allow them. Its REPEATABLE READ is implemented as snapshot isolation: a single snapshot for the whole transaction, and a snapshot doesn't change its contents. In PostgreSQL, t4 would return 20 — the same number as t2. It's a stronger guarantee than the standard's, and we'll come back to it in section 6.

This is the important one, the one that can cost GreenStore money, and the one 09-03 left pending.

Lost update: two transactions read the same value, each computes a new value from it and both write. The second write erases the first one's work.

The scenario: there are 40 units of matcha tea left and two customers buy one each at the same time. The application does the natural thing —read the stock, subtract, write—, which is precisely what 05-05 already flagged as incorrect:

Two units have been sold and only one has been deducted. Neither session got an error, nothing was left in the log, and the discrepancy won't show up until the physical stocktake. With two sessions the loss is one unit; with a Christmas traffic peak, dozens.

Only the shape of the UPDATE changes:

Thirty-eight: correct. And it isn't magic. When B tries at t7 to update a row A has locked, B is left waiting (it's 09-05's implicit lock). On A committing, PostgreSQL doesn't apply B's UPDATE blindly: it rereads the updated row, re-evaluates the WHERE and recomputes the expression over the new version. So stock - 1 is computed over 39, not over 40.

The rule to extract, and it holds for the whole module: in READ COMMITTED, a single write statement is safe; the danger lies in reading with one statement and writing with another. SET stock = stock - 1 is safe; SELECT and then SET stock = 39 isn't. It's the same lesson as 05-05's upsert and 09-02's WHERE stock >= 1.

When the logic doesn't fit in one statement —because you have to query three tables, apply a rule and decide— there are two correct solutions, and they're the two remaining lessons: raise the isolation level (the following sections) or explicitly lock the row with SELECT ... FOR UPDATE (09-05).

The SQL:1992 standard defined the levels by the anomalies they allow, not by how they're implemented:

This is the table that comes up in every job interview. And it's a minimum definition: it says what an engine may allow, not what it does. An engine that allows no anomaly at any level complies with the standard perfectly.

Three differences from the standard, and all three matter:

1. READ UNCOMMITTED doesn't exist. The syntax is accepted for compatibility, but it behaves exactly like READ COMMITTED. The dirty read is impossible with MVCC (section 1).
2. REPEATABLE READ already prevents phantoms. The standard allows them; PostgreSQL doesn't. Its implementation is snapshot isolation: a single snapshot for the whole transaction. There are only three distinguishable levels in practice.
3. REPEATABLE READ and SERIALIZABLE don't block to achieve it: they abort. Instead of making you wait, they let you work and, if they detect a conflict at the end, they kill your transaction with error 40001. That change of model is what forces you to retry, and it's section 11.

And a fourth that deserves its own line, because it's the reason the highest level exists:

The serialization anomaly is more general than the three classic ones: it's any result no sequential execution of the same transactions could have produced, even though each one read and wrote committed and different data. SERIALIZABLE is the only level that prevents it, and it does so with SSI (Serializable Snapshot Isolation): it watches the read and write dependencies between transactions and aborts the ones forming a dangerous cycle.

Dialect note — the migration trap. The level's name doesn't say the same thing in two engines. An application written against MySQL runs in REPEATABLE READ without knowing it and, when carried over to PostgreSQL, moves to READ COMMITTED: non-repeatable reads appear that didn't happen there. And the other way round: code that works in PostgreSQL under REPEATABLE READ because the engine aborts the conflicts, in MySQL doesn't abort and silently loses updates. Always verify the effective level when migrating; it's the first thing to look at.

And for the whole session or the whole server, 09-03's tools: SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL ..., ALTER ROLE ... SET default_transaction_isolation, or that same parameter in postgresql.conf.

The whole difference is in when the snapshot is taken:

Same script, two levels, different results. Session A is the report; B is the shop's day-to-day operation.

A in READ COMMITTED (a new snapshot per statement):

A in REPEATABLE READ (BEGIN ISOLATION LEVEL REPEATABLE READ;), with an identical script:

The same values as at t1 and t2. For A, the world froze at the instant of its first query, and B's UPDATE and INSERT don't exist until A commits and starts another transaction. Neither non-repeatable read nor phantom: it's exactly what a report needs.

The price of that frozen snapshot appears the moment A tries to write something B has already changed:

Section 4's lost update no longer happens: instead of silently trampling B's change, PostgreSQL kills A's transaction. That's the deal with the high levels, and it's an honest deal: you'd rather have an error you can retry than an invisible discrepancy.

REPEATABLE READ protects each row, but it doesn't protect relationships between rows. The typical case in GreenStore: the rule "the Drinks category's total stock can't drop below 250 units", checked by two warehouse operators at the same time.

Both read 370, both subtracted 60, and the real total would have ended at 250… which still satisfies the rule. Change the 60s for 70s and the total drops to 230: both checks were correct and the combined result wasn't. No row was trampled —A touched product 14 and B product 16—, so REPEATABLE READ would have let both through. Only SERIALIZABLE sees that A read what B wrote and vice versa and aborts one of them.

Notice three things about the error, because they're the whole level's signature: the 40001 code, common to every serialization error; the explicit HINT that retrying may work —it isn't a data error, it's a temporal scheduling conflict—; and that the failure arrives at the COMMIT, when you've already done all the work.

If you use REPEATABLE READ or SERIALIZABLE, your application has to be prepared to retry. It isn't optional and it isn't a rare case: it's those levels' normal mode of operation.

Six rules that make this loop actually work:

1. The whole transaction is retried, from the BEGIN. You can't carry on where you left off: its snapshot is no longer valid.
2. Only 40001 and 40P01 (deadlock, 09-05) are retried. A foreign-key violation isn't fixed by repeating it: it'll fail the same way five times and five seconds will be lost.
3. The backoff is exponential and randomised. Without the random component (jitter), two colliding transactions retry at the same time and collide again, indefinitely.
4. The transaction has to be idempotent, which is 05-03's and 09-03's property: if the first attempt got as far as inserting the order and then failed, the second can't duplicate it. A unique business key solves it.
5. Nothing outside the database inside the block: retrying five times a transaction that charges a card means five charges (09-02). And count the retries: a rising rate of 40001 is the signal that there's real contention and that the problem is one of design, not of configuration.

The decision rule, in one sentence: always start at READ COMMITTED and go up only when you can name the specific anomaly that's hurting you. Raising the level isn't "safer" full stop: it's swapping a silent problem for an explicit error that has to be handled with code.

• Learning the standard's table and believing it describes your engine. It describes none of them exactly: PostgreSQL has no READ UNCOMMITTED and its REPEATABLE READ allows no phantoms. And READ COMMITTED doesn't protect against the lost update: it protects against the dirty read and nothing else.
• Confusing SELECT + UPDATE with a relative UPDATE. SET stock = stock - 1 is safe even in READ COMMITTED; reading 40 and writing 39 isn't. It's the lesson's most profitable distinction.
• Going up to SERIALIZABLE without writing the retry. You swap silent corruption for visible outages in production. The high level without a retry is worse than the low one.
• Retrying any error, or retrying with no jitter. Only 40001 and 40P01 deserve a retry; repeating a constraint violation is wasting time five times over. And without the random component, two synchronised transactions collide again on every round.
• Assuming MySQL's REPEATABLE READ is PostgreSQL's. MySQL's allows lost updates PostgreSQL aborts; PostgreSQL's prevents phantoms MySQL's only avoids with gap locks. And WITH (NOLOCK) in SQL Server isn't "going faster": it's a dirty read, with uncommitted, duplicated or missing rows in the same scan.
• Tip: put SHOW transaction_isolation; on your checklist when debugging concurrency; it's the first question. And for a report, BEGIN ISOLATION LEVEL REPEATABLE READ READ ONLY;: total coherence and not a single lock on anybody.
• Tip: measure the 40001s. Their rate tells you whether your design has real contention, and that information isn't anywhere else.

With two psql terminals and the freshly reloaded database.

Demonstrate the lost update and its two solutions.

1. Reproduce section 4 with the SELECT → UPDATE ... SET stock = 39 pattern in READ COMMITTED. Check that the final stock is 39 and explain which unit has been lost.
2. Repeat it with UPDATE ... SET stock = stock - 1. Check that the result is 38 and describe exactly what session B does at t7.
3. Repeat case 1 with both sessions in REPEATABLE READ. What happens and exactly when? Copy the error.
4. Which isolation level would you use in GreenStore's real application for confirming an order, and why is going up to SERIALIZABLE not the answer?

The analyst Daniel Vercher (employee 8) runs the monthly report and complains that "the numbers don't agree between the tables in the PDF".

1. Reproduce the problem: session A in READ COMMITTED counts orders, another session inserts an order with two lines and commits, and A counts orders and lines again. Show the inconsistent numbers.
2. Identify which anomaly it is and why the standard calls it that.
3. Fix it by changing a single line of A's script, and demonstrate that it now adds up.
4. If the report takes 40 minutes, what side effect does keeping that transaction open for all that time have? Relate it to what you saw in 09-02 and propose 09-03's variant that mitigates it.

GreenStore imposes a new rule: the Drinks category (4) can never drop below 250 units of total stock. Today it has 370.

1. Write the transaction that reduces product 14 by 70 units, checking the rule first, in SERIALIZABLE.
2. Run it at the same time in two sessions —one over product 14 (stock 180) and another over 17 (stock 90)— and show what happens at each COMMIT.
3. What would have happened under REPEATABLE READ? And under READ COMMITTED? Reason out why neither of them detects the problem.
4. Write the retry pseudocode that would be needed for this operation to work in production, and say why it can't be solved with a CHECK.

Solution 1

1. Final stock 39. Session A's unit has been lost: its UPDATE at t5 did get written and committed, but B's overwrote it with an absolute value computed from an already stale read. A sold and didn't deduct.

2. Final stock 38. At t7, session B tries to update a row A has locked, so it's left waiting —the UPDATE returns nothing, the terminal hangs— until A commits at t6. Then PostgreSQL rereads the already updated row, re-evaluates the WHERE and recomputes stock - 1 over 39, not over 40. A single statement's atomicity does the job.

3. The session that arrives second fails at its UPDATE, not at the COMMIT:

The lost update is now impossible, but in exchange you have to retry. And notice the nuance: in REPEATABLE READ the conflict is detected on writing, whereas in SERIALIZABLE (exercise 3) it's usually detected on committing.

4. In the real application, READ COMMITTED plus one of these two: 09-02's UPDATE ... WHERE id = 15 AND stock >= 1, or 09-05's SELECT ... FOR UPDATE. Going up to SERIALIZABLE isn't the answer for three reasons: it would work, but it would force you to implement the retry loop on the shop's hottest path; it aborts under load exactly when there are the most orders, which is the worst possible thing; and it's unnecessary, because the invariant affects a single row and a single statement can express it. The high level is reserved for invariants no statement can express.

Solution 2

1. A gets 20 orders in its first query and, after the other session's INSERT, 21 orders and 49 lines. The PDF would say 21 orders with 49 lines if both counts were done afterwards, or 20 orders and 49 lines if they straddled the other session's COMMIT: either way, two figures taken from two different states of the world.

2. It's a phantom read: the same query returns a different set of rows because another transaction inserted rows satisfying the condition. It's called that because the new rows "appear" in a query that had already been run, as if they materialised out of nowhere.

3. The line that changes is the BEGIN:

With the snapshot fixed, A sees 20 orders and 47 lines in all its queries, whatever session B gets up to. In PostgreSQL this is enough because its REPEATABLE READ allows no phantoms; in an engine following the standard to the letter you'd need SERIALIZABLE.

4. Keeping a transaction with a fixed snapshot open for 40 minutes prevents VACUUM from cleaning any row version later than that instant, across the whole database: it's the bloat mechanism 09-02 explained and the damage 09-01 anticipated. 09-03's mitigation is to run the report as BEGIN ISOLATION LEVEL SERIALIZABLE READ ONLY DEFERRABLE;: it still holds its snapshot, but being read-only and deferrable it blocks nobody and can't abort. And the underlying solution is the usual one: run long reports against a replica (09-02).

Solution 3

1. The transaction, for product 14:

2. The first to commit succeeds. The second fails at the COMMIT:

Each one checked 370 − 70 = 300 and both were right separately; together they'd leave the total at 230, below the limit. SERIALIZABLE detects that A read a set of rows B modified and vice versa —a crossed read/write dependency— and cancels one of the two.

3. Neither REPEATABLE READ nor READ COMMITTED detects it, and for the same reason: there's no conflicting row. A writes product 14 and B product 17; they're different rows, so there's no lock, no update conflict and nothing to abort. REPEATABLE READ protects rows; the invariant here is over a set. Under READ COMMITTED it would be even worse, because each statement would additionally see different data. Both would commit happily and the total would end at 230.

4. The pseudocode is section 11's: a loop of up to five attempts, BEGIN ISOLATION LEVEL SERIALIZABLE, catching 40001, rollback(), a wait with exponential backoff and jitter, and rethrowing any other error without retrying.

And it can't be solved with a CHECK for the reason 09-02 explained: a CHECK can only look at the columns of the row itself, and this rule needs to sum the stock of four rows of the table. The real alternatives are three: SERIALIZABLE with a retry (this exercise's); explicitly locking the four rows with SELECT ... FOR UPDATE before checking (09-05); or keeping the aggregated total in a row of its own and protecting it with a CHECK and a trigger that maintains it (10-05). The first is the cleanest; the second, the most predictable under load.

You now know what can happen to you and how much avoiding it costs:

• The four anomalies, demonstrated in two sessions: dirty read (reading the uncommitted), non-repeatable read (the same row changes value), phantom read (new rows appear) and lost update / serialization anomaly (two individually correct transactions produce a result no sequential execution would give).
• That the lost update is the central case: two customers buy the last matcha, both read 40, both write 39, and a unit is sold that nobody deducts, with no error and no trace.
• And its most useful counterpart in the whole module: in READ COMMITTED, a single write statement is safe —SET stock = stock - 1 gives 38 because the engine rereads and recomputes— and the danger is in reading with one statement and writing with another.
• The standard's four levels with their classic table… and what PostgreSQL really does: it doesn't implement READ UNCOMMITTED (the dirty read is impossible with MVCC), its REPEATABLE READ already prevents phantoms and there are only three distinguishable levels.
• Each engine's default level —READ COMMITTED in PostgreSQL, Oracle and SQL Server; REPEATABLE READ in InnoDB; SERIALIZABLE de facto in SQLite— and the migration trap: the same name doesn't mean the same thing.
• The 40001 error, in its two forms (concurrent update in REPEATABLE READ, read/write dependencies in SERIALIZABLE), with its HINT saying that retrying may work. And the consequence: going up a level forces you to write the retry loop, with exponential backoff, jitter, SQLSTATE filtering and an idempotent unit of work.
• And the criterion: READ COMMITTED by default; REPEATABLE READ READ ONLY for reports; SERIALIZABLE only for invariants spanning several rows that no statement can express. Go up a level only when you can name the anomaly that's hurting you.

There remains the other answer to the lost update, the one that aborts nothing and the one e-commerce systems actually use: locking the row. In Handling Concurrency: Locks and Deadlocks you'll see why session B was left waiting at t7 and what was holding it; the safe read-modify-write pattern with SELECT ... FOR UPDATE and its whole family; SKIP LOCKED for sharing out a queue of orders across several processes without treading on each other; optimistic locking with a version column against pessimistic locking; what an ALTER TABLE locks and why CREATE INDEX CONCURRENTLY had to exist; and deadlocks: how they arise, how the engine detects and resolves them, how they're diagnosed with pg_locks and pg_blocking_pids, and the rules for never causing them.