Dirty reads

One account with 50 in it. Transaction B is charging the customer’s card 40. Transaction A is deciding whether the same customer can pay for an order of 30. Nothing has run yet.

B subtracts 40. The balance is now 10, but only inside B. B hasn’t committed, because it’s still waiting to hear back from the card network, and that can take a few seconds.

The card network says no, so B rolls back. The balance was never really 10. A turned a customer away because of a number that, as far as the database is concerned, never existed.

That’s a dirty read: reading data that another transaction hasn’t committed yet, and might never commit.

Committed doesn’t mean consistent. Each statement sees what was committed when that statement began. Two statements in the same transaction can see two different states of the database.

That gap is where the next note starts.

You can stop here and you’ll know what a dirty read is. The next five steps are why Postgres can’t produce one, even if you ask it to.

The NO ISOLATION button above is imaginary. Postgres accepts SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED and then quietly runs READ COMMITTED. The SQL standard allows this, because a level is allowed to be stricter than requested.

For Postgres, preventing dirty reads costs nothing extra. The reason is in how an update is stored.

B’s update didn’t change the row. It added a second version with xmin 102, and stamped the first one with xmax 102.

When A reads, it checks 102 against its snapshot. 102 is still running, so the new version doesn’t count yet and the old one hasn’t been replaced. A gets 50 without waiting for anyone and without anyone waiting for A.

When B rolls back, Postgres doesn’t go back and repair the row. It changes one entry in its commit log, pg_xact, from in progress to aborted. From then on every transaction treats 102’s versions as if they had never been written.

The dead version stays on disk until vacuum removes it. That’s why a rollback in Postgres is fast however much the transaction wrote.

There’s a worse cousin: overwriting data someone else hasn’t committed. If A had tried to update the balance while B’s change was pending, A would have waited on B’s row lock until B finished.

No level in Postgres allows dirty writes. Without that rule, a rollback wouldn’t even know which value to go back to.

READ COMMITTED takes a new snapshot for every statement. That’s cheap, and a long transaction always sees recent data. It also means the transaction as a whole doesn’t belong to any single point in time.

Part 3 shows what goes wrong when a transaction adds up two reads.