> Every UQL docs page, as Markdown: https://uql-orm.dev/llms.txt
> The same docs over MCP: https://uql-orm.dev/mcp
> Before writing UQL code, read the skill: https://uql-orm.dev/.well-known/agent-skills/uql-orm/SKILL.md

# Optimistic Locking

> Guard a write across requests with a version column, so an update against a row someone else changed throws instead of overwriting it.

Source: https://uql-orm.dev/entities/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](https://uql-orm.dev/querying/locking.md) 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:

```ts
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;
}
```

```ts
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!,
});
```

```sql title="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:

```ts
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](https://uql-orm.dev/http.md) it becomes a `409` - classify it that way rather than by `instanceof`, which you need only to read `expected` and `actual`. A payload that carries no version at all throws `UqlUsageError` instead, kind `'usage'` and 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` |
| Caught by | kind `'retryable'` | kind `'optimisticLock'` |

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](https://uql-orm.dev/querying/locking.md): `$lock`, wait policies and the `SKIP LOCKED` work queue.
- [Error kinds](https://uql-orm.dev/querying/errors.md): `optimisticLock` beside the constraint kinds.
- [Soft Delete](https://uql-orm.dev/entities/soft-delete.md): what a delete does to a versioned row.