ACID, isolation & MVCC

The guarantees that make concurrent databases trustworthy.

Why transactions are trustworthy

Transactions give the ACID guarantees:

Atomicity — all statements commit or none do.
Consistency — constraints hold before and after.
Isolation — concurrent transactions don't see each other's half-finished work.
Durability — once committed, it survives a crash.

Isolation levels trade strictness for concurrency: READ COMMITTED (Postgres default — you only see committed data), REPEATABLE READ (a stable snapshot for the whole transaction), and SERIALIZABLE (as if transactions ran one at a time). Looser levels permit anomalies — non-repeatable reads, phantom rows.

Postgres achieves this with MVCC (multi-version concurrency control): writers create new row versions instead of overwriting, so readers never block writers and writers never block readers. Explicit locks (SELECT ... FOR UPDATE) exist for when you must serialize a hot row, and two transactions waiting on each other cause a deadlock, which Postgres detects and aborts one of.

A non-repeatable read. T1 never wrote anything and never did anything wrong — it simply asked twice, and READ COMMITTED took a fresh snapshot for the second query. REPEATABLE READ pins one snapshot for the whole transaction and both reads return 14.99.

A phantom: not a changed row but a new one that matches a WHERE you already ran. It is the anomaly that row-level locking cannot prevent, because the row did not exist to be locked. SERIALIZABLE is what rules it out.

Read it as what you are buying. Postgres never permits a dirty read at any level, so the real choice is how much of the second and third columns you are willing to pay for — SERIALIZABLE also aborts transactions, and your code must be ready to retry them.