Lesson 18 · Transactions & concurrency

ACID & isolation levels

The guarantees a transaction gives — and how much it hides from its neighbours.

Your win: define ACID and name Postgres's three isolation levels with what each one guarantees — a staple interview question, built on the MVCC snapshots from Lesson 6.

ACID in one line each

Atomicity

All-or-nothing — commit together or roll back together (Lesson 4).

Consistency

Constraints (Lesson 2) always hold; a tx moves the DB from one valid state to another.

Isolation

Concurrent transactions don't corrupt each other — the subject of this part.

Durability

Committed changes survive a crash — via the WAL (Lesson 8).

Isolation levels: how much you see of others

An isolation level controls which concurrent changes your transaction can observe. Under the hood it's just which MVCC snapshot you read from (Lesson 6). Postgres offers three usable levels:¹

Level	Sees	Prevents
Read Committed (default)	a fresh snapshot per statement of committed data	dirty reads
Repeatable Read	one snapshot fixed at transaction start	+ non-repeatable & phantom reads
Serializable	as if transactions ran one at a time	+ write skew (all anomalies)

Two facts interviewers reward (1) Postgres never allows dirty reads — even at Read Committed (MVCC only shows committed data). (2) Postgres's Repeatable Read is stronger than the SQL standard — it also prevents phantom reads. Higher level = fewer anomalies but more conflicts (Serializable may abort a tx).

Anchor database.ExecInTx runs at the default Read Committed (plain BEGIN). The only isolation-aware helper is ExecInTxWithRetry, which retries on SerializationFailure (SQLSTATE 40001) — the error a Serializable transaction raises when it can't be made to look serial. So the retry helper exists specifically for code that opts into the strictest level (Lesson 20).

PostgreSQL — Transaction Isolation

The authoritative chapter on the three levels, the anomalies each prevents, and how they map onto MVCC snapshots.

→ postgresql.org/docs — Transaction Isolation

Check yourself (from memory)

Q1. Postgres's default isolation level is…

Read Committed — a fresh snapshot per statement. (Postgres has no Read Uncommitted; it never shows dirty data.)

Q2. A higher isolation level gives you…

Stricter isolation prevents more anomalies but conflicts more — Serializable can abort a tx (retry it).

Q3. Isolation levels are built on top of…

A level decides which MVCC snapshot you read from (Lesson 6) — per-statement, per-transaction, or serial-equivalent.

Name Postgres's three isolation levels and what each guarantees.

recall, then click to reveal

READ COMMITTED (default) — a fresh snapshot per statement of committed data; no dirty reads, but non-repeatable/phantom reads possible. REPEATABLE READ — one snapshot fixed at tx start (Postgres also prevents phantoms — stronger than the SQL standard). SERIALIZABLE — behaves as if transactions ran one at a time (via SSI), catching write skew; conflicts abort with SQLSTATE 40001 to retry. All built on MVCC snapshots. Postgres never permits dirty reads. Our ExecInTx = Read Committed; ExecInTxWithRetry retries 40001.

Want the precise definitions of non-repeatable read vs phantom read, with a worked example each? Ask me.

1. PostgreSQL — Transaction Isolation.