Row Locking

Reading a row and writing it back are two statements, and another transaction can read the same row in between. Both then write, and the first update is lost without a word. $lock locks the rows a query returns until the transaction ends:

await pool.transaction(async (querier) => {
  const item = await querier.findOneById(Item, id, {
    $select: { stock: true },
    $lock: true,
  });
  await querier.updateOneById(Item, id, { stock: item!.stock! - 1 });
});

Without the lock, two overlapping callers both read 5 and both write 4. With it, the second waits for the first to commit, then reads 4 and writes 3.

$lock needs an open transaction. Outside one, the lock would be released as the statement commits, so UQL throws a UqlUsageError instead. A pool call such as pool.findMany outside a transaction runs on its own auto-committing connection, so it throws too.

When the row is already locked

By default the query waits. $wait picks something else:

$lock	A row another transaction holds
true	wait for it
{ $wait: 'nowait' }	fail at once
{ $wait: 'skip' }	leave it out of the result
false	no lock, handy in a conditional query

A nowait failure rolls back the transaction unless you catch it. queryErrorKind names it retryable on every engine, as it does a deadlock: run the transaction again.

skip turns a table into a work queue. Each worker takes only rows nobody else holds, so no two workers get the same job:

await pool.transaction(async (querier) => {
  const batch = await querier.findMany(Item, {
    $select: { id: true },
    $where: { isActive: true },
    $sort: { createdAt: 'asc' },
    $limit: 10,
    $lock: { $wait: 'skip' },
  });
  for (const item of batch) {
    await querier.updateOneById(Item, item.id, { isActive: false });
  }
});

PostgreSQL

SELECT "id" FROM "Item" WHERE "isActive" = $1
ORDER BY "createdAt" LIMIT 10 FOR UPDATE SKIP LOCKED

Expect fewer rows than $limit when other workers hold some.

What gets locked

Only rows of the queried entity. Relations loaded with $populate are not locked: a to-many is read in a subquery, and for a joined to-one UQL narrows the lock to the queried table (FOR UPDATE OF "Item"). To lock a related row, query it directly. count, updateMany, deleteMany and a nested $populate query take no $lock.

Engine support

Engine	$lock and $wait	With a joined relation
PostgreSQL, CockroachDB, PGlite	✅	✅
MySQL, MSSQL	✅	✅
MariaDB	✅	❌ rejected: without FOR ... OF, the lock would cover joined rows
SQLite, libSQL, Turso, D1	❌	❌
MongoDB	❌	❌

SQLite locks the whole database rather than rows, and MongoDB has no row locks, so both reject $lock rather than ignore it. There the transaction is your concurrency control, and on MongoDB an atomic update such as $inc with a guard in $where is the usual tool. PGlite has a single connection, so its locks never block (see PGlite). The HTTP transport rejects $lock with a 400, because each request commits on its own.

Across requests

A lock lasts only as long as its transaction. When a user loads a form and saves it a minute later, or on an engine without row locks, guard the write with a version column instead: see Optimistic Locking. The two work together, since a versioned row read under $lock cannot change before you write it.