Optimistic Concurrency Control for APIs and Databases

Introduction

Most concurrency bugs do not look dramatic. They look like a user edits a project name, another user changes the billing contact, and the second save accidentally erases the first change. Both requests were valid. Both users had permission. The problem was that each request was based on a stale copy of the same record.

Optimistic concurrency control is a practical way to catch that kind of lost update without locking records for long periods. The server lets readers proceed normally, but every write must prove that it still applies to the version the client originally saw. If the record changed in between, the write fails with a conflict instead of silently overwriting newer data.

This article walks through optimistic concurrency for backend APIs and relational databases. The examples use SQL, JavaScript, and HTTP conventions, but the same idea applies to document stores, event streams, queues, and internal service APIs.

Why Lost Updates Happen

A lost update happens when two actors read the same state, make independent changes, and then save in an order that overwrites one actor's work.

Imagine a projects table:

10:00:00  Alice reads project version 7
10:00:01  Bob reads project version 7
10:00:10  Alice saves "Launch site", version becomes 8
10:00:12  Bob saves "Website launch", based on version 7

If Bob's update writes the whole record without checking the version, Alice's change can disappear. This is especially common when APIs accept full object replacement, admin screens keep tabs open for a long time, mobile clients reconnect after offline edits, or background jobs retry old commands.

Pessimistic locking avoids the problem by blocking other writers while one actor holds a lock. That can be correct for short critical sections, but it is often a poor fit for user-facing APIs. Users can pause, close tabs, lose network access, or hold an edit form open for hours. You do not want a database lock tied to that behavior.

Optimistic concurrency assumes conflicts are relatively rare. It does not prevent concurrent edits up front. It detects them at write time and asks the caller to resolve the conflict deliberately.

Choose a Concurrency Token

The server needs a token that changes every time the protected state changes. Clients read the token with the resource, then include it when they attempt a write.

Common token choices include:

• A numeric version column that increments on every update.
• An updated_at timestamp, if the database precision is high enough for your write rate.
• A content hash derived from fields that matter for the update.
• An event sequence number for event-sourced systems.
• An HTTP ETag that wraps one of the internal tokens.

For most relational APIs, a numeric version is the simplest and most reliable option:

