Optimistic vs Pessimistic Locking in NestJS with Drizzle ORM and Postgres

by | Sep 12, 2026 | NestJS, Post | 0 comments

Two requests hit your API at the same moment. Both read the same product row, both see stock: 1, both decide the sale is fine, and both write stock: 0. You just sold one item twice. Nothing threw an error, nothing showed up in the logs, and you won’t hear about it until a customer does.

This is the lost update problem, and it shows up anywhere two writers can touch the same row: inventory, account balances, seat reservations, document editing, job queues. Postgres gives you two families of tools to deal with it: optimistic locking and pessimistic locking. In this post we’ll implement both in a NestJS application with Drizzle ORM, look at a third option that is often better than either, and run a concurrency test that shows exactly where the naive version falls apart.

All of the code is in the companion repo: github.com/mguay22/nestjs-locking.

The Lost Update Problem

Here’s the code most of us write first. Read the product, check the stock in JavaScript, write the new value. It’s even wrapped in a transaction, which feels safe.

// BROKEN ON PURPOSE
private async reserveNaive(productId: string, quantity: number) {
  return this.db.transaction(async (tx) => {
    const product = await tx.query.products.findFirst({
      where: eq(products.id, productId),
    });
    if (!product) throw new NotFoundException();
    if (product.stock < quantity) {
      throw new ConflictException('Insufficient stock');
    }

    await tx
      .update(products)
      .set({ stock: product.stock - quantity })
      .where(eq(products.id, productId));

    const [reservation] = await tx
      .insert(reservations)
      .values({ productId, quantity })
      .returning();
    return reservation;
  });
}

The transaction doesn’t help. Postgres’s default isolation level is READ COMMITTED, which means each statement sees whatever was committed when that statement started. Fifty transactions can all run the SELECT, all see stock: 10, and all run an UPDATE that sets stock to 9. The UPDATEs serialize on the row lock, but each one writes a value computed from stale data.

Optimistic vs Pessimistic: The Mental Model

Both approaches solve this. They differ in when they detect the conflict.

Pessimistic locking assumes conflicts are likely, so it takes a lock on the row before reading it. Everyone else who wants that row waits in line. Nobody ever works with stale data because nobody can read the row while you hold it.

Optimistic locking assumes conflicts are rare, so it doesn’t lock anything. Instead it remembers what version of the row it read, and at write time it says “update this row, but only if it’s still the version I saw.” If someone else changed it, zero rows match, and you retry or report a conflict.

OptimisticPessimistic
Locks held while thinkingNoneRow lock until commit
Conflict detectedAt write timeAt read time (by waiting)
Cost when uncontendedAlmost zeroOne lock acquisition
Cost when contendedRetries, wasted workQueueing, held connections
Works across HTTP requestsYes (send the version to the client)No (locks die with the transaction)
Best forEdit forms, low-contention updatesHot rows, money, inventory under load

That last row matters more than people expect. A user opens an edit form, goes to lunch, and saves an hour later. No database lock survives that. Optimistic locking does, because the version number travels with the form.

Project Setup

The demo is a NestJS 11 app on Bun using Drizzle with the postgres-js driver. The schema has two things worth pointing out: a version column for optimistic locking, and a check constraint so the database refuses negative stock no matter what the application does.

export const products = pgTable(
  'products',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    name: text('name').notNull(),
    stock: integer('stock').notNull().default(0),
    // Bumped on every write. Every write is guarded by
    // WHERE version = <the version we read>.
    version: integer('version').notNull().default(1),
    updatedAt: timestamp('updated_at', { withTimezone: true })
      .notNull()
      .defaultNow(),
  },
  (t) => [check('products_stock_non_negative', sql`${t.stock} >= 0`)],
);

export const reservations = pgTable('reservations', {
  id: uuid('id').primaryKey().defaultRandom(),
  productId: uuid('product_id')
    .notNull()
    .references(() => products.id, { onDelete: 'cascade' }),
  quantity: integer('quantity').notNull(),
  createdAt: timestamp('created_at', { withTimezone: true })
    .notNull()
    .defaultNow(),
});

Drizzle is wired into Nest with a global module that exposes the database under a DRIZZLE token. Services inject it with @Inject(DRIZZLE) private readonly db: Database. If you’ve read my earlier posts, this is the same setup.

Optimistic Locking

There are two flavors of optimistic locking, and they behave differently enough that they deserve separate treatment.

Client-driven: the version travels with the request

