Optimistic Locking

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

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

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

  @Id({ type: 'uuid', onInsert: uuidv7 }) id!: string;
  @Field({ type: String }) title?: string | null;
  @Field({ type: Number, version: true }) 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 updatable, onInsert, onUpdate, defaultValue, computed and isId are refused on it. 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, and other conditions may sit beside it ({ id, tenantId }). It must also 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, since MySQL’s ON DUPLICATE KEY UPDATE takes no WHERE. Insert or update explicitly.

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 a minute later makes two requests, so 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.