Optimistic Concurrency vs Pessimistic Locking: Choosing the Right Strategy

Two people open the same record, both make a change, and both press save. One save wins, and the other person’s work disappears without an error or a warning. You only find out when a customer asks why their change never stuck.

That bug is called a lost update, and every application that lets several users write to the same data has to deal with it. Two strategies cover most cases: optimistic concurrency and pessimistic locking. They stop the same bug in opposite ways. One lets everyone work and checks for a clash at save time, and the other makes people wait their turn.

In this post you see how each strategy works, run the SQL and Node.js code for both, and get a simple rule for choosing between them.

Quick Answer

Optimistic concurrency assumes conflicts are rare. You let everyone read and edit freely, then check a version number when you save and reject the write if someone else got there first. Pessimistic locking assumes conflicts are likely. You lock the row before you change it, and everyone else waits until you commit. Use optimistic concurrency by default for read-heavy, low-conflict applications, and reserve pessimistic locking for hot rows where a conflict is costly, such as account balances or stock counts.

What Is Concurrency Control?

Concurrency control is the set of techniques that databases and applications use to manage simultaneous access to the same data. Without it, two overlapping updates cause a lost update, where one user’s change silently erases another’s.

• Optimistic concurrency control (OCC) lets operations proceed without locking anything upfront. It checks for conflicts only at the moment you save.
• Pessimistic locking prevents conflicts by locking the data before you change it, so only one user can modify it at a time.

Both approaches work in relational databases, NoSQL databases, and distributed systems. Which one fits depends on how often conflicts actually happen in your application.

The Problem

Imagine two support agents open the same customer ticket at the same time. Agent A changes the status to “Resolved”. Agent B, working from an older copy of the ticket, changes the priority to “High” and saves a few seconds later. With no concurrency control, Agent B’s save overwrites Agent A’s status change, and nobody notices until the ticket reopens unexpectedly.

Two agents read the same ticket, agent A saves Resolved, then agent B saves a stale copy and erases the status change

This silent data loss gets more likely as the number of concurrent users grows. Inventory systems, booking platforms, and shared documents all hit it sooner or later. The question you have to answer is: how do you let several users work on the same data without losing anyone’s changes?

The Solution

Both strategies solve the lost-update problem. They differ in where they pay the cost: optimistic concurrency pays at save time with a possible retry, and pessimistic locking pays upfront by making other users wait. The examples below use PostgreSQL and the pg package for Node.js, and they share one table.

CREATE TABLE products (
  id       integer PRIMARY KEY,
  name     text    NOT NULL,
  quantity integer NOT NULL,
  version  integer NOT NULL DEFAULT 1
);

INSERT INTO products (id, name, quantity, version)
VALUES (101, 'Desk lamp', 50, 3);

How Optimistic Concurrency Works

You implement optimistic concurrency with a version number or a timestamp column on each record. You never lock the row. Instead, you remember the version you read and make the save depend on it.

Optimistic concurrency flow: read the row with its version, edit in the app, save with a version check, then see whether one row or zero rows changed

Step 1: Read the Record With Its Version

SELECT id, name, quantity, version FROM products WHERE id = 101;

Step 2: Make Changes in the Application

Your user edits the quantity locally. Nothing is locked in the database while they do.

Step 3: Save With a Version Check

UPDATE products
SET quantity = 45, version = version + 1
WHERE id = 101 AND version = 3;

If another user already saved the row, its version is no longer 3 and this query updates zero rows. The check and the write happen in a single statement, so the database makes them atomic for you.

Step 4: Handle the Conflict

Your code reads the number of affected rows. Zero rows means a conflict.

import pg from "pg";

const pool = new pg.Pool({ connectionString: process.env.DATABASE_URL });

async function updateQuantity(productId, newQuantity, expectedVersion) {
  const result = await pool.query(
    `UPDATE products
     SET quantity = $1, version = version + 1
     WHERE id = $2 AND version = $3`,
    [newQuantity, productId, expectedVersion]
  );

  if (result.rowCount === 0) {
    throw new Error("Conflict detected: the record was modified by another user");
  }
}

When you catch that error, reload the latest row and ask the user to retry, or merge the non-conflicting changes automatically.

How Pessimistic Locking Works

Pessimistic locking uses database-level locks to block other transactions from touching a row until your transaction finishes. You lock first, change second, and release last.

Pessimistic locking flow: begin a transaction and lock the row, update it, commit to release the lock, then the next waiting transaction runs

Step 1: Start a Transaction and Lock the Row

BEGIN;

SELECT quantity FROM products WHERE id = 101 FOR UPDATE;

The FOR UPDATE clause locks the row. Any other transaction that tries to lock it the same way, or update it, waits. The PostgreSQL explicit locking documentation lists the exact rules.

Step 2: Make the Update

UPDATE products SET quantity = 45 WHERE id = 101;

