Optimistic Concurrency (Row Versioning)

Atscript DB supports first-class optimistic concurrency control (OCC) via a server-managed integer version column. A single annotation makes the column auto-bump on every write that changes a non-exempt column (see version-exempt fields); the inline $cas operator turns any write into a conditional one that rejects stale read-modify-write submissions.

When You Need This

Atscript DB has two atomic primitives out of the box: insert and field ops ($inc, $dec, $mul). Every other write is last-write-wins — a caller reads a row, computes a patch in JS, and submits it via updateOne. Between read and write there is a race window in which another writer can change the row, and the second writer silently overwrites the first.

That window is fine for many fields (display name, profile bio). It is not fine for state machines with read-modify-write semantics:

• Consuming a single-use credential (backup code, magic-link token) — two concurrent consumers can both succeed and use the credential twice.
• Adding to a JSON-column collection (mfa.methods[], trustedDevices[]) — concurrent appenders each compute their own union and one entry is lost.
• Confirming an MFA enrollment or transitioning a workflow step stored in a row column.
• Inventory decrement with business rules (no-go-below-zero) — $inc(-1) is atomic but the rule check on the resulting value is not.

The common pattern is "read state, compute next state, write state, fail if state changed under us." That is OCC, and it is the de-facto enterprise primitive in every major ORM. Atscript DB exposes it via a single annotation, an inline $cas operator, and a thin auto-lift in @atscript/moost-db so REST clients get OCC seamlessly.

The @db.column.version Annotation

Mark a numeric column as the row's version:

interface Task {
    @meta.id
    @db.default.increment
    id: number.int

    title: string
    status: 'open' | 'done'

    @db.column.version
    version: number.int
}

Constraints (enforced at compile time):

• At most one version column per table — composite versioning makes no semantic sense.
• The field must resolve to a SQL INTEGER (or Mongo Number). String / timestamp versioning is not supported.
• The annotation takes no arguments. To store the field under another column name, add @db.column as for any field (@db.column 'row_version'). The rename changes only the storage column: $cas, write bodies, rows read back and /meta's versionColumn keep using the field name (version). Renamed version fields are supported since 0.1.141.
• The field is server-managed: the adapter sets it to 0 on insert and increments it by 1 on every successful update that writes a non-exempt field (see Version-exempt fields). See defaults.

Auto-bump is mandatory

Every successful write to a versioned row increments version by 1, whether or not $cas was supplied. This is the property that makes OCC actually work — if the version column did not auto-increment, opting in to $cas would silently degrade to no protection.

The one exception is declarative and schema-level: a patch that writes only fields marked @db.column.version.exempt (and carries no $cas) leaves the version untouched (since 0.1.150). The update time such a patch sets (number.timestamp.updated) and the change counter it increments do not count as a write here — use @db.onUpdate.inc when you need a token that every write moves, exempt ones included.

Callers MAY read the version (and SHOULD, in order to pass $cas) but MUST NOT write it directly. See Direct-write rejection.

The $cas Operator

$cas is a top-level payload operator — a sibling to plain SET fields and $inc/$mul field ops. It is the only opt-in surface for CAS.

const ok = await tasks.updateOne({
  id: 1,
  status: "done",
  $cas: { version: row.version }, // ← opt-in conflict detection
});

if (ok.matchedCount === 0) {
  // Either the row doesn't exist OR another writer touched it. Retry.
}

The map shape ({ [versionColumn]: N }) keys by the version field's name (the logical name, even when @db.column renames its column). No exception is thrown on mismatch — the call returns { matchedCount: 0, modifiedCount: 0 } and the caller decides what to do.

No throw on mismatch

Throwing creates an asymmetry where every retry path needs try/catch instead of a clean if (!result.matchedCount). Distinguish "row missing" from "version mismatch" with an extra findOne if you care; for the dominant retry-on-conflict use case both states warrant the same response.

PK-only $cas — the versioned touch

A payload that carries only the identifying fields and $cas is a real write (since 0.1.128): the adapter executes UPDATE … SET version = version + 1 WHERE pk AND version = ? (Mongo: $inc), so a hit bumps the version and reports { matchedCount: 1, modifiedCount: 1 }, and a stale or missing row reports { 0, 0 }. The CAS predicate is never silently dropped.

// Fence a parent row for the rest of the surrounding transaction: on SQL
// engines the conditional UPDATE takes the row lock; concurrent touchers with
// the same expected version lose the race and see matchedCount === 0.
const fence = await orders.updateOne({ id, $cas: { version: order.version } });
if (fence.matchedCount === 0) throw new Error("order moved on");

