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:
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"
Defaults to "none"
forNoKeyUpdateOptionalboolean
Defaults to false
| Option | SQL | Blocks |
|---|---|---|
lock: 'update' | FOR UPDATE OF <table> | Every other lock and every write to the rows. |
forNoKeyUpdate: true | FOR 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 unset | none | Nothing. |
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.
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 postWhich read methods accept a lock?
find, findOne, findAll and findByCursor:
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:
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
The clause is added, but the lock ends with the statement because there is no surrounding transaction. Always pass { transaction: tx } with a lock.
On PostgreSQL, only the service's own table: the package adds OF <table>, so joined belongs-to relations are not locked. On MySQL the joined rows are locked too.
forNoKeyUpdate wins and the query uses FOR NO KEY UPDATE.
Use a row lock when one transaction reads and writes a row in one go and others should wait. Use optimistic locking with a version column for a read, edit, save flow where the edit happens in a client.
Next steps
Drizzle transactions in NestJS
Open the transaction a lock needs and pass it to every call.
Drizzle optimistic locking with versions
A version column that turns a stale write into a 409 without holding a lock.
SqlBaseCrudService API reference
The full SqlOperationOptions type, including lock and forNoKeyUpdate.
Drizzle transactions in NestJS
Run Drizzle transactions in NestJS across several service calls. Open one with executeSqlTransaction or db.transaction and pass the handle to each call.
NestJS Drizzle filter and column allowlists
A NestJS Drizzle filter allowlist limits the columns a client may filter, sort, select, search and write. Anything outside a set list is a 400.