Step 3: Commit to Release the Lock

COMMIT;

Once you commit, the lock is released and the next waiting transaction proceeds. In Node.js, you wrap the same three steps in one transaction on a single connection:

import pg from "pg";

const pool = new pg.Pool({ connectionString: process.env.DATABASE_URL });

async function reserveStock(productId, amount) {
  const client = await pool.connect();
  try {
    await client.query("BEGIN");

    const { rows } = await client.query(
      "SELECT quantity FROM products WHERE id = $1 FOR UPDATE",
      [productId]
    );
    if (rows[0].quantity < amount) {
      throw new Error("Not enough stock");
    }

    await client.query(
      "UPDATE products SET quantity = quantity - $1 WHERE id = $2",
      [amount, productId]
    );
    await client.query("COMMIT");
  } catch (err) {
    await client.query("ROLLBACK");
    throw err;
  } finally {
    client.release();
  }
}

Keep the time between locking and committing as short as you can, because every other user who wants that row is blocked during that window.

Choosing Between Them

Ask how often two people really edit the same row at the same time, and what a conflict costs you. The table puts the two strategies side by side.

| Aspect | Optimistic Concurrency | Pessimistic Locking |

| :---- | :---- | :---- |

| Assumption | Conflicts are rare | Conflicts are likely |

| Blocking | None until you save | Blocks other users immediately |

| Conflict handling | Detected at save time, needs a retry | Prevented upfront |

| Best for | Web apps, content systems, low-conflict data | Payments, stock levels, high-conflict data |

| Complexity | Needs a version or timestamp column | Needs careful transaction and lock management |

Use optimistic concurrency when conflicting edits are rare, when you want to keep the system responsive, when your application already handles retries, and when you serve many concurrent readers. Use pessimistic locking when conflicts are frequent on the same records, when correctness matters more than raw throughput, when a retry or lost update is unacceptable, and when transactions are short and predictable.

You do not have to pick one for the whole application. Many teams run optimistic concurrency as the default and apply pessimistic locking only to specific operations, such as taking payment or reducing stock at checkout.

Whichever you choose, a few habits apply to both:

• Add a version or updated_at column to every table that supports concurrent edits.
• Log conflicts, so you can measure how often they happen in production.
• Show users a clear message when a conflict occurs instead of failing silently.
• Set a lock timeout, so a stuck transaction fails instead of blocking others indefinitely.

Trade-offs

Optimistic concurrency is not free. It only works if every write path includes the version check, and every conflict needs a retry or a merge strategy. Under high contention, those retries pile up and can end up slower than a well-managed lock. It is the wrong choice for a row that many users update at once, such as a shared counter or a popular item’s stock.

Pessimistic locking removes that risk by blocking conflicting writes outright, but the wait is a real cost. As more transactions try to lock the same rows, more of them queue, and throughput drops. It also adds a failure mode of its own: a deadlock, where two transactions each hold a lock the other one needs and both wait forever. It is the wrong choice for read-heavy pages, dashboards, and most CRUD screens, where conflicts are rare and locks only add waiting.

Scale matters too. Optimistic concurrency only needs a version check at write time, so it fits distributed systems naturally. A pessimistic lock that depends on a single database can become a bottleneck once several services share it. Neither strategy removes risk, and each one trades one kind of cost for another. The table below lists the problems you are most likely to meet and how teams fix them.

| Problem | What is going on | What to do about it |

| :---- | :---- | :---- |

| Version-conflict errors keep appearing | Many people edit the same record in the same short window, so version checks fail more often than you expect | Narrow what you version-check, for example by tracking individual fields, or move that one hot resource to pessimistic locking |

| Transactions freeze in a deadlock | Two transactions each hold a lock the other needs | Acquire locks in the same order everywhere in your code, and set a lock timeout |

| Locks stay open too long | A transaction holds a lock while it waits on something slow, such as an external API call or user input | Keep locked sections short, and never do slow work while you hold a lock |

| Changes are silently overwritten | There is no version column and no locking, so two writes to the same row cancel each other out | Add a version or updated_at column as a baseline safeguard |

| Users lose edits with no explanation | A save fails because of a conflict, but the interface never says so | Show a specific message, and where it makes sense let the user compare both versions |

PostgreSQL and MySQL both support SELECT ... FOR UPDATE for row-level locks, and SQL Server gets the same effect with the UPDLOCK table hint. Any of them supports a version or updated_at column for optimistic concurrency.

Conclusion

On Monday morning, find the tables where several people can edit the same row and add a version column to each one. Change those UPDATE statements to include WHERE version = ..., check the affected row count, and show users a clear message when the count is zero. Then log how often conflicts happen. If one row turns out to be a hot spot, such as a balance or a stock count, move just that operation to SELECT ... FOR UPDATE and keep the rest optimistic. Need help applying this to your own system? Contact us.