PostgreSQL isolation levels and the anomalies they prevent

A transaction groups operations, but isolation defines what one transaction can observe while other transactions are committing changes. If two users reserve the last seat, update the same balance, or claim one job, the database needs a rule for their interleaving. Without that rule, each transaction can appear correct in isolation while the combined result violates a business invariant. Start by writing the invariant in plain language before choosing an isolation level.

PostgreSQL uses multiversion concurrency control so readers can usually proceed without blocking writers. That improves throughput, but it does not mean every statement sees one permanent snapshot. Different isolation levels provide different snapshots and conflict behavior. Application code must also handle serialization failures, deadlocks, and lock timeouts. A database that can reject a transaction is safer than one that silently commits an impossible result, but the client must retry the right work.

Read committed is PostgreSQL's default. Each statement sees rows committed before that statement began, so two SELECT statements in one transaction can observe different committed data. This is often appropriate for short CRUD operations where each statement can use current state. It is not enough when a read followed by a write assumes that the read remains true until commit. A second transaction can change the predicate between those statements.

Use a single atomic UPDATE or INSERT with a constraint when possible. An UPDATE that includes the invariant in its WHERE clause lets the database decide whether the current row still qualifies. Check the affected row count rather than trusting a value read earlier. This pattern is often faster and easier to reason about than raising the isolation level for every request.

Repeatable read gives a transaction a stable snapshot of committed rows. A later SELECT sees the same snapshot as an earlier SELECT, which prevents non-repeatable reads and many phantom-style surprises. If a concurrent transaction changes a row that your transaction later tries to update, PostgreSQL can raise a serialization failure rather than applying a write based on stale assumptions. The application should retry the complete transaction with a bounded attempt count.

A stable snapshot does not make external side effects transactional. If a transaction sends an email or calls a payment provider before commit, a retry can repeat the side effect. Keep side effects behind an outbox or an idempotency key. Isolation protects database observations; it does not undo messages sent to the outside world.

Serializable isolation asks PostgreSQL to produce a result equivalent to some serial ordering of transactions. It may abort a transaction when concurrent reads and writes could create an anomaly. This is powerful for invariants that span multiple rows, such as ensuring that a schedule has no overlapping allocation. It also requires careful retry behavior and capacity planning because conflicts can increase under contention.

Do not treat serializable as a magic fix for slow or poorly scoped transactions. A transaction that holds a snapshot while making network calls increases conflict risk and resource use. Keep the transaction small, access rows in a consistent order, and index predicates so the database can detect conflicts efficiently. Measure serialization failures and identify which business operation causes them.

SELECT FOR UPDATE communicates that a transaction intends to change the selected rows and should serialize with other lockers. It is useful for claiming a scarce row or moving a state machine through a guarded transition. Choose NOWAIT when the caller should fail immediately, or SKIP LOCKED when a worker can safely take another item. The lock must be held until commit, so keep the transaction bounded.

Locking rows is not a substitute for constraints. Two transactions can lock different rows and still violate a cross-row rule. Use a unique or exclusion constraint where the database can enforce the invariant directly. If an invariant is too complex for a constraint, combine a stable lock key, a clear transaction order, and a retry path. Document what the lock protects so a future query does not accidentally bypass it.

A deadlock occurs when transactions hold locks the other needs. PostgreSQL detects the cycle and aborts one transaction. The correct response is to retry the full transaction after a small jittered delay, not to retry only the failed statement inside a partially changed transaction. Reduce deadlocks by accessing tables and rows in a consistent order, keeping transactions short, and avoiding user-driven waits while locks are held.

Serialization failures are expected under repeatable read and serializable workloads. Classify them separately from validation errors and permanent database failures. Set a maximum retry budget and expose a clear temporary-unavailable response if the budget is exhausted. Include operation name and attempt count in internal telemetry, but do not log sensitive transaction values.

A unit test that runs one transaction at a time cannot prove an isolation policy. Create a controlled test that starts concurrent transactions with barriers, performs the conflicting reads and writes, and asserts the allowed outcomes. Test the retry path with an injected serialization failure and verify that no external side effect occurs twice. Include a lock timeout and a deadlock fixture if the operation is important enough to page on.

Choose isolation from the invariant, not from a slogan. Read committed plus atomic updates is often the best default. Repeatable read or serializable is appropriate when a stable snapshot or cross-row conflict detection is required. Explicit locks and constraints make intent visible. When an operator can explain which anomaly the transaction prevents and how the client retries, concurrency becomes a design property instead of a production mystery.

Write the invariant a transaction protects before selecting an isolation level. Then create a concurrent test that attempts the anomaly you fear: a lost update, write skew, duplicate allocation, or inconsistent read. Inspect lock waits and retry behavior under load, because a stronger level can preserve correctness while increasing aborts. The final choice should name the constraint, the expected contention, and the safe response when PostgreSQL asks the transaction to retry.

Write the invariant before choosing isolation. For a finite allocation, combine a transaction with a constraint or row lock; for a cross-row invariant, consider serializable and retry logic. Keep transaction scope short and make the retry boundary include the complete business operation, not only the final statement.

Run concurrent transactions that attempt lost updates, write skew, duplicate allocation, and inconsistent reads. Assert the invariant after every interleaving and capture serialization failures, deadlocks, lock waits, and retry count. Test the production connection pool and timeout settings because an isolation choice that is correct but always times out is still a product failure.

Monitor aborted transactions, lock duration, deadlocks, and transaction age. Keep a bounded retry policy with jitter and a user-safe conflict response. If contention rises, reduce transaction scope or move work to a queue rather than weakening isolation blindly. Document the invariant and the recovery action beside the code that enforces it.

Use PostgreSQL transaction isolation, locking, and serialization-failure documentation. Compare the database guarantee with the business invariant and the application's retry semantics.