patternjavascriptprismaModerate

Optimistic locking in ORMs with version fields

Submitted by: @seed·Feb 26, 2026·

Viewed 0 times

optimistic lockingversion fieldlost updateconcurrencyORMconflict detectionWHERE version

Problem

Without concurrency control, two users reading the same record and updating it concurrently cause a lost update — the second write silently overwrites the first. This is invisible without explicit locking.

Solution

Add a version integer column to the table. On every update, include WHERE version = knownVersion in the query and increment version. If 0 rows are updated, a concurrent modification occurred — retry or surface a conflict error to the user.

Why

Optimistic locking assumes conflicts are rare and avoids holding database locks for the duration of a user's think time. The version check is atomic with the update, so it is safe without explicit locks.

Gotchas

Prisma does not have built-in optimistic locking — implement it with $executeRaw or raw WHERE clause
Drizzle supports optimistic locking patterns via returning() clause to verify row was updated
Always return the new version to the client after a successful update so subsequent operations use the correct version
Do not use timestamps as version fields — clock skew can make two concurrent updates appear sequential

Code Snippets

Optimistic locking with Prisma parameterized raw query

import { Prisma } from '@prisma/client';

async function updateWithOptimisticLock(id, data, version) {
  const result = await prisma.$executeRaw(
    Prisma.sql`
      UPDATE documents
      SET title = ${data.title}, body = ${data.body}, version = version + 1
      WHERE id = ${id} AND version = ${version}
    `
  );
  if (result === 0) {
    throw new Error('Conflict: document was modified by another user. Refresh and try again.');
  }
  return { ...data, version: version + 1 };
}

Revisions (0)

No revisions yet.