Optimistic Locking

Optimistic locking catches a lost update without holding a lock. You read a row with its version, then write back only if the version is unchanged. If someone else wrote in between, Turbine throws OptimisticLockError.

Use it when conflicts are rare. For a lock held during a transaction, see Row locks.

The version column#

Add an integer column, conventionally version, defaulting to 0:

// schema.ts
export default defineSchema({
  posts: {
    id: { type: 'serial', primaryKey: true },
    title: { type: 'text', notNull: true },
    version: { type: 'integer', notNull: true, default: 0 },
  },
});

Or as a migration against an existing table:

ALTER TABLE posts ADD COLUMN version integer NOT NULL DEFAULT 0;

The `optimisticLock` option#

Pass optimisticLock: { field, expected } to update, where expected is the version you read:

const post = await db.posts.findUniqueOrThrow({ where: { id: 1 } });
 
await db.posts.update({
  where: { id: post.id },
  data: { title: 'Updated title' },
  optimisticLock: { field: 'version', expected: post.version },
});

Turbine checks the version and increments it in the same statement:

UPDATE posts SET title = $1, version = version + 1
WHERE id = $2 AND version = $3
RETURNING *

If another writer bumped version after your read, no row matches and Turbine throws OptimisticLockError.

`update` only#

Passing optimisticLock to updateMany, upsert, delete or deleteMany throws ValidationError (E003). Use these instead:

// updateMany: put the expected version in `where` and bump it in `data`.
const { count } = await db.posts.updateMany({
  where: { authorId: 7, version: expected },
  data: { published: true, version: { increment: 1 } },
});
// `count` is the number of rows that were still at that version.
 
// upsert: read the row first, then update or create, inside one transaction.
await db.$transaction(async (tx) => {
  const row = await tx.posts.findUnique({ where: { id } });
  if (row) {
    await tx.posts.update({ where: { id }, data, optimisticLock: { field: 'version', expected } });
  } else {
    await tx.posts.create({ data: { id, ...data } });
  }
});

For delete, put the version in where (where: { id, version: expected }): a row at another version is not deleted, and the call throws NotFoundError (E001). For deleteMany, the returned count is the rows still at that version.

Handling a conflict#

OptimisticLockError (E015) carries table, versionField and expectedVersion. Under the default errorMessages: 'safe', the message leaves out the value you sent; read it from .expectedVersion.

import { OptimisticLockError } from 'turbine-orm';
 
try {
  await db.posts.update({
    where: { id: post.id },
    data: { title: input.title },
    optimisticLock: { field: 'version', expected: post.version },
  });
} catch (err) {
  if (err instanceof OptimisticLockError) {
    // err.table          -> 'posts'
    // err.versionField   -> 'version'
    // err.expectedVersion-> the stale version you sent
    // The row changed under you. Re-read and reapply, or surface a 409 conflict.
  }
  throw err;
}

Not auto-retryable#

OptimisticLockError has no isRetryable flag, so withRetry does not retry it. The data changed, so replaying the same statement would be wrong. Re-read the row and decide whether your change still applies:

import { OptimisticLockError } from 'turbine-orm';
 
async function patchPost(id: number, patch: { title?: string }, maxAttempts = 3) {
  for (let attempt = 1; attempt <= maxAttempts; attempt++) {
    const current = await db.posts.findUniqueOrThrow({ where: { id } });
    try {
      return await db.posts.update({
        where: { id },
        data: patch,
        optimisticLock: { field: 'version', expected: current.version },
      });
    } catch (err) {
      if (err instanceof OptimisticLockError && attempt < maxAttempts) continue;
      throw err;
    }
  }
}

For an edit form, return the conflict (HTTP 409) so the person sees the latest data.