Two consequences to keep in mind:

• Every touch invalidates every other holder's version. A form that re-submits an unchanged record together with its version bumps it and makes the other open editors conflict. Strip version (or don't send the PATCH) when nothing changed.
• Without $cas, an empty patch is a no-op — no statement runs, the version does not move, and matchedCount honestly reports whether the row exists ({ 1, 0 } / { 0, 0 }). Before 0.1.128 both shapes returned a fabricated { 1, 0 } without touching the store.

$cas with bulkUpdate

Each payload in bulkUpdate carries its own $cas. Rows with matching versions are updated; rows that mismatch (or do not exist) are silently skipped. modifiedCount reflects how many actually applied.

await tasks.bulkUpdate([
  { id: 1, status: "done", $cas: { version: 7 } },
  { id: 2, status: "done", $cas: { version: 3 } },
  { id: 3, status: "done" }, // no $cas — always applies
]);

This is the version-locked batch primitive — partial success is the point. Detect partial failure via matchedCount < items.length.

$cas with replaceOne

replaceOne supports $cas with the same semantics as updateOne: predicate filters by version, bump on success.

await tasks.replaceOne({
  id: 1,
  title: "Bake bread",
  status: "done",
  $cas: { version: 4 },
});

Batch touch — touchMany

touchMany(keys, opts?) (since 0.1.129) is the batch form of the versioned touch: it bumps the version of every listed row by exactly one, each row guarded by its own expected version. Every key carries the primary key (composite supported — a unique index does not identify a touch key) plus the version column and nothing else — a touch has no payload; undefined-valued properties are ignored, as in every write payload.

const result = await orders.touchMany([
  { id: 1, version: 4 },
  { id: 2, version: 0 },
  { id: 3, version: 9 },
]);
// { matchedCount: 3, modifiedCount: 3 } — every version + 1

• require: 'all' (default): the table counts the matching rows first and throws CasMismatchError (code === "CAS_MISMATCH", errors[0] = { path: "$cas", message: "touchMany: 2 of 3 rows matched — stale or missing rows" }) before any write when a row is stale or missing. The bumps then run as UPDATE … SET version = version + 1 WHERE (pk AND version = ?) OR … statements in chunks of at most 500 keys inside one adapter transaction; a row that moves between the count and the bump throws the same error and, on SQL engines, the transaction rolls every bump back. The memory adapter and a MongoDB standalone topology run withTransaction as a passthrough: there the pre-count covers the common stale case and the residual race window is accepted.
• A touch writes the row: the same statement sets @db.onUpdate.now fields and increments @db.onUpdate.inc counters (since 0.1.162; before, touchMany left @db.onUpdate.now unchanged).
• require: 'any': no pre-count; whatever matches is bumped and the honest summed { matchedCount, modifiedCount } is returned.
• Validation (DbError("INVALID_QUERY"), before any statement): a table without @db.column.version (path ""), a key missing a primary-key field ([i].<pk>), a key with a missing / non-numeric version ([i].version), a key carrying any other property ([i].<prop>), a duplicate primary key ([i]). Empty keys → { 0, 0 } without a statement.
• No guard, no onWrite, and not exposed over HTTP — an app that needs it adds its own route on the controller. @atscript/moost-db maps CAS_MISMATCH to 409.

UPGRADE NOTE

Up to 0.1.127 an empty updateMany(orFilter, {}) bumped the matched versions, and some code used it as a batch lock. Since 0.1.128 that call is a no-op that still reports the full matchedCount — it takes no lock. Use touchMany.

$cas is NOT supported on updateMany

updateMany(filter, data) always writes through, auto-bumping the version (unless the patch writes only version-exempt fields) but never checking it. A single expectedVersion cannot sensibly match N rows with different versions. Per-row version locking is the job of bulkUpdate (see above).

// ✅ Auto-bumps every matched row's version
await tasks.updateMany({ status: "open" }, { status: "done" });

// ❌ Throws — `$cas` is not allowed on updateMany
await tasks.updateMany({ status: "open" }, { status: "done", $cas: { version: 1 } });

Composition with field operations

$cas composes atomically with $inc / $dec / $mul. The adapter generates a single statement like UPDATE … SET counter = counter + 1, version = version + 1 WHERE id = ? AND version = ? — either everything applies or nothing does.

import { $inc } from "@atscript/db/ops";

await tasks.updateOne({
  id: 1,
  counter: $inc(),
  $cas: { version: 4 },
});
// Both the counter increment AND the version bump happen atomically,
// gated by the version predicate.

Version-exempt fields (@db.column.version.exempt)

