The Practical Developer

Optimistic Locking in Postgres: Stop Losing Data to Race Conditions

May 13, 2026 by The Practical Developer

Two API requests update the same row. One silently disappears. Here is the compare-and-swap pattern that fixes it without adding pessimistic lock contention to your database.

database

postgres

node.js

Close-up of code editor on a laptop screen

Your user opens an order edit page. Their coworker opens the same page. Both change the shipping address. Both hit Save.

The API handles the first request: reads the row, merges the patch, writes it back. The API handles the second request a millisecond later: reads the same original row (because the first write is not yet visible or because the ORM re-fetched the old state), merges a different patch, writes it back. The first user’s change is gone. No error. No log. Just a support ticket tomorrow that reads “my edit disappeared.”

This is not a theoretical race condition. It is the default behavior of most read-modify-write APIs. If your UPDATE statement sets columns to literal values derived from a previous SELECT, the last writer wins every time.

Pessimistic locking with SELECT ... FOR UPDATE fixes the overwrite, but it holds a row lock for the entire transaction. Under load that becomes contention, waits, and deadlocks. Optimistic locking fixes the same problem with zero lock time. The database does the collision detection; your API decides what to do about it.

This post shows the working pattern: a version column, a compare-and-swap UPDATE, a 409 response the client can retry, and a test that proves the race is closed.

The demo: a race condition in 20 lines

Here is the dangerous pattern, wrapped in Express and plain SQL. It looks correct until two requests arrive at the same time.

app.patch('/api/orders/:id', async (req, res) => {
  const { id } = req.params;
  const { shipping_address } = req.body;

  // DANGER: this read is not bound to the write.
  const { rows: [existing] } = await pool.query(
    'SELECT * FROM orders WHERE id = $1',
    [id]
  );

  if (!existing) return res.status(404).end();

  const merged = { ...existing, shipping_address };

  await pool.query(
    'UPDATE orders SET shipping_address = $1 WHERE id = $2',
    [merged.shipping_address, id]
  );

  res.json(merged);
});

Run two PATCH requests in parallel with different addresses. Both read the old row. Both write back different values. The last UPDATE clobbers the first. Postgres did exactly what it was told.

The fix is not “add a transaction.” A transaction around the read and write does not help unless the read holds a lock, which is what we are trying to avoid.

Add a version column

Optimistic locking stores a counter on every row. Every successful UPDATE increments it. The UPDATE clause includes a WHERE version = $knownVersion check. If the row changed since the read, the version no longer matches, zero rows are updated, and the application knows a collision happened.

ALTER TABLE orders ADD COLUMN version INTEGER NOT NULL DEFAULT 1;

-- Backfill existing rows with distinct values so they are not all 1.
UPDATE orders SET version = id WHERE version = 1;

Use an integer, not a timestamp. updated_at can collide if two writes happen in the same millisecond, and clock_timestamp() behavior across transaction boundaries is subtle. Integers are simple and deterministic.

The compare-and-swap update

The UPDATE now carries the version the client read:

UPDATE orders
SET shipping_address = $1,
    version = version + 1
WHERE id = $2
  AND version = $3
RETURNING *;

If RETURNING returns a row, the write succeeded and the version advanced. If it returns nothing, the row changed between read and write. The API returns a 409 Conflict, and the caller can retry from the top with fresh data.

Here is the safe API handler:

app.patch('/api/orders/:id', async (req, res) => {
  const { id } = req.params;
  const { shipping_address, expected_version } = req.body;

  if (typeof expected_version !== 'number') {
    return res.status(400).json({
      error: 'expected_version is required for optimistic locking',
    });
  }

  const { rows } = await pool.query(
    `UPDATE orders
     SET shipping_address = $1,
         version = version + 1
     WHERE id = $2
       AND version = $3
     RETURNING *`,
    [shipping_address, id, expected_version]
  );

  if (rows.length === 0) {
    return res.status(409).json({
      error: 'Conflict',
      detail: 'The order was modified by another request. Please re-fetch and retry.',
    });
  }

  res.json(rows[0]);
});

The client reads the order, remembers version, sends the patch including expected_version, and handles 409 by re-fetching and replaying the user’s intent on fresh data. This is exactly what idempotency keys solve for retries; if you already have an idempotency system, the retry is free. If not, the user sees a clear message instead of a silent overwrite.

Why not use SELECT ... FOR UPDATE?

Pessimistic locking works, but it keeps the row locked until commit. In a busy system that is expensive.