CREATE TABLE projects (
  id uuid PRIMARY KEY,
  name text NOT NULL,
  billing_contact_email text NOT NULL,
  version integer NOT NULL DEFAULT 1,
  updated_at timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX projects_id_version_idx
ON projects (id, version);

The version is not a display field. It is part of the write contract. The client does not need to understand why the version changed; it only needs to send the version it last observed.

Protect the right scope

The token should cover the state that can conflict. A single row-level version is usually enough for a project settings page. A collaborative editor might need finer-grained versions per section, paragraph, or document operation. A shopping cart might use one version for the cart header and separate versions for line items.

If the scope is too broad, harmless edits create unnecessary conflicts. If the scope is too narrow, conflicting writes can still slip through. Start with the smallest boundary where overwriting newer state would be unacceptable.

Enforce the Check in the Database

The concurrency check must happen in the same statement that performs the write. Checking the version in one query and updating in a later query creates a race condition between the two operations.

Use a compare-and-swap update:

UPDATE projects
SET
  name = $3,
  billing_contact_email = $4,
  version = version + 1,
  updated_at = now()
WHERE id = $1
  AND version = $2
RETURNING id, name, billing_contact_email, version, updated_at;

If the WHERE clause matches, the update succeeds and returns the new version. If the row exists but the version no longer matches, the update affects zero rows. That zero-row result is the conflict signal.

Here is the same pattern wrapped in application code:

async function updateProject(db, projectId, expectedVersion, changes) {
  const result = await db.query(
    `
      UPDATE projects
      SET
        name = $3,
        billing_contact_email = $4,
        version = version + 1,
        updated_at = now()
      WHERE id = $1
        AND version = $2
      RETURNING id, name, billing_contact_email, version, updated_at
    `,
    [
      projectId,
      expectedVersion,
      changes.name,
      changes.billingContactEmail,
    ]
  );

  if (result.rowCount === 1) {
    return result.rows[0];
  }

  const exists = await db.query(
    "SELECT id, version FROM projects WHERE id = $1",
    [projectId]
  );

  if (exists.rowCount === 0) {
    throw new NotFoundError("Project not found");
  }

  throw new ConflictError("Project was modified by another writer", {
    currentVersion: exists.rows[0].version,
    expectedVersion,
  });
}

The follow-up SELECT distinguishes a missing record from a version conflict. That distinction matters for API responses, logs, and user guidance.

Use transactions when multiple writes move together

Some operations update more than one table. For example, changing a subscription plan might update the subscription row, append an audit record, and enqueue a billing event. Keep the version check and all dependent writes in one transaction. If the version check fails, none of the side effects should happen.

Expose Conflicts Through APIs

API clients need a clear way to send the token and a clear response when the token is stale. For HTTP APIs, ETags and conditional requests are a good fit.

When returning a resource, include an ETag:

app.get("/projects/:id", async (req, res) => {
  const project = await loadProject(req.params.id);

  res.setHeader("ETag", `"project:${project.version}"`);
  res.json({
    id: project.id,
    name: project.name,
    billingContactEmail: project.billingContactEmail,
    version: project.version,
  });
});

For updates, require the client to send If-Match with the ETag it received:

app.put("/projects/:id", async (req, res) => {
  const expectedVersion = parseProjectVersion(req.header("If-Match"));

  if (!expectedVersion) {
    return res.status(428).json({
      error: "precondition_required",
      message: "Send an If-Match header with the project version.",
    });
  }

  try {
    const project = await updateProject(
      db,
      req.params.id,
      expectedVersion,
      req.body
    );

    res.setHeader("ETag", `"project:${project.version}"`);
    return res.json(project);
  } catch (error) {
    if (error instanceof ConflictError) {
      return res.status(412).json({
        error: "version_conflict",
        message: "The project changed after you loaded it.",
        expectedVersion: error.details.expectedVersion,
        currentVersion: error.details.currentVersion,
      });
    }

    throw error;
  }
});

428 Precondition Required tells clients they attempted a write without a concurrency token. 412 Precondition Failed tells them the token was present but stale. Some APIs use 409 Conflict for stale writes instead. That can be fine, but be consistent and document the contract.

Make Conflict Responses Useful

A conflict response should help the caller decide what to do next. Returning only "conflict" pushes the hard work into support tickets and retry loops.

A useful response includes a stable error code, the expected version, the current version, and optionally the latest resource representation:

{
  "error": "version_conflict",
  "message": "The project changed after you loaded it.",
  "expectedVersion": 7,
  "currentVersion": 8,
  "current": {
    "id": "prj_123",
    "name": "Launch site",
    "billingContactEmail": "[email protected]",
    "version": 8
  }
}

For browser clients, this supports a humane flow: show the user that the record changed, display the latest state, and let them reapply their edits. For API clients, it supports a fetch-merge-retry loop when automatic merging is safe.

Do not blindly retry every conflict

Automatic retries are safe only when the operation is commutative or can be merged without changing intent. Incrementing a counter, adding a tag, or appending a comment may be safe after reloading current state. Replacing a billing email, changing a price, or updating an access policy usually needs human or domain-specific resolution.

Treat a conflict as information, not as a transient network error. Retrying the exact same stale write will keep failing, and retrying with the newest version without checking semantics can recreate the lost-update bug you were trying to prevent.

Handle Failure Modes

Optimistic concurrency is small enough to implement quickly, but the edge cases deserve deliberate choices.

Partial updates still need protection

Patch-style APIs reduce the number of fields a request can overwrite, but they do not remove the need for version checks. Two patches can still conflict if they depend on the same old state:

Alice reads version 7 where quota = 100
Bob reads version 7 where quota = 100
Alice patches quota to 150
Bob patches quota to 200

If the business rule is "last writer wins", that may be acceptable. If the quota change requires approval, auditability, or billing coordination, the patch should still include a version check.

Background jobs need fresh intent

Jobs often run after the user request that scheduled them. Store enough context for the job to know whether it still applies. For example, a job that archives a project can store expectedVersion. When it runs, it should archive only if the project is still at that version. If a user changed the project in the meantime, the job should stop or create a conflict record instead of forcing stale intent onto current state.

Replicas can return stale tokens

If clients read from replicas and write to a primary database, replication lag can produce stale versions. A client may read version 7 from a replica even though the primary is already at version 8. The write will correctly fail. If that causes too many false conflicts, read-after-write flows should use the primary, session consistency, or a replica lag guard.

Bulk updates need their own contract

Bulk operations should report which records succeeded, which records conflicted, and which records were missing. Do not hide conflicts behind one vague batch failure. A good batch response lets the client retry only the records that can still be applied.

Conclusion and Next Steps

Optimistic concurrency control is one of the most useful safeguards you can add to APIs that update shared data. The core pattern is simple: return a version token with reads, require the token on writes, enforce the token in the database update, and return a clear conflict response when the token is stale.

Start with records where lost updates would be expensive: account settings, billing configuration, permissions, inventory, workflow state, and admin-managed content. Add a version column, wire it into the write path, and log conflict rates by endpoint. Those metrics will show whether conflicts are rare enough for optimistic concurrency or whether a specific workflow needs a more specialized merge or locking model.