Since 0.1.150. Some columns are derived or reporting data refreshed in the background — scores, view counters, caches. Writing them should not invalidate the versions held by editors who are mid-way through a form. Mark such a field as version-exempt:

interface Task {
    @meta.id
    @db.default.increment
    id: number.int

    title: string

    @db.column.version
    version: number.int

    @db.column.version.exempt
    score: number

    @db.column.version.exempt
    hits: number
}

A patch that writes only exempt fields neither bumps the version nor adds a version predicate. Any non-exempt field in the patch makes it a normal versioned write. The decision depends only on the schema and the payload's keys — it needs no row read and no per-call flag, so a client cannot bypass or force it.

Operation	Payload	$cas	Version bump
updateOne / bulkUpdate item	every written field exempt	no	no
updateOne / bulkUpdate item	every written field exempt	yes	yes — check and bump, as without the annotation
updateOne / bulkUpdate item	any non-exempt field	either	yes
updateMany	every written field exempt	n/a	no (every matched row)
updateMany	any non-exempt field	n/a	yes
replaceOne / bulkReplace / replaceMany / PUT	full row	either	always
touchMany, PK-only $cas touch	none	yes	always
empty patch without $cas	none	no	no statement (unchanged)

Where it applies:

• Field ops on an exempt column ({ hits: $inc(1) }) and array ops on an exempt array ($insert, $update, $remove, $upsert, $replace) are exempt writes.
• Objects. Marking an object field covers every nested field. An object whose fields are all exempt counts as exempt itself, so a replace-strategy write of that object stays exempt. In a merge-strategy object, only the supplied children count: { mixed: { cached: 1 } } is exempt when cached is, { mixed: { label: "x" } } is not. A replace-strategy object that is only partly exempt bumps, because the write null-fills its optional siblings.
• Allowed on scalars, @db.encrypted fields, @db.json fields (the whole JSON column), array fields (the whole array), foreign-key columns, fields with @db.default* and nested object fields.
• Rejected (compile error, mirrored at runtime): on the @db.column.version field itself, on a @meta.id field, on a navigation field (@db.rel.*), below a @db.json field or inside an array of objects (mark the JSON / array field itself), and together with @db.column.derived.
• Warned (compile time): the annotation on a table that declares no @db.column.version, or on a @db.ignore field — it has no effect there.
• Nested writes through @db.rel.* follow the related table's own schema; the parent is bumped only by its own non-exempt columns.
• It is not part of the schema snapshot or hash: adding or removing the annotation causes no sync.
• table.versionExemptFields lists the exempt logical paths, including objects covered by the all-children rule.

$cas still checks and bumps

$cas is the caller's explicit request for a versioned write, so updateOne({ id, score: 3, $cas: { version: N } }) is checked against N and bumps the version, exactly as before. Over HTTP the auto-lift of a body version into $cas works the same way: a UI form that sends version stays a conflict-checked edit; a background job that omits it does not bump.

The version is no longer a whole-row change token

With exempt fields declared, the version moves on every write that changes a non-exempt column, not on every write. Do not use it as a cache key or ETag for the whole row if exempt columns matter to the cache: an exempt-only refresh keeps the same version.

modifiedCount for unchanged exempt writes

Without a bump nothing forces a change. On MySQL and MongoDB, an exempt write that stores values equal to the current ones reports { matchedCount: 1, modifiedCount: 0 } (before 0.1.150 a versioned table always reported modifiedCount ≥ 1 on a hit). SQLite, PostgreSQL and the memory adapter report matched rows as modified. Use matchedCount to tell whether the row was found; do not read modifiedCount === 0 as a stale version.

Concurrent exempt array writers

Array ops on the generic (SQL, memory) path are a read-modify-write. Like any array patch without $cas, two concurrent writers of the same exempt array can lose an update.

withOptimisticRetry — The Retry Helper

Raw CAS works, but every consumer writing a retry loop is friction. withOptimisticRetry covers the 90% case:

import { withOptimisticRetry } from "@atscript/db";

await withOptimisticRetry(
  users,
  { id }, // filter — typically the primary key
  async (row) => {
    // Pure function: receives the current row, returns the patch.
    return {
      backupCodes: row.backupCodes.filter((h) => h !== usedHash),
    };
  },
  { maxAttempts: 5 },
);

What it does, in order:

1. findOne(filter) — read the current row (throws DbError("NOT_FOUND") if missing).
2. Call mutator(row) to compute the patch.
3. updateOne({ ...filter, ...patch, $cas: { [versionColumn]: row[versionColumn] } }).
4. On matchedCount === 0, retry from step 1 up to maxAttempts times.
5. After maxAttempts consecutive conflicts, throw CasExhaustedError.