BEGIN;
SELECT * FROM orders WHERE id = $1 FOR UPDATE;
-- ... application logic ...
UPDATE orders SET shipping_address = $1 WHERE id = $2;
COMMIT;

If the application logic involves calling another service, validating a payment, or rendering a PDF, that row is locked the entire time. Other requests for the same order wait. Under load, waits become deadlocks, and deadlocks become retries that add more load.

Optimistic locking assumes collisions are rare. It reads without a lock, does the work, and attempts the write. If a collision happens, the application retries. The database is never held hostage by a slow business rule.

Use pessimistic locking when contention is high and the operation is fast: decrementing inventory, deducting credits, assigning a sequential number. Use optimistic locking when contention is low and the operation is long: editing a CRM record, updating a project plan, saving a draft document.

Handling the retry loop

The simplest client retry is re-read, re-apply, re-submit. But if the client is a browser, pushing that logic to the server gives a cleaner API contract.

Here is a server-side wrapper that retries transparently up to a limit. The caller gets a normal 200 or a 409 if the collision rate is pathological.

async function patchOrderWithRetry(pool, id, patch, maxRetries = 3) {
  for (let attempt = 0; attempt < maxRetries; attempt++) {
    const { rows } = await pool.query(
      'SELECT version, shipping_address FROM orders WHERE id = $1',
      [id]
    );

    if (rows.length === 0) {
      const e = new Error('Not found');
      e.status = 404;
      throw e;
    }

    const { version, shipping_address } = rows[0];
    const merged = { shipping_address, ...patch };

    const update = await pool.query(
      `UPDATE orders
       SET shipping_address = $1,
           version = version + 1
       WHERE id = $2
         AND version = $3
       RETURNING *`,
      [merged.shipping_address, id, version]
    );

    if (update.rows.length > 0) {
      return update.rows[0];
    }

    // Sleep with jitter so thundering retries do not amplify.
    const backoff = Math.min(100 * 2 ** attempt, 500);
    const jitter = Math.floor(Math.random() * 50);
    await new Promise((r) => setTimeout(r, backoff + jitter));
  }

  const e = new Error('Conflict');
  e.status = 409;
  throw e;
}

The retry limit matters. If three writers are slamming the same row in a tight loop, transparent retry hides a hot object. After the limit, surface the 409 so an operator or the user can investigate.

Testing that the race is closed

A unit test that calls the handler twice sequentially proves nothing. The bug only appears under concurrency. The test must fire both requests in parallel and assert that both changes are visible, or that one is rejected with a 409.

import assert from 'node:assert/strict';
import { fork } from 'node:child_process';

async function raceTest(pool, baseUrl) {
  // Seed one order.
  const { rows: [order] } = await pool.query(
    `INSERT INTO orders (shipping_address, version)
     VALUES ('Old Street', 1)
     RETURNING id, version`
  );

  // Fire two patches simultaneously.
  const a = fetch(`${baseUrl}/api/orders/${order.id}`, {
    method: 'PATCH',
    headers: { 'content-type': 'application/json' },
    body: JSON.stringify({
      shipping_address: 'Address A',
      expected_version: order.version,
    }),
  });

  const b = fetch(`${baseUrl}/api/orders/${order.id}`, {
    method: 'PATCH',
    headers: { 'content-type': 'application/json' },
    body: JSON.stringify({
      shipping_address: 'Address B',
      expected_version: order.version,
    }),
  });

  const [resA, resB] = await Promise.all([a, b]);

  const statuses = [resA.status, resB.status].sort();
  const bodies = await Promise.all([resA.json(), resB.json()]);

  // One must succeed, the other must conflict (or both succeed on retry).
  // For this test we expect exactly one 200 and one 409 without server retries.
  assert.deepStrictEqual(statuses, [200, 409]);

  // Verify the database holds the winner's write, not a merge or the original.
  const { rows: [final] } = await pool.query(
    'SELECT shipping_address, version FROM orders WHERE id = $1',
    [order.id]
  );

  assert.ok(
    final.shipping_address === 'Address A' || final.shipping_address === 'Address B',
    `unexpected final value: ${final.shipping_address}`
  );
  assert.strictEqual(final.version, 2);
}

Run with node --test or Jest using multiple workers. If the old code is in place, both requests return 200 and the final address is either A or B — whichever wrote last. With optimistic locking, exactly one writer wins, the other gets a 409, and the database version is 2.

Composite updates across tables

