Isolation Levels and Concurrency Anomalies

Isolation levels define which concurrent histories a transaction may observe, not a linear scale from unsafe to safe. PostgreSQL read committed gives each statement a fresh snapshot; repeatable read holds a transaction snapshot and prevents additional anomalies; serializable detects dangerous structures and aborts a transaction so the application can retry the entire unit.

What will you be able to do?

• Explain read committed, repeatable read, and serializable behavior.
• Reproduce nonrepeatable outcomes, lost-update patterns, and write skew.
• Select isolation from a stated invariant.
• Implement bounded retry for serialization failures.

Prerequisites and mental model

Two individually valid transactions can form an invalid combined history. Isolation protects histories; row constraints protect individual states.

Which level should you use?

• Read committed is PostgreSQL’s default; each command sees committed data as of its start.
• Repeatable read provides one snapshot and in PostgreSQL prevents phantom-like changes in that snapshot, but write skew remains possible.
• Serializable aims for equivalence to some serial order and may return SQLSTATE 40001 for retry.
• Read uncommitted behaves like read committed in PostgreSQL.

Example invariant: at least one qualified agent remains on duty. Two transactions each see two on-duty agents and independently turn one off. Row checks pass, combined invariant fails: write skew.

At serializable, one may abort. The application must retry the whole transaction with a fresh snapshot, bounded attempts, jitter, and idempotent external behavior.

Why does this matter in AI-native systems?

Two agent workflows may approve conflicting changes from the same stale context. A model’s reasoning cannot detect a concurrent commit it never saw. Use expected versions, locks, or serializable transactions for the invariant; revalidate authorization and state after the model response.

Prove it worked

Create a disposable on_call table and reproduce write skew in two sessions under repeatable read. Repeat at serializable and capture 40001. Roll back the lab. Document which transaction unit must retry and why retrying only the final statement is wrong.

Production judgment

• Higher isolation can increase aborts, so transactions must stay short.
• Retrying after deadlock or serialization failure is not the same as retrying every error.
• Unique constraints and atomic conditional writes are often simpler than broad serializable transactions.
• External effects must occur after commit or be coordinated idempotently.

Common mistakes

• Serializable error shown to user: no retry policy → retry bounded whole unit.
• Only failed statement retried: old snapshot retained → restart transaction.
• Read then write assumes exclusivity: no lock/version/isolation → use explicit concurrency control.

AI Pair-Programmer Prompt

Hands-on exercise

Model a ticket with a limited remaining service-credit balance and two simultaneous redemptions. Produce one atomic conditional-update solution and one serializable solution.

Acceptance criterion: concurrent tests never make balance negative, and serialization retries do not double-record redemption.

Knowledge check

1. When does read committed take snapshots?
2. What does serializable do when it cannot preserve a serial history?
3. Why retry the whole transaction?

---

Answers

1. At the start of each statement.
2. Aborts a transaction with a serialization failure.
3. The old transaction snapshot and decisions are no longer valid.

Completion checklist

• Isolation is chosen from an invariant.
• A two-session test exposes the race.
• Retry is bounded and SQLSTATE-specific.
• External effects cannot duplicate on retry.

Primary references

• Transaction isolation
• Serializable consistency