Explicit no-write (since 0.1.128). A mutator that returns undefined or an empty object {} decided there is nothing to change: the helper resolves { matchedCount: 1, modifiedCount: 0 } without writing, and the version does not move — N concurrent loops that all decide "nothing to do" never bump each other into CasExhaustedError. Callers who want the fence instead call the versioned touch directly.

The second parameter is a filter (not just an id) so composite-key and non-id tables work without contortion. The helper requires the table to declare @db.column.version — otherwise it throws DbError("INVALID_QUERY") (silently degrading to last-write-wins would defeat the purpose).

Options

Option	Type	Default	Effect
maxAttempts	number	5	How many times to re-read and retry before giving up.
delay	(attempt: number) => Promise<void>	none	Hook invoked between failed attempts. Receives the 1-based attempt number that just failed. Use for backoff + jitter.

await withOptimisticRetry(users, { id }, mutator, {
  maxAttempts: 10,
  delay: async (attempt) => {
    const ms = Math.min(100 * 2 ** attempt, 1000) + Math.random() * 50;
    await new Promise((r) => setTimeout(r, ms));
  },
});

This helper is optional sugar — the raw $cas API is fine for callers that want explicit control over retry policy.

Direct-Write Rejection

The version column is server-managed. Any attempt to write it from a payload is rejected at the patch-decomposer layer, regardless of which write method is used:

// ❌ All three throw DbError("VERSION_COLUMN_WRITE")
await tasks.updateOne({ id: 1, version: 5 });
await tasks.updateOne({ id: 1, version: $inc() });
await tasks.updateOne({ id: 1, version: $mul(2) });

await tasks.replaceOne({ id: 1, title: "x", status: "open", version: 9 });
await tasks.bulkUpdate([{ id: 1, version: 5 }]);

The error code is VERSION_COLUMN_WRITE. Catch it like any other DbError:

import { DbError } from "@atscript/db";

try {
  await tasks.updateOne({ id: 1, version: 5 });
} catch (err) {
  if (err instanceof DbError && err.code === "VERSION_COLUMN_WRITE") {
    // Caller tried to write the version column. Use $cas instead.
  }
}

CasExhaustedError

Thrown by withOptimisticRetry when maxAttempts is reached without a successful commit — the target row kept changing under the read-modify-write loop. The error carries the attempt count and the last-observed version, useful for logging hot-row contention:

import { CasExhaustedError, withOptimisticRetry } from "@atscript/db";

try {
  await withOptimisticRetry(tokens, { id }, consumeToken);
} catch (err) {
  if (err instanceof CasExhaustedError) {
    console.warn(
      `Token ${id} contended out after ${err.attempts} attempts; ` +
        `last seen version=${err.lastSeenVersion}`,
    );
  }
}

CasExhaustedError extends DbError with code === "CAS_EXHAUSTED".

CasMismatchError

Thrown by touchMany with require: 'all' (the default) when fewer rows than keys matched their expected version — at least one key is stale or missing. Nothing was written: the pre-count refused before the first statement, or (a row moved in between) the SQL transaction rolled every bump back. The error carries the counts:

import { CasMismatchError } from "@atscript/db";

try {
  await orders.touchMany(keys);
} catch (err) {
  if (err instanceof CasMismatchError) {
    console.warn(`${err.matched} of ${err.expected} rows were current — reload and retry`);
  }
}

CasMismatchError extends DbError with code === "CAS_MISMATCH"; @atscript/moost-db maps it to HTTP 409.

Edge Cases & Gotchas

updateMany never CAS-checks

This is a locked design decision. A single expectedVersion cannot sensibly match N rows with different versions; per-row version locking belongs in bulkUpdate. Passing $cas to updateMany throws.

replaceOne supports CAS

replaceOne({ …, $cas: { version: N } }) behaves identically to updateOne from a CAS perspective: predicate filters by version, bump on success, matchedCount: 0 on mismatch.

$cas + field ops compose atomically

{ id, counter: $inc(), $cas: { version: 4 } } produces a single statement that either applies both the increment and the version bump or applies neither — there is no intermediate state.

Version on insert

@db.column.version implies a 0 default at insert time — see Version defaults. You do not need to add @db.default 0 explicitly, and you do not need to send the field: the shared validator treats it as server-managed (like @db.default*) on insert and replace, at any nesting depth — so nested inserts of versioned related rows omit it too (since 0.1.128). A version value supplied on insert is accepted as a plain number.

Empty patches

