Row locks with SELECT FOR UPDATE

Drizzle SELECT FOR UPDATE in NestJS. Pass lock or forNoKeyUpdate to find, findOne or findAll inside a transaction to lock rows on PostgreSQL or MySQL.

For a Drizzle SELECT ... FOR UPDATE in NestJS, pass lock: 'update' (or 'share', or forNoKeyUpdate: true) to find, findOne, findAll or findByCursor, together with a transaction. The package adds Drizzle's .for(...) clause to the query, naming only the service's own table, and the rows stay locked until the transaction commits or rolls back. Row locks work on PostgreSQL and MySQL; on SQLite asking for one is a 400.

A lock without a transaction does nothing useful

Pass { transaction: tx } with the lock. Without it the SELECT runs in its own implicit transaction, and the lock is released the moment the statement ends, before your write.

How do I lock a row before updating it?

Read it with lock: 'update' inside a transaction, then write in the same transaction:

accounts.service.ts
withdraw(id: number, amount: number) {
  return this.executeSqlTransaction(async (tx) => {
    const account = await this.find(id, { lock: 'update', transaction: tx });
    // SELECT ... FROM accounts WHERE id = $1 LIMIT 1 FOR UPDATE OF "accounts"
    if (!account || account.balance < amount) {
      throw new BadRequestException('Insufficient funds');
    }
    return this.update(id, { balance: account.balance - amount }, { transaction: tx });
  });
}

A second transaction that runs the same find waits until the first one ends, so it reads the new balance instead of a stale one. That is the lost update this prevents. See Transactions for executeSqlTransaction and db.transaction.

What lock options are there?

Two fields on SqlOperationOptions. The SQL below is PostgreSQL's; MySQL differs as described further down:

lockOptional

"update" | "share" | "none"

FOR UPDATE or FOR SHARE on the selected rows. "none" adds no clause.

Defaults to "none"

forNoKeyUpdateOptional

boolean

FOR NO KEY UPDATE on the selected rows. Wins over lock when both are set.

Defaults to false

OptionSQLBlocks
lock: 'update'FOR UPDATE OF <table>Every other lock and every write to the rows.
forNoKeyUpdate: trueFOR NO KEY UPDATE OF <table>Other writes, but not foreign key checks.
lock: 'share'FOR SHARE OF <table>Writes, but not other FOR SHARE readers.
lock: 'none' or unsetnoneNothing.

When should I use FOR NO KEY UPDATE?

Use it when you lock a parent row but other transactions may still insert child rows that reference it. FOR NO KEY UPDATE is like FOR UPDATE, but it does not conflict with the key-share lock PostgreSQL takes when it checks a foreign key, so inserting a child row (an audit log entry, say) does not wait for your transaction.

posts.service.ts
const parent = await this.findOne({ slug }, { forNoKeyUpdate: true, transaction: tx });
// SELECT ... FOR NO KEY UPDATE OF "posts"
// other transactions can still INSERT rows that reference this post

Which read methods accept a lock?

find, findOne, findAll and findByCursor:

accounts.service.ts
const active = await this.findAll(
  { status: 'active' },
  { page: 1, limit: 50 },
  { lock: 'share', transaction: tx },
);
// SELECT ... FROM accounts WHERE ... ORDER BY ... LIMIT 50 OFFSET 0 FOR SHARE OF "accounts"

On findAll and findByCursor the lock goes on the data query only. The COUNT(*) query is not locked. The write methods (create, update, delete, softDelete, restore) do not take a lock option: lock with a find first, in the same transaction, as above.

Can I lock a row and eager-load a belongs-to relation?

Yes. The lock always names the base table (FOR UPDATE OF <table>), so only the service's own rows are locked and the LEFT JOINed relation is not:

posts.service.ts
const post = await this.find(id, { lock: 'update', relations: ['author'], transaction: tx });
// SELECT ... FROM posts LEFT JOIN users ... FOR UPDATE OF "posts"

Before 3.3.1 the lock covered every table in the query, and PostgreSQL refused it with FOR UPDATE cannot be applied to the nullable side of an outer join.

Do row locks work on MySQL and SQLite?

On MySQL, yes, since 3.7.0. lock: 'update' adds FOR UPDATE and lock: 'share' adds FOR SHARE. MySQL has no FOR NO KEY UPDATE, so forNoKeyUpdate: true becomes FOR UPDATE, the stronger lock. MySQL has no OF <table> form in Drizzle, so with an eager belongs-to the joined parent row is locked too. Before 3.7.0 the options were accepted and ignored on MySQL.

On SQLite, no. SQLite locks the whole database for a write and has no row locks, so lock (other than "none") or forNoKeyUpdate is a 400 rather than a lock that is silently not there.

Can I use SKIP LOCKED or NOWAIT?

Not through the package options: lock and forNoKeyUpdate only choose the lock strength. For a job queue that needs SKIP LOCKED, use Drizzle's own .for('update', { skipLocked: true }) on tx inside the same transaction, and pass the same tx to the package calls that follow.

Common questions

Next steps

Last updated View change history

On this page