Optimistic Locking

Stop one user silently overwriting another's edit.
No row locks, no held transactions — just a version check on the write itself.

The Problem

Two users open the same article. Alice saves a new title; Bob, whose editor still holds the old row, saves a new body. Bob's write carries the stale title with it and Alice's edit is gone — no error, no trace.

lost-update.ts

// Alice reads  { id: 1, title: "Draft",      body: "Hello"  }
// Bob   reads  { id: 1, title: "Draft",      body: "Hello"  }

await repo.update(1, { title: "Final" });        // Alice
await repo.update(1, { body: "Hello world" });   // Bob

// Result: { title: "Draft", body: "Hello world" }  <- Alice's edit lost

Declaring the Lock

Mark one column optimisticLock: true. The first column that carries the flag becomes the version field for that model.

models/article.ts

export const Article = defineModel({
  tableName: "articles",
  columns: {
    id: { type: DataTypes.INTEGER, primaryKey: true, autoIncrement: true },
    title: { type: DataTypes.STRING },
    body: { type: DataTypes.TEXT },
    version: { type: DataTypes.INTEGER, optimisticLock: true },
  },
});

How It Behaves

behaviour.ts

// create() seeds the version at 1 when you don't supply one
const article = await repo.create({ title: "Draft", body: "Hello" });
// => { id: 1, title: "Draft", body: "Hello", version: 1 }

// Every update() advances it and matches on the value it expects
await repo.update(1, { title: "Final" });
// UPDATE articles SET title = ?, version = 2 WHERE id = ? AND version = 1
// => { id: 1, title: "Final", body: "Hello", version: 2 }

The update is a single statement whose WHERE clause carries the version. If another write landed in between, the row no longer matches, zero rows are affected, and Stabilize raises:

conflict.ts

import { StabilizeError } from "stabilize-orm";

try {
  await repo.update(1, { title: "Final" });
} catch (error) {
  if (error instanceof StabilizeError && error.code === "CONCURRENT_MODIFICATION") {
    // "Record was modified by another transaction
    //  (optimistic lock conflict on version)"
    return res.status(409).json({ error: "This record changed. Reload and retry." });
  }
  throw error;
}

Checking the Version Yourself

To catch a conflict at the moment the user submits rather than after they typed, send the version you loaded with the form. A version you supply wins over the one just read, so a stale form fails immediately.

form.ts

// The client posts back the version it rendered
await repo.update(articleId, {
  title: form.title,
  version: form.version,   // fails if this is no longer current
});

Bulk Updates

updateBy() touches many rows at once, so there is no single version to compare against. It advances the column in SQL instead — unless you included the version in the update yourself, in which case your value is kept.

update-by.ts

// SET status = ?, version = version + 1 WHERE ...
await repo.updateBy({ status: "draft" }, { status: "archived" });

bulkUpdate() does not advance the version

updateBy() and bulkUpdate() look like the same operation and are not. bulkUpdate() takes a list of { where, set } pairs, re-reads each matched row and updates it by id — but its SET clause is built from the keys you passed and nothing else. No version = version + 1 is added.

typescript

await repo.bulkUpdate([
  { where: { condition: "status = ?", params: ["draft"] },
    set:   { status: "archived" } },
]);
// Rows keep their old version. The next ordinary update() carrying that
// version will still succeed - the conflict this column exists to catch
// is not detected for these rows.

Use updateBy() when the model has an optimistic lock, or include the version yourself in set.

Retrying

Stabilize reports the conflict — it does not resolve it. The correct response depends on your domain: a conflict on a counter can be retried automatically, a conflict on prose cannot, because one edit has to win. Retry only what is genuinely re-derivable:

retry.ts

for (let attempt = 0; attempt < 3; attempt++) {
  const current = await repo.findOrFail(articleId);
  try {
    return await repo.update(articleId, {
      ...change,
      version: current.version,
    });
  } catch (error) {
    if (!(error instanceof StabilizeError)) throw error;
    if (error.code !== "CONCURRENT_MODIFICATION") throw error;
    // someone else got there first - re-read and try again
  }
}
throw new Error("Could not apply change after 3 attempts");

Not row locking

This is optimistic locking: nothing is held open while a user thinks. If you need a pessimistic lock — read a row, hold it, and block others until you finish — use lockForUpdate() inside a transaction instead. See Transactions. The two compose: a lock prevents the conflict, a version detects it.