Optimistic Locking

$lock holds a row until the transaction ends, so it only reaches as far as a transaction does. A user who loads a form, thinks, and saves a minute later is two requests, with no transaction spanning them, and four backends have no row lock at all.

A version column closes that gap. The row carries a counter, the write carries the counter it read, and the update matches on it:

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

@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;
}

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 another writer got there first, the row is no longer at version 3, the update matches nothing, and it throws instead of overwriting their work:

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, and err.status === 409
  }
}

The error says which of three things happened - the row moved on, it is gone, or another condition of the $where excluded a row still at that version - because it reads the row by its id once before throwing. queryErrorKind(err) answers 'optimisticLock', and over the HTTP transport it becomes a 409. A payload that carries no version at all is a 400 there, the run-time half of the compile-time rule.

What the version costs you

• The payload has to carry it. That is a compile error, not a runtime surprise: updateOneById(Post, id, { title }) does not type-check on a versioned entity. The versionKey brand on the class is what carries version: true to the type level, since a decorator’s options never reach the entity’s type.
• The column is the lock’s, so it is NOT NULL DEFAULT 0 and the ORM writes it. updatable, onInsert, onUpdate, defaultValue, computed and isId are refused on it.
• One row, named by its id. One version cannot say which of many rows it belongs to, so a versioned update filters by the primary key. Other conditions may sit beside it - { id, tenantId } is fine - but updateMany over a filter that names many rows is refused.
• One statement, or none. A versioned update takes no $sort, $limit or $skip, its payload writes no relation, and on engines that cannot filter by a relation in an UPDATE its filter reads none: each of those would settle the rows in a separate statement first, putting the race back in the gap.
• Delete and restore take no version. They move the row’s lifecycle rather than its content, and two restores racing agree on the result anyway, so deleteOneById and restoreOneById work as on any entity and leave the version alone.
• saveOne, saveMany, upsertOne and upsertMany are refused on a versioned entity. MySQL’s ON DUPLICATE KEY UPDATE takes no WHERE, so the check cannot be expressed portably in an upsert, and a silently unguarded write is worse than a refusal. Insert or update it explicitly.

Which to use

	$lock	version
Reaches	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 of them
Fails by	waiting, or $wait: 'nowait'	throwing UqlOptimisticLockError

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

Next Steps

• Row Locking: $lock, wait policies and the SKIP LOCKED work queue.
• Error kinds: optimisticLock beside the constraint kinds.
• Soft Delete: what a delete does to a versioned row.