What if saving an order also updates line items, billing, and audit logs? You need a single version that protects the whole aggregate. Options:

  1. Version on the parent only. Update orders.version + 1 inside a transaction, then insert/update children without version checks. The parent version guarantees no other process mutated the order while you worked. This is the normal pattern.

  2. Version on every child table. Needed only if children have independent concurrent lifecycles. Most systems do not need this complexity.

  3. Use a xmax trick for child tables. Postgres exposes the internal xmax system column, which holds the ID of the deleting transaction (0 for current rows). It changes on every UPDATE, so SELECT xmax, * FROM line_items gives you a free optimistic lock token without adding a column. This is elegant but non-portable and harder to reason about. Prefer explicit version columns for application code.

-- Parent-only version is usually enough.
BEGIN;
UPDATE orders SET version = version + 1 WHERE id = $1 AND version = $2;
-- inserts/updates to line_items, billing, etc.
COMMIT;

If the parent UPDATE affects zero rows, roll back and return 409. No child work happens after a stale read.

The xmax alternative: free locking with system columns

If you cannot add a column to a legacy table, xmax is a usable escape hatch. It is not a user column; it is the transaction ID of the tuple’s deleting or updating transaction. It changes on every UPDATE or DELETE.

SELECT xmax, * FROM orders WHERE id = 42;
-- returns xmax = 0, id = 42, shipping_address = 'Old Street'

-- later, another session updates the row.

SELECT xmax, * FROM orders WHERE id = 42;
-- returns xmax = 123456, ...

You can pass xmax as the optimistic lock token the same way you pass version. The UPDATE still increments xmax implicitly. The client re-reads and compares.

The downsides: xmax is not part of your schema, so it is invisible to ORM mappings. It wraps around in long-lived databases. It is implementation-specific to Postgres. Do not build a cross-database product on it. Use it only as a short-term patch on a table you cannot migrate yet.

When optimistic locking is the wrong choice

Optimistic locking fails when collisions are frequent. A counter that every request hits, a seat inventory table during a flash sale, or a rate-limit bucket — these are high-contention objects. With optimistic locking, almost every write collides, and retries burn CPU without making progress.

For those cases, use a single atomic operation that does the math server-side:

UPDATE inventory
SET quantity = quantity - $1
WHERE id = $2
  AND quantity >= $1;

If quantity changed under you, the WHERE clause fails. But the check is part of the business rule, not a separate version token. A retry is unnecessary because the operation is self-contained and atomic.

Similarly, UPDATE ... SET counter = counter + 1 needs no lock token; Postgres makes the increment atomic. Optimistic locking is for read-modify-write sequences where the new value depends on external input or complex logic, not for arithmetic deltas.

Production guardrails

Add the version to your API contract. Every GET response should include it. Every PATCH or PUT should require it. If an old client forgets to send it, fail fast with a 400. Silent fallback to last-write-wins is the bug you are trying to eliminate.

// Include version in every read response.
res.json({
  id: order.id,
  shipping_address: order.shipping_address,
  version: order.version,
});

Add a metric for 409 responses. A low rate means the system is healthy. A spike means a hot object or a client bug that re-submits without re-reading.

if (update.rows.length === 0) {
  conflictCounter.inc({ resource: 'orders' });
  return res.status(409).json({ ... });
}

Set an alert on 409 rate per endpoint. A sudden jump is often the first symptom of a UI that auto-saves drafts and does not refresh state between edits.

The practical workflow

When a read-modify-write API loses data under concurrency:

  1. Identify the read and the write. If they are separate queries, the race exists.
  2. Add an integer version column, default 1, backfill with unique values.
  3. Change UPDATE to WHERE id = $1 AND version = $2, add version = version + 1, and RETURNING.
  4. Return 409 when zero rows are updated.
  5. Make the client re-read and retry, or add server-side retry with a limit.
  6. Test with parallel requests, not sequential ones.
  7. Alert on 409 rate to detect pathological contention.

The fix is usually three lines of SQL and a status code. The hard part is admitting that the overwrite was there all along.

A note from Yojji

Patterns like optimistic locking, compare-and-swap updates, and safe retry loops are the kind of backend precision work that separates a prototype from a production-grade system. Most teams only discover the gap after silent data loss shows up in support tickets.

Yojji is an international custom software development company founded in 2016, with teams across Europe, the US, and the UK. Their engineers build custom web applications and scalable microservices with the same attention to data integrity, concurrency safety, and operational observability that keeps systems correct under real traffic.