Optimistic vs Pessimistic Locking in JS ORMs (2026 Guide)

A tiny coffee‑shop inventory service once lost 300 cups in a single weekend. Two Lambda functions read the same stock row at 15 copies/second, both thought they had a free cup, and each wrote “‑1”. The final count was off by 2 and the owner started receiving angry emails. The root cause? A race condition that slipped through because the team relied on a naïve “read‑then‑write” pattern without any lock protection.

Optimistic vs Pessimistic Locking in JavaScript ORMs: a quick snapshot

Optimistic locking assumes transaction conflicts are rare and uses a version check (like a timestamp or incrementing number) at commit time to detect collisions, throwing an error if data changed. Pessimistic locking assumes conflicts are common and exclusively locks the database row for the entire transaction duration using SQL clauses like SELECT FOR UPDATE. JS ORMs like Prisma and Sequelize support both patterns.

The Problem of Concurrent Database Writes

When two or more requests touch the same row without coordination, you get a lost update. The classic sequence is:

1. Request A reads balance = 100.
2. Request B reads balance = 100.
3. A writes balance = 90.
4. B writes balance = 95.

The final value should be 85, but it ends up 95. In a high‑traffic API, such anomalies cascade into financial loss, inventory miscounts, or data corruption. Modern ORMs abstract away SQL, but they cannot magically prevent concurrent writes; you must explicitly choose a concurrency control strategy.

“Optimistic locking assumes conflicts are rare and are cheaper to handle in the exception case. Pessimistic locking assumes they are common.” – Martin Fowler, Patterns of Enterprise Application Architecture

The Datadog 2022 report warned that naïve optimistic locking without retries caused a 15 % rise in transaction failures at 1k RPS. That statistic alone justifies a deeper dive.

Understanding Pessimistic Locking: How It Works

Pessimistic locking tells the database, “Give me this row and don’t let anyone else touch it until I’m done.” PostgreSQL implements it with SELECT … FOR UPDATE (exclusive) or FOR SHARE (shared). The lock lives as long as the transaction’s connection stays open.

Pros:

• Guarantees serializable access for the locked rows.
• Simple mental model – you read, you lock, you write, you commit.

Cons:

• Holds a DB connection; in pooled environments this can exhaust the pool.
• In serverless, where functions spin up/down quickly, a lock can linger after a cold start, leading to deadlocks.
• Slower under high contention because each waiting transaction is blocked.

Pessimistic lock with Sequelize

// sequelize@6.35.0, node >=18
import { Sequelize, DataTypes } from 'sequelize';

const sequelize = new Sequelize(process.env.PG_URL, {
  pool: { max: 20, min: 2, idle: 10000 },
});

const Account = sequelize.define('Account', {
  balance: DataTypes.INTEGER,
});

async function debit(accountId, amount) {
  const t = await sequelize.transaction({ isolationLevel: Sequelize.Transaction.ISOLATION_LEVELS.READ_COMMITTED });
  try {
    // Acquire an exclusive row lock
    const acct = await Account.findOne({
      where: { id: accountId },
      lock: t.LOCK.UPDATE,          // SELECT … FOR UPDATE
      transaction: t,
    });

    if (acct.balance < amount) throw new Error('Insufficient funds');

    acct.balance -= amount;
    await acct.save({ transaction: t });
    await t.commit();
  } catch (err) {
    await t.rollback();
    throw err;
  }
}

Pessimistic lock with Prisma

// prisma@5.0.0, node >=18
import { PrismaClient, Prisma } from '@prisma/client';
const prisma = new PrismaClient();

async function credit(accountId: number, amount: number) {
  await prisma.$transaction(async (tx) => {
    // SELECT … FOR UPDATE via raw query because Prisma 5+ doesn't expose lock API directly
    const row = await tx.$queryRaw<
      { id: number; balance: number }[]
    >`SELECT id, balance FROM "Account" WHERE id = ${accountId} FOR UPDATE`;

    const account = row[0];
    if (!account) throw new Error('Account not found');

    await tx.account.update({
      where: { id: accountId },
      data: { balance: account.balance + amount },
    });
  });
}

My take: If your service runs on a managed Postgres instance with a robust connection pool, pessimistic locks are a reliable safety net for high‑contention endpoints like “reserve inventory”. However, they should never be the default for every CRUD operation.

Understanding Optimistic Locking: How It Works

Optimistic locking bets that collisions are infrequent. Each row carries a version (or updatedAt) column. When you read a row, you remember its version. At update time you include WHERE version = :oldVersion. If another transaction already changed the row, the UPDATE affects zero rows, and you throw a conflict error.

Pros:

• No long‑living DB connections; every request can use a short transaction.
• Scales horizontally; many workers can operate on the same table simultaneously.
• Works smoothly in serverless and edge runtimes.

Cons:

• You must write retry logic; otherwise the user sees a generic “conflict” error.
• Stale reads still happen, so you need to re‑fetch after a conflict.
• Not suitable when contention is consistently high – you’ll waste cycles retrying.

Optimistic lock with Prisma (built‑in support)

// prisma@5.0.0
import { PrismaClient } from '@prisma/client';
const prisma = new PrismaClient();

async function transfer(fromId: number, toId: number, amount: number) {
  const maxRetries = 5;
  let attempt = 0;

  while (attempt < maxRetries) {
    attempt++;
    try {
      await prisma.$transaction(async (tx) => {
        const [from, to] = await Promise.all([
          tx.account.findUnique({ where: { id: fromId } }),
          tx.account.findUnique({ where: { id: toId } }),
        ]);

        if (from!.balance < amount) throw new Error('Insufficient funds');

        // Prisma automatically includes the `version` column in the WHERE clause
        await tx.account.update({
          where: { id: fromId, version: from!.version },
          data: { balance: { decrement: amount } },
        });

        await tx.account.update({
          where: { id: toId, version: to!.version },
          data: { balance: { increment: amount } },
        });
      });
      // Success – break out of loop
      break;
    } catch (e: any) {
      if (e.code === 'P2025') {
        // Conflict: re‑try with exponential backoff
        await new Promise(r => setTimeout(r, 2 ** attempt * 100));
      } else {
        throw e;
      }
    }
  }
}

Optimistic lock with Sequelize

// sequelize@6.35.0
import { Sequelize, DataTypes } from 'sequelize';
const sequelize = new Sequelize(process.env.PG_URL);
const Account = sequelize.define('Account', {
  balance: DataTypes.INTEGER,
  version: { type: DataTypes.INTEGER, defaultValue: 0 },
}, {
  version: true, // enables optimistic locking
});

async function updateBalance(id, delta) {
  const maxAttempts = 4;
  for (let i = 0; i < maxAttempts; i++) {
    const acct = await Account.findByPk(id);
    if (!acct) throw new Error('Not found');
    acct.balance += delta;
    try {
      await acct.save(); // Sequelize adds WHERE version = oldVersion
      return;
    } catch (err) {
      if (err.name === 'SequelizeOptimisticLockError') {
        await new Promise(r => setTimeout(r, 2 ** i * 120));
      } else {
        throw err;
      }
    }
  }
  throw new Error('Failed after retries');
}

Optimistic lock with TypeORM

// typeorm@0.3.17
import { DataSource } from 'typeorm';
import { Account } from './entity/Account';

const ds = new DataSource({
  type: 'postgres',
  url: process.env.PG_URL,
  entities: [Account],
});
await ds.initialize();

async function adjust(id: number, delta: number) {
  const repo = ds.getRepository(Account);
  for (let attempt = 0; attempt < 3; attempt++) {
    const acc = await repo.findOneBy({ id });
    if (!acc) throw new Error('Missing');

    acc.balance += delta;
    try {
      await repo.save(acc); // version column handled automatically
      return;
    } catch (e) {
      // @ts-ignore
      if (e?.code === 'ER_LOCK_DEADLOCK') {
        await new Promise(r => setTimeout(r, 2 ** attempt * 150));
      } else {
        throw e;
      }
    }
  }
}

Practical Implementation in Popular JS ORMs

ORM	Pessimistic API	Optimistic API	Recommended DB version
Prisma	Raw query with FOR UPDATE	Built‑in version field + transaction	Postgres 13+
Sequelize	lock: transaction.LOCK.UPDATE	version: true model option	Postgres 12+
TypeORM	queryRunner.lock('pessimistic_write')	@VersionColumn() on entity	Postgres 13+

Prisma checklist

1. Add a @@version column in schema:

   model Account {
     id      Int    @id @default(autoincrement())
     balance Int
     version Int    @default(0) @ignore // version column for optimistic lock
   }

1. Use $transaction for atomic groups.
2. Wrap the call in a retry loop (see transfer example).

Sequelize checklist

1. Enable optimistic locking in model definition.
2. For pessimistic paths, start a transaction and set lock: t.LOCK.UPDATE.
3. Remember to release the transaction in a finally block.

TypeORM checklist

1. Decorate the version field: @VersionColumn() version: number;.
2. For pessimistic locks, acquire a QueryRunner and call await queryRunner.startTransaction(); then await queryRunner.manager.findOne(Entity, { lock: { mode: 'pessimistic_write' } });.
3. Always await queryRunner.release(); to avoid dangling connections.

