Stop Lost Updates: Fix Race Conditions with Optimistic Locking

Imagine two administrators updating the exact same e-commerce inventory count at the exact same second. Without proper concurrency controls, one admin's update silently wipes out the other's, leading to phantom stock and frustrated customers.

The Subtle Nightmare of the Lost Update Problem

In stateless web applications, transactions don't span across HTTP requests. When user A loads a form to edit product details, your app reads row data with version = 1 from MySQL or PostgreSQL and returns it in JSON. User A takes 10 seconds to read the screen, change the product title, and click Save. Meanwhile, user B opens the exact same form, updates the stock quantity from 50 to 10, and clicks Save 2 seconds before user A.

When user B clicks Save, their request updates the row in the database. Two seconds later, user A submits their request. Because user A's payload contains the data loaded 10 seconds ago, your application runs a standard update query:

UPDATE products 
SET title = 'Wireless Mouse Pro', stock = 50 
WHERE id = 42;

User A's query overwrites stock back to 50! User B's stock change to 10 is permanently lost without throwing an error, leaving no log trace. In software engineering, this classic concurrency flaw is known as the Lost Update Problem.

Why Pessimistic Locking Is Often Overkill for Web APIs

When developers encounter race conditions, the knee-jerk reaction is to reach for Pessimistic Locking using database row locks like SQL's SELECT ... FOR UPDATE. While database row locking works well for high-contention, microsecond-level internal transactions, it falls apart when applied across HTTP request-response cycles.

Holding a database lock across web requests forces database connection pools to hang open while waiting for external network latency or user interaction. This starves your database connection pool, degrades throughput, and risks severe deadlocks. Web APIs require an approach that handles concurrency gracefully without hogging database resources: Optimistic Locking.

How Optimistic Locking Works Under the Hood

Optimistic Locking assumes that data conflicts are infrequent. Instead of locking the row when reading it, you verify that no other process modified the row between your read operation and your write operation.

Implementing Optimistic Locking requires adding a dedicated concurrency control column to your database schema—typically an integer version counter or an updated_at timestamp.

Here is how the workflow operates step-by-step:

1. Read: Fetch the row along with its current version number (e.g., version = 1).
2. Modify: Prepare the changes in application memory.
3. Write with Condition: Execute an UPDATE statement that filters by both the primary key AND the version expected from step 1, while incrementing the version counter:

UPDATE products 
SET title = 'Wireless Mouse Pro', stock = 45, version = version + 1 
WHERE id = 42 AND version = 1;

1. Verify Affected Rows: If another request updated the row in the interim, its version became 2. Your UPDATE query will match 0 rows (affected_rows = 0).

Real-World Implementation in TypeScript & SQL

Let's translate this concept into clean, practical application logic. Here is how you can implement an atomic update with Optimistic Locking in Node.js/TypeScript:

interface ProductUpdatePayload {
  id: number;
  title: string;
  stock: number;
  expectedVersion: number;
}

async function updateProduct(payload: ProductUpdatePayload): Promise<boolean> {
  const result = await db.query(
    `UPDATE products 
     SET title = $1, stock = $2, version = version + 1 
     WHERE id = $3 AND version = $4`,
    [payload.title, payload.stock, payload.id, payload.expectedVersion]
  );

  // If rowCount is 0, someone else updated the record first!
  if (result.rowCount === 0) {
    throw new ConcurrencyConflictError(
      `Product ${payload.id} was updated by another process. Please refresh.`
    );
  }

  return true;
}

Notice how straightforward this is: zero persistent locks, zero connection leaks, and guaranteed concurrency safety at the database engine level.

Designing the API and User Interface for Conflict Resolution

When an optimistic lock failure occurs (rowCount === 0), your backend API should reject the request with an explicit HTTP status code: 409 Conflict.

{
  "error": "CONCURRENCY_CONFLICT",
  "message": "The resource has been modified by another user. Please re-fetch and try again.",
  "current_version": 2
}

How should your application handle this HTTP 409 error? Depending on your business domain, you have two primary strategies:

1. Automatic Retry (Background): If the update modifies non-overlapping or non-destructive fields, fetch the fresh state, apply changes, and retry the update once.
2. User Notification (Frontend): Prompt the user with a visual diff modal: "Another teammate modified this record 5 seconds ago. Would you like to overwrite or merge your changes?"

Key Takeaways

• Default to Optimistic Locking for web applications and REST/GraphQL APIs where user think time or network latency spans across requests.
• Always check affected rows count: An UPDATE query executing without syntax errors does not mean data was saved if WHERE version = x matched 0 rows.
• Use HTTP 409 Conflict to communicate race conditions clearly to client applications.

By incorporating a single version column into your critical database tables, you eliminate silent data corruption and keep your applications rock-solid under concurrent loads.