Transactions ACID and Isolation Levels

01 The idea

The problem this lesson solves

Transaction A reads a committed value at its statement snapshot.

Prerequisite: earlier lessons introduce the vocabulary and shared examples used here. Follow the module order.

02 Worked example

Follow one worked example

Trace the example in order. Follow the same identities, units and state transitions through the diagram and the events below.

A transaction boundary groups work; the isolation and predicates determine whether concurrent work preserves the intended invariant.

03 The method

Mechanisms decisions and a guided tutorial

Work through the explanation in order, then apply it in the tutorial. Each comparison names a cost as well as a benefit.

Separate ACID's promises

Atomicity means a transaction's changes commit together or not at all. Consistency means constraints and correctly written transaction logic preserve rules; it does not mean every replica instantly agrees. Isolation controls what concurrent transactions can observe. Durability concerns surviving failures after commit, under the configured persistence guarantees. Replication settings can affect which failures preserve an acknowledged transaction. These are different promises, so ‘the database is ACID’ is not an explanation of your booking algorithm.

Choose the isolation behavior you need

PostgreSQL Read Committed takes a fresh snapshot for each statement and does not allow dirty reads. Repeatable Read gives a stable transaction snapshot but still permits serialization anomalies; its implementation prevents phantom reads. Serializable aims for an outcome equivalent to some serial execution and can abort transactions that must be retried. Retry the whole failed transaction with a bounded policy. A transaction around a separate read and unconditional update does not automatically protect a business invariant.

A transaction can contain incorrect logic

Imagine reading an account balance and later writing a replacement value computed from that earlier read. Another transaction can act in between under weaker isolation. Committing both statements together does not prove that the earlier decision stayed valid.

State the business invariant first: balances may not go below zero, seats have one owner, or inventory cannot be oversold. Then choose conditional updates, locks, constraints or serializable transactions that actually defend it. “We use transactions” describes a mechanism, not the complete correctness argument.

Understand the snapshot boundary

At PostgreSQL Read Committed, each statement receives a new snapshot of committed data. Two SELECT statements in the same transaction may therefore see different committed values. Repeatable Read uses a stable transaction snapshot, but that does not automatically remove every serialization anomaly.

Serializable can reject executions that would not match any valid serial order. Such rejection is an expected possible outcome, not proof that the database is broken. Retrying requires a fresh transaction that reruns the reads and decisions. Repeating only the final UPDATE can keep the stale premise that caused the problem.

Keep external effects outside unsafe retries

A transaction retry may run application code more than once. If that code sends an email or charges a card before the database commits, repeating it can duplicate an external effect. Use stable operation identities and an explicit workflow for effects that cannot share the database transaction.

Bound retries and include backoff where appropriate. Treat a genuine business conflict differently from a transient transaction abort. A seat already owned by someone else should not be endlessly retried as if another attempt will make the requirement disappear. Logs and metrics should distinguish aborts, conflicts and infrastructure failures.

Guided tutorial

1. Open two sessions

Use a disposable PostgreSQL database and record each session’s statements.

2. Observe statement snapshots

Read, commit a change in the other session, and read again at Read Committed.

3. Compare Repeatable Read

Begin a fresh transaction at Repeatable Read and repeat the observation.

4. Design retries

Write which errors cause a whole-transaction retry and which return a business conflict.

-- Setup once in a disposable database, before opening both sessions:
CREATE TABLE sd_lab_values (id integer PRIMARY KEY, value integer NOT NULL);
INSERT INTO sd_lab_values VALUES (1,10);
-- Session A:
BEGIN ISOLATION LEVEL READ COMMITTED;
SELECT value FROM sd_lab_values WHERE id = 1;
-- Session B, outside A's transaction:
UPDATE sd_lab_values SET value = 20 WHERE id = 1;
-- Session A again: this reads the newer committed value 20.
SELECT value FROM sd_lab_values WHERE id = 1;
COMMIT;

04 Cheat sheet

Review the decisions

Use this table to explain the mechanism and its limit without memorizing a product name.

05 Where & why

Check the primary references

Use official references for protocol and implementation details. The questions here are teaching prompts.

06 Interview questions

Explain the reasoning out loud

Answer before opening the explanation. The questions progress from vocabulary to decisions and failure behavior.

07 Practice problems

Practice a new case

Solve the new cases before revealing hints. Complete worked solutions follow the set.

Worked solutions