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	✅	✅	✅
PGlite	✅	✅	✅
CockroachDB	✅	✅	✅
MySQL	✅	✅	✅
MariaDB	✅	✅	❌ rejected: it has no FOR ... OF, so the lock would extend to the joined rows
SQLite, libSQL, Turso, D1	❌	❌	❌
MongoDB	❌	❌	❌

PGlite emits every one of those the way Postgres does, but being single-connection it has no second transaction to contend with, so a lock there never actually blocks. PGlite covers what that costs you.

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.