Optimistic Locking API

Version-based conflict detection on writes, and the pessimistic locking alternative

optimisticLock Column Option

typescript

interface ColumnConfig {
  optimisticLock?: boolean;  // Enable optimistic locking
}

The first column carrying the flag becomes the model's lock field. The lock column is advanced on every locked write path.

Parameters:

optimisticLock Marks this column as the model's lock field

models/Document.ts

const Document = defineModel({
  tableName: "documents",
  columns: {
    id: { type: DataTypes.STRING, required: true, unique: true },
    title: { type: DataTypes.STRING, length: 255, required: true },
    version: { type: DataTypes.INTEGER, optimisticLock: true },
  },
});

create()

typescript

async create(
  entity: Partial<T>,
  options: { relations?: string[] } = {},
  _client?: DBClient,
): Promise<T>

Seeds the version to 1 if you don't supply one.

example/create.ts

const doc = await documentRepo.create({
  id: generateUUID(),
  title: "Draft",
});
// doc.version === 1

update()

typescript

async update(
  id: number | string,
  entity: Partial<T>,
  _client?: DBClient,
): Promise<T>

A version you pass in the entity wins over the one just read (expected = callerVersion ?? lockValue).

What the statement becomes:

SET Advances the lock column to expected + 1, or to 1 when the value is non-numeric
WHERE id = ? AND <lockCol> = ?, or IS NULL when the read value was null
conflict Zero rows affected throws

example/update.ts

const doc = await documentRepo.findOne(id);

// Optimistic: the version read above travels with the write.
await documentRepo.update(id, {
  title: "Final",
  version: doc.version,
});

updateBy()

typescript

async updateBy(
  conditions: Partial<T>,
  updates: Partial<T>,
  _client?: DBClient,
): Promise<number>

Advances the lock column in SQL as <lockCol> = <lockCol> + 1, unless the lock field is itself among the write values.

example/update-by.ts

await documentRepo.updateBy({ title: "Draft" }, { title: "Final" });
// SET title = ?, version = version + 1 WHERE title = ?

upsert()

typescript

async upsert(
  entity: Partial<T>,
  keys: string[],
  _client?: DBClient,
): Promise<T>

Seeds the version to 1 if undefined.

example/upsert.ts

await documentRepo.upsert(
  { id: generateUUID(), title: "Draft" },
  ["id"],
);
// version seeded to 1 when the entity does not carry one

CONCURRENT_MODIFICATION

typescript

class StabilizeError extends Error {
  code: string;
  originalError?: unknown;
}

A lost update throws a StabilizeError with the code "CONCURRENT_MODIFICATION".

Message:

typescript

Record was modified by another transaction (optimistic lock conflict on <field>)

There is no built-in retry. The caller handles the conflict.

example/conflict.ts

import { StabilizeError } from "stabilize-orm";

try {
  await documentRepo.update(id, { title: "Final", version: doc.version });
} catch (err) {
  if (err instanceof StabilizeError && err.code === "CONCURRENT_MODIFICATION") {
    // Re-read and retry at the application level.
  }
}

lockForUpdate()

typescript

async lockForUpdate(id: number | string, _client?: DBClient): Promise<T | null>

The pessimistic alternative on the repository. It builds find().where("id = ?", id).limit(1), applies FOR UPDATE on PostgreSQL and MySQL, and returns the row or null. SQLite and SQL Server skip the lock clause entirely — SQLite has no FOR UPDATE, and T-SQL spells its equivalent as a table hint the builder cannot express — so on those two dialects this degrades to an ordinary read and takes no row lock.

example/lock-for-update.ts

await orm.transaction(async (tx) => {
  const doc = await documentRepo.lockForUpdate(id, tx);
  if (!doc) return;

  await documentRepo.update(id, { title: "Final" }, tx);
});

QueryBuilder locking

typescript

lock(mode: LockMode = "FOR UPDATE"): QueryBuilder<T>
forUpdate(): QueryBuilder<T>
forShare(): QueryBuilder<T>

Chainable. Valid modes are "FOR UPDATE", "FOR SHARE", "FOR NO KEY UPDATE", and "FOR KEY SHARE".

example/qb-lock.ts

const rows = await documentRepo
  .find()
  .where("id = ?", id)
  .forUpdate()
  .execute(orm.client);

const shared = await documentRepo
  .find()
  .where("status = ?", "open")
  .lock("FOR SHARE")
  .execute(orm.client);

Related: versioned

versioned: true (model-level history) is a SEPARATE feature from the optimisticLock column. They are not the same mechanism and should not be conflated: versioned keeps a history of row revisions, while optimisticLock detects a concurrent write to a single row.

models/Document.ts

const Document = defineModel({
  tableName: "documents",
  versioned: true,                       // row history
  columns: {
    version: { type: DataTypes.INTEGER, optimisticLock: true },  // conflict detection
  },
});