Performance & Scalability Trade‑offs: When to Use Which

Scenario	Contention Level	Preferred Lock	Reasoning
High‑throughput inventory reservation	High	Pessimistic	Guarantees no double‑bookings
User profile updates (rare conflicts)	Low	Optimistic	Minimal DB round‑trips
Financial transfers across micro‑services	Medium	Optimistic + retry	Keeps latency low, retries cheap
Batch jobs that lock many rows	Very high	Pessimistic	Simpler to reason about deadlocks

The Datadog finding reminds us: without a retry strategy, optimistic locks become a performance liability. For a robust implementation, follow the “Building a Retry Mechanism with Exponential Backoff in Node.js” tutorial (internal link).

Mermaid diagram – lock decision flow

flowchart LR
    A[Start request] --> B{Contention level?}
    B -->|High| C[Pessimistic lock]
    B -->|Low| D[Optimistic lock]
    C --> E[Commit or rollback]
    D --> F{Conflict?}
    F -->|Yes| G[Retry with backoff]
    F -->|No| E

Real‑World Case Studies: Handling Lock Contention at Scale

Case 1 – E‑commerce flash sale (nileshblog.tech) During a 2023 Black Friday flash sale, the shop’s checkout service handled 2 k RPS. Initially it used optimistic locking on the orders table, but the conflict rate hit 30 %. Implementing a hybrid approach—pessimistic lock only on the stock row while keeping optimistic updates for user profiles—cut aborts by 85 % and reduced latency from 120 ms to 68 ms. The team posted a post‑mortem on their blog, highlighting the importance of a per‑entity strategy.

Case 2 – SaaS subscription billing A subscription platform built on Vercel serverless functions stored billing cycles in PostgreSQL. Because each function executed in isolation, a pessimistic lock held for the whole request caused “deadlock detected” errors when multiple instances tried to lock the same invoice row. Swapping to optimistic locking with a version column and a 3‑attempt exponential backoff eliminated deadlocks entirely. The code lives in an open‑source repo that references the official PostgreSQL row locking documentation.

Case 3 – Real‑time game leaderboard A multiplayer game used a Redis‑backed distributed lock service to protect the leaderboard while still applying optimistic updates to the player’s score table. The combination kept the leaderboard consistent without sacrificing the low‑latency updates needed for a fast‑paced game. The pattern is described in the “Advanced Go Concurrency Patterns for Microservices” article, which shares a similar strategy using Go’s channels.

Beyond Simple Locks: Patterns for High‑Concurrency Systems

1. Distributed lock services – When you cannot guarantee a single DB instance, tools like Redlock (Redis) or Etcd provide a lease‑based lock that works across serverless containers.

1. Idempotency keys – Coupled with optimistic locking, an idempotency token guarantees that a retry does not duplicate the effect. See the internal guide on “Implementing Idempotency Keys for API Design” for a practical pattern.

1. Versioned APIs – Expose a version field in your REST/GraphQL schema so clients can perform conditional updates (If-Match header).

1. Read‑replica “stale‑while‑revalidate” – Serve reads from replicas and only apply writes through a leader node that enforces pessimistic locks for hot rows.

1. Saga orchestration – For multi‑service transactions, break the work into a series of compensating actions instead of a single DB transaction. This reduces the need for long‑held row locks.

When you combine these patterns, you can build a system that stays consistent even under 10k RPS spikes while still leveraging the ergonomic APIs of modern JavaScript ORMs.

Common Errors & Fixes

Error you see	Why it happens	How to fix
SequelizeOptimisticLockError thrown on every save	Version column not incremented because the model lacks version: true.	Add version: true to the model definition and ensure the column exists in DB.
Transaction deadlock reported by Prisma (P2025)	Pessimistic lock held while another transaction tries the same row.	Reduce lock scope, avoid nested transactions, or switch to optimistic locking for that endpoint.
Serverless function times out with a lock held	The function’s execution time exceeds the DB connection’s timeout, leaving the lock dangling.	Use optimistic locking, or move the critical section into a separate microservice with a persistent connection pool.
Lost updates after a conflict retry fails	Retry logic does not re‑fetch the latest row before applying the update.	In the retry loop, always query the row again after a conflict error, then apply changes based on the fresh data.
High latency on read‑heavy endpoints with pessimistic locks	Unnecessary lock acquisition slows down reads.	Switch reads to optimistic mode or use FOR SHARE (shared lock) if you only need to prevent deletions.

Frequently asked questions

—

If you’ve spotted a pattern that works better for your stack, or you ran into a quirky lock‑related bug, drop a comment below. Sharing your experience helps the community avoid the same pitfalls and builds a richer knowledge base for all JavaScript backend developers. Happy coding!