Postgres Isolation Levels Explained Through Real Race Conditions

If your background is Mongo and Express, you may have leaned on application-level locking or simply hoped concurrent requests would not collide. PostgreSQL gives you real ACID transactions with configurable isolation, which means you can let the database enforce correctness instead of bolting it on yourself. The catch is that isolation is not a single switch; it is a spectrum of guarantees, each permitting certain anomalies in exchange for performance. Read Committed, the default, prevents some problems but allows others that surprise people debugging financial logic or inventory counts. Understanding the named anomalies, dirty reads, non-repeatable reads, phantom reads, and write skew, lets you reason precisely about what can go wrong under concurrency. Once you can name the failure mode, choosing the right isolation level or locking strategy becomes a deliberate decision rather than a hopeful guess, and your concurrent code stops corrupting data under load.

What A Transaction Actually Guarantees

A transaction wraps multiple statements into a single unit that either fully commits or fully rolls back, and Postgres guarantees this atomicity even if the server crashes mid-operation. Beyond atomicity, the ACID acronym promises consistency, isolation, and durability. Consistency means committed data respects all your constraints and foreign keys. Durability means once Postgres says committed, the data survives a power loss because it was flushed to the write-ahead log. Isolation, the property we care about here, governs how concurrent transactions perceive each other's in-flight changes. Wrapping a debit and a credit in one transaction ensures money never vanishes between the two updates. For Mongo developers used to single-document atomicity only, this multi-statement guarantee is liberating: you express invariants once, and the database refuses to leave the world in a half-finished state regardless of how requests interleave.

Read Committed, The Default

Postgres defaults to Read Committed isolation, where each statement sees a fresh snapshot of data committed before that statement began. This prevents dirty reads, so you never observe another transaction's uncommitted changes. However, within one transaction two identical SELECT statements can return different results if another transaction commits in between, an anomaly called a non-repeatable read. For many web requests this is perfectly acceptable, since a single quick query rarely re-reads the same row. Problems appear in longer transactions that read a value, make a decision, then write based on what they read, because the value may have changed under them. Read Committed is fast and rarely causes serialization conflicts, which is why it is the sensible default for typical CRUD workloads, but you must know its limits before relying on read-then-write patterns inside it.

The Lost Update Problem

The classic concurrency bug is the lost update, and it bites people building counters, inventory, or wallet balances. Imagine two requests both read a stock count of ten, both subtract one in application code, and both write back nine. One decrement vanished; the count should be eight. Under Read Committed this happens silently because each transaction read the same starting value before either wrote. The naive fix of reading, computing in Node, then writing is exactly what fails. Solutions include performing the arithmetic in a single SQL UPDATE that reads and writes atomically, taking an explicit row lock with SELECT FOR UPDATE so the second reader waits, or escalating to a stricter isolation level that detects the conflict. Recognizing this pattern in your own code is the first defense, because the failure only manifests under real concurrent load.

Repeatable Read And Snapshots

Repeatable Read strengthens guarantees by giving the entire transaction a single consistent snapshot taken at its first statement, so every read sees the same data no matter what others commit meanwhile. This eliminates non-repeatable reads and, in Postgres specifically, most phantom reads too. The trade-off is that if your transaction tries to update a row another transaction modified after your snapshot, Postgres aborts yours with a serialization failure rather than risk corruption. That means your application must be ready to catch the error and retry the whole transaction. Repeatable Read suits reports and multi-step reads that need a stable view of the world, like generating an invoice from several tables that must agree. The mental shift for newcomers is accepting that retries are normal and building a retry loop, not treating the serialization error as a bug.

Serializable And Write Skew

Serializable is the strictest level, guaranteeing that concurrent transactions produce a result equivalent to running them one after another in some order. It catches anomalies the weaker levels miss, most notably write skew. Write skew happens when two transactions each read overlapping data, see a condition holds, and each writes a change that individually respects the rule but together violates it. A textbook case is two doctors each checking that at least one colleague remains on call, then each taking themselves off, leaving nobody covering. Repeatable Read would allow this because neither transaction modified the same row the other read. Serializable detects the dangerous dependency and aborts one transaction. The cost is more serialization failures and thus more retries, so you reserve it for invariants that genuinely span multiple rows and cannot be expressed as a single constraint.

Explicit Locking With FOR UPDATE

Sometimes you do not want stricter isolation across the whole transaction; you want to lock specific rows you are about to modify. SELECT FOR UPDATE takes a row-level lock so any other transaction trying to lock or update those same rows waits until you commit. This is the pessimistic approach: you assume conflict is likely and serialize access explicitly. It cleanly solves the lost update problem for the wallet or inventory case, because the second request blocks until the first finishes and then reads the updated value. Variants like FOR NO KEY UPDATE and FOR SHARE offer lighter locking when you only need to prevent deletes or concurrent writes. The danger is deadlock when two transactions lock rows in opposite order, so always acquire locks in a consistent order across your codebase to keep the database from aborting one of them.

Optimistic Concurrency Control

The optimistic alternative assumes conflicts are rare and checks for them only at write time, avoiding the blocking that pessimistic locks cause. You add a version column to the row, read it along with the data, and when updating you include a WHERE clause requiring the version to still match what you read, incrementing it in the same statement. If another transaction already bumped the version, your UPDATE affects zero rows, and your application detects this and retries with fresh data. This pattern shines in high-read, low-contention systems and in REST APIs where you cannot hold a database transaction open across an HTTP round trip. It pushes conflict handling into application logic but avoids long-held locks. Prisma and many ORMs support this through optimistic locking helpers, making it a natural fit for Node services that already think in terms of stateless request handling.

Choosing The Right Level

Default to Read Committed for ordinary request handling, where each operation is short and conflicts are uncommon. Reach for explicit SELECT FOR UPDATE locks when you have a clear read-modify-write on specific rows, like decrementing inventory or moving money, because it is targeted and easy to reason about. Escalate to Repeatable Read when a transaction must see a stable snapshot across several reads, accepting that you will add a retry loop. Use Serializable only when correctness depends on an invariant spanning multiple rows that no single constraint can enforce, and budget for more retries. Across all of these, the non-negotiable habit is wrapping retry logic around any transaction that can raise a serialization failure. Picking the level deliberately, based on the specific anomaly you must prevent, is what separates robust concurrent systems from ones that corrupt data mysteriously in production.