Row-level locks

On PostgreSQL, find, findOne, and findAll accept a row-level lock in their third argument (SqlOperationOptions). The package wraps the generated SELECT with Drizzle's .for(...) clause. On MySQL the lock options are no-ops. The option is silently ignored, and no FOR UPDATE is generated.

When to use

Read-modify-write patterns where you need to prevent another transaction from mutating the row between your read and your update:

• Fetching an account balance, then decrementing it.
• Locking a posts row while computing a slug.
• Anything where a stale read would cause a lost-update bug.

If you only need to fetch data and write it later, prefer executeSqlTransaction and call select(...).for('update') directly. Locks are scoped to the transaction and released on commit or rollback.

Options

Two fields on SqlOperationOptions. Both Postgres-only:

lock: 'none' is the default. Pass it explicitly to override a service-level default. lock and forNoKeyUpdate are mutually exclusive; forNoKeyUpdate wins if both are set.

FOR NO KEY UPDATE is like FOR UPDATE but does not block other transactions from inserting FK-target rows. Useful when you're locking a parent row but children may legitimately appear (e.g. appending audit-log entries).

Examples

const account = await accounts.find(id, { lock: 'update' });
// SELECT ... FOR UPDATE

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

const parent = await posts.findOne({ slug }, { forNoKeyUpdate: true });
// SELECT ... FOR NO KEY UPDATE
// other transactions can still INSERT child rows referencing this parent

Caveats

• MySQL. The options are accepted but ignored. No FOR UPDATE is added. If you need MySQL locking, drop down to a custom method.
• No assertions under contention. The package's unit tests cover the code path (the option reaches Drizzle's .for(...)); contention behavior is the database's contract, not ours. Verify with a concurrent integration test before relying on it.
• Read methods only. create / update / delete / softDelete / restore have their own transaction model. They don't take a lock option. Use executeSqlTransaction and a find(..., { lock }) inside it if you need to lock across reads and writes.

Next

• → Transactions, executeSqlTransaction for multi-statement locks
• → API reference: full SqlOperationOptions signature