Optimistic Locking

A version column stops one user from overwriting another’s changes. An update sends the version it read, and matches only if the row is still at that version. It works across requests, on every engine.

import { f, Entity, Field, filledKey, versionKey } from 'uql-orm';
import { v7 as uuidv7 } from 'uuid';

@Entity()
export class Post {
  [versionKey]?: 'version';
  [filledKey]?: 'id';

  @Field(f.uuid().id().onInsert(uuidv7)) id!: string;
  @Field(f.string().nullable()) title?: string | null;
  @Field(f.version()) version!: number;
}

The [versionKey] line tells TypeScript which field is the version, since decorator options never reach the type. With it, an update that leaves out the version does not compile:

const postId = '0190a1b2-c3d4-7e5f-8a6b-7c8d9e0f1a2b';
const post = await pool.findOneById(Post, postId); // version: 3
await pool.updateOneById(Post, postId, {
  title: 'Edited',
  version: post!.version,
});

One statement, on every engine

UPDATE "Post" SET "title" = ?, "version" = ? WHERE "id" = ? AND "version" = ?

If someone saved first, the update matches nothing and throws UqlOptimisticLockError, with expected and actual versions and a message saying whether the version moved on, the row is gone, or another $where condition excluded it. queryErrorKind(err) answers 'optimisticLock', and over HTTP it is a 409.

import { UqlOptimisticLockError } from 'uql-orm';

try {
  await pool.updateOneById(Post, postId, { title: 'Edited', version: 3 });
} catch (err) {
  if (err instanceof UqlOptimisticLockError) {
    // err.expected === 3, err.actual === 4
  }
}

Rules

The column is NOT NULL DEFAULT 0 and uql writes it, so f.version() takes only .name() and .comment(): nothing else may decide its value. An update without a version that slips past the compiler throws UqlUsageError (a 400 over HTTP).

A versioned update names one row by its primary key, with other conditions beside it if you like ({ id, tenantId }). It must be a single statement, so these are refused:

• updateMany over a filter matching many rows.
• $sort, $limit or $skip, and a payload that writes a relation.
• A $where on a relation, on engines that cannot filter by one in an UPDATE.

saveOne, saveMany, upsertOne and upsertMany read the rows first, in a transaction (on MongoDB, in one you open): one they find is updated by its id at the version it carries, as above, and the rest are inserted.

deleteOneById, restoreOneById and restoreMany take no version and leave it alone.

Version or row lock

$lock holds a row until the transaction ends. A user who loads a form and saves it a minute later makes two requests, and only a version covers that.

	$lock	version
Covers	one transaction	any number of requests
Costs	a held lock, and waiting writers	a retry when a conflict happens
Engines	all but SQLite, libSQL, Turso, D1 and MongoDB	all
Fails by	waiting, or $wait: 'nowait'	throwing UqlOptimisticLockError
Error kind	'retryable'	'optimisticLock'

Use $lock when the read and the write share a transaction and contention is likely. Use a version when they do not, which on the web is most of the time.