updateOne({ id }) (identifying fields only, no $cas) executes nothing and reports the row's existence; updateMany(filter, {}) likewise counts the matches and writes nothing — neither bumps a version (and a patch of only version-exempt fields does not either). Add $cas to turn the single-row form into a versioned touch; for a keyed batch use touchMany.

External writers do not auto-bump

Any process that writes to a versioned row outside the adapter (raw SQL migrations, ETL pipelines, ops scripts, replication catchup) will NOT bump the version column. A subsequent CAS-protected caller will then succeed against a stale state without knowing.

This is a known limitation of the application-layer approach. Consumers that need to defend against this should install a per-engine database trigger on the versioned table that auto-bumps version on every UPDATE. Atscript DB does not install such triggers.

Counter overflow

int32 saturates after ~2.1B updates. For tables that turn over that often, use int64 via the existing precision semantics. Default int32 is plenty for nearly every realistic scenario (2.1B writes to a single row is ~70 years of one write per second).

JSON columns

@db.json columns are independent of CAS. Version operates at the row level; the JSON sub-document is replaced wholesale on update, and the version bump applies to the row regardless of which fields changed (unless the JSON field is @db.column.version.exempt and is the only thing written).

End-to-End Example: Consuming a Backup Code

A classic single-use-credential race — two concurrent consumers must not both succeed:

interface User {
    @meta.id
    @db.default.uuid
    id: string

    email: string
    backupCodes: string[]

    @db.column.version
    version: number.int
}

import { withOptimisticRetry } from "@atscript/db";
import { sha256 } from "./crypto";

async function consumeBackupCode(userId: string, code: string): Promise<boolean> {
  const hash = sha256(code);
  let consumed = false;

  await withOptimisticRetry(
    users,
    { id: userId },
    (user) => {
      if (!user.backupCodes.includes(hash)) {
        return {}; // No-op patch — code not found. Still bumps version (harmless).
      }
      consumed = true;
      return {
        backupCodes: user.backupCodes.filter((h) => h !== hash),
      };
    },
    { maxAttempts: 5 },
  );

  return consumed;
}

Two concurrent consumeBackupCode calls with the same code: exactly one returns true, the other re-reads after the first commits, finds the code gone, and returns false. Guaranteed by the $cas predicate.

REST Clients

When using @atscript/moost-db, HTTP clients get OCC for free: round-trip the version field in your PATCH / PUT body and the controller auto-lifts it to $cas. Mismatches surface as 409 Conflict; missing rows surface as 404 Not Found. See CRUD Endpoints — OCC over HTTP.

Handling 409 in @atscript/db-client

Since @atscript/db-client 0.1.84, the client throws a typed VersionMismatchError subclass automatically whenever the server response carries kind: "version_mismatch" — no manual body inspection required:

import { VersionMismatchError } from "@atscript/db-client";

try {
  await users.update({ id, name: "X", version: row.version });
} catch (e) {
  if (e instanceof VersionMismatchError) {
    // e.currentVersion is the row's now-stored version — refresh + retry.
  }
}

instanceof VersionMismatchError is the recommended discriminator. On older db-client releases (pre-0.1.84), fall back to inspecting err.body?.kind === "version_mismatch" and reading err.body.currentVersion directly — the wire shape is unchanged, only the typed marker is new.

Since 0.1.128 the client also accepts the SDK shape — users.update({ id, $cas: { version: row.version } }) — and lifts it to the wire version field before preflight; a raw $cas body sent by any client is accepted by the server too. See HTTP Client — update.

Alternatives Considered

These are intentionally not supported in v1 — listed so you know they were considered and rejected:

• Timestamp-based versioning — suffers from clock skew on distributed writers and millisecond collision under high load.
• Hash-based versioning — pushes hashing into every read+write; harder to reason about than monotonic integers.
• Per-field versioning — strictly more granular (concurrent edits to disjoint fields succeed) but significantly more complex; the 80% use case is "this row's state machine moved on," which is row-level by nature.
• Pessimistic locking (SELECT FOR UPDATE) — separate concept; could be a future addition but not OCC.
• If-Match header — body-embedded version is simpler for SPA clients that already round-trip the row.
• opts.expectedVersion parameter — duplicate surface for the same semantics; $cas is the only way to opt in.

Next Steps

• Update & Patch — operator catalog, including $cas alongside $inc / $dec / $mul.
• CRUD Operations — basic updateOne / replaceOne reference.
• Defaults & Generated Values — how @db.column.version implies its default.
• HTTP CRUD — OCC over HTTP — REST behavior, 409 body shape, bulk semantics.