This is the edit-form case. The client loaded the product, including its version, and sends the version back when it saves. The update is guarded by that version. If it doesn’t match, we return a 409 Conflict and let the client decide what to do. We deliberately do not retry here: a human needs to look at the newer data.

async update(id: string, dto: UpdateProductDto) {
  const [updated] = await this.db
    .update(products)
    .set({
      name: dto.name,
      version: sql`${products.version} + 1`,
      updatedAt: new Date(),
    })
    .where(and(eq(products.id, id), eq(products.version, dto.version)))
    .returning();

  if (updated) return updated;

  // Zero rows matched. Was it a stale version or a missing product?
  const exists = await this.db.query.products.findFirst({
    where: eq(products.id, id),
    columns: { id: true, version: true },
  });
  if (!exists) throw new NotFoundException(`Product ${id} not found`);

  throw new ConflictException({
    message: `Product was modified by someone else (you sent version ${dto.version}, current is ${exists.version})`,
    currentVersion: exists.version,
  });
}

The whole mechanism is the and(eq(products.id, id), eq(products.version, dto.version)) clause. Postgres evaluates it against the current committed row when it takes the row lock for the UPDATE, so two concurrent saves with the same version can’t both succeed. One bumps the version to 2, the other’s WHERE version = 1 matches nothing.

In practice:

$ curl -X PATCH /products/:id -d '{"name":"widget v2","version":1}'
{"id":"...","name":"widget v2","stock":5,"version":2}

$ curl -X PATCH /products/:id -d '{"name":"widget v3","version":1}'
{"message":"Product was modified by someone else (you sent version 1, current is 2)","currentVersion":2}   [409]

If you prefer HTTP semantics, the same idea maps cleanly onto an ETag response header and an If-Match request header, returning 412 Precondition Failed instead of 409.

Server-driven: read, guard, retry

For the inventory case the client doesn’t know or care about versions. The server reads the row, computes the new stock, and writes with a version guard. If the guard fails, it rolls back and tries again with fresh data.

class StaleVersionError extends Error {}

const MAX_OPTIMISTIC_RETRIES = 5;

private async reserveOptimistic(productId: string, quantity: number) {
  for (let attempt = 1; attempt <= MAX_OPTIMISTIC_RETRIES; attempt++) {
    try {
      return await this.db.transaction(async (tx) => {
        const product = await tx.query.products.findFirst({
          where: eq(products.id, productId),
        });
        if (!product) throw new NotFoundException();
        if (product.stock < quantity) {
          throw new ConflictException('Insufficient stock');
        }

        const [updated] = await tx
          .update(products)
          .set({
            stock: product.stock - quantity,
            version: product.version + 1,
            updatedAt: new Date(),
          })
          .where(
            and(
              eq(products.id, productId),
              eq(products.version, product.version), // <-- the guard
            ),
          )
          .returning({ id: products.id });

        if (!updated) throw new StaleVersionError();

        const [reservation] = await tx
          .insert(reservations)
          .values({ productId, quantity })
          .returning();
        return reservation;
      });
    } catch (error) {
      if (!(error instanceof StaleVersionError)) throw error;
      await sleep(Math.random() * 10 * 2 ** attempt); // full jitter backoff
    }
  }
  throw new ConflictException(
    `Could not reserve stock after ${MAX_OPTIMISTIC_RETRIES} attempts`,
  );
}

A few details that matter:

  • Throwing inside db.transaction rolls it back. Drizzle rethrows whatever you throw, so a custom StaleVersionError is a clean way to abort and signal “retry” to the outer loop without leaking a half-written reservation.
  • Backoff with jitter. Without it, every retrying request wakes up at the same instant and collides again. Full jitter spreads them out.
  • Cap the retries. Under real contention, optimistic locking degrades into a retry storm. Five attempts is plenty. If you’re routinely hitting the cap, you have a hot row and you want pessimistic locking instead.

Pessimistic Locking

Pessimistic locking uses SELECT ... FOR UPDATE. Postgres takes a row-level lock on every row the query returns and holds it until the transaction commits or rolls back. Any other transaction that tries FOR UPDATE (or a plain UPDATE or DELETE) on those rows blocks until you’re done. When it unblocks, it sees your committed changes.

In Drizzle, that’s the .for('update') modifier on the query builder:

private async reservePessimistic(productId: string, quantity: number) {
  return this.db.transaction(async (tx) => {
    // Never wait forever for a lock. SET LOCAL scopes this to the transaction.
    await tx.execute(sql`SET LOCAL lock_timeout = '3s'`);

    const [product] = await tx
      .select()
      .from(products)
      .where(eq(products.id, productId))
      .for('update');

    if (!product) throw new NotFoundException();
    if (product.stock < quantity) {
      throw new ConflictException('Insufficient stock');
    }

    await tx
      .update(products)
      .set({
        stock: product.stock - quantity,
        version: sql`${products.version} + 1`,
        updatedAt: new Date(),
      })
      .where(eq(products.id, productId));

    const [reservation] = await tx
      .insert(reservations)
      .values({ productId, quantity })
      .returning();
    return reservation;
  });
}

Same read-check-write shape as the naive version. The only difference is .for('update'), and it’s enough. Fifty concurrent requests line up on the row lock, each one reads the real current stock, and exactly ten succeed.

Things to know:

Use the query builder, not the relational API. db.query.products.findFirst() has no for update option. Row locks are only available on db.select(). This is the one place in the codebase where you’ll mix the two styles, and it’s worth a comment so nobody “cleans it up” later.

Always set lock_timeout. By default a FOR UPDATE waits forever. One slow transaction holding a hot row can pin every connection in your pool behind it. SET LOCAL lock_timeout = '3s' applies only to the current transaction and turns a pileup into a fast 409. Drizzle’s tx.execute(sql\…`)` is the right tool for session settings like this.

Prefer FOR NO KEY UPDATE when you can. FOR UPDATE also blocks inserts into child tables that reference the locked row via foreign key. If you’re not changing the primary key, .for('no key update') takes a weaker lock that lets those inserts through. It’s a free concurrency win that almost nobody uses.

Lock in a consistent order. If transaction A locks product 1 then product 2, and transaction B locks 2 then 1, Postgres detects the deadlock after deadlock_timeout (1s by default) and kills one of them with error 40P01. Sorting IDs before locking multiple rows prevents this entirely.

NOWAIT: fail fast instead of queueing

Sometimes waiting is the wrong answer. If a request is going to fail anyway once it gets the lock, or if you’d rather shed load than queue it, add NOWAIT. Postgres raises error 55P03 immediately if the row is locked.

.for('update', { noWait: true })

Turning Postgres errors into 409s

Out of the box, 55P03 (lock not available), 40P01 (deadlock), and 40001 (serialization failure) all surface as 500s. They aren’t server errors. They’re the database telling you someone else got there first, and the client should retry.

One subtlety: since version 0.44, Drizzle wraps every driver error in a DrizzleQueryError and puts the original postgres-js error on cause. A filter that catches PostgresError directly will never fire. Catch the wrapper and inspect the cause:

import { DrizzleQueryError } from 'drizzle-orm/errors';
import postgres from 'postgres';

const { PostgresError } = postgres;

const CONFLICT_CODES: Record<string, string> = {
  '55P03': 'Row is locked by another transaction',
  '40P01': 'Deadlock detected',
  '40001': 'Serialization failure',
  '23514': 'Check constraint violated',
};

@Catch(DrizzleQueryError)
export class PostgresErrorFilter implements ExceptionFilter {
  catch(error: DrizzleQueryError, host: ArgumentsHost) {
    const res = host.switchToHttp().getResponse<Response>();
    const cause = error.cause;
    const code = cause instanceof PostgresError ? cause.code : undefined;
    const reason = code ? CONFLICT_CODES[code] : undefined;

    if (reason) {
      return res.status(HttpStatus.CONFLICT).json({
        statusCode: HttpStatus.CONFLICT,
        error: 'Conflict',
        message: reason,
        code,
      });
    }

    return res.status(HttpStatus.INTERNAL_SERVER_ERROR).json({
      statusCode: HttpStatus.INTERNAL_SERVER_ERROR,
      message: 'Internal server error',
      code,
    });
  }
}

Register it with APP_FILTER in your root module and every lock-related failure in the app becomes a 409 with a machine-readable code.

Sometimes You Need Neither: The Atomic Update

Step back and look at what the inventory check actually is: “subtract n from stock, but only if stock is at least n.” That fits in a single SQL statement.

private async reserveAtomic(productId: string, quantity: number) {
  return this.db.transaction(async (tx) => {
    const [updated] = await tx
      .update(products)
      .set({
        stock: sql`${products.stock} - ${quantity}`,
        version: sql`${products.version} + 1`,
        updatedAt: new Date(),
      })
      .where(and(eq(products.id, productId), gte(products.stock, quantity)))
      .returning({ id: products.id });

    if (!updated) {
      const exists = await tx.query.products.findFirst({
        where: eq(products.id, productId),
        columns: { id: true },
      });
      if (!exists) throw new NotFoundException();
      throw new ConflictException('Insufficient stock');
    }

    const [reservation] = await tx
      .insert(reservations)
      .values({ productId, quantity })
      .returning();
    return reservation;
  });
}

A single UPDATE is atomic. When Postgres acquires the row lock it re-evaluates the WHERE clause against the latest committed version of the row, so stock >= quantity can never be satisfied by stale data. There’s no read-then-write window to race through. No version column, no retries, no explicit lock.

This is the fastest of the three in the benchmark, and it’s what you should reach for first when the invariant fits in one statement. It stops fitting the moment you need to read several rows, call another service, or make a decision in application code before writing. That’s when you graduate to optimistic or pessimistic locking.

Bonus: A Job Queue with FOR UPDATE SKIP LOCKED

The other lock modifier Drizzle exposes is skipLocked, and it turns a plain table into a multi-worker job queue with no extra infrastructure.

@Interval(500)
async tick() {
  await this.db.transaction(async (tx) => {
    const [job] = await tx
      .select()
      .from(jobs)
      .where(eq(jobs.status, 'pending'))
      .orderBy(asc(jobs.createdAt))
      .limit(1)
      .for('update', { skipLocked: true }); // <-- the whole trick

    if (!job) return;

    await process(job); // other workers skip this row while we hold it

    await tx
      .update(jobs)
      .set({ status: 'done', workerId: this.workerId, completedAt: new Date() })
      .where(eq(jobs.id, job.id));
  });
}

SKIP LOCKED tells Postgres to silently skip any row another transaction currently holds, instead of waiting for it. Run ten copies of this worker against the same table and each one grabs a different job. No job is processed twice, no worker waits on another, and if a worker crashes mid-job its transaction rolls back and the row goes straight back to pending. For moderate volumes this replaces a message broker entirely.

Running the Race

The repo includes a script that creates a product with 10 units, fires 50 concurrent reservation requests at it, and reports what happened. Here’s the full run:

50 concurrent requests for 10 units

naive               stock 10 ->  3   reservations 50     38ms   OVERSOLD
   50 x 201
optimistic          stock 10 ->  0   reservations 10     75ms   ok
   10 x 201
   40 x 409 Insufficient stock
pessimistic         stock 10 ->  0   reservations 10     21ms   ok
   10 x 201
   40 x 409 Insufficient stock
pessimistic-nowait  stock 10 ->  6   reservations  4     14ms   ok
    4 x 201
   46 x 409 Row is locked by another transaction
atomic              stock 10 ->  0   reservations 10     18ms   ok
   10 x 201
   40 x 409 Insufficient stock

And at 300 concurrent requests for 100 units, where contention really bites:

StrategyWall timeCorrect?
Optimistic480msYes
Pessimistic225msYes
Atomic137msYes

Optimistic is the slowest under heavy contention because it does the most wasted work: every collision is a full transaction that gets thrown away and retried. That’s the tradeoff. When conflicts are rare it’s essentially free. When everyone is fighting over the same row, let the database queue them.

Which One Should You Use?

  • The invariant fits in one UPDATE statement? Use the atomic update. Simplest, fastest, no extra columns.
  • A human edits data and saves later? Client-driven optimistic locking with a version column (or ETag / If-Match). Locks can’t span requests.
  • Low contention, complex logic between read and write? Server-driven optimistic locking with bounded, jittered retries.
  • Hot rows, money, or anything where retry storms would hurt? Pessimistic FOR UPDATE with lock_timeout set. Prefer FOR NO KEY UPDATE if you aren’t changing keys.
  • Work distribution across many consumers? FOR UPDATE SKIP LOCKED.

And regardless of which you pick: add a check constraint. The application should never be the only thing standing between you and a negative balance.

Conclusion

The naive read-check-write pattern is wrong under concurrency, and wrapping it in a transaction doesn’t fix it. Optimistic locking detects conflicts at write time with a version guard and works across HTTP requests. Pessimistic locking prevents conflicts by making writers wait, and Drizzle’s .for('update') with noWait and skipLocked gives you the full Postgres toolkit. And for the common case where the rule fits in one statement, an atomic UPDATE ... WHERE beats both.

Sign up to receive updates on new content & exclusive offers

We don’t spam! Cancel anytime.

Submit a Comment