READ COMMITTED isn't as safe as you think

What READ COMMITTED actually does

READ COMMITTED is one of four isolation levels in the SQL standard. It's Postgres's default. It's also the default in SQL Server and Oracle. MySQL InnoDB defaults to REPEATABLE READ, which is a slightly different story.

What READ COMMITTED prevents is called a dirty read - reading uncommitted data from another transaction. Without isolation, transaction A could read a row that transaction B has changed but not yet committed. If B rolls back, A saw a value that never officially existed. READ COMMITTED closes that hole.

Every read in your transaction only sees data that's already committed at the moment of the read. Uncommitted writes from other transactions are invisible.

That's it. That's the whole promise. Everything else - reading the same row twice and getting different values, running the same query twice and getting different rows, two transactions overwriting each other's changes - READ COMMITTED does nothing about. That last one is the one that costs you.

The bug you've probably shipped

Here's a NestJS endpoint that increments a counter. notifications_unread for a user, seat_usage for a tenant, like_count for a post - pick your favorite. The shape is always the same:

// notifications.service.ts
async incrementUnread(userId: string) {
  const row = await this.repo.findOne({ where: { userId } });
  row.count = row.count + 1;
  await this.repo.save(row);
}

Read the current value. Add one. Save it back. Familiar? I've written this code more times than I want to admit.

Now two requests hit this endpoint at the same moment. Both are wrapped in transactions. Both are running at READ COMMITTED. Here's what happens:

Both transactions read 5. Both computed 6. Both wrote 6. Both committed. The counter went from 5 to 6 instead of 5 to 7.

Notice what didn't happen. No deadlock. No error. No serialization failure. The database did exactly what you asked. You asked for two independent transactions that each read 5 and each wrote 6. It gave you both.

This is a lost update. The name is precise - one of the increments was silently lost. Under low load you'll never see it. Both requests almost never land in the same millisecond. But on a Friday when traffic spikes, the counter starts drifting. Someone runs a reconciliation against the source-of-truth event log and finds you're off by hundreds. And now you're debugging.

Why the transaction wasn't enough

The instinct most of us have - myself included, for years - is that wrapping something in BEGIN / COMMIT makes it atomic against concurrent access. It doesn't. Atomicity is about your transaction being all-or-nothing against failure. Isolation is what governs concurrent access. They're separate properties. ACID's A and I are not the same thing, and READ COMMITTED is a very weak setting on the I axis.

At each SELECT, READ COMMITTED takes a fresh snapshot of "what's committed right now." When transaction B does its SELECT at t=4, the value is still 5 - A hasn't committed yet, so B sees the last committed value. When A commits at t=6 and B's UPDATE fires at t=7, Postgres doesn't check that the row is still 5. B's UPDATE is well-formed SQL. Postgres runs it.

Postgres kept its promise: no dirty reads. What it never promised is that your read-then-write pattern would be safe.

The four ways out

There are four ways to fix this. They're not interchangeable - each has a different failure mode.

Atomic SQL

If your update can be expressed as a pure function of the old value, do it in one SQL statement:

// notifications.service.ts
await this.repo.increment({ userId }, 'count', 1);

Which becomes:

UPDATE notifications SET count = count + 1 WHERE user_id = $1;

The read and the write happen inside a single statement, holding a row lock the entire time. No application-level race window exists. Works at any isolation level. No retries. Fastest option.

This is the best fix when it fits. If your logic really is "increment," "decrement," "append to array," "set updated_at" - always prefer atomic SQL. Application-level read-modify-write is the pattern that creates the bug in the first place.

SELECT FOR UPDATE

Pessimistic locking. Acquire a row-level exclusive lock at the SELECT, hold it until the transaction commits:

// notifications.service.ts
await this.dataSource.transaction(async (manager) => {
  const row = await manager.findOne(Notification, {
    where: { userId },
    lock: { mode: 'pessimistic_write' },
  });
  row.count = row.count + 1;
  await manager.save(row);
});

TypeORM's pessimistic_write compiles to SELECT ... FOR UPDATE. When transaction B tries to lock the same row A already holds, B blocks until A commits. B then unblocks, reads the new value (6), and correctly writes 7.

Use this when the update logic is too complex to express as atomic SQL - you need to read some fields, run business logic, then write. SELECT FOR UPDATE gives you a critical section that behaves the way most of us assumed the plain transaction already did.

The cost: it's blocking. Under high contention on a hot row you'll build a queue of transactions waiting for each other, latencies climb, and if you lock multiple rows in different orders in different code paths, you get deadlocks.

Optimistic locking with a version column

Add a version column and check it in the WHERE clause on every UPDATE:

// notification.entity.ts
@Entity()
export class Notification {
  @PrimaryColumn()
  userId: string;

  @Column()
  count: number;

  @VersionColumn()
  version: number;
}

TypeORM auto-manages the version column. Every UPDATE it generates becomes:

UPDATE notifications SET count = 6, version = 4 WHERE user_id = $1 AND version = 3;

If another transaction updated the row since you read it, the version has moved past 3, your UPDATE matches zero rows, and TypeORM throws an optimistic-lock error. You catch it and retry.

Different failure mode from SELECT FOR UPDATE: no locks, no blocking, but conflicts become retries. Right choice when reads outnumber writes and conflicts are rare - you pay retry cost only when the conflict actually happens, not upfront blocking cost every time.

Raise the isolation level

The last option: opt into REPEATABLE READ. In Postgres, this isn't just what the SQL standard describes - it's snapshot isolation. Your transaction sees a single snapshot of the database from the moment it started. If you try to UPDATE a row that changed since your snapshot, Postgres aborts you:

ERROR: could not serialize access due to concurrent update

You catch the error, retry the transaction, and the second time your snapshot is fresh.

// notifications.service.ts
await this.dataSource.transaction('REPEATABLE READ', async (manager) => {
  // ...same code as before
});

Same retry-on-conflict shape as optimistic locking, but the database does the version check for you across every row you read. Useful when you're doing something like "read many rows, compute a decision, write to one row" and want the database to guarantee nothing changed under you.

The cost: you need a retry wrapper around every transaction that uses REPEATABLE READ. Under heavy contention transactions can retry many times or fail entirely. It's the strongest guarantee, and the one that demands the most operational discipline.

What to actually do

Most codebases end up using two of these four, not all four:

• Atomic SQL for anything expressible as a pure function of the old row.
• SELECT FOR UPDATE for the critical sections where atomic SQL doesn't fit.

That combination handles the majority of cases with minimal retry logic. Version columns come out when you have a specific high-read table where blocking hurts. REPEATABLE READ comes out when you're already committed to retry logic and want the database to enforce consistency across many rows.

The one thing worth internalizing: the plain transaction - the one your ORM opens by default when you call .transaction() - isn't the protection you think it is. Postgres kept its promise. Just not the promise you assumed.