Optimistic locking

Stop a user from overwriting an edit made by someone else since they loaded the page, without holding a lock while they type.

Setup

The code of this recipe depends on these declarations: the imports, the schema classes, the repo, and the migration that creates the tables. Run the recipe in an empty database of its own: each recipe creates its own tables, and some have the same names (posts) with other columns.

import { postgres } from "@egon-orm/postgres";
import { cast, createRepo, Schema, StaleEntryError, t } from "egon-orm";
import { createMigrator, defineMigration } from "egon-orm/migration";

const repo = createRepo({ adapter: postgres({ connectionString: process.env.DATABASE_URL }) });

// The tables, created by a migration.
const tables = defineMigration({
  up(m) {
    m.createTable("pages", (table) => {
      table.id({ type: "integer" });
      table.text("body").notNull();
      table.integer("lock_version").notNull().default(1);
    });
  },
  down(m) {
    m.dropTable("pages");
  },
});

/** Creates the tables, in the database that DATABASE_URL names. */
export async function createTables(): Promise<void> {
  const admin = postgres({ connectionString: process.env.DATABASE_URL });
  await createMigrator({
    adapter: admin,
    migrations: [{ version: 20261021000801, name: "optimistic_locking_tables", migration: tables }],
  }).up();
  await admin.close();
}

A version field

class Page extends Schema("pages", {
  id: t.integer().primaryKey().dbGenerated(),
  body: t.string(),
  lockVersion: t.integer().default(1),
}) {
  static changeset(page: Page, params: unknown) {
    return cast(page, params, ["body"]).optimisticLock("lockVersion");
  }
}

optimisticLock("lockVersion") adds where lock_version = <the value read> to the update and increments the field. If someone else updated the row in between, no row matches.

What happens on a conflict

The examples below start from these rows, after creating the tables:

await createTables();
const page = await repo.insertOrThrow(cast(Page, { body: "Draft" }, ["body"]));

const mine = await repo.getOrThrow(Page, page.id); // lockVersion 1
const theirs = await repo.getOrThrow(Page, page.id); // lockVersion 1

await repo.updateOrThrow(Page.changeset(theirs, { body: "Their edit" })); // lockVersion is now 2

try {
  await repo.updateOrThrow(Page.changeset(mine, { body: "My edit" })); // where lock_version = 1: no row
} catch (error) {
  if (!(error instanceof StaleEntryError)) throw error;
  // reload, show the other edit, and let the user decide
}

By default the update throws StaleEntryError. To show the conflict on the form instead, report it as an error on a field:

// Reports a conflict on the form instead of throwing.
export function save(page: Page, params: unknown) {
  return repo.update(Page.changeset(page, params), {
    staleError: { field: "body", message: "was changed by someone else; reload and try again" },
  });
}

result.error.errorMessages() is then { body: ["was changed by someone else; reload and try again"] }.

An unchanged save

A form submitted without edits changes nothing, so the update runs no query: the version is not incremented, and the copies other users have open stay current.

const current = await repo.getOrThrow(Page, page.id); // lockVersion 2
const saved = await repo.updateOrThrow(Page.changeset(current, { body: current.body })); // no query
saved.lockVersion; // => 2
(await repo.getOrThrow(Page, page.id)).lockVersion; // => 2

The increment alone does not count as a change. Any other change does, including one a prepareChanges function makes and a change to an association: a parent saved with only castAssoc or putAssoc changes is incremented and checked like any other update.

Because no query runs, an unchanged save of a copy that is already stale is not checked: it succeeds, and nothing is written. Pass { force: true } to run the update anyway: it then increments the version and checks it, and a stale copy fails.

Good to know

• The version must travel with the form: send the version the user saw as a hidden field, and check it against the row you load when the form comes back (or keep the loaded row in the session). A row loaded again just before the update has the latest version, and so defeats the check.
• A delete with the same changeset checks the version too.
• For a lock held during a transaction instead, use lock("update") on the query that reads the row: see Locks.

See also

• Hooks for the repo
• Update: staleError and allowStale