Row Locking

Reading a row and writing it back are two separate statements. Another transaction can read the same row in between; both then write, the second write wins, and the first update is lost with no error anywhere.

$lock closes that gap: it locks the rows a query returns and holds them until the transaction ends.

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

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

Requires a transaction

Every engine accepts FOR UPDATE in autocommit and then releases the lock as the statement commits, before you can act on the rows: correct SQL that protects nothing. UQL rejects it rather than letting it look like it worked.

// throws: $lock requires an open transaction
await pool.findMany(Item, { $lock: true });

That covers pool.findMany and its siblings, which take their own auto-committing connection. Use the querier the transaction callback hands you.

Wait policies

What to do about a row someone else already holds:

$lock: true; // wait for whoever holds the rows (same as { wait: 'block' })
$lock: {
  wait: 'nowait';
} // fail immediately instead of waiting
$lock: {
  wait: 'skip';
} // leave locked rows out of the result
$lock: false; // no lock, for a query built conditionally

With nowait the engine raises an error, so the transaction rolls back unless you catch it.

A work queue

{ wait: 'skip' } is what makes a queue on your database possible. Each worker takes the rows nobody else holds, so two workers never draw 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 });
  }
  return batch;
});

postgres

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

Expect fewer rows than $limit when other workers hold some. That is the feature working: ask for more than you need, or loop.

What gets locked

$lock locks rows of the queried entity and nothing else. A relation reached through $populate is not locked:

await pool.transaction(async (querier) => {
  // the items are locked; the company reached through $populate is not
  await querier.findMany(Item, { $populate: { company: true }, $lock: true });
});

A to-many relation is loaded by a second statement, which carries no lock. A to-one is joined into the same statement, whether $populate asked for it or $sort needed it, and UQL narrows the lock to the queried table for you (FOR UPDATE OF "Item"): a bare lock over a LEFT JOIN is an error on PostgreSQL and silently locks the joined rows everywhere else. To lock a related row, query it directly.

$lock belongs to a find, and the types keep it there: count, update, and delete do not accept it, and neither does a nested $populate query.

Engine support

Engine	$lock: true	wait: 'skip' / 'nowait'	with a joined relation
PostgreSQL	✅	✅	✅
CockroachDB	✅	✅	✅
MySQL	✅	✅	✅
MariaDB	✅	✅	❌ rejected: it has no FOR ... OF, so the lock would extend to the joined rows
SQLite, libSQL, Turso, D1	❌	❌	❌
MongoDB	❌	❌	❌

SQLite locks the whole database rather than individual rows, and MongoDB has no row lock to map onto, so both reject $lock instead of ignoring it. There the transaction is the whole of the concurrency control; on MongoDB, an atomic update such as findOneAndUpdate is the idiom.

$lock is also rejected over the HTTP transport with a 400: each request runs on its own auto-committing connection, so a lock taken for one would be released before the response was written.